MySQL8从入门到精通\\数据类型和运算符

数据类型

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

整数型

整数类型的属性字段可以添加AUTO_INCREMENT自增约束条件。

类型名称	说明	存储需求
TINYINT	很小的整数	1字节
SMALLINT	小的整数	2字节
MEDIUMINT	中等大小的整数	3字节
INT(INTEGER)	普通大小的整数	4字节
BIGINT	大整数	8字节

数据类型	有符号	无符号
TINYINT	-128~127	0~255
SMALLINT	-32768~32767	0~65535
MEDIUMINT	-8388608~8388607	0~15777215
INT(INTEGER)	-2147483648~2147483647	0~4294967295
BIGINT	-9223372036854775808~9223372036854775807	0~18446744073709551615

提示：定义数据类型时括号内的数字表示显示宽度。显示宽度和数据类型的取值范围是无关的。显示宽度只是指明MySQL最大可能显示的数字个数，数值的位数小于指定的宽度时会由空格填充；如果插入了大于显示宽度的值，只要该值不超过该类型整数的取值范围，数值依然可以插入，而且能够显示出来。
其他整型数据类型也可以在定义表结构时指定所需要的显示宽度，如果不指定，则系统为每一种类型指定默认的宽度值。这些默认显示宽度能够保证显示每一种数据类型可以取到取值范围内的所有值。

浮点数和定点数

浮点数类型和定点数类型都可以用一组元组（M,N）来表示。其中，M为精度，表示总共的位数；N为标度，表示小数的位数。

类型名称	说明	存储需求
FLOAT	单精度浮点数	4字节
DOUBLE	双精度浮点数	8字节
DECIMAL(M,D)，DEC	压缩的“严格”定点数	M+2字节

DECIMAL类型不同于FLOAT和DOUBLE，DECIMAL实际是以串存放的，可能的最大取值范围与DOUBLE一样，但是其有效的取值范围由M和D的值决定。
提示：不论是定点数还是浮点数类型，如果用户指定的精度超出精度范围，则会四舍五入。
FLOAT和DOUBLE在不指定精度时，默认会按照实际的精度（由计算机硬件和操作系统决定），DECIMAL若不指定精度则默认为(10,0)。
浮点数相对于定点数的优点是在长度一定的情况下，浮点数能够表示更大的数据范围；它的缺点是会引起精度问题。
提示：在MySQL中，定点数以字符串形式存储，在对精度要求比较高的时候（如货币、科学数据等）使用DECIMAL的类型比较好，另外两个浮点数进行减法和比较运算时容易出问题，所以在使用浮点数时需要注意，并尽量避免做浮点数比较。

日期与时间

每一个类型都有合法的取值范围，当指定确实不合法的值时系统将“零”值插入到数据库中。

类型名称	日期格式	日期范围	存储需求
YEAR	YYYY	1901~2155	1字节
TIME	HH:MM:SS	-838:59:59~838:59;59	3字节
DATE	YYYY-MM-DD	1000-01-01~9999-12-31	3字节
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00~9999-12-31 23:59:59	8字节
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:01 UTC~2038-01-19 03:14:07 UTC	4字节

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。

2.TIME

（1）‘D HH:MM:SS’格式的字符串。可以使用下面任何一种“非严格”的语法：‘HH:MM:SS’、‘HH:MM’、‘D HH:MM’、‘D HH’或‘SS’。这里的D表示日，可以取0~34之间的值。在插入数据库时，D被转换为小时保存，格式为“D*24+HH”。
在使用‘D HH’格式时，小时一定要使用双位数值，如果是小于10的小时数，应在前面加0。
（2）‘HHMMSS’格式的、没有间隔符的字符串或者HHMMSS格式的数值，假定是有意义的时间。例如：‘101112’被理解为‘10:11:12’，但‘109712’是不合法的（它有一个没有意义的分钟部分），存储时将变为00:00:00。
提示：为TIME列分配简写值时应注意：如果没有冒号，MySQL解释值时，假定最右边的两位表示秒。例如，读者可能认为‘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。

3.DATE

（1）以‘YYYY-MM-DD’或者‘YYYYMMDD’字符串格式表示的日期，取值范围为‘1000-01-01’～‘9999-12-3’。例如，输入‘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()，插入当前系统日期。注意：CURRENT_DATE只返回当前日期值，不包括时间部分；NOW()函数返回日期和时间值，在保存到数据库时，只保留了其日期部分。

