有没有一种简单的方法可以从两个整数复合键创建唯一的整数键？
由于与问题不太相关的各种原因，我有一个表，其中包含由两个整数组成的复合键，我想从这两个数字中创建一个唯一的键。我最初的想法是将它们连接起来，但是当我意识到 (51,1) 的复合键会产生与 (5,11) 相同的唯一键（即 511）时，我很快遇到了一个问题
。有一种巧妙的方法可以从两个整数中生成一个整数，使得生成的整数对于起始整数对来说是唯一的？
编辑：在面对大量数学运算后，我意识到我应该包含的一个细节是相关按键的大小。在原始密钥对中，第一个密钥当前为 6 位数字，并且在系统生命周期内可能会保持为 7 位数字；第二个键尚未大于 20。考虑到这些限制，问题似乎不那么令人畏惧。
For various reasons that aren't too germane to the question, I've got a table with a composite key made out of two integers and I want to create a single unique key out of those two numbers. My initial thought was to just concatenate them, but I ran into a problem quickly when I realized that a composite key of (51,1) would result in the same unique key as (5,11), namely, 511.
Does anyone have a clever way to generate an integer out of two integers such that the generated integer is unique to the pair of start integers?
Edit: After being confronted with an impressive amount of math, I'm realizing that one detail I should have included is the sizes of the keys in question. In the originating pair, the first key is currently 6 digits and will probably stay in 7 digits for the life of the system; the second key has yet to get larger than 20. Given these constraints, it looks like the problem is much less daunting.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（9）
如果您希望生成的密钥包含与其两个组件相同数量的位数，您可以从数学上证明这是不可能的。但是，如果您从两个 32 位 int 开始，并且可以使用 64 位 int 作为结果，那么显然您可以执行如下操作：
SQL 语法
You can mathematically prove this is impossible if you want the resulting key to comprise the same number of bits as its two components. However, if you start with two 32 bit ints, and can use a 64 bit int for the result, you could obviously do something like this:
SQL Syntax
已经对此进行了相当多的详细讨论（然而，正如递归所说，输出必须包含比单个输入更多的位）。
将两个整数映射到一个，以唯一和确定性方式
如何使用两个数字作为地图键
http://en.wikipedia.org/wiki/Cantor_pairing_function#Cantor_pairing_function
This has been discussed in a fair amount of detail already (as recursive said, however, the output must be comprised of more bits than the individual inputs).
Mapping two integers to one, in a unique and deterministic way
How to use two numbers as a Map key
http://en.wikipedia.org/wiki/Cantor_pairing_function#Cantor_pairing_function
将 1 与足够高的值相乘
或者使用文本串联：
或者跳过整数并用非数字分隔 ID：
Multiply one with a high enough value
Or use text concatenation:
Or skip the integer thing and separate the IDs with something non-numeric:
仅当您对其中一个键有上限时才能执行此操作。假设您有
key1和key2，并且up1是key1永远不会达到的值，那么您可以组合像这样的密钥：即使理论上密钥可以无限制地增长，通常也可以在实践中估计保存上限。
You can only do it if you have an upper bound for one of the keys. Say you have
key1andkey2, andup1is a value thatkey1will never reach, then you can combine the keys like this:Even if the keys could theoretically grow without limit, it's usually possible to estimate a save upper bound in practice.
因为我喜欢你问题的理论方面（它确实很漂亮），并且与许多实际答案所说的相矛盾，我想对你标签的“数学”部分给出答案:)
事实上可以将任何两个数字（或实际上任何一系列数字）映射到单个数字。这称为哥德尔数，由库尔特·哥德尔于 1931 年首次发表。
举一个简单的例子，回答你的问题；假设我们有两个变量 v1 和 v2。那么 v3=2v1*3v2 将给出一个唯一的编号。该编号还唯一标识 v1 和 v2。
当然，所得的数字 v3 可能会增长得非常快。请将此答案作为对您问题中理论方面的答复。
As I like the theoretical side of your question (it really is beautiful), and to contradict what many of the practical answers say, I would like to give an answer to the "math" part of your tags :)
In fact it is possible to map any two numbers (or actually any series of numbers) to a single number. This is called the Gödel number and was first published in 1931 by Kurt Gödel.
To give a quick example, with your question; say we have two variables v1 and v2. Then v3=2v1*3v2 would give a unique number. This number also uniquely identifies v1 and v2.
Of course the resulting number v3 may grow undesirably rapid. Please, just take this answer as a reply to the theoretical aspect in your question.
这两种建议的解决方案都需要一些有关可接受密钥范围的知识。
为了避免做出这种假设，可以将数字组合在一起。
Key1 = ABC =>;数字 = A、B、C键2 = 123 =>数字 = 1, 2, 3
Riffle(Key1, Key2) = A, 1, B, 2, C, 3当数字不足时可以使用零填充：
Key1 = 12345, Key2 = 1 = > 1020304051此方法也适用于任意数量的键。
Both of the suggested solutions require some knowledge about the range of accepted keys.
To avoid making this assumption, one can riffle the digits together.
Key1 = ABC => Digits = A, B, CKey2 = 123 => Digits = 1, 2, 3Riffle(Key1, Key2) = A, 1, B, 2, C, 3Zero-padding can be used when there aren't enough digits:
Key1 = 12345, Key2 = 1 => 1020304051This method also generalizes for any number of keys.
为 mysql 写了这些，它们工作正常
CREATE FUNCTION
pair(x BIGINT unsigned, y BIGINT unsigned)返回 BIGINT 无符号确定性
返回 ((x + y) * (x + y + 1)) / 2 + y;
创建函数
reversePairX（z BIGINT 无符号）返回 BIGINT 无符号确定性
返回 (FLOOR((-1 + SQRT(1 + 8 * z))/2)) * ((FLOOR((-1 + SQRT(1 + 8 * z))/2)) + 3) / 2 - z ;
创建函数
reversePairY（z BIGINT 无符号）返回 BIGINT 无符号确定性
返回 z - (FLOOR((-1 + SQRT(1 + 8 * z))/2)) * ((FLOOR((-1 + SQRT(1 + 8 * z))/2)) + 1) / 2 ;
wrote these for mysql they work fine
CREATE FUNCTION
pair(x BIGINT unsigned, y BIGINT unsigned)RETURNS BIGINT unsigned DETERMINISTIC
RETURN ((x + y) * (x + y + 1)) / 2 + y;
CREATE FUNCTION
reversePairX(z BIGINT unsigned)RETURNS BIGINT unsigned DETERMINISTIC
RETURN (FLOOR((-1 + SQRT(1 + 8 * z))/2)) * ((FLOOR((-1 + SQRT(1 + 8 * z))/2)) + 3) / 2 - z;
CREATE FUNCTION
reversePairY(z BIGINT unsigned)RETURNS BIGINT unsigned DETERMINISTIC
RETURN z - (FLOOR((-1 + SQRT(1 + 8 * z))/2)) * ((FLOOR((-1 + SQRT(1 + 8 * z))/2)) + 1) / 2;
冒着听起来很滑稽的风险：
其中 fn() 是一个函数，它从添加到现有表的列中查找新的自动编号键值。
显然，两个整数字段可以比单个整数字段容纳指数级多的值。
At the risk of sounding facetious:
where fn() is a function that looks up a new autonumbered key value from a column added to your existing table.
Obviously, two integer fields can hold exponentially more values than a single integer field.
为什么不直接使用 ROW_NUMBER() 或 IDENTITY(int,1,1) 来设置新 ID？他们真的需要有关系吗？
Why don't you just use ROW_NUMBER() or IDENTITY(int,1,1) to set new ID? Do they REALLY need to be in relation?