MySQL 数据类型

转自：http://c.biancheng.net/view/2425.html

MySQL常用的数据类型：

1. 数字类型

　　包括 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，浮点数类型 FLOAT 和 DOUBLE，定点数类型 DECIMAL。

2. 时间日期

　　YEAR、TIME、DATE、DATETIME 和 TIMESTAMP。

3. 字符串类型

　　 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和 SET 等。

4. 二进制

　　BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。

一、整数型

TINYINT　　　　-128~127　　　　　　　　　　　　　　　　　　　　1字节

SMALLINT　　   -32768~32767　　　　　　　　　　　　　　　　　　2字节

MEDIUMINT　　-8388608〜8388607　　　　　　　　　　　　　　　  3 字节

INT(INTEGER)  -2147483648〜2147483647　　　　　　　　　　　　  4字节

BIGINT　　　　-9223372036854775808〜9223372036854775807　　  8字节

二、小数型

1. 浮点数：单精度浮点数（FLOAT）和双精度浮点数（DOUBLE）

2. 定点类型 DECIMAL

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。

DECIMAL (M, D)，DEC　  M+2个字节

在MySQL中，定点数以字符串形式存储，DECIMAL 如果不指定精度，默认为（10，0）。如果改变 M 而固定 D，则取值范围将随 M 的变大而变大。

三、时间和日期

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 　　   1980-01-01 00:00:01 UTC ~ 2040-01-19 03:14:07 UTC 　 4 个字节

YEAR 类型

YEAR 类型是一个单字节类型，用于表示年，在存储时只需要 1 个字节。可以使用各种格式指定 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。

提示：两位整数范围与两位字符串范围稍有不同。例如，插入 3000 年，读者可能会使用数字格式的 0 表示 YEAR，实际上，插入数据库的值为 0000，而不是所希望的 3000。只有使用字符串格式的 '0' 或 '00'，才可以被正确解释为 3000，非法 YEAR值将被转换为 0000。

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*24+HH”。
• 'HHMMSS' 格式、没有间隔符的字符串或者 HHMMSS 格式的数值，假定是有意义的时间。例如，'101112' 被理解为'10：11：12'，但是 '106112' 是不合法的（它有一个没有意义的分钟部分），在存储时将变为 00：00：00。

提示：为 TIME 列分配简写值时应注意：如果没有冒号，MySQL 解释值时，假定最右边的两位表示秒。（MySQL 解释 TIME 值为过去的时间而不是当前的时间）。例如，读者可能认为 '1112' 和 1112 表示 11：12：00（即 11 点过 12 分钟），但MySQL 将它们解释为 00：11：12（即 11 分 12 秒）。同样 '12' 和 12 被解释为00：00：12。相反，TIME 值中如果使用冒号则肯定被看作当天的时间，也就是说，'11：12' 表示 11：12：00，而不是 00：11：12。

DATE 类型

DATE 类型用于仅需要日期值时，没有时间部分，在存储时需要 3 个字节。日期格式为 'YYYY-MM-DD'，其中 YYYY 表示年，MM 表示月，DD 表示日。

在给 DATE 类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，只要符合 DATE 的日期格式即可。如下所示：

• 以 'YYYY-MM-DD' 或者 'YYYYMMDD' 字符中格式表示的日期，取值范围为 '1000-01-01'～'9999-12-3'。例如，输入 '2015-12-31' 或者 '20151231'，插入数据库的日期为2015-12-31。
• 以 'YY-MM-DD' 或者 'YYMMDD' 字符串格式表示日期，在这里YY表示两位的年值。MySQL 解释两位年值的规则：'00～69' 范围的年值转换为 '2000~2069'，'70~99' 范围的年值转换为 '1970～1999'。例如，输入 '15-12-31'，插入数据库的日期为 2015-12-31；输入 '991231'，插入数据库的日期为 1999-12-31。
• 以 YYMMDD 数字格式表示的日期，与前面相似，00~69 范围的年值转换为 2000～2069，80～99 范围的年值转换为 1980～1999。例如，输入 151231，插入数据库的日期为 2015-12-31，输入 991231，插入数据库的日期为 1999-12-31。
• 使用 CURRENT_DATE 或者 NOW()，插入当前系统日期。

提示：MySQL 允许“不严格”语法：任何标点符号都可以用作日期部分之间的间隔符。例如，'98-11-31'、'98.11.31'、'98/11/31'和'98@11@31' 是等价的，这些值也可以正确地插入数据库。

