mysql：对于alter table modify列上的列，数据太长

I am changing a column from text to varchar column.

我正在将列从text更改为varchar列。

+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| ID           | bigint(19)   | NO   | PRI | NULL    |       |
| STATUS           | varchar(120) | YES  |     | NULL    |       |
| PRIORITY         | varchar(120) | YES  |     | NULL    |       |
| DESCRIPTION      | text         | YES  |     | NULL    |       |

when i execute the below query,

当我执行以下查询时,

alter table StatInfo modify column DESCRIPTION varchar(255) NULL;

It says ERROR 1406 (22001): Data too long for column 'DESCRIPTION' at row 7

它表示ERROR 1406(22001):第7行的“描述”列的数据太长

It doesn't truncates the value in the column and alters the table why?. where as in older version it works.

它不会截断列中的值并改变表格的原因?它在旧版本中的作用。

2 个解决方案

#1

May be you need to check the sql mode, if it strict then it will show this error

可能是你需要检查sql模式,如果严格则会显示此错误

When you change a data type using CHANGE or MODIFY, MySQL tries to convert existing column values to the new type as well as possible.

当您使用CHANGE或MODIFY更改数据类型时,MySQL会尝试尽可能将现有列值转换为新类型。

Warning This conversion may result in alteration of data. For example, if you shorten a string column, values may be truncated. To prevent the operation from succeeding if conversions to the new data type would result in loss of data, enable strict SQL mode before using ALTER TABLE (see Section 5.1.6, “Server SQL Modes”).

警告此转换可能会导致数据更改。例如,如果缩短字符串列,则可能会截断值。要防止操作成功,如果转换为新数据类型会导致数据丢失,请在使用ALTER TABLE之前启用严格SQL模式(请参见第5.1.6节“服务器SQL模式”)。

http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

#2

I suggest you to
1) Copy that table on a temporary table (through an insert-select)
2) Alter the original table
3) Restore the values in the original table using the same procedure described in 1)

我建议你1)将该表复制到临时表(通过insert-select)2)更改原始表3)使用1)中描述的相同过程恢复原始表中的值

#1