如果使用这个简单的算法,表中的所有数字都会 100% 唯一吗?
我的目标是每次在用户机器上发生事件时,使用ajax将一个新的、唯一的(唯一的非常重要)数字插入到服务器上的MySQL表中。
因此,服务器在用户事件上的部分是这样做的(使用php):
- 从数据库中的列中查找最大值,
- 将最大值加10,
- 这是一个新的且唯一的(大于最大值)值,我们插入插入到一个表中。
如果从 1 开始,所有数字都是唯一的,并且都是 1、11、21、31 吗?我很好奇插入表是否在开始执行另一个队列之前完成,并且可以像 1, 11, 21, 21, 31, 41 一样?
如果理论上它是这样工作的(按时间排序)
- ,则从第一个用户的列中查找最大值,
- 从第二个用户的列中查找最大值(它将是相同的),
- 为第一个用户插入一个(最大值+10)到同一个表中,
- 将第二个用户的(max+10)插入到同一个表中(它将是相同的),那么结果将是相同的,并且 1 值可以重复两次甚至更多......
所以,问题是:所有数字都是 100%吗 独特的? 根据这一点,我必须选择使用哪种算法来创建唯一的数字。
额外: 是否可以使用该算法来确定而不使用自动增量?自动增量用于另一列。数字之间的空洞是可以的。唯一的要求是数字应该不同,但有一些“增量”大于一。抱歉,我在问题中没有注意到这一点。谢谢。
My goal is to insert a new and unique(unique is very important) number into a MySQL table on the server every time upon an event on a user's machine, using ajax.
So, server's part on user's event is doing this (using php):
- Finds a maximum value from the column in the db,
- Adds 10 to a maximum value,
- this is a new and unique (bigger than a maximum) value, we insert insert into a table.
Will all numbers be unique and go like 1, 11, 21, 31, if it starts from 1? I'm curious if inserting into the Table finishes before it starts performing another queue and coule be like 1, 11, 21, 21, 31, 41?
If it theoretically works like this (ordered by time)
- find max value from the column for the first user
- find max a value from the column for the second user (it will be the same)
- insert a (max+10) for the first user into the same table
- insert a (max+10) for the second user into the same table (it will be the same), then the results will be the same, and 1 value could be repeated twice or even more...
So, the question is: will all numbers be 100% unique?
Depending on this I have to choose which algorithm to use for creating unique numbers.
Added:
Is it possible to be sure with this algorythm and without using autoincrements? Autoincrement is used for another column. Holes between numbers are OK. The only requirement is that numbers should be different, but with some "delta" that is more than one. Sorry I didn't notice about that in my question. Thank you.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
除非您有非常具体的理由反对它,否则我建议使用 AUTO_INCRMENT - 它会扩展很多比你的方法更好,并且实际上在数字序列中留下的“漏洞”更少。
你是对的 - 你的方法实际上并不能保证并发环境中的唯一性。让你的算法工作的一种方法是对你的字段有一个唯一的约束(如果它还不是主键),然后重复尝试插入一个新值 - 如果失败,只需生成一个新值并重试,它最终会成功。
Unless you have a very specific reason against it, I recommend using AUTO_INCREMENT - it will scale much better and actually leave fewer "holes" in the sequence of numbers than your approach.
And you are correct - your approach will not actually guarantee uniqueness in concurrent environment. One way to make your algorithm work is to have a UNIQUE constraint on your field (if it is not already PRIMARY KEY) and then repeatedly attempt to insert a new value - if it fails just generate a new value and try again and it will eventually succeed.
在数据库中使用自动递增列
Use an auto incrementing column in the db
听起来您想使用自动增量。
It sounds like you want to use autoincrement.
如果您在单独的列上使用自动增量,您仍然可以使用如下所示的内容来模拟它。
由于
insert
是一个事务性语句,ACID 数据库将确保max+1
技巧始终比当前最高值大 1。请记住,您需要稍微调整一下才能在空表上工作,因为在这种情况下查询将返回 NULL。这应该足够了:
这会在空表上强制将初始值设置为 1,否则它将使用下一个可用值。
If you're using auto-increment on a separate column, you can still emulate it with something like the following.
Because the
insert
is a transactional statement, ACID databases will ensure themax+1
trick is always one greater then the current top value.Keep in mind you'll need a slight adjustment for that to work on an empty table, since the query will return NULL in that case. This should suffice:
This forces the initial value to 1 on an empty table, otherwise it uses the next available value.