資料庫取流水號

資料庫取流水號

取流水號在大量存取的專案上，一值是個令很多人頭痛的問題，不是怕取到重複的號碼，就是怕資料庫鎖定造成效率變差，事實上，只要了解不同方法的使用特性和限制，就不用怕不小心用錯方法，以下介紹幾個常用的方法，並舉例說明他們該何時使用。

將Table欄位設定為自動流水號

大部分的資料庫都提供將Table欄位設定為自動流水號，以下是MSSQL的範例語法，在建立Table的時候，將流水號的欄位Sell_Id設定為IDENTITY(1,1)，代表存入資料時會從1開始自動取號，每次累加1。

CREATE TABLE [dbo].[Ticket_Sell_Info]( [Sell_Id] [int] IDENTITY(1,1) NOT NULL, [Ticket_Price] [numeric] (18,6)NOT NULL, [Maintain_Date] [datetime] NOT NULL CONSTRAINT [PK_Ticket_Sell_Info] PRIMARY KEY CLUSTERED ( [Sell_Id] ASC ) ) ON [PRIMARY]

因為流水號欄位的內容是遊資料庫自動產生，所以在存資料進入此Table時，不須也不能指定流水號欄位Sell_Id的資料，範例如下：

String SQL = "INSERT INTO Ticket_Sell_Info (Ticket_Price,Maintain_Date) VALUES (100,getdate()) "; statement.executeUpdate(SQL); //statement 宣告此處為簡化範例故省略

這樣的做法很方便，理論上不用擔心會取到重複的流水號，但是很多資料庫管理師會視狀況不使用此方法，主要在於這樣的設計，流水號欄位只能讀取且無法寫入或修改，會讓資料庫後續管理在某些狀況下較為不便，例如將此筆資料移到另外一個Table再移回來，流水號就會因為再次重新取號而變了。

使用SQL語法MAX+1取流水號

假設上面的Ticket_Sell_Info在建立時，Sell_Id欄位不加上IDENTITY(1,1)的描述，則新增一筆資料時，因為該欄位指定 NOT NULL屬性(且同時為PrimaryKey欄位)，代表Sell_Id欄位一定要存一些東西進去，且存入的資料不能和已存在Table中現有的資料重複，我們常常會使用下面的方法取號。

String SQL = "INSERT INTO Ticket_Sell_Info (Sell_Id,Ticket_Price,Maintain_Date) SELECT ISNULL(MAX(Sell_Id)+1,1), 100,getdate() FROM Ticket_Sell_Info"; statement.executeUpdate(SQL); //statement 宣告此處為簡化範例故省略

這樣的MAX+1流水號取法也相當方便，其中ISNULL函式只是為了讓資料庫中還沒有任何資料時，也就是第一筆資料產生時寫入1，以免寫入null值而產生錯誤，一般常用在系統設定上，主要是因為系統設定不會有大量人員同時執行寫入的動作，但是如過是個售票系統，同一時間只要有超過數百人同時買票，將因資料庫硬體效能與多功能力，導致多筆寫入的資料抓到相同的流水號，此時只有第一個寫入的會成功，其他流水號重複寫入的皆會失敗。

使用Transaction啟動Lock功能取流水號

如果前面使用MAX+1的方法，在取號前先用Transaction啟動Lock功能來確保同一時間只有一個執行緒可以取號，這樣的方法看似可行，但如果同時間數百人或是千人以上同時取號，每個取號都要排隊，資料庫不僅會塞車還可能因為塞太多導致記憶體不足等各種原因而掛掉，當這個Table裡的資料量過多例如上百萬筆時，狀況更是雪上加霜，附帶一提，通常我們會設計一個系統假設預計可以使用50年的話，會同時設計Table內的資料量50年後不會超過100萬筆，否則可以動態產生新的Table來儲存，例如動態依不同年度產生Ticket_Sell_Info_[年度]之類的Table，或甚至每場售票就動態產生一個的Table來儲存，例如Ticket_Sell_Info_[場次流水號]。

如果要使用Transaction的lock功能來取號，會建議另外產生一個通常只有一筆(或少數幾筆)資料的Table來儲存目前的流水號取到第幾號，因為Table中只有一筆資料，所以取號相當快速，較不擔心數百人同時取號會造成塞車，取出號碼之後，再將取到的號碼存入前面的Ticket_Sell_Info表單中。

