mysql 选择插入语句_带有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.如何在不进行转储和手动编辑的情况下执行此操作？