MySQL基础-MySQL数据类型

本文使用MySQL版本为8.0.11

1.MySQL数据类型介绍

        MySQL支持多种数据类型，主要有数值类型、日期/时间类型和字符串类型。

        （1）数值类型：包括整数类型TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT；浮点小数数据类型FLOAT和DOUBLE；定点小数类型DECIMAL。

        （2）日期/时间类型：包括YEAR、TIME、DATE、DATETIME、TIMESTAMP。

        （3）字符串类型：CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM、SET等。字符串类型又分为文本字符串和二进制字符串。

2.整数类型

        数值型数据类型主要用来存储数字，MySQL提供了很多种数值数据类型，不同的数据类型提供不同的取值范围，可以存储的值范围越大，所需要的存储空间也会越大。MySQL主要提供的整数类型有TINYINT、SMALLINT、MEDIUMINT、INT(INTEGER)、BIGINT。整数类型的属性字段可以添加AUTO_INCREMENT自增约束条件。下图列出了MySQL中的整数类型。

        可以看到，不同类型整数存储所需的字节数是不同的，占用字节数量最小的是TINYINT类型，占用字节最大的是BIGINT类型，相应的占用字节越多的类型所能表示的数值范围越大。根据占用字节数可以求出每一种数据类型的取值范围。例如，TINYINT需要1字节（8bits）来存储，那么TINYINT无符号的最大是2的8次方-1（255），TINYINT有符号位的最大值是2的7次方-1。其他类型的整数取值范围计算方法相同，如下图。

 假设声明一个INT类型的字段：

year INT(4)

该声明指明，year字段的数据类型是INT(4)，注意后面的数字4，它表示的是该数据类型指定的显示宽度，即能够显示的数值中数字的个数，如上声明，在year字段中的数据只显示4位数字的宽度。

【注意】显示宽度和数据类型的取值范围是无关的。显示宽度只是指明MySQL最大可能显示的数字个数，数值的位数小于指定的宽度时会由空格填充；如果插入了大于显示宽度的值，只要该值不超过该整数类型的取值范围，数值依然可以插入，而且能够显示出来。例如，向year字段插入一个数值19999，当使用SELECT查询该列值的时候，MySQL显示的将是完成的带有5位数字的19999，而不是4位数的值。

        其他整型数据类型也可以再定义表结构时指定所需要的显示宽度，如果不指定，则系统为每一种类型指定默认的宽度值。

【例2.1】创建表tmp1,其中字段x,y,z,m,n数据类型依次为：TINYINT/SMALLINT/MEDIUMINT/INT/BIGINT/:

CREATE TABLE tmp1
(
    x tinyint,
    y smallint,
    z mediumint,
    m int,
    n bigint
)

查看表结构：

        可以看到，系统将添加不同的默认显示宽度。这些显示宽度能够保证显示每一种数据类型可以取到取值范围内的所有值。

        不同整数类型有不同的取值范围，并且需要不同的存储空间，因此应该根据实际需要选择最合适的类型，这样有利于提高查询的效率和节省存储空间。整数类型是不带小数部分的数值。

【提示】显示宽度只用于显示，并不能限制取值范围和占用空间。例如：INT(3)会占用4个字节的存储空间，并且允许的最大值不是999，而是INT类型所允许的最大值。

3.浮点类型和定点类型

        MySQL中使用浮点数和定点数来表示小数。浮点数类型有两种：单精度类型（FLOAT）和双精度类型（DOUBLE）。定点数只有一种类型：DECIMAL。浮点数类型和定点数类型都可以用（M,N）来表示，其中M称为精度，表示总共的位数，N 称为标度，表示小数的位数。下图列出MySQL中的小数类型和存储大小。

        DECIMAL类型不同于FLOAT和DOUBLE，DECIMAL实际是以串存放的，可能的最大取值范围与DOUBLE，但是其有效的取值范围由M和D决定。如果改变M而固定D，则其取值范围将随M的变大而变大。从上图看出，DECIMAL的存储空间并不是固定的，而由其精度M决定的，占用M+2字节。

【提示】不论是定点还是浮点类型，如果用户指定的精度超出精度范文，则会四舍五入。

【例3.1】创建表tmp2，其中字段x,y,x的数据类型依次为FLOAT(5,1)，DOUBLE(5,1)，DECIMAL(5,1)，向表中插入数据5.12,5.15和5.123，最后查询表数据：

