重学MySQL系列(三)：MySQL数据类型详解在这篇文章中，我们来学习MySQL的数据类型，数据类型是MySQL学习中

原创作者，公众号【程序员读书】，欢迎关注公众号，转载文章请注明出处哦。

在这篇文章中，我们来学习MySQL的数据类型，数据类型是MySQL学习中比较基础的知识，但同时也是比较容易被忽略的知识，因此有必要认真学习一下。

当然，很多对数据库有了解的小伙伴应该都知道，MySQL的数据类型，是在创建数据表(Table)的时候，用于指数据表中字段(Field)的类型，即规定字段用存储什么样的数据。

比如下面我们在创建数据表时，在每个字段名称后面指定了数据表的数据类型。

CREATE TABLE users(
    id INT NOT NULL AUTO_INCREMENT, 
    username VARCHAR(32) NOT NULL,
    email VARCHAR(50) NOT NULL,
    phone CHAR(11) NOT NULL,
);

如何查看数据类型

对于已经创建好的数据表，我们可以使用SHOW CREATE TABLE语句查看具体数据表的原始建表SQL语句，比如我们想知道上面的users数据表的建表语句，可以这样：

SHOW CREATE TABLE users;

执行了上面的语句后，就会打印上面我们创建数据表的原始SQL语句。

MySQL的数据类型

目前MySQL支持的数据类型有：数字类型，字符串类型(包括字符)、时间与日期类型、空间类型和JSON类型，不同数据类型有不同的特性，我们可从该类型的存储空间、取值范围、默认值、如何进行比较，是否可以索引等等方面对其进行学习。

注：空间数据类型在平时使用并不太常用，因此在我们的文章中便不作介绍了。

数字数据类型

MySQL的数字类型可分为整数类型、定点型类型、浮点数类型、位类型四种类型，下面表格是这四种数据类型的更细致划分，我们可以从表格中对所有数字类型有一定的了解。

数据类型定义	存储空间占用量	取值范围
TINYINT[(M)]	1个字节	带符号：-128~127(-2 $^{7}$ 到2 $^{7}$ -1) 无符号：0~255(2 $^{8}$ -1)
SMALLINT[(M)]	2个字节	带符号：-32768~32767(-2 $^{15}$ 到2 $^{15}$ -1) 无符号：0~65535(2 $^{16}$ -1)
MEDIUMINT[(M)]	3个字节	带符号：-8388608~8388607(-2 $^{23}$ 到2 $^{23}$ -1) 无符号：0~16777215(2 $^{24}$ -1)
INT[(M)]	4个字节	带符号：-2147683648~2147683647(-2 $^{31}$ 到2 $^{31}$ -1) 无符号：0~4294967295(2 $^{32}$ -1)
BIGINT[(M)]	8个字节	带符号：-128~127 无符号：0~255
DECIMAL（[M[,D]]）	可变长度	其取值范围由M和D的值决定
FLOAT[(M)]	4个字节	最小非零值 : $\pm$ 1.175494351E-38 最大非零值: $\pm$ 1.175494351E-38
DOUBLE[(M)]	8个字节	最小非零值 : $\pm$ 1.175494351E-38 最大非零值: $\pm$ 1.175494351E-38
BIT[(M)]	1~4个字节	0到2 $^{M}$ -1

整数类型

整数类型用于存储整数，根据其存储的字节大小可分为TINYINT，SMALLINT，MEDIUMINT,INT,BIGINT，分别占用1，2，3，4，8个字节的大小，存储范围从 -2 $^{(N-1)}$ 到2 $^{(N-1)}$ -1，其中N是类型的存储空间的位数(bit,1个字节8bit)。

UNSIGNED

一般整数都支持正负值，不过可以使用可选的属性UNSIGNED，使其不允许负值，这样的话，可以使用存储的正数上限提高一倍，比如TINYINT的范围由-128~127变为0~255。

CREATE TABLE test_int(
    id int unsigned not null
);

AUTO_INCREMENT

对整数类型，在创建数据表时，可以指定AUTO_INCRMENT属性，让该字段可以变成一个可以自动增长的序列，如：

CREATE TABLE test_int(
    id INT UNSIGNED NOT NULL AUTO_INCRMENT PRIMARY KEY,
    name VARCHAR(32) NOT NULL
);

实数类型

实数类型分为浮点数和定点数类型两种，定点数类型一种是精确值类型，DECIMAL就是定点数类型，浮点数是一种是近似值类型，FLOAT和DOUBLE就是浮点数类型。

浮点数类型存四舍五入的问题，因此在有需要精确运算的时候，可以使用定点数或整数数据，避免计算误差。

浮点数也可以使用UNSIGNED属性，与整数一样，这会让浮点数变成全部是正数，不能支持负数。

