1. 建表

-- ho_his.cf_bsp_addins definition
-- 创建ID计数器系列，id SERIAL时，自动生成系列
-- 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 SERIAL PRIMARY KEY, -- PostgreSQL,人大金仓
    id INT AUTO_INCREMENT PRIMARY KEY, -- TDSQL
    -- 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后，速度提升。

5. 插入关联表

尝试使用returning返回插入id并不能实现，最终使用再次查询

INSERT INTO cf_bsp_prdline
(code, caption, note, create_datetime, update_datetime, hospital_dr, create_user_dr, update_user_dr, activity, order_no)
VALUES ('x', '描述', '', now(), now(), -1, '0', '0', true, 14) ;

INSERT INTO cf_bsp_prdline_module
(code,prdline_dr, caption, note, create_datetime, update_datetime, hospital_dr, 
 create_user_dr, update_user_dr, activity, order_no)
SELECT m.code,new_prdline.id,m.caption,m.note,m.create_datetime,m.update_datetime,m.hospital_dr,
m.create_user_dr,m.update_user_dr,m.activity,m.order_no
FROM (select id from cf_bsp_prdline where code='doctortt') new_prdline,
     ( VALUES
           ('xx1', '描述1', '', now(), now(), NULL, '0', '0', true, NULL),
           ('xx2', '描述2', '', now(), now(), NULL, '0', '0', true, NULL),
           ('xx3', '描述3', '', now(), now(), NULL, '0', '0', true, NULL),
           ('xx4', '描述4', '', now(), now(), NULL, '0', '0', true, NULL),
           ('xx5', '描述5', '', now(), now(), NULL, '0', '0', true, NULL)
     ) AS m(code, caption, note, create_datetime, update_datetime, hospital_dr,
            create_user_dr, update_user_dr, activity, order_no);