概述
每次想要实现一个功能时,总是百度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表达式