I'm planning on using client provided UUID's as the primary key in several tables in a MySQL Database.
我计划使用客户端提供的UUID作为MySQL数据库中几个表的主键。
I've come across various mechanisms for storing UUID's in a MySQL database but nothing that compares them against each other. These include storage as:
我遇到过在MySQL数据库中存储UUID的各种机制，但是没有任何机制可以对它们进行比较。这些包括存储:
- BINARY(16)
- 二进制(16)
- CHAR(16)
- CHAR(16)
- CHAR(36)
- CHAR(36)
- VARCHAR(36)
- VARCHAR(36)
- 2 x BIGINT
- 2 x长整型数字
Are there any better options, how do the options compare against each other in terms of:
有更好的选择吗?在以下方面，这些选择之间的比较如何?
- storage size?
- 存储大小?
- query overhead? (index issues, joins etc.)
- 查询开销?(索引问题,连接等)
- ease of inserting and updating values from client code? (typically Java via JPA)
- 易于插入和更新客户端代码的值?(典型的Java通过JPA)
Are there any differences based on which version of MySQL your running, or the storage engine? We're currently running 5.1 and were planning on using InnoDB. I'd welcome any comments based on practical experience of trying to use UUIDs. Thanks.
你的运行的MySQL版本和存储引擎有什么不同吗?我们目前运行5.1版本，并计划使用InnoDB。我欢迎任何基于使用uuid的实践经验的评论。谢谢。
2 个解决方案
#1
1
I have used UUIDs for smart client online/offline storage and data synchronization and for databases that I knew would have to be merged at some point. I have always used char(36) or char(32)(no dashes). You get a slight performance gain over varchar and almost all databases support char. I have never tried binary or bigint. One thing to be aware of, is that char will pad with spaces if you do not use 36 or 32 characters. Point being, don't write a unit test that sets the ID of an object to "test" and then try to find it in the database. ;)
我使用uuid进行智能客户端在线/脱机存储和数据同步，以及数据库，我知道在某些时候必须合并它们。我一直使用char(36)或char(32)(没有破折号)。与varchar和几乎所有支持char的数据库相比，性能略有提高。我从来没有尝试过二进制或bigint。需要注意的一件事是，如果不使用36或32个字符，那么char会填充空格。要点在于，不要编写一个单元测试，该测试将对象的ID设置为“test”，然后尝试在数据库中找到它。,)
#2
3
I would go with storing it in a Binary(16) column, if you are indeed set on using UUIDs at all. something like 2x bigint would be quite cumbersome to manage. Also, i've heard of people reversing them because the start of the UUIDs on the same machine tend to be the same at the beginning, and the different parts are at the end, so if you reverse them, your indexes will be more efficient.
如果您确实开始使用uuid，我将把它存储在一个二进制(16)列中。像2x bigint这样的操作会很麻烦。同样，我也听说过有人逆转它们，因为在同一台机器上uid的开始往往在一开始是相同的，而不同的部分在最后，所以如果你逆转它们，你的索引会更有效。
Of course, my instinct says that you should be using auto increment integers unless you have a really good reason for using the UUID. One good reason is generating unique keys accross different databases. The other option is that you plan to have more records than an INT can store. Although not many applications really need things like this. THere is not only a lot of efficiency lost when not using integers for your keys, and it's also harder to work with them. they are too long to type in, and passing them around in your URLs make the URLs really long. So, go with the UUID if you need it, but try to stay away.
当然，我的直觉告诉我你应该使用自动递增整数，除非你有很好的理由使用UUID。一个很好的原因是在不同的数据库中生成唯一的键。另一种选择是计划拥有比INT所能存储的更多的记录。虽然并不是很多应用都需要这样的东西。如果不使用整数作为键，不仅会降低很多效率，而且使用整数也会更加困难。它们太长了，无法输入，在url中传递它们会使url非常长。所以，如果你需要UUID，就去吧，但要尽量远离它。
#1
1
I have used UUIDs for smart client online/offline storage and data synchronization and for databases that I knew would have to be merged at some point. I have always used char(36) or char(32)(no dashes). You get a slight performance gain over varchar and almost all databases support char. I have never tried binary or bigint. One thing to be aware of, is that char will pad with spaces if you do not use 36 or 32 characters. Point being, don't write a unit test that sets the ID of an object to "test" and then try to find it in the database. ;)
我使用uuid进行智能客户端在线/脱机存储和数据同步，以及数据库，我知道在某些时候必须合并它们。我一直使用char(36)或char(32)(没有破折号)。与varchar和几乎所有支持char的数据库相比，性能略有提高。我从来没有尝试过二进制或bigint。需要注意的一件事是，如果不使用36或32个字符，那么char会填充空格。要点在于，不要编写一个单元测试，该测试将对象的ID设置为“test”，然后尝试在数据库中找到它。,)
#2
3
I would go with storing it in a Binary(16) column, if you are indeed set on using UUIDs at all. something like 2x bigint would be quite cumbersome to manage. Also, i've heard of people reversing them because the start of the UUIDs on the same machine tend to be the same at the beginning, and the different parts are at the end, so if you reverse them, your indexes will be more efficient.
如果您确实开始使用uuid，我将把它存储在一个二进制(16)列中。像2x bigint这样的操作会很麻烦。同样，我也听说过有人逆转它们，因为在同一台机器上uid的开始往往在一开始是相同的，而不同的部分在最后，所以如果你逆转它们，你的索引会更有效。
Of course, my instinct says that you should be using auto increment integers unless you have a really good reason for using the UUID. One good reason is generating unique keys accross different databases. The other option is that you plan to have more records than an INT can store. Although not many applications really need things like this. THere is not only a lot of efficiency lost when not using integers for your keys, and it's also harder to work with them. they are too long to type in, and passing them around in your URLs make the URLs really long. So, go with the UUID if you need it, but try to stay away.
当然，我的直觉告诉我你应该使用自动递增整数，除非你有很好的理由使用UUID。一个很好的原因是在不同的数据库中生成唯一的键。另一种选择是计划拥有比INT所能存储的更多的记录。虽然并不是很多应用都需要这样的东西。如果不使用整数作为键，不仅会降低很多效率，而且使用整数也会更加困难。它们太长了，无法输入，在url中传递它们会使url非常长。所以，如果你需要UUID，就去吧，但要尽量远离它。