DATETIME 类型

DATETIME 类型用于需要同时包含日期和时间信息的值，在存储时需要 8 个字节。日期格式为 'YYYY-MM-DD HH：MM：SS'，其中 YYYY 表示年，MM 表示月，DD 表示日，HH 表示小时，MM 表示分钟，SS 表示秒。

在给 DATETIME 类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，只要符合 DATETIME 的日期格式即可，如下所示。

• 以 'YYYY-MM-DD HH：MM：SS' 或者 'YYYYMMDDHHMMSS' 字符串格式表示的日期，取值范围为 '1000-01-01 00：00：00'～'9999-12-3 23：59：59'。例如，输入 '2014-12-31 05：05：05' 或者 '20141231050505’，插入数据库的 DATETIME 值都为 2014-12-31 05：05：05。
• 以 'YY-MM-DD HH：MM：SS' 或者 'YYMMDDHHMMSS' 字符串格式表示的日期，在这里 YY 表示两位的年值。与前面相同，'00～79' 范围的年值转换为 '2000～2079'，'80～99' 范围的年值转换为 '1980～1999'。例如，输入 '14-12-31 05：05：05'，插入数据库的 DATETIME 为 2014-12-31 05：05：05；输入 141231050505，插入数据库的 DATETIME 为 2014-12-31 05：05：05。
• 以 YYYYMMDDHHMMSS 或者 YYMMDDHHMMSS 数字格式表示的日期和时间。例如，输入 20141231050505，插入数据库的 DATETIME 为 2014-12-31 05：05：05；输入 140505050505，插入数据库的 DATETIME 为 2014-12-31 05：05：05。

提示：MySQL 允许“不严格”语法：任何标点符号都可用作日期部分或时间部分之间的间隔符。例如，'98-12-31 11：30：45'、'98.12.31 11+30+35'、'98/12/31 11*30*45' 和 '98@12@31 11^30^45' 是等价的，这些值都可以正确地插入数据库。

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 Coordonné）又称为世界统一时间、世界标准时间、国际协调时间。英文（CUT）和法文（TUC）的缩写不同，作为妥协，简称 UTC。

TIMESTAMP 与 DATETIME 除了存储字节和支持的范围不同外，还有一个最大的区别是：

• DATETIME 在存储日期数据时，按实际输入的格式存储，即输入什么就存储什么，与时区无关；
• 而 TIMESTAMP 值的存储是以 UTC（世界标准时间）格式保存的，存储时对当前时区进行转换，检索时再转换回当前时区。即查询时，根据当前时区的不同，显示的时间值是不同的。

提示：如果为一个 DATETIME 或 TIMESTAMP 对象分配一个 DATE 值，结果值的时间部分被设置为 '00：00：00'，因此 DATE 值未包含时间信息。如果为一个 DATE 对象分配一个 DATETIME 或 TIMESTAMP 值，结果值的时间部分被删除，因此DATE 值未包含时间信息。

 四、字符串类型

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

　　TEXT 类型分为 4 种：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。不同的 TEXT 类型的存储空间和数据长度不同。

• TINYTEXT 表示长度为 255（28-1）字符的 TEXT 列。
• TEXT 表示长度为 65535（216-1）字符的 TEXT 列。
• MEDIUMTEXT 表示长度为 16777215（224-1）字符的 TEXT 列。
• LONGTEXT 表示长度为 4294967295 或 4GB（232-1）字符的 TEXT 列。

MEDIUMTEXT：　　中等大小的非二进制字符串；　　　　　　　　　　　　　　　　　  L+3字节，在此，L<2^24

LONGTEXT ：　　   大的非二进制字符串 ；　　　　　　　  　　　　　　　　　　　　　L+4字节，在此，L<2^32

ENUM ：　　　　    枚举类型，只能有一个枚举字符串值 ；　　　　　　　　　　　　　  1或2个字节，取决于枚举值的数目 (最大值为65535)

SET ：　　　　        一个设置，字符串对象可以有零个或 多个SET成员，自动去重；　 　 1、2、3、4或8个字节，取决于集合 成员的数量（最多64个成员）

 五、二进制类型

MySQL 中的二进制字符串有 BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。

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

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 不可以插入超出该列允许范围的值，因而插入数据时要确保插入的值在指定的范围内。

BINARY 和 VARBINARY 类型

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

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

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

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

BLOB 类型

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

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