mysql笔记：4. 数据类型

数据库中的表由多列字段构成，每一个字段指定了不同的数据类型。指定字段的数据类型之后，也不决定了向字段插入的数据内容，不同的数据类型也决定了MySQL在存储数据时的使用方式。MySQL支持多种数据类型，包括数值类型、浮点型、日期/时间类型和字符串类型等

整数类型

类型	存储需求	取值范围	无符号取值范围
tinyint	1字节	-2^7 ~ 2^7 - 1	0 ~ 2^8-1
smallint	2字节	-2^15 ~ 2^15-1	0 ~ 2^16-1
mediumint	3字节	-2^23 ~ 2^23-1	0 ~ 2^24-1
int(integer)	4字节	-2^31 ~ 2^31-1	0 ~ 2^32-1
bigint	8字节	-2^63 ~ 2^63-1	0 ~ 2^64-1

注意
不同的整数类型，取值范围不同，所需要的存储空间也不同。因此，要根据实际需要选择最合适的数据类型。

浮点数类型和定点数类型

浮点数类型可以用(M,D)来表示，M为精度，表示总共的位数；D为标度，表示小数的位数。其中FLOAT为单精度浮点数类型，DOUBLE为双精度浮点数类型。

类型名称	存储需求	取值范围	无符号的取值范围
FLOAT	4字节	-3.402823466E+38到-1.175494351E-38	0和1.175494351E-38到3.402823466E+38
DOUBLE	8字节	-1.7976931348623157E+308到-2.2250738585072014E-308	0和2.2250738585072014E-308到 1.7976931348623157E+308

注意
这些是理论限制，基于IEEE标准。实际的范围根据硬件或操作系统的不同可能稍微小些。

在mysql中，还可以使用定点数表示小数。

类型名称	存储需求	说明
DECIMAL(M,D), DEC	M+2字节	压缩的严格定点数

DECIMAL类型不同于FLOAT和DOUBLE，它实际是以字符串存储的。它的有效取值范围由M和D决定。如果改变M而固定D，其取值范围将随M的变大而变大。如果固定M而改变D，则其取值范围将随D的变大而变小（精度增加）。

注意
DECIMAL的默认M值是10，D值是0。
DECIMAL整数最大位数(M)为65。支持的十进制数的最大位数(D)是30。
所有DECIMAL列的基本计算(+，-，*，/)用65位精度完成。

日期类型和时间类型

类型名称	日期格式	日期范围	存储需求
YEAR	YY / YYYY	1970到2069 / 1901到2155	1字节
DATE	YYYY-MM-DD	1000-01-01到9999-12-31	3字节
TIME	HH:MM:SS	‘-838:59:59’到’838:59:59’	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’到’2038-01-19 03:14:07’	4字节

注意
TIMESTAMP为UTC时间，转换为东八区就是1970-01-01 08:00:01到2038-01-19 11:14:07

1. YEAR

两位或四位格式的年。默认是四位格式。

四位

可以用四位字符串或四位数字表示，范围是1901~2155。

两位

可以用两位字符串或两位数字表示，范围是00~99。

• 两位字符串：00 ~ 99。00 ~ 69和70 ~ 99范围的值分别为2000 ~ 2069和1970 ~ 1999。
• 两位数字：1 ~ 99。1 ~ 69和70 ~ 99范围的值分别为2001 ~ 2069和1970 ~ 1999。

注意
两位整数范围与字符串范围稍有不同。使用数字表示时，0值被转换为0000，而不是2000。

2. DATE

DATE类型用在只需要日期信息时，没有时间部分。格式为YYYY-MM-DD。
指定方式：

• 以YYYY-MM-DD或YYYYMMDD两种格式的字符串表示
• 以YY-MM-DD或YYMMDD两种格式的字符串表示。这里的YY表示两位的年值。因为不知道是哪个世纪，MySQL使用以下规则解释年值：00 ~ 69和70 ~ 99分别转换为2000 ~ 2069和1970 ~ 1999。
• 以YYYYMMDD或YYMMDD两种格式的数字表示。年与前文相似，00 ~ 69和70 ~ 99分别转换为2000 ~ 2069和1970 ~ 1999。
• 使用CURRENT_DATE或NOW()插入当前日期。

3. TIME

