MySQL “列的数据太长” - 为什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18897420/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
"Data too long for column" - why?
提问by Ben C.
I've written a MySQL script to create a database for hypothetical hospital records and populate it with data. One of the tables, Department, has a column named Description, which is declared as type varchar(200). When executing the INSERT command for Description I get an error:
我编写了一个 MySQL 脚本来为假设的医院记录创建一个数据库并用数据填充它。其中一个表Department 有一个名为Description 的列，该列声明为类型varchar(200)。执行描述的 INSERT 命令时，出现错误：
error 1406: Data too long for column 'Description' at row 1.
错误 1406：第 1 行“说明”列的数据太长。
All the strings I'm inserting are less than 150 characters.
我插入的所有字符串都少于 150 个字符。
Here's the declaration:
这是声明：
CREATE TABLE Department(
...
Description varchar(200)
...);
And here's the insertion command:
这是插入命令：
INSERT INTO Department VALUES
(..., 'There is some text here',...), (..., 'There is some more text over here',...);
By all appearances, this should be working. Anyone have some insight?
从各方面来看，这应该有效。任何人都有一些见解？
回答by Alexander Serkin
Change column type to LONGTEXT
将列类型更改为 LONGTEXT
回答by Claudionor Oliveira
I had a similar problem when migrating an old database to a new version.
将旧数据库迁移到新版本时，我遇到了类似的问题。
Switch the MySQL mode to not use STRICT.
将 MySQL 模式切换为不使用 STRICT。
SET @@global.sql_mode= 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
回答by STT LCU
There is an hard limit on how much data can be stored in a single row of a mysql table, regardless of the number of columns or the individual column length.
无论列数或单个列的长度如何，mysql 表的单行中可以存储多少数据都有硬性限制。
As stated in the OFFICIAL DOCUMENTATION
The maximum row size constrains the number (and possibly size) of columns because the total length of all columns cannot exceed this size. For example, utf8 characters require up to three bytes per character, so for a CHAR(255) CHARACTER SET utf8 column, the server must allocate 255 × 3 = 765 bytes per value. Consequently, a table cannot contain more than 65,535 / 765 = 85 such columns.
Storage for variable-length columns includes length bytes, which are assessed against the row size. For example, a VARCHAR(255) CHARACTER SET utf8 column takes two bytes to store the length of the value, so each value can take up to 767 bytes.
最大行大小限制了列的数量（可能还有大小），因为所有列的总长度不能超过此大小。例如，utf8 字符每个字符最多需要三个字节，因此对于 CHAR(255) CHARACTER SET utf8 列，服务器必须为每个值分配 255 × 3 = 765 个字节。因此，一个表不能包含超过 65,535 / 765 = 85 个这样的列。
可变长度列的存储包括长度字节，这些字节是根据行大小进行评估的。例如，VARCHAR(255) CHARACTER SET utf8 列需要两个字节来存储值的长度，因此每个值最多可以占用 767 个字节。
Here you can find INNODB TABLES LIMITATIONS
在这里你可以找到INNODB 表的限制
回答by Jayesh Amin
Varchar has its own limits. Maybe try changing datatype to text.!
Varchar 有其自身的局限性。也许尝试将数据类型更改为文本。！
回答by Ben C.
Turns out, as is often the case, it was a stupid error on my part. The way I was testing this, I wasn't rebuilding the Department table after changing the data type from varchar(50) to varchar(200); I was just re-running the insert command, still with the column as varchar(50).
事实证明，正如经常发生的那样，这是我的一个愚蠢的错误。我测试的方式是，在将数据类型从 varchar(50) 更改为 varchar(200) 后，我没有重建 Department 表；我只是重新运行插入命令，列仍然为 varchar(50)。
回答by Krishna Kumar Jangid
in mysql if you take VARCHARthen change it to TEXTbcoz its size is 65,535
and if you can already take TEXTthe change it with LONGTEXTonly if u need more then 65,535.
在 mysql 中，如果您采取，VARCHAR则将其更改为TEXTbcoz，其大小为 65,535，如果您已经可以TEXT进行更改，则LONGTEXT仅当您需要更多时65,535。
total size of LONGTEXTis 4,294,967,295characters
总大小LONGTEXT是4,294,967,295字符
回答by rahimv
Very old question, but I tried everything suggested above and still could not get it resolved.
很老的问题，但我尝试了上面建议的所有方法，但仍然无法解决。
Turns out that, I had after insert/update trigger for the main table which tracked the changes by inserting the record in history table having similar structure. I increased the size in the main table column but forgot to change the size of history table column and that created the problem.
事实证明，我在主表的插入/更新触发器之后通过在具有类似结构的历史表中插入记录来跟踪更改。我增加了主表列的大小，但忘记更改历史表列的大小，这造成了问题。
I did similar changes in the other table and error is gone.
我在另一个表中做了类似的更改，错误消失了。
回答by hugh
With Hibernate you can create your own UserType. So thats what I did for this issue. Something as simple as this:
使用 Hibernate，您可以创建自己的 UserType。所以这就是我为这个问题所做的。像这样简单的事情：
public class BytesType implements org.hibernate.usertype.UserType {
private final int[] SQL_TYPES = new int[] { java.sql.Types.VARBINARY };
//...
}
There of course is more to implement from extending your own UserType but I just wanted to throw that out there for anyone looking for other methods.
当然，通过扩展您自己的 UserType 可以实现更多功能，但我只是想把它扔给任何寻找其他方法的人。
回答by Joe Taras
回答by Buttle Butkus
If your source data is larger than your target field and you just want to cut off any extra characters, but you don't want to turn off strict mode or change the target field's size, then just cut the data down to the size you need with LEFT(field_name,size).
如果您的源数据大于目标字段，而您只想剪掉任何多余的字符，但又不想关闭严格模式或更改目标字段的大小，那么只需将数据剪到您需要的大小即可与LEFT(field_name,size).
INSERT INTO Department VALUES
(..., LEFT('There is some text here',30),...), (..., LEFT('There is some more text over here',30),...);
I used "30" as an example of your target field's size.
我使用“30”作为目标字段大小的示例。
In some of my code, it's easy to get the target field's size and do this. But if your code makes that hard, then go with one of the other answers.
在我的一些代码中，很容易获取目标字段的大小并执行此操作。但是，如果您的代码很难做到这一点，那么请选择其他答案之一。
