MYSQL:在单个查询中批量插入/更新语句 [英] MYSQL: Batch insert/update statements in single query

问题描述

我需要进行批处理 MYSQL插入/更新.我可以使用批处理插入语句，但是当插入作为多个衬板使用时，它不会..同样，我也无法生成批处理更新.请参见下面的示例.

I need to do batch MYSQL insert/updates. I got batch insert statement to work, but when the insert comes as multiple one liners it does not.. Similarly I have not been able to generate a batch update. Please see examples below.

批处理插入语句有效

Batch insert statement works

$sql = "INSERT INTO `test` (`somefield`) VALUES ('test', 'test');";
db::statement($sql);

多个单独的插入语句不有效

Multiple separate insert statements NOT working

$sql = "INSERT INTO `test` (`somefield`) VALUES ('test'); INSERT INTO `test` (`somefield`) VALUES ('test');";
db::statement($sql);

SQLSTATE [42000]:语法错误或访问冲突:1064您的SQL语法有一个错误；请参阅第1914页的"SQLSTATE错误".在第1行(SQL)中，检查与您的MySQL服务器版本相对应的手册以获取正确的语法，以在'INSERT INTO test ( somefield )VALUES('test')'附近使用:INSERT INTO test ( somefield )VALUES('test'); INSERT INTO test ( somefield )VALUES('test');)

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO test (somefield) VALUES ('test')' at line 1 (SQL: INSERT INTO test (somefield) VALUES ('test'); INSERT INTO test (somefield) VALUES ('test');)

批量更新语句不起作用

$sql = "INSERT INTO 'flights' (`id`, `airline`) VALUES ('142832', 'BA') ON DUPLICATE KEY UPDATE `airline`=VALUES(`airline`);"
db::statement($sql);

1064-您的SQL语法有错误；检查与您的MySQL服务器版本相对应的手册以获取正确的语法，以在航班"( id ， airline )值附近使用("142832"，"BA")在第1行上的打开重复键"

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''flights' (id, airline) VALUES ('142832', 'BA') ON DUPLICATE KEY UP' at line 1

评论了多个Stackoverflow帖子-但我出了错

多个插入语句-单个查询中有多个SQL更新语句

批量更新声明- MySQL中的多个更新

在此感谢您的帮助-谢谢！

Would appreciate help on this - thanks!

推荐答案

这很奇怪.乍一看似乎批处理语句是错误的，应该可以正常工作.

This is weird. at a quick glance it appears that the batch statement is wrong, which is supposedly working.

在语义上正确的批处理语句将括号括起来，即每行数据，即.像这样:

A semantically correct batch statement would have the brackets separate each row of data, ie. like this:

INSERT INTO test 
VALUES 
  ('test1')
, ('test2')
, ('test3');

单独的插入语句看起来不错，但是，您的数据库驱动程序可能不支持其 statement 方法中的多个语句(大多数情况下不支持AFAIK).解决方法是从客户端开始事务，遍历语句数组并执行.然后，当所有语句执行时，如果没有错误，则提交，或回滚事务.不过第一种选择是更快的.

The separate insert statements look fine, however, your database driver might not support multiple statements in its statement method (most don't, AFAIK). The work around would be to start a transaction from your client, loop through the array of statements and execute. Then when all the statements execute, commit if there were no errors, or roll back the transaction. The first option is faster though.

更新语句不起作用，因为表名 flights 用单引号引起来.如果要引用模式/表/列标识符，请使用反引号，并为字符串值&保留单引号.日期，就像您在同一查询中的其他地方所做的一样.仅当数据库元素名称是保留字时才需要转义，但是将数据库元素命名为"into"，"user"等之类的做法则是不好的做法，应避免使用.

The update statement doesn't work because the tablename flights is quoted using single-quotes. If you want to quote schema / table / column identifiers, use back-ticks, and reserve single-quotes for string values & dates, as you have done elsewhere in the same query. It is only necessary to escape a database element name if it is a reserved word, but naming database elements things like 'into', 'user', etc. is bad practice and should be avoided.

INSERT INTO flights (`id`, `airline`) 
VALUES 
('142832', 'BA') 
ON DUPLICATE KEY UPDATE 
airline=VALUES(`airline`)

这篇关于MYSQL:在单个查询中批量插入/更新语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！