SQLServer(T-SQL)でulid
はじめに
ulid (Universally Unique Lexicographically Sortable Identifier) というものがあるらしく、T-SQLで生成するストアドプロシージャを書いてみました。
https://github.com/ulid/spec
https://qiita.com/kai_kou/items/b4ac2d316920e08ac75a
ulid とは
Timestamp：48bit整数、UNIX時間(ミリ秒)
Randomness：80bit乱数
をBase32 Encodingしたものらしい。
検証環境
Microsoft SQL Server 2016 (RTM-GDR) (KB4019088) - 13.0.1742.0 (X64)
Jul 5 2017 23:41:17
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows 10 Pro 6.3 (Build 17134: ) (Hypervisor)
T-SQL
USE TestDB;
GO
IF OBJECT_ID('dbo.get_ulid', 'P') IS NOT NULL DROP PROCEDURE dbo.get_ulid;
GO
CREATE PROCEDURE dbo.get_ulid
@result VARCHAR(26) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @characters VARCHAR(32) = '0123456789ABCDEFGHJKMNPQRSTVWXYZ';
DECLARE @timestamp BIGINT = DATEDIFF_BIG(ms,'1970/1/1',SYSUTCDATETIME());
DECLARE @randomness INT = FLOOR((RAND(CONVERT(INT, CONVERT(VARBINARY, NEWID()))) * 32));
DECLARE @remainder INT
DECLARE @quotient BIGINT = @timestamp
DECLARE @count INT = 0;
--DECLARE @result VARCHAR(26) = '';
SET @result = ''
WHILE @quotient > 0
BEGIN
SET @remainder = @quotient %32
SET @quotient = @quotient / 32
SET @result = SUBSTRING(@characters, @remainder + 1, 1) + @result
END
SET @result = RIGHT('0000000000' + @result, 10)
WHILE @count < 16
BEGIN
SET @randomness = FLOOR((RAND(CONVERT(INT, CONVERT(VARBINARY, NEWID()))) * 32));
SET @result = @result + SUBSTRING(@characters, @randomness + 1, 1);
SET @count = @count + 1;
END
--PRINT @result
--SELECT @result AS 'ulid'
RETURN 0
END
GO
実行結果
DECLARE @ulid VARCHAR(26);
EXECUTE dbo.get_ulid @ulid OUTPUT
PRINT @ulid
01D12R8RKKRCTGY9DCDX5ZXBT6
おわりに
USE TestDB;
GO
IF OBJECT_ID('dbo.get_ulid', 'P') IS NOT NULL DROP PROCEDURE dbo.get_ulid;
GO
CREATE PROCEDURE dbo.get_ulid
@result VARCHAR(26) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @characters VARCHAR(32) = '0123456789ABCDEFGHJKMNPQRSTVWXYZ';
DECLARE @timestamp BIGINT = DATEDIFF_BIG(ms,'1970/1/1',SYSUTCDATETIME());
DECLARE @randomness INT = FLOOR((RAND(CONVERT(INT, CONVERT(VARBINARY, NEWID()))) * 32));
DECLARE @remainder INT
DECLARE @quotient BIGINT = @timestamp
DECLARE @count INT = 0;
--DECLARE @result VARCHAR(26) = '';
SET @result = ''
WHILE @quotient > 0
BEGIN
SET @remainder = @quotient %32
SET @quotient = @quotient / 32
SET @result = SUBSTRING(@characters, @remainder + 1, 1) + @result
END
SET @result = RIGHT('0000000000' + @result, 10)
WHILE @count < 16
BEGIN
SET @randomness = FLOOR((RAND(CONVERT(INT, CONVERT(VARBINARY, NEWID()))) * 32));
SET @result = @result + SUBSTRING(@characters, @randomness + 1, 1);
SET @count = @count + 1;
END
--PRINT @result
--SELECT @result AS 'ulid'
RETURN 0
END
GO
DECLARE @ulid VARCHAR(26);
EXECUTE dbo.get_ulid @ulid OUTPUT
PRINT @ulid
01D12R8RKKRCTGY9DCDX5ZXBT6
おわりに
きちんと実装を読んで作ったわけでは無いので、正確さは保証できかねます。。。
Author And Source
この問題について(SQLServer(T-SQL)でulid), 我々は、より多くの情報をここで見つけました https://qiita.com/ryoma_kochi/items/cd3dec168fd7cb2ddaf9著者帰属：元の著者の情報は、元のURLに含まれています。著作権は原作者に属する。
Content is automatically searched and collected through network algorithms . If there is a violation . Please contact us . We will adjust (correct author information ,or delete content ) as soon as possible .