使用 PHP 在一个语句中执行多个 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13980803/
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
Executing multiple SQL queries in one statement with PHP
提问by John Smith
How to join those multiple queries into one (can I?)
如何将这些多个查询合并为一个（我可以吗？）
$query = "DELETE FROM aktywne_kody WHERE kodsms ='$kodSMSgracza' AND typkodu ='$id'";
mysql_query($query) or die(mysql_error());
$query = "INSERT INTO uzyte_kody (gracz, kodsms, typkodu) VALUES ('$nickGracza', '$kodSMSgracza', '$id')";
mysql_query($query) or die("B??d MySQL X04");
$query = "INSERT INTO do_odebrania (gracz, itemDATA, itemQTY) VALUES ('$nickGracza', '$itemDATA', '$itemQTY')";
mysql_query($query) or die("B??d MySQL X05");
By the way is it better if I do mysql_close($db) after all queries are done?
顺便说一句，如果我在所有查询完成后执行 mysql_close($db) 会更好吗？
回答by Husni
Pass 65536to mysql_connectas 5th parameter.
传递65536到mysql_connect作为第五个参数。
Example:
例子：
$conn = mysql_connect('localhost','username','password', true, 65536 /* here! */)
or die("cannot connect");
mysql_select_db('database_name') or die("cannot use database");
mysql_query("
INSERT INTO table1 (field1,field2) VALUES(1,2);
INSERT INTO table2 (field3,field4,field5) VALUES(3,4,5);
DELETE FROM table3 WHERE field6 = 6;
UPDATE table4 SET field7 = 7 WHERE field8 = 8;
INSERT INTO table5
SELECT t6.field11, t6.field12, t7.field13
FROM table6 t6
INNER JOIN table7 t7 ON t7.field9 = t6.field10;
-- etc
");
When you are working with mysql_fetch_* or mysql_num_rows, or mysql_affected_rows, only the first statement is valid.
当您使用 mysql_fetch_* 或 mysql_num_rows 或 mysql_affected_rows 时，只有第一条语句有效。
For example, the following codes, the first statement is INSERT, you cannot execute mysql_num_rows and mysql_fetch_*. It is okay to use mysql_affected_rows to return how many rows inserted.
比如下面的代码，第一条语句是INSERT，不能执行mysql_num_rows和mysql_fetch_*。使用mysql_affected_rows 返回插入的行数是可以的。
$conn = mysql_connect('localhost','username','password', true, 65536) or die("cannot connect");
mysql_select_db('database_name') or die("cannot use database");
mysql_query("
INSERT INTO table1 (field1,field2) VALUES(1,2);
SELECT * FROM table2;
");
Another example, the following codes, the first statement is SELECT, you cannot execute mysql_affected_rows. But you can execute mysql_fetch_assoc to get a key-value pair of row resulted from the first SELECT statement, or you can execute mysql_num_rows to get number of rows based on the first SELECT statement.
再比如下面的代码，第一条语句是SELECT，不能执行mysql_affected_rows。但是你可以执行mysql_fetch_assoc来获取第一条SELECT语句产生的行键值对，或者你可以执行mysql_num_rows根据第一条SELECT语句获取行数。
$conn = mysql_connect('localhost','username','password', true, 65536) or die("cannot connect");
mysql_select_db('database_name') or die("cannot use database");
mysql_query("
SELECT * FROM table2;
INSERT INTO table1 (field1,field2) VALUES(1,2);
");
回答by Alupotha
This may be created sql injection point "SQL Injection Piggy-backed Queries". attackers able to append multiple malicious sql statements. so do not append user inputs directly to the queries.
这可能是创建 sql 注入点“SQL Injection Piggy-backed Queries”。攻击者能够附加多个恶意 sql 语句。所以不要将用户输入直接附加到查询中。
Security considerations
The API functions mysqli_query() and mysqli_real_query() do not set a connection flag necessary for activating multi queries in the server. An extra API call is used for multiple statements to reduce the likeliness of accidental SQL injection attacks. An attacker may try to add statements such as ; DROP DATABASE mysql or ; SELECT SLEEP(999). If the attacker succeeds in adding SQL to the statement string but mysqli_multi_query is not used, the server will not execute the second, injected and malicious SQL statement.
安全考虑
API 函数 mysqli_query() 和 mysqli_real_query() 不会设置激活服务器中的多查询所需的连接标志。对多条语句使用额外的 API 调用，以减少意外 SQL 注入攻击的可能性。攻击者可能会尝试添加诸如 ; 删除数据库 mysql 或 ; 选择睡眠（999）。如果攻击者成功将 SQL 添加到语句字符串中，但未使用 mysqli_multi_query，则服务器将不会执行第二条注入的恶意 SQL 语句。
回答by Jonas ?ppelgran
With mysqliyou're able to use multiple statements for real using mysqli_multi_query().
随着mysqli您能够使用多个语句来真正使用mysqli_multi_query().
回答by MayTheSchwartzBeWithYou
You can just add the word JOIN or add a ; after each line(as @pictchubbate said). Better this way because of readability and also you should not meddle DELETE with INSERT; it is easy to go south.
您可以添加单词 JOIN 或添加 ; 在每一行之后（如@pictchubbate 所说）。由于可读性，这种方式更好，而且您不应该干预 DELETE 和 INSERT；往南走很容易。
The last question is a matter of debate, but as far as I know yes you should close after a set of queries. This applies mostly to old plain mysql/php and not PDO, mysqli. Things get more complicated(and heated in debates) in these cases.
最后一个问题是一个有争议的问题，但据我所知，是的，您应该在一系列查询后关闭。这主要适用于旧的普通 mysql/php 而不是 PDO，mysqli。在这些情况下，事情变得更加复杂（并且在辩论中变得更加激烈）。
Finally, I would suggest either using PDO or some other method.
最后，我建议使用 PDO 或其他一些方法。
