mysql8.0 数据类型和运算符

MySQL 数据类型介绍

 

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

Part 1. 整数类型

类型名称	说明	存储需求	有符号范围	无符号范围
TINYINT	很小的整数	1字节	-2^7~2^7-1	0~2^8
SMALLINT	小整数	2字节	-2^15~2^15-1	0~2^16
MEDIUMINT	中等大小的整数	3字节	-2^23~2^23-1	0~2^24
INT(INTEGER)	普通大小的整数	4字节	-2^31~2^31-1	0~2^32
BIGINT	大整数	8字节	-2^63~2^63-1	0~2^64

注释：在MySQL中例如INT(4) 指的是一般显示长度为4，并不是取值范围只能是4位。

一般不指定类型长度会系统指定默认的宽度值。

显示宽度只用于显示，并不能限制取值范围和占用空间。

Part2. 浮点数类型和定点数类型

浮点数分两类：单精度浮点类型（FLOAT）和双精度浮点类型（DOUBLE）

定点数类型：DECIMAL

类型名称	存储需求
FLOAT	4字节
DOUBLE	8字节
DECIMAL(M,D),DEC	M+2字节

 注意：FLOAT和DOUBLE在不给长度和精度的时候，会按照数值具体的精度显示。

DECIMAL的默认长度和精度是DECIMAL(10,0)。

但是不意味着直接使用浮点数会好，书中提到使用浮点数做数值计算往往会出现问题，还是使用定点数类型配上相应精度稳妥。

Part3. 日期与时间类型

主要的时间类型有DATETIME、DATE、TIMESTAMP、TIME和YEAR。

日期与时间数据类型

类型名称	日期格式	日期范围	存储需要
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-3	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		4字节

1. YEAR 

        （1）以4位字符串或者4位数字格式表示的YEAR，范围为‘1901’~‘2155’，输入格式为‘YYYY'或者YYYY。

        （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范围的值分别被转换为2000~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".

        （2）'HHMMSS'格式的、没有间隔符的字符串或者HHMMSS格式的数值，假定是有意义的时间。

3. DATE类型

        （1）以‘YYYY-MM-DD’或者‘YYYYMMDD'字符串格式表示的日期，取值范围为’1000-01-01‘~’9999-12-3‘。

        （2）以’YY-MM-DD'或者‘YYMMDD'字符串格式表示的日期，在这里YY表示两位的年值。包含两位年值的日期会令人模糊，因为不知道世纪。MySQL使用以下规则解释两位年值：’00~69‘范围的年值转换为’2000~2069‘；’70~99‘范围的年值转换为’1970~1999‘。

        （3）以YY-MM-DD或者YYMMDD数字格式表示的日期，与前面相似，00~69范围的年值转换2000~2069，70~99范围的年值转换为1970~1999.

        （4）使用CURRENT_DATE 或者 NOW()，插入当前系统日期。

4. DATETIME

        （1）以’YYYY-MM-DD HH：MM：SS'或者‘YYYYMMDDHHMMSS'字符串格式表示的值，取值范围为’1000-01-01 00：00：00‘~’9999-12-3 23：59：59‘。

        （2）以’YY-MM-DD HH：MM：SS'或者‘YYMMDDHHMMSS’字符串格式表示的日期，这里YY表示两位的年值。

        （3）以YYYYMMDDHHMMSS或者YYMMDDHHMMSS数字格式表示的日期和时间。

5. 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）为世界标准时间，因此在插入数据时，要保证在合法的取值范围内。

Part 4. 文本字符串类型

类型名称	说明	存储需求
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字节，取决于枚举值的数目
SET	一个设置，字符串对象可以有零个或多个SET成员	1、2、3、4或8字节，取决于集合成员的数量

1. CHAR和VARCHAR类型

CHAR(M)为固定长度字符串，在定义时指定字符串列长。当保存时在右侧填充空格，以达到指定的长度。M表示列长度，M的范围是0~255个字符。当检测到末尾的空格是会被删除。

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

CHAR(4)与VARCHAR(4)存储区别

插入值	CHAR(4)	存储需求	VARCHAR(4)	存储需求
’‘	’    ‘	4字节	''	1字节
'ab'	'ab  '	4字节	'ab'	3字节
'abc'	'abc '	4字节	'abc'	4字节
’abcd'	'abcd'	4字节	'abcd'	5字节
'abcdef'	'abcd'	4字节	'abcd'	5字节

CHAR(4)定义了固定长度为4的列，不管存入的数据长度为多少，所占用的空间均为4个字节；VARCHAR(4)定义的列所占的字节数为实际长度加1.

表格中最后一行的值只有在使用”不严格“模式时，字符串才会被截断插入；如果MySQL运行在”严格“模式，超过列长度的值不会被保存，并且会出现错误信息”ERROR 1406(22001):Data not long for column", 即字符串长度超过指定长度，无法插入。

2. TEXT类型

TEXT 列保存非二进制字符串，如文章内容、评论等。当保存或查询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列。

3. ENUM类型

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

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

ENUM类型的取值范围

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

提示：

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

 4. SET 类型

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

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

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

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

PART 5. 二进制字符串类型

MySQL中的二进制字符串类型

类型名称	说明	存储需求
BIT(M)	位字段类型	大约(M+7)/8字节
BINARY(M)	固定长度二进制字符串	M字节
VARBINARY(M)	可变长度二进制字符串	M+1字节
TINYBLOB(M)	非常小的BLOB	L+1字节，在此L<2^8
BLOB(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'相同。BIT数据类型用来保存位字段值。例如，以二进制的形式保存数据13（13的二进制喜欢那个是位1101），在这里需要位数至少为4位的BIT类型，即可以定义列类型位BIT(4),大于二进制1111的数据是不能插入BIT(4)类型的字段中的。

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

2. BINARY和VARBINARY类型

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

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

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

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

3. BLOB类型

BLOB类型的存储范围

数据类型	存储范围
TINYBLOB	最大长度为255(2^8-1)B
BLOB	最大长度为(2^16-1)B
MEDIUMBLOB	最大长度为(2^24-1)B
LONGBLOB	最大长度为(2^32-1)B或者4GB