提示：MySQL允许“不严格”语法：任何标点符号都可以用作日期部分之间的间隔符。例如，‘98-11-31’、‘98.11.31’、‘98/11/31’和‘98@11@31’是等价的，这些值也可以正确地插入到数据库中。

4.DATETIME

（1）以‘YYYY-MM-DD HH:MM:SS’或者‘YYYYMMDDHHMMSS’字符串格式表示的值，取值范围为‘1000-01-01 00:00:00’～‘9999-12-3 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。

5.TIMESTAMP

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

文本字符串类型

类型名称	说明	存储需求
CHAR(M)	固定长度非二进制字符串	M字节，1≤M≤255
VARCHAR(M)	变长非二进制字符串	L+1字节，L≤M，1≤M≤255
TINYTEXT	非常小的非二进制字符串	L+1字节，L<2^8
TEXT	小的非二进制字符串	L+2字节，L<2^16
MEDIUMTEXT	中等大小的非二进制字符串	L+3字节，L<2^24
LONGTEXT	大的非二进制字符串	L+4字节，L<2^32
ENUM	枚举类型，只能有一个枚举字符串值	1或2字节，取决于枚举字符串的数目（最大值为65535）
SET	一个设置，字符串对象可以有零个或多个SET成员	1，2，3，4或8字节，取决于集合成员数量（至多64）

1.CHAR和VARCHAR

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

2.TEXT类型

TEXT列保存非二进制字符串，如文章内容、评论等。当保存或查询TEXT列的值时，不删除尾部空格。

3.ENUM类型

ENUM是一个字符串对象，其值为表创建时在列规定中枚举的一列值。

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

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

值	索引
NULL	NULL
"	0
first	1
second	2
third	3

例

mysql> create table tmp
    -> (
    -> soc INT,
    -> level enum('excellent','good','bad')
    -> );
Query OK, 0 rows affected (0.12 sec)

mysql> insert into tmp values(70,'good'),(90,1),(75,2),(50,3);
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from tmp;
+------+-----------+
| soc  | level     |
+------+-----------+
|   70 | good      |
|   90 | excellent |
|   75 | good      |
|   50 | bad       |
+------+-----------+
4 rows in set (0.00 sec)
由结果可以看到，因为ENUM列表中的值在MySQL中都是以编号序列存储的，所以插入列表中的值“good”或者插入其对应序号‘2’的结果是相同的。

提示：ENUM列总有一个默认值：如果将ENUM列声明为NULL，NULL值则为该列的一个有效值，并且默认值为NULL；如果ENUM列被声明为NOT NULL，其默认值为允许的值列表的第1个元素。

4.SET类型

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

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

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

二进制字符串类型

类型名称	说明	存储需求
BIT(M)	位字段类型	大约（M+7/8）字节
BINARY(M)	固定长度二进制字符串	M字节
VARBINARY(M)	可变长度二进制字符串	M+1字节
TINYBLOB(M)	非常小的BLOB	L+1字节，L＜2^8
BOLB(M)	小BLOB	L+2字节，L<2^16
MEDIUMBLOB(M)	中等大小的BLOB	L+3字节，L<2^24
LONGBLOB(M)	大BLOB	L+4字节，L<2^32

1.BIT类型

BIT类型是位字段类型。M表示每个值的位数，范围为1~64。如果M被省略，默认为1。如果为BIT(M)列分配的值的长度小于M位，就在值的左边用0填充。例如，为BIT(6)列分配一个值b’101’，其效果与分配b’000101’相同。

2．BINARY和VARBINARY类型

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

3.BLOB类型

BLOB是一个二进制大对象，用来存储可变数量的数据。

如何选取数据类型

1．整数和浮点数

浮点类型包括FLOAT和DOUBLE类型。DOUBLE类型精度比FLOAT类型高，因此要求存储精度较高时应选择DOUBLE类型。

2．浮点数和定点数

浮点数FLOAT、DOUBLE相对于定点数DECIMAL的优势是：在长度一定的情况下，浮点数能表示更大的数据范围。由于浮点数容易产生误差，因此对精确度要求比较高时，建议使用DECIMAL来存储。DECIMAL在MySQL中是以字符串存储的，用于定义货币等对精确度要求较高的数据。在数据迁移中，float(M,D)是非标准SQL定义，数据库迁移可能会出现问题，最好不要这样使用。另外，两个浮点数进行减法和比较运算时也容易出问题，因此在进行计算的时候，一定要小心。进行数值比较时，最好使用DECIMAL类型。

3．日期与时间类型

4．CHAR与VARCHAR之间的特点与选择

