1. 建表

-- ho_his.cf_bsp_addins definition
-- 创建ID计数器系列
create sequence "ho_his".cf_bsp_addins_id_seq increment by 1 start with 1
-- 创建表
-- create_datetime,update_datetime为约定每张表都需要的字段

CREATE TABLE ho_his.cf_bsp_addins (
    id numeric(10,0) DEFAULT nextval('ho_his.cf_bsp_addins_id_seq'::regclass) NOT NULL,
    code varchar(100) DEFAULT NULL::varchar NOT NULL,
    domId varchar(100) DEFAULT NULL::varchar NOT NULL,
    create_datetime timestamp NOT NULL,
    update_datetime timestamp NOT NULL,
    create_user_dr varchar(32) NOT NULL,
    update_user_dr varchar(32) NOT NULL,
    activity bool DEFAULT true NOT NULL,
    CONSTRAINT cf_bsp_addins_pkey PRIMARY KEY (id)
);
CREATE UNIQUE INDEX cf_bsp_addins_domid_un ON ho_his.cf_bsp_addins ("domId");
CREATE UNIQUE INDEX cf_bsp_addins_un ON ho_his.cf_bsp_addins (code);
COMMENT ON COLUMN "ho_his"."cf_bsp_addins"."id" IS '主键';
COMMENT ON COLUMN "ho_his"."cf_bsp_addins"."code" IS '代码';
COMMENT ON COLUMN "ho_his"."cf_bsp_addins"."domId" IS '调用id';

2. 表计数器

-- 查询seq - 自增加变量
SELECT * FROM sys_statio_all_sequences WHERE relname LIKE '%bsp_msg%'

-- 让计数器归0
-- 设置步调-100
ALTER SEQUENCE cf_bsp_msg_x_id_seq INCREMENT BY -100 ;
-- 调用一次seq, -100
SELECT nextval('cf_bsp_msg_x_id_seq'::regclass);
-- 调用一次seq,-100
SELECT nextval('cf_bsp_msg_x_id_seq'::regclass) ;

3.查询条件

在mybatis.xml中忽略特殊字符查询记录写法

select code, id from cf_bsp_alert_code t
where REGEXP_REPLACE(t.code,'[^A-Za-z0-9]','') like concat('%',REGEXP_REPLACE(#{dto.code},'[^A-Za-z0-9]',''),'%')

使用ilike来模糊查询

(code ilike concat('%', #{dto.code}, '%') or description ilike concat('%',#{dto.code},'%'))

使用UPPER转大写，再模糊查询

(UPPER(code) like concat('%', UPPER(#{dto.code}), '%') or UPPER(description) like concat('%',UPPER(#{dto.code}),'%'))

使用CURRENT_DATE来判断当前是不是有效

AND d.activity=true AND d.is_deleted=0
AND (t.start_date is NULL OR CURRENT_DATE>=t.start_date)
AND (t.end_date IS NULL OR t.end_date>=CURRENT_DATE)
ORDER BY d.id ASC

使用TO_DATE来查询表中数据

AND d.activity=true AND d.is_deleted=0
AND (t.start_date is NULL OR to_date('20250501','yyyymmdd')>=t.start_date)
AND (t.end_date IS NULL OR t.end_date>=CURRENT_DATE)
ORDER BY d.id ASC

4.联表查询时，联表字段类型保持一致,才会使用索引

cf_bsp_test_patient表中只有3行记录,pa_pat_mast表有28万行数据

explain analyze
select t.id,t.patient_id,t.start_date,t.end_date, p.name "patientName"
    from cf_bsp_test_patient t
    left join pa_pat_mast p on p.id=t.patient_id
    where 1=1 and (t.start_date >= '20250501' or t.start_date is null) order by t.id asc LIMIT 500

上面解析sql，评估运行花费350ms时间

explain analyze
select t.id,t.patient_id,t.start_date,t.end_date, p.name "patientName"
    from cf_bsp_test_patient t
    left join pa_pat_mast p on p.id=cast(t.patient_id as bigint)
    where 1=1 and (t.start_date >= '20250501' or t.start_date is null) order by t.id asc LIMIT 500

连表前转换字段类型，此时运行SQL,解析SQL,评估运行花费0.129ms

修改cf_bsp_test_patient表的patient_id字段类型修改成bigint后，速度提升。