带有last_insert_id()的MySQL多行插入选择语句 [英] Mysql mulitple row insert-select statement with last_insert_id()

问题描述

好的.简而言之，我正在尝试执行INSERT SELECT，例如:

Ok. So the short of it is, I was trying to do an INSERT SELECT such as:

START TRANSACTION;  
INSERT INTO dbNEW.entity (commonName, surname)  
SELECT namefirst, namelast  
FROM dbOLD.user;  
SET @key = LAST_INSERT_ID();  
INSERT INTO dbNEW.user (userID, entityID, other)  
SELECT user_id, @key, other  
FROM dbOLD.user;  
COMMIT;

当然，@ key不会从每个插入中返回每个后续的LAST_INSERT_ID()，而是仅从最后一个插入返回ID.

Of course @key does not return each subsequent LAST_INSERT_ID() from each insert but the ID from only the last insert.

基本上，我会将旧的USER表拆分为一个ENTITY和USER，例如:

Basically, I'm splitting an old USER Table into an ENTITY and USER like:

 dbOLD.user
 +-------------+---------------------+------+-----+------------+----------------+
 | Field       | Type                | Null | Key | Default    | Extra          |
 +-------------+---------------------+------+-----+------------+----------------+
 | user_id     | int(10) unsigned    | NO   | PRI | NULL       | auto_increment |
 | namefirst   | varchar(20)         | NO   |     |            |                |
 | namelast    | varchar(20)         | NO   |     |            |                |
 | other       | varchar(10)         | NO   |     |            |                |
 +-------------+---------------------+------+-----+------------+----------------+


 dbNEW.user
 +-------------+---------------------+------+-----+------------+----------------+
 | Field       | Type                | Null | Key | Default    | Extra          |
 +-------------+---------------------+------+-----+------------+----------------+
 | userID      | int(10) unsigned    | NO   | PRI | NULL       | auto_increment |
 | entityID    | int(10) unsigned    | NO   | MUL | 0          |                |
 | other       | varchar(10)         | NO   |     |            |                |
 +-------------+---------------------+------+-----+------------+----------------+


 dbNEW.entity
 +--------------+---------------------+------+-----+------------+----------------+
 | Field        | Type                | Null | Key | Default    | Extra          |
 +--------------+---------------------+------+-----+------------+----------------+
 | entityID     | int(10) unsigned    | NO   | PRI | NULL       | auto_increment |
 | commonName   | varchar(20)         | NO   |     |            |                |
 | surname      | varchar(20)         | NO   |     |            |                |
 +--------------+---------------------+------+-----+------------+----------------+

我为什么要这样做?基本上，我有一个商店"实体，它将具有用户"公用的字段，例如地址和电话号码.因此，任何"ENTITY"都可能没有多个地址(送货，帐单，邮寄)，也没有多个电话号码(传真，主，帐单，手机，家)，可能有其他方法可以做到这一点，但这是我的解决方案结束了.

Why would I want to do this? Basically, I have a "STORE" entity which is going to have fields common to "USERS" such as address and phone number. So any "ENTITY" might have none to multiple addresses (shipping, billing, mailing) and none to multiple phone numbers (fax, main, billing, cell, home) There may be other ways to accomplish this, but this is the solution I ended up with.

旧数据库中的商店和用户需要保留其旧PK，并获得额外的ENTITY fk.如何在不进行转储和手动编辑的情况下做到这一点?

The STOREs and USERS from the old db need to keep their old PKs and gain an additional ENTITY fk. How can I do this without making a dump and manually editing it?

推荐答案

对于最后一个查询，请使用此

For the last query, use this

INSERT INTO dbNEW.`user` (userID, entityID, other)  
SELECT user_id, entityID, other
FROM
(
    SELECT user_id, @key + @rn entityID, other, @rn := @rn + 1
    FROM (select @rn:=0) x, dbOLD.`user`
    order by user_id
) y;

MySQL中的LAST_INSERT_ID()是批量创建的第一个ID，与SQL Server中的SCOPE_IDENTITY()不同，它是LAST ID.由于它是第一行，因此我们使用变量@rn递增每一行，从第一行的addition=0开始.

The LAST_INSERT_ID() in MySQL is the FIRST id created in a batch, unlike SCOPE_IDENTITY() in SQL Server which is the LAST id. Since it is the first, we increment each row using the variable @rn, starting at addition=0 for the first row.

这篇关于带有last_insert_id()的MySQL多行插入选择语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！