MySQL 批量生成 SQL 脚本语句解决实际的业务需求/如何拼接字符串/拼接字符串的 SQL 语句

实际需求

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

分析思路

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

（二）用到哪些技术点？

1. 截取字符串函数
2. 拼接字符串函数
3. 空值转换函数
4. 关联子查询

上级：(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 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 = '';

执行得到脚本语句

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

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';