二. MySQL支持的数据类型

前言

每一个常量、变量和参数都由数据类型，它用来指定一定的存储格式、约束和有效范围。Mysql提供了多种数据类型，主要包括数值型、字符串类型、日期和时间类型。不同的MySQL版本支持的数据类型可能会稍有不同，用户可以通过查询相应版本的帮助文件来获取具体信息。此处以MySQL 5.0为例。

1. 数值类型

1.1 整数类型

整数类型	字节	最小值	最大值
TINYINT	1	有符号-128 无符号0	有符号127 无符号255
SMALLINT	2	有符号-32768 无符号0	有符号32767 无符号255
MEDIUMINT	3	有符号-8388608 无符号0	有符号 8388607 无符号65535
INT、INTEGER	4	有符号-2147483648 无符号0	有符号22147483647 无符号4294967295
BIGINT	8	有符号-9223372036854775808 无符号0	有符号9223372036854775807 无符号18446744073709551615

对于整数数据，MySQL还支持在类型名称后面的小括号内指定显示宽度，例如int(5)表示当数值宽度小于5位的时候，在数字前面填满宽度，如果不显示指定宽度则默认为int(11)。一般配合zerofill使用，顾名思义，zerofill就是用“0”填充的意思。

整数类型还有一个属性：AUTO_INCREMENT。在需要产生唯一标识符或顺序值时，可利用此属性，这个属性只用于整数类型。
AUTO_INCREMENT值一般从1开始，每行增加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));

1.2 小数类型

浮点数类型	字节	最小值	最大值
FLOAT	4	±1.175494351E-38	±3.402823466E+38

定点数类型	字节	描述
DEC(M,D)	M + 2	最大取值范围与DOUBLE相同，给定DECIMAL的有小区至范围由 M 和 D 决定
DECIMAL(M,D)	M + 2	最大取值范围与DOUBLE相同，给定DECIMAL的有小区至范围由 M 和 D 决定

对于小数的表示，MySQL分为两种方式：浮点数和定点数。浮点数包括float（单精度）和double（双精度），而定点数则只有decimal一种表示。
定点数在MySQL内部以字符串形式存放，比浮点数更精确，适合用来表示货币等精度高的数据。
浮点数和定点数都可以用类型名称后加“(M,D)”的方式来进行表示，“(M,D)”表示该值一共显示M为数字（整数位+小数位），其中D位位于小数点后面，M和D又称为精度和标度。例如，定义为float(7,4)的一个列可以显示为-999.9999。MySQL保存值时进行四舍五入，因此如果在float(7,4)列内插入999.00009，近似结果是999.0001。值得注意的是，浮点数后面跟“(M,D)”的用法是非标准用法，如果要用于数据库的迁移，则最好不要这么使用。float和double在不指定精度时，默认会按照实际的精度（由实际的硬件和操作系统决定）来显示，而decimal在不指定精度时，默认的整数位为10，默认的小数位为0。
浮点数如果不写精度和标度，则会按照实际精度值显示，如果由精度和标度，则会自动将四舍五入后的结果插入，系统不会报错。
定点数如果不写精度和标度，则按照默认值decimal(10,0)来进行操作，并且如果数据超越了精度和标度值，系统则会报错

1.3 BIT类型

位类型	字节	最小值	最大值
BIT(M)	1~8	BIT(1)	BIT(64)

用于存放位字段值，BIT(M)可以用来存放多位二进制数，M范围从1~64，如果不写则默认为1位。对于位字段，直接使用SELECT命令将不会看到结果，可以用bin()（显示为二进制格式）或者hex()（显示为十六进制格式）函数进行读取。

1.4 日期时间类型

日期和时间类型	字节	最小值	最大值	零值表示
DATE	4	1000-01-01	9999-12-31	0000-00-00 00:00:00
DATETIME	8	1000-01-001 00:00:00	9999-12-31 23:59:59	0000-00-00
TIMESTAMP	4	19700101080001	2039年的某个时刻	00000000000000
TIME	3	-838:59:59	838:59:59	00:00:00
YEAR	1	1901	2155	0000

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)	M	允许长度0~M个字节的定长字节字符串

2.1 CAHR和VARCHAR类型

• 相同点：用来保存MySQL中较短的字符串。
• 不同点：
  • 存储方式不同
    CHAR存储方式：列的长度固定为创建表时声明的长度，长度可以为从0~255的任意值。
    VARCHAR存储方式：列中的值为可变长字符串，长度可以指定为0~255（MySQL 5.0.3版本以前）或者65535（MySQL5.0.3版本以后）之间的值。
  • 检索时不同
    在检索时，CHAR列删除了尾部的空格
    VARCHAR则保留了这些空格

2.2 BINARY和VARBINARY类型

• 不同点：是否包含二进制字符串
  • BINARY：当存入值的长度小于定义的长度时，会使用0x00（零字节）来填充，以达到指定的字段定义长度。
  • VARBINARY：不会填充零字节。

2.3 ENUM类型

ENUM中文名称叫枚举类型，它的值范围需要在创建表时通过枚举方式显式指定，对1~255个成员的枚举需要1个字节存储；对于255~65535个成员，需要2个字节存储。最多允许有65535个成员。一次只能选取一个成员。类似于下拉菜单。

-- 命令格式
CREATE TABLE tableName(fieldName ENUM(value1,value2,……,valuen));

2.4 SET类型

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

• 1~8成员的集合：占1个字节
• 9~16成员的集合，占2个字节
• 17~24成员的集合，占3个字节
• 25~32成员的集合，占4个字节
• 33~64成员的集合，占8个字节
  SET和ENUM除了存储之外，最主要的区别在于SET类型一次可以选取多个成员，而ENUM则只能选一个。