MySQL如何選擇數據類型

今天就跟大家聊聊有關MySQL如何選擇數據類型，可能很多人都不太了解，為了讓大家更加了解，小編給大家總結了以下內容，希望大家根據這篇文章可以有所收獲。

　　MySQL 提供了大量的數據類型，為了優化存儲和提高數據庫性能，在任何情況下都應該使用最精確的數據類型。

　　前面主要對 MySQL 中的數據類型及其基本特性進行了描述，包括它們能夠存放的值的類型和占用空間等。本節主要討論創建數據庫表時如何選擇數據類型。

　　可以說字符串類型是通用的數據類型，任何內容都可以保存在字符串中，數字和日期都可以表示成字符串形式。

　　但是也不能把所有的列都定義為字符串類型。對于數值類型，如果把它們設置為字符串類型的，會使用很多的空間。并且在這種情況下使用數值類型列來存儲數字，比使用字符串類型更有效率。

　　另外需要注意的是，由于對數字和字符串的處理方式不同，查詢結果也會存在差異。例如，對數字的排序與對字符串的排序是不一樣的。

　　例如，數字 2 小于數字 11，但字符串 '2' 卻比字符串 '11' 大。此問題可以通過把列放到數字上下文中來解決，如下面 SQL 語句：

　　SELECT course+ 0 as num ... ORDER BY num;

　　讓 course 列加上 0，可以強制列按數字的方式來排序，但這么做很明顯是不合理的。

　　如果讓 MySQL 把一個字符串列當作一個數字列來對待，會引發很嚴重的問題。這樣做會迫使讓列里的每一個值都執行從字符串到數字的轉換，操作效率低。而且在計算過程中使用這樣的列，會導致 MySQL 不會使用這些列上的任何索引，從而進一步降低查詢的速度。

　　所以我們在選擇數據類型時要考慮存儲、查詢和整體性能等方面的問題。

　　在選擇數據類型時，首先要考慮這個列存放的值是什么類型的。一般來說，用數值類型列存儲數字、用字符類型列存儲字符串、用時態類型列存儲日期和時間。

　　數值類型

　　對于數值類型列，如果要存儲的數字是整數(沒有小數部分)，則使用整數類型;如果要存儲的數字是小數(帶有小數部分)，則可以選用 DECIMAL 或浮點類型，但是一般選擇 FLOAT 類型(浮點類型的一種)。

　　例如，如果列的取值范圍是 1~99999 之間的整數，則 MEDIUMINT UNSIGNED 類型是最好的選擇。

　　MEDIUMINT 是整數類型，UNSIGNED 用來將數字類型無符號化。比如 INT 類型的取值范圍是 -2 147 483 648 ～ 2 147 483 647，那么 INT UNSIGNED 類型的取值范圍就是 0 ～ 4 294 967 295。

　　如果需要存儲某些整數值，則值的范圍決定了可選用的數據類型。如果取值范圍是 0~1000，那么可以選擇 SMALLINT~BIGINT 之間的任何一種類型。如果取值范圍超過了 200 萬，則不能使用 SMALLINT，可以選擇的類型變為從 MEDIUMINT 到 BIGINT 之間的某一種。

　　當然，完全可以為要存儲的值選擇一種最“大”的數據類型。但是，如果正確選擇數據類型，不僅可以使表的存儲空間變小，也會提高性能。因為與較長的列相比，較短的列的處理速度更快。當讀取較短的值時，所需的磁盤讀寫操作會更少，并且可以把更多的鍵值放入內存索引緩沖區里。

　　如果無法獲知各種可能值的范圍，則只能靠猜測，或者使用 BIGINT 以滿足最壞情況的需要。如果猜測的類型偏小，那么也不是就無藥可救。將來，還可以使用 ALTER TABLE 讓該列變得更大些。

　　如果數值類型需要存儲的數據為貨幣，如人民幣。在計算時，使用到的值常帶有元和分兩個部分。它們看起來像是浮點值，但 FLOAT 和 DOUBLE 類型都存在四舍五入的誤差問題，因此不太適合。因為人們對自己的金錢都很敏感，所以需要一個可以提供完美精度的數據類型。

　　可以把貨幣表示成 DECIMAL(M,2) 類型，其中 M 為所需取值范圍的最大寬度。這種類型的數值可以精確到小數點后 2 位。DECIMAL 的優點在于不存在舍入誤差，計算是精確的。

