相关文章推荐

酷酷的牙膏 · 林葳为打NCAA舍弃600万年薪？媒体人：这 ...· 3 月前 ·

朝气蓬勃的豌豆 · 使用kindle推送小助手，将公众号文章、电 ...· 1 年前 ·

玩命的小虾米 · 中国摄影师首夺冠！2019年度野生动物摄影师 ...· 2 年前 ·

谈吐大方的伏特加 · IOT设备AI搭建3：TF ...· 2 年前 ·

想出国的甘蔗 · 我成了反派大佬的小娇妻？！漫画免费 - ...· 2 年前 ·

當您輸入電子信箱、訂閱本公司之「電子報」時，我們會向您蒐集、處理、利用的個資為您的「電子信箱」以及「提交日期」，當您輸入電子信箱並送出時，即表示您同意我們使用您的個資，為保障您的權益，關於更多相關政策更新資訊，請務必閱讀我們的「隱私權政策」、「使用條款」及「免責聲明」。如您不同意本網站之「隱私權政策」、「使用條款」及「免責聲明」，您可以隨時「取消訂閱」，謝謝您。

PK之必要性:

有些人主張Table不一定要有PK，但是我認應該每一張Table都要有PK，不論是單一或是複合PK，有PK的存在代表著Table的結構完整性，Table的Record能夠被唯一識別，另外PK也表示著其它Table的FK關聯，如果沒有PK那麼我們在操作Table時將會變的非常麻煩。

主鍵的無意義性：

我認為PK不應該具有實際的意義，這可能對於一些朋友來說不太認同，比如「訂單資料表」，會有「訂單編號」的欄位，而這個欄位呢在業務實際中本身就是應該具有唯一性，具有唯一標識記錄的功能，但我是不推薦採用「訂單編號」欄位作為PK，因為具有實際意義的欄位，具有“ 意義更改 ”的可能性，比如訂單編號在剛開始的時候我們一切順利，後來客戶說“ 訂單可以作廢，並重新生成訂單，而且訂單號要保持原訂單號一致 ”，這樣原來的PK就面臨重覆的危險了。因此雖然人造資料也許可能是刻意不讓它重覆，但經過人為的新、刪、修可能就會不小心重覆，這再次強調人為無法保證不重覆，若設成PK就會造成系統崩潰。因此，我推薦是新設一個欄位專門作為PK，此PK本身在商業邏輯上不必呈現，不具有實際意義。而這種PK設計可降低某些程式的複雜度(因為可不用複合主鍵)，且不論您本來的Table設計如何都可以加入無意義的PK。

主鍵的選擇策略:

我們現在在思考一下，應該採用什麽來作表的主鍵比較合理，說明一下，主鍵的設計沒有一個定論，各人有各人的方法，哪怕同一個，在不同的項目中，也會採用不同的主鍵設計原則。

第一：編號作主鍵

此方法就是採用實際業務中的唯一欄位的“ 編號 ”作為主鍵設計，這在小型的項目中是推薦這樣做的，因為這可以使項目比較簡單化，但在使用中卻可能帶來一些麻煩，比如要進行“ 編號修改 ”時，可能要涉及到很多相關聯的其他表，因為人造資料也可能事後被要求允許重復。無論我們再怎麽先知先覺，都無法預測業務將會修改成什麽?

第二：自動編號主鍵

這種方法也是很多朋友在使用的，就是新建一個ID欄位，自動編號，非常方便也滿足主鍵的原則。

優點是：DB自動編號，速度快，而且是有順序性，聚集型主鍵按順序存放，對於檢索非常有利；數字型的，占用空間小，易排序，在程式中傳遞參數也方便；如果透過非系統增加Record（比如手動錄入，或是用其他工具直接在表裏插入新記錄，或老系統匯入資料）時，非常方便，不用擔心主鍵重復問題。

缺點：其實缺點也就是來自其優點，就是因為自動增長，在手動要插入指定ID的記錄時會顯得麻煩(無法指定編號)，尤其是當系統要移植到別的DB時很難保證原系統的ID不發生主鍵衝突（前提是老系統也是數字型的）;如果其他系統主鍵不是數字型那就麻煩更大了，會導致修改主鍵數據類型了，這也會導致其他相關表的修改，後果同樣很嚴重;就算其他系統也是數字型的，在導入時，為了區分新老數據，可能想在老數據主鍵前統一加一個“o”(old)來表示這是老數據，那麽自動增長的數字型又面臨一個挑戰。但是這個問題我們可以在資料順利移植完成後SQL語句來修正，ex:

Old資料表

Import to New資料表+自動編號

