1366不正确的整数值:将数据从CSV导入到mysql时出错
问题描述:
我正试图使用LOAD DATA LOCAL INFILE将数据从CSV导入到MySQL。对于所谓的 “数” 有我收到错误1366不正确的整数值的所有空值的整数列:1366不正确的整数值:将数据从CSV导入到mysql时出错
我已经试过count = IF(@count='',NULL,@count)
CREATE TABLE MySchema.response:
CREATE TABLE MySchema.response
(
`id` int,
`name` varchar(500),
`count` int,
);
将数据加载到表:
LOAD DATA LOCAL INFILE 'C:/response.csv'
INTO TABLE MySchema.response
FIELDS TERMINATED BY ',' optionally ENCLOSED by '"'
ignore 1 lines
(id, name, @count)
SET count = IF(@count='',NULL,@count)
样本CSV文件数据
1,abc,
2,xyz,
答
你忘了定义VAR,请参见下面正确LOAD DATA INFILE ...
声明:
[email protected] [test]> SHOW CREATE TABLE `Response`\G
*************************** 1. row ***************************
Table: Response
Create Table: CREATE TABLE `Response` (
`id` int(11) DEFAULT NULL,
`name` varchar(500) DEFAULT NULL,
`count` int(11) DEFAULT NULL
)
[email protected] [test]> LOAD DATA INFILE '~/Desktop/data.csv' INTO TABLE Response FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (id, name, @count) SET count = IF(@count = '', NULL, @count);
[email protected] [test]> SELECT * FROM `Response`;
+------+------+-------+
| id | name | count |
+------+------+-------+
| 1 | abc | NULL |
| 2 | xyz | 12345 |
+------+------+-------+
文件data.csv
1,abc,
2,xyz,12345
+0
对不起,这是一个错字...我已经定义了var –
我遇到了同样的问题,现在。你有没有设法解决这个问题? –