增量计数器mysql [英] incremental counter mysql

问题描述

我的问题很简单，但答案可能很棘手.

My question is pretty simple but answer might be tricky.

我在PHP中，我想手动管理对象的唯一ID. 棘手的是管理原子性.我不希望2个元素具有相同的ID.

I'm in PHP and I want to manage manually a unique ID for my objects. What is tricky is to manage atomicity. I dont want that 2 elements get the same ID.

元素"分组在组"中.在每个组中，我希望元素ID从1开始，并对该组中的每个插入进行递增.

"Elements" are grouped in "Groups". In each group I want elements ID starting from 1 and grow incrementally for each insert in that group.

我的第一个解决方案是在组"表中具有"lastID"列:

My first solution is to have a "lastID" column in the table "Groups" :

CREATE TABLE groups ( id INT AUTO_INCREMENT, lastId INT )
CREATE TABLE elements ( myId INT, multiple values ...)

为了避免许多具有相同ID的元素，我必须更新lastId并在原子SQL查询中选择它.

在那之后，我找到了一个唯一的ID，无法再次选择它，我可以插入我的元素.

After that, one retrieved, I have a unique ID that can't be picked again and I can insert my element.

我的问题是如何解决粗体部分?我的数据库是带有MyISAM引擎的MySQL，因此没有事务支持.

My question is how to solve the bold part ? My database is MySQL with MyISAM engine so there is no transaction support.

UPDATE groups 
SET lastId = lastId + 1 
WHERE id = 42

SELECT lastId 
FROM groups
WHERE id = 42

是否有比这两个请求更原子的东西?

Is there something more atomic than these 2 requests ?

谢谢

推荐答案

UPDATE groups SET lastId = last_insert_id(lastId + 1)

然后您可以使用来获取新的ID

and then you can get your new id with

SELECT last_insert_id()

将last_insert_id与参数一起使用将存储该值，并在以后调用它时将其返回.
这种生成自动编号的方法最适用于只有几行的MyISAM表(MyISAM始终锁定整个表).它还具有在事务期间不锁定表的优点(如果它是InnoDB表，则会发生此情况).

Using last_insert_id with a parameter will store the value and return it when you call it later.
This method of generating autonumbers works best with MyISAM tables having only a few rows (MyISAM always locks the entire table). It also has the benefit of not locking the table for the duration of the transaction (which will happen if it is an InnoDB table).

这来自 MySQL手册:

如果将expr用作LAST_INSERT_ID()的参数，则 参数由函数返回，并记为下一个 LAST_INSERT_ID()返回的值.这可以用来模拟 顺序:

If expr is given as an argument to LAST_INSERT_ID(), the value of the argument is returned by the function and is remembered as the next value to be returned by LAST_INSERT_ID(). This can be used to simulate sequences:

创建一个表来保存序列计数器并对其进行初始化:

Create a table to hold the sequence counter and initialize it:

CREATE TABLE sequence (id INT NOT NULL); 
INSERT INTO sequence VALUES (0); 

使用表格生成如下序列号:

Use the table to generate sequence numbers like this:

UPDATE sequence SET id=LAST_INSERT_ID(id+1);
SELECT LAST_INSERT_ID(); 

UPDATE语句增加序列计数器 并导致对LAST_INSERT_ID()的下一次调用返回更新的 价值. SELECT语句检索该值.这 mysql_insert_id()C API函数也可用于获取值. 参见第21.8.3.37节"mysql_insert_id()".

The UPDATE statement increments the sequence counter and causes the next call to LAST_INSERT_ID() to return the updated value. The SELECT statement retrieves that value. The mysql_insert_id() C API function can also be used to get the value. See Section 21.8.3.37, "mysql_insert_id()".

您可以生成序列而无需调用LAST_INSERT_ID()，但是 使用此功能的实用程序是ID值为 保留在服务器中，作为最后一个自动生成的值.它 是多用户安全的，因为多个客户端可以发出UPDATE 语句，并使用SELECT语句获取自己的序列值 (或mysql_insert_id())，而不会影响其他 生成自己的序列值的客户端.

You can generate sequences without calling LAST_INSERT_ID(), but the utility of using the function this way is that the ID value is maintained in the server as the last automatically generated value. It is multi-user safe because multiple clients can issue the UPDATE statement and get their own sequence value with the SELECT statement (or mysql_insert_id()), without affecting or being affected by other clients that generate their own sequence values.

这篇关于增量计数器mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！