MySQL 数据类型

MySQL 的数值数据类型可以大致划分为两个类别，一个是整数，另一个是浮点数或小数。

一、整型

         整数类型是数据库中最基本的数据类型。标准SQL中支持INTEGER和SMALLINT这两种整数类型。MySQL数据库除了支持这两种类型外，还扩展支持了TINYINT、MEDIUMINT和BIGINT。

         各种整数类型的取值范围、存储的字节数如下：

整型	字节数	无符号数的取值范围	有符号数的取值范围
TINYINT	1	0~255	128~127
SMALLINT	2	0~65535	-32768~12767
MEDIUMINT	3	0~16777215	-8388608~8388607
INT	4	0~4294967295	-2147483648~2147483647
INTEGER	4	0~4294967295	-2147483648~2147483647
BIGINT	8	0~18446744073709551615	-9223372036954775808~9223372036854775807

         

       MySQL 以一个可选的显示宽度指示器的形式对 SQL 标准进行扩展，这样当从数据库检索一个值时，可以把这个值加长到指定的长度。例如，指定一个字段的类型为 INT(6)，就可以保证所包含数字少于 6 个的值从数据库中检索出来时能够自动地用空格填充。需要注意的是，使用一个宽度指示器不会影响字段的大小和它可以存储的值的范围。

　　万一我们需要对一个字段存储一个超出许可范围的数字，MySQL 会根据允许范围最接近它的一端截短后再进行存储。还有一个比较特别的地方是，MySQL 会在不合规定的值插入表前自动修改为 0。

　　UNSIGNED 修饰符规定字段只保存正值。因为不需要保存数字的正、负符号，可以在储时节约一个“位”的空间。从而增大这个字段可以存储的值的范围。

　　ZEROFILL 修饰符规定 0（不是空格）可以用来真补输出的值。使用这个修饰符可以阻止 MySQL 数据库存储负值。

 

 二、浮点数类型和定点数类型

         MySQL使用浮点数类型和定点数类型来表示小数。浮点数类型包括单精度浮点数(FLOAT类型)和双精度浮点数(DOUBLE类型)。定点数类型就是DECIMAL型。

         FLOAT型、DOUBLE型、DECIMAL型的取值范围、存储的字节数如下：