儲存目前流水號取到第幾號的Table建立範例如下，建立完Table就先存入一筆資料Current_Sell_Id代表目前流水號取到第0號：

CREATE TABLE [dbo].[Current_Id_Info]( [Current_Sell_Id] [int] NOT NULL, CONSTRAINT [PK_Current_Id_Info] PRIMARY KEY CLUSTERED ( [Current_Sell_Id] ASC ) ) ON [PRIMARY] INSERT INTO Current_Id_Info (Current_Sell_Id) VALUES (0)

用Transaction啟動Lock功能從Current_Id_Info表單中取號，並將取到的流水號儲存到Ticket_Sell_Info表單的範例如下，範例中，我們將針對Current_Id_Info表單更新流水號(流水號+1)與取號的SQL語法放在同一個連線交易中完成，如此才能千百倍的減少Table被鎖定的時間：

<%@ page import="java.sql.*" %> <% String SQL = ""; String Current_Sell_Id = "0"; try { //取消自動commit，則接下來存取的Table會被上鎖並獨佔存取，其他人的存取必須排隊，直到恢復自動commit為止 connection.setAutoCommit(false); //connection 宣告此處為簡化範例故省略 //以下兩個語法合併在同一個連線交易中完成，才能減少Table被鎖定的時間 SQL="UPDATE Current_Id_Info SET Current_Sell_Id = Current_Sell_Id + 1; " +"SELECT Current_Sell_Id FROM Current_Id_Info" ; statement.execute(SQL); //statement 宣告此處為簡化範例故省略 statement.getMoreResults(); ResultSet rs = statement.getResultSet(); if (rs.next()) { Current_Sell_Id = rs.getString("Current_Sell_Id"); } } catch (Exception e) { out.print("ERROR"+e); } finally { connection.setAutoCommit(true); } //取到流水號且解除Table鎖定之後，可以慢慢的拿來任意使用了 String SQL = "INSERT INTO Ticket_Sell_Info (Sell_Id,Ticket_Price,Maintain_Date) VALUES ("+Current_Sell_Id+", 100,getdate())"; statement.executeUpdate(SQL); //statement 宣告此處為簡化範例故省略 %>

 

使用JAVA產生UID當作流水號

如果仍嫌上面使用Transaction啟動Lock功能取流水號的方法不夠快速，同時成千上萬人存取有造成資料庫鎖死之疑慮，則你可以考慮直接用JAVA產生一組行遍全球皆不會重複的UID長字串當作流水號，等於放棄使用資料庫取流水號。

<%@ page import="java.sql.*" %> <% java.util.UUID uuid = java.util.UUID.randomUUID(); String Current_Sell_Id = uuid.toString(); //使用Java取到流水號之後再拿來存入資料庫 String SQL = "INSERT INTO Ticket_Sell_Info (Sell_Id,Ticket_Price,Maintain_Date) VALUES ('"+Current_Sell_Id+"', 100,getdate())"; statement.executeUpdate(SQL); //statement 宣告此處為簡化範例故省略 %>

產生的UID範例如36a00559-550b-4672-aad0-67ff238f9c3e，長度有36個字，因為是長字串流水號，所以前面建立Ticket_Sell_Info表單時，Sell_Id要改成字串欄位如下：

CREATE TABLE [dbo].[Ticket_Sell_Info]( [Sell_Id] [char](36) NOT NULL, [Ticket_Price] [numeric] (18,6)NOT NULL, [Maintain_Date] [datetime] NOT NULL CONSTRAINT [PK_Ticket_Sell_Info] PRIMARY KEY CLUSTERED ( [Sell_Id] ASC ) ) ON [PRIMARY]

使用這個方法的好處，除了不用浪費資料庫的資源來取流水號，理論上連不同企業之間的資料交換，也不會產生流水號重複的問題，另外，使用此方法產生的流水號因為是一個相當長的字串，所以網路上可以Google一堆縮短流水號長度的演算方法，但如果你沒有確定的把握和對該方法深入的了解，建議儘量不要使用那些方法，舉例來說，筆者就曾經使用了其中某些方法而在微調JSP伺服器時間後發生重複取號的窘境，Java還有其他產生UID的方法，像是使用java.rmi.server等方法都不建議使用，這些較短的UID都可能在某些特定狀況下造成流水號重複。