使用 64 位有符号值在 SQL 和 C# 中模拟 128 位无符号整数？
考虑这样的场景：您有一些 C# 中的标志枚举与 SQL Server 中的枚举表相关联（实际上是从这些表生成的）。假设您是经销商，并且您允许经销商指定他们运送到美国的哪些州。作为一名才华横溢且优雅的软件工程师，您将这些实现为可按位组合的标志值以节省存储空间：
create table USState (
StateID bigint, StateAbbr char(2), StateName varchar(50))
/* insert all US States + DC into USState, StateIDs must be in powers of two */
/* StateID 0 reserved for 'None': */
create procedure GetStatesByFlag (@StateFlags bigint) as
declare @StateIDs table
(
StateID bigint,
primary key (StateID)
)
insert into @StateIDs
select StateID
from USState
where @StateFlags & StateID != 0
or (@StateFlags = 0 and StateID = 0)
select s.StateID, s.StateAbbr, s.StateName
from
USState s join
@StateIDs si
on si.StateID = s.StateID
Sweet。您可以使用按位逻辑在 SQL 和 C# 中动态包含/排除，这使您可以立即在 Asp.NET 中合并复选框列表和选择列表，同时仍然只存储单个 64 位数字来保存任何选择组合。并且您不需要在过程的 WHERE 子句中使用不可索引的比较运算符，除非枚举表本身最多有 64 行。在经销商中搜索运送到印第安纳州和加利福尼亚州的每个人时，仍然可以使用平等比较和索引。
现在，您请求添加对美国领土、武装部队邮寄代码和加拿大省份的支持，并以向后兼容的方式执行此操作。无法将列表缩减到 << 64 个条目，企业确实希望避免将老式州与其他领土和部门隔离。
你做什么工作？
创造性的答案值得赞赏，但这里真正的挑战是：有没有一种方法可以强制对无符号 64 位值进行相同的按位数学运算来对有符号值进行处理，同时使用负空间超过 64 个可能的位，在 C# 中和 SQL (2008)？如果重要的话，该标志是模拟的，而不是“真实”标志，因此从技术上讲，它没有必要针对具有 [Flags] 属性的 CLR 枚举。
Take this scenario: You have a few flag enumerations in C# tied to (and in fact generated from) Enum-ish tables in SQL Server. Say you are a distributor, and you allow your resellers to specify what US states they ship to. Being a brilliant and elegant software engineer, you implemented these as a bitwise-combinable flag value to save storage:
create table USState (
StateID bigint, StateAbbr char(2), StateName varchar(50))
/* insert all US States + DC into USState, StateIDs must be in powers of two */
/* StateID 0 reserved for 'None': */
create procedure GetStatesByFlag (@StateFlags bigint) as
declare @StateIDs table
(
StateID bigint,
primary key (StateID)
)
insert into @StateIDs
select StateID
from USState
where @StateFlags & StateID != 0
or (@StateFlags = 0 and StateID = 0)
select s.StateID, s.StateAbbr, s.StateName
from
USState s join
@StateIDs si
on si.StateID = s.StateID
Sweet. You can include/exclude dynamically in both SQL and C# using bitwise logic, which lets you instantly hydrate checkbox lists and select lists in Asp.NET while still only storing a single 64-bit number to hold any combination of selections. And you don't need a non-indexable comparison operator in your procedures' WHERE clauses, except against the enum table itself which has a maximum of 64 rows. Searching your distributors for everyone who ships to Indiana and California can still use an equality comparison and an index.
Now you have a request to add support for US territories, armed forces mailing codes and Canadian provinces, and do so in a backwards-compatible fashion. There's no cutting the list down to < 64 entries, and the business really wants to avoid having to segregate old-school states from the rest of the territories and divisions.
What do you do?
Creative answers are appreciated, but the real challenge here is this: Is there a way to force the same bitwise math that works on unsigned 64-bit values to work on signed ones while using the negative space to exceed 64 possible bits, in both C# and SQL (2008)? If it matters, the flag is simulated, not a "real" flag, so it's technically not necessary for this to work against a CLR enum with [Flags] attribute.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
64 位值不能超过 64 位，甚至不能使用“负空间”。如果你有 64 位，你就有 64 位。您可以使用 Guid 来获取 128 位，这会将问题推迟一段时间，但最终您将需要添加其他字段。
You can't exceed 64 bits on a 64 bit value, not even using the "negative space". If you have 64 bits, you have 64 bits. You can use a Guid to get 128 bits, which will put the problem off for a while, but ultimately you will need to add additional fields.
在SQL Server中，您可以尝试decimal(38,0)，
这将为您提供小数点左侧的38位数字(1E38)。以二进制表示，大约为 126 位 (8.5E37)。而且它可以像数字一样被操纵。
但是，一种选择是在 .NET 中定义您想要的内容并使用匹配的 SQL Server 中的 CLR 数据类型。这样，两个平台之间就可以保持一致。
然而，我真的会考虑改变标志......
In SQL Server, you could try decimal(38,0)
This gives you 38 digits to the left of the decimal point (1E38). In binary terms, it's about 126 bits (8.5E37). And it can be manipulated like a number.
However, one option would be to define what you want in .NET and use a matching CLR data type in SQL Server. This way, it can be consistent between the 2 platforms.
However, I would really consider changing away from flags...
我的2c：你试图表现得聪明，但却给自己带来了痛苦。位字段和 SQL 不能很好地混合，主要是因为位字段无法正确索引，并且任何搜索都必须进行完整扫描。例如。要查找向 AK 发货的所有经销商，您需要扫描整个经销商表。此外，该解决方案不会扩展到超过 64 个值（正如您已经发现的那样）。它的存储选择也很差，它需要一个值为 0 的位来存储负面信息（缺乏关联）。
使用单独的表对经销商与其发货目的地的州/地区/省/国家之间的多对多关系进行建模。
My 2c: You're trying to be clever, and your causing your own pain. Bitfields and SQL don't mix well, primarily because bitfields cannot be properly indexed and any search will have to do a full scan. Eg. to find all reselers that ship to AK you need to scan the entire table of reselers. Also the solution does not scale to more than 64 values (as you already discovered). It also poor storage choice, it requires a bit with value 0 to store the negative information (lack of association).
Use a separate table to model the many-to-many relation between resellers and states/territories/provinces/countries they ship to.