MySQL数据类型

• 数据类型（data_type）是指系统中所允许的数据类型。
• 数据库中的每个列都应该有适当的数据类型，用于限制或允许该列中存储的数据。例如，列中的存储的为数据，则相应的数据类型应该为数值类型。使用数据类型有助于对数据进行正确的排序，并在优化磁盘使用方面起着重要作用。因此，在创建表时必须为每个列设置正确的数据类型极可能的长度。
• MySQL的数据类型分别有整数、浮点数和定点数类型、日期和时间类型、字符串类型和二进制类型等。

1 整数类型

• 数值型数据类型主要用来储存数字，MySQL提供了多种数值型数据类型，不同的数据类型提供了不同的取值范围，可以存储的值范围越大，所需的存储空间也越大。MySQL主要提供的整数类型有TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，其属性字段可以添加AUTO_INCREMENT自增约束条件。表1列出了MySQL中的数值类型。

•                                            表 1 MySQL中的整数数据类型
  

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

• 从表1可以看到，不同类型的整数储存所需的字节数不同，占用字节数最小的是TINYINT类型，占用字节数最大的是BIGINT类型，占用的字节越多的类型所能表示的书值范围越大。根据占用字节数可以求出每一种数据类型的取值范围。例如，TINYINT需要1个字节（8bit）来存储，那么TINYINT无符号数的最大值为28-1，即255；TINYINT有符号数的最大值为27-1，即127.其他类型的整数的取值范围计算方法相同，如表2所示。

•                                            表 2 不同整数类型的取值范围
  

类型名称	说明	存储需求
TINYINT	-128~127	0~255
SMALLINT	-32768~32767	0~65535
MEDIUMINT	-8388608~8388607	0~16777215
INT（INTEDER）	-2147483648~2147483647	0~4294967295
BIGINT	-9223372036854775808~9223372036854775807	0~18446744073709551615

• 提示：显示宽度和数据类型的取值范围是无关的。显示宽度只是指明MySQL最大可能显示的数字个数，数值的位数小于指定的宽度时会由空格填充。如果插入了大于显示宽度的值，只要该值不超过该类型整数的取值范围，数值依然可以插入，而且能够显示出来。例如，year字段插入19999，当使用SELECT查询该列值的时候，MySQL显示的将是完整的带有5位数字的19999，而不是4位数字的值。
• 其他整型数据类型也可以在定义表结构时指定所需的显示宽度，如果不指定，则系统为每一种类型指定默认的宽度值。

2 浮点数类型和定点数类型

MySQL中使用浮点数和定点数来表示小数。浮点类型有两种：单精度浮点数（FLOAT）和双精度浮点数（DOUBLE）。定点类型只有一种：DECIMAL。浮点类型和定点类型都可以用（M,N）来表示，其中M称为精度，表示总共的位数；N称为标度，表示小数的位数。表3列出了MySQL中的小数类型和存储需求。

•                                              表 3 MySQL中的小数类型
  

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

DECIMAL类型不同于FLOAT和DOUBLE。DOUBLE实际上事宜串存放的，DECIMAL可能的最大取值范围与DOUBLE相同，但是有效的取值范围有M和D决定。如果改变M而固定D，则取值范围将随M的变大而变大。如果改变M而固定D，则取值范围将随M的变大而变大。从表3可以看出，DECIMAL的存储空间并不是固定的，而由精度值M决定，占用M+2个字节。

FLOAT类型的取值范围如下。

• 有符号的取值范围：-3.402823466E+38~-1.175494351E-38
• 无符号的取值范围：0和-1.175494351E-38~-3.402823466E+38

DOUBLE类型的取值范围如下。

• 有符号的取值范围：-1.7976931348623157E+308~-2.2250738585072014E-308
• 无符号的取值范围：0和-2.2250738585072014E-308~-1.7976931348623157E+308
  提示：无论是定点还是浮点类型，如果用户指定的精度超出精度范围，则会四舍五入进行处理。

FLOAT和DOUBLE在不指定精度时，默认会按照实际的精度（有计算机硬件和操作系统决定），DECIMAL如果不指定精度，默认（10，0）。
浮点数相对于定点数的优点是在长度一定的情况下，浮点数能够表示更大的范围；缺点是会引起精度问题。

• 提示：在MySQL中，定点数以字符串的形式存储，在对精度比较高的时候（如货币、科学数据），使用DECIMAL的类型比较好，另外两个浮点数进行减法和比较运算时也容易出问题，所以在使用浮点数时需要注意，并尽量避免做浮点数比较。

3 日期和时间类型

MySQL中有多处表示日期的数据类型：YEAR、TIME、DATE、DTAETIME、TIMESTAMP。当只记录年信息时候，可以只使用YEAR类型。每一个类型都有合法的取值范围，当指定确定不合法的值时，系统将“零”值插入数据库中，表4列出了MySQL中的日期与时间类型。

