SQL Server 中数据库范围的唯一但简单的标识符 [英] Database-wide unique-yet-simple identifiers in SQL Server
问题描述
首先，我知道这个问题，以及建议(使用GUID) 不适用于我的情况.
First, I'm aware of this question, and the suggestion (using GUID) doesn't apply in my situation.
我想要简单的 UID，以便我的用户可以轻松地通过电话传达此信息:
I want simple UIDs so that my users can easily communicate this information over the phone :
您好，订单有问题1584
Hello, I've got a problem with order 1584
相对于
你好，订单有问题4daz33-d4gerz384867-8234878-14
hello, I've got a problem with order 4daz33-d4gerz384867-8234878-14
我希望它们是唯一的(数据库范围内)，因为我有几种不同类型的对象"……有订单 ID、交货 ID 和账单 ID，因为没有一对一的关系在这两者之间，我无法猜测一个 ID 指的是什么类型的对象.
I want those to be unique (database wide) because I have a few different kind of 'objects' ... there are order IDs, and delivery IDs, and billing-IDs and since there's no one-to-one relationship between those, I have no way to guess what kind of object an ID is referring to.
使用数据库范围内的唯一 ID，我可以立即知道我的客户所指的是什么对象.我的用户只需在搜索工具中输入一个 ID，我就为他保存额外的点击以进一步细化要查找的内容.
With database-wide unique IDs, I can immediately tell what object my customer is referring to. My user can just input an ID in a search tool, and I save him the extra-click to further refine what is looking for.
我目前的想法是使用具有不同种子 1、2、3 等且增量值为 100 的标识列.
My current idea is to use identity columns with different seeds 1, 2, 3, etc, and an increment value of 100.
不过这引发了一些问题:
This raises a few question though :
如果我最终得到 100 多个对象类型怎么办?当然我可以使用 1000 或 10000，但是不能很好地扩展的东西闻起来"
What if I eventually get more than 100 object types? granted I could use 1000 or 10000, but something that doesn't scale well "smells"
种子是否有可能丢失"(在复制、数据库问题等期间?)
Is there a possibility the seed is "lost" (during a replication, a database problem, etc?)
更一般地说，还有其他我应该注意的问题吗?
more generally, are there other issues I should be aware of?
是否可以使用非整数(我目前使用 bigints)作为标识列，以便我可以用代表对象类型的东西作为 ID 前缀?(例如 varchar 列)
is it possible to use an non integer (I currently use bigints) as an identity columns, so that I can prefix the ID with something representing the object type? (for example a varchar column)
使用仅包含标识列和对象类型的主表"是否是个好主意，这样我就可以在需要新想法时在其中插入一行.我觉得这可能有点矫枉过正，恐怕它会使我所有的插入请求变得复杂.另外，如果不查看数据库，我将无法确定对象类型
would it be a good idea to user a "master table" containing only an identity column, and maybe the object type, so that I can just insert a row in it whenever a need a new idea. I feel like it might be a bit overkill, and I'm afraid it would complexify all my insertion requests. Plus the fact that I won't be able to determine an object type without looking at the database
还有其他聪明的方法可以解决我的问题吗?
are there other clever ways to address my problem?
推荐答案
为什么不在所有表上使用标识，而在任何时候向用户展示标识时，只需为该类型添加一个字符?例如O1234是订单，D123213是交货等?这样你就不必设计一些疯狂的计划......
Why not use identities on all the tables, but any time you present it to the user, simply tack on a single char for the type? e.g. O1234 is an order, D123213 is a delivery, etc.? That way you don't have to engineer some crazy scheme...
这篇关于SQL Server 中数据库范围的唯一但简单的标识符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
