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

-- 字段合并一行,支持排序
SELECT tenant_id, group_concat(id separator '.') FROM hawkeye_tenant_config GROUP BY tenant_id
SELECT tenant_id, group_concat(id ORDER BY id DESC separator '.') FROM hawkeye_tenant_config GROUP BY tenant_id

索引创建

1
2
-- 2023-03-16 11:42:51
CREATE INDEX hawkeye_job_log_date_created_job_id_idx ON hawkeye_job_log (date_created, job_id);

日期函数

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即可。
在这里插入图片描述