MySQL将列数据类型从VARCHAR转换为INT [英] MySQL convert column datatype from VARCHAR to INT
本文介绍了MySQL将列数据类型从VARCHAR转换为INT的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个包含VARCHAR(25)列的MySQL表,我想将其转换为INT.该列中的所有数据都是整数或空白.我已经尝试过以下命令:
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:
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
感谢您的帮助.
推荐答案
在更改表之前,请尝试更新您的值.
before altering your table, try to update your values.
目标是在您具有空值(无法将其转换为int)的字段中设置"0"值
The goal is to set a '0' value in the fields where you have empty values (which can't be converted to int)
update ip
set isp = '0' where trim(coalesce(isp, '')) = '';
如果isp不可为空,则可以删除合并函数.
If isp was not nullable, you can remove the coalesce function.
update ip
set isp = '0' where trim(isp) = '';
这篇关于MySQL将列数据类型从VARCHAR转换为INT的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文