TIME类型用在只需要时间信息时。其小时部分会如此大的原因是它不仅可以表示一天的时间（必须小于24小时），还可以表示某个事件过去的时间或两个事件之间的时间间隔（可以大于24小时，或者为负）。
指定TIME值的方式：

• ‘D HH:MM:SS’ 格式的字符串。也可以使用下面任一种非严格语法：‘HH:MM:SS’/‘HH:MM’/‘D HH:MM’/‘D HH’或’SS’。D表示日，取值范围是0 ~ 34。插入时，D被转换为小时保存，格式为D*24+HH。
• 'HHMMSS’格式的、没有间隔符的字符串或者数值。

指定的数值必须是有意义的，如’101112’被理解为10:11:12，但’109712’是不合法的，存储时将变为00:00:00。

冒号差异

• 如果没有冒号，MySQL假定最右边两位表示秒。（MySQL解释TIME值为过去的时间而不是当天的时间。）例如，'1112’和1112表示00:11:12，'12’和12表示00:00:12。
• 如果有冒号，则被看作当天的时间。即，'11:12’表示11:12:00，而不是00:11:12。

4. DATETIME

DATETIME类型用在需要同时包含日期和时间信息时。格式为：YYYY-MM-DD HH:MM:SS。指定方式：

• 以YYYY-MM-DD HH:MM:SS或者YYYYMMDDHHMMSS两种格式的字符串
• 以YY-MM-DD HH:MM:SS或者YYMMDDHHMMSS两种格式的字符串
• 以YYYYMMDDHHMMSS或者YYMMDDHHMMSS两种格式的数字

5. TIMESTAMP

TIMESTAMP的显示格式与DATETIME相同，也是YYYY-MM-DD HH:MM:SS。但TIMESTAMP的取值范围小于DATETIME的取值范围，为’1970-01-01 00:00:01’ UTC到’2038-01-19 03:14:07’ UTC。世界标准时间（Coordinated Universal Time），即UTC。
DATETIME类型或TIMESTAMP类型与DATE类型可以相互转换，未包含的时间部分会被设置为00:00:00，多出的部分会被删除。

区别
TIMESTAMP与DATETIME除了存储字节和范围不同，还有存储格式不同。DATETIME按照实际输入的格式存储，与时区无关；而TIMESTAMP存储是以UTC格式保存，存储时对当前时区进行转换，检索时再转换回当前时间。即，查询时根据当前时区不同，显示时间值不同。

字符串类型

MySQL支持两类字符串数据：文本字符串和二进制字符串。文本字符串可以进行区分或不区分大小写的串比较，也可以进行模式匹配查找。字符串类型有：CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT。

类型	说明	最大长度	存储需求
CHAR(M)	固定长度的字符串	255	M字节，1<=M<=255
VARCHAR(M)	变长字符串	65535	L+1字节，L<=M和1<=M<=255
TINYTEXT	非常小的字符串	255(2^8-1)字符	L+1字节，L<28
TEXT	字符串	65535(2^16-1)字符	L+2字节，L<216
MEDIUMTEXT	中等字符串	16777215(2^24-1)字符	L+3字节，L<224
LONGTEXT	大字符串	4294967295或4GB(2^32-1)字符	L+4字节，L<232

CHAR和VARCHAR类型

CHAR(M)为固定长度字符串，M表示列长度，M的范围是0 ~ 255。保存CHAR值时在右侧填充空格以达到指定的长度；检索CHAR值时，尾部的空格将被删除。在存储或检索过程中，不进行大小写转换。
VARCHAR(M)是长度可变的字符串，M表示最大列长度，M的范围是0 ~ 65535。VARCHAR最大实际长度由最长的行大小和使用的字符集确定，而其实际占用的空间为字符串的长度加1.
下表显示了将各种字符串值保存到CHAR(4)和VARCHAR(4)列后的结果，说明了CHAR和VARCHAR之间的差别：

值	CHAR(4)	存储需求	VARCHAR(4)	存储需求
‘’	’ ’	4个字节	‘’	1个字节
‘ab’	'ab ’	4个字节	'ab ’	3个字节
‘abcd’	‘abcd’	4个字节	‘abcd’	5个字节
‘abcdefgh’	‘abcd’	4个字节	‘abcd’	5个字节

请注意上表中最后一行的值只适用不使用严格模式时；如果MySQL运行在严格模式，超过列长度不的值不保存，并且会出现错误。

TEXT类型

