1. MySQL中的數(shù)據(jù)類(lèi)型

MySQL 中定義數(shù)據(jù)字段的類(lèi)型對(duì)你數(shù)據(jù)庫(kù)的優(yōu)化是非常重要的。

MySQL 支持多種類(lèi)型，大致可以分為三類(lèi)：數(shù)值、日期/時(shí)間和字符串(字符)類(lèi)型。

常見(jiàn)數(shù)據(jù)類(lèi)型的屬性，如下：

2.類(lèi)型介紹

整數(shù)類(lèi)型一共有 5 種，包括 TINYINT、SMALLINT、MEDIUMINT、INT（INTEGER）和 BIGINT。
它們的區(qū)別如下表所示：

2.2 可選屬性

整數(shù)類(lèi)型的可選屬性有三個(gè)：
2.2.1 M
M : 表示顯示寬度，M的取值范圍是(0, 255)。例如，int(5)：當(dāng)數(shù)據(jù)寬度小于5位的時(shí)候在數(shù)字前面需要用
字符填滿(mǎn)寬度。該項(xiàng)功能需要配合“ ZEROFILL ”使用，表示用“0”填滿(mǎn)寬度，否則指定顯示寬度無(wú)效。
如果設(shè)置了顯示寬度，那么插入的數(shù)據(jù)寬度超過(guò)顯示寬度限制，會(huì)不會(huì)截?cái)嗷虿迦胧?#xff1f;
答案：

不會(huì)對(duì)插入的數(shù)據(jù)有任何影響，還是按照類(lèi)型的實(shí)際寬度進(jìn)行保存，即 顯示寬度與類(lèi)型可以存儲(chǔ)的
值范圍無(wú)關(guān) 。從MySQL 8.0.17開(kāi)始，整數(shù)數(shù)據(jù)類(lèi)型不推薦使用顯示寬度屬性。

整型數(shù)據(jù)類(lèi)型可以在定義表結(jié)構(gòu)時(shí)指定所需要的顯示寬度，如果不指定，則系統(tǒng)為每一種類(lèi)型指定默認(rèn)
的寬度值。
舉例：

CREATE TABLE test_int1 ( x TINYINT, y SMALLINT, z MEDIUMINT, m INT, n BIGINT );

TINYINT有符號(hào)數(shù)和無(wú)符號(hào)數(shù)的取值范圍分別為-128_127和0255，由于負(fù)號(hào)占了一個(gè)數(shù)字位，因此
TINYINT默認(rèn)的顯示寬度為4。同理，其他整數(shù)類(lèi)型的默認(rèn)顯示寬度與其有符號(hào)數(shù)的最小值的寬度相同。
舉例：

CREATE TABLE test_int2(
f1 INT,
f2 INT(5),
f3 INT(5) ZEROFILL
)
DESC test_int2;
INSERT INTO test_int2(f1,f2,f3)
VALUES(1,123,123);
INSERT INTO test_int2(f1,f2)
VALUES(123456,123456);
INSERT INTO test_int2(f1,f2,f3)
VALUES(123456,123456,123456);

SELECT * FROM test_int2;

2.2.2 UNSIGNED

UNSIGNED : 無(wú)符號(hào)類(lèi)型（非負(fù)），所有的整數(shù)類(lèi)型都有一個(gè)可選的屬性UNSIGNED（無(wú)符號(hào)屬性），無(wú)
符號(hào)整數(shù)類(lèi)型的最小取值為0。所以，如果需要在MySQL數(shù)據(jù)庫(kù)中保存非負(fù)整數(shù)值時(shí)，可以將整數(shù)類(lèi)型設(shè)
置為無(wú)符號(hào)類(lèi)型。
int類(lèi)型默認(rèn)顯示寬度為int(11)，無(wú)符號(hào)int類(lèi)型默認(rèn)顯示寬度為int(10)。

CREATE TABLE test_int3(
f1 INT UNSIGNED
);
mysql> desc test_int3;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| f1 | int(10) unsigned | YES | | NULL | |
+-------+------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

2.2.3 ZEROFILL

ZEROFILL : 0填充,（如果某列是ZEROFILL，那么MySQL會(huì)自動(dòng)為當(dāng)前列添加UNSIGNED屬性），如果指
定了ZEROFILL只是表示不夠M位時(shí)，用0在左邊填充，如果超過(guò)M位，只要不超過(guò)數(shù)據(jù)存儲(chǔ)范圍即可。
原來(lái)，在 int(M) 中，M 的值跟 int(M) 所占多少存儲(chǔ)空間并無(wú)任何關(guān)系。 int(3)、int(4)、int(8) 在磁盤(pán)上都
是占用 4 bytes 的存儲(chǔ)空間。也就是說(shuō)，int(M)，必須和UNSIGNED ZEROFILL一起使用才有意義。如果整
數(shù)值超過(guò)M位，就按照實(shí)際位數(shù)存儲(chǔ)。只是無(wú)須再用字符 0 進(jìn)行填充。

2.3 適用場(chǎng)景

TINYINT ：一般用于枚舉數(shù)據(jù)，比如系統(tǒng)設(shè)定取值范圍很小且固定的場(chǎng)景。
SMALLINT ：可以用于較小范圍的統(tǒng)計(jì)數(shù)據(jù)，比如統(tǒng)計(jì)工廠(chǎng)的固定資產(chǎn)庫(kù)存數(shù)量等。
MEDIUMINT ：用于較大整數(shù)的計(jì)算，比如車(chē)站每日的客流量等。
INT、INTEGER ：取值范圍足夠大，一般情況下不用考慮超限問(wèn)題，用得最多。比如商品編號(hào)。
BIGINT ：只有當(dāng)你處理特別巨大的整數(shù)時(shí)才會(huì)用到。比如雙十一的交易量、大型門(mén)戶(hù)網(wǎng)站點(diǎn)擊量、證
券公司衍生產(chǎn)品持倉(cāng)等。

2.4 如何選擇？

在評(píng)估用哪種整數(shù)類(lèi)型的時(shí)候，你需要考慮 存儲(chǔ)空間 和 可靠性 的平衡問(wèn)題：一方 面，用占用字節(jié)數(shù)少
的整數(shù)類(lèi)型可以節(jié)省存儲(chǔ)空間；另一方面，要是為了節(jié)省存儲(chǔ)空間， 使用的整數(shù)類(lèi)型取值范圍太小，一
旦遇到超出取值范圍的情況，就可能引起 系統(tǒng)錯(cuò)誤 ，影響可靠性。
舉個(gè)例子，商品編號(hào)采用的數(shù)據(jù)類(lèi)型是 INT。原因就在于，客戶(hù)門(mén)店中流通的商品種類(lèi)較多，而且，每
天都有舊商品下架，新商品上架，這樣不斷迭代，日積月累。
如果使用 SMALLINT 類(lèi)型，雖然占用字節(jié)數(shù)比 INT 類(lèi)型的整數(shù)少，但是卻不能保證數(shù)據(jù)不會(huì)超出范圍
65535。相反，使用 INT，就能確保有足夠大的取值范圍，不用擔(dān)心數(shù)據(jù)超出范圍影響可靠性的問(wèn)題。
你要注意的是，在實(shí)際工作中，系統(tǒng)故障產(chǎn)生的成本遠(yuǎn)遠(yuǎn)超過(guò)增加幾個(gè)字段存儲(chǔ)空間所產(chǎn)生的成本。因
此，我建議你首先確保數(shù)據(jù)不會(huì)超過(guò)取值范圍，在這個(gè)前提之下，再去考慮如何節(jié)省存儲(chǔ)空間。

3. 浮點(diǎn)類(lèi)型

