mysql realizes the operation of exporting csv file and importing csv file to database

This article describes the example of mysql query results export csv file and import csv file to the database operation. Share it for your reference, as follows:

mysql query results are exported to csv file:

select logtime, operatingsystem, imei
from GameCenterLogs
where
  operatingsystem >= 1 and operatingsystem <=3
group by operatingsystem,imei
into outfile '/tmp_logs/tmp.csv'
fields TERMINATED BY ',' OPTIONALLY ENCLOSED BY '#' LINES TERMINATED BY '\r\n'

fields TERMINATED BY ',' Set the divider of the field

OPTIONALLY ENCLOSED BY '#' If the field content is a string, use '#' to include

LINES TERMINATED BY '\r\n' Data row divider

Export file contents:

1453513680,3,#hello word#\r\n
1453515470,2,#title content#\r\n

The mysql command line imports the csv file into the database:

load data infile '/tmp_logs/tmp.csv'
into table GameCenterDAULogs fields terminated by ',' OPTIONALLY ENCLOSED BY '#' lines terminated by '\r\n'

It is possible that the operating system is different, and the data row separator of the exported csv file is not 1, which is set as\ r\ n. You can use cat -A /tmp_logs/tmp.csv View the ending character

More readers interested in MySQL can check out the topics on this site: "MySQL Common Functions Summary", "MySQL Log Operation Skills Collection", "MySQL Transaction Operation Skills Collection", "MySQL Stored Procedure Skills Collection" and "MySQL Database Lock Related Skills Collection"

I hope this article is helpful to everyone's MySQL database.