•                                              表 4 日期与时间类型
  

类型名称	日期格式	日期范围	存储需求
YEAR	YYYY	1901~2155	一个字节
TIME	HH：MM：SS	-838：59：59~838：59：59	3个字节
DATE	YYYY-MM-DD	1000-01-01~999-12-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-DD	1980-01-01 00：00：01 UTC~2040-01-19 03：14：07 UTC	4个字节

（1）YEAR类型
YEAR类型是一个单位字节类型，用于表示年，在储存时只需要一个字节。可以使用各种格式指定YEAR，如下所示。

• 以4位字符串或者4位数字格式表示的YEAR，范围为‘1901’~‘2155’，输入格式为‘YYYY’或者YYYY，例如，输入‘2010’或2010，插入数据库的值均为2010.
• 以2位字符串格式表示的YEAR，范围为‘00’~‘99’。‘00’-‘69’和‘70’-‘99’范围的值分别被转化为2000-2069和1970-1999范围的YEAR值。‘0’与‘00’的作用相同。插入超过取值范围的值将会被转换为2000.
• 以2位数字表示的YEAR，范围为1~99 . 1-99和70-99范围的值分别被转换为2001-2069和1970-1999范围的YEAR值。注意，在这里0值将被转换为0000，而不是2000.

（2）TIME类型
TIME类型用于只需要时间信息的值，在存储是需要3个字节。格式为HH：MM：SS。HH表示小时，MM表示分钟，SS表示秒。TIME类型的取值范围为-838：59：59~838：59：59，小时部分如此大的原因是TIME类型不仅可以用于表示一天的时间（必须小于24小时），还可能是某个事件过去的时间或两个事件之间的时间间隔（可大于24小时，或者甚至为负）。可以使用各种格式指定TIME值，如下所示。

• ‘D HH：MM：SS’格式的字符串。还可以使用这些“非严格”的语法：‘HH：MM：SS’、‘HH：MM’、‘D HH’或‘SS’。这里的D表示日，可以取0~34之间的值。如果插入数据库时，D被转换为小时保存，格式为“D*224+HH”。
• ‘HHMMSS’格式、没有间隔符的字符串或者HHMMSS格式的数值，假定是有意义的时间。例如，‘101112’被理解为‘10：11：12’，但是‘101112’是不合法的（他有一个没有意义的分钟部分），在存储时将变为00：00：00。

（3）DATE类型
DATE类型用于用于仅需要日期值时，没有时间部分，在存储时需要三个字节。日期格式为‘YYYY-MM-DD’，其中YYYY表示年，MM表示月，DD表示日。在给DATE类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，只要符合DATE的日期格式即可。

• 使用CURRENT_DATE或者NOW()，插入当前系统日期。
• 提示：MySQL允许不严格的语法：任何标点符号都可以用作日期部分之间的间隔符。例如，‘98-11-31’、‘98.111.31’、‘98/11/31’和‘98@11@31’是等价的，这些值也可以正确地插入数据库。

（4）DATETIME类型
DATETIME类型用于需要同时包含日期和时间信息的值，在存储时需要8个字节。日期格式为‘YYYY-MM-DD HH：MM：SS’或者‘YYYYMMDDHHMMSS’，其中YYYY表示年，MM表示月，DD表示日，HH表示小时，MM表示分钟，SS表示秒。在给DATETIME类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，只要符合DATETIME的日期格式即可，如下所示

• 以‘YYYY-MM-DD HH：DD：SS’或者‘YYYYMMDDHHMMSS’字符串格式表示的日期，取值范围为‘1000-01-01 00：00:00’~‘9999-12-3 23:59:59’
• 以‘YY-MM-DD HH：MM：SS’或者‘YYMMDDHHMMSS’字符串格式表示的日期，在这里YY表示两位的年值。
• 以‘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。在插入数据时要保证合法的取值范围内。

• 提示：协调世界时（英：Coordinated Universal Time，法：Temps Universel Coordonne）又称为世界统一时间、世界标准时间、国际协调时间。英文（CUT）和法文（TUC）的缩写不同，作为妥协，简称UTC。
• TIMESTAMP与DATETIME除了存储字节和支持的范围不同外，还有一个最大的区别是：DATETIME在存储日期数据时，按实际输入的格式存储，即输入什么就存储什么，与时区无关；而TIMESTAMP值的存储是以UTC（世界标准时间）格式保存的，存储时对当前时区进行转换，检索时在转换回当前时区。即查询时，根据当前时区的不同，显示的时间只是不同的。

4 字符串和二进制类型

字符串类型用来存储字符串数据，还可以存储图片和声音的二进制数据。字符串可以区分或者不区分大小写的串比较，还可以，还可以进行正则表达式的匹配查找。MySQL中的字符串类型有CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM、SET等。表5列出了MySQL中字符串数据类型。

•                                        表 5 MySQL中的字符串数据类型
  