浮點(diǎn)數(shù)和定點(diǎn)數(shù)類(lèi)型的特點(diǎn)是可以 處理小數(shù) ，你可以把整數(shù)看成小數(shù)的一個(gè)特例。因此，浮點(diǎn)數(shù)和定點(diǎn)
數(shù)的使用場(chǎng)景，比整數(shù)大多了。 MySQL支持的浮點(diǎn)數(shù)類(lèi)型，分別是 FLOAT、DOUBLE、REAL。

• FLOAT 表示單精度浮點(diǎn)數(shù)；
• DOUBLE 表示雙精度浮點(diǎn)數(shù)；
  
  REAL默認(rèn)就是 DOUBLE。如果你把 SQL 模式設(shè)定為啟用“ REAL_AS_FLOAT ”，那 么，MySQL 就認(rèn)為
  REAL 是 FLOAT。如果要啟用“REAL_AS_FLOAT”，可以通過(guò)以下 SQL 語(yǔ)句實(shí)現(xiàn)：

SET sql_mode = “REAL_AS_FLOAT”;

問(wèn)題1：FLOAT 和 DOUBLE 這兩種數(shù)據(jù)類(lèi)型的區(qū)別是啥呢？
FLOAT 占用字節(jié)數(shù)少，取值范圍小；DOUBLE 占用字節(jié)數(shù)多，取值范圍也大。
問(wèn)題2：為什么浮點(diǎn)數(shù)類(lèi)型的無(wú)符號(hào)數(shù)取值范圍，只相當(dāng)于有符號(hào)數(shù)取值范圍的一半，也就是只相當(dāng)于
有符號(hào)數(shù)取值范圍大于等于零的部分呢？
MySQL 存儲(chǔ)浮點(diǎn)數(shù)的格式為： 符號(hào)(S) 、 尾數(shù)(M) 和 階碼(E) 。因此，無(wú)論有沒(méi)有符號(hào)，MySQL 的浮
點(diǎn)數(shù)都會(huì)存儲(chǔ)表示符號(hào)的部分。因此， 所謂的無(wú)符號(hào)數(shù)取值范圍，其實(shí)就是有符號(hào)數(shù)取值范圍大于等于
零的部分。

3.2 數(shù)據(jù)精度說(shuō)明

對(duì)于浮點(diǎn)類(lèi)型，在MySQL中單精度值使用 4 個(gè)字節(jié)，雙精度值使用 8 個(gè)字節(jié)。

• MySQL允許使用 非標(biāo)準(zhǔn)語(yǔ)法 （其他數(shù)據(jù)庫(kù)未必支持，因此如果涉及到數(shù)據(jù)遷移，則最好不要這么 用）： FLOAT(M,D) 或
  DOUBLE(M,D) 。這里，M稱(chēng)為 精度 ，D稱(chēng)為 標(biāo)度 。(M,D)中 M=整數(shù)位+小數(shù) 位，D=小數(shù)位。
  D<=M<=255，0<=D<=30。

例如，定義為FLOAT(5,2)的一個(gè)列可以顯示為-999.99-999.99。如果超過(guò)這個(gè)范圍會(huì)報(bào)錯(cuò)。

• FLOAT和DOUBLE類(lèi)型在不指定(M,D)時(shí)，默認(rèn)會(huì)按照實(shí)際的精度（由實(shí)際的硬件和操作系統(tǒng)決定） 來(lái)顯示。
• 說(shuō)明：浮點(diǎn)類(lèi)型，也可以加 UNSIGNED ，但是不會(huì)改變數(shù)據(jù)范圍，例如：FLOAT(3,2) UNSIGNED仍然
  只能表示0-9.99的范圍。
• 不管是否顯式設(shè)置了精度(M,D)，這里MySQL的處理方案如下：

1.如果存儲(chǔ)時(shí)，整數(shù)部分超出了范圍，MySQL就會(huì)報(bào)錯(cuò)，不允許存這樣的值
2.如果存儲(chǔ)時(shí)，小數(shù)點(diǎn)部分若超出范圍，就分以下情況：
3.若四舍五入后，整數(shù)部分沒(méi)有超出范圍，則只警告，但能成功操作并四舍五入刪除多余的小數(shù)位后保存。例如在FLOAT(5,2)列內(nèi)插入999.009，近似結(jié)果是999.01。
4.若四舍五入后，整數(shù)部分超出范圍，則MySQL報(bào)錯(cuò)，并拒絕處理。如FLOAT(5,2)列內(nèi)插入999.995和-999.995都會(huì)報(bào)錯(cuò)。
從MySQL 8.0.17開(kāi)始，FLOAT(M,D) 和DOUBLE(M,D)用法在官方文檔中已經(jīng)明確不推薦使用，將來(lái)可能被移除。另外，關(guān)于浮點(diǎn)型FLOAT和DOUBLE的UNSIGNED也不推薦使用了，將來(lái)也可能被移除。
舉例

CREATE TABLE test_double1(
f1 FLOAT,
f2 FLOAT(5,2),
f3 DOUBLE,
f4 DOUBLE(5,2)
);
DESC test_double1;
INSERT INTO test_double1
VALUES(123.456,123.456,123.4567,123.45);
#Out of range value for column 'f2' at row 1
INSERT INTO test_double1
VALUES(123.456,1234.456,123.4567,123.45);
SELECT * FROM test_double1;

3.3 精度誤差說(shuō)明

浮點(diǎn)數(shù)類(lèi)型有個(gè)缺陷，就是不精準(zhǔn)。下面我來(lái)重點(diǎn)解釋一下為什么 MySQL 的浮點(diǎn)數(shù)不夠精準(zhǔn)。比如，我
們?cè)O(shè)計(jì)一個(gè)表，有f1這個(gè)字段，插入值分別為0.47,0.44,0.19，我們期待的運(yùn)行結(jié)果是：0.47 + 0.44 + 0.19 =
1.1。而使用sum之后查詢(xún)：

CREATE TABLE test_double2(
f1 DOUBLE
);
INSERT INTO test_double2
VALUES(0.47),(0.44),(0.19);

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)

查詢(xún)結(jié)果是 1.0999999999999999?？吹搅藛?#xff1f;雖然誤差很小，但確實(shí)有誤差。 你也可以嘗試把數(shù)據(jù)類(lèi)型
改成 FLOAT，然后運(yùn)行求和查詢(xún)，得到的是， 1.0999999940395355。顯然，誤差更大了。
那么，為什么會(huì)存在這樣的誤差呢？問(wèn)題還是出在 MySQL 對(duì)浮點(diǎn)類(lèi)型數(shù)據(jù)的存儲(chǔ)方式上。
MySQL 用 4 個(gè)字節(jié)存儲(chǔ) FLOAT 類(lèi)型數(shù)據(jù)，用 8 個(gè)字節(jié)來(lái)存儲(chǔ) DOUBLE 類(lèi)型數(shù)據(jù)。無(wú)論哪個(gè)，都是采用二
進(jìn)制的方式來(lái)進(jìn)行存儲(chǔ)的。比如 9.625，用二進(jìn)制來(lái)表達(dá)，就是 1001.101，或者表達(dá)成 1.001101×2^3。如
果尾數(shù)不是 0 或 5（比如 9.624），你就無(wú)法用一個(gè)二進(jìn)制數(shù)來(lái)精確表達(dá)。進(jìn)而，就只好在取值允許的范
圍內(nèi)進(jìn)行四舍五入。
在編程中，如果用到浮點(diǎn)數(shù)，要特別注意誤差問(wèn)題，因?yàn)楦↑c(diǎn)數(shù)是不準(zhǔn)確的，所以我們要避免使用“=”來(lái)
判斷兩個(gè)數(shù)是否相等。同時(shí)，在一些對(duì)精確度要求較高的項(xiàng)目中，千萬(wàn)不要使用浮點(diǎn)數(shù)，不然會(huì)導(dǎo)致結(jié)
果錯(cuò)誤，甚至是造成不可挽回的損失。那么，MySQL 有沒(méi)有精準(zhǔn)的數(shù)據(jù)類(lèi)型呢？當(dāng)然有，這就是定點(diǎn)數(shù)
類(lèi)型： DECIMAL 。

