資料類型詳述

• 這節為了解型的知識點，有些筆記我直接複製貼上了
• 康師父這節講得太細，趕時間直接看最底的結論(對應教學影片p65)
• 用語注意:byte = 8 bit = 字節 = 位元組

MySQL中的資料類型

• 常見資料類型的屬性，如下:

整數類型

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

• 它們的區別如下表所示:

可選屬性

整數類型的可選屬性有三個

UNSIGNED

• UNSIGNED:無符號類型（非負），所有的整數類型都有一個可選的屬性UNSIGNED（無符號屬性），不帶正負號的整數類型的最小取值為0。所以，如果需要在MySQL資料庫中保存非負整數值時，可以將整數類型設置為無符號類型。
• int類型預設顯示寬度為int(11)，無符號int類型預設顯示寬度為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)

M

單用沒有意義，通常是與ZEROFILL一起用，而且在8版基本廢棄了

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

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

  • 答案:不會對插入的資料有任何影響，還是按照類型的實際寬度進行保存，即顯示寬度與類型可以存儲的值範圍無關。

• 從MySQL 8.0.17開始，整數資料類型不推薦使用顯示寬度屬性。

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

• 舉例:

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

• 查看表結構 （MySQL5.7中顯式如下，MySQL8中不再顯式範圍）

mysql> desc test_int1;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
|   x   | tinyint(4)   | YES  |     | NULL    |       |
| 　y   | smallint(6)  | YES  |     | NULL    |       |
| 　z   | mediumint(9) | YES  |     | NULL    |       |
| 　m   | int(11)      | YES  |     | NULL    |       |
| 　n   | bigint(20)   | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

• TINYINT有符號數和無符號數的取值範圍分別為-128~127和0~255，由於負號占了一個數字位元，因此TINYINT預設的顯示寬度為4。同理，其他整數類型的預設顯示寬度與其有符號數的最小值的寬度相同。

• 舉例:

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);

mysql> SELECT * FROM test_int2;
+--------+--------+--------+
| f1     | f2     | f3     |
+--------+--------+--------+
|      1 |    123 |  00123 |
| 123456 | 123456 |   NULL |
| 123456 | 123456 | 123456 |
+--------+--------+--------+
3 rows in set (0.00 sec)

ZEROFILL

• ZEROFILL:0填充,（如果某列是ZEROFILL，那麼MySQL會自動為當前列添加UNSIGNED屬性），如果指定了ZEROFILL只是表示不夠M位時，用0在左邊填充，如果超過M位元，只要不超過資料存儲範圍即可。
• 原來，在 int(M) 中，M 的值跟 int(M) 所占多少存儲空間並無任何關係。 int(3)、int(4)、int(8) 在磁片上都是佔用 4 bytes 的存儲空間。
• 也就是說，**int(M)，必須和UNSIGNED ZEROFILL一起使用才有意義。**如果整數值超過M位元，就按照實際位元數存儲。只是無須再用字符 0 進行填充。

適用場景

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

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

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

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

• BIGINT:只有當你處理特別巨大的整數時才會用到。比如雙十一的交易量、大型門戶網站點擊量、證券公司衍生產品持倉等。

如何選擇？

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

• 舉個例子，商品編號採用的資料類型是 INT。原因就在於，客戶門店中流通的商品種類較多，而且，每天都有舊商品下架，新商品上架，這樣不斷反覆運算，日積月累。

• 如果使用 SMALLINT 類型，雖然佔用字節數比 INT 類型的整數少，但是卻不能保證資料不會超出範圍 65535。相反，使用 INT，就能確保有足夠大的取值範圍，不用擔心資料超出範圍影響可靠性的問題。

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

浮點類型

實際開發幾乎沒在用這個，都是用DECIMAL

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

• FLOAT 表示單精確度浮點數

• DOUBLE 表示雙精度浮點數

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

SET sql_mode = "REAL_AS_FLOAT";

• **問題1:**FLOAT 和 DOUBLE 這兩種資料類型的區別是啥呢？

  • FLOAT 佔用字節數少，取值範圍小；DOUBLE 佔用字節數多，取值範圍也大。

• **問題2:**為什麼浮點數類型的無符號數取值範圍，只相當於有符號數取值範圍的一半，也就是只相當於有符號數取值範圍大於等於零的部分呢？

  • MySQL 存儲浮點數的格式為:符號(S)、尾數(M)和 階碼(E)。因此，無論有沒有符號，MySQL 的浮點數都會存儲表示符號的部分。因此， 所謂的無符號數取值範圍，其實就是有符號數取值範圍大於等於零的部分。
  • 白話:UNSIGNED對於浮點數沒意義，不會讓儲存空間多一位

資料精度說明

接近廢棄的用法，過眼即可

對於浮點類型，在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也不推薦使用了，將來也可能被移除。

• 舉例

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;

精度誤差說明

