数据库表由多列字段构成，每一个字段指定了不同的数据类型。指定字段的数据类型之后，也就决定了向字段插入的数据内容，例如，当要插入数值的时候，可以将它们存储为整数类型，也可以将它们存储为字符串类型。不同的数据类型也决定了MySQL在存储它们的时候使用的方式，以及在使用它们的时候选择什么运算符号进行运算。

MySQL数据类型介绍

1. 数值类型：包括整数类型TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT、浮点小数数据类型FLOAT和DOUBLE，定点小数类型DECIMAL。
2. 日期/时间类型：包括YEAR、TIME、DATE、DATETIME和TIMESTAMP。
3. 字符串类型：包括CHAR、VARCHAR、BINARY、BLOB、TEXT、ENUM和SET等。字符串类型又分为文本字符串和二进制字符串。

整数类型

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

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

例如，有如下建表语句

create table test(
	id int(4)
)

id字段的数据类型为INT(4)，注意后面的数字4，它表示的是该数据类型指定的显示宽度，即能够显示的数值中数字的个数。

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

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

例如，创建表tmp1，其中字段x、y、z、m、n数据类型依次为TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，SQL语句如下：

CREATE TABLE TEMP1(
	x TINYINT, y SMALLINT, Z MEDIUMINT, M INT , N BIGINT 
);

执行成功之后，使用DESC查看表结构

DESC TEMP1;

+-------+--------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+--------------+------+-----+---------+-------+

| X | tinyint(4) | YES | | NULL | |

| Y | smallint(6) | YES | | NULL | |

| Z | mediumint(9) | YES | | NULL | |

| M | int(11) | YES | | NULL | |

| N | bigint(20) | YES | | NULL | |

+-------+--------------+------+-----+---------+-------+

可以看到，系统将添加不同的默认显示宽度。这些显示宽度能够保证显示每一种数据类型可以取到取值范围内的所有值。例如，TINYINT有符号数和无符号数的取值范围分别为-128127和0255，由于负号占了一个数字位，因此TINYINT默认的显示宽度为4。同理，其他整数类型的默认显示宽度与其有符号数的最小值的宽度相同。不同的整数类型有不同的取值范围，并且需要不同的存储空间，因此应该根据实际需要选择最合适的类型，这样有利于提高查询的效率和节省存储空间。整数类型是不带小数部分的数值，现实生活中很多地方需要用到带小数的数值，下面将介绍MySQL中支持的小数类型。

浮点数类型和定点数类型

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

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

FLOAT类型的取值范围如下：

• 有符号的取值范围：-3.402823466E+38 ~ -1.175494351E-38。
• 无符号的取值范围：0和1.175494351E-38 ~ 3.402823466E+38。

DOUBLE类型的取值范围如下：

• 有符号的取值范围：-1.7976931348623157E+308 ~-2.2250738585072014E-308。
• 无符号的取值范围：0和2.2250738585072014E-308 ~1.7976931348623157E+308。

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

创建表tmp2，其中字段x、y、z的数据类型依次为FLOAT(5,1)、DOUBLE(5,1)和DECIMAL(5,1)，向表中插入数据5.12、5.15和5.123，SQL语句如下：

CREATE TABLE TEMP2(
	x FLOAT(5,1),
	y DOUBLE(5,1),
	z DECIMAL(5,3)
);

向表中插入数据：

INSERT INTO TEMP2 VALUES (5.12,5.16,5.123);

可以看到在插入数据时，MySQL给出了一个警告信息，使用SHOW WARNINGS;语句查看警告信息：

可以看到，FLOAT和DOUBLE在进行四舍五入时没有给出警告，只给出z字段数值被截断的警告。查看结果：

FLOAT和DOUBLE在不指定精度时，默认会按照实际的精度（由计算机硬件和操作系统决定），DECIMAL若不指定精度则默认为(10,0)。浮点数相对于定点数的优点是在长度一定的情况下，浮点数能够表示更大的数据范围；它的缺点是会引起精度问题。

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

日期与时间类型