4. 定點(diǎn)數(shù)類(lèi)型

4.1 類(lèi)型介紹

MySQL中的定點(diǎn)數(shù)類(lèi)型只有 DECIMAL 一種類(lèi)型。

• 使用 DECIMAL(M,D) 的方式表示高精度小數(shù)。其中，M被稱(chēng)為精度，D被稱(chēng)為標(biāo)度。0<=M<=65，
  0<=D<=30，D<M。例如，定義DECIMAL（5,2）的類(lèi)型，表示該列取值范圍是-999.99~999.99。
• DECIMAL(M,D)的最大取值范圍與DOUBLE類(lèi)型一樣，但是有效的數(shù)據(jù)范圍是由M和D決定的。 DECIMAL
  的存儲(chǔ)空間并不是固定的，由精度值M決定，總共占用的存儲(chǔ)空間為M+2個(gè)字節(jié)。也就是
  說(shuō)，在一些對(duì)精度要求不高的場(chǎng)景下，比起占用同樣字節(jié)長(zhǎng)度的定點(diǎn)數(shù)，浮點(diǎn)數(shù)表達(dá)的數(shù)值范圍可 以更大一些。
• 定點(diǎn)數(shù)在MySQL內(nèi)部是以 字符串 的形式進(jìn)行存儲(chǔ)，這就決定了它一定是精準(zhǔn)的。
  當(dāng)DECIMAL類(lèi)型不指定精度和標(biāo)度時(shí)，其默認(rèn)為DECIMAL(10,0)。當(dāng)數(shù)據(jù)的精度超出了定點(diǎn)數(shù)類(lèi)型的
  精度范圍時(shí)，則MySQL同樣會(huì)進(jìn)行四舍五入處理。
• 浮點(diǎn)數(shù) vs 定點(diǎn)數(shù)
  1.浮點(diǎn)數(shù)相對(duì)于定點(diǎn)數(shù)的優(yōu)點(diǎn)是在長(zhǎng)度一定的情況下，浮點(diǎn)類(lèi)型取值范圍大，但是不精準(zhǔn)，適用
  于需要取值范圍大，又可以容忍微小誤差的科學(xué)計(jì)算場(chǎng)景（比如計(jì)算化學(xué)、分子建模、流體動(dòng)
  力學(xué)等）
  2.定點(diǎn)數(shù)類(lèi)型取值范圍相對(duì)小，但是精準(zhǔn)，沒(méi)有誤差，適合于對(duì)精度要求極高的場(chǎng)景 （比如涉
  及金額計(jì)算的場(chǎng)景）
  eg：

CREATE TABLE test_decimal1(
f1 DECIMAL,
f2 DECIMAL(5,2)
);
DESC test_decimal1;
INSERT INTO test_decimal1(f1,f2)
VALUES(123.123,123.456);
#Out of range value for column 'f2' at row 1
INSERT INTO test_decimal1(f2)
VALUES(1234.34);

mysql> SELECT * FROM test_decimal1;
+------+--------+
| f1 | f2 |
+------+--------+
| 123 | 123.46 |
+------+--------+
1 row in set (0.00 sec)

eg：
我們運(yùn)行下面的語(yǔ)句，把test_double2表中字段“f1”的數(shù)據(jù)類(lèi)型修改為 DECIMAL(5,2)：

ALTER TABLE test_double2
MODIFY f1 DECIMAL(5,2);

然后，我們?cè)僖淮芜\(yùn)行求和語(yǔ)句：

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

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

4.2 開(kāi)發(fā)中經(jīng)驗(yàn)

“由于 DECIMAL 數(shù)據(jù)類(lèi)型的精準(zhǔn)性，在我們的項(xiàng)目中，除了極少數(shù)（比如商品編號(hào)）用到整數(shù)類(lèi)型 外，其他的數(shù)值都用的是
DECIMAL，原因就是這個(gè)項(xiàng)目所處的零售行業(yè)，要求精準(zhǔn)，一分錢(qián)也不能 差。 ” ——來(lái)自某項(xiàng)目經(jīng)理

5. 位類(lèi)型：BIT

BIT類(lèi)型中存儲(chǔ)的是二進(jìn)制值，類(lèi)似010110。

BIT類(lèi)型，如果沒(méi)有指定(M)，默認(rèn)是1位。這個(gè)1位，表示只能存1位的二進(jìn)制值。這里(M)是表示二進(jìn)制的
位數(shù)，位數(shù)最小值為1，最大值為64。

CREATE TABLE test_bit1(
f1 BIT,
f2 BIT(5),
f3 BIT(64)
);
INSERT INTO test_bit1(f1)
VALUES(1);
#Data too long for column 'f1' at row 1
INSERT INTO test_bit1(f1)
VALUES(2);
INSERT INTO test_bit1(f2)
VALUES(23);

注意：在向BIT類(lèi)型的字段中插入數(shù)據(jù)時(shí)，一定要確保插入的數(shù)據(jù)在BIT類(lèi)型支持的范圍內(nèi)。

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

mysql> SELECT BIN(f2),HEX(f2)
-> FROM test_bit1;
+---------+---------+
| BIN(f2) | HEX(f2) |
+---------+---------+
| NULL | NULL |
| 10111 | 17 |
+---------+---------+
2 rows in set (0.00 sec)

mysql> SELECT f2 + 0
-> FROM test_bit1;
+--------+
| f2 + 0 |
+--------+
| NULL |
| 23 |
+--------+
2 rows in set (0.00 sec)

可以看到，使用b+0查詢(xún)數(shù)據(jù)時(shí)，可以直接查詢(xún)出存儲(chǔ)的十進(jìn)制數(shù)據(jù)的值。

6. 日期與時(shí)間類(lèi)型

日期與時(shí)間是重要的信息，在我們的系統(tǒng)中，幾乎所有的數(shù)據(jù)表都用得到。原因是客戶(hù)需要知道數(shù)據(jù)的
時(shí)間標(biāo)簽，從而進(jìn)行數(shù)據(jù)查詢(xún)、統(tǒng)計(jì)和處理。
MySQL有多種表示日期和時(shí)間的數(shù)據(jù)類(lèi)型，不同的版本可能有所差異，MySQL8.0版本支持的日期和時(shí)間
類(lèi)型主要有：YEAR類(lèi)型、TIME類(lèi)型、DATE類(lèi)型、DATETIME類(lèi)型和TIMESTAMP類(lèi)型。

• YEAR 類(lèi)型通常用來(lái)表示年
• DATE 類(lèi)型通常用來(lái)表示年、月、日
• TIME 類(lèi)型通常用來(lái)表示時(shí)、分、秒
• DATETIME 類(lèi)型通常用來(lái)表示年、月、日、時(shí)、分、秒
• TIMESTAMP 類(lèi)型通常用來(lái)表示帶時(shí)區(qū)的年、月、日、時(shí)、分、秒
  
  可以看到，不同數(shù)據(jù)類(lèi)型表示的時(shí)間內(nèi)容不同、取值范圍不同，而且占用的字節(jié)數(shù)也不一樣，你要根據(jù)
  實(shí)際需要靈活選取。
  為什么時(shí)間類(lèi)型 TIME 的取值范圍不是 -23:59:59～23:59:59 呢？原因是 MySQL 設(shè)計(jì)的 TIME 類(lèi)型，不光表
  示一天之內(nèi)的時(shí)間，而且可以用來(lái)表示一個(gè)時(shí)間間隔，這個(gè)時(shí)間間隔可以超過(guò) 24 小時(shí)。

