load data infile方式数据无法正确导入的问题
请教大家
看资料上讲到load data方式导入数据速度很快 于是我测试了下
但是我用 select * into outfile 导出数据
load data infile 方式导入数据 但是数据导入时 报错 插入不进去
导入 ：
mysql> SELECT * INTO OUTFILE ‘/usr/local/mysql/var/emp.txt’ fields terminated by ‘,’ optionally enclosed by “‘” lines terminated by ‘;\n’ from emp;
Query OK, 7 rows affected (0.00 sec)
查看导出文件：
[*@** ]$more emp.txt
1,’a’,’dba’,’2012-01-01 12:12:12′,12.23;
2,’b’,’dba’,’2012-02-02 22:22:22′,98.23;
3,’d’,’pm’,’2008-02-02 10:22:22′,198.23;
4,’e’,’dev’,’2010-10-02 08:22:30′,108.23;
5,’f’,’sell’,’2011-10-02 11:22:30′,360.23;
1001,’we’,’manager’,’2001-11-08 09:21:30′,8880.23;
20001,’dd’,’caiwu’,’2009-08-12 14:21:30′,80.23;
导入：
mysql> load data infile ‘/usr/local/mysql/var/emp.txt’ into table emp;
Query OK, 7 rows affected, 35 warnings (0.00 sec)
Records: 7 Deleted: 0 Skipped: 0 Warnings: 35
报错及报错信息：
mysql> show warnings;
+———+——+——————————————–+
| Level | Code | Message |
+———+——+——————————————–+
| Warning | 1265 | Data truncated for column ’empno’ at row 1 |
| Warning | 1261 | Row 1 doesn’t contain data for all columns |
| Warning | 1261 | Row 1 doesn’t contain data for all columns |
| Warning | 1261 | Row 1 doesn’t contain data for all columns |
| Warning | 1261 | Row 1 doesn’t contain data for all columns |
| Warning | 1265 | Data truncated for column ’empno’ at row 2 |
| Warning | 1261 | Row 2 doesn’t contain data for all columns |
| Warning | 1261 | Row 2 doesn’t contain data for all columns |
| Warning | 1261 | Row 2 doesn’t contain data for all columns |
| Warning | 1261 | Row 2 doesn’t contain data for all columns |
| Warning | 1265 | Data truncated for column ’empno’ at row 3 |
| Warning | 1261 | Row 3 doesn’t contain data for all columns |
| Warning | 1261 | Row 3 doesn’t contain data for all columns |
| Warning | 1261 | Row 3 doesn’t contain data for all columns |
| Warning | 1261 | Row 3 doesn’t contain data for all columns |
| Warning | 1265 | Data truncated for column ’empno’ at row 4 |
| Warning | 1261 | Row 4 doesn’t contain data for all columns |
| Warning | 1261 | Row 4 doesn’t contain data for all columns |
| Warning | 1261 | Row 4 doesn’t contain data for all columns |
| Warning | 1261 | Row 4 doesn’t contain data for all columns |
| Warning | 1265 | Data truncated for column ’empno’ at row 5 |
| Warning | 1261 | Row 5 doesn’t contain data for all columns |
| Warning | 1261 | Row 5 doesn’t contain data for all columns |
| Warning | 1261 | Row 5 doesn’t contain data for all columns |
| Warning | 1261 | Row 5 doesn’t contain data for all columns |
| Warning | 1265 | Data truncated for column ’empno’ at row 6 |
| Warning | 1261 | Row 6 doesn’t contain data for all columns |
| Warning | 1261 | Row 6 doesn’t contain data for all columns |
| Warning | 1261 | Row 6 doesn’t contain data for all columns |
| Warning | 1261 | Row 6 doesn’t contain data for all columns |
| Warning | 1265 | Data truncated for column ’empno’ at row 7 |
| Warning | 1261 | Row 7 doesn’t contain data for all columns |
| Warning | 1261 | Row 7 doesn’t contain data for all columns |
| Warning | 1261 | Row 7 doesn’t contain data for all columns |
| Warning | 1261 | Row 7 doesn’t contain data for all columns |
+———+——+——————————————–+
35 rows in set (0.01 sec)
查看表导入的数据：
mysql> SELECT * FROM emp;
+——-+——-+——+———-+——+
| empno | ename | job | hiredate | sal |
+——-+——-+——+———-+——+
| 1 | NULL | NULL | NULL | NULL |
