如何使用 MySQL 在带有外键的表中插入值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4775520/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to insert values in table with foreign key using MySQL?
提问by okami
I have these two tables just for example:
例如，我有这两个表：
TAB_TEACHER
- id_teacher // primary key, autoincrement
- name_teacher // a varchar
TAB_STUDENT
- id_student // primary key, autoincrement
- name_student // a varchar
- id_teacher_fk // foreign key reference to a teacher (TAB_TEACHER)
I want to know how to insert in these two cases:
我想知道如何在这两种情况下插入：
CASE 1 - INSERT a new Student with an pre-existing TEACHER, so I have to get the foreign key with a teacher name
案例 1 - 使用预先存在的教师插入一个新学生，所以我必须获得带有教师姓名的外键
CASE 2 - INSERT a new Student with a new TEACHER (the teacher I'm creating in the same time I'm creating the student)
案例 2 - 插入一个新学生和一个新老师（我在创建学生的同时创建的老师）
回答by Imre L
http://dev.mysql.com/doc/refman/5.0/en/insert-select.html
http://dev.mysql.com/doc/refman/5.0/en/insert-select.html
For case1:
对于案例1：
INSERT INTO TAB_STUDENT(name_student, id_teacher_fk)
SELECT 'Joe The Student', id_teacher
FROM TAB_TEACHER
WHERE name_teacher = 'Professor Hyman'
LIMIT 1
For case2 you just have to do 2 separate insert statements
对于 case2，您只需要执行 2 个单独的插入语句
回答by William Entriken
Case 1: Insert Row and Query Foreign Key
案例一：插入行并查询外键
Here is an alternate syntax I use:
这是我使用的替代语法：
INSERT INTO tab_student
SET name_student = 'Bobby Tables',
id_teacher_fk = (
SELECT id_teacher
FROM tab_teacher
WHERE name_teacher = 'Dr. Smith')
I'm doing this in Excel to import a pivot table to a dimension table and a fact table in SQL so you can import to both departmentand expensestables from the following:
我在Excel中这样做是为了导入数据透视表，在SQL维度表和事实表，所以你可以导入到两个department及expenses表从以下方面：
Case 2: Insert Row and Then Insert Dependant Row
案例 2：插入行然后插入从属行
Luckily, MySQL supports LAST_INSERT_ID()exactly for this purpose.
幸运的是，MySQLLAST_INSERT_ID()正好支持这个目的。
INSERT INTO tab_teacher
SET name_teacher = 'Dr. Smith';
INSERT INTO tab_student
SET name_student = 'Bobby Tables',
id_teacher_fk = LAST_INSERT_ID()
回答by Francis
Case 1
情况1
INSERT INTO tab_student (name_student, id_teacher_fk)
VALUES ('dan red',
(SELECT id_teacher FROM tab_teacher WHERE name_teacher ='jason bourne')
it is advisable to store your values in lowercase to make retrieval easier and less error prone
建议以小写形式存储您的值，以便检索更容易且不易出错
Case 2
案例二
INSERT INTO tab_teacher (name_teacher)
VALUES ('tom stills')
INSERT INTO tab_student (name_student, id_teacher_fk)
VALUES ('rich man', LAST_INSERT_ID())
