创建随机生成 ID 号的 MySQL 存储过程

DELIMITER //

CREATE PROCEDURE proc_randICNo(OUT out_icno CHAR(18))
BEGIN
    DECLARE j INT DEFAULT 0;
    DECLARE head VARCHAR(100) DEFAULT '000000,321102,321001,320112,320021';
    DECLARE yearsStart INT DEFAULT 1940;
    DECLARE yearsEnd INT DEFAULT 2005;
    DECLARE monthsStart INT DEFAULT 1;
    DECLARE monthsEnd INT DEFAULT 12;
    DECLARE content CHAR(10) DEFAULT '0123456789';

    SET @headd = SUBSTRING_INDEX(SUBSTRING_INDEX(head, ',', 1 + FLOOR(RAND() * (LENGTH(head) - LENGTH(REPLACE(head, ',', ''))))), ',', -1);
    SET @yeard = CAST(yearsStart + FLOOR(RAND() * (yearsEnd - yearsStart + 1)) AS CHAR(4));
    SET @monthd = LPAD(CAST(monthsStart + FLOOR(RAND() * (monthsEnd - monthsStart + 1)) AS CHAR(2)), 2, '0');
    SET @dayd = LPAD(CAST(FLOOR(1 + RAND() * 31) AS CHAR(2)), 2, '0');
    SET @idcard = CONCAT(@headd, @yeard, @monthd, @dayd);

    SET j = 1; -- Initialize j outside the nested WHILE loop

    WHILE j < 5 DO
        SET @idcard = CONCAT(@idcard, SUBSTRING(content, FLOOR(1 + RAND() * LENGTH(content)), 1));
        SET j = j + 1;
    END WHILE;

    -- 返回生成的身份证号码
    SET out_icno := @idcard;

    
END //

DELIMITER ;