MySQL Alter Table修改列在具有空值的行上失败 [英] MySQL alter table modify column failing at rows with null values

问题描述

我有一个约有1万行的表，我试图对其进行更改，以使字段fielddelimiter永远不会为空.我正在尝试执行一条alter语句，希望将任何null值都更改为默认值，但是从sql语句返回一个错误.

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.

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]

据我了解，这意味着数据超出了该行的字段大小，但是(a)字段中的数据在该行中为(null)，并且(b)我可以直接使用值"t"，并且不会出现截断错误.如果我用非空值更新该行并尝试重新运行alter语句，则它在fielddelimiter为null的下一行失败. [ETA:我知道MySQL 可以向任何方向更新，但实际上我可以在更改行时跟踪其进度.]

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

MySQL文档中有一个警告:

There's a warning in the MySQL docs:

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:现有的fielddelimiter字段定义为char(1)(允许为null，没有默认值)，因此它不应具有大于1个char的值，并且select确认它没有.字段中的不同值是NULL，''(空字符串)，"p"，"t"和"y".]

[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'.]

推荐答案

我刚遇到此错误，看来解决方法是使用IGNORE语句:

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

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

请注意，您可能仍然存在数据截断问题，因此请确保这是所需的结果.使用IGNORE语句将抑制列中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!!!)

这篇关于MySQL Alter Table修改列在具有空值的行上失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！