新增資料時自動產生識別代號的一些方法
時常接觸資料庫應用的編程人員，應該都會遇到需要新增資料後自動產生一個識別代號(ID)的設計。但遺憾的是， SQL Standard 並沒有定義任何相關型態或函數。於是各種資料庫系統都有自己的一套做法。使得這種普遍的應用設計，帶來了一個可攜性陷阱。
在不考慮使用資料庫系統特定方法的情況下，我長久以來習慣用 MAX(id) + 1 產生識別代號。至於 MySQL 的用戶，我都特別為它準備另一個 SQL Schema，改用 MySQL 特有的 AUTO_INCREMENT 欄位屬性。
MAX(id)
首先說明 MAX(id)+1 的方式。理論上，這個方式適用於各種 SQL 資料庫系統。至少我在 PostgreSQL, MS SQL Server 上一直這樣用。
CREATE TABLE testid (
id INT PRIMARY KEY,
content CHAR(10)
);
INSERT INTO testid (id, content)
VALUES (
(SELECT CASE WHEN MAX(id) IS NULL THEN 1 ELSE MAX(id)+1 END FROM testid),
'abc'
);
這個語法是用子查詢敘述呼叫 MAX() 找出目前 id 欄位的最大值再加1，然後將此子查詢的結果做為主查詢中插入 id 欄位的值。
它的執行計劃一般是進入交易後，先鎖住主資料表，接著進行子查詢，掃描 index ，取得最大值加一後，完成資料的新增動作。理論上是在同一個交易中完成，所以沒有安全性問題。
此外，有一些額外的處理技巧可以省下 CASE WHEN MAX(id) IS NULL 這個 filter。例如建立資料表格後立即新增一筆 id 為 0 的空記錄 (或預設記錄)，那麼爾後新增資料時，便不需考慮 MAX(id) IS NULL 的情形。
資料庫系統的特定方法
使用 MAX() 的方法雖然在理論上適用各種 SQL 資料庫，實際上我也一直這樣用。但不久前我終於在 MySQL 上踢到鐵板...
日前我寫了一個新的資料庫應用項目，一開始是連接 PostgreSQL ，接著移到測試機上改用 MySQL 。因為我忘了判斷資料庫系統，所以直接套用 MAX(id) 的新增資料方式。當場 MySQL 就丟給我一個錯誤訊息:
ErrorNo: 1093; You can't specify target table 'testid' for update in FROM clause
基本上，錯誤訊息表示 MySQL 不允許我在插入資料的主查詢中，再以子查詢去掃描資料表格的索引。我想這代表 MySQL 實作的資料鎖定能力有限，當它以寫入鎖鎖住資料表格後，就連子查詢讀取資料表格的動作也被排拒了。
Anyway, 當我發覺 MySQL 的鎖定能力有限時，我也不打算在這上頭多花時間。反正我早己習慣為 MySQL 特別準備一套 SQL schema。不過，我還是列一下我常用的三種資料庫系統所提供的自動產生識別代號的功能。
| Database | Data Type or Constraint | Also apply... | Not apply... |
|---|---|---|---|
| PostgreSQL (7.x,8.x) | SERIAL | NOT NULL | UNIQUE, PRIMARY KEY |
| MS SQL Server (2000,2005) | IDENTITY(1,1) | UNIQUE | NOT NULL, PRIMARY KEY |
| MySQL (5.x) | AUTO_INCREMENT | PRIMARY KEY (it's an attribute of key column) |
Example
CREATE TABLE testid (id SERIAL UNIQUE, content CHAR(10));
-- PostgreSQL, SERIAL is also apply NOT NULL constraint automatically.
CREATE TABLE testid (id INT NOT NULL IDENTITY(1,1), content CHAR(10));
-- MS SQL Server
CREATE TABLE testid (id INT PRIMARY KEY AUTO_INCREMENT, content CHAR(10));
-- MySQL, there can be only one auto column and it must be defined as a key.
INSERT INTO testid (content) VALUES ('abc');
-- You don't need to specify the value of id.
一般而言，我會直接把 PostgreSQL 的 SERIAL 視為等義的 INT NOT NULL IDENTITY(1,1) (MS SQL) 或 INT PRIMARY KEY AUTO_INCREMENT，直接替換。例如 s/\sSERIAL(?=\s*,)/$dbSerialType/gi';
樂多舊回應