mysql - 错误 1062 (23000) : Duplicate entry though entries are not duplicate

Question

表定义:

CREATE TABLE PositionalDataNGS ( Date DATE, Time TIME(3) , X FLOAT(5), Y FLOAT(5), D FLOAT(5) , V FLOAT(5),  OnField BOOL, GameID_GSIS INT, TeamID INT, PlayerID_GSIS CHAR(10), PRIMARY KEY(Time) );

这会创建所需的表格，我需要用以下条目填充它:

    Data, Time, X, Y, D, V, OnFeild, GameID, TeamID,  PlayerID_GSIS  
 2015-11-15 ,17:56:45.300 ,  93.32,  8.6,  4.57,  0.45 , true,   2015111500  , 5110  , 22-0026189 
 2015-11-15 ,17:56:45.400 ,  93.77,  8.48,  4.55,  0.47 , true,   2015111500  , 5110  , 02-0022289 
 2015-11-15 ,17:56:45.500 ,  94.23,  8.36,  4.53,  0.47 , true,   2015111500  , 5110  , 03-0026179 
 2015-11-15 ,17:56:45.600 ,  94.67,  8.23,  4.51,  0.46 , true,   2015111500  , 5110  , 30-0026180 

显然时间不重复，但是当尝试将 csv 表填充到数据库时:

LOAD DATA INFILE "/PositionalDataNGS_2.csv" INTO TABLE PositionalDataNGS COLUMNS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES;

我得到这个错误:错误 1062 (23000):键“PRIMARY”的重复条目“17:56:45.300”

这是 MySQL 5.6.28，因此它应该能够处理几分之一秒的时间。谢谢!

Answer 1

我无法重现问题。在文件 PositionalDataNGS_2.csv 中没有重复值？

测试:

mysql> SELECT VERSION();
+-------------------------+
| VERSION()               |
+-------------------------+
| 5.6.28                  |
+-------------------------+
1 row in set (0.00 sec)

