共計 7468 個字符，預計需要花費 19 分鐘才能閱讀完成。
丸趣 TV 小編給大家分享一下 MySQL 數(shù)據(jù)類型和存儲機制的示例分析，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！
1.1 數(shù)據(jù)類型概覽
數(shù)據(jù)類型算是一種字段約束，它限制每個字段能存儲什么樣的數(shù)據(jù)、能存儲多少數(shù)據(jù)、能存儲的格式等。MySQL/MariaDB 大致有 5 類數(shù)據(jù)類型，分別是：整形、浮點型、字符串類型、日期時間型以及特殊的 ENUM 和 SET 類型。
這 5 種數(shù)據(jù)類型的意義、限制和相關說明如下圖所示：
各數(shù)據(jù)類型占用字節(jié)數(shù)，參見 mariadb 官方手冊。
1.2 存儲機制和操作方式
數(shù)據(jù)類型之所以能限定字段的數(shù)據(jù)存儲長度，是因為在創(chuàng)建表時在內存中嚴格劃定了地址空間，地址空間的長度是多少就能存儲多少字節(jié)的數(shù)據(jù)。當然，這是一個很粗獷的概念，更具體的存儲方式見下面的描述。
數(shù)據(jù)類型限定范圍的方式有兩種：一是嚴格限定空間，劃分了多少空間就只能存儲多少數(shù)據(jù)，超出的數(shù)據(jù)將被切斷；二是使用額外的字節(jié)的 bit 位來標記某個地址空間的字節(jié)是否存儲了數(shù)據(jù)，存儲了就進行標記，不存儲就不標記。
1.2.1 整型的存儲方式
此處主要說明整型的存儲方式，至于浮點型數(shù)據(jù)類型的存儲方式要考慮的東西太多。
對于整型數(shù)據(jù)類型來說，它嚴格限定空間，但它和字符不同，因為每個已劃分的字節(jié)上的 bit 位上的 0 和 1 直接可以計算出數(shù)值，所以它的范圍是根據(jù) bit 位的數(shù)量值來計算的。一個字節(jié)有 8 個 Bit 位，這 8 個 bit 位可以構成 2^8=256 個數(shù)值，同理 2 字節(jié)的共 2^16=65536 個數(shù)值，4 字節(jié)的 int 占用 32bit，可以表示的范圍為 0 -2^32。也就是說，在 0 -255 之間的數(shù)字都只占用一個字節(jié)，256-65535 之間的數(shù)字需要占用兩個字節(jié)。
需要注意，在 MySQL/mariadb 中的整型數(shù)據(jù)類型可以使用參數(shù) M，M 是一個正整數(shù)，例如 INT(M)，tinyint(M)。這個 M 表示的是顯示長度，如 int(4)表示在輸出時將顯示 4 位整數(shù)，如果實際值的位數(shù)小于顯示值寬度，則默認使用空格填充在左邊。而結果位數(shù)超出時將不影響顯示結果。一般該功能都會配合 zerofill 屬性用 0 代替空格填充，但是使用了 zerofill 后，該列就會自動變成無符號字段。例如：
CREATE TABLE test3(id INT(2) ZEROFILL NOT NULL);
INSERT INTO test3 VALUES(1),(2),(11),(111);
SELECT id FROM test3;
+-----+
| id |
+-----+
| 01 |
| 02 |
| 11 |
| 111 |
+-----+
4 rows in set (0.00 sec)唯一需要注意的是，顯示寬度僅僅影響顯示效果，不影響存儲、比較、長度計算等等任何操作。
1.2.2 字符類型的存儲方式
此處主要說明 char 和 varchar 的存儲方式以及區(qū)別。
char 類型是常被稱為 定長字符串類型，它嚴格限定空間長度，但它限定的是字符數(shù)，而非字節(jié)數(shù)，但以前老版本中限定的是字節(jié)數(shù)。因此 char(M)嚴格存儲 M 個字符，不足部分使用空格補齊，超出 M 個字符的部分直接截斷。
由于 char 類型有 短了就使用空格補足 的能力，因此為了體現(xiàn)數(shù)據(jù)的真實性，在從地址空間中檢索數(shù)據(jù)時將自動刪除尾隨的空格部分。這正是 char 的一個特殊性，即使是我們手動存儲的尾隨空格也會被認為是自動補足的，于是在檢索時被刪除。也就是說在 where 語句中 name= gaoxiaofang 和 name= gaoxiaofang 的結果是一樣的。
例如：
create table test2(a char(4) charset utf8mb4);
insert into test2 values(恭喜你),(恭喜你成功晉級),(hello),( he
select concat(a, x) from test2;
+---------------+
| concat(a, x) |
+---------------+
| 恭喜你 x |
| 恭喜你成 x |
| hellx |
| hex |
+---------------+
4 rows in set從上面的結果可以看到，char(4)只能存儲 4 個字符，并刪除尾隨空格。
varchar 常被稱為 變長字符串類型，它存儲數(shù)據(jù)時使用額外的字節(jié)的 bit 位來標記某個字節(jié)是否存儲了數(shù)據(jù)。每存儲一個字節(jié) (不是字符) 占用一個 bit 位進行記錄，因此一個額外的字節(jié)可以標記共 256 個字節(jié)，2 個額外的字節(jié)可以標記 65536 個字節(jié)。但 MySQL/mariadb 限制了最大能存儲 65536 個字節(jié)。這表示，如果是單字節(jié)的字符，它最多能存儲 65536 個字符，如果是多字節(jié)字符，如 UTF8 的每個字符占用 3 個字節(jié)，它最多能存儲 65536/3=21845 個 utf8 字符。
因此，varchar(M)存儲時除了真實數(shù)據(jù)占用空間長度，還要額外計算 1 或 2 個字節(jié)的 Bit 位長度，即對于單字節(jié)字符實際占用的空間為 M + 1 或 M + 2 個字節(jié)，對于多字節(jié)字符 (如 3 字節(jié)) 實際占用的空間為 M *3+ 1 或 M *3+ 2 個字節(jié)。
由于 varchar 存儲時需要采用額外的 bit 位記錄每一個字節(jié)，短了的數(shù)據(jù)不會自動使用補齊，因此顯式存儲的尾隨空格也會被存儲并在 Bit 位上進行標記，也就是說不會刪除尾隨空格。
和 char(M)一樣，當指定 varchar(2)時，只能存儲兩個字節(jié)的字符，如果超出了，則切斷。
關于 char、varchar 以及 text 字符串類型，它們在比較時不會考慮尾隨空格，但做 like 匹配或正則匹配時會考慮空格，因為匹配時字符是精確的。例如：
create table test4(a char(4),b varchar(5));
insert into test4 values( ab , ab
select a= ab ,b= ab ,a=b from test4;
+-----------+--------------+-----+
| a= ab | b= ab | a=b |
+-----------+--------------+-----+
| 1 | 1 | 1 |
+-----------+--------------+-----+
1 row in set
select a like ab from test4;
+-------------------+
| a like ab |
+-------------------+
| 0 |
+-------------------+
1 row in set最后需要說明的是，數(shù)值在存儲 (或調入內存) 時，以數(shù)值型方式存儲比字符型或日期時間類型更節(jié)省空間。因為整數(shù)值存儲時是直接通過 bit 計算數(shù)值的，0-255 之間的任意整數(shù)都只占一個字節(jié)，256-65535 之間的任意整數(shù)都占 2 個字節(jié)，而占用 4 個字節(jié)時便可以代表幾十億個整數(shù)之間的任意一個，這顯然比字符型存儲時每個字符占用一個字節(jié)節(jié)省空間的多。例如值 100 存儲為字符型時占用三個字節(jié)，而存儲為數(shù)值型將只占用一個字節(jié)。因此數(shù)據(jù)庫默認將不使用引號包圍的值當作數(shù)值型，如果明確要存儲為字符型或日期時間型則應該使用引號包圍以避免歧義。
1.2.3 日期時間型的存儲方式
日期時間性數(shù)據(jù)存儲時需要使用引號包圍，避免和數(shù)值類型的數(shù)據(jù)產生歧義。關于日期時間的輸入方式是非常寬松的，以下幾種方式都是被允許的：任意允許的分隔符，建議使用 4 位的年份。
20110101
2011-01-01 18:40:20
2011/01/01 18-40-20
201101011840201.2.4 ENUM 數(shù)據(jù)類型
ENUM 數(shù)據(jù)類型是枚舉型。定義方式為 ENUM(value1 , value2 , value3 ,…)，在向該類型的字段中插入數(shù)據(jù)時只能插入 value 中的某一個或 NULL，插入其他值或空 (即) 時都將截斷為空數(shù)據(jù)。存儲時會忽略大小寫(將轉換為 ENUM 中的字符)，且會截斷尾隨空格。
mysql create table test6(id int auto_increment primary key,name char(20),gender enum( Mail , f
mysql insert into test6(name,gender) values(malongshuai , Mail),(gaoxiaofang , F),(wugui , x),(tuner ,null),( woniu ,
Query OK, 5 rows affected
Records: 5 Duplicates: 0 Warnings: 2
mysql show warnings;
+---------+------+---------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------+
| Warning | 1265 | Data truncated for column gender at row 3 |
| Warning | 1265 | Data truncated for column gender at row 5 |
+---------+------+---------------------------------------------+
2 rows in set
mysql select * from test6;
+----+-------------+--------+
| id | name | gender |
+----+-------------+--------+
| 1 | malongshuai | Mail |
| 2 | gaoxiaofang | f |
| 3 | wugui | |
| 4 | tuner | NULL |
| 5 | woniu | |
+----+-------------+--------+
5 rows in setENUM 類型的數(shù)據(jù)存儲時是通過 index 數(shù)值進行存儲的，相比于字符串類型，它只需要 1 或 2 個字節(jié)進行存儲即可。理論上，當 value 的數(shù)量少于 256 個時只需一個字節(jié)，超出 256 個但少于 65536 個時使用 2 個字節(jié)存儲。MySQL/MariaDB 限制最多只能存儲 65536 個 value。當然，這是理論上的限制，實際存儲時要考慮的因素有很多，例如 NULL 也會占用 bit 位，所以實際存儲時可能 250 個 value 就需要 2 個字節(jié)。
ENUM 的每個 value 都通過 index 號碼進行編號，無論是檢索還是操作該字段時都會通過 index 的值來操作。value1 的 index=1，value2 的 index=2，依次類推。但需要注意有兩個特殊的 index 值：NULL 值的 index=NULL，空數(shù)據(jù)的 index=0。
例如 ENUM(a , b , c)，向該字段依次插入 , b , a , c ,NULL, xxx 時，由于第一個和最后一個都會截斷為空數(shù)據(jù)，所以它們的 index 為 0，插入的 NULL 的 index 為 NULL，插入的 b , a , c 的 index 值分別為 2,1,3。所以 index 號碼和值的對應關系為：
indexvalueNULLNULL0 0 1 a 2 b 3 c
使用 ENUM 的 index 進行數(shù)據(jù)檢索：
mysql select * from test6 where gender=2;
+----+-------------+--------+
| id | name | gender |
+----+-------------+--------+
| 2 | gaoxiaofang | f |
+----+-------------+--------+
1 row in set特別建議，不要使用 ENUM 存儲數(shù)值，因為無論是排序還是檢索或其他操作，都是根據(jù) index 值作為條件的，這很容易產生誤解。例如，下面是用 ENUM 存儲兩個數(shù)值，然后進行檢索和排序操作。
mysql create table test7(id enum( 3 , 1 , 2
mysql insert into test7 values(1),(2),( 3
# 檢索時 id=2，但結果查出來卻為 1，因為 id= 2 的 2 是 enum 的 index 值，在 enum 中 index= 2 的值為 1
mysql select * from test7 where id=2;
+----+
| id |
+----+
| 1 |
+----+
1 row in set
# 按照 id 進行排序時，也是通過 index 大小進行排序的
mysql select * from test7 order by id asc;
+----+
| id |
+----+
| 3 |
| 1 |
| 2 |
+----+
3 rows in set因此，強烈建議不要在 ENUM 中存放數(shù)值，即使是浮點型數(shù)值也很容易出現(xiàn)歧義。
1.2.5 SET 數(shù)據(jù)類型
對于 SET 類型，和 enum 類似，不區(qū)分大小寫，存儲時刪除尾隨空格，null 也是有效值。但不同的是可以組合多個給出的值。如 set(a , b , c , d)可以存儲 a，b , d，b 等，多個成員之間使用逗號隔開。所以，使用多個成員的時候，成員本身的值中不能出現(xiàn)逗號。如果要存儲的內容不在 set 列表中，則截斷為空值。
SET 數(shù)據(jù)類型占用的空間大小和 SET 成員數(shù)量 M 有關，計算方式為(M+7)/ 8 取整。所以：1- 8 個成員占用 1 個字節(jié)；
9-16 個成員占用 2 個字節(jié)；17-24 個成員占用 3 字節(jié)；25-32 個成員占用 4 個字節(jié)；33-64 個成員占用 8 字節(jié)。MySQL/MariaDB 限制最多只能有 64 個成員。
存儲 SET 數(shù)據(jù)類型的數(shù)據(jù)時忽略重復成員并按照枚舉時的順序存儲。如 set(b , b , a)，存儲 a,b,a , b,a,b 的結果都是 b,a。
mysql create table test8(a set( d , b , a
mysql insert into test8 values(b,b,a),(b,a,b),( bab
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 1
mysql select * from test8;
+-----+
| a |
+-----+
| b,a |
| b,a |
| |
+-----+
3 rows in set使用 find_in_set(set_value,set_column_name)可以檢索出包含指定 set 值 set_value 的行。例如檢索 a 字段中包含成員 b 的行：
mysql select * from test8 where find_in_set(b ,a);
+-----+
| a |
+-----+
| b,a |
| b,a |
+-----+
2 rows in set1.3 數(shù)據(jù)類型屬性：unsigned
unsigned 屬性就是讓數(shù)值類型的數(shù)據(jù)變得無符號化。使用 unsigned 屬性將會改變數(shù)值數(shù)據(jù)類型的范圍，例如 tinyint 類型帶符號的范圍是 -128 到 127，而使用 unsigned 時范圍將變成 0 到 255。同時 unsigned 也會限制該列不能插入負數(shù)值。
create table t(a int unsigned,b int unsigned);
insert into t select 1,2;
insert into t select -1,-2;上面的語句中，在執(zhí)行第二條語句準備插入負數(shù)時將會報錯，提示超出范圍。
使用 unsigned 在某些情況下確有其作用，例如一般的 ID 主鍵列不會允許使用負數(shù)，它相當于實現(xiàn)了一個 check 約束。但是使用 unsigned 有時候也會出現(xiàn)些不可預料的問題：在進行數(shù)值運算時如果得到負數(shù)將會報錯。例如上面的表 t 中，字段 a 和 b 都是無符號的列，且有一行 a =1,b=2。
mysql select * from t;
+---+---+
| a | b |
+---+---+
| 1 | 2 |
+---+---+
1 row in set此時如果計算 a - b 將會出錯，不僅如此，只要是 unsigned 列參與計算并將得到負數(shù)都會出錯。
mysql select a-b from t;
1690 - BIGINT UNSIGNED value is out of range in (`test`.`t`.`a` - `test`.`t`.`b`)
mysql select a-2 from t;
1690 - BIGINT UNSIGNED value is out of range in (`test`.`t`.`a` - 2)如果計算結果不是負數(shù)時將沒有影響。
mysql select 2-a,a*3 from t;
+-----+-----+
| 2-a | a*3 |
+-----+-----+
| 1 | 3 |
+-----+-----+
1 row in set這并不是 MySQL/MariaDB 中的 bug，在 C 語言中的 unsigned 也一樣有類似的問題。這個問題在 MySQL/MariaDB 中設置 set sql_mode= no_unsigned_subtraction 即可解決。
所以個人建議不要使用 unsigned 屬性修飾字段。
1.4 數(shù)據(jù)類型屬性：zerofill
zerofill 修飾字段后，不足字段顯示部分將使用 0 來代替空格填充，啟用 zerofill 后將自動設置 unsigned。zerofill 一般只在設置了列的顯示寬度后一起使用。關于列的顯示寬度在上文已經介紹過了。
mysql create table t1(id int(4) zerofill);
mysql select * from t1;
+-------+
| id |
+-------+
| 0001 |
| 0002 |
| 0011 |
| 83838 |
+-------+
4 rows in set (0.00 sec)zerofill 只是修飾顯示結果，不會影響存儲的數(shù)據(jù)值。
以上是“MySQL 數(shù)據(jù)類型和存儲機制的示例分析”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注丸趣 TV 行業(yè)資訊頻道！
