mysql使用小技巧

发表于 | 分类于 |

MySQL总结
.
mysql使用小技巧
pg使用小技巧
ck使用小技巧

四舍五入

1
2
3
4
5
-- 四舍五入取整
ROUND(x)

-- 四舍五入取整，保留2位小数
ROUND(x, 2)

一行转多行

1
2
3
4
5
6
7
8
9
-- 2023-02-14 16:46:09
with s as (select store_id, org_structure_id from t_sys_org_structure_store where tenant_id=$tenant_id$)
select
    s.store_id,
    -- o.path          -- 一行变多行
    substring_index(substring_index(o.path, ',', m.help_topic_id + 1) ,',', -1) org_id
from s left join v_sys_org_structure o on s.org_structure_id=o.id
join mysql.help_topic m on m.help_topic_id < length(o.path) - length(replace(o.path,',','')) + 1
-- where s.store_id=8

合并数组/数组拆分

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- mysql 有长度限制
set session group_concat_max_len = 值;-- 当前会话（去除 group_concat 长度限制）
set global group_concat_max_len = 值;-- 全局设置（去除 group_concat 长度限制）
group_concat(store_id separator ',') storeIds
group_concat(distinct store_id order by store_id asc separator ',') storeIds

-- mysql 无长度限制
with tmp as (
      select distinct store_id
      from ...
          where ...
          order by store_id
)
select json_arrayagg(store_id) from tmp

-- ck
arrayStringConcat(groupArray(post_name), ',')
arrayStringConcat(groupUniqArray(post_name), ',')

字符串合并

1
2
3
4
5
6
7
8
9
10
11
12
-- 2023-04-17 09:49:09

-- ----- --
-- MySQL --
-- ----- --

-- concat    所有元素直接拼接（任何一个为NULL，结果为NULL）
select concat('a', 'b', 'c') -- abc
select concat('a', NULL, 'c') -- NULL

-- concat_ws 第一个为拼接字符，忽略NULL
select concat_ws('.', 'a', 'b', NULL, 'c') -- a.b.c

字符串分割

1
2
3
4
5
6
7
8
9
10
11
12
-- 2024-04-12 13:40:29 之前一直都没有记录

-- pg
select split_part('11,22', ',', 1);-- '11' 第一个元素
select split_part('11,22', ',', 5);-- '' 取不到，返回空字符串
select split_part('11', ',', 1);-- '11' 第一个元素
-- mysql
select substring_index('11,22', ',', 1);-- '11' 第一个元素
select substring_index('11,22', ',', 5);-- '11,22' 取不到，返回原字符串
select substring_index('11', ',', 1);-- '11'
select substring_index('11,22', ',', -1);-- '22' 倒数第一个元素
select substring_index('11,22', ',', -5);-- '11,22' 取不到，返回原字符串

索引/慢查询/杀进程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 2023-03-16 11:42:51 创建索引
CREATE INDEX hawkeye_job_log_date_created_job_id_idx ON hawkeye_job_log (date_created, job_id);

-- 2023-12-19 17:38:50 一次SQL慢查询，杀进程，查询索引
select * from information_schema.processlist
where DB='tenant' and info not like '%information_schema.processlist%'
and command='Query' and time>5 -- 查询耗时大于5s
order by time desc;

kill xxx

select
    table_name as '表名',
    index_name as '索引名',
    index_type as '索引类型',
    group_concat(column_name order by seq_in_index separator ',') as '索引字段'
from information_schema.statistics
where table_schema = 'tenant' and index_name!='PRIMARY'
and table_name in ('table1', 'table2')
group by table_name, index_name, index_type
order by table_name, index_name, index_type;

在这里插入图片描述

日期函数

MySQL常用的日期函数

1
2
3
4
5
6
-- 2023-03-07 14:24:28
    to_char(date_created,'yyyy-mm-dd') as cover_day -- PG
    date_format(date_created,'%Y-%m-%d') as cover_day -- MySQL

    to_char(r.last_modified, 'YYYY-MM-DD HH24:MI:SS') last_modified, -- PG
    date_format(r.last_modified, '%Y-%m-%d %H:%i:%s') last_modified, -- MySQL

count多条件

