mysql使用小技巧
 pg使用小技巧
 ck使用小技巧

一些常用的

-- 2021-10-17 10:56:35
-- 查询版本号
select version();

-- 查看事务状态
SHOW default_transaction_isolation;
SHOW transaction_isolation;

json写入错误

1
2
3

-- 问题：json 类型插入报错 column "**" is of type json but expression is of type character varying
-- 解决方案：url添加后缀 &stringtype=unspecified
-- 参考：   jdbc:postgresql://10.11.12.232:5432/patrol?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&stringtype=unspecified

修改连接数

-- 2021-10-18 18:21:08
-- 2023-03-03 17:37:13 更新
-- 1、查看最大连接数
show max_connections;

-- 2、查看当前连接数
select count(*) from pg_stat_activity;

-- 3、查看系统保留的用户数（此参数为数据库为超级用户预留的连接数，默认值为3。）
-- 当数据库的连接数达到（max_connections - superuser_reserved_connections）时，只有超级用户才能建立新的数据库连接，
-- 普通用户连接时将会返回错误信息“FATAL: sorry, too many clients already.”
-- 或者“FATAL: remaining connection slots are reserved for non-replication superuser connections”，登录数据库查询。
show superuser_reserved_connections;

-- 4、查看按用户分组统计连接数
select usename, count(*) from pg_stat_activity group by usename order by count(*) desc;

-- 5、优化建议
--      postgresql的最大连接数合适值used_connections/max_connections ≈ 85%
--      通过postgresql.conf文件修改，需重启服务
--      修改最大连接数      alter system set max_connections= 数量

-- 6、Docker 修改最大连接数
-- docker inspect {容器id} | grep Mounts -A 20
-- vim postgresql.conf      ===>    搜索 max_connections    修改后重启容器

四舍五入

-- 2021-11-04 11:54:33
-- 测试 PostgreSQL 列计算
select 1/4 as value;-- 0
select 11/4 as value;-- 2
select round(1::numeric/4::numeric,2) as value;-- 0.25   => 1/4 保留两位小数
-- 例如（金额 money：元 -> 万 保留一位小数）：select round(money::numeric/10000::numeric,1) feature_value from table ...

日期函数

-- 2023-03-07 14:24:28
<if test="dbType == 'PostgreSQL'">
    to_char(date_created,'yyyy-mm-dd') as cover_day
</if>
<if test="dbType == 'MySQL'">
    date_format(date_created,'%Y-%m-%d') as cover_day
</if>


-- 2024-05-22 10:53:06 获取 今天 昨天
select current_date    -- 2024-05-22
select current_date-1  -- 2024-05-21

count多条件

-- 2023-03-07 09:18:24
-- 方式1 （不兼容mysql）
with tmp as (...)

select
    count(1) FILTER(where rel_type='CATEGORY_TASK') score_count,-- 打分项
    count(1) FILTER(where rel_type='CATEGORY_TASK' and score!=max_score) loss_score_count,-- 失分项
    count(1) FILTER(where rel_type='DIAGNOSIS_TASK') diagnosis_count,-- 异常跟进
    (select round(sum(score)/sum(max_score)*100, 0) from tmp where rel_type='CATEGORY_TASK') patrol_score-- 巡检检查分
from tmp

-- 2023-03-17 18:03:08
-- 方式2 （兼容mysql）
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$

rank函数

2023-01-28...11.47.49 pg rank函数.txt

之前竟然没记录，今天邹俊问我，我才记录

select
	region_id,
	score,
	-- 默认别名 rank
	rank() over(partition by null order by score desc)
from t_score_card_report
where tenant_id='xtep'
and score_card_type ='DAY_STORE_SC'
and calc_begin_time ='2023-01-27'


