mysql文本自动递增

DELIMITER //

CREATE PROCEDURE generate_unique_text(IN prefix VARCHAR(10), OUT unique_text VARCHAR(50))
BEGIN
    DECLARE last_id INT DEFAULT 0;
    DECLARE new_id INT DEFAULT 0;
    DECLARE result VARCHAR(50) DEFAULT '';

    -- 获取当前最大ID
    SELECT COALESCE(MAX(CAST(SUBSTRING(text_field, LENGTH(prefix) + 1) AS UNSIGNED)), 0) INTO last_id FROM your_table;

    -- 生成新的ID
    SET new_id = last_id + 1;
    SET result = CONCAT(prefix, LPAD(new_id, 10, '0'));

    -- 设置输出参数
    SET unique_text = result;
END //

DELIMITER ;

CALL generate_unique_text('ORD', @unique_text);
INSERT INTO your_table (text_field) VALUES (@unique_text);

DELIMITER //

CREATE TRIGGER trg_generate_unique_text
BEFORE INSERT ON your_table
FOR EACH ROW
BEGIN
    DECLARE last_id INT DEFAULT 0;
    DECLARE new_id INT DEFAULT 0;
    DECLARE result VARCHAR(50) DEFAULT '';

    -- 获取当前最大ID
    SELECT COALESCE(MAX(CAST(SUBSTRING(text_field, LENGTH('ORD') + 1) AS UNSIGNED)), 0) INTO last_id FROM your_table;

    -- 生成新的ID
    SET new_id = last_id + 1;
    SET result = CONCAT('ORD', LPAD(new_id, 10, '0'));

    -- 设置新值
    SET NEW.text_field = result;
END //

DELIMITER ;