MySQL常用代码片段

概述

每次想要实现一个功能时，总是百度Google，挺浪费时间的，于是整理得到此文。持续更新中。

字符串截取函数

length

​​length(str)​​：返回str的长度

left

​​left(str, length)​​，根据length指定的长度取str从第一个字符开始的子串，length不能为负数。

select left('www.google.com', 3);-- www

right

​​right(str, length)​​，根据length指定的长度取str从最后一个字符开始的子串，length不能为负数。

select right('www.google.com', 3);-- com

substring

​​substring(str, pos, [len])​​：从str的第pos个字符开始，取len个字符；len如果不指定，则取第pos个字符开始的所有字符；pos如果为负数，则表示倒数。

select substring('www.google.com', 5);-- google.com
select substring('www.google.com', 5, 6);-- google
select substring('www.google.com', -3, 3);-- com

mid

等价于substring。

substr

等价于substring。

locate

​​locate(substr, str)​​，返回子串substr在字符串str第一个出现的位置，如果substr不是在str里面，返回0。

select locate('google', 'www.google.com');-- 4
select locate('baidu', 'www.google.com');-- 0

position

​​position(substr IN str)​​​等价于​​locate(substr, str)​​。

select position('google' in 'www.google.com');--5

substring_index

用于截取字符串：​​substring_index(str, delim, count)​​

• str：要处理的字符串
• delim：分隔符
• count：计数

实例：
count是正数，从左往右，第N个分隔符的左边的全部内容

select substring_index('www.google.com', '.', 1);-- www
select substring_index('www.google.com', '.', 2);-- www.google

count是负数，从右往左，第N个分隔符的右边的全部内容

select substring_index('www.google.com', '.', -1);-- com
select substring_index('www.google.com', '.', -2);-- google.com

怎么取中间的​​google​​？从两个方向截取：

select substring_index(substring_index('www.google.com', '.', -2), '.', 1);-- google

concat

​​concat(str1, str2, ...)​​​：将若干个字符串按照顺序拼接起来
常用于like查询：​​​select user_id from user where username like concat('%', 'johnny', '%');​​

SELECT concat('My', NULL, 'QL');-- null

concat_ws

​​concat_ws(separator, str1, str2, ...)​​：将若干个字符串按照顺序通过separator拼接起来

SELECT concat_ws(',', 'My', 'SQL');-- My,SQL

group_concat

​​group_concat()​​​：用于将若干行结果集合并输出为一行字符串，separator默认是​​,​​，可替换为指定的分隔符：

select group_concat(login_name) from user;--aa,bb
select group_concat(login_name SEPARATOR ';') from user;--aa;bb

如果想要对输出的结果进行排序和去重咋处理：

• DISTINCT子句用于在连接分组之前消除组中的重复值
• ORDER BY子句允许在连接之前按升序或降序排序值，默认升序
• SEPARATOR指定在组中的值之间插入的文字值

GROUP_CONCAT函数忽略NULL值，如果找不到匹配的行，或所有参数都为NULL值，则返回NULL；GROUP_CONCAT函数返回二进制或非二进制字符串，这取决于参数。 默认情况下，返回字符串的最大长度为1024。如果您需要更多的长度，可以通过在SESSION或GLOBAL级别设置​​group_concat_max_len​​系统变量来扩展最大长度。

其他

列更新

如，http更新为https：

UPDATE <table> SET <field> = replace(field, 'http://www.baidu.com', 'https://www.baidu.com');

常用于批量更新数据，洗数据：

// 优化字段命名方式为驼峰命名
UPDATE dataset SET data_json = replace(data_json, 'mongokey', 'mongoKey');
// data_json见名知义是json string,其中sql字段为多段SQL,以`;`分隔,删除多余空SQL子句,查询时无需转移,update洗数据时需要加上转义字符
UPDATE dataset SET data_json = replace(data_json, ';\\n;', ';')
where json_unquote(json_extract(data_json, '$.query.sql')) like '%;\n;%';

列统计

有如下经过简化的建表语句：

create table execlog
(
    id             bigint(11) auto_increment        primary key,
    model_id       bigint(11)                          null comment '任务Id',
    model_type     tinyint(2)                          not null comment '任务类型',
    exec_status    tinyint(2)                          null comment '执行状态0-失败,1-成功,2-处理中, 3-延时'
);

