代码之家 › 专栏 › 技术社区 › methuselah

一次插入300行的MySQL查询

mysql

methuselah · 技术社区 · 14 年前

我在MySQL上有一张叫租金的桌子

id, int (5), autoincrement PRIMARY KEY

dNo, int (5)

pRent, varchar (5)

status, varchar (10)

我需要在这张表中插入300行

ID和DNO需要匹配

所以最终我们将有ID/DNO,直到300。怎么能做到?

prent的默认值为0

状态的默认值为“空闲”

我应该使用什么SQL查询来同时插入ID/DNO自动增量高达300的所有300行?

4 回复 | 直到 14 年前

J V 14 年前

auto increment update table set dNo=id

dNo=id NULL

dNo id

INSERT INTO `test` (`id`, `id2`) VALUES ('2', `id`)

id=NULL

triggers

CREATE TRIGGER set_nDo AFTER INSERT ON test FOR EACH ROW SET `nDo`=`id`

methuselah 14 年前

   INSERT INTO
    myTable
    (
    nr
    )
SELECT
    SEQ.SeqValue
FROM
(
SELECT
    (HUNDREDS.SeqValue + TENS.SeqValue + ONES.SeqValue) SeqValue
FROM
    (
    SELECT 0  SeqValue
    UNION ALL
    SELECT 1 SeqValue
    UNION ALL
    SELECT 2 SeqValue
    UNION ALL
    SELECT 3 SeqValue
    UNION ALL
    SELECT 4 SeqValue
    UNION ALL
    SELECT 5 SeqValue
    UNION ALL
    SELECT 6 SeqValue
    UNION ALL
    SELECT 7 SeqValue
    UNION ALL
    SELECT 8 SeqValue
    UNION ALL
    SELECT 9 SeqValue
    ) ONES
CROSS JOIN
    (
    SELECT 0 SeqValue
    UNION ALL
    SELECT 10 SeqValue
    UNION ALL
    SELECT 20 SeqValue
    UNION ALL
    SELECT 30 SeqValue
    UNION ALL
    SELECT 40 SeqValue
    UNION ALL
    SELECT 50 SeqValue
    UNION ALL
    SELECT 60 SeqValue
    UNION ALL
    SELECT 70 SeqValue
    UNION ALL
    SELECT 80 SeqValue
    UNION ALL
    SELECT 90 SeqValue
    ) TENS
CROSS JOIN
    (
    SELECT 0 SeqValue
    UNION ALL
    SELECT 100 SeqValue
    UNION ALL
    SELECT 200 SeqValue
    UNION ALL
    SELECT 300 SeqValue
    UNION ALL
    SELECT 400 SeqValue
    UNION ALL
    SELECT 500 SeqValue
    UNION ALL
    SELECT 600 SeqValue
    UNION ALL
    SELECT 700 SeqValue
    UNION ALL
    SELECT 800 SeqValue
    UNION ALL
    SELECT 900 SeqValue
    ) HUNDREDS
) SEQ