注意:这与数据库设计或GUID的一般用法无关。这是关于在Microsoft SQL 服务器上确定性地为测试数据创建此类 GUID。
我们正在将数据库从整数标识符迁移到唯一标识符数据类型。
出于测试目的,我们希望将测试数据集迁移到已知的 GUID 值,根据以前的整数值确定性地
UPDATE Unit
SET UnitGuid = NEWID(UnitId)
显然,这不会立即起作用。如何使用 UnitId 创建确定性 GUID?
停止从"字符串"的角度思考问题。int
由 4 个字节组成。uniqueidentifier
由 16 个字节组成。您可以轻松地获取 12 个固定字节,并将int
中的四个字节附加到这些字节的末尾,并获得适用于所有int
值的解决方案:
declare @Unit table
(
UniqueColumn UNIQUEIDENTIFIER DEFAULT NEWID(),
Characters VARCHAR(10),
IntegerId int
)
-- Add *3* data rows
INSERT INTO @Unit(Characters, IntegerId) VALUES ('abc', 1111),('def', 2222),('ghi',-17)
-- Deterministically creates a uniqueidentifier value out of an integer value.
DECLARE @GuidPrefix binary(12) = 0xefbeadde0000000000000000
UPDATE @Unit
SET UniqueColumn = CONVERT(uniqueidentifier,@GuidPrefix + CONVERT(binary(4),IntegerId))
-- Check the result
SELECT * FROM @Unit
结果:
UniqueColumn Characters IntegerId
------------------------------------ ---------- -----------
DEADBEEF-0000-0000-0000-000000000457 abc 1111
DEADBEEF-0000-0000-0000-0000000008AE def 2222
DEADBEEF-0000-0000-0000-0000FFFFFFEF ghi -17
(由于各种原因,我们必须以与将uniqueidentifier
显示为字符串时默认使用的顺序不同的顺序提供前四个字节,这就是为什么如果我们想显示DEADBEEF
,我们必须以efbeadde
开头我们的二进制文件(
此外,当然,插入通常的警告,如果您正在创建 guid/唯一标识符,但不使用规定的方法来生成它们,那么您不能假设任何关于唯一性的常见保证。
您可以创建键盘映射表:
CREATE TABLE tab_map(id_old INT PRIMARY KEY, guid UNIQUEIDENTIFIER);
INSERT INTO tab_map(id_old, guid)
SELECT id, NEWID()
FROM src_table;
DBFiddle 演示
之后,您可以使用简单的查询或与函数包装:
SELECT guid
FROM tab_map
WHERE id_old = ?
我最终为自己解决了这个问题。这是我的解决方案,供将来参考:
我以deadbeef-0000-0000-0000-
的形式创建 GUID 的前缀部分,然后将 Id 列的整数值的"字符串化"、零填充版本附加到其中,如000000000001
,结果为
DEADBEEF-0000-0000-0000-000000000001
在此示例中。
下面是对整个表执行此操作的 SQL 命令:
-- Deterministically creates a uniqueidentifier value out of an integer value.
DECLARE @GuidPrefix nvarchar(max) = N'deadbeef-0000-0000-0000-'; -- without the last 12 digits
UPDATE Unit
SET UniqueColumn =
(SELECT @GuidPrefix + RIGHT('000000000000' + CAST(IntegerId AS NVARCHAR (12)), 12 ) AS NUMBER_CONVERTED)
警告:
- 此实现仅适用于正 int 值(已启动 最大2147483647(
- 这仅适用于测试数据!用途是 强烈建议不要使用生产数据!
这是一个完整的工作示例:
-- Create an example table with random GUID's
CREATE TABLE Unit
(
UniqueColumn UNIQUEIDENTIFIER DEFAULT NEWID(),
Characters VARCHAR(10),
IntegerId int
)
-- Add 2 data rows
INSERT INTO Unit(Characters, IntegerId) VALUES ('abc', 1111)
INSERT INTO Unit(Characters, IntegerId) VALUES ('def', 2222)
-- Deterministically creates a uniqueidentifier value out of an integer value.
DECLARE @GuidPrefix nvarchar(max) = N'deadbeef-0000-0000-0000-'; -- without the last 12 digits
UPDATE Unit
SET UniqueColumn =
(SELECT @GuidPrefix + RIGHT('000000000000' + CAST(IntegerId AS NVARCHAR (12)), 12 ) AS NUMBER_CONVERTED)
-- Check the result
SELECT * FROM Unit
结果:
UniqueColumn Characters IntegerId
--------------------------------------- ---------- ---------
DEADBEEF-0000-0000-0000-000000001111 abc 1111
DEADBEEF-0000-0000-0000-000000002222 def 2222