MySQL - 如何使用外键插入多个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4565195/
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
MySQL - How to insert into multiple tables with foreign keys
提问by Crayl
I'am new to MySQL, so please be nice :)
我是 MySQL 的新手，所以请保持友善:)
I would like to insert data from a php form into 3 different tables, which all have foreign keys. How can I write an insert command which updates all 3 tables at once, because if I try to update a table manually then I get an error because of the missing references. Do I have to deal with "NULL" entries and update every table after another or is it possible to solve this with one single command? Like MySQLi_Multi_Query?
我想将 php 表单中的数据插入 3 个不同的表中，这些表都有外键。如何编写一次更新所有 3 个表的插入命令，因为如果我尝试手动更新表，则会由于缺少引用而出现错误。我是否必须处理“NULL”条目并逐个更新每个表，还是可以用一个命令来解决这个问题？像 MySQLi_Multi_Query？
Thank you very much!
非常感谢！
采纳答案by Corith Malin
You're most likely going to have to insert things in order of their dependence. So if you have three tables (A, B, and C) we'll assume C depends on B and B depends on A. We'll also assume each table has primary keys AID, BID, and CID respectively.
您很可能必须按照它们的依赖顺序插入东西。因此，如果您有三个表（A、B 和 C），我们将假设 C 依赖于 B，B 依赖于 A。我们还将假设每个表分别具有主键 AID、BID 和 CID。
- You'd insert your row into A and get AID.
- Then you'd insert your row into B using the AID you got from step 1.
- Then you'd insert your row into C using the BID (and perhaps AID) you got from step 2 (and perhaps 1)
- 您将行插入 A 并获得 AID。
- 然后，您将使用从步骤 1 中获得的 AID 将您的行插入到 B 中。
- 然后，您将使用从步骤 2（也许是 1）中获得的 BID（也许是 AID）将您的行插入到 C 中
回答by Meeshal
You can do it in 3 Methods:
您可以通过 3 种方法进行操作：
First & Recommended. USING SELECT IN THE INSERT VALUE:
第一&推荐。在插入值中使用选择：
INSERT INTO user (name) VALUES ('John Smith'); INSERT INTO user_details (id, weight, height) VALUES ((SELECT id FROM user WHERE name='John Smith'), 83, 185);
INSERT INTO user (name) VALUES ('John Smith'); INSERT INTO user_details (id, weight, height) VALUES ((SELECT id FROM user WHERE name='John Smith'), 83, 185);
Second. USING LAST_INSERT_ID IN THE INSERT VALUE:
第二。在插入值中使用 LAST_INSERT_ID：
INSERT INTO a (id)
VALUES ('anything');
INSERT INTO user_details (id, weight, height)
VALUES (LAST_INSERT_ID(),83, 185);
Third. USING PHP SCRIPT
第三。使用 PHP 脚本
<?php
// Connecting to database
$link = mysql_connect($wgScriptsDBServerIP, $wgScriptsDBServerUsername, $wgScriptsDBServerPassword, true);
if(!$link || !@mysql_SELECT_db($wgScriptsDBName, $link)) {
echo("Cant connect to server");
exit;
}
// Values to insert
$name = 'John Smith';
$weight = 83;
$height = 185;
// insertion to user table
$sql = "INSERT INTO user (name) VALUES ('$name')";
$result = mysql_query( $sql,$conn );
// retrieve last id
$user_id = mysql_insert_id( $conn );
mysql_free_result( $result );
// insertion to user_details table
$sql = "INSERT INTO user_details (id, weight, height) VALUES ($user_id, $weight, $height)";
$result = mysql_query( $sql,$conn );
mysql_free_result( $result );
?>
回答by ChinChiller
There is an error in your syntax for the method 1.
方法 1 的语法有错误。
INSERT INTO user_details (id, weight, height)
VALUES (SELECT(id FROM user WHERE name='John Smith'), 83, 185);
should be
应该
INSERT INTO user_details (id, weight, height)
VALUES ((SELECT id FROM user WHERE name='John Smith'), 83, 185);
回答by bortunac
1) The sense of foreign_key is to associate a value in a field with a pre_existing value somewhere else. So you should make your inserts in a logic order.
1）foreign_key 的意义是将字段中的值与其他地方的预先存在的值相关联。所以你应该按照逻辑顺序插入。
2) If you want to avoid logical restrictions you should
2）如果你想避免逻辑限制，你应该
SET foreign_key_checks = 0 // disable key checks in server
INSERT ... // any order
INSERT ...
...
SET foreign_key_checks = 1
