好.所以缺點(diǎn)是,我試圖做一個(gè)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;

當(dāng)然@key不會從每個(gè)插入返回每個(gè)后續(xù)的LAST_INSERT_ID(),而只返回最后一個(gè)插入的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 | | | |

+--------------+---------------------+------+-----+------------+----------------+

我為什么要這樣做？基本上,我有一個(gè)“STORE”實(shí)體,它將擁有“USERS”共有的字段,例如地址和電話號碼.因此,任何“ENTITY”可能沒有多個(gè)地址(運(yùn)輸,計(jì)費(fèi),郵寄),也沒有多個(gè)電話號碼(傳真,主要,小區(qū),家庭)可能有其他方法可以實(shí)現(xiàn)這一點(diǎn),但這是我的解決方案結(jié)束了.

來自舊數(shù)據(jù)庫的STORE和USERS需要保留舊的PK并獲得額外的ENTITY fk.如何在不進(jìn)行轉(zhuǎn)儲和手動編輯的情況下執(zhí)行此操作？

總結(jié)

以上是生活随笔為你收集整理的mysql 选择插入语句_带有last_insert_id()的Mysql多行插入 – 选择语句的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。