二、深入浅出MySQL、数据库开发、优化与管理维护（MySQL支持的数据类型）

本文链接：https://blog.csdn.net/qushaming/article/details/90232566

摘要：数据类型用来指定一定的存储格式、约束和有效范围。MySQL提供的数据类型主要包括：数值型、字符串型、日期型、时间型。其中MySQL的不同版本支持的数据类型会稍有不同，本章节以MySQL5.0为例。

一、数值类型

MySQL支持标准SQL中的所有数值类型，包括如下

严格数值类型：

INTEGER
SMALLINT
DECIMAL
NUMERIC

近似数值数据类型：

FLOAT
REAL
DOUBLE
PRECISION

扩展的数据类型：

TINYINT
MEDIUMINT
BIGINT
BIT

在整数类型中，按照取值范围和存储方式不同，分为TINYINT、SMALLINT、MEDIUMINT、INT和BIGINT5个类型。超出类型范围，会发生"Out of range"错误。MySQL支持在类型名称后的小括号内指定显示宽度INT(5)，不显示指定默认位INT(11)。一般配合ZEROFILL使用，作用即为用0填充。

用例1：创建表t1，由id1和id2两个字段，指定其数值宽度分别为INT和INT(5)
命令：CREATE TABLE t1(id1 INT, id2 INT(5));

用例2：在id1和id2中都插入数值1
命令：INSERT INTO t1 VALUES(1,1);

用例3：分别修改id1和id2的字段类型，加入ZEROFILL参数
命令：ALTER TABLE t1 MODIFY id1 INT ZEROFILL;
命令：ALTER TABLE t1 MODIFY id2 INT(5) ZEROFILL;

注：设置宽度限制后，如果插入大于宽度限制的值，对插入的数据没有任何影响，会按照类型的实际精度惊醒保存，而宽度格式实际已经没有意义，并且不会再填充字符。

