带有last_insert_id()的Mysql多行插入 – 选择语句

好.以是弱点是,我试图做一个INSERT SELECT,譬喻：

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.

根基上,我将旧的USER表拆分为ENTITY和USER,如：

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   |     |            |                |
 +--------------+---------------------+------+-----+------------+----------------+

我为什么要这样做？根基上,我有一个“STORE”实体,它将拥有“USERS”共有的字段,譬喻地点和电话号码.因此,任何“ENTITY”也许没有多个地点(运输,计费,邮寄),也没有多个电话号码(传真,首要,小区,家庭)也许有其他要领可以实现这一点,但这是我的办理方案竣事了.

来自旧数据库的STORE和USERS必要保存旧的PK并得到特另外ENTITY fk.如安在不举办转储和手动编辑的环境下执行此操纵？

办理要领

INSERT INTO dbNEW.`user` (userID,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()差异,后者是最后一个ID.因为它是第一行,我们行使变量@rn递增每一行,从第一行的加法= 0开始.

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!