儲存引擎與資料型態
1 表格與儲存引擎
表格(table)是資料庫中用來儲存紀錄的基本單位，在建立一個新的資料庫以後，你必須為這個資料庫建立一些儲存資料的表格：
每一個資料庫都會使用一個資料夾，這些資料庫資料夾用來儲存所有資料庫各自需要的檔案：
「Storage engine、儲存引擎」是MySQL用來儲存資料的技術，為了資料庫多樣化的應用，你可以在建立表格的時候，依照自己的需求指定一種儲存引擎，不同的儲存引擎會有不同的資料儲存方式與運作的特色。MySQL提供許多儲存引擎讓你選擇，下列是主要的三種儲存引擎的簡介：
MyISAM： MySQL預設的儲存引擎，雖然它支援的功能並沒有像一般的資料庫那麼多(例如交易、transaction)；不過也因為它比較簡單，所以運作的效率相對也比較好
InnoDB： 這種儲存引擎所提供的功能已經跟大型的商用資料庫軟體一樣了，像是交易(transaction)、紀錄鎖定(row-level locking) 與自動回復(auto-recovery)。
MEMORY： 這是一個比較特殊的儲存引擎，它把資料儲存在紀憶體中，所以運作的效率是最快的；不過只要MySQL伺服器關閉後，儲存的資料就全部不見了。
1.1 MyISAM
「MyISAM」是MySQL預設的儲存引擎，「預設」的意思是如果你在建立表格的時候沒有指定一種儲存引擎，MySQL會幫你建立的新表格指定為「MyISAM」儲存引擎。以下列一個使用MyISAM儲存引擎的資料庫來說，在資料庫資料夾中的檔案會像這樣：
當你建立一個表格以後，「MyISAM」儲存引擎會建立以表格名稱為檔案名稱的三個檔案，以「city」表格來說：
使用「MyISAM」儲存引擎的資料庫具有「可攜性、portable」的特色，你可以很容易的把一個資料庫複製到另外一台電腦的MySQL伺服器中：
註： 使用「MyISAM」儲存引擎時，MySQL並不會限制一個資料庫中可以包含的表格數量。不過一個表格會在檔案系統中建立三個檔案，如果超過作業系統對於檔案數量或容量的限制，你就不能再建立任何新的表格。
1.2 InnoDB
MySQL資料庫伺服器從3.23.49版本開始把「InnoDB」儲存引擎列為正式支援的功能，所以從這個版本開始，MySQL也提供與大型商用資料庫軟體一樣的功能。最主要的功能是支援「交易、transaction」，在比較複雜的資料庫應用系統中，很常遇到這樣的情況：
在順利的情況下，當然不會有任何問題。可是如果發生下列的情況：
這樣的情況是一定要避免，否則資料庫中儲存的資料就會出現很大的問題了。所以一般的大型商用資料庫都會使用交易的功能來處理這樣的情況：
「InnoDB」儲存引擎除了提供許多功能外，與「MyISAM」儲存引擎最大的差異是檔案的儲存方式：
「InnoDB」儲存引擎實際儲存在檔案系統中的檔案會像這樣：
註： 因為使用「InnoDB」儲存引擎的表格會使用同一個儲存空間，所以不同資料庫的表格資料也會儲存在一起。「InnoDB」儲存引擎限制在這個共用的儲存空間中不能超過兩百萬個表格。
1.3 MEMORY
「MEMORY」儲存引擎與其它儲存引擎有一個主要的差異，就是它會把紀錄與索引資料儲存在記憶體中。所以使用「MEMORY」儲存引擎的表格，不論在查詢或維護資料時的效率都是很好的。在檔案系統中儲存的檔案只有「frm」檔，也就是儲存表格結構資訊的檔案：
註： 因為「MEMORY」儲存引擎會把紀錄與索引資料儲存在記憶體中，所以只要MySQL伺服器關閉、重新啟動、當機，所有使用「MEMORY」儲存引擎的表格資料都會全部消失，只剩下表格結構；它也不適合儲存大量資料的表格，會耗用太多記憶體
1.4 儲存引擎與作業系統
雖然MySQL資料庫是一個獨立運作的軟體，不過它還是得安裝在某一個作業系統中，例如Windows或Linux。而由作業系統控制的檔案系統可能會有許多限制，例如檔案的數量和檔案的大小。如果MySQL資料庫軟體在建立或使用資料庫檔案的時候，超過作業系統的限制，就會發生錯誤。
如果以支援的功能來決定儲存引擎的話，那就會比較明確。如果要以作業系統的限制來決定儲存引擎的話，你可以參考下列的作法：
使用「MyISAM」儲存引擎可以避免違反檔案大小的限制
使用「InnoDB」儲存引擎可以避免違反檔案數量的限制
如果在檔案數量與大小的限制都遇到問題的話，你只好增加硬體和修改作業系統在檔案系統上的設定
2 欄位資料型態
在建立表格時，你會幫每一個欄位指定適合的「資料型態、data type」。正確的選擇欄位資料型態，除了可以幫你儲存正確的資料外，還可以讓資料庫使用最少的記憶體與儲存空間，這樣會讓資料庫運作的效率更好一些。資料型態主要分為下列三大類：
數值： 任何包含正、負號的整數與小數資料；另外還有位元(bit)的數值資料，它使用二進位來表示一個數字。
字串： 包含non-binary與binary兩種字串值，non-binary字串值是一些使用字元集與collation的字元(character)組合起來的；binary字串值是一些位元組(bytes)組合的資料。
日期與時間： 包含日期、時間與日期加時間。
2.1 數值
數值資料分為整數與小數資料，下列是MySQL提供的整數型態：
| 型態 | Byte(s) | 預設長度 | 有號數範圍 | 無號數範圍 |
|---|---|---|---|---|
| TINYINT[(長度)] | 1 | 4 | -128~127 | 0~255 |
| SMALLINT[(長度)] | 2 | 6 | -32768~32767 | 0~65535 |
| MEDIUMINT[(長度)] | 3 | 9 | -8388608~8388607 | 0~16777215 |
| INT[(長度)] | 4 | 11 | -2147683648~2147683647 | 0~4294967295 |
| BIGINT[(長度)] | 8 | 20 | -9223372036854775808~9223372036854775807 | 0~18446744073709551615 |
整數型態的意思就是它們不能儲存小數，在建立表格的時候，如果需要一個可以儲存整數資料的欄位，你可以依照整數資料的大小需求，選擇一個夠用又不會太浪費空間的整數形態。以下列的「cmdev.integertable」表格來說：
| 欄位名稱 | 型態 | 範圍 |
|---|---|---|
| n | TINYINT(4) | -128~127 |
| n2 | SMALLINT(6) | -32768~32767 |
| n3 | MEDIUMINT(9) | -8388608~8388607 |
| n4 | INT(11) | -2147683648~2147683647 |
| n5 | BIGINT(20) | -9223372036854775808~9223372036854775807 |
整數型態的後面會在左右刮號中指定一個數字，以「SMALLING」型態來說：
當你在執行資料的新增或修改的時候，就要特別注意它們的可以儲存數字的範圍：
整數型態的欄位，就表示它們不可以儲存小數的數值：
數值型態還有下列幾種可以儲存小數資料的浮點數型態：
| 型態 | Byte(s) | 預設長度 | 最大長度 | 說明 |
|---|---|---|---|---|
| FLOAT[(長度,小數位數)] | 4 | 註1 | 255, 30 | 單精確度浮點數(近似值) |
| DOUBLE[(長度,小數位數)] | 8 | 255, 30 | 雙精確度浮點數(近似值) | |
| DECIMAL[(長度[,小數位數])] | 註2 | 10, 0 | 65, 30 | 自行指定位數的精確值 |
註1： FLOAT與DOUBLE的預設長度會因為不同的作業系統而有不一樣的長度
註2： 依照指定的位數決定實際儲存的空間
「FLOAT」和「DOUBLE」型態的欄位可以用來儲存包含小數的數值，儲存空間分別是4和8個位元組，它們是一種佔用儲存空間比較小，執行運算比較快的型態。不過因為它們是使用「近似值」來儲存你的數值，所以如果你需要儲存完全精準的數值，就不能使用這兩種型態。
另外一種可以儲存小數數值的「DECIMAL」型態就可以用來儲存完全精準的數值，儲存在這個型態中的數值，不論是查詢或是運算，都不會有任何誤差，不過「DECIMAL」型態佔用的儲存空間就比「FLOAT」和「DOUBLE」型態大。「DECIMAL」型態在MySQL還有一個一樣的關鍵字是「NUMERIC」，這兩種型態完全一樣。
在MySQL中，「FLOAT」、「DOUBLE」和「DECIMAL」都可以依照自己的需要設定長度與位數：
在設定長度與小數位數的時候，要注意下列幾個規則：
不可以超過最大長度
小數位數不可以超過長度
長度與小數位數一樣的時候，表示只可以儲存小數，例如「0.123」
MySQL的數值型態，包含整數與浮點數都可以設定為「只能儲存正數」，以下列的「cmdev.numerictable」表格來說：
| 欄位名稱 | 型態 |
|---|---|
| i | TINYINT(3) UNSIGNED |
| i2 | SMALLINT(5) UNSIGNED |
| i3 | MEDIUMINT(8) UNSIGNED |
| i4 | INT(10) |
| i5 | BIGINT(20) UNSIGNED |
| f | FLOAT UNSIGNED |
| f2 | DOUBLE |
| f3 | DECIMAL(10, 0) UNSIGNED |
設定為只能儲存正數的欄位，就跟字面上的效果一樣，任何希望儲存負數的動作都會造成錯誤：
MySQL的數值型態都可以依照自己的需要設定長度，以下列的「cmdev.numerictable2」表格來說：
| 欄位名稱 | 型態 |
|---|---|
| i | TINYINT(3) |
| i2 | SMALLINT(3) |
| i3 | MEDIUMINT(3) |
| i4 | INT(3) |
| i5 | BIGINT(3) |
| f | FLOAT(5, 2) |
| f2 | DOUBLE(5, 2) |
| f3 | DECIMAL(5, 2) |
同樣為數值型態設定長度，在整數和浮點數會有不一樣的效果。如果你為整數型態的欄位設定長度的話，這個長度只是設定顯示的長度而已，並不會影響實際儲存的長度：
為浮點數型態設定長度與小數位數的時候，效果就跟整數型態不一樣了：
不過在整數位數的部份，就一定會依照設定來儲存，否則會造成錯誤：
MySQL的在數值型態的設定上，還有一個比較特別的設定，就是「ZEROFILL」，以下列的「cmdev.numerictable3」表格來說：
| 欄位名稱 | 型態 |
|---|---|
| i | TINYINT(3) UNSIGNED ZEROFILL |
| i2 | SMALLINT(4) UNSIGNED ZEROFILL |
| i3 | MEDIUMINT(5) UNSIGNED ZEROFILL |
| i4 | INT(6) UNSIGNED ZEROFILL |
| i5 | BIGINT(7) UNSIGNED ZEROFILL |
| f | FLOAT(5, 2) UNSIGNED ZEROFILL |
| f2 | DOUBLE(7, 3) UNSIGNED ZEROFILL |
| f3 | DECIMAL(9, 5) UNSIGNED ZEROFILL |
「ZEROFILL」的設定表示在查詢這些欄位的時候，回傳的資料會在左側根據長度的設定填滿「0」：
註： 「ZEROFILL」一定要跟「UNSIGNED」一起使用，就算你只有為欄位設定「ZEROFILL」，MySQL也會自動加入「UNSIGNED」的設定。
整數型態的部份，在補0的處理上會不太一樣：
2.2 位元
「位元、BIT」型態其實也是用來儲存數值用的，不過它是以二進位的型式儲存資料，也就是只有0跟1兩種資料。MySQL的在數值型態的設定上，還有一個比較特別的設定，就是「ZEROFILL」，以下列的「cmdev.numerictable3」表格來說：
| 欄位名稱 | 型態 | 數字範圍 |
|---|---|---|
| n | BIT | 0~1 |
| n2 | BIT(8) | 0~255 |
| n3 | BIT(64) | 0~18446744073709551615 |
你可以直接儲存數字到位元型態的欄位；也可以指定一個使用二進位表示的值：
2.3 字串
MySQL把字串型態分為兩大類： 「非二進位制、non-binary」與「二進位制、binary」。非二進位制就是儲存一般文字的字串，會有特定的字元集與collation；二進位制使用位元組儲存資料，不包含字元集與collation，所以大多用來儲存圖片或音樂這類資料。「非二進位制、non-binary」的字串型態有下列幾種：
| 型態 | 最大長度 | 實際儲存的空間 | 說明 |
|---|---|---|---|
| CHAR[(長度)] | 255 | 指定的長度 | 固定長度的字串，預設長度為1 |
| VARCHAR(長度) | 65535 | 字元個數加1或2bytes | 變動長度的字串 |
| TINYTEXT | 255 | 字元個數加1byte | TEXT |
| 65535 | 字元個數加2bytes | MEDIUMTEXT | 16,772,215 |
| 字元個數加3bytes | LONGTEXT | 4,294,967,295 | 字元個數加4bytes |
固定長度與變動長度的兩種字串型態都可以儲存字串，差異在儲存的文字個數小於型態指定的長度時，變動長度實際儲存的空間會小一些，以下列的「cmdev.nonbinarytable」表格來說：
| 欄位名稱 | 型態 |
|---|---|
| s | CHAR(10) |
| s2 | VARCHAR(10) |
同樣把長度設定為10的「CHAR」與「VARCHAR」字串型態，它們在儲存字串資料的時候會不太一樣：
「非二進位制、non-binary」的字串都會包含特定的字元集與collation，所以可以用來儲存各種不同國家的文字。不同的字元集會佔用不同的儲存空間，以下列的「cmdev.nonbinarytable2」表格來說：
| 欄位名稱 | 型態 | 字元集 |
|---|---|---|
| s | VARCHAR(6) | latin1 |
| s2 | VARCHAR(6) | big5 |
| s3 | VARCHAR(6) | utf8 |
上列的表格中，三個欄位分別設定為「latin1」、「big5」與「utf8」字元集，你可以查詢MySQL資料庫支援的字元集特性，「MAXLEN」欄位是關於儲存空間的資訊：
使用在「LENGTH」函式來查詢儲存在這個表格中的字串資料，就可以很明顯的看出不同的字元集，在儲存字元時使用的儲存空間：
「LENGTH」函式會傳回字串資料實際的儲存長度(byte)；如果你要查詢字串的字元數量的話，就要使用「CHAR_LENGTH」函式：
字元集會影響字串的儲存空間，collation會影響字串排列順序。以下列的「cmdev.nonbinarytable3」表格來說：
| 欄位名稱 | 型態 | 字元集 | Collation |
|---|---|---|---|
| s | VARCHAR(6) | latin1 | latin1_general_ci |
| s2 | VARCHAR(6) | latin1 | latin1_general_cs |
上列表格中欄位的字元集都指定為「latin1」，不過「s」欄位的collation設定為「latin1_general_ci」，表示排序時不區分大小寫；「s2」欄位設定為「latin1_general_cs」，表示排序時會區分大小寫。以下列儲存在這個表格中紀錄來說：
Collation設定中的「latin1_general_ci」，最後的「ci」表示「case insensitive」，是不分大小寫的意思。在這樣的設定下，MySQL會把字串「ABC」和「abc」當成是一樣的；「latin1_general_cs」，最後的「cs」表示「case sensitive」，是區分大小寫的意思。在這樣的設定下，MySQL就會把字串「ABC」和「abc」當成是不一樣的字串。
是否區分大小寫的collation設定會影響排序的結果：
另外一個影響是條件的判斷：
「二進位制、binary」的字串型態是使用位元組(byte)為單位來儲存字串資料，跟非二進位制的字串類似，它也提供許多應用在不同長度的型態：
| 型態 | 最大長度(byte) | 實際儲存的空間(byte) | 說明 |
|---|---|---|---|
| BINARY[(長度)] | 255 | 指定的長度 | 固定長度的字串，預設長度為1 |
| VARBINARY(長度) | 65535 | 長度加1或2bytes | 變動長度的字串 |
| TINYBLOB | 255 | byte數加1byte | BLOB |
| 65535 | byte數加2bytes | MEDIUMBLOB | 16,772,215 |
| byte數加3bytes | LONGBLOB | 4,294,967,295 | byte數加4bytes |
「BINARY」與「VARBINARY」兩種型態的差異，與「CHAR」和「VARCHAR」的差異一樣。在一般的情況下，使用「VARBINARY」會比「BINARY」節省一點儲存空間。你也可以使用「二進位制、binary」型態儲存文字資料，只不過MySQL都是以位元組來儲存所有的資料，也就是0到255的數字：
所有「二進位制、binary」的字串型態都不可以指定字元集與collation，不過你可以使用它們來儲存任何語言的文字，也可以儲存類似音樂或圖片資料，因為MySQL都是一個一個byte的把資料儲存到資料庫中；所以在執行查詢時的排序和條件設定，都是以使用位元組為單位來判斷。
2.4 列舉與集合
列舉(ENUM)與集合(SET)是一種特殊的「非二進位制、non-binary」字串型態，所以它們也可以指定字元集與collation。下列是這兩種型態的說明：
| 型態 | 最大個數 | 儲存空間 | 說明 |
|---|---|---|---|
| ENUM(字串值[,...]) | 65535 | 1byte(255個)2bytes(256到65535個) | 包含一組合法的字串值(單一值) |
| SET(字串值[,...]) | 64 | 1byte(8個)2bytes(16個)3bytes(24個)4bytes(32個)8bytes(64個) | 包含一組合法的字串值(多個值) |
列舉(enumeration)的資料在資料庫中的應用很常見，例如服裝的大小就會以S、M與L來表示小、中與大。你可以使用字串來儲存這類資料，不過這類的資料也很適合使用「ENUM」型態來儲存。以下列的「cmdev.enumtable」表格來說：
在儲存資料的時候，「ENUM」型態看起來似乎與「VARCHAR」完全一樣：
可是列舉型態在資料的正確性方面，就會比單純的字串型態好多了。例如下列錯誤示範：
列舉型態欄位除了可以直接使用字串值來新增與更新資料外，還可以使用數值資料的編號來代替，任何一個列舉型態中的成員，MySQL都會幫它們編一個號碼：
瞭解列舉型態中成員的編號以後，你可以選擇字串值或數值來管理列舉型態欄位儲存的資料：
雖然在查詢列舉型態欄位資料的時候，所得到的結果都是成員的字串值；不過真正儲存在資料庫中的資料卻是成員的編號，所以指定列舉型態欄位為排序欄位的時候，資料庫會使用編號來排序，而不是以成員的字串值：
在指定列舉型態欄位的查詢條件時，可以使用成員的字串值或編號：
集合(SET)型態同樣可以設定一組成員，不過它可以儲存多個成員資料。例如星期的成員總共有七個，而需要工作的星期就會有一個以上了，類似這樣的需求就應該使用集合型態。以下列的「cmdev.settable」表格來說：
| 欄位名稱 | 型態 |
|---|---|
| workingday | SET(‘MON’,'TUE’,'WED’,'THU’,'FRI’,'SAT’,'SUN’) |
你可以使用一個字串值來管理集合型態欄位，在這個字串值中，使用逗號來隔開不同的成員字串：
集合型態欄位與列舉型態欄位同樣具有檢查資料是否正確的能力：
列舉型態欄位的成員編號使用簡單的連續數字；集合型態欄位會比較複雜一些：
瞭解集合型態欄位的成員所代表的數字後，你就可以使用數值來管理儲存的資料：
要使用數值來代表多個成員的時候，你只要把所有成員的數字加總起來就可以了：
列舉與集合型態都可以設定需要的字元集與collation，以下列的「cmdev.estable」表格來說：
| 欄位名稱 | 型態 | 字元集 | Collation |
|---|---|---|---|
| enumsize | enum(‘XS’, …) | latin1 | latin1_general_ci |
| enumsize2 | enum(‘XS’, …) | latin1 | latin1_general_cs |
| workingday | set(‘MON’, …) | latin1 | latin1_general_ci |
| workingday2 | set(‘MON’, …) | latin1 | latin1_general_cs |
字元集的設定可以決定可以儲存字串資料的編碼，而collation的設定會決定字串值是否區分大小寫：
如果指定字串值的時候違反collation設定的大小寫規則，就會發生錯誤：
2.5 日期與時間
MySQL提供下列幾個可以儲存日期與時間資料的欄位型態：
| 型態 | Byte(s) | 說明 | 範圍 | |
|---|---|---|---|---|
| DATE | 3 | 日期 | ’1000-01-01′~’9999-12-31′ | |
| TIME | 3 | 時間 | ‘-838:59:59′~’838:59:59′ | |
| DATETIME | 8 | 日期與時間 | ’1000-01-01 00:00:00′~’9999-12-31 23:59:59′ | |
| YEAR[(4\ | 2)] | 1 | 西元年 | 1901~2155[YEAR(4)]1970~2069[YEAR(2)] |
| TIMESTAMP | 4 | 日期與時間 | ’1970-01-01 00:00:00′~2037 |
日期(DATE)型態欄位可以儲存年、月、日的資料，範圍從「1000-01-01」到「9999-12-31」，你的日期資料不可以超過「9999-12-31」，可是你可以儲存「1000-01-01」以前的日期，不過MySQL建議你最好不要這麼作，不然可能會造成一些奇怪的問題。
因為日期中的西元年份可以使用四個或兩個數字，使用兩個數字的時候，「70」到「99」表示「1970」到「1999」；如果是「00」到「69」就是「2000」到「2069」。所以要注意下列的情況：
另一個日期資料會變成這樣：
時間(TIME)型態可以儲存時、分、秒的資料，範圍從「-838:59:59」到「838:59:59」。這個儲存時間資料的範圍可能會跟你想的不太一樣。一般來說，時間資料指的是從「00:00:00」到「23:59:59」，也就是一天的時間。MySQL的時間型態欄位可以讓你儲存類似「經過的時間」這樣的資料：
在指定一個時間資料的時候，你可以省略秒或分，省略的部份，MySQL都會幫你設定為「0」：
日期與時間(DATETIME)型態可以儲存完整的年、月、日與時、分、秒資料，範圍從「1000-01-01 00:00:00」到「9999-12-31 23:59:59」。在表示一個日期與時間資料的時候，日期與時間之間，至少要使用一個空白隔開。時間部份的時、分、秒都可以省略，省略的部份，MySQL都會幫你設定為「0」：
如果只需要儲存年份資料的話，你可以使用西元年(YEAR)型態，這樣會節省很多儲存空間。你可以視需要把西元年型態設定為兩位或四位數字，四位數字可以儲存的範圍從「1901」到「2155」；兩位數字的範圍從「00」到「99」，實際的西元年份是「1970」到「2069」。
在指定一個年份資料給西元年型態欄位的時候，可以使用字串值或數值來表示西元年份，不同個數的資料會有不同的儲存效果：
如果西元年型態欄位的值是「0」的話，MySQL會把它當成是一個不正確的西元年資料，所以你應該不會指定這樣的資料，不過指定不同的資料也會有不同的儲存結果：
「TIMESTAMP」型態的格式與「DATETIME」一樣，都包含完整的年、月、日與時、分、秒資料，不過它使用的儲存空間只有4bytes，是「DATETIME」型態的一半。
「TIMESTAMP」也是MySQL日期與時間型態中具有「時區」特性的型態。它可以儲存從「1970-01-01 00:00:00」到目前經過的秒數。這個起始日期與時間使用「Coordinated Universal Time、UTC」世界標準時間為儲存資料的依據，它與「Greenwich Mean Time、GMT」格林威治標準時間是一樣的。
全世界分為許多不同時區(time zone)，所有時區都使用跟標準時間的差異來當作自己的標準時間。以台灣來說，你會在安裝Windows平台的電腦中，經由控制台裡的日期和時間，看到這個關於時區的設定：
MySQL資料庫採用與作業系統同樣的時區設定，所以在儲存「TIMESTAMP」型態欄位的資料時，過程中會有一些計算的動作：
而查詢「TIMESTAMP」型態欄位資料的時候，也會有這樣的情況：
瞭解時區設定與「TIMESTAMP」型態的關係後，你就可以知道下列的動作為什麼會發生錯誤了：
你可以使用查詢敘述取得MySQL資料庫伺服器關於時區的設定：
如果想要設定其它的時區，可以使用「+時時:分分」或「-時時:分分」的格式。例如日本東京時區比格林威治標準時間晚九小時，你可以設定為「+09:00」；而美國舊金山比格林威治標準時間早七小時，可以設定為「-07:00」：
設定新的時區以後，使用下列的範例測試「DATETIME」和「TIMESTAMP」兩種型態，可以看出在儲存日期時間資料上的差異：
因為「TIMESTAMP」型態儲存的是格林威治標準時間，所以在修改時區後，查詢得到的日期時間資料就會有差異了：