mysql导入csv的4种报错的解决方法
16lz
2021-12-14
以此悼念我今天踩过的4个坑……
坑一:local的错
报错:
ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides
修正:去掉local
mysql> load data infile ……
mysql> load data infile ……
mysql>load data infile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/user_info_utf.csv' ……
坑三:文件格式错
报错:
ERROR 1366 (HY000): Incorrect integer value: ‘\FEFF1' for column ‘userId' at row 1
修正:修改文件格式
先用excel打开,另存为,保存类型-utf8,工具-web选项-编码utf8,替换原来文件:
再用“记事本”打开,另存为,保存类型-所有文件,编码-utf8,替换原来文件:
坑四:空值的错
报错:
ERROR 1292 (22007): Incorrect date value: ‘' for column ‘birth' at row 18
修正:
修改sql_mode:
mysql> set @@sql_mode=ANSI;
最后爬出坑:
mysql>load data infile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/user_info_utf.csv' into table data.userinfo fields terminated by ',' optionally enclosed by '"' escaped by '"'lines terminated by '\r\n';
更多相关文章
- Android(安卓)报错:Caused by: android.os.FileUriExposedExcepti
- Ionic 运行报错No resource identifier found for attribute 'ap
- android中HttpURLConnection调用getResponseCode()时崩溃 解决方
- Android空引用问题的解决方法——on a null object reference
- android读取assets大于1M文件的解决方法
- 几个Android小错误解决方法
- Android(安卓)启动Tomcat服务报错,端口占用解决方案
- Android(安卓)项目混编flutter报错
- Gradle Sync Failed,报错"could not find com.android.support:ap