1.1 數(shù)據(jù)類型概覽

數(shù)據(jù)類型算是一種字段約束，它限制每個(gè)字段能存儲(chǔ)什么樣的數(shù)據(jù)、能存儲(chǔ)多少數(shù)據(jù)、能存儲(chǔ)的格式等。MySQL/MariaDB大致有5類數(shù)據(jù)類型，分別是：整形、浮點(diǎn)型、字符串類型、日期時(shí)間型以及特殊的ENUM和SET類型。

這5種數(shù)據(jù)類型的意義、限制和相關(guān)說明如下圖所示：

各數(shù)據(jù)類型占用字節(jié)數(shù)，參見mariadb官方手冊(cè)。

1.2 存儲(chǔ)機(jī)制和操作方式

數(shù)據(jù)類型之所以能限定字段的數(shù)據(jù)存儲(chǔ)長(zhǎng)度，是因?yàn)樵趧?chuàng)建表時(shí)在內(nèi)存中嚴(yán)格劃定了地址空間，地址空間的長(zhǎng)度是多少就能存儲(chǔ)多少字節(jié)的數(shù)據(jù)。當(dāng)然，這是一個(gè)很粗獷的概念，更具體的存儲(chǔ)方式見下面的描述。

數(shù)據(jù)類型限定范圍的方式有兩種：一是嚴(yán)格限定空間，劃分了多少空間就只能存儲(chǔ)多少數(shù)據(jù)，超出的數(shù)據(jù)將被切斷；二是使用額外的字節(jié)的bit位來標(biāo)記某個(gè)地址空間的字節(jié)是否存儲(chǔ)了數(shù)據(jù)，存儲(chǔ)了就進(jìn)行標(biāo)記，不存儲(chǔ)就不標(biāo)記。

1.2.1 整型的存儲(chǔ)方式

此處主要說明整型的存儲(chǔ)方式，至于浮點(diǎn)型數(shù)據(jù)類型的存儲(chǔ)方式要考慮的東西太多。

對(duì)于整型數(shù)據(jù)類型來說，它嚴(yán)格限定空間，但它和字符不同，因?yàn)槊總€(gè)已劃分的字節(jié)上的bit位上的0和1直接可以計(jì)算出數(shù)值，所以它的范圍是根據(jù)bit位的數(shù)量值來計(jì)算的。一個(gè)字節(jié)有8個(gè)Bit位，這8個(gè)bit位可以構(gòu)成2^8=256個(gè)數(shù)值，同理2字節(jié)的共2^16=65536個(gè)數(shù)值，4字節(jié)的int占用32bit，可以表示的范圍為0-2^32。也就是說，在0-255之間的數(shù)字都只占用一個(gè)字節(jié)，256-65535之間的數(shù)字需要占用兩個(gè)字節(jié)。

需要注意，在MySQL/mariadb中的整型數(shù)據(jù)類型可以使用參數(shù)M，M是一個(gè)正整數(shù)，例如INT(M)，tinyint(M)。這個(gè)M表示的是顯示長(zhǎng)度，如int(4)表示在輸出時(shí)將顯示4位整數(shù)，如果實(shí)際值的位數(shù)小于顯示值寬度，則默認(rèn)使用空格填充在左邊。而結(jié)果位數(shù)超出時(shí)將不影響顯示結(jié)果。一般該功能都會(huì)配合zerofill屬性用0代替空格填充，但是使用了zerofill后，該列就會(huì)自動(dòng)變成無符號(hào)字段。例如：

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)

唯一需要注意的是，顯示寬度僅僅影響顯示效果，不影響存儲(chǔ)、比較、長(zhǎng)度計(jì)算等等任何操作。

1.2.2 字符類型的存儲(chǔ)方式

此處主要說明char和varchar的存儲(chǔ)方式以及區(qū)別。

char類型是常被稱為"定長(zhǎng)字符串類型"，它嚴(yán)格限定空間長(zhǎng)度，但它限定的是字符數(shù)，而非字節(jié)數(shù)，但以前老版本中限定的是字節(jié)數(shù)。因此char(M)嚴(yán)格存儲(chǔ)M個(gè)字符，不足部分使用空格補(bǔ)齊，超出M個(gè)字符的部分直接截?cái)唷?/p>

