由于需求变更,导致代码逻辑变化,造成数据库中的code_字段数据格式混乱。考虑到表中数据量较大,所以选择写sql进行update数据处理。
涉及字段注释:
标准数据格式如下:
楼-单元-层-号
老数据如下:
缺少 ‘单元’ ‘层’ ‘号’ 中文
只有‘房号’,没有拼接完整
多了‘--’,要求把‘--’后有一位数字的,替换为0;‘--’后有两位数字的去掉‘--’
好特别的房号。。。
用到的mysql函数:
replace()
length()
substring_index()
concat()
实现思路:
先把图四处理成图二,再把图二处理成图三,最后把图三处理成图一
图五的房号虽然奇怪,但并不难处理,可直接处理成图一
实现过程:
就按上面写的思路一步步来
首先观察图四,房号部分多了‘--’,可能是用户在录入数据时写错了,所以要通过SQL把‘--’后有一位数的,替换为0;‘--’后有两位数字的去掉‘--’
先解决‘--’后两位数的,SQL如下:
update space_house set code_ = replace(code_, '--','')
where id_ in (
select a.id_ from (
select id_,code_,length(substring_index(code_, '--', -1)) le from space_house WHERE code_ like "%--%"
) a where a.le = 2
)
最里层(黄色)先是查出了包含‘--’的数据,并使用substring_index(code_, '--', -1)截取了code_字段中最后一次出现‘--’符号之后的全部数据,通过length()函数计算出截取到的长度
中层(绿色)通过where条件过滤出length长度为2的数据id
外层通过update语句进行修改,并配合replace()函数把‘--’替换为空
处理后效果: 百合园小区7号楼-1-1-710
该处理'--'后一位数的了,SQL如下:
update space_house set code_ = replace(code_, '--','0')
where id_ in (
select a.id_ from (
select id_,code_,length(substring_index(code_, '--', -1)) le from space_house WHERE code_ like "%--%"
) a where a.le = 1
)
逻辑同上,就不解释了。效果: 百合园小区7号楼-1-1-709
上面已经把图四处理成图二了
继续按思路把图二处理成图三,把图三处理成图一,一条SQL就可以,如下:
update space_house set code_ = concat(building_name,'-',unit_,'单元-',floor_,'层-',substring_index(code_, '-', -1),'号')
where id_ in (
select b.* from (
select id_ from space_house where code_ not like '%号' and id_ not in (
select a.id_ from (
select id_,code_, length(code_)-length(replace(code_,'-','')) le from space_house
) a where a.le = 4
)
) b
)
最里层的SQL(黄色)计算出每条数据具有几个’-‘符号的数量
中间两层SQL(绿色),则是用于过滤数据结尾包含’号‘字的数据,和过滤含有四个’-‘符号的数据。因为’%号‘代表正常的数据不需要处理,而含有四个’-‘符号的数据代表图五的那批数据,需要单独处理。此时过滤后的数据,只有图二和图三形式的了
外层SQL(红色),通过concat()函数对剩余的数据重新拼接成图一形式,注意:mysql连贯字符串不能利用加号(+),而是利用concat。concat()函数中配合了substring_index()函数对房号进行了截取,注意:substring_index函数的参数二指定的值,截取不到就会就返回整个字符串。
只剩下图五了,SQL如下:
update space_house set code_ = concat(building_name,'-',unit_,'单元-',floor_,'层-',substring_index(code_, '-', -2),'号')
where id_ in (
select a.id_ from (
select id_,code_, length(code_)-length(replace(code_,'-','')) le from space_house
) a where a.le = 4
)
思路同上,不解释了。只是在substring_index函数中的参数三,截取了倒数第二位置的'-'符号