《MySQL 基礎篇》七：資料型別

Author: ACatSmiling

Since: 2024-09-20

MySQL 中的資料型別

資料	資料型別
整數型別	TINYINT、SMALLINT、MEDIUMINT、INT（或 INTEGER）、BIGINT
浮點型別	FLOAT、DOUBLE
定點數型別	DECIMAL
位型別	BIT
日期與時間型別	YEAR、TIME、DATE、DATETIME、TIMESTAMP
文字字串型別	CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
列舉型別	ENUM
集合型別	SET
二進位制字串型別	BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB
JSON 型別	JSON 物件、JSON 陣列
空間資料型別	單值：GEOMETRY、POINT、LINESTRING、POLYGON； 集合：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION

其中，常用的幾類型別介紹如下：

資料型別	描述
INT	從 $$-2^{31}$$ 到 $$2^{31}-1$$ 的整型資料，儲存大小為 4 個位元組
CHAR(size)	定長字元資料，若未指定，預設為 1 個字元，最大長度 255
VARCHAR(size)	可變長字元資料，根據字串實際長度儲存，必須指定長度
FLOAT(M, D)	單精度，佔用 4 個位元組，M = 整數位 + 小數位，D = 小數位。D <= M <= 255，0 <= D <= 30，預設 M + D <= 6
DOUBLE(M, D)	雙精度，佔用 8 個位元組，D <= M <= 255，0 <= D <= 30，預設 M + D <= 15
DECIMAL(M, D)	高精度小數，佔用 M + 2 個位元組，D <= M <= 65，0 <= D <= 30，最大取值範圍與 DOUBLE 相同
DATE	日期型資料，格式 'YYYY-MM-DD'
BLOB	二進位制形式的長文字資料，最大可達 4G
TEXT	長文字資料，最大可達 4G

常見資料型別的屬性，如下：

MySQL 關鍵字	含義
NULL	資料列可包含 NULL 值
NOT NULL	資料列不允許包含 NULL 值
DEFAULT	預設值
PRIMARY KEY	主鍵
AUTO_INCREMENT	自動遞增，適用於整數型別
UNSIGNED	無符號
CHARACTER SET name	指定一個字符集

整數型別

型別介紹

整數型別一共有 5 種，包括 TINYINT、SMALLINT、MEDIUMINT、INT（INTEGER）和 BIGINT。

整數型別	位元組	有符號數取值範圍	無符號數取值範圍
TINYINT	1	-128~127	0~255
SMALLINT	2	-32768~32767	0~65535
MEDIUMINT	3	-8388608~8388607	0~16777215
INT（INTEGER）	4	-2147483648~2147483647	0~4294967295
BIGINT	8	-9223372036854775808~9223372036854775807	0~18446744073709551615

可選屬性

整數型別的可選屬性有三個：M，UNSIGNED 和 ZEROFILL。

M

整型資料型別可以在定義表結構時指定所需要的顯示寬度，如果不指定，則系統為每一種型別指定預設的寬度值。

M：表示顯示寬度，M 的取值範圍是 (0, 255)。例如，int(5) 表示當資料寬度小於 5 位的時候，在數字前面需要用字元填滿寬度。該項功能需要配合 ZEROFILL 使用，表示用 0 填滿寬度，否則指定顯示寬度無效。

如果設定了顯示寬度，那麼插入的資料寬度超過顯示寬度限制，會不會截斷或插入失敗？

答案：不會對插入的資料有任何影響，還是按照型別的實際寬度進行儲存，即顯示寬度與型別可以儲存的值範圍無關。從 MySQL 8.0.17 開始，整數資料型別不推薦使用顯示寬度屬性。

UNSIGNED

UNSIGNED：無符號型別（非負），所有的整數型別都有一個可選的屬性 UNSIGNED（無符號屬性），無符號整數型別的最小取值為 0。所以，如果需要在 MySQL 資料庫中儲存非負整數值時，可以將整數型別設定為無符號型別。

ZEROFILL

ZEROFILL：0 填充，如果指定了 ZEROFILL，表示不夠 M 位時，用 0 在左邊填充，如果超過 M 位，就按照實際位數儲存，只是無須再用字元 0 進行填充。如果某列是 ZEROFILL，那麼 MySQL 會自動為當前列新增 UNSIGNED 屬性。

適用場景

TINYINT：一般用於列舉資料，比如系統設定取值範圍很小且固定的場景。

SMALLINT：可以用於較小範圍的統計資料，比如統計工廠的固定資產庫存數量等。

MEDIUMINT：用於較大整數的計算，比如車站每日的客流量等。

INT（INTEGER）：取值範圍足夠大，一般情況下不用考慮超限問題，用得最多，比如商品編號。

BIGINT：只有當處理特別巨大的整數時才會用到，比如雙十一的交易量、大型入口網站點選量、證券公司衍生產品持倉等。

在評估用哪種整數型別的時候，需要考慮儲存空間和可靠性的平衡問題：一方面，用佔用位元組數少的整數型別可以節省儲存空間；另一方面，要是為了節省儲存空間， 使用的整數型別取值範圍太小，一旦遇到超出取值範圍的情況，就可能引起系統錯誤，影響可靠性。

