不全？MySQL数据类型精讲，定点日期枚举文本字符串，json二进制，空间，选择建议，完整详细可收藏
文章目錄
- 1. MySQL中的數據類型
- 2. 整數類型
- 3. 浮點數類型
- 4. 定點數類型
- 5. 位類型：BIT
- 6. 日期與時間類型
- 7. 文本字符串類型
- 8. ENUM類型
- 9. SET類型
- 10. 二進制字符串類型
- 11. JSON 類型
- 12. 空間類型
- 13. 選擇建議
1. MySQL中的數據類型
常見數據類型的屬性，如下：
2. 整數類型
2.1 類型介紹
整數類型一共有 5 種，包括 TINYINT、SMALLINT、MEDIUMINT、INT（INTEGER）和 BIGINT。它們的區別如下表所示：
2.2 可選屬性
整數類型的可選屬性有三個：
2.2.1 M
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)2.2.2 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)2.2.3 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 進行填充。
2.3 適用場景
①TINYINT ：一般用于枚舉數據，比如系統設定取值范圍很小且固定的場景。
②SMALLINT ：用于較小范圍的統計數據，如統計工廠的固定資產庫存數量等。
③MEDIUMINT ：用于較大整數的計算，比如車站每日的客流量等。
④INT、INTEGER ：取值范圍足夠大，一般情況下不用考慮超限問題，用得最多，比如商品編號。
⑤BIGINT ：只有當你處理特別巨大的整數時才會用到。比如雙十一的交易量、大型門戶網站點擊量、證券公司衍生產品持倉等。
2.4 如何選擇？
在評估用哪種整數類型的時候，你需要考慮 存儲空間 和 可靠性 的平衡問題：一方 面，用占用字節數少的整數類型可以節省存儲空間；另一方面，要是為了節省存儲空間， 使用的整數類型取值范圍太小，一旦遇到超出取值范圍的情況，就可能引起 系統錯誤 ，影響可靠性。舉個例子，商品編號采用的數據類型是 INT。原因就在于，客戶門店中流通的商品種類較多，而且，每天都有舊商品下架，新商品上架，這樣不斷迭代，日積月累。如果使用 SMALLINT 類型，雖然占用字節數比 INT 類型的整數少，但是卻不能保證數據不會超出范圍65535。相反，使用 INT，就能確保有足夠大的取值范圍，不用擔心數據超出范圍影響可靠性的問題。要注意的是，在實際工作中，系統故障產生的成本遠遠超過增加幾個字段存儲空間所產生的成本。因此，建議首先確保數據不會超過取值范圍，在這個前提之下，再去考慮如何節省存儲空間。
3. 浮點數類型
3.1 類型介紹
浮點數和定點數類型的特點是可以 處理小數 ，你可以把整數看成小數的一個特例。因此，浮點數和定點數的使用場景，比整數大多了。 MySQL支持的浮點數類型，分別是 FLOAT、DOUBLE、REAL。
①FLOAT 表示單精度浮點數；
②DOUBLE 表示雙精度浮點數；
③REAL默認就是 DOUBLE。如果把 SQL 模式設定為啟用“ REAL_AS_FLOAT ”，那么，MySQL 就認為REAL 是 FLOAT。如果要啟用“REAL_AS_FLOAT”，可以通過以下 SQL 語句實現：
為什么浮點數類型的無符號數取值范圍，只相當于有符號數取值范圍的一半，也就是只相當于有符號數取值范圍大于等于零的部分呢？
答案：MySQL 存儲浮點數的格式為： 符號(S) 、 尾數(M) 和 階碼(E) 。因此，無論有沒有符號，MySQL 的浮點數都會存儲表示符號的部分。因此， 所謂的無符號數取值范圍，其實就是有符號數取值范圍大于等于零的部分。
3.2 數據精度說明
對于浮點類型，在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;3.3 精度誤差說明
浮點數類型有個缺陷，就是不精準。下面我來重點解釋一下為什么 MySQL 的浮點數不夠精準。比如，一個表中有f1這個字段，插入值分別為0.47,0.44,0.19，我們期待的運行結果是：0.47 + 0.44 + 0.19 = 1.1。而使用sum之后查詢：
為什么會存在這樣的誤差呢？問題還是出在 MySQL 對浮點類型數據的存儲方式上。MySQL 用 4 個字節存儲 FLOAT 類型數據，用 8 個字節來存儲 DOUBLE 類型數據。無論哪個，都是采用二進制的方式來進行存儲的。比如 9.625，用二進制來表達，就是 1001.101，或者表達成 1.001101×2^3。如果尾數不是 0 或 5（比如 9.624），你就無法用一個二進制數來精確表達。進而，就只好在取值允許的范圍內進行四舍五入。
在編程中，如果用到浮點數，要特別注意誤差問題，因為浮點數是不準確的，所以我們要避免使用“=”來判斷兩個數是否相等。同時，在一些對精確度要求較高的項目中，千萬不要使用浮點數，不然會導致結果錯誤，甚至是造成不可挽回的損失。
4. 定點數類型
4.1 類型介紹
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);mysql> SELECT * FROM test_decimal1; +------+--------+ | f1 | f2 | +------+--------+ | 123 | 123.46 | +------+--------+ 1 row in set (0.00 sec)5. 位類型：BIT
BIT類型中存儲的是二進制值，類似010110。
BIT類型，如果沒有指定(M)，默認是1位。這個1位，表示只能存1位的二進制值。這里(M)是表示二進制的位數，位數最小值為1，最大值為64。
使用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)6. 日期與時間類型
日期與時間是重要的信息，在我們的系統中，幾乎所有的數據表都用得到。原因是客戶需要知道數據的時間標簽，從而進行數據查詢、統計和處理。
MySQL有多種表示日期和時間的數據類型，不同的版本可能有所差異，MySQL8.0版本支持的日期和時間類型主要有：YEAR類型、TIME類型、DATE類型、DATETIME類型和TIMESTAMP類型。
6.1 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年。
從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)6.2 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 );插入數據：
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類型
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 );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類型
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-DDHH: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 );插入數據：
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類型
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則只能反映出插入時當地的時區，其他時區的人查看數據必然會有誤差的。
6.6 開發中經驗
用得最多的日期時間類型，就是 DATETIME 。雖然 MySQL 也支持YEAR（年）、 TIME（時間）、DATE（日期），以及 TIMESTAMP 類型，但是在實際項目中，盡量用 DATETIME 類型。因為這個數據類型包括了完整的日期和時間信息，取值范圍也最大，使用起來比較方便。畢竟，如果日期時間信息分散在
好幾個字段，很不容易記，而且查詢的時候，SQL 語句也會更加復雜。此外，一般存注冊時間、商品發布時間等，不建議使用DATETIME存儲，而是使用時間戳，因為DATETIME雖然直觀，但不便于計算。
7. 文本字符串類型
MySQL中，文本字符串總體上分為CHAR 、VARCHAR 、TINYTEXT 、TEXT 、MEDIUMTEXT 、LONGTEXT 、 ENUM 、 SET 等類型。
7.1 CHAR與VARCHAR類型
CHAR和VARCHAR類型都可以存儲比較短的字符串。
CHAR類型：
①CHAR(M) 類型一般需要預先定義字符串長度。如果不指定(M)，則表示長度默認是1個字符。
②如果保存時，數據的實際長度比CHAR類型聲明的長度小，則會在 右側填充 空格以達到指定的長度。當MySQL檢索CHAR類型的數據時，CHAR類型的字段會去除尾部的空格。
③定義CHAR類型字段時，聲明的字段長度即為CHAR類型字段所占的存儲空間的字節數。
VARCHAR類型：
①VARCHAR(M) 定義時， 必須指定 長度M，否則報錯。
②MySQL4.0版本以下，varchar(20)：指的是20字節，如果存放UTF8漢字時，只能存6個（每個漢字3字節）；MySQL5.0版本以上，varchar(20)：指的是20字符。
③檢索VARCHAR類型的字段數據時，會保留數據尾部的空格。VARCHAR類型的字段所占用的存儲空間為字符串實際長度加1個字節。
哪些情況使用 CHAR 或 VARCHAR 更好？
情況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和數據存儲總量比較好。
7.2 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類型字段，建議單獨分出去，單獨用一個表。
8. ENUM類型
ENUM類型也叫作枚舉類型，ENUM類型的取值范圍需要在定義字段時進行指定。設置字段值時，ENUM類型只允許從成員中選取單個值，不能一次選取多個值。
其所需要的存儲空間由定義ENUM類型時指定的成員個數決定。
當ENUM類型包含1～255個成員時，需要1個字節的存儲空間；
當ENUM類型包含256～65535個成員時，需要2個字節的存儲空間；
ENUM類型的成員個數的上限為65535個。
舉例：創建表如下：
CREATE TABLE test_enum( season ENUM('春','夏','秋','冬','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);9. 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('男','睡覺,打豆豆'); #成功INSERT INTO temp_mul VALUES('男,女','睡覺,寫代碼'); #失敗INSERT INTO temp_mul VALUES('妖','睡覺,寫代碼');#失敗INSERT INTO temp_mul VALUES('男','睡覺,寫代碼,吃飯'); #成功10. 二進制字符串類型
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) ，否則報錯。
舉例：創建表：
添加數據：
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中。
舉例：
TEXT和BLOB的使用注意事項：
在使用text和blob字段類型時要注意以下幾點，以便更好的發揮數據庫的性能。
① BLOB和TEXT值也會引起自己的一些問題，特別是執行了大量的刪除或更新操作的時候。刪除這種值會在數據表中留下很大的" 空洞 "，以后填入這些"空洞"的記錄可能長度不同。為了提高性能，建議定期使用 OPTIMIZE TABLE 功能對這類表進行碎片整理 。
② 如果需要對大文本字段進行模糊查詢，MySQL 提供了前綴索引 。但是仍然要在不必要的時候避免檢索大型的BLOB或TEXT值。例如，SELECT * 查詢就不是很好的想法，除非能夠確定作為約束條件的WHERE子句只會找到所需要的數據行。否則，可能毫無目的地在網絡上傳輸大量的值。
③ 把BLOB或TEXT列分離到單獨的表中。在某些環境中，如果把這些數據列移動到第二張數據表中，可以把原數據表中的數據列轉換為固定長度的數據行格式，那么它就是有意義的。這會減少主表中的碎片 ，使得到固定長度數據行的性能優勢。它還使你在主數據表上運行 SELECT * 查詢的時候不會通過網絡傳輸大量的BLOB或TEXT值。
11. 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數據的值。
12. 空間類型
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.21334231.234532)，POINT(30 10)，坐標值支持DECIMAL類型，經度（longitude）在前，維度（latitude）在后，用空格分隔。
②LineString，線，由一系列點連接而成。如果線從頭至尾沒有交叉，那就是簡單的（simple）；如果起點和終點重疊，那就是封閉的（closed）。例如LINESTRING(30 10,10 30,4040)，點與點之間用逗號分隔，一個點中的經緯度用空格分隔，與POINT格式一致。
③Polygon，多邊形。可以是一個實心平面形，即沒有內部邊界，也可以有空洞，類似紐扣。最簡單的就是只有一個外邊界的情況，例如POLYGON((0 0,10 0,10 10, 0 10))。
下面展示幾種常見的幾何圖形元素：
MultiPoint、MultiLineString、MultiPolygon、GeometryCollection 這4種類型都是集合類，是多個Point、LineString或Polygon組合而成。
下面展示的是多個同類或異類幾何圖形元素的組合：
13. 選擇建議
在定義數據類型時，如果確定是整數 ，就用 INT ； 如果是小數 ，一定用定點數類型DECIMAL(M,D) ； 如果是日期與時間，就用 DATETIME 。這樣做的好處是，首先確保系統不會因為數據類型定義出錯。不過，凡事都是有兩面的，可靠性好，并不意味著高效。比如，TEXT 雖然使用方便，但是效率不如 CHAR(M) 和VARCHAR(M)。
關于字符串的選擇，建議參考如下阿里巴巴的《Java開發手冊》規范：
任何字段如果為非負數，必須是 UNSIGNED
【 強制 】小數類型為 DECIMAL，禁止使用 FLOAT 和 DOUBLE。
說明：在存儲的時候，FLOAT 和 DOUBLE 都存在精度損失的問題，很可能在比較值的時候，得到不正確的結果。如果存儲的數據范圍超過 DECIMAL 的范圍，建議將數據拆成整數和小數并分開存儲。
【 強制 】如果存儲的字符串長度幾乎相等，使用 CHAR 定長字符串類型。
【 強制 】VARCHAR 是可變長字符串，不預先分配存儲空間，長度不要超過 5000。如果存儲長度大于此值，定義字段類型為 TEXT，獨立出來一張表，用主鍵來對應，避免影響其它字段索引效率。
總結
以上是生活随笔為你收集整理的不全？MySQL数据类型精讲，定点日期枚举文本字符串，json二进制，空间，选择建议，完整详细可收藏的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 无限精度运算
- 下一篇： 华为路由器如何设置访问权限如何在路由器上