文章目录

实际需求

有些行政区域的字段 area_fullname 是空的，如何补全呢？如下所示：

分析思路

（一）如何取到每个区域的上级名称和上上级名称？

区域编码有规律，末尾有2个0的是上级，末尾有3个0 的是上上级，从而可以通过截取字符串、拼接字符串、关联子查询来得到。

（二）用到哪些技术点？

截取字符串函数拼接字符串函数空值转换函数关联子查询

上级：(ifnull((select b.area_name from td_area_test b where b.area_code = concat(left(a.area_code,4),'00')),'')) superior上上级：(ifnull((select c.area_name from td_area_test c where c.area_code = concat(left(a.area_code,3),'000')),'')) superlative本级：a.area_name inferior

把 area_fullname 为空的区域的名称，上级名称，上上级名称查询出来，语句如下：

select a.area_code,a.area_name as inferior,(ifnull((select b.area_name from td_area_test b where b.area_code = concat(left(a.area_code,4),'00')),'')) superior,(ifnull((select c.area_name from td_area_test c where c.area_code = concat(left(a.area_code,3),'000')),'')) superlative from td_area_test a where a.area_fullname ='';

写拼接 SQL 脚本的脚本语句

批量生成 SQL 脚本语句的脚本语句如下：

select concat('update td_area_test set area_fullname = ''',(concat((ifnull((select c.area_name from td_area_test cwhere c.area_code = concat(left(a.area_code,3),'000')),'')),(ifnull((select b.area_name from td_area_test b whereb.area_code = concat(left(a.area_code,4),'00')),'')),a.area_name)),''' where area_code = ''',a.area_code,''';')sqlsentence from td_area_test a where a.area_fullname = '';

执行得到脚本语句

执行上述脚本语句后得到如下结果：

mysql> select concat('update td_area_test set area_fullname = ''',(concat((ifnull((select c.area_name from td_area_test c where c.area_code = concat(left(a.area_code,3),'000')),'')),(ifnull((select b.area_name from td_area_test b where b.area_code = concat(left(a.area_code,4),'00')),'')),a.area_name)),''' where area_code = ''',a.area_code,''';') sqlsentence from td_area_test a where a.area_fullname = '';+---------------------------------------------------------------------------------------------------------------------------------------------------------+| sqlsentence |+---------------------------------------------------------------------------------------------------------------------------------------------------------+| update td_area_test set area_fullname = '内蒙古自治区赤峰市敖汉旗' where area_code = '150430'; || update td_area_test set area_fullname = '锡林郭勒盟正蓝旗' where area_code = '152530';|| update td_area_test set area_fullname = '黑龙江省哈尔滨市香坊区' where area_code = '230110';|| update td_area_test set area_fullname = '黑龙江省齐齐哈尔市克东县' where area_code = '230230'; || update td_area_test set area_fullname = '黑龙江省伊春市五营区' where area_code = '230710'; || update td_area_test set area_fullname = '上海市杨浦区' where area_code = '310110';|| update td_area_test set area_fullname = '上海市奉贤区' where area_code = '310120';|| update td_area_test set area_fullname = '上海市崇明县' where area_code = '310230';|| update td_area_test set area_fullname = '江苏省淮安市盱眙县' where area_code = '320830'; || update td_area_test set area_fullname = '浙江省杭州市余杭区' where area_code = '330110'; || update td_area_test set area_fullname = '罗津' where area_code = '342201'; || update td_area_test set area_fullname = '福建省三明市建宁县' where area_code = '350430'; || update td_area_test set area_fullname = '江西省九江市彭泽县' where area_code = '360430'; || update td_area_test set area_fullname = '江西省赣州市宁都县' where area_code = '360730'; || update td_area_test set area_fullname = '江西省吉安市永新县' where area_code = '360830'; || update td_area_test set area_fullname = '抚州市抚州市广昌县' where area_code = '361030'; || update td_area_test set area_fullname = '抚州市上饶市婺源县' where area_code = '361130'; || update td_area_test set area_fullname = '山东省济宁市汶上县' where area_code = '370830'; || update td_area_test set area_fullname = '许昌市南阳市桐柏县' where area_code = '411330'; || update td_area_test set area_fullname = '湖南省长沙市安居区' where area_code = '430185'; || update td_area_test set area_fullname = '湖南省株洲市芦松区' where area_code = '430282'; || update td_area_test set area_fullname = '湖南省常德市贺家山原种场' where area_code = '430782'; || update td_area_test set area_fullname = '湖南省常德市德山开发区' where area_code = '430783';|| update td_area_test set area_fullname = '湖南省常德市西湖管理区' where area_code = '430784';|| update td_area_test set area_fullname = '湖南省常德市西洞庭管理区' where area_code = '430785'; || update td_area_test set area_fullname = '湖南省益阳市大通湖区' where area_code = '430940'; || update td_area_test set area_fullname = '郴州市怀化通道侗族自治县' where area_code = '431230'; || update td_area_test set area_fullname = '郴州市怀化洪江区' where area_code = '431282';|| update td_area_test set area_fullname = '湘西土家族苗族自治州龙山县' where area_code = '433130';|| update td_area_test set area_fullname = '广西壮族自治区桂林市平乐县' where area_code = '450330';|| update td_area_test set area_fullname = '百色市百色市西林县' where area_code = '451030'; || update td_area_test set area_fullname = '省直辖县级行政区划省直辖县级行政区划琼中黎族苗族自治县' where area_code = '469030'; || update td_area_test set area_fullname = '重庆市万盛区' where area_code = '500110';|| update td_area_test set area_fullname = '重庆市丰都县' where area_code = '500230';|| update td_area_test set area_fullname = '重庆市石柱土家族自治县' where area_code = '500240';|| update td_area_test set area_fullname = '阿坝藏族羌族自治州壤塘县' where area_code = '513230'; || update td_area_test set area_fullname = '甘孜藏族自治州德格县' where area_code = '513330'; || update td_area_test set area_fullname = '凉山彝族自治州金阳县' where area_code = '513430'; || update td_area_test set area_fullname = '贵州省遵义市习水县' where area_code = '520330'; || update td_area_test set area_fullname = '铜仁地区万山特区' where area_code = '522230';|| update td_area_test set area_fullname = '黔东南苗族侗族自治州台江县' where area_code = '522630';|| update td_area_test set area_fullname = '黔南布依族苗族自治州龙里县' where area_code = '522730';|| update td_area_test set area_fullname = '云南省昭通市水富县' where area_code = '530630'; || update td_area_test set area_fullname = '红河哈尼族彝族自治州金平苗族瑶族傣族自治县' where area_code = '532530';|| update td_area_test set area_fullname = '大理白族自治州洱源县' where area_code = '532930'; || update td_area_test set area_fullname = '日喀则地区仁布县' where area_code = '542330';|| update td_area_test set area_fullname = '那曲地区尼玛县' where area_code = '542430'; || update td_area_test set area_fullname = '陕西省宝鸡市凤县' where area_code = '610330';|| update td_area_test set area_fullname = '陕西省咸阳市淳化县' where area_code = '610430'; || update td_area_test set area_fullname = '陕西省延安市宜川县' where area_code = '610630'; || update td_area_test set area_fullname = '陕西省汉中市佛坪县' where area_code = '610730'; || update td_area_test set area_fullname = '陕西省榆林市清涧县' where area_code = '610830'; || update td_area_test set area_fullname = '克孜勒苏柯尔克孜自治州喀什地区巴楚县' where area_code = '653130';|+---------------------------------------------------------------------------------------------------------------------------------------------------------+53 rows in set (0.01 sec)

保存成 SQL 脚本文件

上述生成的SQL 脚本语句直接从命令行复制粘贴到脚本文件中，要注意清除掉多余的符号|，然后在命令终端执行脚本文件即可，清理后如下：

update td_area_test set area_fullname = '内蒙古自治区赤峰市敖汉旗' where area_code = '150430'; update td_area_test set area_fullname = '锡林郭勒盟正蓝旗' where area_code = '152530';update td_area_test set area_fullname = '黑龙江省哈尔滨市香坊区' where area_code = '230110'; update td_area_test set area_fullname = '黑龙江省齐齐哈尔市克东县' where area_code = '230230'; update td_area_test set area_fullname = '黑龙江省伊春市五营区' where area_code = '230710'; update td_area_test set area_fullname = '上海市杨浦区' where area_code = '310110';update td_area_test set area_fullname = '上海市奉贤区' where area_code = '310120';update td_area_test set area_fullname = '上海市崇明县' where area_code = '310230';update td_area_test set area_fullname = '江苏省淮安市盱眙县' where area_code = '320830'; update td_area_test set area_fullname = '浙江省杭州市余杭区' where area_code = '330110'; update td_area_test set area_fullname = '罗津' where area_code = '342201'; update td_area_test set area_fullname = '福建省三明市建宁县' where area_code = '350430'; update td_area_test set area_fullname = '江西省九江市彭泽县' where area_code = '360430'; update td_area_test set area_fullname = '江西省赣州市宁都县' where area_code = '360730'; update td_area_test set area_fullname = '江西省吉安市永新县' where area_code = '360830'; update td_area_test set area_fullname = '抚州市抚州市广昌县' where area_code = '361030'; update td_area_test set area_fullname = '抚州市上饶市婺源县' where area_code = '361130'; update td_area_test set area_fullname = '山东省济宁市汶上县' where area_code = '370830'; update td_area_test set area_fullname = '许昌市南阳市桐柏县' where area_code = '411330'; update td_area_test set area_fullname = '湖南省长沙市安居区' where area_code = '430185'; update td_area_test set area_fullname = '湖南省株洲市芦松区' where area_code = '430282'; update td_area_test set area_fullname = '湖南省常德市贺家山原种场' where area_code = '430782'; update td_area_test set area_fullname = '湖南省常德市德山开发区' where area_code = '430783';update td_area_test set area_fullname = '湖南省常德市西湖管理区' where area_code = '430784';update td_area_test set area_fullname = '湖南省常德市西洞庭管理区' where area_code = '430785'; update td_area_test set area_fullname = '湖南省益阳市大通湖区' where area_code = '430940'; update td_area_test set area_fullname = '郴州市怀化通道侗族自治县' where area_code = '431230'; update td_area_test set area_fullname = '郴州市怀化洪江区' where area_code = '431282';update td_area_test set area_fullname = '湘西土家族苗族自治州龙山县' where area_code = '433130';update td_area_test set area_fullname = '广西壮族自治区桂林市平乐县' where area_code = '450330';update td_area_test set area_fullname = '百色市百色市西林县' where area_code = '451030'; update td_area_test set area_fullname = '省直辖县级行政区划省直辖县级行政区划琼中黎族苗族自治县' where area_code = '469030'; update td_area_test set area_fullname = '重庆市万盛区' where area_code = '500110';update td_area_test set area_fullname = '重庆市丰都县' where area_code = '500230';update td_area_test set area_fullname = '重庆市石柱土家族自治县' where area_code = '500240';update td_area_test set area_fullname = '阿坝藏族羌族自治州壤塘县' where area_code = '513230'; update td_area_test set area_fullname = '甘孜藏族自治州德格县' where area_code = '513330'; update td_area_test set area_fullname = '凉山彝族自治州金阳县' where area_code = '513430'; update td_area_test set area_fullname = '贵州省遵义市习水县' where area_code = '520330'; update td_area_test set area_fullname = '铜仁地区万山特区' where area_code = '522230';update td_area_test set area_fullname = '黔东南苗族侗族自治州台江县' where area_code = '522630';update td_area_test set area_fullname = '黔南布依族苗族自治州龙里县' where area_code = '522730';update td_area_test set area_fullname = '云南省昭通市水富县' where area_code = '530630'; update td_area_test set area_fullname = '红河哈尼族彝族自治州金平苗族瑶族傣族自治县' where area_code = '532530';update td_area_test set area_fullname = '大理白族自治州洱源县' where area_code = '532930'; update td_area_test set area_fullname = '日喀则地区仁布县' where area_code = '542330';update td_area_test set area_fullname = '那曲地区尼玛县' where area_code = '542430'; update td_area_test set area_fullname = '陕西省宝鸡市凤县' where area_code = '610330';update td_area_test set area_fullname = '陕西省咸阳市淳化县' where area_code = '610430'; update td_area_test set area_fullname = '陕西省延安市宜川县' where area_code = '610630'; update td_area_test set area_fullname = '陕西省汉中市佛坪县' where area_code = '610730'; update td_area_test set area_fullname = '陕西省榆林市清涧县' where area_code = '610830'; update td_area_test set area_fullname = '克孜勒苏柯尔克孜自治州喀什地区巴楚县' where area_code = '653130';

如果觉得《MySQL 批量生成 SQL 脚本语句解决实际的业务需求/如何拼接字符串/拼接字符串的 SQL 语句》对你有帮助，请点赞、收藏，并留下你的观点哦！