• 浮點數類型有個缺陷，就是不精准。下面我來重點解釋一下為什麼 MySQL 的浮點數不夠精准。
• 比如，我們設計一個表，有f1這個欄位，插入值分別為0.47,0.44,0.19，我們期待的運行結果是:0.47 + 0.44 + 0.19 = 1.1。而使用sum之後查詢:

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)

• 查詢結果是 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) 的方式表示高精度小數。其中，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 定點數

  • 浮點數相對於定點數的優點是在長度一定的情況下，浮點類型取值範圍大，但是不精准，適用於需要取值範圍大，又可以容忍微小誤差的科學計算場景（比如計算化學、分子建模、流體動力學等）
  • 定點數類型取值範圍相對小，但是精准，沒有誤差，適合於對精度要求極高的場景 （比如涉及金額計算的場景）

• 舉例

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)

• 舉例，我們運行下面的語句，把test_double2表中欄位"f1"的資料類型修改為 DECIMAL(5,2):

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

• 然後，我們再一次運行求和語句:

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

位類型:BIT

基本沒用，過眼即可

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

• BIT類型，如果沒有指定(M)，預設是1位。這個1位元，表示只能存1位元的二進位值。這裡(M)是表示二進位的位元數，位元數最小值為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類型的欄位中插入資料時，一定要確保插入的資料在BIT類型支援的範圍內。

• 使用SELECT命令查詢位欄位時，可以用BIN()或HEX()函數進行讀取。

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查詢資料時，可以直接查詢出存儲的十進位數字據的值。

日期與時間類型

• 日期與時間是重要的資訊，在我們的系統中，幾乎所有的資料表都用得到。原因是客戶需要知道資料的時間標籤，從而進行資料查詢、統計和處理。

• MySQL有多種表示日期和時間的資料類型，不同的版本可能有所差異，MySQL8.0版本支持的日期和時間類型主要有:YEAR類型、TIME類型、DATE類型、DATETIME類型和TIMESTAMP類型。

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

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

YEAR類型

注意2位的YY有坑；注意年份最小值，要是西元999年之類的也會坑

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年。

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

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

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)

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

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)

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()函數，會插入當前系統的日期。

舉例:

創建資料表，表中只包含一個DATE類型的欄位f1。

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

插入資料:

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;

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()，會插入當前系統的時間。

舉例:

創建資料表，表中包含一個TIME類型的欄位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;

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類型的欄位插入系統的當前日期和時間。

舉例:

創建資料表，表中包含一個DATETIME類型的欄位dt。

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

插入資料:

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());

TIMESTAMP類型

TIMESTAMP類型也可以表示日期時間，其顯示格式與DATETIME類型相同，都是YYYY-MM-DD HH: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類型的欄位ts。

CREATE TABLE test_timestamp1(
ts TIMESTAMP
);

插入資料:

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的區別:

• TIMESTAMP存儲空間比較小，表示的日期時間範圍也比較小

• 底層存儲方式不同，TIMESTAMP底層存儲的是毫秒值，距離1970-1-1 0:0:0 0毫秒的毫秒值。

• 兩個日期比較大小或日期計算時，TIMESTAMP更方便、更快。

• TIMESTAMP和時區有關。TIMESTAMP會根據使用者的時區不同，顯示不同的結果。而DATETIME則只能反映出插入時當地的時區，其他時區的人查看資料必然會有誤差的。

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)

#修改當前的時區
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)

開發中經驗

用得最多的日期時間類型，就是 DATETIME。雖然 MySQL 也支持 YEAR（年）、 TIME（時間）、DATE（日期），以及 TIMESTAMP 類型，但是在實際項目中，儘量用 DATETIME 類型。因為這個資料類型包括了完整的日期和時間資訊，取值範圍也最大，使用起來比較方便。畢竟，如果日期時間資訊分散在好幾個欄位，很不容易記，而且查詢的時候，SQL 語句也會更加複雜。

此外，一般存註冊時間、商品發佈時間等，不建議使用DATETIME存儲，而是使用時間戳記，因為DATETIME雖然直觀，但不便於計算。

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

文本字串類型

• 在實際的項目中，我們還經常遇到一種資料，就是字串資料。

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

CHAR與VARCHAR類型

CHAR和VARCHAR類型都可以存儲比較短的字串。

CHAR類型:

• CHAR(M) 類型一般需要預先定義字串長度。如果不指定(M)，則表示長度預設是1個字符。
• 如果保存時，資料的實際長度比CHAR型別宣告的長度小，則會在右側填充空格以達到指定的長度。當MySQL檢索CHAR類型的資料時，CHAR類型的欄位會去除尾部的空格。
• 定義CHAR類型欄位時，聲明的欄位長度即為CHAR類型欄位所占的存儲空間的字節數。
  • 個人實測存儲空間要看編碼的，比如在utf8mb4編碼之下的char(1)可以存進’a’也能存進’中’，顯然’中’字不是1byte能存的下
  • 把M都看做字符考慮編碼再算它的占用空間比較妥當

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類型:

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

CREATE TABLE test_varchar1(
NAME VARCHAR  #錯誤 必須指定長度M
);

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

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教育');

開發中經驗

• 情況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和資料存儲總量比較好。

• 結論:用VARCHAR

TEXT類型

極少用，過眼即可

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

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

• 每種TEXT類型保存的資料長度和所佔用的存儲空間不同，如下:

• 由於實際存儲的長度不確定，MySQL 不允許 TEXT 類型的欄位做主鍵。遇到這種情況，你只能採用 CHAR(M)，或者 VARCHAR(M)。
• 舉例:

-- 創建資料表:
CREATE TABLE test_text(
tx TEXT
);

INSERT INTO test_text
VALUES('atguigu   ');

SELECT CHAR_LENGTH(tx)
FROM test_text; #10

• 說明在保存和查詢資料時，並沒有刪除TEXT類型的資料尾部的空格。

開發中經驗

TEXT文本類型，可以存比較大的文本段，搜索速度稍慢，因此如果不是特別大的內容，建議使用CHAR，VARCHAR來代替。還有TEXT類型不用加預設值，加了也沒用。而且text和blob類型的資料刪除後容易導致"空洞"，使得檔碎片比較多，所以頻繁使用的表不建議包含TEXT類型欄位，建議單獨分出去，單獨用一個表。

ENUM類型

創建欄位時就定義好能包含的元素，類似約束的作用

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

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

• 當ENUM類型包含1～255個成員時，需要1個字節的存儲空間；

• 當ENUM類型包含256～65535個成員時，需要2個字節的存儲空間。

• ENUM類型的成員個數的上限為65535個。

• 舉例:

-- 創建表如下:
CREATE TABLE test_enum(
season ENUM('春','夏','秋','冬','unknow')
);
-- 表示我這欄位只能放入這些:'春','夏','秋','冬','unknow'

-- 添加數據:
INSERT INTO test_enum
VALUES('春'),('秋'); -- 報錯，一次只能放一個

# 忽略大小寫
INSERT INTO test_enum
VALUES('UNKNOW');

# 允許按照角標的方式獲取指定索引位置的枚舉值
INSERT INTO test_enum
VALUES('1'),(3);

# Data truncated for column 'season' at row 1
INSERT INTO test_enum
VALUES('ab');

# 當ENUM類型的欄位沒有聲明為NOT NULL時，插入NULL也是有效的
INSERT INTO test_enum
VALUES(NULL);

SET類型

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

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

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

• 舉例:

-- 創建表:
CREATE TABLE test_set(
s SET ('A', 'B', 'C')
);

-- 向表中插入資料:
INSERT INTO test_set (s) VALUES ('A'), ('A,B');

#插入重複的SET類型成員時，MySQL會自動刪除重複的成員
INSERT INTO test_set (s) VALUES ('A,B,C,A');

#向SET類型的欄位插入SET成員中不存在的值時，MySQL會拋出錯誤。
INSERT INTO test_set (s) VALUES ('A,B,C,D');

SELECT *
FROM test_set;

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

INSERT INTO temp_mul VALUES('男','睡覺,打豆豆'); #成功

# Data truncated for column 'gender' at row 1
INSERT INTO temp_mul VALUES('男,女','睡覺,寫代碼'); #失敗

# Data truncated for column 'gender' at row 1
INSERT INTO temp_mul VALUES('妖','睡覺,寫代碼');#失敗


INSERT INTO temp_mul VALUES('男','睡覺,寫代碼,吃飯'); #成功

二進位字符串類型

極少用，過眼即可

• 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)，否則報錯。

• 舉例:

-- 創建表:
CREATE TABLE test_binary1(
f1 BINARY,
f2 BINARY(3),
# f3 VARBINARY,
f4 VARBINARY(10)
);

-- 添加數據:
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類型

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

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

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

• 舉例:

CREATE TABLE test_blob1(
id INT,
img MEDIUMBLOB
);

開發中經驗

在使用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類型的資料更加方便和高效。

-- 創建資料表，表中包含一個JSON類型的欄位 js 。
CREATE TABLE test_json(
js json

);

-- 向表中插入JSON資料。
INSERT INTO test_json (js)
VALUES ('{"name":"songhk", "age":18, "address":{"province":"beijing", "city":"beijing"}}');

-- 查詢t19表中的資料。
mysql> SELECT *
    -> FROM test_json;

• 當需要檢索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 row in set (0.00 sec)
-- 通過"->"和"->>"符號，從JSON欄位中正確查詢出了指定的JSON資料的值。

空間類型

太高端，了解一下就好

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開發手冊》規範:

阿裡巴巴MySQL資料庫規範

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

上次修改於 2021-12-23