在PogstgreSQL/pgsql中维护【根据日期时间从1开始计数】的单据号

天马座的流星拳

已于 2022-10-27 17:05:14 修改

阅读量383

点赞数

分类专栏： # PostgreSQL 文章标签：数据库 postgresql pgsql

于 2022-06-28 11:44:51 首次发布

本文链接：https://blog.csdn.net/ouyongke/article/details/125498095

版权

PostgreSQL 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

我以前呆过的公司，主键ID都是代码自行维护的，不使用数据库自增字段（数据库是MSSQL，自增字段不允许Insert和Update）。

使用数据库的表来自主维护顺序增加的id值，相对于UUID而言，使用这样值做ID主键优势是那个表在写入记录时不会对id的主键索引造成太大压力，毕竟表里的数据是需要按主键进行排序的；

当然，缺点也很明显，因为需要频繁的读取写入自主维护id的那个表，当大批量Insert时必然需要大批量生成这个id，其性能肯定不会太好。做主键的话，还是应该用自增字段。

最后，其实对于PgSQL而言，自主维护主键ID没有太多实际意义，因为即使某个字段是自增id的，它也是允许Insert和Update的，只不过，改完值之后该自增字段的序列值（sequence）需要使用currval()函数和setval()来进行维护一下，否则新添加的记录可能会主键冲突。

自主维护ID的事说完了，说说本文的主题：可以根据日期时间从1开始计数的单据号。

前段时间，看到钉钉里的单据号，突然发现它是每分钟从1开始计数的，而我们若有业务需要，也会需要每月1号、或者每小时从1开始生成单据号。所以，干脆用PgSQL把这个需求实现一下，语法基本上兼容MySQL。

思路大致分为两步：分别为建表、通过存储过程取值和维护。

关键的技术点是：

需要对【自主维护的ID】所在的那个行或者叫那条记录，加上【行锁】，以避免并发。

以前在MSSQL时是【启用事务】，因为MSSQL遇到被事务锁住的行就会等待事务结束，现在PgSQL和MySQL则可以在Select中使用【for update】子句实现行锁。

第1步，建表及初始化

CREATE TABLE sys_serial_no
(
  sid serial8 NOT NULL,
  use_table VARCHAR NOT NULL,
  use_field VARCHAR NOT NULL,
  use_datetime TIMESTAMP NOT NULL,
  current_no int8 NOT NULL DEFAULT 1,
  prefix_char VARCHAR NOT NULL DEFAULT '',
  char_len SMALLINT NOT NULL DEFAULT 8,
  min_value SMALLINT NOT NULL DEFAULT 1,
  max_value INT8 NOT NULL DEFAULT 9223372036854775800,
  no_format VARCHAR NOT NULL,
  is_setting int2 NOT NULL DEFAULT 0,
  PRIMARY KEY (sid)
);
COMMENT ON COLUMN sys_serial_no.use_table IS '使用单据号的表';
COMMENT ON COLUMN sys_serial_no.use_field IS '使用单据号的字段';
COMMENT ON COLUMN sys_serial_no.use_datetime IS '单据分段的日期时间';
COMMENT ON COLUMN sys_serial_no.current_no IS '当前单据号的值';
COMMENT ON COLUMN sys_serial_no.prefix_char IS '生成单据号的前缀';
COMMENT ON COLUMN sys_serial_no.char_len IS '生成单据号的最小位数，前面补0值';
COMMENT ON COLUMN sys_serial_no.min_value IS '初始单据号的最小值，达到max_value时将从此值重新开始';
COMMENT ON COLUMN sys_serial_no.max_value IS '单据号的最大值，比logint的值9223372036854775807略小';
COMMENT ON COLUMN sys_serial_no.no_format IS '单据号格式，{prefix_char}前缀、{year}年、{month}月、{date}日、{hour}时、{minute}分、{second}秒、{current_no}当前号码';
COMMENT ON COLUMN sys_serial_no.is_setting IS '1=参数设置记录;0=自增id记录';
COMMENT ON TABLE sys_serial_no IS '单据号自主维护表，表简称sid_table、syssid_table。is_setting=1的记录，通过prefix_char/char_len/min_value/max_value等字段进行序列号设置，而is_setting=0的记录用于记录当前序列号';

-- 对use_table、use_field和use_datetime字段加聚集索引，防止重复
CREATE UNIQUE INDEX idx_sysserialno_unique on sys_serial_no(use_table, use_field, use_datetime)

-- 例：初始化客人资料表的客人号码
INSERT INTO sys_serial_no (use_table, use_field, use_datetime, 
  current_no, prefix_char, char_len, 
  min_value, max_value, no_format, is_setting) 
SELECT 'table_guests', 'field_guest_no', date_trunc('second', current_timestamp), 
  0, 'G', 8, 
  1, 9223372036854775800, '{prefix_char}{year}{month}{date}{hour}{current_no}', 1
 WHERE NOT EXISTS(SELECT sid FROM sys_serial_no 
                   WHERE is_setting=1 AND use_table='table_guests' AND use_field='field_guest_no');
 
-- 例：初始化账单表的账单号码（no_format格式中不输出hour）
INSERT INTO sys_serial_no (use_table, use_field, use_datetime, 
  current_no, prefix_char, char_len, 
  min_value, max_value, no_format, is_setting) 