整数类型	字节	最小值	最大值
TINYINT	1	有符号 -128 无符号 0	有符号 127 无符号 255
SMALLINT	2	有符号 -32768 无符号 0	有符号 32767 无符号 65535
MEDIUMINT	3	有符号 -8388608 无符号 0	有符号 8388607 无符号 1677215
INT、INTEGER	4	有符号 -2147483648 无符号 0	有符号 2147483647 无符号 4294967295
BIGINT	8	有符号 -9223372036854775808 无符号 0	有符号 9223372036854775807 无符号 18446744073709551615
浮点数类型	字节	最小值	最大值
FLOAT	4	±1.175494351E-38	±3.402823466E±38
DOUBLE	8	±2.2250738585072014E-308	±1.7976931348623157E±308
定点数类型	字节	描述
DEC(M,D)，DECIMAL(M,D)	M+2	最大取值范围与DOUBLE相同，给定DECIMAL的有效取值范围由M和D决定
位类型	字节	最小值	最大值
BIT(M)	1~8	BIT(1)	BIT(64）

表-MySQL中的数值类型

UNSIGNED：所有整数类型都有一个可选属性UNSIGNED(无符号)，用于保存非负数或者作为较大的上限值。其取值范围为正常值的下限取0，上限取原值的2倍。如，TINYINT有符号范围时-128~+127，而无符号范围时0~255。如果一个列指定为欸ZEROFILL，则MySQL自动为该列添加UNSIGNED属性。

AUTO_INCREMENT：此属性可产生唯一标识或顺序值，只用于整数类型。AUTO_INCREMENT值一般从1开始，每行增加1。一个表中最多只有一个AUTO_INCREMENT列，且对于任何想要使用AUTO_INCREMENT的列，应该定义为NOT NULL，并定义为PRIMARY KEY或定义为UNIQUE键。

用例：定义AUTO_INCREMENT列
命令：CREATE TABLE AI(ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY);
命令：CREATE TABLE AI(ID INT AUTO_INCREMENT NOT NULL, PRIMARY KEY(ID));
命令：CREATE TABLE AI(ID INT AUTO_INCREMENT NOT NULL, UNIQUE(ID));

MySQL小数表示：浮点数和定点数。浮点数包括FLOAT(单精度)和DOUBLE(双精度)，而定点数只有DECIMAL。定点数再MySQL内部以字符串形式存放，比浮点数更精确，适合用来表示货币等高精度的数据。

浮点数和定点数均可用"类型名称(M,D)"的方式来表示，M代表总位数(整数位+小数位)，D代表小数点后的位数。例如FLOAT(7,4)的值可以为-999.9999。如果在FLOAT(7,4)中插入999.00009，近似结果时999.0001。

注：浮点数(M,D)是非保准用法，如果要用于数据库迁移，尽量避免这样使用。FLOAT和DOUBLE在不指定精度时，默认会按照实际的精度(由实际的硬件和操作系统决定)来显示，如果指定精度和标度，MySQL保存值时会进行四舍五入。而DECIMAL在不指定精度时，默认整数位位10，小数位为0，如果不写精度和标度，默认按照DECIMAL(10,0)进行操作，如果数据超越精度和标度值系统则会报错。

BIT位数：用于存放位字段值，BIT(M)可以用来存放多位二进制数，M范围从1~64，如果不指定默认位1位。对于位字段，直接使用SELECT命令将无法查看结果，可以使用BIN()(显示为二进制格式)或者HEX()(显示为十六进制格式)函数进行读取。

语法：SELECT BIN(字段名), HEX(字段名) FROM 表名;

二、日期时间类型

日期和时间类型	字节	最小值	最大值	零值表示	说明
DATE	4	1000-01-01	9999-12-31	0000-00-00	年月日
DATETIME	8	1000-01-01 00:00:00	9999-12-31 23:59:59	0000-00-00 00:00:00	年月日时分秒
TIMESTAMP	4	19700101080001	2038年的某个时刻	00000000000000	经常插入或更新日期为当前系统时间时使用，返回值为"YYYY-MM-DD HH:MM:SS"格式的字符串，如果想要获得数字值，应在TIMESTAMP列添加"+0"
TIME	3	-838:59:59	838:59:59	00:00:00	时分秒
YEAR	1	1901	2155	0000	表示年份，有2位或4位格式的年，默认是4位格式。在4位格式中，允许的值是1901~2155和0000。在2位格式中，允许的值是70~69，表示从1970~2069年。MySQL以YYYY格式显示YEAR值(从5.5.27开始，2位格式的year已经不被支持)

DATETIME是DATE和TIME的组合，TIMESTAMP和DATETIME不同，系统会自动为其赋默认值CURRENT_TIMESTAMP(系统时间)。要注意的是，MySQL只给表中的第一个TIMESTAMP字段设置默认值为系统日期，如果有第二个TIMESTAMP类型，则默认值为0。

TIMESTAMP和时区相关。当插入日期时，会先转换为本地时区后存放；而从数据库里面取出时，也同样会将日期转换为本地时区后显示，所以两个不同时区的用户看到的同一个日期可能不一样。

TIMESTAMP和DATETIME的区别主要有以下几点：

TIMESTAMP支持的时间范围较小，其取值范围从19700101080001到2038年的某个时间，而DATETIME是从1000-01-01 00:00:00到9999-12-31 23:59:59，范围更大。
表中的第一个TIMESTAMP列自动设置为系统时间，如果在一个TIMESTAMP列中插入NULL，则该列值将自动设置为当前的日期和时间，在插入或更新一行但不明确给TIMESTAMP列赋值时也会自动设置该列的值为当前的日期和时间，当插入的值超出取值范围时，MySQL认为该值溢出，使用"0000-00-00 00:00:00"进行填补。
TIMESTAMP的插入和查询都受当地时区的影响，更能反应出实际的日期，而DATETIME则只能反映出插入时当地的时区，其他时区的人查看数据必然会有误差的。
TIMESTAMP的属性受MySQL版本和服务器SQLMode的影响很大，本章都是以MySQL5.0为例进行介绍的，对于不同版本可以参考相应的MySQL帮助文档。

日期类型的插入格式多种多样，对于正确插入日期字段到对应格式中，以DATETIME为例：

YYYY-MM-DD HH:MM:SS或YY-MM-DD HH:MM:SS格式的字符串。允许"不严格"语法，即任何标点符都可以用做日期部分和时间部分之间的间隔符。例如，"98-12-31 11:30:45"、"98.12.31 11+30+45"、"98/12/31 11*30*45"和"98@12@31 11^30^45"是等价的。对于包括日期部分间隔符的字符串值，如果日和月的值小于10，不需要指定两位数。"1979-6-9"与"1979-06-09"是相同的，同样对于包括时间部分间隔符的字符串值，如果时、分和秒的值小于10，不需要指定两位数。"1979-10-30 1:2:3"与"1979-10-30 01:02:03"相同。

YYYYMMDDHHMMSS或YYMMDDHHMMSS格式的没有间隔符的字符串，假定字符串对于日期类型是有意义的。例如，"19970523091528"和"970523091528"被解释为"1997-05-23 09:15:28"，但"971122129015"是不合法的（分钟部分超出实际时间范围，没有意义），将变为"0000-00-00 00:00:00"。

YYYYMMDDHHMMSS或YYMMDDHHMMSS格式的数字，假定数字对于日期类型是有意义的。例如，19830905132800和830905132800被解释为"1983-09-05 13:28:00"。数字值应为6、8、12或14位长，如果一个数值是8位或14位长，则假定位YYYYMMDD或YYYYMMDDHHMMSS格式，前4位表示年，如果数字是6位或12位长，则假定为YYMMDD或YYMMDDHHMMSS格式，前2位数表示年。其他数字被解释位仿佛用零填充到最近的长度。

其他数据类型使用原则和上面内容类似，不再赘述。

三、字符串类型

字符串类型	字节	描述及存储需求
CHAR(M)	M	M为0~255之间的整数
VARCHAR(M)		M为0~65535之间的整数，值的长度+1个字节
TINYBLOB		允许长度0~255字节，值的长度+1个字节
BLOB		允许长度0~65535字节，值的长度+2个字节
MEDIUMBLOB		允许长度0~167772150字节，值的长度+3个字节
LONGBLOB		允许长度0~4294967295字节，值的长度+4个字节
TINYTEXT		允许长度0~255字节，值的长度+2个字节
TEXT		允许长度0~65535字节，值的长度+2个字节
MEDIUMTEXT		允许长度0~167772150字节，值的长度+3个字节
LONGTEXT		允许长度0~4294967295字节，值的长度+4个字节
VARBINARY(M)		允许长度0~M个字节的边长字节字符串，值的长度+1个字节
BINARY(M)		允许长度0~M个字节的定常字节字符串

1.CHAR和VARCHAR类型

CHAR和VARCHAR都用来保存MySQL中较短的字符串，二者的主要区别如下：

CHAR列的长度固定为创建表时声明的长度，长度可以为从0~255的任何值；而VARCHAR列的值为可变长字符串，长度可以指定为0~255(MySQL5.0.3以前版本)或者65535(MySQL5.0.3以后版本)之间的值。

在检索的时候，CHAR列删除了尾部空格，而VARCHAR则保留了这些空格。

2. BINARY和VARBINARY类型

BINARY和VARBINARY类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不包含非二进制字符串。当保存BINARY值时，在值的最后通过填充"0x00"(零字节)以达到指定的字段定义长度。

3. ENUM类型

枚举类型，它的值范围需要在创建表时通过枚举方式显式指定，对1~255个成员的枚举需要1个字节存储；对于255~65535个成员，需要2个字节存储，最多允许65535个成员。ENUM类时忽略大小写的，存储时都会转换成大写，对于插入不再ENUM指定范围内的值时，不会返回警告，并且插入的是ENUM的第一个值。另外ENUM类型只允许从集合中选取单个值，而不能一次取多个值。

4. SET类型

SET和ENUM类型非常类似，也是字符串对象，里面可以包含0~64个成员。根据成员的不同，存储上也有所不同。

1~8成员的集合，占1个字节。

9~16成员的集合，占2个字节。

17~24成员的集合，占3个字节。

25~32成员的集合，占4个字节。

33~64成员的集合，占8个字节。

SET和ENUM除了存储之外，最主要的在于SET类型一次可选取多个成员，而ENUM只能选一个。对于超出允许值范围内的值将不允许注入到设置的SET类型列中，而对于包含重复成员的集合将只取一次存入。