MySQL資料型別與儲存機制詳解

本文目錄：
#1.1 資料型別概覽
1.2 儲存機制與操作方式
　1.2.1 整數的儲存方式
　1.2.2 字符類型的儲存方式
　1.2.3 日期時間型的儲存方式
　1.2.4 ENUM資料型別
　1.2.5 SET資料型別
1.3. 資料型別屬性：unsigned
#1.3. 資料型別屬性：zerofill 

1.1 資料型別概覽

資料型別算是一種欄位約束，它限制每個欄位能儲存什麼樣的資料、能儲存多少資料、能儲存的格式等。 MySQL/MariaDB大致上有5類資料類型，分別是：整形、浮點型、字串類型、日期時間型以及特殊的ENUM和SET類型。

這5種資料型別的意義、限制及相關說明如下圖所示：

#各資料型別佔用位元組數，請參考 mariadb官方手冊。

1.2 儲存機制與操作方式

資料類型之所以能限定欄位的資料儲存長度，是因為在建立表格時在記憶體中嚴格劃定了位址空間，位址空間的長度是多少就能儲存多少位元組的資料。當然，這是一個很粗獷的概念，更具體的儲存方式見下面的描述。

資料型別限定範圍的方式有兩種：一是嚴格限定空間，分割了多少空間就只能儲存多少數據，超出的資料將被切斷；二是使用額外的位元組的bit位元來標記某個位址空間的位元組是否儲存了數據，儲存了就進行標記，不儲存就不標記。

1.2.1 整數型的儲存方式

此處主要說明整數的儲存方式，至於浮點型資料型別的儲存方式要考慮的東西太多。

對於整數資料型別來說，它嚴格限定空間，但它和字元不同，因為每個已分割的位元組上的bit位元上的0和1直接可以計算出數值，所以它的範圍是根據bit位的數量值來計算的。一個位元組有8個Bit位，這8個bit位元可以構成2^8=256個數值，同理2位元組的共2^16=65536個數值，4位元組的int佔用32bit，可以表示的範圍為0-2^32。也就是說，0-255之間的數字都只佔一個位元組，256-65535之間的數字需要佔用兩個位元組。

要注意，在MySQL/mariadb中的整數資料型別可以使用參數M，M是一個正整數，例如INT(M)，tinyint(M)。這個M表示的是顯示長度，如int(4)表示在輸出時將顯示4位元整數，如果實際值的位數小於顯示值寬度，則預設使用空格填充在左邊。而結果位數超出時將不影響顯示結果。一般功能都會配合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的儲存方式以及差異。

char類型是常被稱為"定長字串型別"，它嚴格限定空間長度，但它限定的是字元數，而非位元組數，但以前老版本中限定的是位元組數。因此char(M)嚴格儲存M個字符，不足部分使用空格補齊，超出M個字符的部分直接截斷。

由於char類型有"短了就使用空格補足"的能力，因此為了體現資料的真實性，在從地址空間中檢索資料時將自動刪除尾隨的空格部分。這正是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常被称为"变长字符串类型"，它存储数据时使用额外的字节的bit位来标记某个字节是否存储了数据。每存储一个字节(不是字符)占用一个bit位进行记录，因此一个额外的字节可以标记共256个字节，2个额外的字节可以标记65536个字节。但MySQL/mariadb限制了最大能存储65536个字节。这表示，如果是单字节的字符，它最多能存储65536个字符，如果是多字节字符，如UTF8的每个字符占用3个字节，它最多能存储65536/3=21845个utf8字符。

因此，varchar(M)存储时除了真实数据占用空间长度，还要额外计算1或2个字节的Bit位长度，即对于单字节字符实际占用的空间为M+1或M+2个字节，对于多字节字符(如3字节)实际占用的空间为M*3+1或M*3+2个字节。

由于varchar存储时需要采用额外的bit位记录每一个字节，短了的数据不会自动使用补齐，因此显式存储的尾随空格也会被存储并在Bit位上进行标记，也就是说不会删除尾随空格。

和char(M)一样，当指定varchar(2)时，只能存储两个字节的字符，如果超出了，则切断。

关于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 setselect a like 'ab      ' from test4;+-------------------+| a like 'ab      ' |
+-------------------+|                 0 |
+-------------------+1 row in set

最后需要说明的是，数值在存储(或调入内存)时，以数值型方式存储比字符型或日期时间类型更节省空间。因为整数值存储时是直接通过bit计算数值的，0-255之间的任意整数都只占一个字节，256-65535之间的任意整数都占2个字节，而占用4个字节时便可以代表几十亿个整数之间的任意一个，这显然比字符型存储时每个字符占用一个字节节省空间的多。例如值"100"存储为字符型时占用三个字节，而存储为数值型将只占用一个字节。因此数据库默认将不使用引号包围的值当作数值型，如果明确要存储为字符型或日期时间型则应该使用引号包围以避免歧义。