mysql> DROP TABLE IF EXISTS `PositionalDataNGS`;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE `PositionalDataNGS`(
    ->   `Date` DATE,
    ->   `Time` TIME(3),
    ->   `X` FLOAT(5),
    ->   `Y` FLOAT(5),
    ->   `D` FLOAT(5),
    ->   `V` FLOAT(5),
    ->   `OnField` BOOL,
    ->   `GameID_GSIS` INT,
    ->   `TeamID` INT,
    ->   `PlayerID_GSIS` CHAR(10),
    ->   PRIMARY KEY(`Time`)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `PositionalDataNGS` (
    ->   `Date`,
    ->   `Time`,
    ->   `X`,
    ->   `Y`,
    ->   `D`,
    ->   `V`,
    ->   `OnField`,
    ->   `GameID_GSIS`,
    ->   `TeamID`,
    ->   `PlayerID_GSIS`
    -> )
    -> VALUES
    -> ('2015-11-15', '17:56:45.300', 93.32, 8.6, 4.57, 0.45, true, '2015111500', 5110, '22-0026189'),
    -> ('2015-11-15', '17:56:45.400', 93.77, 8.48, 4.55, 0.47, true, '2015111500', 5110, '02-0022289'),
    -> ('2015-11-15', '17:56:45.500', 94.23, 8.36, 4.53, 0.47, true, '2015111500', 5110, '03-0026179'),
    -> ('2015-11-15', '17:56:45.600', 94.67, 8.23, 4.51, 0.46, true, '2015111500', 5110, '30-0026180');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT
    ->   `Date`,
    ->   `Time`,
    ->   `X`,
    ->   `Y`,
    ->   `D`,
    ->   `V`,
    ->   `OnField`,
    ->   `GameID_GSIS`,
    ->   `TeamID`,
    ->   `PlayerID_GSIS`
    -> FROM
    ->   `PositionalDataNGS`;
+------------+--------------+-------+------+------+------+---------+-------------+--------+---------------+
| Date       | Time         | X     | Y    | D    | V    | OnField | GameID_GSIS | TeamID | PlayerID_GSIS |
+------------+--------------+-------+------+------+------+---------+-------------+--------+---------------+
| 2015-11-15 | 17:56:45.300 | 93.32 |  8.6 | 4.57 | 0.45 |       1 |  2015111500 |   5110 | 22-0026189    |
| 2015-11-15 | 17:56:45.400 | 93.77 | 8.48 | 4.55 | 0.47 |       1 |  2015111500 |   5110 | 02-0022289    |
| 2015-11-15 | 17:56:45.500 | 94.23 | 8.36 | 4.53 | 0.47 |       1 |  2015111500 |   5110 | 03-0026179    |
| 2015-11-15 | 17:56:45.600 | 94.67 | 8.23 | 4.51 | 0.46 |       1 |  2015111500 |   5110 | 30-0026180    |
+------------+--------------+-------+------+------+------+---------+-------------+--------+---------------+
4 rows in set (0.00 sec)

更新

文件:PositionalDataNGS_2.csv

Data,Time,X,Y,D,V,OnFeild,GameID,TeamID,PlayerID_GSIS
2015-11-15,17:56:45.300,93.32,8.6,4.57,0.45,true,2015111500,5110,22-0026189
2015-11-15,17:56:45.400,93.77,8.48,4.55,0.47,true,2015111500,5110,02-0022289
2015-11-15,17:56:45.500,94.23,8.36,4.53,0.47,true,2015111500,5110,03-0026179
2015-11-15,17:56:45.600,94.67,8.23,4.51,0.46,true,2015111500,5110,30-0026180

MySQL:

mysql> TRUNCATE TABLE `PositionalDataNGS`;
Query OK, 0 rows affected (0.00 sec)

mysql> LOAD DATA INFILE '/path/to/file/PositionalDataNGS_2.csv'
    ->   INTO TABLE `PositionalDataNGS`
    ->   COLUMNS TERMINATED BY ','
    ->   LINES TERMINATED BY '\n'
    ->   IGNORE 1 LINES;
Query OK, 4 rows affected, 4 warnings (0.01 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 4

mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------------------------+
| Level   | Code | Message                                                       |
+---------+------+---------------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'true' for column 'OnField' at row 1 |
| Warning | 1366 | Incorrect integer value: 'true' for column 'OnField' at row 2 |
| Warning | 1366 | Incorrect integer value: 'true' for column 'OnField' at row 3 |
| Warning | 1366 | Incorrect integer value: 'true' for column 'OnField' at row 4 |
+---------+------+---------------------------------------------------------------+

mysql> SELECT
    ->   `Date`,
    ->   `Time`,
    ->   `X`,
    ->   `Y`,
    ->   `D`,
    ->   `V`,
    ->   `OnField`,
    ->   `GameID_GSIS`,
    ->   `TeamID`,
    ->   `PlayerID_GSIS`
    -> FROM
    ->   `PositionalDataNGS`;
+------------+--------------+-------+------+------+------+---------+-------------+--------+---------------+
| Date       | Time         | X     | Y    | D    | V    | OnField | GameID_GSIS | TeamID | PlayerID_GSIS |
+------------+--------------+-------+------+------+------+---------+-------------+--------+---------------+
| 2015-11-15 | 17:56:45.300 | 93.32 |  8.6 | 4.57 | 0.45 |       0 |  2015111500 |   5110 | 22-0026189    |
| 2015-11-15 | 17:56:45.400 | 93.77 | 8.48 | 4.55 | 0.47 |       0 |  2015111500 |   5110 | 02-0022289    |
| 2015-11-15 | 17:56:45.500 | 94.23 | 8.36 | 4.53 | 0.47 |       0 |  2015111500 |   5110 | 03-0026179    |
| 2015-11-15 | 17:56:45.600 | 94.67 | 8.23 | 4.51 | 0.46 |       0 |  2015111500 |   5110 | 30-0026180    |
+------------+--------------+-------+------+------+------+---------+-------------+--------+---------------+
4 rows in set (0.00 sec)