mysql使用小技巧

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

四舍五入

-- 四舍五入取整
ROUND(x)

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

一行转多行

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

合并数组/数组拆分

-- 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), ',')

字符串合并

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

字符串分割

-- 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' 取不到，返回原字符串

索引/慢查询/杀进程

-- 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常用的日期函数

-- 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多条件

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

-- 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即可。