Skip to content

bobby_dreamer

MySQL Tips and Tricks

mysql1 min read

Last updated : 26/April/2020

Events are in reverse chronological order.


on duplicate update

VALUES(col_name) in the ON DUPLICATE KEY UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred

Above when it says, "2 rows affected", its not two rows, its just value 2, for example,

  • if 0, had come, it means nothing is udpated
  • 1 means row is inserted
  • 2 means row is updated.

Its confusing a bit here but it is how it is.


Row change timestamp

Represents timestamp, when the row was last changed.


Running MySQL script from windows prompt

Below is the load card to truncate and load data into mySQL table

Below windows batch file to execute the above load control card,

FYI : Load card - Its a mainframe term for Db2 Load control card


Loading mysql tables

Line number and description,

  1. Input file path, its Windows, so remember its a forward slash
  2. IGNORE ignores duplicates while loading into table
  3. Columns are terminated by pipe symbol(|) rather than comma(,) as unitName can contain commas.
  4. Ignore the header line
  5. Table Column names as they appear in the delimited file

Updating values while loading the table

SET clause can be used for this purpose, all MySQL functions can be used.

Thanks