6.1 YEAR類(lèi)型

YEAR類(lèi)型用來(lái)表示年份，在所有的日期時(shí)間類(lèi)型中所占用的存儲(chǔ)空間最小，只需要 1個(gè)字節(jié) 的存儲(chǔ)空
間。
在MySQL中，YEAR有以下幾種存儲(chǔ)格式：

• 以4位字符串或數(shù)字格式表示YEAR類(lèi)型，其格式為YYYY，最小值為1901，最大值為2155。
• 以2位字符串格式表示YEAR類(lèi)型，最小值為00，最大值為99。

1.當(dāng)取值為01到69時(shí)，表示2001到2069；
2.當(dāng)取值為70到99時(shí)，表示1970到1999；
3.當(dāng)取值整數(shù)的0或00添加的話(huà)，那么是0000年；
4.當(dāng)取值是日期/字符串的’0’添加的話(huà)，是2000年。
從MySQL5.5.27開(kāi)始，2位格式的YEAR已經(jīng)不推薦使用。YEAR默認(rèn)格式就是“YYYY”，沒(méi)必要寫(xiě)成YEAR(4)，
從MySQL 8.0.19開(kāi)始，不推薦使用指定顯示寬度的YEAR(4)數(shù)據(jù)類(lèi)型。

CREATE TABLE test_year(
f1 YEAR,
f2 YEAR(4)
);

INSERT INTO test_year
VALUES('2020','2021');

mysql> DESC test_year;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| f1 | year(4) | YES | | NULL | |
| f2 | year(4) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM test_year;
+------+------+
| f1 | f2 |
+------+------+
| 2020 | 2021 |
+------+------+
1 rows in set (0.00 sec)

INSERT INTO test_year
VALUES('45','71');
INSERT INTO test_year
VALUES(0,'0');
mysql> SELECT * FROM test_year;
+------+------+
| f1 | f2 |
+------+------+
| 2020 | 2021 |
| 2045 | 1971 |
| 0000 | 2000 |
+------+------+
3 rows in set (0.00 sec)

6.2 DATE類(lèi)型

DATE類(lèi)型表示日期，沒(méi)有時(shí)間部分，格式為 YYYY-MM-DD ，其中，YYYY表示年份，MM表示月份，DD表示
日期。需要 3個(gè)字節(jié) 的存儲(chǔ)空間。在向DATE類(lèi)型的字段插入數(shù)據(jù)時(shí)，同樣需要滿(mǎn)足一定的格式條件。

• 以 YYYY-MM-DD 格式或者 YYYYMMDD 格式表示的字符串日期，其最小取值為1000-01-01，最大取值為
  9999-12-03。YYYYMMDD格式會(huì)被轉(zhuǎn)化為YYYY-MM-DD格式。
• 以 YY-MM-DD 格式或者 YYMMDD 格式表示的字符串日期，此格式中，年份為兩位數(shù)值或字符串滿(mǎn)足
  YEAR類(lèi)型的格式條件為：當(dāng)年份取值為00到69時(shí)，會(huì)被轉(zhuǎn)化為2000到2069；當(dāng)年份取值為70到99
  時(shí)，會(huì)被轉(zhuǎn)化為1970到1999。
• 使用 CURRENT_DATE() 或者 NOW() 函數(shù)，會(huì)插入當(dāng)前系統(tǒng)的日期。

舉例：
創(chuàng)建數(shù)據(jù)表，表中只包含一個(gè)DATE類(lèi)型的字段f1。

CREATE TABLE test_date1(
f1 DATE
);
Query OK, 0 rows affected (0.13 sec)

插入數(shù)據(jù)：

INSERT INTO test_date1
VALUES ('2020-10-01'), ('20201001'),(20201001);
INSERT INTO test_date1
VALUES ('00-01-01'), ('000101'), ('69-10-01'), ('691001'), ('70-01-01'), ('700101'),
('99-01-01'), ('990101');

INSERT INTO test_date1
VALUES (000301), (690301), (700301), (990301);
INSERT INTO test_date1
VALUES (CURRENT_DATE()), (NOW());
SELECT *
FROM test_date1;

6.3 TIME類(lèi)型

TIME類(lèi)型用來(lái)表示時(shí)間，不包含日期部分。在MySQL中，需要 3個(gè)字節(jié) 的存儲(chǔ)空間來(lái)存儲(chǔ)TIME類(lèi)型的數(shù)
據(jù)，可以使用“HH:MM:SS”格式來(lái)表示TIME類(lèi)型，其中，HH表示小時(shí)，MM表示分鐘，SS表示秒。
在MySQL中，向TIME類(lèi)型的字段插入數(shù)據(jù)時(shí)，也可以使用幾種不同的格式。 （1）可以使用帶有冒號(hào)的
字符串，比如’ D HH:MM:SS' 、' HH:MM:SS '、' HH:MM '、' D HH:MM '、' D HH '或' SS '格式，都能被正
確地插入TIME類(lèi)型的字段中。其中D表示天，其最小值為0，最大值為34。如果使用帶有D格式的字符串
插入TIME類(lèi)型的字段時(shí)，D會(huì)被轉(zhuǎn)化為小時(shí)，計(jì)算格式為D*24+HH。當(dāng)使用帶有冒號(hào)并且不帶D的字符串
表示時(shí)間時(shí)，表示當(dāng)天的時(shí)間，比如12:10表示12:10:00，而不是00:12:10。 （2）可以使用不帶有冒號(hào)的
字符串或者數(shù)字，格式為’ HHMMSS '或者 HHMMSS 。如果插入一個(gè)不合法的字符串或者數(shù)字，MySQL在存
儲(chǔ)數(shù)據(jù)時(shí)，會(huì)將其自動(dòng)轉(zhuǎn)化為00:00:00進(jìn)行存儲(chǔ)。比如1210，MySQL會(huì)將最右邊的兩位解析成秒，表示
00:12:10，而不是12:10:00。 （3）使用 CURRENT_TIME() 或者 NOW() ，會(huì)插入當(dāng)前系統(tǒng)的時(shí)間。
舉例：
創(chuàng)建數(shù)據(jù)表，表中包含一個(gè)TIME類(lèi)型的字段f1。

CREATE TABLE test_time1(
f1 TIME
);
Query OK, 0 rows affected (0.02 sec)

INSERT INTO test_time1
VALUES('2 12:30:29'), ('12:35:29'), ('12:40'), ('2 12:40'),('1 05'), ('45');
INSERT INTO test_time1
VALUES ('123520'), (124011),(1210);
INSERT INTO test_time1
VALUES (NOW()), (CURRENT_TIME());
SELECT * FROM test_time1;

6.4 DATETIME類(lèi)型

DATETIME類(lèi)型在所有的日期時(shí)間類(lèi)型中占用的存儲(chǔ)空間最大，總共需要 8 個(gè)字節(jié)的存儲(chǔ)空間。在格式上
為DATE類(lèi)型和TIME類(lèi)型的組合，可以表示為 YYYY-MM-DD HH:MM:SS ，其中YYYY表示年份，MM表示月
份，DD表示日期，HH表示小時(shí)，MM表示分鐘，SS表示秒。
在向DATETIME類(lèi)型的字段插入數(shù)據(jù)時(shí)，同樣需要滿(mǎn)足一定的格式條件。

• 以 YYYY-MM-DD HH:MM:SS 格式或者 YYYYMMDDHHMMSS 格式的字符串插入DATETIME類(lèi)型的字段時(shí)，
  最小值為1000-01-01 00:00:00，最大值為9999-12-03 23:59:59。
• 以YYYYMMDDHHMMSS格式的數(shù)字插入DATETIME類(lèi)型的字段時(shí)，會(huì)被轉(zhuǎn)化為YYYY-MM-DD HH:MM:SS格式。
• 使用函數(shù) CURRENT_TIMESTAMP() 和 NOW() ，可以向DATETIME類(lèi)型的字段插入系統(tǒng)的當(dāng)前日期和 時(shí)間。
  舉例：
  創(chuàng)建數(shù)據(jù)表，表中包含一個(gè)DATETIME類(lèi)型的字段dt。

CREATE TABLE test_datetime1(
dt DATETIME
);
Query OK, 0 rows affected (0.02 sec)

插入數(shù)據(jù)：

INSERT INTO test_datetime1
VALUES ('2021-01-01 06:50:30'), ('20210101065030');
INSERT INTO test_datetime1
VALUES ('99-01-01 00:00:00'), ('990101000000'), ('20-01-01 00:00:00'),
('200101000000');
INSERT INTO test_datetime1
VALUES (20200101000000), (200101000000), (19990101000000), (990101000000);
INSERT INTO test_datetime1
VALUES (CURRENT_TIMESTAMP()), (NOW());

6.5 TIMESTAMP類(lèi)型

TIMESTAMP類(lèi)型也可以表示日期時(shí)間，其顯示格式與DATETIME類(lèi)型相同，都是 YYYY-MM-DD
HH:MM:SS ，需要4個(gè)字節(jié)的存儲(chǔ)空間。但是TIMESTAMP存儲(chǔ)的時(shí)間范圍比DATETIME要小很多，只能存儲(chǔ)
“1970-01-01 00:00:01 UTC”到“2038-01-19 03:14:07 UTC”之間的時(shí)間。其中，UTC表示世界統(tǒng)一時(shí)間，也叫
作世界標(biāo)準(zhǔn)時(shí)間。

存儲(chǔ)數(shù)據(jù)的時(shí)候需要對(duì)當(dāng)前時(shí)間所在的時(shí)區(qū)進(jìn)行轉(zhuǎn)換，查詢(xún)數(shù)據(jù)的時(shí)候再將時(shí)間轉(zhuǎn)換回當(dāng)前的時(shí)
區(qū)。因此，使用TIMESTAMP存儲(chǔ)的同一個(gè)時(shí)間值，在不同的時(shí)區(qū)查詢(xún)時(shí)會(huì)顯示不同的時(shí)間。

向TIMESTAMP類(lèi)型的字段插入數(shù)據(jù)時(shí)，當(dāng)插入的數(shù)據(jù)格式滿(mǎn)足YY-MM-DD HH:MM:SS和YYMMDDHHMMSS
時(shí)，兩位數(shù)值的年份同樣符合YEAR類(lèi)型的規(guī)則條件，只不過(guò)表示的時(shí)間范圍要小很多。
如果向TIMESTAMP類(lèi)型的字段插入的時(shí)間超出了TIMESTAMP類(lèi)型的范圍，則MySQL會(huì)拋出錯(cuò)誤信息。
舉例：
創(chuàng)建數(shù)據(jù)表，表中包含一個(gè)TIMESTAMP類(lèi)型的字段ts。

CREATE TABLE test_timestamp1(
ts TIMESTAMP
);

插入數(shù)據(jù)：

INSERT INTO test_timestamp1
VALUES ('1999-01-01 03:04:50'), ('19990101030405'), ('99-01-01 03:04:05'),
('990101030405');
INSERT INTO test_timestamp1
VALUES ('2020@01@01@00@00@00'), ('20@01@01@00@00@00');
INSERT INTO test_timestamp1
VALUES (CURRENT_TIMESTAMP()), (NOW());
#Incorrect datetime value
INSERT INTO test_timestamp1
VALUES ('2038-01-20 03:14:07');

TIMESTAMP和DATETIME的區(qū)別：

• TIMESTAMP存儲(chǔ)空間比較小，表示的日期時(shí)間范圍也比較小
• 底層存儲(chǔ)方式不同，TIMESTAMP底層存儲(chǔ)的是毫秒值，距離1970-1-1 0:0:0 0毫秒的毫秒值。
• 兩個(gè)日期比較大小或日期計(jì)算時(shí)，TIMESTAMP更方便、更快。
• TIMESTAMP和時(shí)區(qū)有關(guān)。TIMESTAMP會(huì)根據(jù)用戶(hù)的時(shí)區(qū)不同，顯示不同的結(jié)果。而DATETIME則只能
  反映出插入時(shí)當(dāng)?shù)氐臅r(shí)區(qū)，其他時(shí)區(qū)的人查看數(shù)據(jù)必然會(huì)有誤差的。

CREATE TABLE temp_time(
d1 DATETIME,
d2 TIMESTAMP
);

INSERT INTO temp_time VALUES('2021-9-2 14:45:52','2021-9-2 14:45:52');
INSERT INTO temp_time VALUES(NOW(),NOW());

mysql> SELECT * FROM temp_time;
+---------------------+---------------------+
| d1 | d2 |
+---------------------+---------------------+
| 2021-09-02 14:45:52 | 2021-09-02 14:45:52 |
| 2021-11-03 17:38:17 | 2021-11-03 17:38:17 |
+---------------------+---------------------+
2 rows in set (0.00 sec)

#修改當(dāng)前的時(shí)區(qū)
SET time_zone = '+9:00';

mysql> SELECT * FROM temp_time;
+---------------------+---------------------+
| d1 | d2 |
+---------------------+---------------------+
| 2021-09-02 14:45:52 | 2021-09-02 15:45:52 |
| 2021-11-03 17:38:17 | 2021-11-03 18:38:17 |
+---------------------+---------------------+
2 rows in set (0.00 sec)

6.6 開(kāi)發(fā)中經(jīng)驗(yàn)

用得最多的日期時(shí)間類(lèi)型，就是 DATETIME 。雖然 MySQL 也支持 YEAR（年）、 TIME（時(shí)間）、
DATE（日期），以及 TIMESTAMP 類(lèi)型，但是在實(shí)際項(xiàng)目中，盡量用 DATETIME 類(lèi)型。因?yàn)檫@個(gè)數(shù)據(jù)類(lèi)型
包括了完整的日期和時(shí)間信息，取值范圍也最大，使用起來(lái)比較方便。畢竟，如果日期時(shí)間信息分散在
好幾個(gè)字段，很不容易記，而且查詢(xún)的時(shí)候，SQL 語(yǔ)句也會(huì)更加復(fù)雜。
此外，一般存注冊(cè)時(shí)間、商品發(fā)布時(shí)間等，不建議使用DATETIME存儲(chǔ)，而是使用 時(shí)間戳 ，因?yàn)?br /> DATETIME雖然直觀(guān)，但不便于計(jì)算。

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

7. 文本字符串類(lèi)型

在實(shí)際的項(xiàng)目中，我們還經(jīng)常遇到一種數(shù)據(jù)，就是字符串?dāng)?shù)據(jù)。
MySQL中，文本字符串總體上分為 CHAR 、 VARCHAR 、 TINYTEXT 、 TEXT 、 MEDIUMTEXT 、
LONGTEXT 、 ENUM 、 SET 等類(lèi)型。

7.1 CHAR與VARCHAR類(lèi)型

CHAR和VARCHAR類(lèi)型都可以存儲(chǔ)比較短的字符串。

