本文目次：
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数据范例和存储机制详解的细致内容，更多请关注ki4网别的相干文章！
