MySQL实现雪花Id函数

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

CREATE FUNCTION `SnowId`() RETURNS bigint(20)

BEGIN

    DECLARE b_current_time BIGINT;

    DECLARE b_time_tick BIGINT;

    DECLARE i_work_id INT;

    DECLARE i_work_id_big_length INT;

    DECLARE i_seq_big_length INT;

    DECLARE f_random FLOAT;

    DECLARE b_res BIGINT;

    SET i_work_id = 1;

    SET i_work_id_big_length = 4;

    SET i_seq_big_length = 8;

    SET b_current_time = (REPLACE(UNIX_TIMESTAMP(CURRENT_TIMESTAMP(3)),'.','')) + 0;

    SET b_time_tick = b_current_time - 1582136402000;

    SET f_random = RAND();

    SET b_res = b_time_tick * POWER(2, i_work_id_big_length + i_seq_big_length) + i_work_id * POWER(2, i_seq_big_length) + (5 + round((POWER(2, i_seq_big_length)-1) * f_random, 0));

    RETURN b_res;

END

1

2

3

4

5

6

7

8

9

10

11

CREATE PROCEDURE `TestSnowId` ()

BEGIN

    DECLARE i INT DEFAULT 1;

    CREATE TEMPORARY TABLE IF NOT EXISTS temp_numbers ( number BIGINT );

    WHILE i <= 5000 DO

        INSERT INTO temp_numbers ( number ) VALUES (SnowId ());

        SET i = i + 1;

    END WHILE;

    SELECT * FROM temp_numbers;

    DROP TEMPORARY TABLE IF EXISTS temp_numbers;

END

1

SELECT SnowId();

1

CALL TestSnowId();