CHAR類(lèi)型：

• CHAR(M) 類(lèi)型一般需要預(yù)先定義字符串長(zhǎng)度。如果不指定(M)，則表示長(zhǎng)度默認(rèn)是1個(gè)字符。

如果保存時(shí)，數(shù)據(jù)的實(shí)際長(zhǎng)度比CHAR類(lèi)型聲明的長(zhǎng)度小，則會(huì)在 右側(cè)填充 空格以達(dá)到指定的長(zhǎng)
度。當(dāng)MySQL檢索CHAR類(lèi)型的數(shù)據(jù)時(shí)，CHAR類(lèi)型的字段會(huì)去除尾部的空格。

• 定義CHAR類(lèi)型字段時(shí)，聲明的字段長(zhǎng)度即為CHAR類(lèi)型字段所占的存儲(chǔ)空間的字節(jié)數(shù)。

CREATE TABLE test_char1(
c1 CHAR,
c2 CHAR(5)
);
DESC test_char1;

INSERT INTO test_char1
VALUES('a','Tom');
SELECT c1,CONCAT(c2,'***') FROM test_char1;

INSERT INTO test_char1(c2)
VALUES('a ');
SELECT CHAR_LENGTH(c2)
FROM test_char1;

VARCHAR類(lèi)型：

• VARCHAR(M) 定義時(shí)， 必須指定 長(zhǎng)度M，否則報(bào)錯(cuò)。
• MySQL4.0版本以下，varchar(20)：指的是20字節(jié)，如果存放UTF8漢字時(shí)，只能存6個(gè)（每個(gè)漢字3字 節(jié)）
  ；MySQL5.0版本以上，varchar(20)：指的是20字符。
• 檢索VARCHAR類(lèi)型的字段數(shù)據(jù)時(shí)，會(huì)保留數(shù)據(jù)尾部的空格。VARCHAR類(lèi)型的字段所占用的存儲(chǔ)空間 為字符串實(shí)際長(zhǎng)度加1個(gè)字節(jié)。

CREATE TABLE test_varchar1(
NAME VARCHAR #錯(cuò)誤
);

#Column length too big for column 'NAME' (max = 21845);
CREATE TABLE test_varchar2(
NAME VARCHAR(65535) #錯(cuò)誤
);

CREATE TABLE test_varchar3(
NAME VARCHAR(5)
);
INSERT INTO test_varchar3
VALUES('尚硅谷'),('尚硅谷教育');
#Data too long for column 'NAME' at row 1
INSERT INTO test_varchar3
VALUES('尚硅谷IT教育');

哪些情況使用 CHAR 或 VARCHAR 更好

情況1：存儲(chǔ)很短的信息。比如門(mén)牌號(hào)碼101，201……這樣很短的信息應(yīng)該用char，因?yàn)関archar還要占個(gè)
byte用于存儲(chǔ)信息長(zhǎng)度，本來(lái)打算節(jié)約存儲(chǔ)的，結(jié)果得不償失。
情況2：固定長(zhǎng)度的。比如使用uuid作為主鍵，那用char應(yīng)該更合適。因?yàn)樗潭ㄩL(zhǎng)度，varchar動(dòng)態(tài)根據(jù)
長(zhǎng)度的特性就消失了，而且還要占個(gè)長(zhǎng)度信息。
情況3：十分頻繁改變的column。因?yàn)関archar每次存儲(chǔ)都要有額外的計(jì)算，得到長(zhǎng)度等工作，如果一個(gè)
非常頻繁改變的，那就要有很多的精力用于計(jì)算，而這些對(duì)于char來(lái)說(shuō)是不需要的。
情況4：具體存儲(chǔ)引擎中的情況：

• MyISAM 數(shù)據(jù)存儲(chǔ)引擎和數(shù)據(jù)列：MyISAM數(shù)據(jù)表，最好使用固定長(zhǎng)度(CHAR)的數(shù)據(jù)列代替可變長(zhǎng)
  度(VARCHAR)的數(shù)據(jù)列。這樣使得整個(gè)表靜態(tài)化，從而使 數(shù)據(jù)檢索更快 ，用空間換時(shí)間。
• MEMORY 存儲(chǔ)引擎和數(shù)據(jù)列：MEMORY數(shù)據(jù)表目前都使用固定長(zhǎng)度的數(shù)據(jù)行存儲(chǔ)，因此無(wú)論使用
  CHAR或VARCHAR列都沒(méi)有關(guān)系，兩者都是作為CHAR類(lèi)型處理的。
• InnoDB 存儲(chǔ)引擎，建議使用VARCHAR類(lèi)型。因?yàn)閷?duì)于InnoDB數(shù)據(jù)表，內(nèi)部的行存儲(chǔ)格式并沒(méi)有區(qū)
  分固定長(zhǎng)度和可變長(zhǎng)度列（所有數(shù)據(jù)行都使用指向數(shù)據(jù)列值的頭指針），而且主要影響性能的因素
  是數(shù)據(jù)行使用的存儲(chǔ)總量，由于char平均占用的空間多于varchar，所以除了簡(jiǎn)短并且固定長(zhǎng)度的，
  其他考慮varchar。這樣節(jié)省空間，對(duì)磁盤(pán)I/O和數(shù)據(jù)存儲(chǔ)總量比較好。

7.2 TEXT類(lèi)型

在MySQL中，TEXT用來(lái)保存文本類(lèi)型的字符串，總共包含4種類(lèi)型，分別為T(mén)INYTEXT、TEXT、
MEDIUMTEXT 和 LONGTEXT 類(lèi)型。
在向TEXT類(lèi)型的字段保存和查詢(xún)數(shù)據(jù)時(shí)，系統(tǒng)自動(dòng)按照實(shí)際長(zhǎng)度存儲(chǔ)，不需要預(yù)先定義長(zhǎng)度。這一點(diǎn)和
VARCHAR類(lèi)型相同。
每種TEXT類(lèi)型保存的數(shù)據(jù)長(zhǎng)度和所占用的存儲(chǔ)空間不同，如下：

由于實(shí)際存儲(chǔ)的長(zhǎng)度不確定，MySQL 不允許 TEXT 類(lèi)型的字段做主鍵。遇到這種情況，你只能采用
CHAR(M)，或者 VARCHAR(M)。
舉例：
創(chuàng)建數(shù)據(jù)表：

CREATE TABLE test_text(
tx TEXT
);

INSERT INTO test_text
VALUES('atguigu ');
SELECT CHAR_LENGTH(tx)
FROM test_text; #10

開(kāi)發(fā)中經(jīng)驗(yàn)：
TEXT文本類(lèi)型，可以存比較大的文本段，搜索速度稍慢，因此如果不是特別大的內(nèi)容，建議使用CHAR，
VARCHAR來(lái)代替。還有TEXT類(lèi)型不用加默認(rèn)值，加了也沒(méi)用。而且text和blob類(lèi)型的數(shù)據(jù)刪除后容易導(dǎo)致
“空洞”，使得文件碎片比較多，所以頻繁使用的表不建議包含TEXT類(lèi)型字段，建議單獨(dú)分出去，單獨(dú)用
一個(gè)表。

8. ENUM類(lèi)型

ENUM類(lèi)型也叫作枚舉類(lèi)型，ENUM類(lèi)型的取值范圍需要在定義字段時(shí)進(jìn)行指定。設(shè)置字段值時(shí)，ENUM
類(lèi)型只允許從成員中選取單個(gè)值，不能一次選取多個(gè)值。
其所需要的存儲(chǔ)空間由定義ENUM類(lèi)型時(shí)指定的成員個(gè)數(shù)決定。