由于char類型有"短了就使用空格補(bǔ)足"的能力，因此為了體現(xiàn)數(shù)據(jù)的真實(shí)性，在從地址空間中檢索數(shù)據(jù)時(shí)將自動(dòng)刪除尾隨的空格部分。這正是char的一個(gè)特殊性，即使是我們手動(dòng)存儲(chǔ)的尾隨空格也會(huì)被認(rèn)為是自動(dòng)補(bǔ)足的，于是在檢索時(shí)被刪除。也就是說在where語句中name='gaoxiaofang '和name='gaoxiaofang'的結(jié)果是一樣的。

例如：

create table test2(a char(4) charset utf8mb4);
insert into test2 values('恭喜你'),('恭喜你成功晉級(jí)'),('hello'),('he ');
select concat(a,'x') from test2;
+---------------+
| concat(a,'x') |
+---------------+
| 恭喜你x |
| 恭喜你成x |
| hellx  |
| hex  |
+---------------+
4 rows in set

從上面的結(jié)果可以看到，char(4)只能存儲(chǔ)4個(gè)字符，并刪除尾隨空格。

varchar常被稱為"變長(zhǎng)字符串類型"，它存儲(chǔ)數(shù)據(jù)時(shí)使用額外的字節(jié)的bit位來標(biāo)記某個(gè)字節(jié)是否存儲(chǔ)了數(shù)據(jù)。每存儲(chǔ)一個(gè)字節(jié)(不是字符)占用一個(gè)bit位進(jìn)行記錄，因此一個(gè)額外的字節(jié)可以標(biāo)記共256個(gè)字節(jié)，2個(gè)額外的字節(jié)可以標(biāo)記65536個(gè)字節(jié)。但MySQL/mariadb限制了最大能存儲(chǔ)65536個(gè)字節(jié)。這表示，如果是單字節(jié)的字符，它最多能存儲(chǔ)65536個(gè)字符，如果是多字節(jié)字符，如UTF8的每個(gè)字符占用3個(gè)字節(jié)，它最多能存儲(chǔ)65536/3=21845個(gè)utf8字符。

因此，varchar(M)存儲(chǔ)時(shí)除了真實(shí)數(shù)據(jù)占用空間長(zhǎng)度，還要額外計(jì)算1或2個(gè)字節(jié)的Bit位長(zhǎng)度，即對(duì)于單字節(jié)字符實(shí)際占用的空間為M+1或M+2個(gè)字節(jié)，對(duì)于多字節(jié)字符(如3字節(jié))實(shí)際占用的空間為M*3+1或M*3+2個(gè)字節(jié)。

由于varchar存儲(chǔ)時(shí)需要采用額外的bit位記錄每一個(gè)字節(jié)，短了的數(shù)據(jù)不會(huì)自動(dòng)使用補(bǔ)齊，因此顯式存儲(chǔ)的尾隨空格也會(huì)被存儲(chǔ)并在Bit位上進(jìn)行標(biāo)記，也就是說不會(huì)刪除尾隨空格。

和char(M)一樣，當(dāng)指定varchar(2)時(shí)，只能存儲(chǔ)兩個(gè)字節(jié)的字符，如果超出了，則切斷。

關(guān)于char、varchar以及text字符串類型，它們?cè)诒容^時(shí)不會(huì)考慮尾隨空格，但做like匹配或正則匹配時(shí)會(huì)考慮空格，因?yàn)槠ヅ鋾r(shí)字符是精確的。例如：

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ù)值在存儲(chǔ)(或調(diào)入內(nèi)存)時(shí)，以數(shù)值型方式存儲(chǔ)比字符型或日期時(shí)間類型更節(jié)省空間。因?yàn)檎麛?shù)值存儲(chǔ)時(shí)是直接通過bit計(jì)算數(shù)值的，0-255之間的任意整數(shù)都只占一個(gè)字節(jié)，256-65535之間的任意整數(shù)都占2個(gè)字節(jié)，而占用4個(gè)字節(jié)時(shí)便可以代表幾十億個(gè)整數(shù)之間的任意一個(gè)，這顯然比字符型存儲(chǔ)時(shí)每個(gè)字符占用一個(gè)字節(jié)節(jié)省空間的多。例如值"100"存儲(chǔ)為字符型時(shí)占用三個(gè)字節(jié)，而存儲(chǔ)為數(shù)值型將只占用一個(gè)字節(jié)。因此數(shù)據(jù)庫默認(rèn)將不使用引號(hào)包圍的值當(dāng)作數(shù)值型，如果明確要存儲(chǔ)為字符型或日期時(shí)間型則應(yīng)該使用引號(hào)包圍以避免歧義。