SELECT'table_bill', 'field_bill_no', date_trunc('second', current_timestamp), 
  0, 'B', 8, 
  1, 9223372036854775800, '{prefix_char}{year}{month}{date}{current_no}', 1
 WHERE NOT EXISTS(SELECT sid FROM sys_serial_no 
                   WHERE is_setting=1 AND use_table='table_bill' AND use_field='field_bill_no');
 
 -- 调用第2步的函数/存储过程来取值
 SELECT get_serialno_hour('table_guests', 'field_guest_no'); -- G202206262100000029
 SELECT get_serialno_hour('table_bill', 'field_bill_no');    -- B2022062600000112

第2步，通过存储过程取值和维护

建议有个界面对is_setting=1的记录进行维护，而不是通过手动Insert记录。

下面的脚本，是按小时生成从1开始的单据号的函数/存储过程get_serialno_hour()，通过上面初始化记录中的no_format字段，可以自定义单据号的生成格式。

通过重构和调整，也可以改成按月从1开始生成单据号的函数get_serialno_month(_table_name TEXT, _field_name TEXT, _year INT=0, _month INT=0, _date INT=0)，或者其他的日期时间规则。

-- 按小时生成从1开始的单据号的函数/存储过程
CREATE OR REPLACE FUNCTION get_serialno_hour(_table_name TEXT, _field_name TEXT, _year INT=0, _month INT=0, _date INT=0, _hour INT=-1)
  RETURNS TEXT AS
$BODY$
  DECLARE _sid int;
  DECLARE _current_no int8;
  DECLARE _prefix_char TEXT;
  DECLARE _max_value int8;
  DECLARE _char_len int;
  DECLARE _no_format TEXT;
  DECLARE _serialDate TIMESTAMP;
  DECLARE _tmp TEXT;
  DECLARE _result_no TEXT;
BEGIN
  -- 日期的参数任意值为0，或者时间参数任意值为-1，则取当前日期
  IF (_year=0) OR (_month=0) OR (_date=0) OR (_hour=-1) THEN
    --_serialDate := current_date;
    --_serialDate := current_timestamp;
    -- 截取到[小时]，即，从右侧开始给分和秒清零，2022-06-27 22:00:00
    -- 注意：这里决定最终效果为，按小时重新从1开始计数。
    _serialDate := date_trunc('hour', current_timestamp);
  ELSE
    _serialDate := to_date(_year||'-'||_month||'-'||_date|| ' '||_hour||':00:00', 'yyyy-mm-dd hh:mi:ss');
  END IF;
  
  -- 查询单据号自主维护表，通过 [for update] 对该行加排他锁(可以通过调整，修改行锁的范围，或改成表锁)
  SELECT sid, prefix_char, current_no, char_len, no_format
    FROM sys_serial_no 
   WHERE is_setting=0 AND use_table=_table_name AND use_field=_field_name 
     AND use_datetime=_serialDate
     FOR UPDATE
    INTO _sid, _prefix_char, _current_no, _char_len, _no_format;
  
  -- 若找到记录
  IF (_current_no IS NOT NULL) AND (_current_no > 0) THEN
    -- 通过CASE判断，若下一个id已超过max_value则重新从min_value开始计数
    -- 理论上不应该循环使用序列号，而应将max_value设置得尽可能大
    UPDATE sys_serial_no 
       SET current_no=CASE WHEN current_no+1 > max_value 
                      THEN min_value 
                      ELSE current_no+1 END
     WHERE sid=_sid;
  ELSE
    -- 若没有查到记录，则通过is_setting=1的记录新写一条
    INSERT INTO sys_serial_no(use_table, use_field, use_datetime,
           current_no, prefix_char, char_len, 
           min_value, no_format, max_value )
    SELECT _table_name, _field_name, _serialDate, 
           min_value+1, prefix_char, char_len, 
           min_value, no_format, max_value 
      FROM sys_serial_no 
     WHERE is_setting=1 AND use_table=_table_name AND use_field=_field_name 
		 ORDER BY use_datetime DESC LIMIT 1
     RETURNING sid, min_value INTO _sid, _current_no; 

    SELECT prefix_char, char_len, no_format
      FROM sys_serial_no 
     WHERE sid=_sid
      INTO _prefix_char, _char_len, _no_format;
  END IF;

  _result_no := _no_format;
  _result_no := replace(_result_no, '{prefix_char}', _prefix_char::TEXT);
  _result_no := replace(_result_no, '{year}', to_char(_serialDate, 'yyyy'));
  _result_no := replace(_result_no, '{month}', to_char(_serialDate, 'mm'));
  _result_no := replace(_result_no, '{date}', to_char(_serialDate, 'dd'));
  _result_no := replace(_result_no, '{hour}', to_char(_serialDate, 'hh24'));
  _result_no := replace(_result_no, '{minute}', to_char(_serialDate, 'mi'));
  _result_no := replace(_result_no, '{second}', to_char(_serialDate, 'ss'));
  _result_no := replace(_result_no, '{current_no}', LPAD(_current_no::TEXT, _char_len, '0')::TEXT);
  RETURN _result_no;
END
$BODY$
  LANGUAGE plpgsql VOLATILE
;