小数类型	字节数	负数取值范围	无符号取值范围
FLOAT	4	-3.402823466E+38~ 0和1.175494351E-38 ~ -1.175494351E-38	3.402823466E+38
DOUBLE	8	1.7976931348623157E+308 ~ 0和2.2250738585072014E ~ -2.2250738585072014E-308	1.7976931348623157E+308
DECIMAL(M,D)或DEC(M,D)	M+2	DOUBLE型	同DOUBLE型

         M:最大长度（包括小数部分，但不包括小数点）

         D:小数点后保留长度

        与整数一样，这些类型也带有附加参数：一个显示宽度指示器和一个小数点指示器。比如语句 FLOAT(7,3) 规定显示的值不会超过 7 位数字，小数点后面带有 3 位数字。

         对于浮点数和定点数，当插入值的精度高于实际定义的精度，系统会自动进行四舍五入处理。其目的是为了使该值的精度达到要求。浮点数进行四舍五入不会报警，定点数出现警告。

        DECIMAL 数据类型用于精度要求非常高的计算中，这种类型允许指定数值的精度和计数方法作为选择参数。精度在这里指为这个值保存的有效数字的总个数，而计数方法表示小数点后数字的位数。比如语句 DECIMAL(7,3) 规定了存储的值不会超过 7 位数字，并且小数点后不超过 3 位。

         在未指定精度的情况下，浮点数和定点数有其默认的精度。FLOAT类型和DOUBLE类型默认会保存实际精度。这个精度与操作系统和硬件的精度有关。DECIMAL类型默认整数位为10, 小数位为0，即默认为整数。  

         在MySQL中，定点数精度比浮点数要高。而且，浮点数会出现误差。如果要对数据的精度要求比较高，应该选择定点数。

        UNSIGNED 和 ZEROFILL 修饰符也可以被 FLOAT、DOUBLE 和 DECIMAL 数据类型使用。并且效果与 INT 数据类型相同。

 

 三、日期和时间类型

        在处理日期和时间类型的值时，MySQL 带有 5 个不同的数据类型可供选择。它们可以被分成简单的日期、时间类型，和混合日期、时间类型。根据要求的精度，子类型在每个分类型中都可以使用，并且 MySQL 带有内置功能可以把多样化的输入格式变为一个标准格式。

 1、DATETIME类型

         DATETIME类型表示同时包含日期和时间信息的值。MySQL以'YYYY-MM-DD HH:MM:SS'格式检索和显示DATETIME值。支持的范围为'1000-01-01 00:00:00'～'9999-12-31 23:59:59'。

 

 2、DATE类型

         DATE类型表示只有日期值而没有时间值的值。MySQL用'YYYY-MM-DD'格式检索和显示DATE值。支持的范围为'1000-01-01'～'9999-12-31'。

 

 3、TIME类型

         TIME值可以使用多种表示格式。

         'D HH:MM:SS.fraction'格式的字符串。还可以使用下面任何一种“非严格”语法'HH:MM:SS.fraction'、'HH:MM:SS'、'HH:MM'、'D HH:MM:SS'、'D HH:MM'、'D HH'或'SS'。这里D表示日，可以取0～34的值。请注意MySQL不保存分数。

         'HHMMSS'格式的没有间隔符的字符串，被假定为是有意义的时间。例如，'101112'被理解为'10:11:12'，但'109712'是不合法的(它有一个没有意义的分钟部分)，其将变为'00:00:00'。

         HHMMSS格式的数值，被假定为是有意义的时间。例如，101112被理解为'10:11:12'。下面的格式也可以理解：SS、MMSS、HHMMSS、HHMMSS.fraction。请注意MySQL不保存分数。

 

 4、YEAR类型

         YEAR类型是一个单字节类型，用于表示年。MySQL以YYYY格式检索和显示YEAR值。范围为1901～2155。

         可以指定各种格式的YEAR值。

         四位字符串，范围为'1901'～'2155'。

         四位数字，范围为1901～2155。

         两位字符串，范围为'00'～'99'。'00'～'69'和'70'～'99'范围的值分别被转换为2000～2069和1970～1999范围的YEAR值。

         两位整数，范围为1～99。1～69和70～99范围的值分别被转换为2001～2069和1970～1999范围的YEAR值。请注意两位整数范围与两位字符串范围稍有不同，因为不能直接将零指定为数字并将它解释为2000。必须将它指定为一个字符串'0'或'00'或它被解释为0000。

 

 5、TIMESTAMP类型

         TIMESTAMP类型使用４个字节来表示日期和时间。TIMESTAMP类型的范围从1970-01-001 08:00:01到2038-01-19 11:14:07。MySQL中也是以'YYYY-MM-DD HH:MM:SS'的形式显示TIMESTAMP类型的值。从其形式可以看出，TIMESTAMP类型与DATETIME类型显示的格式一样的。给TIMESTAMP类型的字段赋值的表示方法基本与DATETIME类型相同。值的注意的是，TIMESTAMP类型范围比较小，没有DATETIME类型的范围大。因此，输入值时要保证在TIMESTAMP类型时有效范围内。

 

        MySQL 用 DATE 和 YEAR 类型存储简单的日期值，使用 TIME 类型存储时间值。这些类型可以描述为字符串或不带分隔符的整数序列。如果描述为字符串，DATE 类型的值应该使用连字号作为分隔符分开，而 TIME 类型的值应该使用冒号作为分隔符分开。

　　需要注意的是，没有冒号分隔符的 TIME 类型值，将会被 MySQL 理解为持续的时间，而不是时间戳。

　　MySQL 还对日期的年份中的两个数字的值，或是 SQL 语句中为 YEAR 类型输入的两个数字进行最大限度的通译。因为所有 YEAR 类型的值必须用 4 个数字存储。MySQL 试图将 2 个数字的年份转换为 4 个数字的值。把在 00-69 范围内的值转换到 2000-2069 范围内。把 70-99 范围内的值转换到 1970-1979 之内。如果 MySQL 自动转换后的值并不符合我们的需要，请输入 4 个数字表示的年份。

 

        DATETIME 和 TIMESTAMP 这两种混合类型可以把日期和时间作为单个的值进行存储。这两种类型通常用于自动存储包含当前日期和时间的时间戳，并可在需要执行大量数据库事务和需要建立一个调试和审查用途的审计跟踪的应用程序中发挥良好作用。