CHAR会自动删除插入数据的尾部空格，VARCHAR不会删除尾部空格。
存储引擎对于选择CHAR和VARCHAR的影响：
●　对于MyISAM存储引擎：最好使用固定长度的数据列代替可变长度的数据列。这样可以使整个表静态化，从而使数据检索更快，用空间换时间。●　对于InnoDB存储引擎：使用可变长度的数据列，因为InnoDB数据表的存储格式不分固定长度和可变长度，因此使用CHAR不一定比使用VARCHAR更好，但由于VARCHAR是按照实际的长度存储的，比较节省空间，所以对磁盘I/O和数据存储总量比较好。

5．ENUM和SET

ENUM和SET的值是以字符串形式出现的，但在内部，MySQL是以数值的形式存储它们的。

6．BLOB和TEXT

BLOB是二进制字符串，TEXT是非二进制字符串，两者均可存放大容量的信息。BLOB主要存储图片、音频信息等，而TEXT只能存储纯文本文件。

运算符

算术运算符

算术运算符用于各类数值运算，包括加（+）、减（-）、乘（*）、除（/）、求余（或称模运算，%）。
在数学运算时，除数为0的除法是没有意义的，因此除法运算中的除数不能为0，如果被0除，则返回结果为NULL。

比较运算符

比较运算符用于比较运算，包括大于（>）、小于（<）、等于（=）、大于等于（>=）、小于等于（<=）、不等于（!=），以及IN、BETWEEN AND、ISNULL、GREATEST、LEAST、LIKE、REGEXP等。
数值比较时有如下规则：
（1）若有一个或两个参数为NULL，则比较运算的结果为NULL。
（2）若同一个比较运算中的两个参数都是字符串，则按照字符串进行比较。
（3）若两个参数均为整数，则按照整数进行比较。
（4）若用字符串和数字进行相等判断，则MySQL可以自动将字符串转换为数字。

运算符	作用
=	等于
<=>	安全等与
<> 或 !=	不等于
<=	小于等于
>=	大于等于
>	大于
IS NULL	判断一个值是否为NULL
IS NOT NULL	判断一个值是否不为NULL
LEAST	在有两个或多个参数时，返回最小值
GREATEST	在有两个或多个参数时，返回最大值
ISNULL	判断一个值是否为NULL
IN
NOT IN
LIKE	通配符匹配
REGEXP	正则表达式匹配

1.安全等于运算符

这个操作符和=操作符执行相同的比较操作，不过<=>可以用来判断NULL值。在两个操作数均为NULL时，其返回值为1，而不为NULL；当一个操作数为NULL时，其返回值为0，而不为NULL。

2.IN、NOT IN运算符

IN运算符用来判断操作数是否为IN列表中的其中一个值：如果是，返回值为1；否则返回值为0。NOT IN运算符用来判断表达式是否为IN列表中的其中一个值：如果不是，返回值为1；否则返回值为0。

3.LIKE

LIKE运算符用来匹配字符串，语法格式为：expr LIKE 匹配条件。如果expr满足匹配条件，则返回值为1（TRUE）；如果不匹配，则返回值为0（FALSE）。expr或匹配条件中任何一个为NULL，则结果为NULL。
LIKE运算符在进行匹配时，可以使用下面的两种通配符：
（1）‘%’，匹配任何数目的字符，甚至包括零字符。
（2）‘_’，只能匹配一个字符。
例

mysql> select 'stud' like 'stud','stud' like 'stu_','stud' like '%d','stud' like 't____','s' like NULL;
+--------------------+--------------------+------------------+---------------------+---------------+
| 'stud' like 'stud' | 'stud' like 'stu_' | 'stud' like '%d' | 'stud' like 't____' | 's' like NULL |
+--------------------+--------------------+------------------+---------------------+---------------+
|                  1 |                  1 |                1 |                   0 |          NULL |
+--------------------+--------------------+------------------+---------------------+---------------+
1 row in set (0.00 sec)

4.REGEXP

REGEXP运算符在进行匹配时，常用的有下面几种通配符：
（1）‘^’匹配以该字符后面的字符开头的字符串。
（2）‘$’匹配以该字符后面的字符结尾的字符串。（
（3）‘.’匹配任何一个单字符。
（4）“[...]”匹配在方括号内的任何字符。例如，“[abc]”匹配“a”“b”或“c”。为了命名字符的范围，使用一个‘-’。“[a-z]”匹配任何字母，而“[0-9]”匹配任何数字。
（5）‘*’匹配零个或多个在它前面的字符。例如，“x*”匹配任何数量的‘x’字符，“[0-9]*”匹配任何数量的数字，而“*”匹配任何数量的任何字符。
例