类型名称	说明	存储需求
CHAR（M）	固定长度非二进制字符串	M字节，1<=<=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个成员）

VARCHAR、BLOB、和TEXT类型是变长类型，其存储需求取决于列值的实际长度，而不是取决于类型的最大可能尺寸。例如，一个VARCHAR(10)列能保存一个最大长度为10个字符的字符串，实际的存储需要字符串的长度L加上一个字节以记录字符串的长度。对于字符‘abcd’，L是4，而存储要求5个字节。

• （1）CHAR和VARCHAR类型
  CHAR（M）为固定长度字符串，在定义时指定字符串列长。当保存时，在右侧填充空格以达到指定的长度。M表示列的长度，范围是0~255个字节。例如，CHAR（4）定义了一个固定长度的字符串列，包含的字符个数最大为4.当检索到CHAR值时，尾部的空格将被删除。
  VARCHAR（M）是长度可变的字符串，M表示最大列的长度，M的范围是0~65535。VARCHAR的最大实际长度由最长的行的大小和使用的字符集确定，而实际占用的空间为字符串的实际长度加1.例如，VARCHAR（50）定义了一个最大长度为50的字符串，如果插入的字符串有10个字符和一个字符串结束字符。VARCHAR在值保存和检索时尾部的空格仍然保留。

• （2）TEXT类型
  TEXT列保存非二进制字符串，如文章内容、评论等。当保存或查询TEXT列的值时，不删除尾部空格。TEXT类型分为4种:TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。不同的TEXT类型的存储空间和数据长度不同。
  TINYTEXT表示长度为255（28-1）字符的TEXT列。
  TEXT表示长度为65535（216-1）字符的TEXT列。
  MEDIUMTEXT表示长度为16777215（244-1）字符的TEXT列。
  LONGTEXT表示长度为4294967295或者4GB（232-1）字符的TEXT列。

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

<字段名> ENUM('值0'，‘值1’，...'值n'）

字段名指将要定义的字段，值n指枚举列表中的第n个值。ENUM类型的字段在取值时，能在指定的枚举列表中获取，而且一次只能取一个。如果创建的成员有空格，尾部的空格将自动删除。ENUM值在内部用整数表示，每个枚举值均有一个索引值；列表值所允许的成员值从1开始编号，MySQL存储的就是这个索引编号，枚举最多可以有65535个元素。例如定义ENUM类型的列（‘first’，‘second’，‘third’），该列可以取的值和每个值的索引如表6所示。

•                                          表 6 ENUM类型的取值范围
  

值	索引
NULL	NULL
“	0
’first	1
second	2
third	3

ENUM值依照列索引顺序排列，并且空字符串排在非空字符串前，NULL值排在其他所有枚举值前。

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

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

    SET('值1'，‘值2’，...,‘值n’)
  

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

• 提示：如果插入SET字段中的列值有重复，则MySQL自动删除重复的值；插入SET字段的值的顺序并不重要，MySQL会在存入数据库时，按照定义的顺序显示；如果插入了不正确的值，默认情况下，MySQL将忽视这些值，给出警告。
  MySQL支持两类字符型数据：文本字符串和二进制字符串。MySQL中的二进制字符串有BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。表7列出了MySQl中二进制数据类型。

•                               表 7 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类型
位字段类型。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)类型的字段中的。

(2) BINARY和VARBINARY类型
BINARY和VARBINARY类型类似于CHAR和VARCHAR，不同的是他们包含二进制字节字符串。使用语法如下：

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

BINARY类型的长度是固定的，指定长度后，不足最大长度的，将在他们右边填充“\0”补齐，以达到指定长度。例如，指定列数据类型为BINARY(3)，当插入a时，存储的内容实际为“\a0\0”，当插入ab时，实际存储的内容为“ab\0”，无论存储的内容是否达到指定长度，存储空间均为指定的值M。
VARBINARY类型的长度是可变的，指定好长度后，长度可以在零到最大值之间。例如指定列数据类型为VARBINARY(20)，如果插入的值长度只有10，则实际的存储空间为10加1，实际占用的空间为字符串的实际长度加1。

（3）BLOB类型
BLOB是一个二进制的对象，用来存储可变数量的数据。BLOB类型分为4种：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB，它们可容纳值的最大长度不同，如表8所示。

•                                       表 8 MySQL中的二进制数据类型
  

数据类型	存储范围
TINYBLOB	最大长度为255(2^8-1)
BLOB	最大长度为65535（2^16-1）
MEDIUMBLOB	最大长度为16777215（2^24-1）
LONGBLOB	最大长度为4294967295或4GB（2^31-1)

BLOB列存储的是二进制字符串（字节字符串），TEXT列存储的是非二进制字符串(字符字符串)。BLOB列是字符集，并且排序和比较基于列值字节的数值；TEXT列有一个字符集，并且根据字符集对值进行排序和比较。