我正在尝试在我的表格中导入经典城市列表。我在 AWS RDS 上使用 Mysql 5.7.22-log。 这是表 DDL:
CREATE TABLE `city` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`createdBy` varchar(255) DEFAULT NULL,
`createdDate` datetime NOT NULL,
`lastModifiedBy` varchar(255) DEFAULT NULL,
`lastModifiedDate` datetime DEFAULT NULL,
`sid` varchar(36) NOT NULL,
`version` bigint(20) NOT NULL,
`country` varchar(2) NOT NULL,
`district` varchar(255) DEFAULT NULL,
`name` varchar(255) NOT NULL,
`region` varchar(255) DEFAULT NULL,
`zipCode` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `UK_i22k5i5v70edpr8sn0f7qsqu8` (`sid`),
UNIQUE KEY `UKprra9tj5gtk21kc3chb9skup9` (`name`,`district`,`country`)
) ENGINE=InnoDB AUTO_INCREMENT=8192 DEFAULT CHARSET=utf8
我有一个 UTF-8 .csv文件,如下所示:
name;district;zipCode;region;country
Abano Terme;PD;35031;Veneto;IT
Abbadia Cerreto;LO;26834;Lombardia;IT
Abbadia Lariana;LC;23821;Lombardia;IT
Abbadia San Salvatore;SI;53021;Toscana;IT
我以这种方式导入数据:
LOAD DATA LOCAL INFILE 'C:\City.csv' INTO TABLE City FIELDS TERMINATED BY ';' IGNORE 1 LINES
(NAME,district,zipCode,region,country)
SET `createdBy`='system',createdDate=NOW(),lastModifiedBy='system',lastModifiedDate=NOW(),sid=UUID(),`version`=1;
数据被导入,但是我有很多截断数据的警告:
1 queries executed, 1 success, 0 errors, 1 warnings
Query: LOAD DATA LOCAL INFILE 'C:\City.csv' INTO TABLE City FIELDS TERMINATED BY ';' IGNORE 1 LINES (n...
8103 row(s) affected, 8100 warning(s)
Execution Time : 0.309 sec
Transfer Time : 0.002 sec
Total Time : 0.312 sec
Note Code : 1265
Data truncated for column 'country' at row 1
Note Code : 1265
Data truncated for column 'country' at row 2
Note Code : 1265
Data truncated for column 'country' at row 3
Note Code : 1265
Data truncated for column 'country' at row 4
Note Code : 1265
Data truncated for column 'country' at row 5
Note Code : 1265
Data truncated for column 'country' at row 6
Note Code : 1265
Data truncated for column 'country' at row 7
我不明白为什么,在 csv 文件中,contry 值是 2 个字符。
我做错了什么?
你看过加载了什么数据吗?
默认情况下,LOAD DATA
假定行由换行符终止,但 MSWindows(从 MS-DOS 继承,从 CPM 继承(使用回车符和换行符作为行终止符。所以它看到:
name;district;zipCode;region;countryr
Abano Terme;PD;35031;Veneto;ITr
Abbadia Cerreto;LO;26834;Lombardia;ITr
Abbadia Lariana;LC;23821;Lombardia;ITr
Abbadia San Salvatore;SI;53021;Toscana;ITr
尝试:
LOAD DATA LOCAL INFILE 'C:\City.csv'
INTO TABLE City
FIELDS TERMINATED BY ';'
LINES TERMINATED BY 'rn'
IGNORE 1 LINES
(NAME,district,zipCode,region,country)
SET `createdBy`='system'
,createdDate=NOW()
,lastModifiedBy='system'
,lastModifiedDate=NOW()
,sid=UUID()
,`version`=1;
如果我清楚地了解您,您需要在 City 表中更改country
属性的长度超过 2,如下所示。
`country` varchar(50) NOT NULL,