MySQL alter table modify列在具有空值的行中失败

I have a table with about 10K rows, which I am trying to alter so that the field fielddelimiter is never null. I am attempting to do an alter statement, expecting any null values to be changed to the default value, but I get an error back from the sql statement.

我有一个大约10K行的表，我试图改变它，以便字段fielddelimiter永远不为空。我试图做一个alter语句，期望将任何空值更改为默认值，但是我从sql语句中得到一个错误。

alter table merchant_ftp_account modify column `fielddelimiter` char(1) NOT NULL DEFAULT 't';

17:08:48  [ALTER - 0 row(s), 0.000 secs]  [Error Code: 1265, SQL State: 01000]  Data truncated for column 'fielddelimiter' at row 3987
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec  [0 successful, 0 warnings, 1 errors]

As I understand it this means that the data exceeds the field size at this row, but (a) the data in the field is (null) at that row, and (b) I am able to update that row directly with the value 't', and I don't get a truncation error. If I update that row with a nonnull value and try to re-run the alter statement, it fails at the next row where fielddelimiter is null. [ETA: I get that MySQL could update in any direction, but I can actually track its progress as I change rows.]

据我所知，这意味着数据超过了该行的字段大小，但是（a）该行中的数据是（null），并且（b）我能够直接用值'更新该行' t'，我没有得到截断错误。如果我使用非空值更新该行并尝试重新运行alter语句，则会在fielddelimiter为null的下一行失败。 [ETA：我得知MySQL可以向任何方向更新，但我可以在更改行时实际跟踪其进度。

There's a warning in the MySQL docs:

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

But the values that it's supposedly truncating are nulls. Can anybody explain to me what is going on here? And how to resolve it?

但它被认为是截断的值是空值。任何人都可以向我解释这里发生了什么吗？以及如何解决？

[ETA: The existing fielddelimiter field definition is char(1) (allows nulls, no default value), so it should not have values > 1 char, and a select confirms that it does not. The distinct values in the field are NULL, '' (empty string), 'p', 't', and 'y'.]

[ETA：现有的fielddelimiter字段定义是char（1）（允许空值，没有默认值），因此它不应该具有> 1 char的值，并且select确认它不是。字段中的不同值为NULL，''（空字符串），'p'，'t'和'y'。]

3 个解决方案

#1

I have just encountered this error, and it seems the solution was to use the IGNORE statement:

我刚刚遇到这个错误，似乎解决方案是使用IGNORE语句：

ALTER IGNORE TABLE `table` CHANGE COLUMN `col` `col` int(11) NOT NULL;

Note that you may still have data truncation issues, so be sure this is the desired result. Using the IGNORE statement it will suppress the data truncated errors for NULL values in columns (and possibly other errors!!!)

请注意，您可能仍然存在数据截断问题，因此请确保这是所需的结果。使用IGNORE语句，它将抑制列中NULL值的数据截断错误（以及可能的其他错误!!!）

#2

If your column has NULL values, you can't alter it to be "NON NULL". Change the NULL values first to something else, then try it.

如果列具有NULL值，则不能将其更改为“NON NULL”。首先将NULL值更改为其他值，然后尝试它。

#3

First remove any null values

首先删除任何空值

UPDATE merchant_ftp_account SET fielddelimiter='t' WHERE fielddelimiter IS NULL;

Then

然后

ALTER TABLE merchant_ftp_account MODIFY COLUMN `fielddelimiter` char(1) NOT NULL DEFAULT 't';

#1