mysql> select 'ssky' regexp '^s','ssky' regexp 'y$', 'ssky' regexp '.sky', 'ssky' regexp '[ab]';
+--------------------+--------------------+----------------------+----------------------+
| 'ssky' regexp '^s' | 'ssky' regexp 'y$' | 'ssky' regexp '.sky' | 'ssky' regexp '[ab]' |
+--------------------+--------------------+----------------------+----------------------+
|                  1 |                  1 |                    1 |                    0 |
+--------------------+--------------------+----------------------+----------------------+
1 row in set (0.03 sec)

逻辑运算符

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

1.NOT或者!

逻辑非运算符NOT或者！表示当操作数为0时，所得值为1；当操作数为非零值时，所得值为0；当操作数为NULL时，所得的返回值为NULL。
提示：NOT与！优先级不同。

2.AND或者&&

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

3.OR或者||

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

4.XOR

逻辑异或运算符XOR表示当任意一个操作数为NULL时，返回值为NULL；对于非NULL的操作数，如果两个操作数都是非0值或者都是0值，则返回结果为0；如果一个为0值、另一个为非0值，返回结果为1。
提示：a XOR b的计算等同于(a AND (NOT b))或者((NOT a)AND b)

位运算符

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

1.位或运算符（|）

位或运算的实质是将参与运算的几个数据按照对应的二进制数逐位进行逻辑或运算。对应的二进制位有一个或两个为1则该位的运算结果为1，否则为0。
例

mysql> select 10|15,9|4|2;
+-------+-------+
| 10|15 | 9|4|2 |
+-------+-------+
|    15 |    15 |
+-------+-------+

10的二进制数值为1010，15的二进制数值为1111，按位或运算之后，结果为1111，即整数15；9的二进制数值为1001，4的二进制数值为0100，2的二进制数值为0010，按位或运算之后，结果为1111，即整数15。其结果为一个64位无符号整数。

2.位与运算符（&）

位与运算的实质是将参与运算的几个操作数按照对应的二进制数逐位进行逻辑与运算。对应的二进制位都为1则该位的运算结果为1，否则为0。
例

mysql> select 10&15,9&4&2;
+-------+-------+
| 10&15 | 9&4&2 |
+-------+-------+
|    10 |     0 |
+-------+-------+

3.位异或运算符（^）

位异或运算的实质是将参与运算的两个数据按照对应的二进制数逐位进行逻辑异或运算。对应位的二进制数不同时，对应位的结果才为1。如果两个对应位数都为0或者都为1，则对应位的结果为0。
例

mysql> select 10^15,1^1,1^0;
+-------+-----+-----+
| 10^15 | 1^1 | 1^0 |
+-------+-----+-----+
|     5 |   0 |   1 |
+-------+-----+-----+

4.位左移运算符（<<）

位左移运算符<<使指定的二进制值的所有位都左移指定的位数。左移指定位数之后，左边高位的数值将被移出并丢弃，右边低位空出的位置用0补齐。语法格式为：expr<<n。其中，n指定值expr要移位的位数。
例

mysql> select 1<<2,4<<2;
+------+------+
| 1<<2 | 4<<2 |
+------+------+
|    4 |   16 |
+------+------+

5.位右移运算符（>>）

位右移运算符>>使指定的二进制值的所有位都右移指定的位数。右移指定位数之后，右边低位的数值将被移出并丢弃，左边高位空出的位置用0补齐。语法格式为：expr>>n。其中，n指定值expr要移位的位数。
例

mysql> select 16>>2,1>>1;
+-------+------+
| 16>>2 | 1>>1 |
+-------+------+
|     4 |    0 |
+-------+------+

6.位取反运算符（~）

位取反运算的实质是将参与运算的数据按照对应的二进制数逐位反转，即1取反后变为0、0取反后变为1。
例

mysql> select 5 & ~1;
+--------+
| 5 & ~1 |
+--------+
|      4 |
+--------+

提示：MySQL经过位运算之后的数值是一个64位的无符号整数。

运算符的优先级

优先级	运算符
最低	=（赋值运算），:=
.	OR，||
.	XOR
.	&&，AND
.	NOT
.	BETWEEN，CASE，WHEN，THEN，ELSE
.	=（比较运算），<=>，>=，>，<=，<，IS，LIKE，REGEXP，IN
.	|
.	&
.	<<，>>
.	-，+
.	*，%，/
.	^
.	-（负号），~（位反转）
最高	！