create table tmp2 (
    x float(5,1),
    y double(5,1),
    z decimal(5,1)
);
insert into tmp2 values(5.12,5.15,5.123);
select * from tmp2;

 可以看到x,y,z三列的值都被截断。FLOAT、DOUBLE在不指定精度时，默认会按照实际的精度（由计算机硬件和操作系统决定），DECIMAL若不指定精度则默认为（10,0）。

        浮点数相对于定点数的优点是在长度一定的情况下，浮点数能否表示更大的数据范围，它的确定是会引起精度问题。

【提示】在MySQL中，定点数以字符串形式存储，在对精度要求比较高的时候（如货币、科学数据等）使用DECIMAL的类型比较好，另外两个浮点数进行减法和比较运算时容易出问题，所以在使用浮点数时需要注意，并尽量避免做浮点数比较。

4.日期和时间类型

MySQL中有很多种表示日期的数据类型，主要有YEAR、DATETIME、DATE、TIMESTAMP、TIME。每一个类型都有合法的取值范围，当指定确实不合法的值时，系统将零值插入到数据库中。下面列出了MySQL中的日期和时间类型。

4.1 YEAR

        year类型是一个单字节类型，用于表示年，在存储时只需要1字节。

【例4.1.1】创建表tmp3，定义数据类型为YEAR的字段y，向表中插入值2010，‘2010’，‘2016’ 

create table tmp3(y year);
insert into  tmp3 values(2010),('2010'),('2016');
select * from tmp3;

 结果：

4.2 TIME

        time类型用在只需要时间信息的值，在存储时需要3字节，格式为‘HH:MM:SS’。其中，HH表示小时，MM表示分钟，SS表示秒。TIME类型的取值范围：-838:59:59~ 838:59:59，小时部分会如此大的原因是TIME类型不仅可以用于表示一天的时间，还可能是某个事件过去的时间或两个事件之间的时间。

【例4.2.1】创建数据表tmp4，定义数据类型为TIME的字段t，向表中插入数据：

CREATE TABLE tmp4(t time);
insert into tmp4 values('10:05:05'),('23:23');
select * from tmp4;

结果：

4.3 DATE类型

        DATE类型用来仅需要日期值时，没有时间部分，存储需要3个字节。日期格式为‘YYYY-MM-DD’，在给DATE类型的字段赋值时，可以使用字符串或数字类型，只要符合DATE的日期格式即可。

4.4 DATETIME类型

        DATETIME类型用于需要同时包含日期和时间信息的值，在存储时需要8字节。日期格式‘YYYY-MM-DD HH:MM:SS’。在给DATETIME字段赋值时，可以使用字符串或数字类型。

4.5 TIMESTAMP

        TIMESTAMP的显示格式与DATETIME相同，显示宽度固定在19个字符，日期格式为：

YYYY-MM-DD HH:MM:SS，存储需要4个字节。TIMESTAMP的取值范围小于DATETIME的取值范围，TIMESTAMP取值范围是：1970-01-01 00:00:01 UTC~ 2038-01-19 03:14:07.

5 文本字符串类型

        字符串类型用来存储字符串数据，除了可以存储字符串数据之外，还可以存储其他数据，比如图片和声音的二进制数据。MySQL支持两类字符串数据：文本字符串和二进制字符串。文本字符串包括CHAR、VARCHAR、TEXT、ENUM、SET。下图列出了文本字符串类型。

        VARCHAR和TEXT类型与后面说到的BLOB都是变长类型，其存储需求取决于列值的实际长度（在上面的表格中用L表示），而不是取决于类型的最大可能尺寸。例如，一个VARCHAR(10)列可能保存最大长度为10个字符的字符串，实际的存储需要是字符串的长度L加上1字节（记录字符串的长度）。对于字符”abcd“,L是4而存储要求是5字节。

5.1 CHAR和VARCHAR类型

        CHAR(M)为固定长度字符串，在定义时指定字符串列长。当保存时在右侧填充空格，以达到指定的长度。M表示列长度，M的范围是0-255字符。例如，CHAR(4)定义了一个固定长度的字符串列，其包含的字符个数最大为4.当检索到CHAR值时，尾部的空格将被删除。

        VARCHAR(M)是长度可变的字符串，M表示最大列长度。M的范围是0~65535。VARCHAR的最大实际长度由最长的行的大小和使用的字符集确定，而其实际占用的空间为字符串的实际长度加1。例如，VARCHAR(50)定义了一个最大长度为50的字符串，如果插入的字符串只有10个字符，则实际存储的字符串为10个字符和一个字符串结束字符。VARCHAR在值保存和检索时，尾部的空格仍保留。

        下面将不同字符串保存到CHAR(4)和VARCHAR(4)列，说明CHAR和VARCHAR的差别。

        对比结果可以看到，CHAR(4)定义了固定长度为4的列，不管存入的长度为多少，所占用的空间均为4个字节；VARCHAR(4)定义的列所占的字节数为实际长度加1。查询时，CHAR(4)和VARCHAR(4)的值并不一定相同，如【例5.1.1】所示。 

