MySQL入門（三）

標籤：

     寫了兩篇《MySQL入門》以後我發現，寫書的人還是都挺有本事的，起碼人家知道怎麼編排自己想講的知識點，我實在是不知道該先說那裡後說哪裡，那我就想到什麼講什麼吧。

  一 寫SQL

    其實我是不想寫有關SQL的部分的，因為這個部分其實很簡單，基本上大學只要好好聽聽資料庫概論這門課基本上都能寫滿足功能的SQL，但是後來想想，SQL其實是人和資料庫互動的一種介面，不會SQL確實是不可以的，寫的不好感覺不出資料庫有多麼的強大，甚至有可能會讓人產生出資料庫慢的壞印象。

     MySQL作為most popular的開來源資料庫，其實在SQL支援這裡做的不是那麼完美，比如5.6為止還是不支援全外串連（5.7沒試過是不是支援）。如果有人說全外串連這東西不支援就不支援了，沒什麼，但是我想說的是既然RDBMS是基於Codd的理論，那麼全外串連作為關係代數的一部分，不實現是有點說不過去的。相反，Berkeley出身的PostgreSQL就做的很好了，畢竟人家是most advanced開來源資料庫。

     閑話少敘。其實平時很多程式員寫的最多的SQL是這樣子的：

     

select t1.a, t1.b, t2.c, t2.d from table1 t1, table2 t2 where t1.m = t2.m and t1.k = ?;

     這個寫法一點問題都沒有，特別的自然，翻譯成漢語是這樣的：

     我需要從table1和table2中取一些資料出來，這兩張表通過m列關聯起來取交集，其中table1要卡k條件。

     這個SQL按下不表，繼續說些技術性的東西。

     基礎文法的東西就不說了，說一下串連。使用頻率最高的就是內串連和外串連兩種了。串連在我看來就是一種集合運算，比如內串連就是很自然的取交集運算，以兩張表的內串連說，就是A和B兩個集合做了A∩B運算。

     內串連，翻譯成英文就是inner join；外串連，翻譯成英文就是outer join。所以說SQL是很好理解的，甚至接近了自然的語言。

     寫一個內串連：

     

select t1.a, t1.b, t2.c, t2.d from table1 t1 inner join table2 t2 on t1.m = t2.m where t1.k = ?;

     這個SQL就是上面那個SQL的等價形式，只不過這是ANSI SQL92寫法，上面那種是ANSI SQL89寫法，SQL是一種語言，更是一種標準，新的標準更好理解，顯然的讓我們知道了這個SQL採用了內串連的形式。

     至於串連具體怎麼寫，我很早以前寫了一篇筆記：《Oracle的串連》。我就不重複發明輪子了，雖然當時採用的是Oracle，但是我說過了，SQL是一種標準，因此放到任何一個RDBMS裡，這些都是通用的。

     我本來就沒有打算把這個寫成什麼正式的東西，我沒有寫書的夢想，所以基本上都是扯淡，扯淡的中途講點知識。

  二 資料類型

    SQL也是一種程式設計語言！SQL也是一種程式設計語言！SQL也是一種程式設計語言！

    我要把這個強調三遍，因為很多很多，多入牛毛的同行都會覺得寫SQL是個很簡單的事情，SQL就是增刪改查資料，學一個小時就會的東西，是個很low的東西。我見過的很多人都問我SQL到底有什麼寫的，不就些破命令嗎？我真的不知道怎麼回答他們。

    SQL真的可以做很多事情，而寫好SQL並不太容易，當然，更不難。

    MySQL支援的資料類型大致分為：數字類型，時間日期類型，字元型。

    以前玩兒Oracle的時候，數字類型通通是number型，好用到沒朋友。但是MySQL不是這樣搞的，MySQL的數字類型分為tinyint, smallint, mediumint, int, bigint。每種類型都會有“有符號”和“無符號”的屬性，他們的範圍如下表：

類型	佔用空間(位元組)	最小值	最大值
tinyint	1	-128	127
tinyint（unsigned）	1	0	255
smallint	2	-32768	32767
smallint（unsigned）	2	0	65535
mediumint	3	-8388608	8388607
mediumint（unsigned）	3	0	16777215
int	4	-2147483648	2147483647
int（unsigned）	4	0	4294967295
bigint	8	-9223372036854775808	9223372036854775807
bigint（unsigned）	8	0	18446744073709551615

    很難記，但是其實不是很難，佔用空間這裡呢，我們都知道1Byte等於8bit，那麼tinyint的取值範圍就應該是[-2^(8-1), 2^(8-1)-1]。如果是無符號類型的取值範圍就能推匯出來了，有基本數學知識都能理解。

    我第一次玩兒MySQL的時候，發現列類型經常是int(4)這樣的，這個4是什麼呢？從上面的表中明顯可以看出數字型佔用的空間總是一定的，那這裡的4就不是char(4)裡面那種意思了，這其實是填充格式用的，要和ZEROFILL屬性一起用的，這樣就能讓欄位被填充滿，不過不影響取值，感覺像是文法糖之類的東西。

    後來我覺得MySQL的varchar型有意思，有意思就在於varchar括弧內的長度不是位元組數，而是字元數，與漢字英文無關，就是字元數，這個感覺又是一個文法糖之類的東西，我在設計表的時候就不需要考慮什麼字元集了，直接告訴程式員我給你了一個列，這個列最多存100個字元，你們自己控制。

    但是這裡還是有個陷阱。很多書上和網上的資料都會說，InnoDB表的所有varchar欄位長度的總和不得超過65535，就說到了這裡就不說了。實際上不能忽略一個前提，就是字元集應該選擇latin1，而且絕對不是65535，是65532，還有些別的開銷。如果換成UTF8字元集呢？這個理論總和限制就會變成21845，這個數字其實就是65535/3，實際上的限制是21844。所以我說這個是個文法糖，其實還是字元集相關的，只是不再讓上層的程式員去考慮了，而是由DB自己保證。

    時間和日期類型MySQL也是很豐富的，而且特別簡單，‘2015-12-12 12:31:20‘這樣就可以，Oracle的話還得寫個什麼函數去把字串轉成date型。當然時間和日期類型也是有空間佔用的，很多資料上都會說用TIMESTAMP類型取代DATETIME類型，這樣節省空間的，確實沒有問題，TIMESTAMP佔用的空間僅有DATETIME的一半，但是要注意，TIMESTAMP的範圍不大，1970年到2038年，節省空間的是對的，但是要注意應用情境。

    當然還有很討厭的BLOB和TEXT這樣的大資料類型（huge size data type），這可不是現在特別火的big data。這部分資料嘛，我覺得能不用就別用了，他們的儲存都是在單獨的地方，效率上我個人覺得堪憂。不過還是那句話，設計的時候要注意應用情境，給恰當的資料類型。

    今天就扯到這裡吧。

 

MySQL入門（三）