mysql导入csv文件

load data infile '｛csvpath｝'  into table {表名}  fields terminated by ',' lines terminated by '\r\n' ;

 

导入过程中可能的问题：

1 .  [Err] 1300 - Invalid utf8 character string: ''

csv文件必须是utf8格式, windows可以使用notepad++转换编码为utf-8。注意不能为utf-8无bom

2.  [Err] 1366 - Incorrect integer value: ' 521' for column 'dxbusid' at row 1

SET sql_mode = "";

然后需要将语句中有问题的值进行处理

3. [Err] 1290 - The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

需要在mysql.ini中查找：secure-file-priv设置的值。并将要执行的csv文件放在改值对应目录下。

 

参考：

https://stackoverflow.com/questions/43408012/mysql-invalid-utf8-character-string-when-importing-csv-table

https://stackoverflow.com/questions/2536199/1366-incorrect-integer-valuemysql

https://blog.csdn.net/quiet_girl/article/details/71436108