mysql 错误 1406 [英] mysql error 1406

问题描述

我已经创建了一个数据库 &我正在尝试从电子表格 csv 文件中加载数据.里面还没有数据.当我跑

I have created a database & am trying to load data from a spreadsheet csv file. There is no data in it yet. When I run

LOAD DATA INFILE 'docs.csv' INTO list FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' (vendor, title, project, description, shelf); 

我收到一条消息错误 1406 (22001):第 1 行‘供应商’列的数据太长".但是，第 1 行的供应商条目长度为 6 个字符.我按如下方式创建了我的表:

I get a message "ERROR 1406 (22001): Data too long for column 'vendor' at row 1". The vendor entry at row 1 is 6 characters long though. I created my table as follows:

CREATE TABLE list (
    autonumber  SERIAL,
    vendor      varchar(50),
    title       varchar(100),
    project     varchar(100),
    description     text,
    shelf       smallint UNSIGNED,
PRIMARY KEY(autonumber));

有逗号 &在描述列的许多条目中回车(电子表格中的 Alt + Enter)；我是否正确使用 \t 作为 FIELDS TERMINATED 命令&回车会不会有麻烦?

There are commas & carriage returns (Alt + Enter in the spreadsheet)in many entries in the description column; am I right in using \t for the FIELDS TERMINATED command & will the carriage returns cause trouble?

推荐答案

可能你的csv文件编码和mysql字符集不一样，这也会导致ERROR 1406 (22001): Data too很长第 1 行的供应商"列.

maybe your csv file encoding is not same as mysql character set, this will also lead to "ERROR 1406 (22001): Data too long for column 'vendor' at row 1".

例如，如果你的mysql字符集是GBK，而你的csv文件是UTF-8编码的，那么加载时也会出现同样的错误.

for example, if you mysql character set is GBK, while you csv file is encoded in UTF-8, then it will lead to the same error when load.

只需将文件编码更改为与 mysql 字符集相同.

just change you file encoding to the same as mysql character set.

这篇关于mysql 错误 1406的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！