加入原來的關連指令

Query SQL命令如下:

SELECT TableA.new_idAA, TableA.idAA, TableA.AA, TableA.BB, TableB.idAA, TableB.XX, TableB.idBB, TableB.識別碼

FROM TableA INNER JOIN TableB ON TableA.idAA = TableB.idAA ;

SQL執行如下，注意它仍是用舊的PK。

經過下列 Update SQL命令修正後，已變為使用新加入的 PK。

update 查詢1 set TableB.idAA = TableA.new_idAA ; ………(出奇的簡單)

接下來就可以修正TableA and TableB還原成以前的樣子。

    
     刪掉 TableA.idAA

    
     TableA.new_idAA改為TableA.idAA

    
     刪掉TableB.idBB

    
     TableB.識別碼改為TableB.idBB

所以說明白點就是只要用上面的紅色字體 SQL命令就可以移植資料庫了，而且這個Update SQL 還出奇的簡單。

第三：Max加一

由於自動編號存在那些問題，所以有些朋友就採用自己生成，同樣是數字型的，只是把自動編號去掉了，採用在Insert時，讀取Max值後加一，這種方法可以避免自動編號的問題，但也存在一個效率問題，如果記錄非常大的話，那麽Max()也會影響效率的(sort)；更嚴重的是Thread同步問題，如果同時有兩人讀到相同的Max後，加一後插入的ID值會重復，這已經是有經驗教訓的了(高鐵重覆訂位問題)。

第四：自製加一

考慮Max加一的效率後，有朋友採用自制加一，也就是建一個特別的表，欄位為：表名，當前序列值，類似Oracle的Sequence。這樣在往表中插入值時，先從此表中找到相應表的最大值後加一，進行插入，但是仍然無法避免Thread同步問題，我們可以採用Synchronized Thread的方式來避免，在生成此值的時，先Lock，取到值以後，再unLock出來，這樣不會有兩人同時生成了。這比Max加一的速度要快多了。但同樣存在一個問題：在與其他系統合作時(分散式計算)，脫離了系統中的Synchronized Thread方法後，無法保證自制表中的最大值與導入後的保持一致。

第五：GUID主鍵

