MySQL 使用mysql中的限制更新多行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1513206/
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
update multiple rows using limit in mysql?
提问by halocursed
UPDATE messages set test_read =1
WHERE userid='xyz'
ORDER BY date_added DESC
LIMIT 5, 5 ;
I am trying to use this query to update a set of 5 rows using limit but mysql is showing an error..The one below is working
我正在尝试使用此查询使用 limit 更新一组 5 行，但 mysql 显示错误..下面的一个正在工作
UPDATE messages set test_read =1
WHERE userid='xyz'
ORDER BY date_added DESC
LIMIT 5 ;
why is the first one not working?
为什么第一个不起作用？
回答by Luká? Lalinsky
If you really must do it this way, you can use something like this:
如果你真的必须这样做，你可以使用这样的东西：
UPDATE messages SET test_read=1
WHERE id IN (
SELECT id FROM (
SELECT id FROM messages
ORDER BY date_added DESC
LIMIT 5, 5
) tmp
);
回答by Svetoslav Genov
http://bugs.mysql.com/bug.php?id=42415
http://bugs.mysql.com/bug.php?id=42415
The documentation states that any UPDATE statement with LIMIT clause is considered unsafe since the order of the rows affected is not defined: http://dev.mysql.com/doc/refman/5.1/en/replication-features-limit.html
However, if "ORDER BY PK" is used, the order of rows is defined and such a statement could be logged in statement format without any warning.
文档指出，任何带有 LIMIT 子句的 UPDATE 语句都被认为是不安全的，因为未定义受影响的行的顺序：http: //dev.mysql.com/doc/refman/5.1/en/replication-features-limit.html
但是，如果使用“ORDER BY PK”，则定义了行的顺序，这样的语句可以以语句格式记录而没有任何警告。
