MySQL将列数据类型从VARCHAR转换为INT [英] MySQL convert column datatype from VARCHAR to 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屋！