在MySQL 5.7中使用DECLARE语句的注意事项

在MySQL执行以下SQL报错

DELIMITER //
CREATE PROCEDURE generate_and_insert_data()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE j INT DEFAULT 1;
    DECLARE total_iterations INT DEFAULT 1000;
    WHILE i <= total_iterations DO
        -- 创建临时表用于存储生成的id和apply_no
        CREATE TEMPORARY TABLE temp_data (
            temp_id INT,
            temp_apply_no VARCHAR(50)
        );
        -- 生成100个不重复的id和apply_no
        WHILE j <= 100 DO
            -- 生成随机的id和apply_no
            DECLARE temp_id INT;
            DECLARE temp_apply_no VARCHAR(50);
            SET temp_id = FLOOR(RAND() * 1000000) + 1;
            SET temp_apply_no = CONCAT('APPLY_', temp_id);
            -- 检查生成的id和apply_no是否已存在
            WHILE EXISTS (SELECT * FROM temp_data WHERE temp_id = temp_id OR temp_apply_no = temp_apply_no) DO
                SET temp_id = FLOOR(RAND() * 1000000) + 1;
                SET temp_apply_no = CONCAT('APPLY_', temp_id);
            END WHILE;
            -- 将生成的id和apply_no插入到临时表中
            INSERT INTO temp_data (temp_id, temp_apply_no) VALUES (temp_id, temp_apply_no);
            SET j = j + 1;
        END WHILE;
        -- 将临时表中的数据插入到user_base表中
        INSERT INTO acc_loan.user_base (id, apply_no, project_no, phone, id_no, id_type, batch_date, car_no, created_date, due_bill_no, hash_key, last_modified_date, remark, sex, user_id, user_name, version)
        SELECT temp_id, temp_apply_no, 'WS10043190001', '15625172814', '511423199309110012', 'IDENTITY', CURDATE(), CONCAT('CAR_', temp_id), NOW(), temp_apply_no, MD5(CONCAT('IDNO_', temp_id)), NOW(), CONCAT('REMARK_', temp_id), IF(RAND() > 0.5, 'MALE', 'FEMALE'), CONCAT('USER_', temp_id), CONCAT('USER_NAME_', temp_id), NULL
        FROM temp_data;
        -- 删除临时表
        DROP TEMPORARY TABLE IF EXISTS temp_data;
        SET i = i + 1;
        SET j = 1;
    END WHILE;
END //
DELIMITER ;

错误信息如下：

> 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE temp_id INT;
              DECLARE temp_apply_no VARCHAR(50);
            ' at line 15

改正方法：

在MySQL 5.7中，DECLARE语句必须在BEGIN...END块之前进行声明。报错信息指出在DECLARE temp_id INT;语句附近存在语法错误。

为了解决这个问题，可以将DECLARE语句移动到BEGIN语句之前。

DELIMITER //
CREATE PROCEDURE generate_and_insert_data()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE j INT DEFAULT 1;
    DECLARE total_iterations INT DEFAULT 1000;
    DECLARE temp_id INT; -- 将DECLARE语句移动到BEGIN之前
    DECLARE temp_apply_no VARCHAR(50); -- 将DECLARE语句移动到BEGIN之前
    WHILE i <= total_iterations DO
        -- 创建临时表用于存储生成的id和apply_no
        CREATE TEMPORARY TABLE temp_data (
            temp_id INT,
            temp_apply_no VARCHAR(50)
        );
        -- 生成100个不重复的id和apply_no
        WHILE j <= 100 DO
            -- 生成随机的id和apply_no
            SET temp_id = FLOOR(RAND() * 1000000) + 1;
            SET temp_apply_no = CONCAT('APPLY_', temp_id);
            -- 检查生成的id和apply_no是否已存在
            WHILE EXISTS (SELECT * FROM temp_data WHERE temp_id = temp_id OR temp_apply_no = temp_apply_no) DO
                SET temp_id = FLOOR(RAND() * 1000000) + 1;
                SET temp_apply_no = CONCAT('APPLY_', temp_id);
            END WHILE;
            -- 将生成的id和apply_no插入到临时表中
            INSERT INTO temp_data (temp_id, temp_apply_no) VALUES (temp_id, temp_apply_no);
            SET j = j + 1;
        END WHILE;
        -- 将临时表中的数据插入到user_base表中
        INSERT INTO acc_loan.user_base (id, apply_no, project_no, phone, id_no, id_type, batch_date, car_no, created_date, due_bill_no, hash_key, last_modified_date, remark, sex, user_id, user_name, version)
        SELECT temp_id, temp_apply_no, 'WS10043190001', '15625172814', '511423199309110012', 'IDENTITY', CURDATE(), CONCAT('CAR_', temp_id), NOW(), temp_apply_no, MD5(CONCAT('IDNO_', temp_id)), NOW(), CONCAT('REMARK_', temp_id), IF(RAND() > 0.5, 'MALE', 'FEMALE'), CONCAT('USER_', temp_id), CONCAT('USER_NAME_', temp_id), NULL
        FROM temp_data;
        -- 删除临时表
        DROP TEMPORARY TABLE IF EXISTS temp_data;
        SET i = i + 1;
        SET j = 1;
    END WHILE;
END //
DELIMITER ;

修改后执行成功：