I have created new application. For this project I built a login page. In the table storing user's information, I'm looking for one column that will keep user ID. This ID should be unique for each user in the system. While looking around on the internet, I found a few suggestions but each of them had different problems.
我已经创建了新的应用程序。对于这个项目，我建立了一个登录页面。在存储用户信息的表中，我在寻找一个列来保存用户ID，这个ID应该对系统中的每个用户都是唯一的。当我在网上搜索的时候，我发现了一些建议，但是每一个都有不同的问题。
In my project I am usin ColdFusion 2016 and Microsoft SQL 2008. Originally I was going to use NEWID() in SQL to generate the key for each user, but there are a few things that I do not like about this solution.
在我的项目中，我是usin ColdFusion 2016和Microsoft SQL 2008。最初，我打算在SQL中使用NEWID()来为每个用户生成密钥，但是有一些事情我不喜欢这个解决方案。
-
First, the ID is way too long and I wouldn't want my users to have to enter such a long value when they search.
首先，ID太长了，我不希望我的用户在搜索时输入这么长的值。
-
Second, I read that this can affect the performance of the query and slow down my database.
其次，我读到这可能会影响查询的性能并降低数据库的速度。
Then I was thinking about using auto-increment id. This solution is too simple for system ID in my opinion and maybe will cause some conflicts in the future. For example if table has to be recreated I'm not sure that the ID's will remain the same. So I'm wondering what is the best practice to create unique ID's for the system? Should I use ColdFusion CreateUUID()? Again this is way too long in my opinion.
然后我在考虑使用自动递增id，我认为这个解决方案对于系统id来说太简单了，将来可能会引起一些冲突。例如，如果必须重新创建表，我不确定ID是否保持不变。我想知道为系统创建唯一ID的最佳实践是什么?我应该使用ColdFusion CreateUUID()吗?在我看来，这太长了。
If anyone knows the best practice or have any examples of how this can be achieved please let me know. Thank you!
如果有人知道最佳实践或有任何例子可以实现这一点，请让我知道。谢谢你！
1 个解决方案
#1
5
As with most other things IT DEPENDS.
就像大多数其他事情一样，这要看情况而定。
Something that seems like it should be such an easy issue can get quite complex pretty quickly. And INT or even BIGINT is significantly smaller than a GUID, but with only 300k or so records, it's not going to have a huge impact on size, but a GUID as your PRIMARY KEY WILL cause significant fragmentation. That will definitely affect your index and could cause issues with lookups if you need max performance.
一些看似很容易解决的问题很快就会变得非常复杂。INT甚至BIGINT比GUID要小很多，但是只有300k左右的记录，它不会对大小产生很大的影响，但是GUID作为主键会导致严重的碎片化。这肯定会影响索引，如果需要最大性能，可能会导致查找问题。
If high security is a priority, one of the bad things about using sequential ids is that it makes it incredibly easy to numerate through your users when (not if) a bad actor discovers your id scheme. GUIDs fix this, and the chance of two people having the same GUID, even across multiple systems, is very small.
如果高安全性是优先考虑的问题，那么使用顺序id的缺点之一是，当(而不是如果)一个糟糕的参与者发现了您的id方案时，通过您的用户进行数字识别会非常容易。GUIDs解决了这个问题，而且两个人拥有相同的GUID(甚至跨多个系统)的几率非常小。
SQL 2008 does have newsequentialid(), which kinda helps some of the issues with newid(). It will significantly reduce fragmentation, but it will still require a lot more storage than in INT. https://docs.microsoft.com/en-us/sql/t-sql/functions/newsequentialid-transact-sql
SQL 2008确实有newsequentialid()，它帮助了newid()的一些问题。它将显著减少碎片，但是它仍然需要比INT. https://docs.microsoft.com/en-us/sql/t-sql/functions/newsequentialid-transact-sql更多的存储空间
Again, this is an issue that will require you to put some thought into, especially how it will impact your system. This is ultimately something you'll have to weigh within your expected system.
同样，这是一个需要您考虑的问题，特别是它将如何影响您的系统。这最终是你必须在你期望的系统中权衡的事情。
https://news.ycombinator.com/item?id=14523523
https://news.ycombinator.com/item?id=14523523
https://blogs.msdn.microsoft.com/sqlserverfaq/2010/05/27/guid-vs-int-debate/
https://blogs.msdn.microsoft.com/sqlserverfaq/2010/05/27/guid-vs-int-debate/
And you should always pay attention to Ms. Tripp's advice. https://www.sqlskills.com/blogs/kimberly/guids-as-primary-keys-andor-the-clustering-key/
你应该时刻注意特里普女士的建议。https://www.sqlskills.com/blogs/kimberly/guids-as-primary-keys-andor-the-clustering-key/
Though Jeff Atwood has a different opinion. As does Joe Celko. https://blog.codinghorror.com/primary-keys-ids-versus-guids/
虽然杰夫·阿特伍德有不同的看法。乔Celko一样。https://blog.codinghorror.com/primary-keys-ids-versus-guids/
There are about a bazillion links on Google that will give you multiple sides of this discussion. It really begins to approach a religious debate. Ultimately, I don't think anyone here can tell you what the "Best Practice" is. You'll have to try different methods in your system and see what works best for what you have.
谷歌上有大量的链接，可以为您提供讨论的多个方面。它真的开始接近一场宗教辩论。最后，我认为这里没有人能告诉你“最佳实践”是什么。您将不得不在系统中尝试不同的方法，看看哪些方法最适合您现有的方法。
#1
5
As with most other things IT DEPENDS.
就像大多数其他事情一样，这要看情况而定。
Something that seems like it should be such an easy issue can get quite complex pretty quickly. And INT or even BIGINT is significantly smaller than a GUID, but with only 300k or so records, it's not going to have a huge impact on size, but a GUID as your PRIMARY KEY WILL cause significant fragmentation. That will definitely affect your index and could cause issues with lookups if you need max performance.
一些看似很容易解决的问题很快就会变得非常复杂。INT甚至BIGINT比GUID要小很多，但是只有300k左右的记录，它不会对大小产生很大的影响，但是GUID作为主键会导致严重的碎片化。这肯定会影响索引，如果需要最大性能，可能会导致查找问题。
If high security is a priority, one of the bad things about using sequential ids is that it makes it incredibly easy to numerate through your users when (not if) a bad actor discovers your id scheme. GUIDs fix this, and the chance of two people having the same GUID, even across multiple systems, is very small.
如果高安全性是优先考虑的问题，那么使用顺序id的缺点之一是，当(而不是如果)一个糟糕的参与者发现了您的id方案时，通过您的用户进行数字识别会非常容易。GUIDs解决了这个问题，而且两个人拥有相同的GUID(甚至跨多个系统)的几率非常小。
SQL 2008 does have newsequentialid(), which kinda helps some of the issues with newid(). It will significantly reduce fragmentation, but it will still require a lot more storage than in INT. https://docs.microsoft.com/en-us/sql/t-sql/functions/newsequentialid-transact-sql
SQL 2008确实有newsequentialid()，它帮助了newid()的一些问题。它将显著减少碎片，但是它仍然需要比INT. https://docs.microsoft.com/en-us/sql/t-sql/functions/newsequentialid-transact-sql更多的存储空间
Again, this is an issue that will require you to put some thought into, especially how it will impact your system. This is ultimately something you'll have to weigh within your expected system.
同样，这是一个需要您考虑的问题，特别是它将如何影响您的系统。这最终是你必须在你期望的系统中权衡的事情。
https://news.ycombinator.com/item?id=14523523
https://news.ycombinator.com/item?id=14523523
https://blogs.msdn.microsoft.com/sqlserverfaq/2010/05/27/guid-vs-int-debate/
https://blogs.msdn.microsoft.com/sqlserverfaq/2010/05/27/guid-vs-int-debate/
And you should always pay attention to Ms. Tripp's advice. https://www.sqlskills.com/blogs/kimberly/guids-as-primary-keys-andor-the-clustering-key/
你应该时刻注意特里普女士的建议。https://www.sqlskills.com/blogs/kimberly/guids-as-primary-keys-andor-the-clustering-key/
Though Jeff Atwood has a different opinion. As does Joe Celko. https://blog.codinghorror.com/primary-keys-ids-versus-guids/
虽然杰夫·阿特伍德有不同的看法。乔Celko一样。https://blog.codinghorror.com/primary-keys-ids-versus-guids/
There are about a bazillion links on Google that will give you multiple sides of this discussion. It really begins to approach a religious debate. Ultimately, I don't think anyone here can tell you what the "Best Practice" is. You'll have to try different methods in your system and see what works best for what you have.
谷歌上有大量的链接，可以为您提供讨论的多个方面。它真的开始接近一场宗教辩论。最后，我认为这里没有人能告诉你“最佳实践”是什么。您将不得不在系统中尝试不同的方法，看看哪些方法最适合您现有的方法。