MySQL中有多种表示日期的数据类型，主要有DATETIME、DATE、TIMESTAMP、TIME和YEAR。例如，当只记录年信息的时候，可以只使用YEAR类型，而没有必要使用DATE。每一个类型都有合法的取值范围，当指定确实不合法的值时系统将“零”值插入到数据库中。本节将介绍MySQL日期和时间类型的使用方法。

YEAR

YEAR类型是一个单字节类型，用于表示年，在存储时只需要1字节。可以使用各种格式指定YEAR值，如下所示：

1. 以4位字符串或者4位数字格式表示的YEAR，范围为‘1901’ ‘2155’。输入格式为‘YYYY’或者YYYY。例如，输入‘2010’或2010，插入到数据库的值均为2010。
2. 以2位字符串格式表示的YEAR，范围为‘00’到‘99’。‘00’ ‘69’和‘70’ ‘99’范围的值分别被转换为2000 2069和1970 1999范围的YEAR值。‘0’与‘00’的作用相同。插入超过取值范围的值将被转换为2000。
3. 以2位数字表示的YEAR，范围为1 99。1 69和70 99范围的值分别被转换为2001 2069和1970 1999范围的YEAR值。注意：在这里0值将被转换为0000，而不是2000。

两位整数范围与两位字符串范围稍有不同。例如：插入2000年，可能会使用数字格式的0表示YEAR，实际上，插入数据库的值为0000，而不是所希望的2000。只有使用字符串格式的'0'或'00'，才可以被正确地解释为2000。非法YEAR值将被转换为0000。

TIME

TIME类型用在只需要时间信息的值，在存储时需要3字节，格式为‘HH:MM:SS’。其中，HH表示小时，MM表示分钟，SS表示秒。TIME类型的取值范围为-838:59:59 838:59:59，小时部分会如此大的原因是TIME类型不仅可以用于表示一天的时间（必须小于24小时），还可能是某个事件过去的时间或两个事件之间的时间间隔（可以大于24小时，或者甚至为负）。可以使用各种格式指定TIME值。

1. ‘D HH:MM:SS’格式的字符串。可以使用下面任何一种“非严格”的语法：‘HH:MM:SS’、‘HH:MM’、‘D HH:MM’、‘D HH’或‘SS’。这里的D表示日，可以取0~34之间的值。在插入数据库时，D被转换为小时保存，格式为“D*24+HH”。
2. ‘HHMMSS’格式的、没有间隔符的字符串或者HHMMSS格式的数值，假定是有意义的时间。例如：‘101112’被理解为‘10:11:12’，但‘109712’是不合法的（它有一个没有意义的分钟部分），存储时将变为00:00:00。

为TIME列分配简写值时应注意：如果没有冒号，MySQL解释值时，假定最右边的两位表示秒。（MySQL解释TIME值为过去的时间而不是当天的时间。）例如，读者可能认为‘1112’和1112表示11:12:00（11点12分），但MySQL将它们解释为00:11:12（11分12秒）。同样‘12’和12被解释为00:00:12。相反，TIME值中如果使用冒号则肯定被看作当天的时间。也就是说，‘11:12’表示11:12:00，而不是00:11:12。

DATE

DATE类型用在仅需要日期值时，没有时间部分，在存储时需要3字节。日期格式为‘YYYY-MM-DD’。其中，YYYY表示年，MM表示月，DD表示日。在给DATE类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，只要符合DATE的日期格式即可。

1. 以‘YYYY-MM-DD’或者‘YYYYMMDD’字符串格式表示的日期，取值范围为‘1000-01-01’ ‘9999-12-31’。例如，输入‘2012-12-31’或者‘20121231’，插入数据库的日期都为2012-12-31。
2. 以‘YY-MM-DD’或者‘YYMMDD’字符串格式表示的日期，在这里YY表示两位的年值。包含两位年值的日期会令人模糊，因为不知道世纪。MySQL使用以下规则解释两位年值：‘00 69’范围的年值转换为‘2000 2069’；‘70 99’范围的年值转换为‘1970 1999’。例如，输入‘12-12-31’，插入数据库的日期为2012-12-31；输入‘981231’，插入数据的日期为1998-12-31。
3. 以YY-MM-DD或者YYMMDD数字格式表示的日期，与前面相似，00~69范围的年值转换为2000 2069，70 99范围的年值转换为1970 1999。例如，输入12-12-31插入数据库的日期为2012-12-31；输入981231，插入数据的日期为1998-12-31。
4. 使用CURRENT_DATE或者NOW()，插入当前系统日期。

