SQL Server 以外的数据库中的 GUID
问题：我目前正在为我的一个程序规划数据库。
我打算使用 ASP.NET MVC 作为用户后端，数据库位于 Linux 和/或 Windows 上。
现在，即使我只为 Windows 制作它，我也必须考虑到不同的客户使用不同的数据库系统。现在，我想我使用nHibernate，然后我可以把所有东西都放在代码中，它适用于所有主要数据库，例如Oracle/Sybase/MS/PostGre/MySQL/Firebird。
我现在的问题是 GUID。 SQL Server使用GUID，而其余的使用整数自动增量作为主键。虽然自动增量在理论上更好，但它会产生保持多个数据库同步的问题，或者手动更改内容的问题，这需要 CSV 导入/导出...
现在，由于实践中 autoid 的固有问题，我喜欢 GUID 系统更好的。由于 guid 是一个 36 个字符的字符串，我可以使用 varchar(36) 作为主键，但使用 varchar 作为 GUID，可能不是一个理想的解决方案...
您将如何解决这个问题/您会做什么用作主键 ? 或者如何避免自动增量问题，例如插入 csv 文件而不更改 autoid...
Question: I'm planning the database for one of my programs at the moment.
I intend to use ASP.NET MVC for the user backend, the database being on Linux and/or on Windows.
Now, even if I would only make it for windows, I had to take into account, that different customers use different database systems. Now, I figured I use nHibernate, then I can put everything in the code, and it works on all mayor databases, such as Oracle/Sybase/MS/PostGre/MySQL/Firebird.
My probem now is GUIDs. SQL Server uses GUIDs, while the rest uses integer auto-increment as primary keys. While auto-increment is better in theory, it creates problems keeping multiple databases in sync, or problems manually changing things, which requires CSV import/export...
Now, because of the inherent problems with autoid in practise, I like the GUID system better. And since a guid is a 36-character string, I could use varchar(36) as a primary-key, but a varchar as GUID, might just not be an ideal solution...
How would you solve this problem/what do you use as primary-key ?
Or how do you evade the auto-increment problems, say insert a csv file without changing the autoid...
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
使用
guid.comb生成器密钥的 Guid 密钥可在任何数据库中使用，即使它没有 Guid 作为本机类型。A Guid key using the
guid.combgenerator key is usable in any database, even if it doesn't have Guid as a native type.您还可以考虑生成一个主键，它是自动增量（即设置序列）和生成它的机器的唯一标识符（可能使用 MAC 地址）的组合。
请参阅此进行讨论。
这样，您就拥有了本地唯一的 ID（由于序列），该 ID 也是全局唯一的（由于 MAC 地址部分）。
我知道，我知道，您可以欺骗 MAC 地址，但这由您决定这在您的域中是否确实存在风险。此外，当您测试代码时，欺骗它的能力可能会很方便。
请更好地解释当新客户数据库诞生时会发生什么。会在服务器上注册吗？如果是，您可以在服务器上分配一个 DB-id，并使用它代替 MAC 地址，只需为每个新 DB 分配一个编号并与序列一起使用。
基本上，如果您想要一个“唯一的数据库实例 ID”来避免“表 ID”冲突，您只有两种选择：
1）服务器在添加新数据库时分配数据库 ID
2）客户端自动生成唯一 ID，这通常需要使用“原始”或以某种方式处理过的 MAC 地址。
老实说，根据您当前对问题的描述，我看不到其他选择。
You could also consider generating a primary key which is a combination of auto-increment (i.e. setting up a sequence) and an unique identifier of the machine it was generated on, maybe using the MAC address.
See this for a discussion.
This way you have a locally unique (thanks to the sequence) ID which is also globally unique (thanks to the MAC address part).
I know, I know, you can spoof a MAC address but it's up to you to decide if this is really a risk in your domain. Also, the ability to spoof it could be handy when you test your code.
Please explain better what happens when a new customer DB is born. Will it be registered on the Server? If yes, you can assign a DB-id on the server, and use it in lieu of the MAC address, just assign a number to each new DB and use it along with the sequence.
Basically, if you want an "unique DB instance ID" to avoid "table id" collisions, you have only two choices:
1) Server assigns the DB ID whenever a new DB is added
2) Client autogenerate a unique ID, and this usually needs using the MAC address, either "raw" or processed somehow.
I honestly can't see alternatives given your current description of your problem.
Oracle 和 PostgreSQL 也支持 GUID，因此无需在那里使用序列（当然，Diego 是对的：如果您使用自己的算法来创建 GUID，您始终可以使用 varchar 列和您自己生成的 GUID 进行存储）
请注意，它是拼写为 PostgreSQL，而不是 PostGre
Oracle and PostgreSQL support GUIDs as well, there is no need so use sequences there (and of course Diego is right: if you use your own algorithm to create GUIDs you can always store use a varchar column with your own generated GUID)
Note that it's spelled PostgreSQL, never PostGre
我在使用 Guid 时从未遇到过任何问题。我们在一个有很多记录（数百万条）的系统中使用 Guid.Comb，并且没有因为 Guid 本身而出现任何问题。对我来说，卖点是我可以在将某些内容保存到数据库之前生成 Id。即使在客户端。这在 CQRS 场景中非常有帮助。
我认为您还应该考虑的唯一一件事是人类可读性。在主/详细场景中查看数据库并匹配记录是很困难的。
关于 Firebird 的注释...Uuid 被写成一个八位字节。我用来管理数据库的大多数客户端都无法以合适的格式表示这些数据库。因此它通常只显示为几个字符（可能只是将字节数组解码为字符串）。但我不知道其他提供商的情况。例如，SQLServer Management Studio 就很好地展示了它们。
I have never had any trouble using Guids. We used Guid.Comb in a system with many records (millions) and had no trouble because of the Guids themselves. The selling point for me is that I can generate the Ids before i persist something to the database. Even on the client. Which is very helpful in CQRS scenarios.
The only thing that I think you should also consider is human readability. It's hard to look at the database and match records in a lets say master/detail scenario.
And a note on Firebird... Uuid is written as an octet. And most clients that I've used to manage the database can't represent those in a decent format. So it's usually just displayed as a couple of characters (probably by just decoding a byte array as a string). I don't know about other providers though. SQLServer Management Studio for example shows them just fine.