浮点数也可以使用AUTO_INCRMENT,这会让浮点数的表现与整数一样了。

位数类型

在MySQL5.0以前，BIT类型是TINYINT的同义词，在MySQL 5.0以后，BIT类型是一种用于存储位字段值的类型。

BIT字段的值的格式为：0bval,b'val'与B'val'，val表示的是0或1组成的位字段常量值。

下面合法的BIT类型常量值：

0b11111
b'11111'
B'11111'

而下面则不合法的常量值：

0B'11111'
b11111
B11111

使用BIT字段时，可以指定一个长度M，即BIT(M)，M的取值为1~64,，所以BIT类型的字段占用1~4个字节的长度。

#示例表1
CREATE TABLE test_bit_1(
    id bit
);
#示例表2
CREATE TABLE test_bit_2(
    id bit(8)
);

上面的示例表1中，我们没有指定M，所以默认值为1，即我们只能向示例表1插入1个位值。

# 正确
INSERT INTO test_bit_1 VALUES(B1);
# 错误
INSERT INTO test_bit_1 VALUES(B11);
#正确
INSERT INTO test_bit VALUES(B11111111);
#正确
INSERT INTO test_bit VALUES(B000011111111);
# 错误
INSERT INTO test_bit VALUES(B000111111111);

字符串类型

字符串类型是MySQL中最常用的数据类型，也是比较灵活的数据类型，学习字符串数据，也会讨论到字符集与转义字符等知识，因为比较复杂。

在MySQL数据库中，字符串的字段值可以使用双引号("")或单引号('')来括起来，比如：

'test'
"mysql"

如果字符串值里面本身就包含双引号或单引号时，如果只包含单引号，则可以使用双引号来引用字符串，反引亦然，而如果一个字符串里面同时包含单双引号时，也可以使用反斜杠(\)进行转义，比如：

"I'm Programmer"
'He is "Programmer"'
'I\'m Programmer'

下面的表格所展示的MySQL支持的所有字符串数据类型：

数据类型定义	存储空间占用量	最大长度
BINARY([M])	M个字节	M个字节
VARBINARY([M])	M个字节	L+1或L+2个字节
CHAR([M])	M个字符	M*w个字节
VARCHAR([M])	M个字符	L+1或L+2个字节
TINYBLOB	2 $^{32}$ -1个字节	L+1个字节
BLOB	2 $^{32}$ -1个字节	L+2个字节
MEDIUMBLOB	2 $^{32}$ -1个字节	L+3个字节
LONGBLOB	2 $^{32}$ -1个字节	L+4个字节
TINYTEXT	2 $^{8}$ -1个字符	L+1个字节
TEXT]	2 $^{16}$ -1个字符	L+2个字节
MEDIUMBLOB	2 $^{24}$ -1个字符	L+3个字节
LONGTEXT	2 $^{32}$ -1个字符	L+4个字节
SET('value1','value2',.....)	1或2个字节	64个成员
ENUM('value1','value2',.....)	1，2，3，4或8个字节	65535个成员

MySQL的字符串数据类型可以分为二进制字符串类型和非二进制字符串类型两种，因此上面表格中的数据类型，还可以这样划分一样：

二进制字符串类型	非二进制字符串类型
BINARY	CHAR
VARBINARY	VARCHAR
TINYBLOB	TINYTEXT
BLOB	TEXT
MEDIUMBLOB	MEDIUMTEXT
LONGBLOB	LONGTEXT

二进制字符串类型用于存储二进制数据，其存储的尺寸用字节数来计算，而非二进制字符串用于保存其他字符串数据，其存储的尺寸用字符数来计算，其存储的长度可以通过在类型跟一个参数M来指定，如：

CREATE TABLE test_string(
    a BINARY(20),
    b CHAR(20)
);

上面的示例数据表中，字段a是用于二进制数据，表示可以存储20个字节，而字段b存储非进制数据类型，表示可以存储20个字符。

对于非二进制字符串，在存储和解析时，还需要指定属于什么字符集和排序方式，不同的字符集与排序方式二进制字符串类型的存储空间与比较方式。

关于字符集和排序方式的知识，以后有机会再讨论。

CHAR与VARCHAR

CHAR与VARCHAR在于，CHAR是定长字符串数据类型，而VARCHAR是可变长度的字符串数据类型。

CHAR的长度取值范围是1~255，默认值1，但CHAR(0)是合法，占用1个位，表示空字符串。

而VARCHAR的取值范围是1~65535，不过VARCHAR的最大长度一般小于65535，因此MySQL中数据行的最大长度也是65535，何况VARCHAR还需要额外两个字节来记录其目前的存储长度。

BINARY与VARBINARY