1
2
3
4
5
6
7
8
9
10
11
-- 2023-03-17 18:03:08
-- 兼容pg
with tmp as (select count(1) from patrol_config where tenant_id=$tenant_id$ AND config_type='CATEGORY')

select
		count(case when rel_type='SIGNED' then 1 end) signed,
		count(case when rel_type='CATEGORY_TASK' then 1 end) category_task,
		(select * from tmp) category_task_all,
		count(case when rel_type='DIAGNOSIS_TASK' then 1 end) diagnosis_task,
		count(case when rel_type='PATROL_SUMMARY' then 1 end) patrol_summary
from patrol_rel_detail where record_id=$record_id$

json解析

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- json对象解析
    -- PG
    (c.json_config ->> 'maxScore')::numeric max_score
    (rd2.rel_json ->> 'content')::text summary

    -- MySQL
    json_extract(c.json_config,'$.maxScore') max_score -- 函数
    (rd2.rel_json ->> '$.content') summary -- 字符串
    (fsc.feature_json_data ->> '$.code') code, -- 字符串
    convert((fsc.feature_json_data ->> '$.actualValue'), decimal(65, 4)) actualValue -- 解析并转为 decimal



-- json数组解析         ===> [{"featureCode": "sales", "featureName": "销售额", "targetValue": "50000"},{"featureCode": "atv", "featureName": "客单价", "targetValue": "650"}]
select
 sum(tmp.target_value) sales
from
    $table$,
    JSON_TABLE($table$.fetch_target_json, '$[*]' COLUMNS(
     feature_code varchar(255) PATH '$.featureCode',
     target_value decimal(10,2) PATH '$.targetValue'
    )) tmp
where
    tenant_id=$tenant_id$
    AND rel_type='STORE'
    AND rel_id IN ($storeIds$)
    AND stats_date BETWEEN  $startDate$ AND $endDate$
    -- AND stats_date BETWEEN '2023-01-01' AND '2023-01-09'
 AND tmp.feature_code='sales'

视图定义

2023-09-27 00:27:59 Navicat 图形化，直接写查询SQL即可。
在这里插入图片描述

排名、行号

2024-12-30 12:43:05

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

with tmp as (
	          select 'a' name, 3 score
	union all select 'b' name, 1 score
	union all select 'c' name, 1 score
	union all select 'd' name, 2 score
	union all select 'e' name, 0 score
)
select
	*,
	
	-- 如果有两行并列第1，下一行的排名将是第3。
	rank() over (order by score desc) rk1,-- a=1 b=3 c=3 d=2 e=5
	
	-- 如果有两行并列第1，下一行的排名将是第2。
	dense_rank() over (order by score desc) rk2,-- a=1 b=3 c=3 d=2 e=4
	
	-- 多字段排名
	rank() over (order by score desc, name desc) rk3,-- a=1 b=4 c=3 d=2 e=5
	
	-- 行号
	row_number() over (order by score desc) rk4,-- a=1 b=3 c=4 d=2 e=5
	
	''
from tmp
order by name
;



-- partition
with tmp as (
	          select 'a' name, 3 score
	union all select 'a' name, 2 score
	union all select 'a' name, 1 score
	union all select 'b' name, 1 score
	union all select 'b' name, 3 score
	union all select 'c' name, 1 score
), tmp2 as (
		select
			*,
		
			-- 行号
			row_number() over (partition by name order by score desc) rk,
			
			''
		from tmp
		order by name
)

select * from tmp2 where rk<=2
;

字段交换

2025-02-05 11:04:08
如果两个字段搞反了，可使用此方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
select * from aaa;
-- id|f1|f2|min_price|max_price|
-- --+--+--+---------+---------+
--  1|a1|a2|        1|        2|
--  2|b1|b2|        3|        4|

-- ----------------------------------------------------------------------------
-- 数值交换
update aaa set
	min_price = min_price + max_price,
	max_price = min_price - max_price,
	min_price = min_price - max_price
;

-- ----------------------------------------------------------------------------
-- 临时变量交换
update aaa set
	-- f1 = (@temp := f1),
	f1 = @temp := f1,
	f1 = f2,
	f2 = @temp
;