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

我有一个大约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]

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

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

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

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

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

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

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

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

首先删除任何空值

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

然后

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

我想补充一点，我还尝试对这个字段为空的所有行进行更新，并且查询需要永远（到目前为止，我已经停止它在10和15分钟后运行）。我的更新是： update merchant_ftp_account set fielddelimiter = 't' where fielddelimiter IS NULL; - barclay

你的领域是什么类型的？如果你的类型可以保存更大的值，我想，mysql认为列中存在更大的值。 - Ravi Bhatt

如果您的更新速度很慢，请在列上创建索引，然后尝试更新stmt。 - Ravi Bhatt

@Ravi：索引有所改善抬头时间。你不能神奇地使写入更快。事实上，如果有任何建议会让事情变得更慢，因为索引必须重建。索引是一个应该精确使用的工具，而不是盲目和直接地抛出你遇到的任何问题，模糊的希望它们会神奇地“加速我的查询” - Lightness Races in Orbit

建立10k行的索引不应该永远！因为更新stmt正在使用WHERE子句..所涉及的查找不是吗？ - Ravi Bhatt

这可能会解决部分问题，但我不能在生产数据集上这样做...（我刚刚在我正在处理的数据库中遇到原始海报的问题，我有大约10k行，但没有减速做更新）

ALTER IGNORE绝对不推荐！ - SystemParadox

你可以引用这方面的文件吗？ - Lightness Races in Orbit

那不是问题。如果指定默认值（如OP所做的那样），则NULL值会自动更新为此默认值。 - GolezTrol

好的，我的坏，@ Golez是对的，这不是问题。仅供参考我尝试此操作时，NULL值设置为空字符串，而不是新的默认值。但是，如果有一个旧的默认值，也许它会有所不同。 - David Winant

+1这似乎是我的MySQL设置的正确答案（我认为这是服务器的默认设置）我只是通过在调用alter table表达式之前设置默认值来设法修复“损坏的”数据库。

@GolezTrol我既未找到关于此的文档参考，但实际上就是这样。 - TechNyquist

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

答案:

热门问题