BINARY与VARBINARY的区别与CHAR和VARCHAR的区别是类似的。

BLOB家族与TEXT家族

BLOB是包含TINYBLOB,BLOB,MEDIUMBLOB,LONGBLOB系列数据类型的家族，用于存储二进制字符串，比如图片、声音等数据，而TEXT是包含TINYTEXT,TEXT,MEDIUMTEXT,LONGTEXT系列数据类型的家族用于存储非二进制字符串，所以TEXT系列的类型存储与解析与字符集有关。

使用MEMORY存储引擎的数据表不支持BLOB和TEXT这两种数据类型。

ENUM与SET

ENUM是一种特殊的字符串类型，占用1或2个字节，也就是说ENUM类型可以设置65535个成员，ENUM类型与一般的字符串类型不同，设置为ENUM类型的字段，只能存储预先定义好的字符串值。

如下所示，我们创建一个users表，该表有一个定义为ENUM类型的gender字段：

CREATE TABLE users(
    id int not null,
    username varchar(32) not null,
    gender enum('f','m')
);

下面的语句可以正确地写入数据表：

INSERT INTO users values(1,'test_1','f');
INSERT INTO users values(2,'test_2','m');

而下面写入了一个gender字段未定义的值

INSERT INTO users values(3,'test_3','d');

所以会报如下所示的错误：

ERROR 1265 (01000): Data truncated for column 'gender' at row 1

与ENUM一样，SET也是一种特殊的字符串类型，并且一样只能存储预先定义好的字符串值，SET根据预先定义值的数值会占用1，2，4或8个字节，但只能存储64个成员。

时间与日期类型

MySQL为存储时间与日期提供了YEAR，DATE，TIME，DATETIME，TIMESTAMP等数据类型，分别占用1，3，3，8，4个字节的长度。

类型定义	取值范围	占用存储空间
DATE	'1000-01'到'9999-12-31'	3个字节
TIME	'-838:59:59'到'838:59:59'	3个字节
DATETIME	'1000-01-01 00:00:00'到'9999-12-31 23:59:59'	8个字节
TIMESTAMP	'1970-01-01 00:00:00'到'2038-01-19 03:14:07'	4个字节
YEAR([M])	1901到2155	1个字节

DATE,TIME,DATETIME

DATE数据类型表示日期的值，占用3个字节，其取值范围为1000-01-01~9999-12-31,默认零值为0000-00-00。

TIME类型占用3个字节，其取值范围是'-838：59：59'到'838：59：59'，注意TIME类型并不是表示时分秒，而表示逝去的一段时间，即表示两个事件之间的时间间隔，所以TIME类型可以为负值。

当我们从数据表中查询TIME类型时，其显示格式为：

hh:mm:ss或hhh:mm:ss

当我们写入TIME类型字段值的时候，有些要注意的地方：

当我们写入11:30时，会被处理为11:30:00，当我们写入1122时，会被处理为00:11:22。

默认情况下，位于TIME范围之外但有效的值将被裁剪到该范围的最近端点。例如，“-850：00：00”和“ 850：00：00”被转换为“ -838：59：59”和“ 838：59：59”。无效的TIME值将转换为“ 00:00:00”。请注意，由于“ 00:00:00”本身就是有效的TIME值，因此无法通过表中存储的值“ 00:00:00”来判断原始值是否指定为“ 00”： 00:00'或是否无效。

DATETIME数据类型则是DATE和TIME两个种数据类型的组合。

TIMESTAMP

TIMESTAMP数据类型是用于保存日期与时间的组合值的，与时区相关，默认是以UTC(世界标准时间)的格式存储的，其取值范围是1970-01-01 00:00:00到2038-01-29 03:14:07，这也是4个字节所能表达的长度，所以TIMESTAMP占用4个字节，当我们从数据中查询TIMESTAMP的数据列，会根据我们当前的时区自动转换值。

YEAR

YEAR占用一个字节，在声明时，可以指定一个宽度M，M的取值只能是4，即YEAR类型有两种写法，YEAR和YEAR(4)，其取值范围1901~2155

如果我们只需要存储年份，而且年份刚在落在YEAR的取值范围内，那我们应该使用YEAR类型，因为如果我们自己存储一个年份时，至少需要使用SMALLINT类型，这样会占用两个字节，而YEAR只占用一个字节。

JSON数据类型

MySQL从5.7.8版本开始就支持存储原生的JSON类型的数据，我们可以MySQL中存储JSON对象或JSON数组。JSON数据并不是以字符串的形式存储，而是使用一种允许快速读取文本元素的内部二进制格式进行存储的,在JSON数据列中插入或者更新的时候将会自动验证JSON文本数据是否正确，未通过验证的文本将产生一个错误信息。

