mysql - 如何将多行插入到mysql中的两个相关表中？

Question

我想将许多行插入到两个相关表中。我使用 LAST_INSERTED_ID() 向第二个表中插入数据，但是这个 id 没有改变

BEGIN; 
insert into themes (tutorID, year, theme_name, degreeID) 
  select tutorID, year, work_name, degreeID from journal;
INSERT INTO assigned_students (studentID, tutorID, themeID, writing_language_id, work_typeID) 
  select studentID, tutorID, LAST_INSERT_ID(), 0, 4 from journal; 
COMMIT

Answer 1

试一试，声明一个 INT 然后设置该值，见下文。

BEGIN;

declare lastid INT;

insert into table1 (tutorID, year, name, degreeID)
  select sm.tutorID, year, namework, dt.degreeID from table3;

set lastid = (SELECT LAST_INSERT_ID());

INSERT INTO table2 (studentID, tutorID, table1ID, writing_language_id, work_typeID)
  select distinct StudentID, tutorID, lastid , 0, 4 from table3;
COMMIT;

你也可以试试这个。

BEGIN;

insert into table1 (tutorID, year, name, degreeID)
  select sm.tutorID, year, namework, dt.degreeID from table3;

INSERT INTO table2 (studentID, tutorID, table1ID, writing_language_id, work_typeID)
  select distinct StudentID, tutorID, (SELECT LAST_INSERT_ID()), 0, 4 from table3;
COMMIT;