MySQL处理线上字典项数据的升级SQL——问题记录

需求：

项目上线后发现一些sao操作后导致了存字典项编码的字段有少部分数据存的字典项名字，现需要写个升级SQL将存字典项名字的数据更正为存字典项编码。（字典项很多）

开始思路：

最开始想的处理思路是，写个自定义函数（因为要传入变量，不能使用子查询所以就用自定义函数了）根据字典项值查询出对应的字段项编码，然后在where中带条件筛选出需要更正的值。写好后发现通用性不强，换个字典项筛选条件不好处理。

-- 获取民族编码
DELIMITER $$
DROP FUNCTION IF EXISTS getNationCode$$
CREATE FUNCTION getNationCode(nation_code varchar(50)) RETURNS varchar(50)
BEGIN
  DECLARE ret VARCHAR(50);
    -- 把查到的code值放在ret中
  SELECT `code` INTO ret FROM pl_dict_item WHERE dict_code = 'Nation' AND `name` = nation_code;
    -- 若没查到值就返回原来的值
	IF ret IS NULL THEN SET ret = nation_code;
	END IF;
  return ret;
END $$

-- SELECT 'aaa';
-- SELECT 语句原来后面直接跟一个值就可以，本质是输出内容的语句
-- SELECT getNationCode("族");

-- 更新字典项民族名字为编码
UPDATE b_user_detail
SET nation_code = getNationCode(nation_code)
WHERE nation_code LIKE '%族';

通用思路：

之后想到了case when搜索语句，因为字典项编码和字典项名字是一一对应的，case when语句处理一一对应的还是比较方便，但就是字典项数量比较多，有点难得写。于是又想到了用Excel协助处理，把需转换的字典项编码和字典项名字复制到Excel中，在Excel中拼接case when中的【when filed = 'dictName'  then  'dictCode'】字符串，然后复制到SQL中即可。

-- 先写一种情况，再把 WHEN nation_code = '汉族' THEN '01' 复制到Excel中拼接，最后把Excel填充的数据复制过来即可
UPDATE b_user_detail
SET nation_code = (
CASE
	WHEN nation_code = '汉族' THEN '01'
    -- …………
    ELSE
		nation_code
	END
);

通用优雅思路：

update语句中多表操作加条件修改

-- 更新时直接关联字典表带条件更新
UPDATE b_user_detail bsd, pl_dict_item pdi
SET bsd.nation_code = pdi.`code`
WHERE bsd.nation_code = pdi.`name` AND pdi.dict_code = "Nation";

总结

1. case when 处理多项一对一的数据时与Excel搭配使用效果更佳。
2. 在判断是否为 NULL时需要用  IS NULL 或者 IS NOT NULL来判断，不能用算数比较操作符。
3. select语句本质是输出内容的语句，select后面接内容就能执行。

参考：

https://dev.mysql.com/doc/refman/5.7/en/working-with-null.html【null值的解释】