找到你要的答案

Q：Why is my data being truncated? Warning | 1265 | Data truncated for column

Q：为什么我的数据被截断了？警告| 1265 |数据截断柱

I tried adding NULL to both date_of_birth and date_of_death, but that didn't help. Why is data being truncated? (especially because there isn't any data at the moment...)

Also, I don't understand warning 1261 at all. Row 5 is no different from rows 1-4.

Thanks!

The following script:

-- create the people table
DROP TABLE IF EXISTS people;

CREATE TABLE people (
full_name varchar (50) NOT NULL,
people_ID varchar (15) NOT NULL,
date_of_birth DATE,
date_of_death DATE,
PRIMARY KEY(people_ID))
ENGINE = INNODB;

LOAD DATA LOCAL INFILE "People.csv"
INTO TABLE people
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

Gives me these errors:

Query OK, 5 rows affected, 10 warnings (0.00 sec)
Records: 5  Deleted: 0  Skipped: 0  Warnings: 10

mysql> show warnings;
+---------+------+----------------------------------------------------+
| Level   | Code | Message                                            |
+---------+------+----------------------------------------------------+
| Warning | 1265 | Data truncated for column 'date_of_birth' at row 1 |
| Warning | 1265 | Data truncated for column 'date_of_death' at row 1 |
| Warning | 1265 | Data truncated for column 'date_of_birth' at row 2 |
| Warning | 1265 | Data truncated for column 'date_of_death' at row 2 |
| Warning | 1265 | Data truncated for column 'date_of_birth' at row 3 |
| Warning | 1265 | Data truncated for column 'date_of_death' at row 3 |
| Warning | 1265 | Data truncated for column 'date_of_birth' at row 4 |
| Warning | 1265 | Data truncated for column 'date_of_death' at row 4 |
| Warning | 1265 | Data truncated for column 'date_of_birth' at row 5 |
| Warning | 1261 | Row 5 doesn't contain data for all columns         |
+---------+------+----------------------------------------------------+
10 rows in set (0.00 sec)

mysql> select * from people;
+-------------------+-----------+---------------+---------------+
| full_name         | people_ID | date_of_birth | date_of_death |
+-------------------+-----------+---------------+---------------+
| Harry Dunham      | H_Dunham  | 0000-00-00    | NULL          |
| Julien Bryan      | J_Bryan   | 0000-00-00    | 0000-00-00    |
| Jules V.D. Bucher | J_Bucher  | 0000-00-00    | 0000-00-00    |
| Miriam Bucher     | M_Bucher  | 0000-00-00    | 0000-00-00    |
| Paul Bowles       | P_Bowles  | 0000-00-00    | 0000-00-00    |
+-------------------+-----------+---------------+---------------+
5 rows in set (0.01 sec)

Here is my .csv:

FullName,People_ID,DOB,DOD
Jules V.D. Bucher,J_Bucher,,
Miriam Bucher,M_Bucher,,
Julien Bryan,J_Bryan,,
Paul Bowles,P_Bowles,,
Harry Dunham,H_Dunham,,

我尝试添加空都date_of_birth和date_of_death，但这并没有帮助。为什么数据被截断？（特别是因为目前没有任何数据）

此外，我不明白警告1261。5行是从1-4行没有什么不同。

谢谢!

以下脚本：

-- create the people table
DROP TABLE IF EXISTS people;

CREATE TABLE people (
full_name varchar (50) NOT NULL,
people_ID varchar (15) NOT NULL,
date_of_birth DATE,
date_of_death DATE,
PRIMARY KEY(people_ID))
ENGINE = INNODB;

LOAD DATA LOCAL INFILE "People.csv"
INTO TABLE people
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

给我这些错误：

Query OK, 5 rows affected, 10 warnings (0.00 sec)
Records: 5  Deleted: 0  Skipped: 0  Warnings: 10

mysql> show warnings;
+---------+------+----------------------------------------------------+
| Level   | Code | Message                                            |
+---------+------+----------------------------------------------------+
| Warning | 1265 | Data truncated for column 'date_of_birth' at row 1 |
| Warning | 1265 | Data truncated for column 'date_of_death' at row 1 |
| Warning | 1265 | Data truncated for column 'date_of_birth' at row 2 |
| Warning | 1265 | Data truncated for column 'date_of_death' at row 2 |
| Warning | 1265 | Data truncated for column 'date_of_birth' at row 3 |
| Warning | 1265 | Data truncated for column 'date_of_death' at row 3 |
| Warning | 1265 | Data truncated for column 'date_of_birth' at row 4 |
| Warning | 1265 | Data truncated for column 'date_of_death' at row 4 |
| Warning | 1265 | Data truncated for column 'date_of_birth' at row 5 |
| Warning | 1261 | Row 5 doesn't contain data for all columns         |
+---------+------+----------------------------------------------------+
10 rows in set (0.00 sec)