用于记录若干个任务执行结果，任务Id即为​​model_id​​​字段，​​exec_status​​表示执行结果。

现在想要统计全部任务执行失败率降序情况：

select model_id,
       count(*)                                                            as cnt,
       count(case when exec_status = 0 then model_id end)                  as failed_cnt,
       count(case when exec_status = 0 then model_id end) / count(*) * 100 as failed_rate
from execlog e
group by model_id
order by failed_rate desc;

统计最近20次连续执行未成功过一次的情况：

select model_id, group_concat(e.exec_status) as totalStatus
from execlog e
         left join dataset d on e.model_id = d.dataset_id
where d.isactive = 1
  and d.cron_exp_status = 1
  and e.model_type = 2
group by e.model_id
HAVING totalStatus not LIKE '%1%'
   and length(totalStatus) > 40
;

查询重复数据取其中一条

SELECT a.*
FROM channle_sourceid a,
     (
         SELECT MAX(id) as id
         FROM channle_sourceid
         GROUP BY channel_id, source_id, id) b
WHERE a.id = b.id
ORDER BY a.id DESC

删除重复数据，仅保留索引(id)最小的一条数据

硬删除：

delete
from role_res
where role_res_id in (
    select role_res_id
    from (
             select max(role_res_id) as role_res_id
             from role_res
             group by role_id, res_type, res_id, permission
             having count(*) > 1
         ) dt
);

逻辑删除：

update role_res set is_active = 0
where role_res_id in (
    select role_res_id
    from (
             select max(role_res_id) as role_res_id
             from role_res
             group by role_id, res_type, res_id, permission
             having count(*) > 1
         ) dt
);

备注：
网络上流传着另一种写法：上面的​​​in​​​改为​​not in​​​，​​max​​​改为​​min​​，这种写法其实是有问题的，那些没有重复的数据也会被删除。

但是这种方法，也不是没有毛病，如果有3条以上的重复记录，上面的​​in ... max​​写法需要多次执行才能将重复的数据删除。

结论：使用​​in ... max​​方式。至少目前没有找到更好的写法。

添加constraint限制

alter table role_res add constraint uniq_role_res_type_id unique (role_id, res_type, res_id);

指定字段加前缀

加前缀000：

update user_role set user_id = concat('000', user_id) where length(user_id) = 3;

指定字段加后缀

加后缀000：

update user_role set user_id = concat(user_id, '000') where length(user_id) = 3;

指定字段去掉前缀

从左数第二位之前的字符去掉；包括第二位：

update user_role set user_id = right(user_id , length(user_id ) - 2) where length(user_id) = 3;

指定字段去掉后缀

从右数第二位之后的字符去掉；包括第二位：

update user_role set user_id = left(user_id , length(user_id ) - 2) where length(user_id) = 3;

获取时间毫秒数

MySQL表字段定义：

inserttime datetime default CURRENT_TIMESTAMP not null comment '插入时间',

-- 取秒则无需乘以1000
select ROUND(UNIX_TIMESTAMP(inserttime) * 1000) from tb1;

更新cron+3小时

背景，有这么一张数据集定时调度执行表（省略其他字段）：

create table iview_new.dataset
(
    dataset_id    bigint auto_increment primary key comment '主键,数据集ID',
    datasource_id bigint       not null comment '数据集依赖的数据源ID',
    dataset_name  varchar(100) not null comment '数据集名称',
    cron_exp      varchar(200) not null comment 'cron表达式'
);

需求，对数据表里面的cron表达式hour数批量增加3小时，即延后3小时执行。

洗数据脚本如下：

UPDATE dataset SET cron_exp = CONCAT(
    substring_index(cron_exp, ' ', 2),
    ' ',
    substring_index(substring_index(cron_exp, ' ', 3), ' ', -1) + 3,
    ' ',
    substring_index(cron_exp, ' ', -3)
    )
where dataset_id = 8429
-- hour小于12
and substring_index(substring_index(cron_exp, ' ', 3), ' ', -1) < 12;

参考

• ​​search-group-concat-using-like​​
• ​​string-functions​​
• ​​group_concat​​
• ​​Java解析cron表达式​​