1 创建序列

每个表都需要单独创建序列

• 创建序列的目的是用于快速生成snowflakeId.每次将新生成的snowflakeId保存在序列中,然后用于计算新的snowflakeId.
• 序列的命名规则:seq_”表名”_id

drop sequence if exists seq_table_id;
create sequence if not exists seq_table_id 
as bigint
	increment 1
    minvalue 0	
	maxvalue 9223372036854775807
	start 0
	cache 1;
--重置序列
select setval('seq_table_id'::regclass,0);
复制代码

2 创建生成snowflakeId函数

• generateId依赖序列
• generateId是公共函数,第一个参数为表的序列,第二个参数为机器编号(值范围为0-1023)

2.1 创建生成snowflakeId函数(uninx时间戳)

• 因为使用的是uninx时间戳,最多可以使用至2039-09-07 23:47:35.551+08

drop function if exists generateId(regclass,int);
create or replace function generateId(regclass,int)
    returns bigint
as $$
    --4095      =0x0000000000000FFF
    --4190208   =0x00000000003FF000
	select 
        (case when (sn>>22)=milliseconds and ((sn&4190208)>>12)=$2 then	
            setval($1,(milliseconds<<22)|($2<<12)|((sn&4095)))    --sn nextval时已经加1
        else
            setval($1,(milliseconds<<22)|($2<<12)|1)
        end)
    from (select nextval($1) as sn,(extract(epoch from clock_timestamp())*1000)::bigint as milliseconds) as tmp;
$$ language sql;
复制代码

2.2 创建生成snowflakeId函数(时间偏移从2021-01-01开始)

• 时间偏移从2021-01-01开始,最多可以使用至2090-09-07 15:47:35.551+08

drop function if exists generateIdOffset(regclass,int);
create or replace function generateIdOffset(regclass,int)
    returns bigint
as $$
	--1609430400000 =2021-01-01 00:00:00.000000
    --4095          =0x0000000000000FFF
    --4190208       =0x00000000003FF000
	select 
        (case when (sn>>22)=milliseconds and ((sn&4190208)>>12)=$2 then	
            setval($1,(milliseconds<<22)|($2<<12)|((sn&4095)))    --sn nextval时已经加1
        else
            setval($1,(milliseconds<<22)|($2<<12)|1)
        end)
    from (select nextval($1) as sn,(extract(epoch from clock_timestamp())*1000)::bigint-1609430400000  as milliseconds) as tmp;
$$ language sql;
复制代码

3 测试

snowflakeId生成速度约90个/毫秒

3.1 uninx时间戳

select 
	id,id>>22,(id&4190208)>>12,id&4095
from(
    select generateId('public.seq_table_id'::regclass,1) as id from generate_series(1,10000)
) as tmp;
复制代码

3.2 时间偏移从2021-01-01开始

select 
	id,1609430400000 +(id>>22),(id&4190208)>>12,id&4095
from(
    select  generateIdOffset('public.seq_table_id'::regclass,1) as id from generate_series(1,10000)
) as tmp;
复制代码