region_id|score|rank|
---------+-----+----+
     3012| 4.10|   1|
     2204| 4.10|   1|
     2200| 4.10|   1|
     2936| 4.00|   4|
     3036| 4.00|   4|
     3035| 4.00|   4|
     2642| 4.00|   4|
     3154| 4.00|   4|
     2217| 3.90|   9|
     2802| 3.90|   9|
     2214| 3.80|  11|
     2957| 3.80|  11|
     2266| 3.80|  11|
     3046| 3.80|  11|
     2974| 3.80|  11|
     3068| 3.80|  11|
     3123| 3.80|  11|

查询默认值返回

1
2
3

-- 2021-12-27 17:19:04
-- 默认值（查询 end_date = null 的时候，使用默认值 '2021-12-31'）
select id, start_date, coalesce(end_date,'2021-12-31') from t_store_log where id=323

序列

-- 2021-10-21 10:09:43
-- 查看下一个序列号
-- '%s_id_seq' => %s 表名
select nextval('t_cloak_user_id_seq');
select nextval('t_task_center_log_id_seq');
        --nextval(regclass)
        --currval(regclass)
        --lastval()
        --setval(regclass, bigint)
        --setval(regclass, bigint, boolean)

-- 一个库所有表恢复最大
select concat(e'select setval(\'', relname, e'\', max(id)) from ', split_part(relname, '_id_seq', 1), ';') sql from pg_class where relkind = 'S'

-- 2023-12-15 14:57:13 使用序列插入 ===> 序列名称使用上面的方式查询【一个库所有表恢复最大】
select setval('patrol_config_id_seq', max(id)) from patrol_config;
insert into public.patrol_config (id, tenant_id, config_type, category) values (nextval('patrol_config_id_seq'), 'amoros', 'DIAGNOSIS', '鲜货销售占比');
insert into public.patrol_config (id, tenant_id, config_type, category) values (nextval('patrol_config_id_seq'), 'amoros', 'DIAGNOSIS', '零售销售额');
insert into public.patrol_config (id, tenant_id, config_type, category) values (nextval('patrol_config_id_seq'), 'amoros', 'DIAGNOSIS', '件牌价');
select setval('patrol_config_id_seq', max(id)) from patrol_config;

值替换

-- 2021-12-16 16:45:46
-- 值替换
-- [1] string 类型（把表 table_01 中varchar类型字段 rule_json_data 的值 aaa 全部替换为 bbb）
update table_01 set rule_json_data=replace(rule_json_data,'aaa','bbb')
-- [2] json 类型（把表 table_01 中json类型字段 rule_json_data 的值 aaa 全部替换为 bbb。先把字段::text转为字符串，替换后的结果::json转为json类型赋值给rule_json_data字段）
update table_01 set rule_json_data=replace(rule_json_data::text,'aaa','bbb')::json
-- [3] 转义符。在要替换的字符串前面加大写的 E，在内容中'单引号前面加 \ 反斜杠
-- 把 scr.region_type='STORE'     修改为     scr.region_type='BRANCH'
update t_sc_feature_template_rule
 set rule_json_data=replace(rule_json_data::text,E'scr.region_type=\'STORE\'',E'scr.region_type=\'BRANCH\'')::json;

-- 2024-04-16 15:04:44
-- postgreSQL \n 变为 换行
update prompt_config set description=replace(description, '\n', E'\r\n') where id=23;

字符串分割

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

json数据删除元素

-- 2023-12-01 15:45:03
update public.t_sc_feature_month_target_info
set fetch_target_json = (
    select json_agg(element)
    from (
             select json_array_elements(fetch_target_json) as element
         ) as sub
    where element->>'featureCode' != 'sales'
    )
where id = 9444;

json解析/行转列

