同时读取和更新MySql表中的10条数据记录(元组)(使用单个查询)； [英] Reading and updating 10 data records (tuples) in MySql Table at the same time(Using a single query);

问题描述

我正在尝试从 MySql 表中读取 10 条记录并将字段 IsRead 更新为 1 以避免重复读取.所以当我再次读取数据时，应该读取接下来的 10 条记录，而不是使用 IsRead 读取的记录，

<块引用><块引用>

select * from tablename where IsRead=0 limit 10;

但我的问题是如何同时读取和更新 10 条记录.

使用单个查询.

编辑

以前我正在读取和更新一条记录，但现在我想避免读取时间(一次用于读取，一次用于更新)，那么读取和更新 10 条记录的合适方法是什么.不应读取重复记录.

解决方案

您要查找的不是单个语句，而是事务.

事务是使多个语句ACID 兼容的一种方式.在提供的链接中阅读它.简而言之，它的意思是全有或全无".

代码明智，它看起来就像这样:

开始交易；select * from tablename where IsRead=0 ORDER BY created_or_whatever_column limit 10 for update;更新表名设置 IsRead = 1 ORDER BY created_or_whatever_column LIMIT 10;犯罪;

注意，我添加了一个 order by 子句.在没有 order by 的情况下使用 limit 没有意义.除非您指定，否则数据库中的数据没有顺序.

此外，我在 select 语句中添加了 for update，因此行被锁定，直到事务结束(使用 commit)，这样就不会其他事务同时操作这些行.

在这种情况下，您还应该查看隔离级别.>

I am trying to read 10 records from a MySql table and updating a field IsRead to 1 to avoid the duplicate read. So when I again read the data then the next 10 record should be read not the already read records using IsRead,

select * from tablename where IsRead=0 limit 10;

But my Question is how can I read and update the 10 records at the same time.

Using a Single Query.

EDIT

Previously I am reading and updating one one records, but now I want to avoid the reading time (once for reading and once for updating) so what will be the suitable way to read and update 10 records. Duplicate record should not be read.

解决方案

What you're looking for is not a single statement, but transactions.

Transactions are a way to make multiple statements ACID compliant. Read about it in the link provided. In short it means, "all or nothing".

Code wise it would simply look something like this:

START TRANSACTION;
select * from tablename where IsRead=0 ORDER BY created_or_whatever_column limit 10 for update;
update tablename set IsRead = 1 ORDER BY created_or_whatever_column LIMIT 10;
COMMIT;

Notice, that I added an order by clause. Using limit without order by doesn't make sense. There's no order in the data in a database, unless you specify it.

Also I added for update to the select statement, so the rows are locked until the transaction ends (with commit) so that no other transaction manipulates these rows in the meantime.

What you should also have a look at in this context are the isolation levels.

这篇关于同时读取和更新MySql表中的10条数据记录(元组)(使用单个查询)；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！