与MySQL中的Oracle序列等效 [英] Oracle sequences equivalent in MySQL

问题描述

我有一张类似于Oracle user_sequences的表.

I have following table similar to Oracle user_sequences.

我有序列前缀/后缀的逻辑，但是为了简单起见，我在这里略过了.

I have logic of sequence prefix/suffix something, but for simplicity, I'm skipping as matters less here.

create table my_seq(
min_value integer,
Max_value integer,
last_value integer,
increment_by tinyint,
customer_id integer);

假设当前表中有两条记录.

Assume in current table there are two records.

insert into my_seq(min_value,max_value,last_value,increment_by,customer_id) 
  values(1,99999999,1,1,'foo#',1),(1,999999999,100,1,'foo#',2);

我的foo表结构就像

create table foo(id Auto_increment,foo_number varchar(20),customer_id integer);

约束: 我不能使用MySQL AUTO_INCREMENT列，因为foo包含不同的客户数据，并且每个客户都可以选择foo_number自动生成或手动输入，并且如果客户选择了auto_generation，则应该有空白.因此，customer = 1选择了它，foo#应该是1,2,3,4，依此类推，不允许有空格.

Constrained: I can't use MySQL AUTO_INCREMENT columns as foo contains different customers data, and every customer could opt foo_number auto generation or manual entry and there should be gap if customer opted for auto_generation. So customer=1 has opted for it, foo# should be 1,2,3,4 etc, no gaps are allowed.

到目前为止，如果我的应用程序在single thread中运行，我们已经实现了自动递增逻辑.我们生成foo_number并将其与其他数据点一起填充在foo表中.

So far so good, with auto increment logic that we have implemented if my app runs in single thread. We generate foo_number and populate in foo table, along with other data points.

我只是做一个查询来获取下一个自动编号.

I simply do a query to get the next auto#.

select last_number from my_seq where customer_id=?;

读取#并更新记录.

update my_seq set last_number=last_number+increment_by where customer_id=?;

问题: 当多个并发会话尝试运行select last_number from my_seq...时，它将多次返回相同的foo_number.另外，由于应用程序端的限制和性能瓶颈，我无法在应用程序中强制执行单线程，因此需要在数据库端解决它.

Problem: When multiple concurrent session tries the run select last_number from my_seq..., it returns same foo_number multiple times. Also, I can't enforce single thread in application because of application side limitation and performance bottleneck, hence need to solve it in database side.

请提出建议，如何避免重复的数字?请帮忙，谢谢.

Please suggest, how I could avoid duplicate numbers? Please help, thanks in advance.

我做过Google，很多stackoverflow链接建议get_last_id()，如您所见，我无法使用它.

I did google, many stackoverflow links suggests get_last_id(), as you could see, I can't use it.

推荐答案

我能够通过组合@Akina和@RickJames的建议来解决此问题，谢谢你们的支持.

I was able to solve this problem by just combining suggestions of @Akina and @RickJames , thank you both for thier support.

create table my_seq(
min_value integer,
Max_value integer,
last_value integer,
increment_by tinyint,
customer_id integer)ENGINE = InnoDB;

此处ENGINE=InnoDB非常重要. 为了确保读取时锁定表级别，我将应用程序代码修改为:

Here ENGINE=InnoDB is very important. In order to make sure there is table level locking while reading, I have modified my app code to:

Auto-Commit=FALSE

然后

//very import to begin the transaction
begin;
select last_number from my_seq where customer_id=? FOR UPDATE;

Read the result in App.

update my_seq set last_number=last_number+1 where customer_id=?;
commit;

即使在有多个并发会话的情况下，这也会生成唯一的sequence number.

This was generating the unique sequence number even in case of multiple concurrent sessions.

我遇到了另一个问题，该解决方案减慢了我生成序列号的速度.我已经解决了通过索引customer_id启用行级锁而不是表级锁的问题.

I have faced another problem, that this solution has slowed down other are where I do generate sequence#. I have solved it enabling a row level lock instead of table level lock by indexing customer_id.

ALTER TABLE TABLE_NAME ADD INDEX (customer_id);

希望这对其他人有帮助.

Hope this will be help full to others.

这篇关于与MySQL中的Oracle序列等效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！