mysql> select * from people;
+-------------------+-----------+---------------+---------------+
| full_name         | people_ID | date_of_birth | date_of_death |
+-------------------+-----------+---------------+---------------+
| Harry Dunham      | H_Dunham  | 0000-00-00    | NULL          |
| Julien Bryan      | J_Bryan   | 0000-00-00    | 0000-00-00    |
| Jules V.D. Bucher | J_Bucher  | 0000-00-00    | 0000-00-00    |
| Miriam Bucher     | M_Bucher  | 0000-00-00    | 0000-00-00    |
| Paul Bowles       | P_Bowles  | 0000-00-00    | 0000-00-00    |
+-------------------+-----------+---------------+---------------+
5 rows in set (0.01 sec)

这是我的。csv：

FullName,People_ID,DOB,DOD
Jules V.D. Bucher,J_Bucher,,
Miriam Bucher,M_Bucher,,
Julien Bryan,J_Bryan,,
Paul Bowles,P_Bowles,,
Harry Dunham,H_Dunham,,
answer1： 回答1：

Perhaps what's happening is that Mysql is trying to force the empty string "" into your date column, but the column only accepts DATE formatted data so it's converted into the proper format. Try adjusting your CSV so one of the rows has "0000-00-00" in the date columns instead of just "" (blank), and see if you get fewer warnings.

The 1261 warning looks like more of same -- probably harmless but annoying quirks in how Mysql handles CSV import -- and I wouldn't worry about it unless you see evidence that your imported data aren't complete.

也许所发生的事情是，MySQL是试图迫使空字符串“”为你的约会柱，但柱只接受日期格式的数据，所以它转换成适当的格式。试着调整你的CSV，一排“0000-00-00”之列而不只是“”（空白），看看你得到更少的警告。

1261警告看起来更多的是相同的--可能无害但恼人的怪癖在MySQL如何处理CSV导入--我不会担心它，除非你看到你的进口数据不完整。
answer2： 回答2：

Putting it all together, I have divined that your CSV file contains DOS-style line endings (\r\n) whereas you have instructed MySQL to interpret the file using UNIX-style line endings (\n).

Those \r characters are thus treated as data, with \r being "truncated" to nothingness during the string-to-DATE conversion, and with the final row indeed containing no data whatsoever for that final column (because you did not write an empty line at the end of the CSV file). That's how the final row is different.

You can actually fix this by using a more appropriate LINES TERMINATED BY directive:

LOAD DATA LOCAL INFILE "People.csv"
INTO TABLE people
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

MySQL will then allow the \r\n sequences to be treated as a line ending.

You have another problem, which is that you're telling MySQL that your fields are enclosed by ", whereas this is not at all the case. You could remove the ENCLOSED BY directive, or precede it with OPTIONALLY:

If the input values are not necessarily enclosed within quotation marks, use OPTIONALLY before the ENCLOSED BY keywords.

You should read the documentation for the technologies that you use.

全部放在一起，我猜到你的CSV文件包含DOS风格的行尾（\r\n）而你指示MySQL来解释使用Unix风格的行结尾的文件（n）。

那些R字符这样处理过的数据，与R“截断”日期转换字符串中的虚无，并确实不含任何最终的数据列的最后一排（因为你没有写在CSV文件最后一个空行）。这就是最后一行是如何不同。

实际上，您可以使用指令终止的更合适的行来修复此问题：

LOAD DATA LOCAL INFILE "People.csv"
INTO TABLE people
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

MySQL将允许\r\n序列被视为一个行结束。

你有了另一个问题，这是你说的，你的领域是MySQL包围”，而这并不是在所有的情况下。您可以删除所指示的指令，或在其前面任选：

如果输入值不一定包含在引号内，请在使用关键字之前随意使用。

你应该阅读你使用的技术文档。
mysql  date  null  warnings  truncated 
京ICP备15019141号-2