【例5.1.1】创建tmp1表，定义字段ch和vch数据类型，依次为CHAR(4)、VARCHAR(4)，向表中插入数据"ab  "：

create table tmp1(ch CHAR(4),
                  vch VARCHAR(4));

INSERT INTO tmp1 values ('ab  ','ab  ');

select concat('(',ch,')'), concat('(',vch,')') from tmp1;

结果：

5.2 TEXT类型

         TEXT列保存非二进制字符串，如文章内容、评论等。当保存或查询TEXT列的值时，不删除尾部空格。TEXT类型分4种：TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT。不同的TEXT类型的存储空间和数据长度不同。

（1）TINYTEXT最大长度为255字符。

（2）TEXT最大长度为65535字符。

（3）MEDIUMTEXT最大长度为16777215字符。

（4）LONGTEXT最大长度为4294967295或4GB字符。

5.3 ENUM类型

        ENUM是一个字符串对象，其值为表创建时在列规定中枚举的一列值。语法格式如下：

字段名  ENUM('值1','值2',...,'值n')

        其中，”字段名“指将要定义的字段，”值n“指枚举列表中的第n个值。ENUM类型的字段在取值时，只能在指定的枚举列表中取，而且一次只能取一个。创建的成员中有空格时，其尾部的空格将自动被删除。ENUM值在内部用整数表示，并且每个枚举值均有一个索引值：列表值所允许的成员值从1开始编号，MySQL存储的就是这个索引编号。枚举最多可以有63356个元素。

        例如，定义ENUM类型的列（‘first’,'second','third')，该列可以取的值和每个值的索引如下：

        ENUM值依照列索引顺序排列，并且空字符串排在非空字符串前，NULL值排在其他所有的枚举值前。

【例5.3.1】创建表tmp2，定义ENUM类型的列enm('first','second','third')，查看列成员的索引值：

drop table  tmp2;
CREATE TABLE tmp2(
    enm ENUM('first','second','third')
);
insert into tmp2 values ('first'),('second'),('third'),(null);

select enm,enm+0 from tmp2;

结果：

5.4 SET类型

        SET是一个字符串对象，可以有0或多个值。SET列最多可以有64个成员，其值为表创建时规定的一列值。指定包括多个SET成员的SET列值时，各成员之间用逗号间隔。语法：

SET('值1','值2',...,'值n')

        与ENUM类型相同，SET值在内部用整数表示，列表中每一个值都有一个索引编号。当创建表时，SET成员值的尾部空格将自动被删除。与ENUM类型不同的是，ENUM类型的字段只能从定义的列值中选择一个值插入，而SET类型的列可从定义的列值中选择多个字符的联合。

        如果插入SET字段中列值有重复，则MySQL自动删除重复的值；插入SET字段的值的顺序并不重要，MySQL会在存入数据时按照定义的顺序显示；如果插入了不正确的值，默认情况下，MySQL将忽视这些值，并给出警告。

6 二进制字符串类型

        MySQL中的二进制数据类型有BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB。

     

6.1 BIT类型

        BIT类型是位字段类型。M表示每个值的位数，范围为1~64。如果M被省略，默认为1。如果为BIT(M)列分配的值的长度小于M位，就在值的左边用0填充。例如，BIT(6)列分配一个值b'101'，其效果与分配b'000101'相同。BIT数据类型用来保存位字段值。例如，以二进制的形式保存数据13（二进制是1101），在这里需要位数至少为4位的BIT类型，即可以定义列类型为BIT(4)，大于二进制1111的数据是不能插入BIT(4)类型的字段中的。

6.2 BINARY 和VARBINARY 类型

        BINARY和VARBINARY类型类似于CHAR和VARCHAR，不同的是它们包含二进制字节字符串。语法格式：

列名  BINARY(M) 或者列名  VARBINARY(M)

        BINARY类型的长度是固定的，指定长度后，不足最大长度的，将在它们右边填充'\0'补齐以达到指定长度。例如：指定列数据类型为BINARY(3)，当插入‘a’时，存储的内容实际为‘a\0\0‘。

        VARBINARY类型的长度是可变的，指定好长度之后，其长度可以在0到最大值之间。例如：指定列数据类型为VARBINARY（20），如果插入的值的长度只有10，则实际存储空间为10加1，即实际占用的空间为字符串的实际长度加1.

6.3 BLOB类型

        BLOB是一个二进制大对象，用来存储可变数据的数据。BLOB类型分为：TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB。