MySQL 列的数据被截断？

Data truncated for column?

提问by Zagstrug

After changing the data type of a MySql column in order to store Twilio callids (34 char strings), I try to manually change the data in that column with:

在更改 MySql 列的数据类型以存储Twilio 调用ID（34 个字符字符串）后，我尝试使用以下命令手动更改该列中的数据：

update calls 
   set incoming_Cid='CA9321a83241035b4c3d3e7a4f7aa6970d' 
 where id='1';

However I get an error which doesn't make sense seeing as the column's data type was properly modified?

但是，当列的数据类型被正确修改时，我收到一个没有意义的错误？

| Level ||| Code | Message| Warning | 1265 | Data truncated for column 'incoming_Cid' at row 1

| Level ||| Code | Message| Warning | 1265 | Data truncated for column 'incoming_Cid' at row 1

采纳答案by peterm

Your problem is that at the moment your incoming_Cidcolumn defined as CHAR(1)when it should be CHAR(34).

您的问题是，目前您的incoming_Cid列定义为CHAR(1)应该是CHAR(34).

To fix this just issue this command to change your columns' length from 1 to 34

要解决此问题，只需发出此命令即可将列的长度从 1 更改为 34

ALTER TABLE calls CHANGE incoming_Cid incoming_Cid CHAR(34);

Here is SQLFiddledemo

这是SQLFiddle演示

回答by MTurPash

I had the same problem because of an table column which was defined as ENUM('x','y','z') and later on I was trying to save the value 'a' into this column, thus I got the mentioned error.

由于定义为 ENUM('x','y','z') 的表列，我遇到了同样的问题，后来我试图将值 'a' 保存到该列中，因此我得到了提到错误。

Solved by altering the table column definition and added value 'a' into the enum set.

通过更改表列定义并将值“a”添加到枚举集中解决。

回答by RandomSeed

By issuing this statement:

通过发布此声明：

ALTER TABLES call MODIFY incoming_Cid CHAR;

... you omitted the length parameter. Your query was therefore equivalent to:

...您省略了长度参数。因此，您的查询相当于：

ALTER TABLE calls MODIFY incoming_Cid CHAR(1);

You must specify the field size for sizes larger than 1:

您必须为大于 1 的大小指定字段大小：

ALTER TABLE calls MODIFY incoming_Cid CHAR(34);

回答by Gray

However I get an error which doesn't make sense seeing as the column's data type was properly modified?

| Level | Code | Msg | Warn | 12 | Data truncated for column 'incoming_Cid' at row 1

但是，当列的数据类型被正确修改时，我收到一个没有意义的错误？

| Level | Code | Msg | Warn | 12 | Data truncated for column 'incoming_Cid' at row 1

You can often get this message when you are doing something like the following:

当您执行以下操作时，通常会收到此消息：

REPLACE INTO table2 (SELECT * FROM table1);

Resulted in our case in the following error:

在我们的案例中导致以下错误：

SQL Exception: Data truncated for column 'level' at row 1

The problem turned out to be column misalignment that resulted in a tinyinttrying to be stored in a datetimefield or vice versa.

结果证明问题是列未对齐导致tinyint尝试存储在datetime字段中，反之亦然。

回答by iSWORD

In my case it was a table with an ENUM that accepts the days of the week as integers (0 to 6). When inserting the value 0 as an integer I got the error message "Data truncated for column ..." so to fix it I had to cast the integer to a string. So instead of:

在我的例子中，它是一个带有 ENUM 的表，它接受一周中的天数作为整数（0 到 6）。将值 0 作为整数插入时，我收到错误消息“列的数据被截断...”，因此要修复它，我必须将整数转换为字符串。所以而不是：

$item->day = 0;

$item->day = 0;

I had to do;

我必须做;

$item->day = (string) 0;

$item->day = (string) 0;

It looks silly to cast the zero like that but in my case it was in a Laravel factory, and I had to write it like this:

像这样投射零看起来很愚蠢，但在我的情况下它是在 Laravel 工厂中，我不得不这样写：

$factory->define(App\Schedule::class, function (Faker $faker) {
    return [
        'day' => (string) $faker->numberBetween(0, 6),
        //
    ];
});

回答by Saidjon

when i first tried to import csv into mysql , i got the same error , and then i figured out mysql table i created doesn't have the character length of the importing csv field , so if it's the first time importing csv

当我第一次尝试将 csv 导入 mysql 时，我遇到了同样的错误，然后我发现我创建的 mysql 表没有导入 csv 字段的字符长度，所以如果这是第一次导入 csv

1. its a good idea to give more character length .
2. label all fields as varcharor text, don't blend intor other values.

1. 给出更多字符长度是个好主意。
2. 将所有字段标记为varcharor text，不要混合int或其他值。

then you are good to go.

那么你就可以走了。

回答by Black

I had the same problem, with a database field with type "SET" which is an enum type.

我遇到了同样的问题，数据库字段的类型为“SET”，它是一个枚举类型。

I tried to add a value which was not in that list.

我试图添加一个不在该列表中的值。

The value I tried to add had the decimal value 256, but the enum list only had 8 values.

我尝试添加的值的十进制值为 256，但枚举列表只有 8 个值。

1: 1   -> A
2: 2   -> B
3: 4   -> C
4: 8   -> D
5: 16  -> E
6: 32  -> F
7: 64  -> G
8: 128 -> H

So I just had to add the additional value to the field.

所以我只需要向该字段添加附加值。

Reading this documentationentry helped me to understand the problem.

阅读这个文档条目帮助我理解了这个问题。

MySQL stores SET values numerically, with the low-order bit of the stored value corresponding to the first set member. If you retrieve a SET value in a numeric context, the value retrieved has bits set corresponding to the set members that make up the column value. For example, you can retrieve numeric values from a SET column like this:

mysql> SELECT set_col+0 FROM tbl_name; If a number is stored into a

If a number is stored into a SET column, the bits that are set in the binary representation of the number determine the set members in the column value. For a column specified as SET('a','b','c','d'), the members have the following decimal and binary values.

MySQL 以数字方式存储 SET 值，存储值的低位对应于第一个 set 成员。如果在数字上下文中检索 SET 值，则检索到的值具有与构成列值的集合成员相对应的位设置。例如，您可以像这样从 SET 列中检索数值：

mysql> SELECT set_col+0 FROM tbl_name; If a number is stored into a

如果数字存储在 SET 列中，则在数字的二进制表示中设置的位确定列值中的集合成员。对于指定为 SET('a','b','c','d') 的列，成员具有以下十进制和二进制值。

SET Member  Decimal Value   Binary Value
    'a'                1          0001
    'b'                2          0010
    'c'                4          0100
    'd'                8          1000

If you assign a value of 9 to this column, that is 1001 in binary, so the first and fourth SET value members 'a' and 'd' are selected and the resulting value is 'a,d'.

如果为此列分配值 9，即二进制 1001，则选择第一个和第四个 SET 值成员 'a' 和 'd'，结果值为 'a,d'。