1.2.3 日期时间型的存储方式

日期时间性数据存储时需要使用引号包围，避免和数值类型的数据产生歧义。关于日期时间的输入方式是非常宽松的，以下几种方式都是被允许的：任意允许的分隔符，建议使用4位的年份。

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

1.2.4 ENUM数据类型

ENUM数据类型是枚举型。定义方式为ENUM('value1','value2','value3',...)，在向该类型的字段中插入数据时只能插入value中的某一个或NULL，插入其他值或空(即'')时都将截断为空数据。存储时会忽略大小写(将转换为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: 2mysql> 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 setmysql> select * from test6;
+----+-------------+--------+
| id | name        | gender |
+----+-------------+--------+
|  1 | malongshuai | Mail   |
|  2 | gaoxiaofang | f      |
|  3 | wugui       |        |
|  4 | tuner       | NULL   |
|  5 | woniu       |        |
+----+-------------+--------+5 rows in set

ENUM类型的数据存储时是通过index数值进行存储的，相比于字符串类型，它只需要1或2个字节进行存储即可。理论上，当value的数量少于256个时只需一个字节，超出256个但少于65536个时使用2个字节存储。MySQL/MariaDB限制最多只能存储65536个value。当然，这是理论上的限制，实际存储时要考虑的因素有很多，例如NULL也会占用bit位，所以实际存储时可能250个value就需要2个字节。

ENUM的每个value都通过index号码进行编号，无论是检索还是操作该字段时都会通过index的值来操作。value1的index=1，value2的index=2，依次类推。但需要注意有两个特殊的index值：NULL值的index=NULL，空数据的index=0。

例如ENUM('a','b','c')，向该字段依次插入'','b','a','c',NULL,'xxx'时，由于第一个和最后一个都会截断为空数据，所以它们的index为0，插入的NULL的index为NULL，插入的'b','a','c'的index值分别为2,1,3。所以index号码和值的对应关系为：

index	value
NULL	NULL
0	''
0	''
1	'a'
2	'b'
3	'c'

使用ENUM的index进行数据检索：

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

特别建议，不要使用ENUM存储数值，因为无论是排序还是检索或其他操作，都是根据index值作为条件的，这很容易产生误解。例如，下面是用ENUM存储两个数值，然后进行检索和排序操作。

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的值为1mysql> 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中存放数值，即使是浮点型数值也很容易出现歧义。

1.2.5 SET数据类型

对于SET类型，和enum类似，不区分大小写，存储时删除尾随空格，null也是有效值。但不同的是可以组合多个给出的值。如set('a','b','c','d')可以存储'a，b','d，b'等，多个成员之间使用逗号隔开。所以，使用多个成员的时候，成员本身的值中不能出现逗号。如果要存储的内容不在set列表中，则截断为空值。

SET数据类型占用的空间大小和SET成员数量M有关，计算方式为(M+7)/8取整。所以： 1-8个成员占用1个字节；
9-16个成员占用2个字节；
17-24个成员占用3字节；
25-32个成员占用4个字节；
33-64个成员占用8字节。

MySQL/MariaDB限制最多只能有64个成员。

存储SET数据类型的数据时忽略重复成员并按照枚举时的顺序存储。如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: 1mysql> 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 数据类型属性：unsigned

unsigned属性就是让数值类型的数据变得无符号化。使用unsigned属性将会改变数值数据类型的范围，例如tinyint类型带符号的范围是-128到127，而使用unsigned时范围将变成0到255。同时unsigned也会限制该列不能插入负数值。

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

上面的语句中，在执行第二条语句准备插入负数时将会报错，提示超出范围。

使用unsigned在某些情况下确有其作用，例如一般的ID主键列不会允许使用负数，它相当于实现了一个check约束。但是使用unsigned有时候也会出现些不可预料的问题：在进行数值运算时如果得到负数将会报错。例如上面的表t中，字段a和b都是无符号的列，且有一行a=1,b=2。

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

此时如果计算a-b将会出错，不仅如此，只要是unsigned列参与计算并将得到负数都会出错。

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

如果计算结果不是负数时将没有影响。

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 数据类型属性：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只是修饰显示结果，不会影响存储的数据值。

以上是MySQL資料型別與儲存機制詳解的詳細內容。更多資訊請關注PHP中文網其他相關文章！