SQL編寫小記

下面的做法大多對應與一般的情況，關于靈活性與安全性的一些做法，特殊的情況特殊處理總是有他的好處。關于SQL注入方面的內容之前已經寫過一片博文了，在此不再提及

多使用交叉表（實體-關系）

經常會碰到一對多，多對多這樣的邏輯關系的關系型數據庫設計，特別是更多的時候是對應的數量不確定，會隨著時間的推移而發生變化（比如就是說商品A有4個屬性，商品B卻有5個屬性）。這個時候，就不應該把這種對應關系限定在一張表內，而是把這些實體分表儲存，然后用一張表儲存他們的對應關系，這場儲存對應關系的表，就是交叉表

當在思考下面這些問題的時候，可能就需要考慮建立交叉表：

1. 可能需要在一張表中存入一個字段，這個字段里面有好多個值，需要用逗號還是什么標記符隔開，或者還想保證不重復

2. 列表最多能儲存多少數據（受限于varchar的長度限制）

3. 當需要用正則表達式來提取字符串中的數據

4. 哪些字符絕對不會出現在數據中（選擇合適的分隔符的時候）

5. 一個列不能同時指定兩個外鍵（就像這種情況：在個人網站里面既有說說又有博客，瀏覽者都可以在里面發表評論，這樣一般來說就有三個表，說說的內容，博客的文章內容，還有評論，然后設置約束的時候，評論的id對應說說內容的id，同時也對應了博客內容的id，但是在設置評論表的外鍵的時候卻不能設置兩個外鍵。這時就可以設置兩個交叉表）

是否真的需要id

現在好多人大多都習慣性的每建立一張新表就設置一個偽鍵id，來作為表的主鍵。聲明主鍵是必要的，然而哪里都不分青紅皂白的使用id并不是一件好事。它可能會成為冗余的鍵值，可能會使得你的數據庫允許出現重復的項，也可能會因為id的意義不明確而帶來混淆。

所以，在真正建表的時候，應該仔細考慮主鍵的選擇問題

1. 如果采用了偽主鍵，就應該起一個意義清晰的名字，比如說account_id,而不是簡單粗暴的id

2. 考慮使用自然健和組合健。當你發現你的表中有一列能唯一標識一條記錄的列，可能就沒有必要設置id來作為主鍵了，合適的時候，如果多個列的組合能夠唯一的確定一條記錄，也可以考慮使用組合鍵

多使用約束

據說有很多程序員不推薦引用完整性約束，或是說更新數據的時候可能會沖突，或是說影響性能blablabla....但是，當不引用完整性約束的時候，所付出的代價就是必須增加額外的代碼來保證數據的正確，即使保證代碼的邏輯嚴謹性是程序員必須做的事情。但是：

1. 如果要寫出一個沒有瑕疵的代碼，意味著進行數據操作的時候要檢查所有的引用關系。如果恰好在刪除操作的查詢和刪除操作的執行之間插入了一個新的記錄，就會引發一個錯誤的引用關系的問題。要解決這個問題，就要在刪除的時候加鎖，搞完再解鎖。然而這么做在高并發和大量數據查詢的時候表現就會很差

2. 或者有人會想用一個腳本來檢查錯誤的數據。當真正發現錯誤的時候，下一步就是解決錯誤，有些問題是可以解決的，比如通過設置默認值。但是，有些時候就會面臨一個很尷尬的境地，比如有個屬性叫publisher.....

3. 要寫出一個真正沒有瑕疵的代碼，真的很難，比如說整個邏輯必須考慮的非常嚴謹，比如說在代碼迭代的時候必須保證把每個地方都一起改對，等等...

拆分表

項目一開始的時候，可能數據庫三下五除二的就設計好了。但是在開發，或者迭代的時候，可能會產生新的需求，還是說一開始的設計出現了問題，這個時候可能就需要不斷的擴展這張表。還可能，隨著時間的推移，數據量越來越龐大，查詢耗時越來越多，這個時候可能就需要將表分區。

水平分區

當一張表的數據量非常巨大的時候，就可以水平將表分區，在建表的時候就可以做如下設定：

CREATE TABLE story(
    story_id INT PRIMARY KEY AUTO_INCREMENT,
    time DATE
) PARTITION BY HASH(YEAR(time))
  partitions 4;

這樣就可以將實際儲存數據的物理表分成4張，也不用擔心數據會在錯誤的標里面，直接查詢也能得到結果

垂直分區

當某些列非常龐大（如BLOB或TEXT）或者很少使用的時候，就可以將這列分在另外一個表里面，然后用外鍵建立聯系

取整

當在數據庫某一屬性的類型是FLOAT或者DOUBLE的時候，由于二進制的原因，某個數會變成無限小數，這個時候數據庫里面存的就是近似值，查詢的時候救不得不限定一個合理的精度，就像這樣：

SELECT * FROM account WHERE ABS（value - 60.00） < 0.000001;

有時候誤差雖然很小，但是累積起來就會變得很大。

對于FLOAT和DOUBLE，其實還有更好的選擇，NUMERIC 和 DECIMAL。類型的精度是創建的時候指定的，同時，他不會像FLOAT那樣將存儲的有理數進行舍入操作

數據庫儲存文件

很多時候，項目需要在服務起存儲圖片啊什么的這樣的數據，直覺上來看，很自然的方法就是使用文件系統，然后子數據庫里面用VARCHAR來存文件的路徑。但是，這么做也有很多缺陷：

1. 在數據庫里面操作數據（比如說刪除），刪掉之后實質上文件還在

2. 文件不支持輸入

3. 文件不支持回滾操作

4. 文件不支持數據庫悲憤工具

5. 文件不支持SQL 的訪問權限設置

6. 文件也不是SQL數據類型（意味著數據庫不能用約束去驗證這個文件的正確性）

事實上，可以用BLOB類型，直接將文件存在數據庫里面，像MySQL就可以這么做

UPDATE account
SET image=LOAD_FILE('images/1.jpg')
WHERE user_id = 1;

當需要取出來的時候，就可以這么做

SELECT image
INTO DUMPFILE 'images/1.jpg'
FROM account
WHERE id=1;

多次查詢

SQL很強大，也富有表現力，可以一句話做很多的事情。但是，復雜的SQL查詢很難寫，寫出來了也很容易錯，所以完全可以分開多次進行查詢

不使用*

使用 可以少打幾個字，但是也會帶來很大的問題，因為 的不可知性，可能會引發一些錯誤。一來可能會破會代碼重構，因為他引發的一些問題比較難找。二來一次性查詢的列越多，開銷也越大。