JSON数组是以中括号([])包含起来，并使用逗号隔的列表，其格式如下所示：

["abc", 10, null, true, false]

JSON对象包含多个key-value，使用花括号({})包括起来并用逗号分隔的数据类型，其格式如下所示：

{"k1": "value", "k2": 10}

另外，与BLOB和TEXT类型一样，设置为JSON类型的字段时不能设置默认值，其默认值只能为NULL，所以下面的语句是错误的：

CREATE TABLE test_json(
    t json NOT NULL DEFAULT ''
);

运行上面的建表语句，MySQL会提示下面所示的错误：

ERROR 1101 (42000): BLOB, TEXT, GEOMETRY or JSON column 't' can't have a default value

下面的语句才是正确的：

CREATE TABLE test_json(
    t json not null
);

JSON类型的存储长度与LONGBLOB，LONGTEXT类型长度相同，当然最大的长度不能超过max_allowed_packet系统变量设置的值。

我们可以下面的语句往上面的数据插入数据：

INSERT INTO test_json VALUES('{"key1": "value1", "key2": "value2"}');

除了提供JSON数据类型，MySQL提供了许多可以操作JSON数据的函数，下面这些函数的说明：

函数	简介说明
JSON_APPEND()	将数据附加到JSON文档
JSON_ARRAY()	创建一个JSON数组
JSON_ARRAY_APPEND()	将数据附加到JSON文档
JSON_ARRAY_INSERT()	插入JSON数组
JSON_CONTAINS()	JSON文档是否在路径中包含特定对象
JSON_CONTAINS_PATH()	JSON文档是否在路径中包含任何数据
JSON_DEPTH()	JSON文档的最大深度
JSON_EXTRACT()	从JSON文档返回数据
JSON_INSERT()	插入一个值到JSON文档中
JSON_KEYS()	将JSON文档的key提取为一个数组
JSON_LENGTH()	JSON文档中的元素数
JSON_MERGE()	合并JSON文档
JSON_MERGE_PRESERVE()	合并JSON文档，保留重复的键
JSON_OBJECT()	创建一个JSON对象
JSON_PRETTY()	以易于阅读的格式打印JSON文档
JSON_QUOTE()	引用JSON文档
JSON_REMOVE()	从JSON文档中移除数据
JSON_REPLACE()	替换JSON文档中的现有值并返回结果
JSON_SEARCH()	查找所有指定值的位置
JSON_SET()	将数据插入JSON文档
JSON_STORAGE_SIZE()	用于存储JSON文档的二进制表示形式的空间
JSON_TYPE()	返回JSON类型：对象或数组
JSON_UNQUOTE()	取消引用JSON值
JSON_VALID()	验证JSON数据是否有效

JSON函数的简单示例：

# 判断JSON类型
mysql> SELECT JSON_TYPE('["a", "b", 1]');
+----------------------------+
| JSON_TYPE('["a", "b", 1]') |
+----------------------------+
| ARRAY                      |
+----------------------------+

# 创建JSON数组
mysql> SELECT JSON_ARRAY('a', 1, NOW());
+----------------------------------------+
| JSON_ARRAY('a', 1, NOW())              |
+----------------------------------------+
| ["a", 1, "2015-07-27 09:43:47.000000"] |
+----------------------------------------+

上面的一些示例比较简单，对于这些函数的使用，我们可以在实际的使用中慢慢探索。

如何选择数据类型

上面我们介绍了这么多种数据类型，那到底应该如何选择数据类型呢？对于数据类型的选择，一般还是要遵循以下几个原则：

更小的通常更好

数据越小，占用的空间就越小，查询的速度也就越快，占用的内存与CPU就更少，不过也不要因此就错误地选择了更小的数据类型，而导致数据溢出，毕竟在MySQL，修改数据类型是比较麻烦的事情，尤其是对已经有大量数据的数据表进行数据类型修改。

简单就好

同样，也简单的类型越好，查询与写入的速度也就越快。

避免使用NULL

很多的数据类型的默认值就是NULL，所以很多数据列可以把NULL作为空值，不过一般情况下，最好把数据列设置为NOT NULL，除非你真的需要把数据列设置为NULL。

因为允许为NULL的数据列，在索引处理方面复杂，而且需要额外的空间来存储。

小结

作为一个成熟的关系型数据库管理系统，MySQL支持了许多常用数据类型，方便我们根据不同的业务进行挑选，对于这些数据类型，我们不必死记硬背，不过在挑选数据类型时，还是需要对不同数据类型的存储空间长度，默认值，取值范围有一定的了解，才能挑选出最节省空间也最高效的数据类型。

如果你觉得文章不错，欢迎扫码关注，你的关注就是我写作的最大动力