工作问题总结之MYSQL篇

1.函数 RPAD(str, len, padstr) 的使用

最近工作当中遇到这样一个场景：系统中有张部门表sys_depart，部门表的id即其区域编码，例如：商丘----411400000000（长度为12位），但是由于某种未知原因出现了脏数据，导致某些部门的区域编码变为了小于12位，如：商丘----411400，这样在配合中间表联查的时候就会出现问题，由于411400和411400000000匹配不上，所以就查不出数据，最终出现了BUG。此时我们需要用SQL语句来批量对表中的脏数据进行更新，示例SQL语句如下：

update sys_depart set id = (case when length(id) < 12 then rpad(id,12,'0') else id end);

此处用到了mysql中的一个函数：RPAD(str, len, padstr)

返回字符串str，右补齐字符串 padstr 填补到len字符长度。如果 str 的长度大于len，返回值被缩短至 len 个字符。

其中PAD是填充的意思，R是右补齐；类似的函数还有LPAD(str, len, padstr)，为左补齐填充函数，用法同理。

2.函数any_value()的用法

地域表结构：省份和城市，城市等级

 业务要求：查询所有省份

若这样写：

SELECT 
    province_code,
    province_name
FROM t_mip_base_area
GROUP BY province_code

则报错：

 可以改为这样写：

方法一(推荐)：使用any_value()函数

SELECT 
	province_code,
	any_value(province_name)
FROM t_mip_base_area
GROUP BY province_code

方法二：group by 后面加上select 后的所有字段

SELECT 
	province_code,
	province_name
FROM t_mip_base_area
GROUP BY province_code,province_name

方法二的弊端显而易见，如果select需要查询的字段很多，就需要对所有字段进行group by操作，所以虽然可以解决问题，但坚决不推荐这样做

总结：

1.MySQL5.7之后，sql_mode中ONLY_FULL_GROUP_BY模式默认设置为打开状态。

2.ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义，简单的说来，在此模式下，target list中的值要么是来自于聚合函数（sum、avg、max等）的结果，要么是来自于group by list中的表达式的值

3.MySQL提供了any_value()函数来抑制ONLY_FULL_GROUP_BY值被拒绝

4.any_value()会选择被分到同一组的数据里第一条数据的指定列值作为返回数据