• 當(dāng)ENUM類(lèi)型包含1～255個(gè)成員時(shí)，需要1個(gè)字節(jié)的存儲(chǔ)空間；
• 當(dāng)ENUM類(lèi)型包含256～65535個(gè)成員時(shí)，需要2個(gè)字節(jié)的存儲(chǔ)空間。
• ENUM類(lèi)型的成員個(gè)數(shù)的上限為65535個(gè)。
  舉例：
  創(chuàng)建表如下：

CREATE TABLE test_enum(
season ENUM('春','夏','秋','冬','unknow')
);

添加數(shù)據(jù)：

INSERT INTO test_enum
VALUES('春'),('秋');
# 忽略大小寫(xiě)
INSERT INTO test_enum
VALUES('UNKNOW');
# 允許按照角標(biāo)的方式獲取指定索引位置的枚舉值
INSERT INTO test_enum
VALUES('1'),(3);
# Data truncated for column 'season' at row 1
INSERT INTO test_enum
VALUES('ab');
# 當(dāng)ENUM類(lèi)型的字段沒(méi)有聲明為NOT NULL時(shí)，插入NULL也是有效的
INSERT INTO test_enum
VALUES(NULL);

9. SET類(lèi)型

SET表示一個(gè)字符串對(duì)象，可以包含0個(gè)或多個(gè)成員，但成員個(gè)數(shù)的上限為 64 。設(shè)置字段值時(shí)，可以取
取值范圍內(nèi)的 0 個(gè)或多個(gè)值。
當(dāng)SET類(lèi)型包含的成員個(gè)數(shù)不同時(shí)，其所占用的存儲(chǔ)空間也是不同的，具體如下：

SET類(lèi)型在存儲(chǔ)數(shù)據(jù)時(shí)成員個(gè)數(shù)越多，其占用的存儲(chǔ)空間越大。注意：SET類(lèi)型在選取成員時(shí)，可以一次
選擇多個(gè)成員，這一點(diǎn)與ENUM類(lèi)型不同。
舉例：
創(chuàng)建表：

CREATE TABLE test_set(
s SET ('A', 'B', 'C')
);

向表中插入數(shù)據(jù)：

INSERT INTO test_set (s) VALUES ('A'), ('A,B');
#插入重復(fù)的SET類(lèi)型成員時(shí)，MySQL會(huì)自動(dòng)刪除重復(fù)的成員
INSERT INTO test_set (s) VALUES ('A,B,C,A');
#向SET類(lèi)型的字段插入SET成員中不存在的值時(shí)，MySQL會(huì)拋出錯(cuò)誤。
INSERT INTO test_set (s) VALUES ('A,B,C,D');
SELECT *
FROM test_set;

舉例：

CREATE TABLE temp_mul(
gender ENUM('男','女'),
hobby SET('吃飯','睡覺(jué)','打豆豆','寫(xiě)代碼')
);

INSERT INTO temp_mul VALUES('男','睡覺(jué),打豆豆'); #成功
# Data truncated for column 'gender' at row 1
INSERT INTO temp_mul VALUES('男,女','睡覺(jué),寫(xiě)代碼'); #失敗
# Data truncated for column 'gender' at row 1
INSERT INTO temp_mul VALUES('妖','睡覺(jué),寫(xiě)代碼');#失敗
INSERT INTO temp_mul VALUES('男','睡覺(jué),寫(xiě)代碼,吃飯'); #成功

MySQL中的二進(jìn)制字符串類(lèi)型主要存儲(chǔ)一些二進(jìn)制數(shù)據(jù)，比如可以存儲(chǔ)圖片、音頻和視頻等二進(jìn)制數(shù)
據(jù)。
MySQL中支持的二進(jìn)制字符串類(lèi)型主要包括BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和
LONGBLOB類(lèi)型。
BINARY與VARBINARY類(lèi)型
BINARY和VARBINARY類(lèi)似于CHAR和VARCHAR，只是它們存儲(chǔ)的是二進(jìn)制字符串。
BINARY (M)為固定長(zhǎng)度的二進(jìn)制字符串，M表示最多能存儲(chǔ)的字節(jié)數(shù)，取值范圍是0~255個(gè)字符。如果未
指定(M)，表示只能存儲(chǔ) 1個(gè)字節(jié) 。例如BINARY (8)，表示最多能存儲(chǔ)8個(gè)字節(jié)，如果字段值不足(M)個(gè)字
節(jié)，將在右邊填充’\0’以補(bǔ)齊指定長(zhǎng)度。
VARBINARY (M)為可變長(zhǎng)度的二進(jìn)制字符串，M表示最多能存儲(chǔ)的字節(jié)數(shù)，總字節(jié)數(shù)不能超過(guò)行的字節(jié)長(zhǎng)
度限制65535，另外還要考慮額外字節(jié)開(kāi)銷(xiāo)，VARBINARY類(lèi)型的數(shù)據(jù)除了存儲(chǔ)數(shù)據(jù)本身外，還需要1或2個(gè)
字節(jié)來(lái)存儲(chǔ)數(shù)據(jù)的字節(jié)數(shù)。VARBINARY類(lèi)型 必須指定(M) ，否則報(bào)錯(cuò)。

舉例：
創(chuàng)建表：

CREATE TABLE test_binary1(
f1 BINARY,
f2 BINARY(3),
# f3 VARBINARY,
f4 VARBINARY(10)
);

添加數(shù)據(jù)：

INSERT INTO test_binary1(f1,f2)
VALUES('a','a');
INSERT INTO test_binary1(f1,f2)
VALUES('尚','尚');#失敗

INSERT INTO test_binary1(f2,f4)
VALUES('ab','ab');
mysql> SELECT LENGTH(f2),LENGTH(f4)
-> FROM test_binary1;
+------------+------------+
| LENGTH(f2) | LENGTH(f4) |
+------------+------------+
| 3 | NULL |
| 3 | 2 |
+------------+------------+
2 rows in set (0.00 sec)

BLOB是一個(gè) 二進(jìn)制大對(duì)象 ，可以容納可變數(shù)量的數(shù)據(jù)。
MySQL中的BLOB類(lèi)型包括TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB 4種類(lèi)型，它們可容納值的最大
長(zhǎng)度不同?？梢源鎯?chǔ)一個(gè)二進(jìn)制的大對(duì)象，比如 圖片 、 音頻 和 視頻 等。
需要注意的是，在實(shí)際工作中，往往不會(huì)在MySQL數(shù)據(jù)庫(kù)中使用BLOB類(lèi)型存儲(chǔ)大對(duì)象數(shù)據(jù)，通常會(huì)將圖
片、音頻和視頻文件存儲(chǔ)到 服務(wù)器的磁盤(pán)上 ，并將圖片、音頻和視頻的訪(fǎng)問(wèn)路徑存儲(chǔ)到MySQL中。

舉例：

CREATE TABLE test_blob1(
id INT,
img MEDIUMBLOB
);