DATETIME

DATETIME类型用于需要同时包含日期和时间信息的值，在存储时需要8字节。日期格式为‘YYYY-MM-DD HH:MM:SS’。其中，YYYY表示年，MM表示月，DD表示日，HH表示小时，MM表示分钟，SS表示秒。在给DATETIME类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，只要符合DATETIME的日期格式即可。

1. 以‘YYYY-MM-DD HH:MM:SS’或者‘YYYYMMDDHHMMSS’字符串格式表示的值，取值范围为‘1000-01-01 00:00:00’ ‘9999-12-31 23:59:59’。例如，输入‘2012-12-31 05: 05: 05’或者‘20121231050505’，插入数据库的DATETIME值都为2012-12-31 05: 05: 05。
2. 以‘YY-MM-DD HH:MM:SS’或者‘YYMMDDHHMMSS’字符串格式表示的日期，在这里YY表示两位的年值。与前面相同，‘00 69’范围的年值转换为‘2000 2069’，‘70 99’范围的年值转换为‘1970 1999’。例如，输入‘12-12-31 05: 05: 05’，插入数据库的DATETIME为2012-12-31 05: 05:05；输入‘980505050505’，插入数据库的DATETIME为1998-05-05 05: 05:05。
3. 以YYYYMMDDHHMMSS或者YYMMDDHHMMSS数字格式表示的日期和时间。例如，输入20121231050505，插入数据库的DATETIME为2012-12-3105:05:05；输入981231050505，插入数据的DATETIME为1998-12-31 05: 05:05。

MySQL允许“不严格”语法：任何标点符号都可以用作日期部分或时间部分之间的间隔符。例如，‘98-12-31 11:30:45’、‘98.12.3111+30+45’、‘98/12/31 113045’和‘98@12@31 113045’是等价的，这些值都可以正确地插入数据库。

TIMESTAMP

TIMESTAMP的显示格式与DATETIME相同，显示宽度固定在19个字符，日期格式为YYYY-MM-DD HH:MM:SS，在存储时需要4字节。TIMESTAMP列的取值范围小于DATETIME的取值范围，为‘1970-01-01 00:00:01’UTC ‘2038-01-19 03:14:07’UTC。其中，UTC（Coordinated Universal Time）为世界标准时间，因此在插入数据时，要保证在合法的取值范围内。

TIMESTAMP与DATETIME除了存储字节和支持的范围不同外，还有一个最大的区别就是：DATETIME在存储日期数据时，按实际输入的格式存储，即输入什么就存储什么，与时区无关；而TIMESTAMP值的存储是以UTC（世界标准时间）格式保存的，存储时对当前时区进行转换，检索时再转换回当前时区。查询时，不同时区显示的时间值是不同的。

文本类型

字符串类型用来存储字符串数据，除了可以存储字符串数据之外，还可以存储其他数据，比如图片和声音的二进制数据。MySQL支持两类字符型数据：文本字符串和二进制字符串。本小节主要讲解文本字符串类型。文本字符串可以进行区分或者不区分大小写的串比较，还可以进行模式匹配查找。在MySQL中，文本字符串类型是指CHAR、VARCHAR、TEXT、ENUM和SET。列出了MySQL中的文本字符串数据类型。

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在值检索时尾部的空格仍保留。

TEXT类型

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

1. TINYTEXT最大长度为255（2^8–1）字符的TEXT列。
2. TEXT最大长度为65535（2^16–1）字符的TEXT列。
3. MEDIUMTEXT最大长度为16777215（2^24–1）字符的TEXT列。
4. LONGTEXT最大长度为4294967295（2^32–1）或4GB字符的TEXT列。

ENUM类型

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

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

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

例如，定义ENUM类型的列('first'，'second'，'third')。

SET类型

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

字段名 SET ('值1','值2'....'值3');

二进制字符串类型

前面讲解了存储文本的字符串类型，这一小节将讲解MySQL中存储二进制数据的字符串类型。MySQL中的二进制数据类型有BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。本节将讲解各类二进制字符串类型的特点和使用方法。

BIT类型

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

默认情况下，MySQL不可以插入超出该列允许范围的值，因而插入的数据要确保插入的值在指定的范围内。

BINARY和VARBINARY类型

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

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

BINARY类型的长度是固定的，指定长度之后，不足最大长度的，将在它们右边填充‘’补齐以达到指定长度。例如：指定列数据类型为BINARY(3)，当插入‘a’时，存储的内容实际为“a”，当插入“ab”时，实际存储的内容为“ab”，不管存储的内容是否达到指定的长度，其存储空间均为指定的值M。VARBINARY类型的长度是可变的，指定好长度之后，其长度可以在0到最大值之间。例如：指定列数据类型为VARBINARY(20)，如果插入的值的长度只有10，则实际存储空间为10加1，即实际占用的空间为字符串的实际长度加1。

BLOB类型

BLOB是一个二进制大对象，用来存储可变数量的数据。BLOB类型分为4种：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB，它们可容纳值的最大长度不同

BLOB列存储的是二进制字符串（字节字符串），TEXT列存储的是非二进制字符串（字符字符串）。BLOB列没有字符集，并且排序和比较基于列值字节的数值；TEXT列有一个字符集，并且根据字符集对值进行排序和比较。

运算符

运算符连接表达式中的各个操作数，其作用是用来指明对操作数所进行的运算。运用运算符可以更加灵活地使用表中的数据，常见的运算符类型有算术运算符、比较运算符、逻辑运算符和位运算符。本节将介绍各种运算符的特点和使用方法。

CREATE TABLE A(
   AGE INT(3)
   );
INSERT INTO A VALUES(20);

算术运算符

算术运算符用于各类数值运算，包括加（+）、减（-）、乘（*）、除（/）、求余（或称模运算，%）。

SELECT AGE+100 FROM A;

SELECT AGE-10 FROM A;

SELECT AGE*10 FROM A;

SELECT AGE/5 FROM A;

SELECT AGE%3 FROM A;

比较运算符

比较运算符用于比较运算，包括大于（>）、小于（<）、等于（=）、大于等于（>=）、小于等于（<=）、不等于（!=），以及IN、BETWEEN AND、ISNULL、GREATEST、LEAST等。

LEAST:语法格式为：LEAST(值1，值2，...，值n)。在有两个或多个参数的情况下，返回最小值。假如任意一个自变量为NULL，则LEAST()的返回值为NULL。

GREATEST:语法格式为：LEAST(值1，值2，...，值n)。当有两个或多个参数时，返回值为最大值。假如任意一个自变量为NULL，则GREATEST()的返回值为NULL。

SELECT * FROM A WHERE AGE>30;

SELECT * FROM A WHERE AGE<23;

逻辑运算符

逻辑运算符的求值所得结果均为1（TRUE）、0（FALSE），这类运算符有逻辑非（NOT或者!）、逻辑与（AND或者&&）、逻辑或（OR或者||）、逻辑异或（XOR）。

逻辑非运算符NOT或者！表示当操作数为0时，所得值为1；当操作数为非零值时，所得值为0；当操作数为NULL时，所得的返回值为NULL。

逻辑与运算符AND或者&&表示当所有操作数均为非零值并且不为NULL时，计算所得结果为1；当一个或多个操作数为0时，所得结果为0；其余情况返回值为NULL。

逻辑或运算符OR或者||表示当两个操作数均为非NULL值且任意一个操作数为非零值时，结果为1，否则结果为0；当有一个操作数为NULL，且另一个操作数为非零值时，则结果为1，否则结果为NULL；当两个操作数均为NULL时，则所得结果为NULL。

逻辑异或运算符XOR表示当任意一个操作数为NULL时，返回值为NULL；对于非NULL的操作数，如果两个操作数都是非0值或者都是0值，则返回结果为0；如果一个为0值、另一个为非0值，返回结果为1。

SELECT AGE>20&&AGE<30 FROM A;

SELECT AGE=20||AGE=30 FROM A;

位运算符

位运算符参与运算的操作数按二进制位进行运算，包括位与（&）、位或（|）、位非（~）、位异或（^）、左移（<<）、右移（>>）6种。

SELECT AGE^5 FROM A;