MySQL中REPEAT语句报错求助：批量插入300条重复数据失败

Hey there, let's break down why your query is throwing that syntax error and get those 300 rows inserted properly.

The Core Issue #

MySQL's REPEAT loop isn't something you can run as a standalone SQL statement—it's a control structure meant to be used inside stored procedures, functions, or triggers. That's why you're seeing the "not valid input at this position" error. On top of that, your code references Room Number which doesn't exist in your target table, so even if the syntax was right, that would cause another error.

Solution 1: Use a Stored Procedure with REPEAT #

We'll wrap the loop in a stored procedure, which is the proper way to use REPEAT in MySQL. Here's how:

First, create the procedure (we'll use a counter variable to track how many rows we've inserted):

DELIMITER //
CREATE PROCEDURE Insert300TestRows()
BEGIN
    DECLARE row_count INT DEFAULT 0;
    REPEAT
        -- Insert your test data
        INSERT INTO mysqltest.noderedtest (Password, Email) VALUES ('Test', 'email@email.com');
        -- Increment the counter
        SET row_count = row_count + 1;
    -- Stop when we've inserted 300 rows
    UNTIL row_count >= 300 END REPEAT;
END //
DELIMITER ;

Then call the procedure to run the insert:

CALL Insert300TestRows();

Solution 2: No Stored Procedure Needed (Recursive CTE) #

If you don't want to create a stored procedure, you can use a recursive Common Table Expression (CTE) to generate 300 rows and insert them all at once. This is often simpler for one-off bulk inserts:

WITH RECURSIVE number_sequence AS (
    SELECT 1 AS num
    UNION ALL
    SELECT num + 1 FROM number_sequence WHERE num < 300
)
INSERT INTO mysqltest.noderedtest (Password, Email)
SELECT 'Test', 'email@email.com' FROM number_sequence;

This works by generating a sequence of numbers from 1 to 300, then inserting your static values once for each number in the sequence—giving you exactly 300 identical rows.

Quick Notes #

• Make sure your mysqltest.noderedtest table actually has Password and Email columns (I'm assuming it does since you're targeting them).
• If you ever need to adjust the number of rows, just change the 300 value in either solution.

内容的提问来源于stack exchange，提问作者Liam Broughton