　　對于電話號碼、信用卡號和社會保險號都會使用非數字字符。因為空格和短劃線不能直接存儲到數字類型列里，除非去掉其中的非數字字符。但即使去掉了其中的非數字字符，也不能把它們存儲成數值類型，以避免丟失開頭的“零”。

　　日期和時間類型

　　MySQL 對于不同種類的日期和時間都提供了數據類型，比如 YEAR 和 TIME。如果只需要記錄年份，則使用 YEAR 類型即可;如果只記錄時間，可以使用 TIME 類型。

　　如果同時需要記錄日期和時間，則可以使用 TIMESTAMP 或者 DATETIME 類型。由于TIMESTAMP 列的取值范圍小于 DATETIME 的取值范圍，因此存儲較大的日期最好使用 DATETIME。

　　TIMESTAMP 也有一個 DATETIME 不具備的屬性。默認情況下，當插入一條記錄但并沒有指定 TIMESTAMP 這個列值時，MySQL 會把 TIMESTAMP 列設為當前的時間。因此當需要插入記錄和當前時間時，使用 TIMESTAMP 是方便的，另外 TIMESTAMP 在空間上比 DATETIME 更有效。

　　MySQL 沒有提供時間部分為可選的日期類型。DATE 沒有時間部分，DATETIME 必須有時間部分。如果時間部分是可選的，那么可以使用 DATE 列來記錄日期，再用一個單獨的 TIME 列來記錄時間。然后，設置 TIME 列可以為 NULL。SQL 語句如下：

　　CREATE TABLE mytb1 (

　　date DATE NOT NULL, #日期是必需的

　　time TIME NULL #時間可選(可能為NULL)

　　);

　　字符串類型

　　字符串類型沒有像數字類型列那樣的“取值范圍"，但它們都有長度的概念。如果需要存儲的字符串短于 256 個字符，那么可以使用 CHAR、VARCHAR 或 TINYTEXT。如果需要存儲更長一點的字符串，則可以選用 VARCHAR 或某種更長的 TEXT 類型。

　　如果某個字符串列用于表示某種固定集合的值，那么可以考慮使用數據類型 ENUM 或 SET。

　　CHAR 和 VARCHAR 之間的特點和選擇

　　CHAR 和 VARCHAR 的區別如下:

　　CHAR 是固定長度字符，VARCHAR 是可變長度字符。

　　CHAR 會自動刪除插入數據的尾部空格，VARCHAR 不會刪除尾部空格。

　　CHAR 是固定長度，所以它的處理速度比 VARCHAR 的速度要快，但是它的缺點就是浪費存儲空間。所以對存儲不大，但在速度上有要求的可以使用 CHAR 類型，反之可以使用 VARCHAR類型來實現。

　　存儲引擎對于選擇 CHAR 和 VARCHAR 的影響:

　　對于 MyISAM 存儲引擎，最好使用固定長度的數據列代替可變長度的數據列。這樣可以使整個表靜態化，從而使數據檢索更快，用空間換時間。

　　對于InnoDB存儲引擎，最好使用可變長度的數據列，因為 InnoDB 數據表的存儲格式不分固定長度和可變長度，因此使用 CHAR 不一定比使用 VARCHAR 更好，但由于 VARCHAR 是按照實際的長度存儲，比較節省空間，所以對磁盤 I/O 和數據存儲總量比較好。

　　ENUM 和 SET

　　ENUM 只能取單值，它的數據列表是一個枚舉集合。它的合法取值列表最多允許有 65 535個成員。因此，在需要從多個值中選取一個時，可以使用 ENUM。比如，性別字段適合定義，為 ENUM 類型，每次只能從‘男’或‘女’中取一個值。

　　SET 可取多值。它的合法取值列表最多允許有 64 個成員。空字符串也是一個合法的 SET值。在需要取多個值的時候，適合使用 SET 類型，比如，要存儲一個人興趣愛好，最好使用SET類型。

　　ENUM 和 SET 的值是以字符串形式出現的，但在內部，MySQL 以數值的形式存儲它們。

　　二進制類型

　　BLOB 是二進制字符串，TEXT 是非二進制字符串，兩者均可存放大容量的信息。BLOB 主要存儲圖片、音頻信息等，而 TEXT 只能存儲純文本文件。

看完上述內容，你們對MySQL如何選擇數據類型有進一步的了解嗎？如果還想了解更多知識或者相關內容，請關注億速云行業資訊頻道，感謝大家的支持。