[SQL Server] シリアル番号とかクーポン番号を作成・発行しよう。
Create Serial Number / Create Serial Key / Create Coupon Number
下記のようなシリアル番号やクーポン番号を作成する必要時があります。
シリアル番号・クーポン番号 |
シリアル番号・クーポン番号(4文字ずつ「-」で区別したバージョン) |
E272CC7F6F472FA8 |
E272-CC7F-6F47-2FA8 |
3FECD682A4846A8B |
3FEC-D682-A484-6A8B |
B374D6C8574D5DB6 |
B374-D6C8-574D-5DB6 |
7486F9334427F99B |
7486-F933-4427-F99B |
D2729FF72FF4C359 |
D272-9FF7-2FF4-C359 |
4FB78842E249F5BB |
4FB7-8842-E249-F5BB |
2D37D3FEF4358394 |
2D37-D3FE-F435-8394 |
BAD66CE83C58448A |
BAD6-6CE8-3C58-448A |
57BB486AE8448844 |
57BB-486A-E844-8844 |
7428E42C842DAA24 |
7428-E42C-842D-AA24 |
今回は16文字のシリアル番号やクーポン番号を発行する方法について説明したいと思います。
まずは発行されたシリアル番号を保存するためのテーブルを作成する。
CREATE TABLE dbo.SerialInfo
(
SerialNo char(16)
)
発行の為には色々な方法があるが、その中で下記の２つの方法について説明します。
1. WHILE文でLOOPしながら発行する。
2. tComDayテーブルを利用して一括で発行する。
ここで私がおすすめする方法は2番の一括で発行する方法です。
理由は処理速度が速いしもっと効率的だからです。
下記に1番と2番のテスト用SQLを準備しましたので、実際にテストをしながら理解して頂ければと思います。
まず、1番のWHILE文でLOOPしながら発行する方法です。
「@SerialCnt」変数の値が発行数を意味しますので、下記のクエリでは10個のシリアル番号を作成すると意味になります。
DECLARE @SerialCnt int
SET @SerialCnt = 10
DECLARE @i int
SET @i = 1
WHILE (@i <= @SerialCnt)
BEGIN
INSERT INTO dbo.SerialInfo (SerialNo)
SELECT SerialNo FROM
(
SELECT LEFT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(NEWID(),'-',''),'1',''),'I',''),'L',''),'0',''),'O',''), 16) SerialNo
) A
WHERE NOT EXISTS (SELECT TOP 1 1 FROM dbo.SerialInfo WHERE SerialNo = A.SerialNo)
-- 発行したシリアル番号が「dbo.SerialInfo」テーブルに正常に登録された場合、@i変数にをプラスする。
-- もし重複されたデータがあった場合、@i値は変更せず再度シリアル番号を発行するようにして発行数個に合わせる。
IF @@ROWCOUNT > 0
SET @i = @i + 1
END
これはNEWID()を使ってランダム値のクーポン番号やシリアル番号を作成します。
NEWID()についてよく分からないと言う方は下記のSQLを何回も実行してみてください。
実行するたびにランダムな値を返します。
SELECT NEWID()
実行結果 - 1回目
実行結果 - 2回目
この実行結果は皆さんそれぞれ違う結果が出ます。
このランダム値を利用してクーポン番号やシリアル番号を生成します。
上記のSQLではREPLACEが多くて複雑に見えますが、
REPLACEを削除してみれば、すごく単純なSQLになります。
あのREPLACEの役割はNEWID()をすると表示される「-」を表示させないため。
また、分かりにくい文字の「１」、「I」、「l」なども含まれないようにするためです。
上記のSQLが正常に実行されたら件数とデータを確認しましょう。
--件数確認
SELECT COUNT(*) FROM dbo.SerialInfo
--データ確認
SELECT TOP 20 * FROM dbo.SerialInfo
件数とデータの確認が終わりましたら、
次の2番目のtComDayテーブルを利用して一括で発行する方法について説明します。
テーブルは既に作成されていると思いますので、「dbo.SerialInfo」テーブルのデータを一旦削除しましょう。
DELETE TABLE dbo.SerialInfo
その後、下記のSQLを実行したいと思いますが、
下記のSQLを良くみると「dbo.tComDay」というテーブルがあります。
DECLARE @SerialCnt int
SET @SerialCnt = 10
INSERT INTO dbo.SerialInfo
SELECT TOP (@SerialCnt) SerialNo
FROM
(
SELECT LEFT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(NEWID(),'-',''),'1',''),'I',''),'L',''),'0',''),'O',''), 16) SerialNo
FROM dbo.tComDay WHERE Idx <= @SerialCnt*2
) A
WHERE NOT EXISTS (SELECT TOP 1 1 FROM dbo.SerialInfo WHERE SerialNo = A.SerialNo)
「dbo.tComDay」テーブルは作成しておけばすごく便利なテーブルになります。
詳しくは下記のリンクをご参考ください。
「dbo.tComDay」テーブルを作成する前に上記のSQLを簡単に説明すると
dbo.tComDayのところのWHERE句に「WHERE Idx <= @SerialCnt*2」がありますが、
「@SerialCnt*2」にした理由はNEWID()がランダム値を返しますので、既に発行されたシリアル番号がまた発行される可能性があるの重複データ登録を防止するためです。
それで既に多くのシリアル番号を作成してその中で登録されてない番号を抽出して登録するためであります。
「dbo.tComDay」テーブルの作成と詳細データの登録は下記のSQLを実行して登録してください。
CREATE TABLE [dbo].[tComDay](
[Idx] [int] NOT NULL,
[ComDay] [datetime] NOT NULL,
CONSTRAINT [PK_tComDay] PRIMARY KEY CLUSTERED
(
[Idx] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_tComDay_ComDay] ON [dbo].[tComDay]
(
[ComDay] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
--######## データの登録は2999年１２月３１日まで登録する。 ##############################################
DECLARE @i int
SET @i = 0
WHILE @i <= 365242
BEGIN
INSERT INTO dbo.tComDay
SELECT @i+1, DATEADD(DD, @i, '2000-01-01')
SET @i = @i + 1
END
--######## データを確認 ##############################################
SELECT TOP 10 * FROM [dbo].[tComDay] ORDER BY Idx
「tComDay」テーブルは日付のデータが入っているテーブルです。
個人的にはこのテーブルを意外と良く使っています。
このSQLの実行時間はPC環境によりますが、40秒～15分程度所要されます。
「tComDay」テーブルまで作成が終わりましたら、2番目のシリアル番号やクーポン番号を一括で生成するSQLを実行してみてください。
そして件数とデータの確認を行ってください。
現在は10個を発行するSQLですので、どれが効率的かは分かりません。
発行個数を15000枚程度設定して発行を1番目のSQLと2番目のSQLをそれぞれ実行してみてください。
私の場合、実行時間は。。。。。
1番のSQL ： 40秒
2番のSQL ： 0秒
データが多くなればなるほど1番の場合は遅くなります。
またシリアル番号を4文字ずつ切ってその間に「-」を入れて表示したい場合は、
下記のSQLを実行してみてください。
SELECT TOP 10 SUBSTRING(SerialNo,1,4)+'-'+SUBSTRING(SerialNo,5,4)+'-'+SUBSTRING(SerialNo,9,4)+'-'+SUBSTRING(SerialNo,13,4)
FROM dbo.SerialInfo
実行結果