mysql 同时向两张关联表插入数据

Question

create table teacher(
    id int(11) not null auto_increment,
    name varchar(10) not null,
    primary key(id)
)engine=innodb;

create table teacherCourse(
    teacherId int(11) not null,
    courseNum int(10) not null,
    courseName varchar(50) not null,
    constraint foreign key(teacherId) references staff(id) on delete cascade,
    primary key(teacherId, courseNum)
)engine=innodb;

我想在teacher表增加一条记录的同时也要为teacherCourse增加一条记录，问题是表1的id是自增的，能先获取刚插入自增的id然后作为表2的teacherId插入数据吗？

Answer 1

1.select (auto_increment-1) from information_scheme.tables where table_name='TableName'
2.select last_insert_id()

Answer 2

This depends on the data persistence layer framework you use. Generally speaking, it is OK.
For mybatis, you can check out these:
http://lavasoft.blog.51cto.com/62575/1384959/
http://my.oschina.net/u/1256344/blog/159703

The same goes for hibernate. Just search hibernate/Mybatis to get the auto-increment ID.

Answer 3

I use Hibernate less. I usually use Mybatis. Let me talk about how to use Mybatis.
In your teacher table structure, id is the primary key and is incremented by itself. This is how you configure it. In the mybatis xml file, you need to add

in front of insert

<selectKey resultType="java.lang.Long" order="AFTER"
            keyProperty="id">
            SELECT LAST_INSERT_ID()
</selectKey>

That’s it

Answer 4

last_insert_id() is one;
triggers can also be used,

create trigger `insert_teacherCourse` AFTER INSERT on `teacher`
for each row 
insert into teacherCourse(teacherId) values(NEW.id);

Written roughly, there are some things in teacherCourse that are not null and need to be inserted