目前個人覺得比較好的主鍵是採用Time+GUID ，但值由GUID生成，GUID是可以DB自動生成，也可以程序(Java, C#....) 生成，而且鍵值不可能重復(機率太小可略過)，可以解決集成系統或移植問題，幾個不同的Table的GUID值導到一起時，也不會發生重復，而且效率很高，因為GUID的不重複性是可跨資料庫與資料表，在.NET裏可以直接使用System.Guid.NewGuid()進行生成，在SQL Server裏也可以使用 NewID()生成。優點是：與自動增欄位量相比，uniqueidentifier 值可以通過 NewID() 函數提前得知新增加的行 ID ，為應用程序的後續處理提供了很大方便, ex:新增Row後, 取得PK, 再異動到其它Table。

便於資料庫移植，其它資料庫中並不一定具有自動增量欄位，而 Guid 列可以作為String轉換到其它資料庫中，同時將應用程序中產生的 GUID 值存入資料庫，它不會對原有數據帶來影響。

便於資料庫初始化，如果應用程序要加載一些初始數據，自動增量的處理方式就比較麻煩，而 uniqueidentifier 值則無需任何處理，直接用 SQL 加載即可。

便於對某些Object或final或const進行永久標識，ex:類別的 ClassID，Object Instance的實例標識，UDDI 中的聯系人、Service Interface、Model ID定義等。

GUID 值較長，不容易記憶和輸入，而且這個值是隨機、無順序的。

ex:比較難自己手動下SQL取得資料
SELECT * FROM ORDER WHERE ORDERID = 12
比以下這個 SQL 好下多了:
SELECT * FROM ORDER WHERE ORDERGUID = ‘ {45F57B42-38A4-46ce-A180-6DE0E7051178} '

GUID 的值有 16 個byte，與其它那些諸如 4byte的整數相比要相對大一些。這意味著如果在資料庫中使用 uniqueidentifier 鍵，可能會帶來兩方面的缺點：存儲空間增大；索引時間較慢。但是也有學者提出 (組合時間-GUID)，稱為COBM GUID，它提供有順序性的GUID值，但重覆的機率會比原生的GUID大些，

我也不是推薦GUID最好(但可考慮增量型組合式GUID ,時間要設定index)，其實在不同的情況，我們都可以採用上面的某一種方式，思考了一些利與弊，也方便大家在進行設計時參考。這些也只是我的一點思考而已，而且可能我知識面限制，會有一些誤論在裏面，希望大家有什麽想法歡迎討論。

UUID常用程式寫法:

public class Test { public static void main(String[] args) { UUID uuid = UUID.randomUUID(); System.out.println(uuid);

consloe output:

f828fa0f-51d8-49c1-83ed-a6943e276274

當需要使用更少的字元表示GUID時，可能會使用 Base64 或 Ascii85 編碼。Base64編碼的GUID有22－24個字元，如：

7QDBkvCA1+B9K/U0vrQx1A

7QDBkvCA1+B9K/U0vrQx1A==

Ascii85 編碼後是20個字元，如：

5:$Hj:Pf\4RLB9%kU\Lj

但Ascii85字元不利於 http query string 傳送

java使用Base64方法如:

http://magiclen.org/java-base64/

String UUID as base64 String ( 連結 )

CREATE TABLE `test`.`mytable02` ( `idmytable02` int(10) unsigned NOT NULL AUTO_INCREMENT, `myname` varchar(45) NOT NULL, `createti` datetime NOT NULL, PRIMARY KEY (`idmytable02`) ) ENGINE=InnoDB AUTO_INCREMENT DEFAULT CHARSET=utf8;

for Autoint

DROP TABLE IF EXISTS `test`.`mytable01`;
CREATE TABLE  `test`.`mytable01` (
  `idmytable01` char(36) NOT NULL,
  `myname` varchar(45) NOT NULL,
  `createtime` datetime NOT NULL,
  PRIMARY KEY (`idmytable01`),
  KEY `Index_2` (`createtime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

for Autoint+UUID

可以使得select 效率與 autoint 幾乎一樣快

import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.Timestamp; import org.junit.Test; public class TestGUID { static int rowCount = 500000; @Test public void testAuto_UUID() { long t1 = System.currentTimeMillis(); insert_auto_UUID(); long t2 = System.currentTimeMillis(); System.out.println((t2-t1)); @Test public void testUUID() { long t1 = System.currentTimeMillis(); insertUUID(); long t2 = System.currentTimeMillis(); System.out.println((t2-t1)); @Test public void testAuto() { long t1 = System.currentTimeMillis(); insertAuto(); long t2 = System.currentTimeMillis(); System.out.println((t2-t1)); private void insertUUID(){ try { Class.forName("com.mysql.jdbc.Driver"); String url = "jdbc:mysql://localhost:3306/test"; String user = "root"; String password = "1234"; Connection conDB = DriverManager.getConnection(url, user, password); long now = System.currentTimeMillis(); PreparedStatement ps = conDB .prepareStatement("insert into mytable01 values(?,?,?)"); for (int i = 0; i < rowCount; i++) { java.util.UUID uuid = java.util.UUID.randomUUID(); ps.setString(1, uuid.toString()); ps.setString(2, "Hello"); ps.setTimestamp(3, new Timestamp(now)); int ans = ps.executeUpdate(); conDB.close(); } catch (Exception e) { e.printStackTrace(); private void insert_auto_UUID(){ try { Class.forName("com.mysql.jdbc.Driver"); String url = "jdbc:mysql://localhost:3306/test"; String user = "root"; String password = "1234"; Connection conDB = DriverManager.getConnection(url, user, password); long now = System.currentTimeMillis(); PreparedStatement ps = conDB .prepareStatement("insert into mytable03(myuuid03,myname,createtime) values(?,?,?)"); for (int i = 0; i < rowCount; i++) { java.util.UUID uuid = java.util.UUID.randomUUID(); ps.setString(1, uuid.toString()); ps.setString(2, "Hello"); ps.setTimestamp(3, new Timestamp(now)); int ans = ps.executeUpdate(); conDB.close(); } catch (Exception e) { e.printStackTrace(); private void insertAuto(){ try { Class.forName("com.mysql.jdbc.Driver"); String url = "jdbc:mysql://localhost:3306/test"; String user = "root"; String password = "1234"; Connection conDB = DriverManager.getConnection(url, user, password); long now = System.currentTimeMillis(); java.util.UUID uuid = java.util.UUID.randomUUID(); PreparedStatement ps = conDB .prepareStatement("insert into mytable02(myname,createti) values(?,?)"); for (int i = 0; i < rowCount; i++) { ps.setString(1, "Hello"); ps.setTimestamp(2, new Timestamp(now)); int ans = ps.executeUpdate(); conDB.close(); } catch (Exception e) { e.printStackTrace(); @Test public void test2() { for (int i = 0; i < 3; i++) { java.util.UUID uuid = java.util.UUID.randomUUID(); System.out.println(uuid.toString());