将大型（UUID）作为MySQL表主键的最佳方式 [英] Best way to handle large (UUID) as a MySQL table primary key

问题描述

我有一个我想用作MySQL表主键的UUID字符串，UUID是一个32个字符的十六进制字符串（' - '字符被剥离后）。由于最好在数据库中使用数字列（int）作为主键，所以我希望将其转换为整数，但不能确定处理它的最佳方式。

1. 由于字符串的大小（即.uuid ='a822ff2bff02461db45ddcd10a2de0c2'），是否需要将其分解为多个'子字符串'。


2. 由于PHP_INT_MAX大小（最大0xFFFFFFFF），我现在在32位体系结构上运行PHP，所以在PHP内转换它不起作用。我怀疑这会对MySQL造成同样的限制。
   
3. 我不喜欢将多个主键作为解决方案，我宁愿使用字符串表示，尽管这不是首选的方法。

我可能会考虑全部错误，并不反对阅读文档，因此无论是示例还是建议阅读答案是可以接受的。

解决方案

对于大多数情况，最好将UUID / GUID存储为 BINARY （16）。查看这些相关的StackOverflow问题：

• UUID在MySQL中的性能


• 如何将UUID存储为数字

转换可以（也可能应该）在MySQL中完成而不是PHP，因此无论你使用的是32位PHP客户端还是64位都没有关系（双关意图：P）

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.

1. Due to the size of the string (ie. uuid='a822ff2bff02461db45ddcd10a2de0c2'), do I need to break this into multiple 'substrings'.
2. 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.
3. 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.

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.

解决方案

For most cases it is best to store UUIDs/GUIDs as BINARY(16). See these related StackOverflow questions:

• UUID Performance in MySQL
• How to Store UUID as Number

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)

这篇关于将大型（UUID）作为MySQL表主键的最佳方式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！