　　如果我们对 TIMESTAMP 类型的字段没有明确赋值，或是被赋与了 null 值。MySQL 会自动使用系统当前的日期和时间来填充它。

        

 四、字符串类型

         字符串类型是在数据库中存储字符串的数据类型。MySQL 提供了 8 个基本的字符串类型，可以存储的范围从简单的一个字符到巨大的文本块或二进制字符串数据。

 1、CHAR类型和VARCHAR类型

         CHAR类型和VARCHAR类型都是在创建表时指定最大长度，其基本形式如下：

                   字符串类型(M)

         例如，CHAR(4)就是指数据类型为CHAR类型，其最大长度为4。  

         CHAR类型的长度是固定的，在创建表时就指定了。其长度可以是0~255的任意值。比指定长度大的值将被截短，而比指定长度小的值将会用空格作填补。

         例如，CHAR(100)就是指定CHAR类型的长度为100。

         VARCHAR类型的长度是可变的，在创建时指定了最大长度。定义时，其最大值可以取0~65535之间的任意值。指定VARCHAR类型的最大值以后，其长度可以在0到最大长度之间。例如，VARCHAR(100)的最大长度是100。但是，不是每条记录都要占100个位置。而是在这个最大值范围内，使用多少分配多少。VARCHAR类型实际占用的空间为字符串的实际长度加1。这样，可以有效的节约系统的空间。

        CHAR 类型可以使用 BINARY 修饰符。当用于比较运算时，这个修饰符使 CHAR 以二进制方式参于运算，而不是以传统的区分大小写的方式。VARCHAR 类型在使用 BINARY 修饰符时与 CHAR 类型完全相同。

 

 2、TEXT类型

         TEXT类型是一种特殊的字符串类型。TEXT只能保存字符数据，如文章等。TEXT类型包含TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。

类型	允许的长度	存储空间
TINYTEXT	0~255字节	值的长度+2个字节
TEXT	0~65535字节	值的长度+2个字节
MEDIUMTEXT	0~167772150字节	值的长度+3个字节
LONGTEXT	0~4294967295字节	值的长度+4个字节

         从表可以看出，各种TEXT类型的区别在于允许的长度和存储空间不同。因此在这几种TEXT中，根据需求选取既能满足需要以最节约空间的类型即可。  

 

 3、ENUM类型(枚举类型)

         ENUM类型又称为枚举类型。在创建表时，ENUM类型的取值范围就以列表的形式指定了。

                      属性名 ENUM('值1', '值2',...., '值n');

         其中， '属性名'参数指定字段名称；'值n'参数表示列表中的第n个值，这些值末尾的空格将会被系统直接删除。

         ENUm类型的值只能列表中的一个元素。其取值列表中最多能有65535个值。列表中的每个值都有一个顺序排序的编号，MySQL中存入的是这个编号，而不是列表中的值。

         如果ENUm类型加上了NOT NULL属性，其默认值为取值列表的第1个元素。如果不加NOT NULL属性，ENUm类型将允许插入NULL，而且NULL为默认值。

         CREATE TABLE IF NOT EXISTS `test`.`enum_tbl`(

               `a` ENUM('male','female'),

               `b` ENUM('true','false') NOT NULL

         );

         INSERT INTO `test`.`enum_tbl` 

         VALUES('male', 'true'),(NULL, 'false'), (NULL, NULL),(20, 20);

         SELECT * FROM `enum_tbl`;

 

        ENUM 类型因为只允许在集合中取得一个值，有点类似于单选项。在处理相互排拆的数据时容易让人理解，比如人类的性别。ENUM 类型字段可以从集合中取得一个值或使用 null 值，除此之外的输入将会使 MySQL 在这个字段中插入一个空字符串。另外如果插入值的大小写与集合中值的大小写不匹配，MySQL 会自动使用插入值的大小写转换成与集合中大小写一致的值。

　　ENUM 类型在系统内部可以存储为数字，并且从 1 开始用数字做索引。一个 ENUM 类型最多可以包含 65536 个元素，其中一个元素被 MySQL 保留，用来存储错误信息，这个错误值用索引 0 或者一个空字符串表示。

　　MySQL 认为 ENUM 类型集合中出现的值是合法输入，除此之外其它任何输入都将失败。这说明通过搜索包含空字符串或对应数字索引为 0 的行就可以很容易地找到错误记录的位置。

 

 4、SET类型

         基本形式如下：

                 属性名 set('值1','值2','值3'...'值n');

         其中，'属性名'参数指定字段名称；'值n'参数列表中的第n个值,这些值末尾的空格将会被系统直接删除。其基本形式与ENUM类型一样。

         SET类型的值可以取列表中一个元素或者多个元素的组合。取多个元素时，不同元素之间用逗号隔开。SET类型的值最多只能是64个元素构成的组合。列表中的每一个值都有一个顺序排列的编号。MySQL中存入的是这个编号，而不是列表中的值。

         插入记录时，SET字段里的元素顺序无关紧要。存入MySQL数据库后，数据库系统会自动按照定义时的顺序显示。

         CREATE TABLE IF NOT EXISTS `test`.`set_tbl`(

               `a` SET('a','b','c','d','e','f','g')

         );

         INSERT INTO `test`.`set_tbl`    VALUES('f'),('a,b,c'),('d,e,a');

         INSERT INTO `test`.`set_tbl` VALUES('h');

         SELECT * FROM `set_tbl`;

 

        SET 类型与 ENUM 类型相似但不相同。SET 类型可以从预定义的集合中取得任意数量的值。并且与 ENUM 类型相同的是任何试图在 SET 类型字段中插入非预定义的值都会使 MySQL 插入一个空字符串。如果插入一个即有合法的元素又有非法的元素的记录，MySQL 将会保留合法的元素，除去非法的元素。