1.2.3 日期時(shí)間型的存儲(chǔ)方式

日期時(shí)間性數(shù)據(jù)存儲(chǔ)時(shí)需要使用引號(hào)包圍，避免和數(shù)值類型的數(shù)據(jù)產(chǎn)生歧義。關(guān)于日期時(shí)間的輸入方式是非常寬松的，以下幾種方式都是被允許的：任意允許的分隔符，建議使用4位的年份。

20110101
2011-01-01 18:40:20
2011/01/01 18-40-20
20110101184020

1.2.4 ENUM數(shù)據(jù)類型

ENUM數(shù)據(jù)類型是枚舉型。定義方式為ENUM('value1','value2','value3',...)，在向該類型的字段中插入數(shù)據(jù)時(shí)只能插入value中的某一個(gè)或NULL，插入其他值或空(即'')時(shí)都將截?cái)酁榭諗?shù)據(jù)。存儲(chǔ)時(shí)會(huì)忽略大小寫(將轉(zhuǎn)換為ENUM中的字符)，且會(huì)截?cái)辔搽S空格。

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 set

ENUM類型的數(shù)據(jù)存儲(chǔ)時(shí)是通過index數(shù)值進(jìn)行存儲(chǔ)的，相比于字符串類型，它只需要1或2個(gè)字節(jié)進(jìn)行存儲(chǔ)即可。理論上，當(dāng)value的數(shù)量少于256個(gè)時(shí)只需一個(gè)字節(jié)，超出256個(gè)但少于65536個(gè)時(shí)使用2個(gè)字節(jié)存儲(chǔ)。MySQL/MariaDB限制最多只能存儲(chǔ)65536個(gè)value。當(dāng)然，這是理論上的限制，實(shí)際存儲(chǔ)時(shí)要考慮的因素有很多，例如NULL也會(huì)占用bit位，所以實(shí)際存儲(chǔ)時(shí)可能250個(gè)value就需要2個(gè)字節(jié)。

ENUM的每個(gè)value都通過index號(hào)碼進(jìn)行編號(hào)，無論是檢索還是操作該字段時(shí)都會(huì)通過index的值來操作。value1的index=1，value2的index=2，依次類推。但需要注意有兩個(gè)特殊的index值：NULL值的index=NULL，空數(shù)據(jù)的index=0。

例如ENUM('a','b','c')，向該字段依次插入'','b','a','c',NULL,'xxx'時(shí)，由于第一個(gè)和最后一個(gè)都會(huì)截?cái)酁榭諗?shù)據(jù)，所以它們的index為0，插入的NULL的index為NULL，插入的'b','a','c'的index值分別為2,1,3。所以index號(hào)碼和值的對(duì)應(yīng)關(guān)系為：

使用ENUM的index進(jìn)行數(shù)據(jù)檢索：

mysql> select * from test6 where gender=2;
+----+-------------+--------+
| id | name | gender |
+----+-------------+--------+
| 2 | gaoxiaofang | f |
+----+-------------+--------+
1 row in set

特別建議，不要使用ENUM存儲(chǔ)數(shù)值，因?yàn)闊o論是排序還是檢索或其他操作，都是根據(jù)index值作為條件的，這很容易產(chǎn)生誤解。例如，下面是用ENUM存儲(chǔ)兩個(gè)數(shù)值，然后進(jìn)行檢索和排序操作。

mysql> create table test7(id enum('3','1','2'));
mysql> insert into test7 values('1'),('2'),('3');
# 檢索時(shí)id=2，但結(jié)果查出來卻為1，因?yàn)閕d=2的2是enum的index值，在enum中index=2的值為1
mysql> select * from test7 where id=2;
+----+
| id |
+----+
| 1 |
+----+
1 row in set
# 按照id進(jìn)行排序時(shí)，也是通過index大小進(jìn)行排序的
mysql> select * from test7 order by id asc;
+----+
| id |
+----+
| 3 |
| 1 |
| 2 |
+----+
3 rows in set

因此，強(qiáng)烈建議不要在ENUM中存放數(shù)值，即使是浮點(diǎn)型數(shù)值也很容易出現(xiàn)歧義。

