MySQL如何選擇數(shù)據(jù)類型

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

　　MySQL 提供了大量的數(shù)據(jù)類型，為了優(yōu)化存儲(chǔ)和提高數(shù)據(jù)庫(kù)性能，在任何情況下都應(yīng)該使用最精確的數(shù)據(jù)類型。

　　前面主要對(duì) MySQL 中的數(shù)據(jù)類型及其基本特性進(jìn)行了描述，包括它們能夠存放的值的類型和占用空間等。本節(jié)主要討論創(chuàng)建數(shù)據(jù)庫(kù)表時(shí)如何選擇數(shù)據(jù)類型。

　　可以說(shuō)字符串類型是通用的數(shù)據(jù)類型，任何內(nèi)容都可以保存在字符串中，數(shù)字和日期都可以表示成字符串形式。

　　但是也不能把所有的列都定義為字符串類型。對(duì)于數(shù)值類型，如果把它們?cè)O(shè)置為字符串類型的，會(huì)使用很多的空間。并且在這種情況下使用數(shù)值類型列來(lái)存儲(chǔ)數(shù)字，比使用字符串類型更有效率。

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

　　例如，數(shù)字 2 小于數(shù)字 11，但字符串 '2' 卻比字符串 '11' 大。此問(wèn)題可以通過(guò)把列放到數(shù)字上下文中來(lái)解決，如下面 SQL 語(yǔ)句：

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

　　讓 course 列加上 0，可以強(qiáng)制列按數(shù)字的方式來(lái)排序，但這么做很明顯是不合理的。

　　如果讓 MySQL 把一個(gè)字符串列當(dāng)作一個(gè)數(shù)字列來(lái)對(duì)待，會(huì)引發(fā)很嚴(yán)重的問(wèn)題。這樣做會(huì)迫使讓列里的每一個(gè)值都執(zhí)行從字符串到數(shù)字的轉(zhuǎn)換，操作效率低。而且在計(jì)算過(guò)程中使用這樣的列，會(huì)導(dǎo)致 MySQL 不會(huì)使用這些列上的任何索引，從而進(jìn)一步降低查詢的速度。

　　所以我們?cè)谶x擇數(shù)據(jù)類型時(shí)要考慮存儲(chǔ)、查詢和整體性能等方面的問(wèn)題。

　　在選擇數(shù)據(jù)類型時(shí)，首先要考慮這個(gè)列存放的值是什么類型的。一般來(lái)說(shuō)，用數(shù)值類型列存儲(chǔ)數(shù)字、用字符類型列存儲(chǔ)字符串、用時(shí)態(tài)類型列存儲(chǔ)日期和時(shí)間。

　　數(shù)值類型

　　對(duì)于數(shù)值類型列，如果要存儲(chǔ)的數(shù)字是整數(shù)(沒(méi)有小數(shù)部分)，則使用整數(shù)類型;如果要存儲(chǔ)的數(shù)字是小數(shù)(帶有小數(shù)部分)，則可以選用 DECIMAL 或浮點(diǎn)類型，但是一般選擇 FLOAT 類型(浮點(diǎn)類型的一種)。

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

　　MEDIUMINT 是整數(shù)類型，UNSIGNED 用來(lái)將數(shù)字類型無(wú)符號(hào)化。比如 INT 類型的取值范圍是 -2 147 483 648 ～ 2 147 483 647，那么 INT UNSIGNED 類型的取值范圍就是 0 ～ 4 294 967 295。

　　如果需要存儲(chǔ)某些整數(shù)值，則值的范圍決定了可選用的數(shù)據(jù)類型。如果取值范圍是 0~1000，那么可以選擇 SMALLINT~BIGINT 之間的任何一種類型。如果取值范圍超過(guò)了 200 萬(wàn)，則不能使用 SMALLINT，可以選擇的類型變?yōu)閺?MEDIUMINT 到 BIGINT 之間的某一種。

　　當(dāng)然，完全可以為要存儲(chǔ)的值選擇一種最“大”的數(shù)據(jù)類型。但是，如果正確選擇數(shù)據(jù)類型，不僅可以使表的存儲(chǔ)空間變小，也會(huì)提高性能。因?yàn)榕c較長(zhǎng)的列相比，較短的列的處理速度更快。當(dāng)讀取較短的值時(shí)，所需的磁盤讀寫操作會(huì)更少，并且可以把更多的鍵值放入內(nèi)存索引緩沖區(qū)里。

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

　　如果數(shù)值類型需要存儲(chǔ)的數(shù)據(jù)為貨幣，如人民幣。在計(jì)算時(shí)，使用到的值常帶有元和分兩個(gè)部分。它們看起來(lái)像是浮點(diǎn)值，但 FLOAT 和 DOUBLE 類型都存在四舍五入的誤差問(wèn)題，因此不太適合。因?yàn)槿藗儗?duì)自己的金錢都很敏感，所以需要一個(gè)可以提供完美精度的數(shù)據(jù)類型。

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

