I have a UUID string that I want to use as my MySQL tables primary key, the UUID is a 32 character hexadecimal string (after '-' characters are stripped). Due to the fact that it is better to use a numeric column (int) as your primary key in a database, I would like to convert this to an integer but not sure of the best way to handle it.
我有一个UUID字符串,我想用作我的MySQL表主键,UUID是一个32字符的十六进制字符串(在' - '字符被剥离后)。由于最好使用数字列(int)作为数据库中的主键,我想将其转换为整数,但不确定处理它的最佳方法。
- Due to the size of the string (ie. uuid='a822ff2bff02461db45ddcd10a2de0c2'), do I need to break this into multiple 'substrings'.
- I am running PHP on a 32 bit architecture at the moment so converting it within PHP will not work due to PHP_INT_MAX size (max 0xFFFFFFFF). And I suspect that would be the same restriction for MySQL.
- I do not like the idea of multiple primary keys as a fix for this, I would rather use a string representation even though that's not the preferred method.
由于字符串的大小(即.uuid ='a822ff2bff02461db45ddcd10a2de0c2'),我是否需要将其分解为多个“子字符串”。
我目前在32位架构上运行PHP,因此在PHP中转换它将不起作用,因为PHP_INT_MAX大小(最大0xFFFFFFFF)。而且我怀疑这对MySQL也是一样的限制。
我不喜欢多个主键作为修复的想法,我宁愿使用字符串表示,即使这不是首选方法。
I might be thinking about this all wrong, and am not against reading documentation, so either examples or suggested reading as a response would be acceptable.
我可能会认为这一切都是错误的,并且不反对阅读文档,因此无论是示例还是建议阅读作为回应都是可以接受的。
3 个解决方案
#1
26
For most cases it is best to store UUIDs/GUIDs as BINARY(16). See these related * questions:
对于大多数情况,最好将UUID / GUID存储为BINARY(16)。请参阅以下相关的*问题:
- UUID Performance in MySQL
- How to Store UUID as Number
MySQL中的UUID性能
如何将UUID存储为数字
The conversion can (and probably should) be done in MySQL instead of PHP so whether you're using a 32bit PHP client or 64bits doesn't matter a bit (pun intended :P)
转换可以(也可能应该)在MySQL而不是PHP中完成,因此无论您使用的是32位PHP客户端还是64位都无关紧要(双关语:P)
#2
1
Use a string type, not an integer. Better is only better if it solves a problem.
使用字符串类型,而不是整数。如果能解决问题,那就更好了。
If you're really concerned about lookup speed, use a synthetic (auto increment) primary key. You can place a unique constraint on the UUID column, and use it only once to look up the synthetic key which is subsequently used for your joins etc.
如果您真的关心查找速度,请使用合成(自动增量)主键。您可以在UUID列上放置一个唯一约束,并仅使用它一次查找随后用于连接的合成密钥等。
#3
0
It depends on storage engine also. TokuDB should handle all these problems.
这也取决于存储引擎。 TokuDB应该处理所有这些问题。
#1
26
For most cases it is best to store UUIDs/GUIDs as BINARY(16). See these related * questions:
对于大多数情况,最好将UUID / GUID存储为BINARY(16)。请参阅以下相关的*问题:
- UUID Performance in MySQL
- How to Store UUID as Number
MySQL中的UUID性能
如何将UUID存储为数字
The conversion can (and probably should) be done in MySQL instead of PHP so whether you're using a 32bit PHP client or 64bits doesn't matter a bit (pun intended :P)
转换可以(也可能应该)在MySQL而不是PHP中完成,因此无论您使用的是32位PHP客户端还是64位都无关紧要(双关语:P)
#2
1
Use a string type, not an integer. Better is only better if it solves a problem.
使用字符串类型,而不是整数。如果能解决问题,那就更好了。
If you're really concerned about lookup speed, use a synthetic (auto increment) primary key. You can place a unique constraint on the UUID column, and use it only once to look up the synthetic key which is subsequently used for your joins etc.
如果您真的关心查找速度,请使用合成(自动增量)主键。您可以在UUID列上放置一个唯一约束,并仅使用它一次查找随后用于连接的合成密钥等。
#3
0
It depends on storage engine also. TokuDB should handle all these problems.
这也取决于存储引擎。 TokuDB应该处理所有这些问题。