1.2.5 SET數(shù)據(jù)類型

對(duì)于SET類型，和enum類似，不區(qū)分大小寫，存儲(chǔ)時(shí)刪除尾隨空格，null也是有效值。但不同的是可以組合多個(gè)給出的值。如set('a','b','c','d')可以存儲(chǔ)'a，b','d，b'等，多個(gè)成員之間使用逗號(hào)隔開。所以，使用多個(gè)成員的時(shí)候，成員本身的值中不能出現(xiàn)逗號(hào)。如果要存儲(chǔ)的內(nèi)容不在set列表中，則截?cái)酁榭罩怠?/p>

SET數(shù)據(jù)類型占用的空間大小和SET成員數(shù)量M有關(guān)，計(jì)算方式為(M+7)/8取整。所以： 1-8個(gè)成員占用1個(gè)字節(jié)；

9-16個(gè)成員占用2個(gè)字節(jié)；
17-24個(gè)成員占用3字節(jié)；
25-32個(gè)成員占用4個(gè)字節(jié)；
33-64個(gè)成員占用8字節(jié)。

MySQL/MariaDB限制最多只能有64個(gè)成員。

存儲(chǔ)SET數(shù)據(jù)類型的數(shù)據(jù)時(shí)忽略重復(fù)成員并按照枚舉時(shí)的順序存儲(chǔ)。如set('b','b','a')，存儲(chǔ)'a,b,a','b,a,b'的結(jié)果都是'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 set

1.3 數(shù)據(jù)類型屬性：unsigned

unsigned屬性就是讓數(shù)值類型的數(shù)據(jù)變得無符號(hào)化。使用unsigned屬性將會(huì)改變數(shù)值數(shù)據(jù)類型的范圍，例如tinyint類型帶符號(hào)的范圍是-128到127，而使用unsigned時(shí)范圍將變成0到255。同時(shí)unsigned也會(huì)限制該列不能插入負(fù)數(shù)值。

create table t(a int unsigned,b int unsigned);
insert into t select 1,2;
insert into t select -1,-2;

上面的語句中，在執(zhí)行第二條語句準(zhǔn)備插入負(fù)數(shù)時(shí)將會(huì)報(bào)錯(cuò)，提示超出范圍。

使用unsigned在某些情況下確有其作用，例如一般的ID主鍵列不會(huì)允許使用負(fù)數(shù)，它相當(dāng)于實(shí)現(xiàn)了一個(gè)check約束。但是使用unsigned有時(shí)候也會(huì)出現(xiàn)些不可預(yù)料的問題：在進(jìn)行數(shù)值運(yùn)算時(shí)如果得到負(fù)數(shù)將會(huì)報(bào)錯(cuò)。例如上面的表t中，字段a和b都是無符號(hào)的列，且有一行a=1,b=2。

mysql> select * from t;
+---+---+
| a | b |
+---+---+
| 1 | 2 |
+---+---+
1 row in set

此時(shí)如果計(jì)算a-b將會(huì)出錯(cuò)，不僅如此，只要是unsigned列參與計(jì)算并將得到負(fù)數(shù)都會(huì)出錯(cuò)。

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

如果計(jì)算結(jié)果不是負(fù)數(shù)時(shí)將沒有影響。

mysql> select 2-a,a*3 from t;
+-----+-----+
| 2-a | a*3 |
+-----+-----+
| 1 | 3 |
+-----+-----+
1 row in set

這并不是MySQL/MariaDB中的bug，在C語言中的unsigned也一樣有類似的問題。這個(gè)問題在MySQL/MariaDB中設(shè)置set sql_mode='no_unsigned_subtraction'即可解決。

所以個(gè)人建議不要使用unsigned屬性修飾字段。

1.4 數(shù)據(jù)類型屬性：zerofill

zerofill修飾字段后，不足字段顯示部分將使用0來代替空格填充，啟用zerofill后將自動(dòng)設(shè)置unsigned。zerofill一般只在設(shè)置了列的顯示寬度后一起使用。關(guān)于列的顯示寬度在上文已經(jīng)介紹過了。

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只是修飾顯示結(jié)果，不會(huì)影響存儲(chǔ)的數(shù)據(jù)值。

以上這篇(MariaDB)MySQL數(shù)據(jù)類型和存儲(chǔ)機(jī)制全面講解就是小編分享給大家的全部?jī)?nèi)容了，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。