專欄作者新書出版：Android App程式開發剖析 第三版（適用Android 8 Oreo與Android Studio 3）

MySQL 超新手入門（7）字元集與資料庫 << 前情

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提供的整數型態：

整數型態的意思就是它們不能儲存小數，在建立表格的時候，如果需要一個可以儲存整數資料的欄位，你可以依照整數資料的大小需求，選擇一個夠用又不會太浪費空間的整數形態。以下列的「cmdev.integertable」表格來說：

整數型態的後面會在左右刮號中指定一個數字，以「SMALLING」型態來說：

當你在執行資料的新增或修改的時候，就要特別注意它們的可以儲存數字的範圍：

整數型態的欄位，就表示它們不可以儲存小數的數值：

數值型態還有下列幾種可以儲存小數資料的浮點數型態：

註1：FLOAT與DOUBLE的預設長度會因為不同的作業系統而有不一樣的長度
註2：依照指定的位數決定實際儲存的空間

「FLOAT」和「DOUBLE」型態的欄位可以用來儲存包含小數的數值，儲存空間分別是4和8個位元組，它們是一種佔用儲存空間比較小，執行運算比較快的型態。不過因為它們是使用「近似值」來儲存你的數值，所以如果你需要儲存完全精準的數值，就不能使用這兩種型態。

另外一種可以儲存小數數值的「DECIMAL」型態就可以用來儲存完全精準的數值，儲存在這個型態中的數值，不論是查詢或是運算，都不會有任何誤差，不過「DECIMAL」型態佔用的儲存空間就比「FLOAT」和「DOUBLE」型態大。「DECIMAL」型態在MySQL還有一個一樣的關鍵字是「NUMERIC」，這兩種型態完全一樣。

在MySQL中，「FLOAT」、「DOUBLE」和「DECIMAL」都可以依照自己的需要設定長度與位數：

在設定長度與小數位數的時候，要注意下列幾個規則：

• 不可以超過最大長度
• 小數位數不可以超過長度
• 長度與小數位數一樣的時候，表示只可以儲存小數，例如「0.123」

MySQL的數值型態，包含整數與浮點數都可以設定為「只能儲存正數」，以下列的「cmdev.numerictable」表格來說：

設定為只能儲存正數的欄位，就跟字面上的效果一樣，任何希望儲存負數的動作都會造成錯誤：

MySQL的數值型態都可以依照自己的需要設定長度，以下列的「cmdev.numerictable2」表格來說：

同樣為數值型態設定長度，在整數和浮點數會有不一樣的效果。如果你為整數型態的欄位設定長度的話，這個長度只是設定顯示的長度而已，並不會影響實際儲存的長度：

為浮點數型態設定長度與小數位數的時候，效果就跟整數型態不一樣了：

不過在整數位數的部份，就一定會依照設定來儲存，否則會造成錯誤：

MySQL的在數值型態的設定上，還有一個比較特別的設定，就是「ZEROFILL」，以下列的「cmdev.numerictable3」表格來說：

「ZEROFILL」的設定表示在查詢這些欄位的時候，回傳的資料會在左側根據長度的設定填滿「0」：

註：「ZEROFILL」一定要跟「UNSIGNED」一起使用，就算你只有為欄位設定「ZEROFILL」，MySQL也會自動加入「UNSIGNED」的設定。

整數型態的部份，在補0的處理上會不太一樣：

2.2 位元

「位元、BIT」型態其實也是用來儲存數值用的，不過它是以二進位的型式儲存資料，也就是只有0跟1兩種資料。MySQL的在數值型態的設定上，還有一個比較特別的設定，就是「ZEROFILL」，以下列的「cmdev.numerictable3」表格來說：

你可以直接儲存數字到位元型態的欄位；也可以指定一個使用二進位表示的值：

2.3 字串

MySQL把字串型態分為兩大類：「非二進位制、non-binary」與「二進位制、binary」。非二進位制就是儲存一般文字的字串，會有特定的字元集與collation；二進位制使用位元組儲存資料，不包含字元集與collation，所以大多用來儲存圖片或音樂這類資料。「非二進位制、non-binary」的字串型態有下列幾種：

固定長度與變動長度的兩種字串型態都可以儲存字串，差異在儲存的文字個數小於型態指定的長度時，變動長度實際儲存的空間會小一些，以下列的「cmdev.nonbinarytable」表格來說：

同樣把長度設定為10的「CHAR」與「VARCHAR」字串型態，它們在儲存字串資料的時候會不太一樣：