舉個例子，商品編號採用的資料型別是 INT。原因就在於，客戶門店中流通的商品種類較多，而且，每天都有舊商品下架，新商品上架，這樣不斷迭代，日積月累。如果使用 SMALLINT 型別，雖然佔用位元組數比 INT 型別的整數少，但是卻不能保證資料不會超出範圍 65535。相反，使用 INT，就能確保有足夠大的取值範圍，不用擔心資料超出範圍影響可靠性的問題。

需要注意的是，在實際工作中，系統故障產生的成本遠遠超過增加幾個欄位儲存空間所產生的成本。因此，首先應確保資料不會超過取值範圍，在這個前提之下，再去考慮如何節省儲存空間。

浮點型別

型別介紹

浮點數和定點數型別的特點是可以處理小數，可以把整數看成小數的一個特例。因此，浮點數和定點數的使用場景，比整數大多了。 MySQL 支援的浮點數型別，分別是 FLOAT、DOUBLE、REAL。

• FLOAT 表示單精度浮點數。

• DOUBLE 表示雙精度浮點數。

• REAL預設就是 DOUBLE。如果把 SQL 模式設定為啟用 REAL_AS_FLOAT，那 麼，MySQL 就認為 REAL 是 FLOAT。如果要啟用 REAL_AS_FLOAT，可以透過以下 SQL 語句實現：

  mysql> SET sql_mode = “REAL_AS_FLOAT”;
  

• FLOAT 佔用位元組數少，取值範圍小；DOUBLE 佔用位元組數多，取值範圍也大。

• MySQL 儲存浮點數的格式為：符號（S）、尾數（M）和階碼（E）。因此，無論有沒有符號，MySQL 的浮點數都會儲存表示符號的部分。因此， 所謂的無符號數取值範圍，其實就是有符號數取值範圍大於等於零的部分。

  

資料精度說明

對於浮點型別，在 MySQL 中單精度值使用 4 個位元組，雙精度值使用 8 個位元組。

MySQL 允許使用非標準語法（其他資料庫未必支援，因此如果涉及到資料遷移，則最好不要這麼用）：FLOAT(M, D)或DOUBLE(M, D)。這裡，M 稱為精度，D 稱為標度。(M, D) 中 M = 整數位 + 小數位，D = 小數位，且 D <= M <= 255，0 <= D <= 30。

例如，定義為 FLOAT(5, 2) 的一個列可以顯示為 -999.99~999.99，如果超過這個範圍會報錯。FLOAT 和 DOUBLE 型別在不指定 (M, D) 時，預設會按照實際的精度（由實際的硬體和作業系統決定）來顯示。

浮點型別，也可以加 UNSIGNED，但是不會改變資料範圍。例如：FLOAT(3, 2) UNSIGNED 仍然只能表示 0~9.99 的範圍。

不管是否顯式設定了精度 (M, D)，MySQL 的處理方案如下：

• 如果儲存時，整數部分超出了範圍，MySQL 就會報錯，不允許存這樣的值。
• 如果儲存時，小數點部分若超出範圍，就分以下情況：
  • 若四捨五入後，整數部分沒有超出範圍，則只警告，能成功操作並四捨五入刪除多餘的小數位後儲存。例如在 FLOAT(5, 2) 列內插入 999.009，近似結果是 999.01。
  • 若四捨五入後，整數部分超出範圍，則 MySQL 報錯，並拒絕處理。例如 FLOAT(5, 2) 列內插入 999.995 和 -999.995 都會報錯。

從 MySQL 8.0.17 開始，FLOAT(M, D) 和 DOUBLE(M, D) 用法在官方文件中已經明確不推薦使用，將來可能被移除。另外，關於浮點型 FLOAT 和 DOUBLE 的 UNSIGNED 也不推薦使用了，將來也可能被移除。

精度誤差說明

浮點數型別有個缺陷，就是不精準。比如，設計一個表，有 f1 這個欄位，插入值分別為 0.47，0.44，0.19，期待的執行結果是：0.47 + 0.44 + 0.19 = 1.1，實際使用 SUM 之後查詢：

