序列号的使用

建号

CREATE TABLE  id_sequence (
                              sequence_name VARCHAR(50) PRIMARY KEY,
                              next_value INT NOT NULL DEFAULT 1
) ;


INSERT INTO id_sequence (sequence_name, next_value) VALUES
                                                        ('company_id', 1),
                                                        ('person_id', 1)
ON DUPLICATE KEY UPDATE next_value = next_value;

写1

CREATE FUNCTION get_next_id(seq_name VARCHAR(50))
    RETURNS VARCHAR(20)
    READS SQL DATA
BEGIN
    DECLARE next_val INT;
    DECLARE next_id VARCHAR(20);

    -- 获取并递增序列值
    UPDATE id_sequence SET next_value = next_value + 1
    WHERE sequence_name = seq_name;

    -- 获取新的序列值
    SELECT next_value INTO next_val FROM id_sequence
    WHERE sequence_name = seq_name;

    -- 根据序列类型生成ID
    IF seq_name = 'company_id' THEN
        SET next_id = CONCAT('2022company', LPAD(next_val, 3, '0'));
    ELSEIF seq_name = 'person_id' THEN
        SET next_id = CONCAT('2022person', LPAD(next_val, 3, '0'));
    END IF;

    RETURN next_id;
END //

主要

DELIMITER //
CREATE TRIGGER after_tmp001_insert
    AFTER INSERT ON tmp001
    FOR EACH ROW
BEGIN
    DECLARE company_id VARCHAR(20);
    DECLARE company_exists INT;

    -- 检查公司是否已存在
    SELECT COUNT(*) INTO company_exists
    FROM company
    WHERE company_name = NEW.公司;

    -- 处理公司信息
    IF company_exists = 0 THEN
        -- 获取新的公司ID
        SET company_id = get_next_id('company_id');

        -- 插入公司表
        INSERT INTO company (ucompanyid, company_name, province, city)
        VALUES (company_id, NEW.公司, NEW.省份, NEW.城市);
    ELSE
        -- 获取已存在的公司ID
        SELECT ucompanyid INTO company_id
        FROM company
        WHERE company_name = NEW.公司
        LIMIT 1;
    END IF;

    -- 插入联系人表
    INSERT INTO persons (upersonid, ucompanyid, per_name, tel)
    VALUES (get_next_id('person_id'), company_id, NEW. 联系人, NEW.电话 );
END //
DELIMITER ;