「非二進位制、non-binary」的字串都會包含特定的字元集與collation，所以可以用來儲存各種不同國家的文字。不同的字元集會佔用不同的儲存空間，以下列的「cmdev.nonbinarytable2」表格來說：

上列的表格中，三個欄位分別設定為「latin1」、「big5」與「utf8」字元集，你可以查詢MySQL資料庫支援的字元集特性，「MAXLEN」欄位是關於儲存空間的資訊：

使用在「LENGTH」函式來查詢儲存在這個表格中的字串資料，就可以很明顯的看出不同的字元集，在儲存字元時使用的儲存空間：

「LENGTH」函式會傳回字串資料實際的儲存長度(byte)；如果你要查詢字串的字元數量的話，就要使用「CHAR_LENGTH」函式：

字元集會影響字串的儲存空間，collation會影響字串排列順序。以下列的「cmdev.nonbinarytable3」表格來說：

上列表格中欄位的字元集都指定為「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)為單位來儲存字串資料，跟非二進位制的字串類似，它也提供許多應用在不同長度的型態：

「BINARY」與「VARBINARY」兩種型態的差異，與「CHAR」和「VARCHAR」的差異一樣。在一般的情況下，使用「VARBINARY」會比「BINARY」節省一點儲存空間。你也可以使用「二進位制、binary」型態儲存文字資料，只不過MySQL都是以位元組來儲存所有的資料，也就是0到255的數字：

所有「二進位制、binary」的字串型態都不可以指定字元集與collation，不過你可以使用它們來儲存任何語言的文字，也可以儲存類似音樂或圖片資料，因為MySQL都是一個一個byte的把資料儲存到資料庫中；所以在執行查詢時的排序和條件設定，都是以使用位元組為單位來判斷。

2.4 列舉與集合

列舉(ENUM)與集合(SET)是一種特殊的「非二進位制、non-binary」字串型態，所以它們也可以指定字元集與collation。下列是這兩種型態的說明：

列舉(enumeration)的資料在資料庫中的應用很常見，例如服裝的大小就會以S、M與L來表示小、中與大。你可以使用字串來儲存這類資料，不過這類的資料也很適合使用「ENUM」型態來儲存。以下列的「cmdev.enumtable」表格來說：

在儲存資料的時候，「ENUM」型態看起來似乎與「VARCHAR」完全一樣：

可是列舉型態在資料的正確性方面，就會比單純的字串型態好多了。例如下列錯誤示範：

列舉型態欄位除了可以直接使用字串值來新增與更新資料外，還可以使用數值資料的編號來代替，任何一個列舉型態中的成員，MySQL都會幫它們編一個號碼：

瞭解列舉型態中成員的編號以後，你可以選擇字串值或數值來管理列舉型態欄位儲存的資料：

雖然在查詢列舉型態欄位資料的時候，所得到的結果都是成員的字串值；不過真正儲存在資料庫中的資料卻是成員的編號，所以指定列舉型態欄位為排序欄位的時候，資料庫會使用編號來排序，而不是以成員的字串值：

在指定列舉型態欄位的查詢條件時，可以使用成員的字串值或編號：

集合(SET)型態同樣可以設定一組成員，不過它可以儲存多個成員資料。例如星期的成員總共有七個，而需要工作的星期就會有一個以上了，類似這樣的需求就應該使用集合型態。以下列的「cmdev.settable」表格來說：

你可以使用一個字串值來管理集合型態欄位，在這個字串值中，使用逗號來隔開不同的成員字串：

集合型態欄位與列舉型態欄位同樣具有檢查資料是否正確的能力：

列舉型態欄位的成員編號使用簡單的連續數字；集合型態欄位會比較複雜一些：

瞭解集合型態欄位的成員所代表的數字後，你就可以使用數值來管理儲存的資料：

要使用數值來代表多個成員的時候，你只要把所有成員的數字加總起來就可以了：

列舉與集合型態都可以設定需要的字元集與collation，以下列的「cmdev.estable」表格來說：

字元集的設定可以決定可以儲存字串資料的編碼，而collation的設定會決定字串值是否區分大小寫：

如果指定字串值的時候違反collation設定的大小寫規則，就會發生錯誤：

2.5 日期與時間

MySQL提供下列幾個可以儲存日期與時間資料的欄位型態：

日期(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」型態儲存的是格林威治標準時間，所以在修改時區後，查詢得到的日期時間資料就會有差異了：

後續 >> MySQL 超新手入門（9）表格與索引