MySQL:重复插入表

Question

给定一个书号 ID 和书籍副本数量，我想在表中插入与书籍副本数量一样多的行。这是我的无效解决方案:

DROP PROCEDURE IF EXISTS insert_into_book_copy_table;
CREATE PROCEDURE insert_into_book_copy_table (IN in_book_id INT,
                                              IN in_num     INT)
BEGIN

    SET @I = 1;
    SET @ACQUIRED = CURRENT_TIMESTAMP;

    WHILE I <= in_num DO 
        INSERT INTO book_copy (book_id, acquired) # The table has an
        VALUES (in_book_id, @ACQUIRED);           # auto-increment field
                                                  # which I didn't list here
        SET @I = @I + 1;
    END WHILE;

END$$

例如，如果给定的书号 ID 为 23，书籍副本数等于 8，则 book_copy 表应如下所示:

SELECT * FROM book_copy;
╔══════════════╦══════════╦═════════════════════╗
║ book_copy_id ║  book_id ║      acquired       ║
╠══════════════╬══════════╬═════════════════════╣
║            1 ║       23 ║ 2013-04-15 18:15:20 ║
║            2 ║       23 ║ 2013-04-15 18:15:20 ║
║            3 ║       23 ║ 2013-04-15 18:15:20 ║
║            4 ║       23 ║ 2013-04-15 18:15:20 ║
║            5 ║       23 ║ 2013-04-15 18:15:20 ║
║            6 ║       23 ║ 2013-04-15 18:15:20 ║
║            7 ║       23 ║ 2013-04-15 18:15:20 ║
║            8 ║       23 ║ 2013-04-15 18:15:20 ║
╚══════════════╩══════════╩═════════════════════╝

有更好的编码方法吗？

Answer 1

试试这个，作为单个 SQL 语句:-

INSERT INTO book_copy (book_id, acquired)
SELECT a.i+b.i*10+c.i*100 + 1, $BookId, NOW()
FROM integers a, integers b, integers c
WHERE a.i+b.i*10+c.i*100 < $NumberToInsert

依赖于具有单列(称为 i)、10 行、值从 0 到 9 的整数表。将其与自身连接两次给出从 0 到 999 的所有数字(可以轻松扩展)，因此可以在一个语句中最多插入 1000 本书。

编辑 - 如果您不想创建整数表，那么您可以对几个返回常量的子查询执行相同的操作:-

INSERT INTO book_copy (book_id, acquired)
SELECT a.i+b.i*10+c.i*100 + 1, $BookId, NOW()
FROM (SELECT 0 AS i UNION SELECT 1 AS i UNION SELECT 2 AS i UNION SELECT 3 AS i UNION SELECT 4 AS i UNION SELECT 5 AS i UNION SELECT 6 AS i UNION SELECT 7 AS i UNION SELECT 8 AS i UNION SELECT 9 AS i ) a,
(SELECT 0 AS i UNION SELECT 1 AS i UNION SELECT 2 AS i UNION SELECT 3 AS i UNION SELECT 4 AS i UNION SELECT 5 AS i UNION SELECT 6 AS i UNION SELECT 7 AS i UNION SELECT 8 AS i UNION SELECT 9 AS i ) b,
(SELECT 0 AS i UNION SELECT 1 AS i UNION SELECT 2 AS i UNION SELECT 3 AS i UNION SELECT 4 AS i UNION SELECT 5 AS i UNION SELECT 6 AS i UNION SELECT 7 AS i UNION SELECT 8 AS i UNION SELECT 9 AS i ) c
WHERE a.i+b.i*10+c.i*100 < $NumberToInsert