　　對(duì)于電話號(hào)碼、信用卡號(hào)和社會(huì)保險(xiǎn)號(hào)都會(huì)使用非數(shù)字字符。因?yàn)榭崭窈投虅澗€不能直接存儲(chǔ)到數(shù)字類型列里，除非去掉其中的非數(shù)字字符。但即使去掉了其中的非數(shù)字字符，也不能把它們存儲(chǔ)成數(shù)值類型，以避免丟失開(kāi)頭的“零”。

　　日期和時(shí)間類型

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

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

　　TIMESTAMP 也有一個(gè) DATETIME 不具備的屬性。默認(rèn)情況下，當(dāng)插入一條記錄但并沒(méi)有指定 TIMESTAMP 這個(gè)列值時(shí)，MySQL 會(huì)把 TIMESTAMP 列設(shè)為當(dāng)前的時(shí)間。因此當(dāng)需要插入記錄和當(dāng)前時(shí)間時(shí)，使用 TIMESTAMP 是方便的，另外 TIMESTAMP 在空間上比 DATETIME 更有效。

　　MySQL 沒(méi)有提供時(shí)間部分為可選的日期類型。DATE 沒(méi)有時(shí)間部分，DATETIME 必須有時(shí)間部分。如果時(shí)間部分是可選的，那么可以使用 DATE 列來(lái)記錄日期，再用一個(gè)單獨(dú)的 TIME 列來(lái)記錄時(shí)間。然后，設(shè)置 TIME 列可以為 NULL。SQL 語(yǔ)句如下：

　　CREATE TABLE mytb1 (

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

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

　　);

　　字符串類型

　　字符串類型沒(méi)有像數(shù)字類型列那樣的“取值范圍"，但它們都有長(zhǎng)度的概念。如果需要存儲(chǔ)的字符串短于 256 個(gè)字符，那么可以使用 CHAR、VARCHAR 或 TINYTEXT。如果需要存儲(chǔ)更長(zhǎng)一點(diǎn)的字符串，則可以選用 VARCHAR 或某種更長(zhǎng)的 TEXT 類型。

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

　　CHAR 和 VARCHAR 之間的特點(diǎn)和選擇

　　CHAR 和 VARCHAR 的區(qū)別如下:

　　CHAR 是固定長(zhǎng)度字符，VARCHAR 是可變長(zhǎng)度字符。

　　CHAR 會(huì)自動(dòng)刪除插入數(shù)據(jù)的尾部空格，VARCHAR 不會(huì)刪除尾部空格。

　　CHAR 是固定長(zhǎng)度，所以它的處理速度比 VARCHAR 的速度要快，但是它的缺點(diǎn)就是浪費(fèi)存儲(chǔ)空間。所以對(duì)存儲(chǔ)不大，但在速度上有要求的可以使用 CHAR 類型，反之可以使用 VARCHAR類型來(lái)實(shí)現(xiàn)。

　　存儲(chǔ)引擎對(duì)于選擇 CHAR 和 VARCHAR 的影響:

　　對(duì)于 MyISAM 存儲(chǔ)引擎，最好使用固定長(zhǎng)度的數(shù)據(jù)列代替可變長(zhǎng)度的數(shù)據(jù)列。這樣可以使整個(gè)表靜態(tài)化，從而使數(shù)據(jù)檢索更快，用空間換時(shí)間。

　　對(duì)于InnoDB存儲(chǔ)引擎，最好使用可變長(zhǎng)度的數(shù)據(jù)列，因?yàn)?InnoDB 數(shù)據(jù)表的存儲(chǔ)格式不分固定長(zhǎng)度和可變長(zhǎng)度，因此使用 CHAR 不一定比使用 VARCHAR 更好，但由于 VARCHAR 是按照實(shí)際的長(zhǎng)度存儲(chǔ)，比較節(jié)省空間，所以對(duì)磁盤 I/O 和數(shù)據(jù)存儲(chǔ)總量比較好。

　　ENUM 和 SET

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

　　SET 可取多值。它的合法取值列表最多允許有 64 個(gè)成員?？兆址彩且粋€(gè)合法的 SET值。在需要取多個(gè)值的時(shí)候，適合使用 SET 類型，比如，要存儲(chǔ)一個(gè)人興趣愛(ài)好，最好使用SET類型。

　　ENUM 和 SET 的值是以字符串形式出現(xiàn)的，但在內(nèi)部，MySQL 以數(shù)值的形式存儲(chǔ)它們。

　　二進(jìn)制類型

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

看完上述內(nèi)容，你們對(duì)MySQL如何選擇數(shù)據(jù)類型有進(jìn)一步的了解嗎？如果還想了解更多知識(shí)或者相關(guān)內(nèi)容，請(qǐng)關(guān)注億速云行業(yè)資訊頻道，感謝大家的支持。