错误代码：1406。列的数据太长 - MySQL

Error Code: 1406. Data too long for column - MySQL

提问by vikas

Error Code: 1406. Data too long for column

错误代码：1406。列的数据太长

CREATE  TABLE `TEST` 
(

  `idTEST` INT NOT NULL ,

  `TESTcol` VARCHAR(45) NULL ,

  PRIMARY KEY (`idTEST`) 
);

Now Insertsome values

现在Insert一些值

INSERT INTO TEST
VALUES
(
    1,
    'Vikas'
)

select 

SELECT * FROM TEST;

Inserting record more than the length

插入记录超过 length

INSERT INTO TEST
VALUES
(
    2,
    'Vikas Kumar Gupta Kratika Shukla Kritika Shukla'
)

If we selectthe length

如果我们select将length

SELECT LENGTH('Vikas Kumar Gupta Kratika Shukla Kritika Shukla')

 '47'

And it is showing the error message

它显示错误消息

Error Code: 1406. Data too long for column

错误代码：1406。列的数据太长

But what is my expectation is, I want to insert at least first 45 characters in Table

但我的期望是，我想在表中插入至少前 45 个字符

please let me know if the question is not clear.

如果问题不清楚，请告诉我。

I know the cause of this error. I am trying to insert values more than the length of datatype.

我知道这个错误的原因。我试图插入超过数据类型长度的值。

I want solution in MySQL as It is possible in MS SQL. So I hope it would also be in MySQL.

我想要在 MySQL 中的解决方案，因为它可以在MS SQL. 所以我希望它也能在MySQL 中。

回答by echo_Me

MySQL will truncate any insert value that exceeds the specified column width.

MySQL 将截断任何超过指定列宽的插入值。

to make this without error try switch your SQL modeto not use STRICT.

要做到这一点没有错误，请尝试切换SQL mode到不使用STRICT.

Mysql reference manual

Mysql参考手册

---

---

EDIT:

编辑：

To change the mode

改变模式

This can be done in two ways:

这可以通过两种方式完成：

1. Open your my.ini(Windows) or my.cnf(Unix) file within the MySQL installation directory, and look for the text "sql-mode".

1. 在 MySQL 安装目录中打开my.ini(Windows) 或my.cnf(Unix) 文件，并查找文本“sql-mode”。

Find:

找：

Code:

代码：

# Set the SQL mode to strict 
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Replace with:

用。。。来代替：

Code:

代码：

# Set the SQL mode to strict 
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Or

或者

1. You can run an SQL query within your database management tool, such as phpMyAdmin:

2. 您可以在数据库管理工具中运行 SQL 查询，例如 phpMyAdmin：

Code:

代码：

SET @@global.sql_mode= '';

回答by Hirurg103

I think that switching off the STRICT mode is not a good option because the app can start losing the data entered by users.

我认为关闭 STRICT 模式不是一个好的选择，因为应用程序可能会开始丢失用户输入的数据。

If you receive values for the TESTcol from an app you could add model validation, like in Rails

如果您从应用程序收到 TESTcol 的值，您可以添加模型验证，例如在 Rails 中

validates :TESTcol, length: { maximum: 45 }

If you manipulate with values in SQL script you could truncate the string with the SUBSTRINGcommand

如果您使用 SQL 脚本中的值进行操作，则可以使用SUBSTRING命令截断字符串

INSERT INTO TEST
VALUES
(
    1,
    SUBSTRING('Vikas Kumar Gupta Kratika Shukla Kritika Shukla', 0, 45)
);

回答by Joe

This is a step I use with ubuntu. It will allow you to insert more than 45 characters from your input but MySQL will cut your text to 45 characters to insert into the database.

这是我在 ubuntu 中使用的一个步骤。它允许您从输入中插入超过 45 个字符，但 MySQL 会将您的文本剪切为 45 个字符以插入到数据库中。

1. Run command

   sudo nano /etc/mysql/my.cnf

2. Then paste this code

   [mysqld] sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

3. restart MySQL

   sudo service mysql restart;

1. 运行命令

   须藤纳米 /etc/mysql/my.cnf

2. 然后粘贴这段代码

   [mysqld] sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

3. 重启 MySQL

   须藤服务 mysql 重启；

回答by Dian Yudha Negara

This happened to me recently. I was fully migrate to MySQL 5.7, and everything is in default configuration.

这最近发生在我身上。我完全迁移到 MySQL 5.7，一切都在默认配置中。

All previously answers are already clear and I just want to add something.

以前的所有答案都已经很清楚了，我只想补充一些东西。

This 1406 error could happen in your function / procedure too and not only to your table's column length.

这个 1406 错误也可能发生在你的函数/过程中，而不仅仅是你的表的列长度。

In my case, I've trigger which call procedure with IN parameter varchar(16) but received 32 length value.

就我而言，我已经使用 IN 参数 varchar(16) 触发了哪个调用过程，但收到了 32 个长度值。

I hope this help someone with similar problem.

我希望这可以帮助有类似问题的人。

回答by Terranology

Try to check the limits of your SQL database. Maybe you'r exceeding the field limit for this row.

尝试检查 SQL 数据库的限制。也许您超出了这一行的字段限制。

回答by user3713612

Go to your Models and check, because you might have truncated a number of words for that particular column eg. max_length="150".

转到您的模型并检查，因为您可能已经为该特定列截断了许多单词，例如。max_length="150"。