-- insert into select -將一個(gè)表的數(shù)據(jù)添加到另外一個(gè)表中
insert into table_name2(id, project_code)
select 雪花算法id, project_code from table_name1 where type = 1;

SET @last_timestamp = -1; -- 初始化全局變量
SET @sequence = 0; -- 初始化全局變量

DROP FUNCTION IF EXISTS generate_snowflake_id;
DELIMITER //
CREATE FUNCTION generate_snowflake_id() RETURNS BIGINT
    READS SQL DATA
BEGIN
    DECLARE timestamp BIGINT;
    DECLARE machine_id BIGINT DEFAULT 1; -- 假設(shè)機(jī)器 ID 為 1
    DECLARE data_center_id BIGINT DEFAULT 0; -- 假設(shè)數(shù)據(jù)中心 ID 為 0
    DECLARE epoch BIGINT DEFAULT 1288834974657;
    -- 2010-01-01 00:00:00 UTC 的毫秒數(shù)

    -- 獲取當(dāng)前時(shí)間戳（毫秒）
    SET timestamp = FLOOR(UNIX_TIMESTAMP(NOW(3)) * 1000) - epoch;

    -- 檢查當(dāng)前時(shí)間戳
    IF timestamp = @last_timestamp THEN
        SET @sequence = (@sequence + 1) % 4096; -- 在同一毫秒內(nèi)遞增序列號(hào)
    ELSE
        SET @sequence = 0; -- 不同毫秒重置序列號(hào)
    END IF;

    SET @last_timestamp = timestamp;
    -- 存儲(chǔ)當(dāng)前時(shí)間戳

    -- 生成雪花 ID
    RETURN (timestamp << 22) | (data_center_id << 17) | (machine_id << 12) | @sequence;
END //
DELIMITER ;

-- 測(cè)試生成 Snowflake ID
SELECT generate_snowflake_id();

insert into table_name2(id, project_code)
select generate_snowflake_id(), project_code from table_name1 where type = 1;