“数据对于列来说太长”——为什么？

我已经编写了一个 MySQL 脚本来创建一个虚拟医院记录的数据库，并用数据填充它。其中一个表 Department 有一个名为 Description 的列，声明为 varchar (200)类型。当执行 INSERT 命令 for Description 时，我得到一个错误:

错误1406: 第1行“ Description”列的数据太长。

我插入的所有字符串都小于150个字符。

这是宣言:

CREATE TABLE Department(
...
Description varchar(200)
...);

这是插入命令:

INSERT INTO Department VALUES
(..., 'There is some text here',...), (..., 'There is some more text over here',...);

看起来，这应该有用，有人有什么想法吗？

311072 次浏览
小开

Varchar has its own limits. Maybe try changing datatype to text.!

小开

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.

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.

Here you can find INNODB TABLES LIMITATIONS

小开

I try to create a table with a field as 200 characters and I've added two rows with early 160 characters and it's OK. Are you sure your rows are less than 200 characters?

Show SqlFiddle

小开

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).

小开

With Hibernate you can create your own UserType. So thats what I did for this issue. Something as simple as this:

    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.

小开

Change column type to LONGTEXT

小开

I had a similar problem when migrating an old database to a new version.

Switch the MySQL mode to not use STRICT.

SET @@global.sql_mode= 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

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

小开

In my case this error occurred due to entering data a wrong type for example: if it is a long type column, i tried to enter in string type. so please check your data that you are entering and type are same or not

小开

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).

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.

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.

小开

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.

小开

in mysql if you take VARCHAR then change it to TEXT bcoz its size is 65,535 and if you can already take TEXT the change it with LONGTEXT only if u need more then 65,535.

total size of LONGTEXT is 4,294,967,295 characters

小开

For me, I defined column type as BIT (e.g. "boolean")

When I tried to set column value "1" via UI (Workbench), I was getting a "Data too long for column" error.

Turns out that there is a special syntax for setting BIT values, which is:

b'1'
小开

There was a similar problem when storing a hashed password into a table. Changing the maximum column length didn't help. Everything turned out to be simple. It was necessary to delete the previously created table from the database, and then test the code with new values ​​of the allowable length.

小开

If you re using type: DataTypes.STRING, then just pass how long this string can be like DataTypes.STRING(1000)

小开

For me, I try to update column type "boolean" value When I tried to set column value 1 MySQL Workbench, I was getting a "Data too long for column" error.

So for that there is a special syntax for setting boolean values, which is:

   UPDATE `DBNAME`.`TABLE_NAME` SET `FIELD_NAME` = false WHERE (`ID` = 'ID_VALUE');   //false for 0


UPDATE `DBNAME`.`TABLE_NAME` SET `FIELD_NAME` = true WHERE (`ID` = 'ID_VALUE');  //true for 1

提交答案