原 postgresql实现雪花算法

版权声明：本文为博主原创文章，请尊重他人的劳动成果，转载请附上原文出处链接和本声明。
本文链接：https://www.91mszl.com/Dream/article/details/1470

一：创建序列：在data模式下创建序列assign_id_seq

CREATE SEQUENCE "data"."assign_id_seq" 
INCREMENT 1
MINVALUE  1
MAXVALUE 99999999999999999
START 1
CACHE 1
CYCLE;
 
ALTER SEQUENCE "data"."assign_id_seq" OWNER TO "postgres";

二：创建雪花算法函数：在data模式下创建雪花算法函数snow_next_id

CREATE OR REPLACE FUNCTION "data"."snow_next_id"(OUT "result" int8)
  RETURNS "pg_catalog"."int8" AS $BODY$
DECLARE
   our_epoch bigint := 1314220021721;
 
   seq_id bigint;
   now_millis bigint;
   shard_id int := 5;
BEGIN
   seq_id := nextval('assign_id_seq') % 1024;
   SELECT FLOOR(EXTRACT(EPOCH FROM clock_timestamp()) * 1000) INTO now_millis;
   result := (now_millis - our_epoch) << 23;
   result := result | (shard_id << 10);
   result := result | (seq_id);
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
 
ALTER FUNCTION "data"."snow_next_id"(OUT "result" int8) OWNER TO "postgres";

三：使用

3.1）查看雪花算法效果

SELECT snow_next_id();

四：在表中使用雪花算法

4.1）创建表m_user

CREATE TABLE "data"."m_user" (
  "id" int8 NOT NULL,
  "name" varchar(30) COLLATE "pg_catalog"."default",
  "age" int4
)
;

ALTER TABLE "data"."m_user" ADD CONSTRAINT "m_user_pkey" PRIMARY KEY ("id");

4.2）往m_user表中插入数据

insert into data.m_user(id, name, age) values(snow_next_id(), '张无忌', 26);
insert into data.m_user(id, name, age) values(snow_next_id(), '赵敏', 24);

执行结果：