I'm looking for the best way to basically get a unique number (guaranteed, not some random string or current time in milliseconds & of a reasonable length about 8 characters) using MySQL (or other ways suggestions welcome).
我正在寻找最好的方法来基本上获得一个唯一的数字(保证,而不是一些随机字符串或当前时间,以毫秒为单位,合理长度约8个字符)使用MySQL(或其他方式建议欢迎)。
I just basically want to run some SELECT ... statement and have it always return a unique number with out any inserting into database. Just something that increments some stored value and returns the value and can handle a lot of requests concurrently, without heavy blocking of the application.
我只是基本上想要运行一些SELECT ...语句并让它总是返回一个唯一的数字,而不是任何插入数据库。只是增加一些存储值并返回值的东西,可以同时处理大量请求,而不会严重阻塞应用程序。
I know that I can make something with combinations of random numbers with higher bases (for shorter length), that could make it very unlikely that they overlap, but won't guarantee it.
我知道我可以使用具有更高基数(更短的长度)的随机数组合来制作某些东西,这可能使它们不太可能重叠,但不能保证它。
It just feels like there should be some easy way to get this.
感觉应该有一些简单的方法来获得这个。
To clarify...
I need this number to be short as it will be part of a URL and it is ok for the query to lock a row for a short period of time. What I was looking for is maybe some command that underhood does something like this ...
我需要这个数字很短,因为它将成为URL的一部分,查询可以在短时间内锁定一行。我正在寻找的可能是一些命令,在发动机罩做这样的事情......
LOCK VALUE
RETURN VALUE++
UNLOCK VALUE
Where the VALUE is stored in the database, a MySQL database maybe.
如果VALUE存储在数据库中,则可能是MySQL数据库。
4 个解决方案
#1
3
You seek UUID().
你寻求UUID()。
http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_uuid
mysql> SELECT UUID();
-> '6ccd780c-baba-1026-9564-0040f4311e29'
It will return a 128-bit hexadecimal number. You can munge as necessary.
它将返回128位十六进制数。你可以根据需要进行咀嚼。
#2
1
Is the unique number to be associated with a particular row in a table? If not, why not call rand(): select rand(); The value returned is between zero and one, so scale as desired.
是否与表中的特定行关联的唯一编号?如果没有,为什么不调用rand():select rand();返回的值介于0和1之间,因此可根据需要进行缩放。
#3
1
Great question.
Shortest answer - that is simply not possible according to your specifications.
最短的答案 - 根据您的规格,根本不可能。
Long answer - the closest approach to this is MySQL's UUID but that is neither short, nor is sortable (ie: a former UUID value to be greater/smaller than a previous one).
答案很长 - 最接近它的方法是MySQL的UUID,但它既不短,也不可排序(即:以前的UUID值大于/小于前一个)。
To UUID or not to UUID? is a nice article describing pros and cons regarding their usage, touching also some of the reasons of why you can't have what you need
是UUID还是UUID?是一篇很好的文章,描述了它们的用法的优缺点,也触及了为什么你不能拥有你需要的一些原因
#4
1
I am not sure I understand exactly, maybe something like this:
我不确定我是否完全理解,也许是这样的:
SELECT ROUND(RAND() * 123456789) as id
The larger you make the number, the larger your id.
数字越大,你的身份就越大。
No guarantees about uniqueness of course, this is a quick hack after all and you should implement a check in code to handle the off chance a duplicate is inserted, but maybe this would serve your purpose?
当然不能保证唯一性,毕竟这是一个快速的黑客攻击,你应该实现一个检查代码来处理插入重复的机会,但这可能会达到你的目的吗?
Of course, there are many other approaches possible to do this.
当然,还有许多其他方法可以做到这一点。
You can easily use most any scripting language to generate this for you, php example here:
你可以轻松地使用大多数脚本语言为你生成这个,例如:
//Generates a 32 character identifier that is extremely difficult to predict.
$id = md5(uniqid(rand(), true));
//Generates a 32 character identifier that is extremely difficult to predict.
$id = md5(uniqid(rand(), true));
Then use $id in your query or whatever you need your unique id in. In my opinion, the advantage of doing this in a scripting language when interacting with a DB is that it is easier to validate for application / usage purposes and act accordingly. For instance, in your example, whatever method you use, if you wanted to be 100% always sure of data integrity, you have to make sure there are no duplicates of that id elsewhere. This is easier to do in a script than in SQL.
然后在您的查询中使用$ id或者您需要的唯一ID。在我看来,在与数据库交互时以脚本语言执行此操作的优点是,更容易验证应用程序/使用目的并采取相应措施。例如,在您的示例中,无论您使用何种方法,如果您希望100%始终确保数据完整性,您必须确保其他地方没有该ID的重复项。这在脚本中比在SQL中更容易。
Hope that helps my friend, good-luck!
希望能帮助我的朋友,祝你好运!
#1
3
You seek UUID().
你寻求UUID()。
http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_uuid
mysql> SELECT UUID();
-> '6ccd780c-baba-1026-9564-0040f4311e29'
It will return a 128-bit hexadecimal number. You can munge as necessary.
它将返回128位十六进制数。你可以根据需要进行咀嚼。
#2
1
Is the unique number to be associated with a particular row in a table? If not, why not call rand(): select rand(); The value returned is between zero and one, so scale as desired.
是否与表中的特定行关联的唯一编号?如果没有,为什么不调用rand():select rand();返回的值介于0和1之间,因此可根据需要进行缩放。
#3
1
Great question.
Shortest answer - that is simply not possible according to your specifications.
最短的答案 - 根据您的规格,根本不可能。
Long answer - the closest approach to this is MySQL's UUID but that is neither short, nor is sortable (ie: a former UUID value to be greater/smaller than a previous one).
答案很长 - 最接近它的方法是MySQL的UUID,但它既不短,也不可排序(即:以前的UUID值大于/小于前一个)。
To UUID or not to UUID? is a nice article describing pros and cons regarding their usage, touching also some of the reasons of why you can't have what you need
是UUID还是UUID?是一篇很好的文章,描述了它们的用法的优缺点,也触及了为什么你不能拥有你需要的一些原因
#4
1
I am not sure I understand exactly, maybe something like this:
我不确定我是否完全理解,也许是这样的:
SELECT ROUND(RAND() * 123456789) as id
The larger you make the number, the larger your id.
数字越大,你的身份就越大。
No guarantees about uniqueness of course, this is a quick hack after all and you should implement a check in code to handle the off chance a duplicate is inserted, but maybe this would serve your purpose?
当然不能保证唯一性,毕竟这是一个快速的黑客攻击,你应该实现一个检查代码来处理插入重复的机会,但这可能会达到你的目的吗?
Of course, there are many other approaches possible to do this.
当然,还有许多其他方法可以做到这一点。
You can easily use most any scripting language to generate this for you, php example here:
你可以轻松地使用大多数脚本语言为你生成这个,例如:
//Generates a 32 character identifier that is extremely difficult to predict.
$id = md5(uniqid(rand(), true));
//Generates a 32 character identifier that is extremely difficult to predict.
$id = md5(uniqid(rand(), true));
Then use $id in your query or whatever you need your unique id in. In my opinion, the advantage of doing this in a scripting language when interacting with a DB is that it is easier to validate for application / usage purposes and act accordingly. For instance, in your example, whatever method you use, if you wanted to be 100% always sure of data integrity, you have to make sure there are no duplicates of that id elsewhere. This is easier to do in a script than in SQL.
然后在您的查询中使用$ id或者您需要的唯一ID。在我看来,在与数据库交互时以脚本语言执行此操作的优点是,更容易验证应用程序/使用目的并采取相应措施。例如,在您的示例中,无论您使用何种方法,如果您希望100%始终确保数据完整性,您必须确保其他地方没有该ID的重复项。这在脚本中比在SQL中更容易。
Hope that helps my friend, good-luck!
希望能帮助我的朋友,祝你好运!