MySQL sql - 在一个查询中插入多个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3860280/
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
sql - insert into multiple tables in one query
提问by yossi
assuming that i have two tables, namesand phonesand i want to insert data from some input to the tables, in one query- How can it be done?
假设我有两个表，names并且phones我想在一个查询中将一些输入中的数据插入到表中 - 怎么做？
Please, if it can be done, explain the syntax.
如果可以，请解释语法。
采纳答案by OMG Ponies
MySQL doesn't support multi-table insertion in a single INSERT statement. Oracle is the only one I'm aware of that does, oddly...
MySQL 不支持在单个INSERT 语句中插入多表。奇怪的是，Oracle 是我所知道的唯一一个这样做的......
INSERT INTO NAMES VALUES(...)
INSERT INTO PHONES VALUES(...)
回答by Joshua Smith
You can't. However, you CAN use a transactionand have both of them be contained within one transaction.
你不能。但是，您可以使用事务并将它们都包含在一个事务中。
START TRANSACTION;
INSERT INTO table1 VALUES ('1','2','3');
INSERT INTO table2 VALUES ('bob','smith');
COMMIT;
回答by Oscar Zarrus
I had the same problem. I solve it with a for loop.
我有同样的问题。我用 for 循环解决了它。
Example:
例子：
If I want to write in 2 identicaltables, using a loop
如果我想写入 2 个相同的表，请使用循环
for x = 0 to 1
if x = 0 then TableToWrite = "Table1"
if x = 1 then TableToWrite = "Table2"
Sql = "INSERT INTO " & TableToWrite & " VALUES ('1','2','3')"
NEXT
either
任何一个
ArrTable = ("Table1", "Table2")
for xArrTable = 0 to Ubound(ArrTable)
Sql = "INSERT INTO " & ArrTable(xArrTable) & " VALUES ('1','2','3')"
NEXT
If you have a small query I don't know if this is the best solution, but if you your query is very big and it is inside a dynamical script with if/else/case conditions this is a good solution.
如果您有一个小查询，我不知道这是否是最好的解决方案，但是如果您的查询非常大并且它在具有 if/else/case 条件的动态脚本中，这是一个很好的解决方案。
回答by bdspice
Multiple SQL statements must be executed with the mysqli_multi_query()function.
必须使用该mysqli_multi_query()函数执行多个 SQL 语句。
Example (MySQLi Object-oriented):
示例（MySQLi 面向对象）：
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "INSERT INTO names (firstname, lastname)
VALUES ('inpute value here', 'inpute value here');";
$sql .= "INSERT INTO phones (landphone, mobile)
VALUES ('inpute value here', 'inpute value here');";
if ($conn->multi_query($sql) === TRUE) {
echo "New records created successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>
