Mysql mulitple row insert-select语句，带有last_insert_id（）
好。所以缺点是，我试图做一个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;
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 | | | |
+--------------+---------------------+------+-----+------------+----------------+
没有找到相关结果
已邀请:
2 个回复
藐刚
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;开始。肺鬼耙扮群
DELIMITER $$ DROP PROCEDURE IF EXISTS MigrateUsers $$ CREATE PROCEDURE MigrateUsers () BEGIN DECLARE done INT DEFAULT 0; DECLARE user_id INT; DECLARE namefirst VARCHAR(20); DECLARE namelast VARCHAR(20); DECLARE other VARCHAR(10); DECLARE lid INT; /*Cursor looping over old users*/ DECLARE cur CURSOR FOR SELECT user_id, namefirst, namelast, other FROM dbOLD.user; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur; START TRANSACTION; read_loop: LOOP FETCH cur INTO user_id, namefirst, namelast, other; IF done THEN LEAVE read_loop; END IF; /*Insert entity part*/ INSERT INTO dbNEW.entity (commonName, surname) VALUES (namefirst, namelast); SET lid = LAST_INSERT_ID(); /*Insert user part*/ INSERT INTO dbNEW.user (userID, entityID, other) VALUES (user_id, lid, other); END LOOP; COMMIT; CLOSE cur; END$$ DELIMITER ;