TEXT列保存非二进制字符串。TEXT类型有四种：TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT。不同类型的存储空间和数据长度不同。

二进制类型

类型名称	说明	最大长度	存储需求
BIT(M)	位字段类型	64	大约(M+7)/8个字节
BINARY(M)	固定长度二进制字符串		M个字节
VARBINARY(M)	变长二进制字符串		M+1个字节
TINYBLOB(M)	小BLOB	255(2^8-1)字节	L+1字节，在此L<2^8
BLOB(M)	BLOB	65535(2^16-1)字节	L+2字节，在此L<2^16
MEDIUMBLOB(M)	中等BLOB	16777215(2^24-1)字节	L+3字节，在此L<2^24
LONGBLOB(M)	大BLOB	4294967295或4GB(2^32-1)字节	L+4字节，在此L<2^32

BIT

BIT类型为位字段类型，M默认是1。

BINARY和VARBINARY

BINARY和VARBINARY类型类似于CHAR和VARCHAR，不同的是它们包含二进制字节字符串。
BINARY长度是固定的。保存时，不足最大长度的，将在它们右边填充值补齐以达到最大长度。

填充值是0x00(零字节)，比较时，0x00字节和空格是不同的，0x00<空格。

BLOB

BLOB是一个二进制大对象，用来存储可变数量的数据。
BLOB列没有字符集，排序和比较基于列值字节的数值。

复合数据类型

MySQL数据库执行两种复合数据类型，分别是ENUM类型和SET类型。
一个ENUM类型只允许从一个集合中取得一个值，而SET类型允许 从一个集合中取得任意多个值。

ENUM类型

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

column_name ENUM(’value1', 'value2', ... , 'valuen')

创建表时，ENUM成员值尾部的空格将被自动删除。

ENUM类型字段在取值时，只能在指定的枚举列表中取，而且一次只能取一个。
在某些情况下，ENUM值也可以为NULL或空字符串(‘’)。

• 如果将一个非法值插入ENUM列，将插入空字符串以作为特殊错误值。该字符串与普通空字符串不同，它有数值值0。
• 如果将ENUM列声明允许NULL，NULL值作为该列的一个有效值，并且默认值也是NULL。如果ENUM列声明为NOT NULL，其默认值为允许的值列的第一个元素。

ENUM值在内部用整数表示，每个枚举值有一个索引值：

• 列表值所允许的成员值从1开始编号
• 空字符串错误值的索引值是0

MySQL存储的就是这个索引值。ENUM最多有65535个元素。
如果在数值上下文中检索一个ENUM值，将返回列值的索引。

drop table if exists enumtest;
create table enumtest(col enum('one', 'two', 'three'));
insert into enumtest values('one'),('two'),('three'),(NULL);
# 检索ENUM
select col, col+0 from enumtest;

将一个数字保存到ENUM列，数字被视为索引，并且保存的值是该索引对应的枚举成员。注意，这不适用于LOAD DATA。
不建议使用类似数字的枚举值来定义一个ENUM列，因为这很容易引起混淆。

SET类型

SET是一个字符串对象，可以有零个或多个值，其值为表创建时规定的一列值。

SET ('value1', 'value2', ... , 'valuen')

指定多个SET成员的列值时，各成员之间用逗号隔开。列值本身不能包含逗号，且尾部的空格将被自动删除。SET最多可以有64个成员。
与ENUM类型相同，SET值在内部用整数表示，列表中每一个值都有一个索引编号。SET值保存时，数值的低阶位对应第一个SET成员。如果在数值上下文中检索SET值，检索的值的位设置成对应组成列值的SET成员。

drop table if exists set1;
create table set1(id int primary key auto_increment, col set('a', 'b', 'c', 'd'));
insert into set1(col) values('a'),('b'),('c'), ('d'), ('a,b'), ('a,c'), ('a,d');
# 从SET列检索数值值
select *, col + 0 from set1;
# 查看SET列所有可能的值
show columns from set1 like 'col';

对于指定为SET(‘a’,‘b’,‘c’,‘d’)的列，成员有下面的十进制和二进制值：

SET成员	十进制值	二进制值
‘a’	1	0001
‘b’	2	0010
‘c’	4	0100
‘d’	8	1000

如果你为该列分配一个数值值9，其二进制形式为1001，因此第1个成员a和第4个成员d被选择，结果为a,d。