MySQL Tips and Tricks
— mysql — 1 min read
Last updated : 26/April/2020
Events are in reverse chronological order.
- 2020/04/26 - on duplicate update
- 2020/04/26 - Row change timestamp
- 2020/04/26 - Running MySQL script from windows prompt
- 2020/04/26 - Loading mysql tables
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,
- Input file path, its Windows, so remember its a forward slash
IGNOREignores duplicates while loading into table
- Columns are terminated by pipe symbol(
|) rather than comma(
,) as unitName can contain commas.
- Ignore the header line
- 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.