mysql> CREATE TABLE test_double2(
    -> 		f1 DOUBLE
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO test_double2 VALUES(0.47), (0.44), (0.19);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT SUM(f1) FROM test_double2;
+--------------------+
| SUM(f1)            |
+--------------------+
| 1.0999999999999999 |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT SUM(f1) = 1.1, 1.1 = 1.1 FROM test_double2;
+---------------+-----------+
| SUM(f1) = 1.1 | 1.1 = 1.1 |
+---------------+-----------+
|             0 |         1 |
+---------------+-----------+
1 row in set (0.00 sec)

可以看到，查詢結果是 1.0999999999999999。雖然誤差很小，但確實有誤差。 也可以嘗試把資料型別改成 FLOAT，然後執行求和查詢，得到的是 1.0999999940395355，顯然，誤差更大了。那麼，為什麼會存在這樣的誤差呢？問題還是出在 MySQL 對浮點型別資料的儲存方式上。

MySQL 用 4 個位元組儲存 FLOAT 型別資料，用 8 個位元組來儲存 DOUBLE 型別資料。無論哪個，都是採用二進位制的方式來進行儲存的。比如 9.625，用二進位制來表達，就是 1001.101，或者表達成 1.001101×$$2^3$$。如果尾數不是 0 或 5（比如 9.624），就無法用一個二進位制數來精確表達。進而，就只好在取值允許的範圍內進行四捨五入。

在程式設計中，如果用到浮點數，要特別注意誤差問題，因為浮點數是不準確的，所以要避免使用 = 來判斷兩個數是否相等。同時，在一些對精確度要求較高的專案中，千萬不要使用浮點數，不然會導致結果錯誤，甚至是造成不可挽回的損失。那麼，MySQL 有沒有精準的資料型別呢？當然有，這就是定點數型別 DECIMAL。

定點數型別

型別介紹

MySQL 中的定點數型別只有 DECIMAL 一種型別：

資料型別	位元組數	含義
DECIMAL(M, D)，DEC，NUMERIC	M + 2 位元組	有效範圍由 M 和 D 決定

• 使用 DECIMAL(M, D) 的方式表示高精度小數。其中，M 被稱為精度，D 被稱為標度。0 <= M <= 65，0 <= D <= 30，D < M。例如，定義 DECIMAL(5, 2) 的型別，表示該列取值範圍是 -999.99~999.99。
• DECIMAL(M, D) 的最大取值範圍與 DOUBLE 型別一樣，但是有效的資料範圍是由 M 和 D 決定的。DECIMAL 的儲存空間並不是固定的，由精度值 M 決定，總共佔用的儲存空間為 M + 2個位元組。也就是說，在一些對精度要求不高的場景下，比起佔用同樣位元組長度的定點數，浮點數表達的數值範圍可以更大一些。
• 定點數在 MySQL 內部是以字串的形式進行儲存，這就決定了它一定是精準的。
• 當 DECIMAL 型別不指定精度和標度時，其預設為 DECIMAL(10, 0)。當資料的精度超出了定點數型別的精度範圍時，則 MySQL 同樣會進行四捨五入處理。
• 浮點數 vs 定點數：
  • 浮點數相對於定點數的優點是，在長度一定的情況下，浮點型別取值範圍大，但是不精準，適用於需要取值範圍大，又可以容忍微小誤差的科學計算場景（比如計算化學、分子建模、流體動力學等）。
  • 定點數型別取值範圍相對小，但是精準，沒有誤差，適合於對精度要求極高的場景 （比如涉及金額計算的場景）。

位型別

BIT 型別中儲存的是二進位制值，類似 010110。

二進位制字串型別	長度	長度範圍	佔用空間
BIT(M)	M	1 <= M <= 64	約為 (M + 7) / 8 個位元組

BIT 型別，(M) 表示二進位制的位數，位數最小值為 1，最大值為 64。如果沒有指定 (M)，預設是 1 位，這個 1 位，表示只能存 1 位的二進位制值。

示例：

mysql> CREATE TABLE test_bit1(
    -> f1 BIT,
    -> f2 BIT(5),
    -> f3 BIT(64)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO test_bit1(f1) VALUES(1);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO test_bit1(f1) VALUES(2);
ERROR 1406 (22001): Data too long for column 'f1' at row 1
mysql> INSERT INTO test_bit1(f2) VALUES(23);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM test_bit1;
+------------+------------+------------+
| f1         | f2         | f3         |
+------------+------------+------------+
| 0x01       | NULL       | NULL       |
| NULL       | 0x17       | NULL       |
+------------+------------+------------+
2 rows in set (0.01 sec)

# 使用 SELECT 命令查詢位欄位時，可以用 BIN() 或 HEX() 函式進行讀取
mysql> SELECT BIN(f2), HEX(f2) FROM test_bit1;
+---------+---------+
| BIN(f2) | HEX(f2) |
+---------+---------+
| NULL    | NULL    |
| 10111   | 17      |
+---------+---------+
2 rows in set (0.00 sec)

# 使用 b+0 查詢資料時, 可以直接查詢出儲存的十進位制資料的值
mysql> SELECT f2 + 0 FROM test_bit1;
+--------+
| f2 + 0 |
+--------+
|   NULL |
|     23 |
+--------+
2 rows in set (0.00 sec)

日期與時間型別

MySQL 有多種表示日期和時間的資料型別，不同的版本可能有所差異，MySQL 8.0 版本支援的日期和時間型別主要有：YEAR 型別、TIME 型別、DATE 型別、DATETIME 型別和 TIMESTAMP 型別。

• YEAR 型別通常用來表示年。
• DATE 型別通常用來表示年、月、日。
• TIME 型別通常用來表示時、分、秒。
• DATETIME 型別通常用來表示年、月、日、時、分、秒。
• TIMESTAMP 型別通常用來表示帶時區的年、月、日、時、分、秒。

型別	名稱	位元組	日期格式	最小值	最大值
YEAR	年	1	YYYY 或 YY	1901	2155
TIME	時間	3	HH:MM:SS	-838:59:59	838:59:59
DATE	日期	3	YYYY-MM-DD	1000-01-01	9999-12-03
DATETIME	日期時間	8	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00	9999-12-03 23:59:59
TIMESTAMP	日期時間	4	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:00 UTC	2038-01-19 03:14:07UTC

可以看到，不同資料型別表示的時間內容不同、取值範圍不同，而且佔用的位元組數也不一樣，應根據實際需要靈活選取。

為什麼時間型別 TIME 的取值範圍不是 -23:59:59～23:59:59 呢？原因是 MySQL 設計的 TIME 型別，不僅可以表示一天之內的時間，而且可以用來表示一個時間間隔，這個時間間隔可以超過 24 小時。

YEAR 型別

YEAR 型別用來表示年份，在所有的日期時間型別中所佔用的儲存空間最小，只需要 1 個位元組的儲存空間。

在 MySQL 中，YEAR 有以下幾種儲存格式：

• 以 4 位字串或數字格式表示 YEAR 型別，其格式為 YYYY，最小值為 1901，最大值為 2155。
• 以 2 位字串格式表示 YEAR 型別，最小值為 00，最大值為 99。
  • 當取值為 01 到 69 時，表示 2001 到 2069；
  • 當取值為 70 到 99 時，表示 1970 到 1999；
  • 當取值整數的 0 或 00 新增的話，那麼是 0000 年；
  • 當取值是日期/字串的 '0' 新增的話，是 2000 年。

從 MySQL 5.5.27 開始，2 位格式的 YEAR 已經不推薦使用。YEAR 預設格式就是 YYYY，沒必要寫成 YEAR(4)，從 MySQL 8.0.19 開始，不推薦使用指定顯示寬度的 YEAR(4) 資料型別。

DATE 型別

DATE 型別表示日期，沒有時間部分，格式為 YYYY-MM-DD，其中，YYYY 表示年份，MM 表示月份，DD 表示日期。需要 3 個位元組的儲存空間。在向 DATE 型別的欄位插入資料時，同樣需要滿足一定的格式條件。

• 以 YYYY-MM-DD 格式或者 YYYYMMDD 格式表示的字串日期，其最小取值為 1000-01-01，最大取值為 9999-12-03。YYYYMMDD 格式會被轉化 為YYYY-MM-DD 格式。
• 以 YY-MM-DD 格式或者 YYMMDD 格式表示的字串日期，此格式中，年份為兩位數值或字串滿足 YEAR 型別的格式條件為：當年份取值為 00 到 69 時，會被轉化為 2000 到 2069；當年份取值為 70 到 99 時，會被轉化為 1970 到 1999。
• 使用 CURRENT_DATE() 或者 NOW() 函式，會插入當前系統的日期。

TIME 型別

TIME 型別用來表示時間，不包含日期部分。在 MySQL 中，需要 3 個位元組的儲存空間來儲存 TIME 型別的資料，可以使用 HH:MM:SS 格式來表示 TIME 型別，其中，HH 表示小時，MM 表示分鐘，SS 表示秒。

在 MySQL 中，向 TIME 型別的欄位插入資料時，也可以使用幾種不同的格式：

• 可以使用帶有冒號的字串，比如 'D HH:MM:SS'、'HH:MM:SS'、'HH:MM'、'D HH:MM'、'D HH' 或 'SS'格式，都能被正確地插入 TIME 型別的欄位中。其中 D 表示天，其最小值為 0，最大值為 34。如果使用帶有 D 格式的字串插入 TIME 型別的欄位時，D 會被轉化為小時，計算格式為 D * 24 + HH。當使用帶有冒號並且不帶 D 的字串表示時間時，表示當天的時間，比如 12:10 表示 12:10:00，而不是 00:12:10。
• 可以使用不帶有冒號的字串或者數字，格式為 'HHMMSS' 或者 HHMMSS。如果插入一個不合法的字串或者數字，MySQL 在儲存資料時，會將其自動轉化為 00:00:00 進行儲存。比如 1210，MySQL 會將最右邊的兩位解析成秒，表示 00:12:10，而不是 12:10:00。
• 使用 CURRENT_TIME() 或者 NOW() ，會插入當前系統的時間。

DATETIME 型別

DATETIME 型別在所有的日期時間型別中佔用的儲存空間最大，總共需要 8 個位元組的儲存空間。在格式上為 DATE 型別和 TIME 型別的組合，可以表示為 YYYY-MM-DD HH:MM:SS，其中 YYYY 表示年份，MM 表示月份，DD 表示日期，HH 表示小時，MM 表示分鐘，SS 表示秒。

在向DATETIME型別的欄位插入資料時，同樣需要滿足一定的格式條件：

• 以 YYYY-MM-DD HH:MM:SS 格式或者 YYYYMMDDHHMMSS 格式的字串插入 DATETIME 型別的欄位時，最小值為 1000-01-01 00:00:00，最大值為 9999-12-03 23:59:59。
  • 以 YYYYMMDDHHMMSS 格式的數字插入 DATETIME 型別的欄位時，會被轉化為 YYYY-MM-DD HH:MM:SS 格式。
• 以 YY-MM-DD HH:MM:SS 格式或者 YYMMDDHHMMSS 格式的字串插入 DATETIME 型別的欄位時，兩位數的年份規則符合 YEAR 型別的規則，00 到 69 表示 2000 到 2069；70 到 99 表示 1970 到 1999。
• 使用函式 CURRENT_TIMESTAMP() 和 NOW() ，可以向 DATETIME 型別的欄位插入系統的當前日期和時間。

TIMESTAMP 型別

TIMESTAMP 型別也可以表示日期時間，其顯示格式與 DATETIME 型別相同，都是 YYYY-MM-DDHH:MM:SS ，需要 4 個位元組的儲存空間。但是 TIMESTAMP 儲存的時間範圍比 DATETIME 要小很多，只能儲存 1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC 之間的時間。其中，UTC 表示世界統一時間，也叫作世界標準時間。

儲存資料的時候需要對當前時間所在的時區進行轉換，查詢資料的時候再將時間轉換回當前的時區。因此，使用 TIMESTAMP 儲存的同一個時間值，在不同的時區查詢時會顯示不同的時間。

向 TIMESTAMP 型別的欄位插入資料時，當插入的資料格式滿足 YY-MM-DD HH:MM:SS 和 YYMMDDHHMMSS 時，兩位數值的年份同樣符合 YEAR 型別的規則條件，只不過表示的時間範圍要小很多。

如果向 TIMESTAMP 型別的欄位插入的時間超出了 TIMESTAMP 型別的範圍，則 MySQL 會丟擲錯誤資訊。

TIMESTAMP 和 DATETIME 的區別

• TIMESTAMP 儲存空間比較小，表示的日期時間範圍也比較小。
• 底層儲存方式不同，TIMESTAMP 底層儲存的是毫秒值，距離 1970-1-1 0:0:0 0 毫秒的毫秒值。
• 兩個日期比較大小或日期計算時，TIMESTAMP 更方便、更快。
• TIMESTAMP 和時區有關。TIMESTAMP 會根據使用者的時區不同，顯示不同的結果。而 DATETIME 則只能反映出插入時當地的時區，其他時區的人檢視資料必然會有誤差的。

示例：

mysql> CREATE TABLE temp_time(
    -> 		d1 DATETIME,
    -> 		d2 TIMESTAMP
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO temp_time VALUES(NOW(), NOW());
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM temp_time;
+---------------------+---------------------+
| d1                  | d2                  |
+---------------------+---------------------+
| 2023-05-05 14:45:52 | 2023-05-05 14:45:52 |
| 2023-05-05 12:57:45 | 2023-05-05 12:57:45 |
+---------------------+---------------------+
2 rows in set (0.00 sec)

# 修改當前的時區
mysql> SET time_zone = '+9:00';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM temp_time;
+---------------------+---------------------+
| d1                  | d2                  |
+---------------------+---------------------+
| 2023-05-05 14:45:52 | 2023-05-05 15:45:52 |
| 2023-05-05 12:57:45 | 2023-05-05 13:57:45 |
+---------------------+---------------------+
2 rows in set (0.00 sec)

實際開發中，用得最多的日期時間型別，就是 DATETIME。一般存註冊時間、商品釋出時間等，不建議使用 DATETIME 儲存，而是使用時間戳，因為 DATETIME 雖然直觀，但不便於計算。

mysql>  SELECT UNIX_TIMESTAMP();
+------------------+
| UNIX_TIMESTAMP() |
+------------------+
|       1683264546 |
+------------------+
1 row in set (0.00 sec)

文字字串型別

MySQL 中，文字字串總體上分為 CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、ENUM、SET 等型別。

CHAR 和 VARCHAR 型別

CHAR 型別：

• CHAR(M) 型別一般需要預先定義字串長度。如果不指定 (M)，則表示長度預設是 1 個字元。
• 如果儲存時，資料的實際長度比 CHAR 型別宣告的長度小，則會在右側填充空格以達到指定的長度。MySQL 檢索 CHAR 型別的欄位資料時，會去除尾部的空格。
• 定義 CHAR 型別欄位時，宣告的欄位長度即為 CHAR 型別欄位所佔的儲存空間的位元組數。

VARCHAR 型別：

• VARCHAR(M) 定義時，必須指定長度 M，否則報錯。
• MySQL 4.0 版本以下，varchar(20)：指的是 20 位元組，如果存放 UTF8 漢字時，只能存 6 個（每個漢字 3 位元組）；MySQL 5.0 版本以上，varchar(20)：指的是 20 字元。
• MySQL 檢索 VARCHAR 型別的欄位資料時，會保留資料尾部的空格。
• VARCHAR 型別的欄位所佔用的儲存空間，為字串實際長度加 1 個位元組。

CHAR 和 VARCHAR 對比：

型別	特點	空間上	時間上	適用場景
CHAR(M)	固定長度	浪費儲存空間	效率高	儲存不大，速度要求高
VARCHAR(M)	可變長度	節省儲存空間	效率低	非 CHAR 的情況

• 情況 1：儲存很短的資訊。比如門牌號碼 101，201 這樣很短的資訊應該用 CHAR，因為 VARCHAR 還要佔個 byte 用於儲存資訊長度，本來打算節約儲存的，結果得不償失。
• 情況 2：固定長度的。比如使用 uuid 作為主鍵，那用 CHAR 應該更合適。因為它固定長度，VARCHAR 動態根據長度的特性就消失了，而且還要佔個長度資訊。
• 情況 3：十分頻繁改變的 COLUMN。因為 VARCHAR 每次儲存都要有額外的計算，得到長度等工作，如果一個非常頻繁改變的，那就要有很多的精力用於計算，而這些對於 CHAR 來說是不需要的。
• 情況 4：具體儲存引擎中的情況：
  • MyISAM 資料儲存引擎和資料列：MyISAM 資料表，最好使用固定長度（CHAR）的資料列代替可變長度（VARCHAR）的資料列。這樣使得整個表靜態化，從而使資料檢索更快，用空間換時間。
  • MEMORY 儲存引擎和資料列：MEMORY 資料表目前都使用固定長度的資料行儲存，因此無論使用 CHAR 或 VARCHAR 列都沒有關係，兩者都是作為 CHAR 型別處理的。
  • InnoDB 儲存引擎，建議使用 VARCHAR 型別。因為對於 InnoDB 資料表，內部的行儲存格式並沒有區分固定長度和可變長度列（所有資料行都使用指向資料列值的頭指標），而且主要影響效能的因素是資料行使用的儲存總量，由於 char 平均佔用的空間多於 VARCHAR，所以除了簡短並且固定長度的，其他考慮 VARCHAR。這樣節省空間，對磁碟 I/O 和資料儲存總量比較好。

TEXT 型別

在 MySQL 中，TEXT 用來儲存文字型別的字串，總共包含4種型別，分別為TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT 型別。

在向 TEXT 型別的欄位儲存和查詢資料時，系統自動按照實際長度儲存，不需要預先定義長度，這一點和 VARCHAR 型別相同。

每種 TEXT 型別儲存的資料長度和所佔用的儲存空間不同，如下：

文字字串型別	特點	長度	長度範圍	佔用的儲存空間
TINYTEXT	小文字、可變長度	L	0 <= L <= 255	L + 2 個位元組
TEXT	文字、可變長度	L	0 <= L <= 65535	L + 2 個位元組
MEDIUMTEXT	中等文字、可變長度	L	0 <= L <= 16777215	L + 3 個位元組
LONGTEXT	大文字、可變長度	L	0 <= L <= 4294967295（相當於 4 GB）	L + 4 個位元組

• 由於實際儲存的長度不確定，MySQL 不允許 TEXT 型別的欄位做主鍵。遇到這種情況，只能採用 CHAR(M)，或者 VARCHAR(M)。
• TEXT 文字型別，可以存比較大的文字段，搜尋速度稍慢，因此如果不是特別大的內容，建議使用 CHAR，VARCHAR 來代替。
• TEXT 型別不用加預設值，加了也沒用。
• TEXT 和 BLOB 型別的資料，刪除後容易導致 "空洞"，使得檔案碎片比較多，所以頻繁使用的表不建議包含 TEXT 型別欄位，建議單獨分出去，單獨用一個表。

列舉型別

ENUM 型別也叫作列舉型別，ENUM 型別的取值範圍需要在定義欄位時進行指定。設定欄位值時，ENUM 型別只允許從成員中選取單個值，不能一次選取多個值。

ENUM 型別所需要的儲存空間，由定義 ENUM 型別時指定的成員個數決定。

型別	長度	長度範圍	佔用的儲存空間
TINYTEXT	L	1 <= L <= 65535	1 或 2 個位元組

• 當 ENUM 型別包含 1～255 個成員時，需要 1 個位元組的儲存空間；
• 當 ENUM 型別包含 256～65535 個成員時，需要 2 個位元組的儲存空間。
• ENUM 型別的成員個數的上限為 65535 個。

示例：

mysql> CREATE TABLE test_enum(
    -> 		season ENUM('spring', 'summer', 'autumn', 'winter', 'unknow')
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO test_enum VALUES('spring'), ('winter');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

# 忽略大小寫
mysql> INSERT INTO test_enum VALUES('UNKNOW');
Query OK, 1 row affected (0.00 sec)

# 允許按照角標的方式獲取指定索引位置的列舉值
mysql> INSERT INTO test_enum VALUES('2'), (3);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO test_enum VALUES('rain');
ERROR 1265 (01000): Data truncated for column 'season' at row 1

# 當 ENUM 型別的欄位沒有宣告為 NOT NULL 時，插入 NULL 也是有效的
mysql> INSERT INTO test_enum VALUES(NULL);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM test_enum;
+--------+
| season |
+--------+
| spring |
| winter |
| unknow |
| summer |
| autumn |
| NULL   |
+--------+
6 rows in set (0.00 sec)

集合型別

SET 表示一個字串物件，可以包含 0 個或多個成員，但成員個數的上限為 64 。設定欄位值時，可以取取值範圍內的 0 個或多個值。

當 SET 型別包含的成員個數不同時，其所佔用的儲存空間也是不同的，具體如下：

成員個數範圍（L 表示實際成員個數）	佔用的儲存空間
1 <= L <= 8	1 個位元組
9 <= L <= 16	2 個位元組
17 <= L <= 24	3 個位元組
25 <= L <= 32	4 個位元組
33 <= L <= 64	8 個位元組

• SET 型別在儲存資料時成員個數越多，其佔用的儲存空間越大。
• SET 型別在選取成員時，可以一次選擇多個成員，這一點與ENUM型別不同。

示例：

mysql> CREATE TABLE test_set(
    -> 		s SET ('A', 'B', 'C')
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO test_set (s) VALUES ('A'), ('A,B');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

# 逗號分隔，不能有空格
mysql> INSERT INTO test_set (s) VALUES ('A'), ('A, B');
ERROR 1265 (01000): Data truncated for column 's' at row 2

# 插入重複的 SET 型別成員時，MySQL 會自動刪除重複的成員
mysql> INSERT INTO test_set (s) VALUES ('A,B,C,A');
Query OK, 1 row affected (0.01 sec)

# 向 SET 型別的欄位插入 SET 成員中不存在的值時，MySQL 會丟擲錯誤。
mysql> INSERT INTO test_set (s) VALUES ('A,B,C,D');
ERROR 1265 (01000): Data truncated for column 's' at row 1

mysql> SELECT * FROM test_set;
+-------+
| s     |
+-------+
| A     |
| A,B   |
| A,B,C |
+-------+
3 rows in set (0.00 sec)

二進位制字串型別

MySQL 中的二進位制字串型別主要儲存一些二進位制資料，比如可以儲存圖片、音訊和影片等二進位制資料。

MySQL 中支援的二進位制字串型別主要包括 BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB 型別。

BINARY 和 VARBINARY 型別

BINARY 和 VARBINARY 類似於 CHAR 和 VARCHAR，只是它們儲存的是二進位制字串。

BINARY(M) 為固定長度的二進位制字串，M 表示最多能儲存的位元組數，取值範圍是 0~255 個字元。如果未指定 (M)，表示只能儲存 1 個位元組。例如 BINARY(8)，表示最多能儲存 8 個位元組，如果欄位值不足 (M) 個位元組，將在右邊填充 '\0' 以補齊指定長度。

VARBINARY(M) 為可變長度的二進位制字串，M 表示最多能儲存的位元組數，總位元組數不能超過行的位元組長度限制 65535，另外還要考慮額外位元組開銷，VARBINARY 型別的資料除了儲存資料本身外，還需要 1 或 2 個位元組來儲存資料的位元組數。VARBINARY 型別必須指定(M)，否則報錯。

二進位制字串型別	特點	值的長度	佔用空間
BINARY(M)	固定長度	M （0 <= M <= 255）	M 個位元組
VARBINARY(M)	可變長度	M （0 <= M <= 65535）	M + 1 個位元組

BLOB 型別

BLOB 是一個二進位制大物件，可以容納可變數量的資料。

MySQL 中的 BLOB 型別包括 TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB 四種型別，它們可容納值的最大長度不同。可以儲存一個二進位制的大物件，比如圖片、音訊和影片等。

需要注意的是，在實際工作中，往往不會在 MySQL 資料庫中使用 BLOB 型別儲存大物件資料。通常會將圖片、音訊和影片檔案儲存到伺服器的磁碟上，並將圖片、音訊和影片的訪問路徑儲存到 MySQL 中。

二進位制字串型別	值的長度	長度範圍	佔用空間
TINYBLOB	L	0 <= M <= 255	L + 1 個位元組
BLOB	L	0 <= M <= 65535（相當於 64 KB）	L + 2 個位元組
MEDIUMBLOB	L	0 <= M <= 16777215（相當於 16 MB）	L + 3 個位元組
LONGBLOB	L	0 <= M <= 4294967295（相當於 4 GB）	L + 4 個位元組

TEXT 和 BLOB 的使用注意事項

在使用 TEXT 和 BLOB 欄位型別時要注意以下幾點，以便更好的發揮資料庫的效能：

• BLOB 和 TEXT 值也會引起自己的一些問題，特別是執行了大量的刪除或更新操作的時候。刪除這種值會在資料表中留下很大的 "空洞"，以後填入這些 "空洞" 的記錄可能長度不同。為了提高效能，建議定期使用 OPTIMIZE TABLE 功能對這類表進行碎片整理。
• 如果需要對大文字欄位進行模糊查詢，MySQL 提供了字首索引。但是仍然要在不必要的時候避免檢索大型的 BLOB 或 TEXT 值。例如，SELECT * 查詢就不是很好的想法，除非你能夠確定作為約束條件的 WHERE 子句只會找到所需要的資料行。否則，你可能毫無目的地在網路上傳輸大量的值。
• 把 BLOB 或 TEXT 列分離到單獨的表中。在某些環境中，如果把這些資料列移動到第二張資料表中，可以讓你把原資料表中的資料列轉換為固定長度的資料行格式，那麼它就是有意義的。這會減少主表中的碎片，使你得到固定長度資料行的效能優勢。它還使你在主資料表上執行 SELECT * 查詢的時候不會透過網路傳輸大量的 BLOB 或 TEXT 值。

JSON 型別

JSON（JavaScript Object Notation）是一種輕量級的資料交換格式。簡潔和清晰的層次結構使得 JSON 成為理想的資料交換語言，它易於人閱讀和編寫，同時也易於機器解析和生成，並有效地提升網路傳輸效率。JSON 可以將 JavaScript 物件中表示的一組資料轉換為字串，然後就可以在網路或者程式之間輕鬆地傳遞這個字串，並在需要的時候將它還原為各程式語言所支援的資料格式。

在 MySQL 5.7 中，就已經支援 JSON 資料型別。在 MySQL 8.x 版本中，JSON 型別提供了可以進行自動驗證的 JSON 文件和最佳化的儲存結構，使得在 MySQL 中儲存和讀取 JSON 型別的資料更加方便和高效。

示例：

mysql> CREATE TABLE test_json(
    -> 		js json
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO test_json (js) VALUES ('{"name":"songhk", "age":18, "address":{"province":"beijing", "city":"beijing"}}');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test_json;
+--------------------------------------------------------------------------------------+
| js                                                                                   |
+--------------------------------------------------------------------------------------+
| {"age": 18, "name": "songhk", "address": {"city": "beijing", "province": "beijing"}} |
+--------------------------------------------------------------------------------------+
1 rows in set (0.00 sec)

當需要檢索 JSON 型別的欄位中資料的某個具體值時，可以使用 "->" 和 "->>" 符號。

mysql> SELECT js -> '$.name' AS NAME, js -> '$.age' AS age, js -> '$.address.province' AS province, js -> '$.address.city' AS city FROM test_json;
+----------+------+-----------+-----------+
| NAME     | age  | province  | city      |
+----------+------+-----------+-----------+
| "songhk" | 18   | "beijing" | "beijing" |
+----------+------+-----------+-----------+
1 rows in set (0.00 sec)

空間資料型別

MySQL 空間型別擴充套件支援地理特徵的生成、儲存和分析。這裡的地理特徵表示世界上具有位置的任何東西，可以是一個實體，例如一座山；可以是空間，例如一座辦公樓；也可以是一個可定義的位置，例如一個十字路口等等。MySQL 中使用 Geometry（幾何） 來表示所有地理特徵。Geometry 指一個點或點的集合，代表世界上任何具有位置的事物。

MySQL 的空間資料型別（Spatial Data Type）對應於 OpenGIS 類，包括單值型別：GEOMETRY、POINT、LINESTRING、POLYGON以及集合型別：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION 。

• Geometry 是所有空間集合型別的基類，其他型別如 POINT、LINESTRING、POLYGON 都是 Geometry 的子類。
  • Point：顧名思義就是點，有一個座標值。例如 POINT(121.213342 31.234532)，POINT(30 10)，座標值支援 DECIMAL 型別，經度（longitude）在前，維度（latitude）在後，用空格分隔。
  • LineString：線，由一系列點連線而成。如果線從頭至尾沒有交叉，那就是簡單的（simple）；如果起點和終點重疊，那就是封閉的（closed）。例如 LINESTRING(30 10,10 30,40 40)，點與點之間用逗號分隔，一個點中的經緯度用空格分隔，與 POINT 格式一致。
  • Polygon：多邊形。可以是一個實心平面形，即沒有內部邊界，也可以有空洞，類似紐扣。最簡單的就是隻有一個外邊界的情況，例如POLYGON((0 0,10 0,10 10, 0 10))。

下面展示幾種常見的幾何圖形元素：

• MultiPoint、MultiLineString、MultiPolygon、GeometryCollection 這 4 種型別都是集合類，是多個 Point、LineString 或 Polygon 組合而成。

下面展示的是多個同類或異類幾何圖形元素的組合：

小結

在定義資料型別時，如果確定是整數，就用 INT；如果是小數，一定用定點數型別 DECIMAL(M,D) ； 如果是日期與時間，就用 DATETIME。

這樣做的好處是，首先確保你的系統不會因為資料型別定義出錯。不過，凡事都是有兩面的，可靠性好，並不意味著高效。比如，TEXT 雖然使用方便，但是效率不如 CHAR(M) 和 VARCHAR(M)。

關於字串的選擇，建議參考如下阿里巴巴的《Java開發手冊》規範：

阿里巴巴《Java 開發手冊》之 MySQL 資料庫：

• 任何欄位如果為非負數，必須是 UNSIGNED。
• 【 強制 】小數型別為 DECIMAL，禁止使用 FLOAT 和 DOUBLE。
• 說明：在儲存的時候，FLOAT 和 DOUBLE 都存在精度損失的問題，很可能在比較值的時候，得到不正確的結果。如果儲存的資料範圍超過 DECIMAL 的範圍，建議將資料拆成整數和小數並分開儲存。
• 【 強制 】如果儲存的字串長度幾乎相等，使用 CHAR 定長字串型別。
• 【 強制 】VARCHAR 是可變長字串，不預先分配儲存空間，長度不要超過 5000。如果儲存長度大於此值，定義欄位型別為 TEXT，獨立出來一張表，用主鍵來對應，避免影響其它欄位索引效率。

原文連結

https://github.com/ACatSmiling/zero-to-zero/blob/main/RelationalDatabase/mysql.md