-- 取key，转numeric，求sum          sum((sc.json_config ->> 'maxScore')::numeric)
with tmp as (
		with r as (...)
		select
				cc.category,
				-- rd.score,
				-- cast(sc.json_config ->> 'maxScore' as numeric) max_score
				-- (sc.json_config ->> 'maxScore')::numeric max_score
				
				sum(rd.score) score_sum,
				sum((sc.json_config ->> 'maxScore')::numeric) max_score_sum
		from r
		left join patrol_rel_detail rd ON r.id=rd.record_id
		left join patrol_category_config cc ON rd.rel_id=cc.id
		left join patrol_score_config sc ON cc.score_config_id=sc.id
		where rd.rel_type='SCORE_TASK'
		group by cc.category
)

select
		category,-- 大类
		round(score_sum/max_score_sum*100, 0) score -- 检查项平均分    大类中每一项总分/最高总分 * 100
from tmp
order by score desc
limit 5


-- PG case when 行转列
-- PG JSON 类型解析
-- ->> 双箭头获取字符串值 sales
-- ->  单箭头获取字符串 "sales"
select
	sum(case when feature_code = 'sales' then target_value else 0 end ) sales,
	sum(case when feature_code = 'retailCount' then target_value else 0 end ) retail_count,
	sum(case when feature_code = 'atv' then target_value else 0 end) atv,
	sum(case when feature_code = 'storeCustomerCnt' then target_value else 0 end) store_customer_cnt,
	sum(case when feature_code = 'upt' then target_value else 0 end) upt,
	sum(case when feature_code = 'cr' then target_value else 0 end) cr,
	sum(case when feature_code = 'md' then target_value else 0 end) md
from (
		select
			sub.code feature_code,
			sum(sub.value::numeric) target_value
		from
			(
			select
				json_array_elements(fetch_target_json) ->> 'featureCode' code,
				json_array_elements(fetch_target_json) ->> 'targetValue' value
			from
				t_sc_feature_daily_target_info
			where
				tenant_id = $tenant_id$
				and rel_type = 'STORE'
				and rel_id in ($storeIds$)
				and stats_date between $startDate$ and $endDate$) sub
		group by sub.code order by sub.code
) sub2

一行转多行

-- 2023-02-14 16:25:49
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          -- 一行变多行
    unnest(string_to_array(o.path, ',')) org_id
from s left join v_sys_org_structure o on s.org_structure_id=o.id
-- where s.store_id=8

字符串合并

-- 2023-04-17 09:49:09

-- -- --
-- PG --
-- -- --

-- concat    所有元素直接拼接（忽略NULL）
select concat('a', 'b', 'c') -- abc
select concat('a', NULL, 'c') -- ac

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

-- 字段合并一行，支持排序
select tenant_id, string_agg(id::text, ',')  from hawkeye_tenant_config group by tenant_id
select tenant_id, string_agg(id::text, ',' order by id desc)  from hawkeye_tenant_config group by tenant_id

select tenant_id, array_agg(id) from hawkeye_tenant_config group by tenant_id-- 数组类型
select tenant_id, array_agg(distinct id) from hawkeye_tenant_config group by tenant_id-- 数组类型 去重
select tenant_id, array_agg(distinct id order by id desc) from hawkeye_tenant_config group by tenant_id-- 数组类型 去重 排序
select tenant_id, array_to_string(array_agg(distinct id order by id desc), ',') from hawkeye_tenant_config group by tenant_id-- 字符串类型

建表 SQL

1	-- 手动 Navicat 建，DBeaver 导出 DDL（只勾选 “Show comments”）。

修改字段

1 2	-- 修改字段类型 alter table hawkeye_job_log alter column rel_id type varchar(255);

索引

-- 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-05 16:08:38   查询
-- select indexname, indexdef from pg_indexes where tablename = 'message';
select split_part(indexdef, '(', 2) _ from pg_indexes where tablename = '表名';

-- 2024-02-22 13:19:51 删除
drop index 索引名;
drop index hawkeye_job_log_date_created_job_id_idx;

nullif

1 2	-- 2024-05-22 09:29:52 nullif(a, 0) -- 如果a为0，就返回null

数据库迁移

参考博客服务器迁移-PG