mysql |修改表格数据类型

问题描述

之前将excel内容导入到mysql数据库，之前的excel列内容为string文本，存到数据库数据类型为varchar 50；现在因为业务需要将string文本对应的内容通过枚举转换为数字int。

原string	Value
xxx	0
包含yyy的内容	1
空值	2

过程描述

使用update转换

首先使用update将对应文字转换为数字，在这次的场景中有三种文本类型，首先更新前两种，一种为空，一种为xxx 。

SET SQL_SAFE_UPDATES = 0;
UPDATE contract_production_records
SET settlement_status = CASE 
               WHEN settlement_status = 'xxx' THEN 0
                WHEN settlement_status= '' THEN 2
                ELSE settlement_status  -- 保持其他值不变
              END;

接下来更新第三种，其中第三种包含了某几个字眼，比如yyy，需要用到like，代码如下：

UPDATE contract_production_records
SET settlement_status = 1
WHERE settlement_status LIKE '%yyy%' 

设置完成后记得设置:

SET SQL_SAFE_UPDATES = 1;

转换数据类型

最开始使用了如下代码，但是数据中居然有含空格的值，因此无法转为int类型。
注意：以后从excel导入sql时记得去除空格再读取数据。

SET SQL_SAFE_UPDATES = 0;
UPDATE contract_production_records
SET settlement_status = CAST(settlement_status AS UNSIGNED);                   --（会出错) “ ”无法转换为数字

前面基本已经将有文本和空的地方都转换成了数字，然后检查是否真的还有空格数据，然后确实出现几条：

SELECT settlement_status
FROM contract_production_records
WHERE settlement_status NOT REGEXP '^[0-9]+$'; --确实有“ ”中间有个空格

那么接下来就是处理空格数据：

UPDATE contract_production_records
SET settlement_status = 2
WHERE settlement_status =' ' --中间是有个空格的

接下来就直接修改列的数据类型：

ALTER TABLE contract_production_records
MODIFY COLUMN settlement_status INT; --（可以用，而且是永久改变）

中间出了点小插曲，使用CAST只是临时改变列的数据类型：

SET SQL_SAFE_UPDATES = 0;
UPDATE contract_production_records
SET settlement_status = CAST(settlement_status AS UNSIGNED);                   --临时改变

总结

将文本枚举为数字，当然也可以转换为其他类型：

SET SQL_SAFE_UPDATES = 0;
UPDATE table
SET column = CASE 
               WHEN column = 'xxx' THEN 0
                WHEN column= 'yyy' THEN 2
                ELSE settlement_status  -- 保持其他值不变
              END;

永久改变列的数据类型：

ALTER TABLE xx_table
MODIFY COLUMN yy_column data_type; --（可以用，而且是永久改变）