　　一个 SET 类型最多可以包含 64 项元素。在 SET 元素中值被存储为一个分离的“位”序列，这些“位”表示与它相对应的元素。“位”是创建有序元素集合的一种简单而有效的方式。并且它还去除了重复的元素，所以 SET 类型中不可能包含两个相同的元素。

　　希望从 SET 类型字段中找出非法的记录只需查找包含空字符串或二进制值为 0 的行。

 

        两种复合数据类型 ENUM 和 SET，它们扩展了 SQL 规范。虽然这些类型在技术上是字符串类型，但是可以被视为不同的数据类型。一个 ENUM 类型只允许从一个集合中取得一个值；而 SET 类型允许从一个集合中取得任意多个值。

         

 五、二进制类型

         二进制类型是在数据库中存储二进制数据的数据类型。

二进制类型	取值范围
BINARY(M)	字节数为M，允许长度为0~M的定长二进制字符串
VARBINARY(M)	允许长度为0~M的变长二进制字符串，字节数为值的长度加一
BIT(M)	M位二进制数据，M最大值为64
TINYBLOB	可变长二进制数据，最多255个字节
BLOB	可变长二进制数据，最多(2[16]-1)个字节
MEDIUMBLOB	可变长二进制数据，最多(2[24]-1)个字节
LONGBLOB	可变长二进制数据，最多(2[32]-1)个字节

 

1、BINARY和VARBINARY类型

         BINARY和VARBINARY类型都是在创建表时指定了最大长度，其基本形式如下 ：

                   字符串类型(M)

         这与CHAR类型和VARCHAR类型相似。

         例如，BINARY(10)就是指数据类型为BINARY类型，其最大长度为10。

         BINARY类型的长度是固定的，在创建表是就指定了。不足最大长度的空间由"\0"补全。例如，BINARY(50)就是指定BINARY类型的长度为50。

         VARBINARY类型的长度是可变的，在创建表时指定了最大长度。指定好了VARBINARY类型的最大值以后，基长度可以在0到最大长度之间。例如，VARBINARY(50)的最大字节长度是50。但是，不是每条记录的字节长度都是50。在这个最大范围内，使用多少分配多少。VARBINARY类型实际占用的空间为实际长度加一。这样，可以有效的节约系统的空间。

 

 2、BIT类型

         BIT类型也是创建表时指定了最大长度，其基本形式如下：

                   BIT(M)

         其中，'M'指定了该二进制的数的最大字节长度为M，M的最大值为64。例如，BIT(4)就是数据类型BIT类型，长度为4.若字段的类型BIT(4)，存储的数据是从0到15。因为，变成二进制以后，15的值为1111，其长度为4。如果插入的值为16，其二进制数为10000，长度为5，超过了最大长度。因此大于等于16的数是不能插入到BIT(4)类型的字段中的。在查询BIT类型的数据时，要用BIN(字段名+0)来将值转换为二进制显示。

 

 3、BLOB类型

         BLOB类型是一种特殊的二进制类型。BLOB可以用来保存数据量很大的二进制数据，如图片等。BLOB类型包括TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。这几种BLOB类型最大的区别就是能够保存的最大长度不同。LONGBLOB的长度最大，TINYBLOB的长度最小。

         BLOB类型与TEXT类型很类似。不同点在于BLOB类型用于存储二进制数据，BLOB类型数据是根据其二进制编码进行比较和排序。而TEXT类型是文本模式进行比较和排序的。

 

        对于字段长度要求超过 255 个的情况下，MySQL 提供了 TEXT 和 BLOB 两种类型。根据存储数据的大小，它们都有不同的子类型。这些大型的数据用于存储文本块或图像、声音文件等二进制数据类型。

　　TEXT 和 BLOB 类型在分类和比较上存在区别。BLOB 类型区分大小写，而 TEXT 不区分大小写。大小修饰符不用于各种 BLOB 和 TEXT 子类型。比指定类型支持的最大范围大的值将被自动截短。