MySQL 将列数据类型从 VARCHAR 转换为 INT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21784201/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL convert column datatype from VARCHAR to INT
提问by Brian G
I have a MySQL table with a VARCHAR(25) column that I want to convert to INT. All of the data in the column is either integer or blanks. I have tried this command:
我有一个带有 VARCHAR(25) 列的 MySQL 表，我想将其转换为 INT。该列中的所有数据都是整数或空白。我试过这个命令：
ALTER TABLE ip MODIFY isp INT UNSIGNED NOT NULL;
Unfortunately, since some of the existing rows are empty I get this error:
不幸的是，由于一些现有的行是空的，我收到了这个错误：
ERROR 1366 (HY000): Incorrect integer value: '' for column 'isp' at row 2
Thanks for assistance.
感谢您的帮助。
采纳答案by Rapha?l Althaus
before altering your table, try to update your values.
在更改您的表格之前，请尝试更新您的值。
The goal is to set a '0' value in the fields where you have empty values (which can't be converted to int)
目标是在具有空值（无法转换为 int）的字段中设置一个“0”值
update ip
set isp = '0' where trim(coalesce(isp, '')) = '';
If isp was not nullable, you can remove the coalesce function.
如果 isp 不可为空，则可以删除合并函数。
update ip
set isp = '0' where trim(isp) = '';