TEXT和BLOB的使用注意事項(xiàng)：
在使用text和blob字段類(lèi)型時(shí)要注意以下幾點(diǎn)，以便更好的發(fā)揮數(shù)據(jù)庫(kù)的性能。
① BLOB和TEXT值也會(huì)引起自己的一些問(wèn)題，特別是執(zhí)行了大量的刪除或更新操作的時(shí)候。刪除這種值
會(huì)在數(shù)據(jù)表中留下很大的" 空洞 "，以后填入這些"空洞"的記錄可能長(zhǎng)度不同。為了提高性能，建議定期
使用 OPTIMIZE TABLE 功能對(duì)這類(lèi)表進(jìn)行 碎片整理 。
② 如果需要對(duì)大文本字段進(jìn)行模糊查詢(xún)，MySQL 提供了 前綴索引 。但是仍然要在不必要的時(shí)候避免檢
索大型的BLOB或TEXT值。例如，SELECT * 查詢(xún)就不是很好的想法，除非你能夠確定作為約束條件的
WHERE子句只會(huì)找到所需要的數(shù)據(jù)行。否則，你可能毫無(wú)目的地在網(wǎng)絡(luò)上傳輸大量的值。
③ 把BLOB或TEXT列 分離到單獨(dú)的表 中。在某些環(huán)境中，如果把這些數(shù)據(jù)列移動(dòng)到第二張數(shù)據(jù)表中，可
以讓你把原數(shù)據(jù)表中的數(shù)據(jù)列轉(zhuǎn)換為固定長(zhǎng)度的數(shù)據(jù)行格式，那么它就是有意義的。這會(huì) 減少主表中的
碎片 ，使你得到固定長(zhǎng)度數(shù)據(jù)行的性能優(yōu)勢(shì)。它還使你在主數(shù)據(jù)表上運(yùn)行 SELECT * 查詢(xún)的時(shí)候不會(huì)通過(guò)
網(wǎng)絡(luò)傳輸大量的BLOB或TEXT值。

11. JSON 類(lèi)型

JSON（JavaScript Object Notation）是一種輕量級(jí)的 數(shù)據(jù)交換格式 。簡(jiǎn)潔和清晰的層次結(jié)構(gòu)使得 JSON 成
為理想的數(shù)據(jù)交換語(yǔ)言。它易于人閱讀和編寫(xiě)，同時(shí)也易于機(jī)器解析和生成，并有效地提升網(wǎng)絡(luò)傳輸效
率。JSON 可以將 JavaScript 對(duì)象中表示的一組數(shù)據(jù)轉(zhuǎn)換為字符串，然后就可以在網(wǎng)絡(luò)或者程序之間輕
松地傳遞這個(gè)字符串，并在需要的時(shí)候?qū)⑺€原為各編程語(yǔ)言所支持的數(shù)據(jù)格式。
在MySQL 5.7中，就已經(jīng)支持JSON數(shù)據(jù)類(lèi)型。在MySQL 8.x版本中，JSON類(lèi)型提供了可以進(jìn)行自動(dòng)驗(yàn)證的
JSON文檔和優(yōu)化的存儲(chǔ)結(jié)構(gòu)，使得在MySQL中存儲(chǔ)和讀取JSON類(lèi)型的數(shù)據(jù)更加方便和高效。 創(chuàng)建數(shù)據(jù)
表，表中包含一個(gè)JSON類(lèi)型的字段 js 。

CREATE TABLE test_json(
js json
);

向表中插入JSON數(shù)據(jù)。

INSERT INTO test_json (js)
VALUES ('{"name":"songhk", "age":18, "address":{"province":"beijing",
"city":"beijing"}}');

查詢(xún)t19表中的數(shù)據(jù)。

mysql> SELECT *
-> FROM test_json;

當(dāng)需要檢索JSON類(lèi)型的字段中數(shù)據(jù)的某個(gè)具體值時(shí)，可以使用“->”和“->>”符號(hào)。

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 row in set (0.00 sec)

12. 空間類(lèi)型

MySQL 空間類(lèi)型擴(kuò)展支持地理特征的生成、存儲(chǔ)和分析。這里的地理特征表示世界上具有位置的任何東
西，可以是一個(gè)實(shí)體，例如一座山；可以是空間，例如一座辦公樓；也可以是一個(gè)可定義的位置，例如
一個(gè)十字路口等等。MySQL中使用 Geometry（幾何） 來(lái)表示所有地理特征。Geometry指一個(gè)點(diǎn)或點(diǎn)的
集合，代表世界上任何具有位置的事物。
MySQL的空間數(shù)據(jù)類(lèi)型（Spatial Data Type）對(duì)應(yīng)于OpenGIS類(lèi)，包括單值類(lèi)型：GEOMETRY、POINT、
LINESTRING、POLYGON以及集合類(lèi)型：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、
GEOMETRYCOLLECTION 。

• Geometry是所有空間集合類(lèi)型的基類(lèi)，其他類(lèi)型如POINT、LINESTRING、POLYGON都是Geometry的 子類(lèi)。

Point，顧名思義就是點(diǎn)，有一個(gè)坐標(biāo)值。例如POINT(121.213342 31.234532)，POINT(30 10)，
坐標(biāo)值支持DECIMAL類(lèi)型，經(jīng)度（longitude）在前，維度（latitude）在后，用空格分隔。
LineString，線(xiàn)，由一系列點(diǎn)連接而成。如果線(xiàn)從頭至尾沒(méi)有交叉，那就是簡(jiǎn)單的
（simple）；如果起點(diǎn)和終點(diǎn)重疊，那就是封閉的（closed）。例如LINESTRING(30 10,10 30,40
40)，點(diǎn)與點(diǎn)之間用逗號(hào)分隔，一個(gè)點(diǎn)中的經(jīng)緯度用空格分隔，與POINT格式一致。
下面展示幾種常見(jiàn)的幾何圖形元素：

• MultiPoint、MultiLineString、MultiPolygon、GeometryCollection 這4種類(lèi)型都是集合類(lèi)，是多個(gè) Point、LineString或Polygon組合而成。

下面展示的是多個(gè)同類(lèi)或異類(lèi)幾何圖形元素的組合：

13. 小結(jié)及選擇建議

在定義數(shù)據(jù)類(lèi)型時(shí)，如果確定是 整數(shù) ，就用 INT ； 如果是 小數(shù) ，一定用定點(diǎn)數(shù)類(lèi)型
DECIMAL(M,D) ； 如果是日期與時(shí)間，就用 DATETIME 。
這樣做的好處是，首先確保你的系統(tǒng)不會(huì)因?yàn)閿?shù)據(jù)類(lèi)型定義出錯(cuò)。不過(guò)，凡事都是有兩面的，可靠性
好，并不意味著高效。比如，TEXT 雖然使用方便，但是效率不如 CHAR(M) 和 VARCHAR(M)。
關(guān)于字符串的選擇，建議參考如下阿里巴巴的《Java開(kāi)發(fā)手冊(cè)》規(guī)范：
阿里巴巴《Java開(kāi)發(fā)手冊(cè)》之MySQL數(shù)據(jù)庫(kù)：

• 任何字段如果為非負(fù)數(shù)，必須是 UNSIGNED
• 【 強(qiáng)制 】小數(shù)類(lèi)型為 DECIMAL，禁止使用 FLOAT 和 DOUBLE。

說(shuō)明：在存儲(chǔ)的時(shí)候，FLOAT 和 DOUBLE 都存在精度損失的問(wèn)題，很可能在比較值的時(shí)候，得
到不正確的結(jié)果。如果存儲(chǔ)的數(shù)據(jù)范圍超過(guò) DECIMAL 的范圍，建議將數(shù)據(jù)拆成整數(shù)和小數(shù)并
分開(kāi)存儲(chǔ)。

• 【 強(qiáng)制 】如果存儲(chǔ)的字符串長(zhǎng)度幾乎相等，使用 CHAR 定長(zhǎng)字符串類(lèi)型。
• 【 強(qiáng)制 】VARCHAR 是可變長(zhǎng)字符串，不預(yù)先分配存儲(chǔ)空間，長(zhǎng)度不要超過(guò) 5000。如果存儲(chǔ)長(zhǎng)度大 于此值，定義字段類(lèi)型為
  TEXT，獨(dú)立出來(lái)一張表，用主鍵來(lái)對(duì)應(yīng)，避免影響其它字段索引效率。