A-A+

MySQL数据类型 详细讲解

2016年08月03日 Linux 数据库 暂无评论 阅读 263 次

MySQL数据类型

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等。

    1.1 整数类型

    数值型数据类型主要用来存储数字,MYSQL提供了多种数值数据类型,不同的数据库有不同的取值范围,可以存储的值范围越大,其所存储的控件也会越大。MySQL主要提供整数类型有:TINYTINT、SMALLINT、MEDIUMINT、INT、BIGINT。整数类型属性的字段可以添加AUTO_INCREMENT自增约束条件。下表列出了MySQL中的数值类型。

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

从表可知,不同类型整数存储所需的字节数是不同的,占用字节数最小的是TINYINT类型,占用字节最大的是BIGINT类型,相应的占用字节越多的类型所能表示的数值范围越大。根据占用字节数可以求出每一种数据的取值范围,例如TINYINT需要1个字节(8bits)来存储,那么TINYINT无符号数的最大值为2^8-1,即255;TINYINT有符号数的最大值为2^7-1,即127。其它类型如下表所示:

数据类型 有符号 无符号
TINYINT -128~127 0~255
SMALLINT -32768~32767 0~65535
MEDIUMINT -8388608~8388607 0~16777215
INT -2147483648~2147483647 0~4294967295
BIGINT -9223372036854775808~9223372036854775807 0~18446744073709551615

1.2 浮点数类型和定点数类型

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

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

DECIMAL类型不同于FLOAT和DOUBLE,DECIMAL实际是以串存放的,DECIMAL可能的最大取值范围与DOUBLE一样,但是其有效的取值范围由M和D的值决定。如果改变M而固定D,则取值范围将随M的变大而变大。

1.3 日期/时间类型

MySQL中有多种表示日期的数据类型,主要有:DATETIME、DATE、TIMESTAMP、TIME和YEAR。下表列出了MySQL日期/时间类型:

类型名称 日期格式 日期范围 存储需求
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 1970-01-01 UTC ~ 2038-01-19 03:14:07 UTC 4字节

1.4 字符串类型

字符串类型用来存储字符串数据,除了可以存储字符串之外,还可以存储其它数据,比如图片和声音二进制数据。字符串可以进行区分或者不区分大小写的串比较,另外,还可以进行模式匹配查找。MySQL中字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。下表列出了字符串数据类型。

类型名称 说明 存储需求
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
MEDIUMTEXT 中等大小的非二进制字符串 L+3字节,在此L<2^32
LONGTEXT 大的非二进制字符串 L+4字节,在此L<2^24
ENUM 枚举类型,只能有一个枚举字符串值 1或2个字节,取决于枚举值数目(最大值65535)
SET 一个设置,字符串对象可以有0个或多个SET成员 1,2,3,4或8个字节,取决于集合成员的数量(最多64个成员)

1.5 二进制类型

MySQL支持两类字符型数据:文本字符串和二进制字符串。MySQL中存储二进制字符串数据类型有:BIT、BINARY、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

2. 如何选择数据类型

MySQL提供了大量的数据类型,为了优化存储,提高数据库性能,在任何情况下均应该使用精确的类型。即在所有可以表示该列值的类型中,该类型使用的存储最少。

2.1 整数和浮点数

    如果不需要小数部分,则使用整数来保存数据;如果需要小数部分,则使用浮点数来表示。对于浮点数据列,存入的数值会对该列定义的小数位进行四舍五入。例如,如果列的值范围为1-99999,若使用整数,则MEDIUMINT UNSIGNED是最好的类型;若需存储小数,则使用FLOAT类型。

    浮点类型包括FLOAT和DOUBLE类型。DOUBLE类型精度比FLOAT类型高,因此,如要求存储精度较高时,应选择DOUBLE。

2.2 浮点数和定点数

浮点数FLOAT、DOUBLE相对于定点数DECIMAL的优势是:在长度一定的情况下,浮点数能表示更大的数据范围。但是由于浮点数容易产生误差,因此对精确度要求比较高时,建议使用DECIMAL来存储。DECIMAL在MySQL中是以字符串存储的,用于定义货币等对精确度要求较高的数据。DECIMAL在MySQL中是以字符串存储的。在数据迁移中,float(M,D)是非标准SQL定义,数据库迁移可能会出现问题,最好不要这样使用。另外两个浮点数进行减法和比较运算时也容易出现问题,因此在进行计算的时候,一定要小心。如果进行数值比较,建议使用DECIMAL类型。

2.3 日期/时间类型

    MySQL对于不同种类的日期和时间有多种数据类型,比如YEAR和TIME。如果只需记录年份,则使用YEAR即可,如果只记录时间,则使用TIME类型。

    如果同时需要记录日期和时间则可以使用TIMESTAMP或者DATETIME类型。由于TIMESTAMP这个列取值时范围小于DATETIME的取值范围,因此存储范围较大的日期最好使用DATETIME。

    TIMESTAMP也有一个DATETIME不具备的属性。默认情况下,当插入一条记录但并没有指定TIMESTAMP这个��值时,MySQL会把TIMESTAMP列设为当前的时间。因此需要插入记录同时插入当前时间时,使用TIMESTAMP是方便的,另外TIMESTAMP在空间上比DATETIME更有效。

2.4 CHAR与VARCHAR之间的特点与选择

CHAR与VARCHAR之间的区别:

CHAR是固定长度字符,VARCHAR是可变长度字符;CHAR会自动删除插入数据的尾部空格,VARCHAR不删除尾部空格。

CHAR是固定长度,所以它的处理速度比VARCHAR速度要快,但是它的缺点就是浪费存储空间。所以对存储不大,但在速度上有要求的可以使用CHAR类型,反之可以使用VARCHAR类型来实现。


存储引擎对CHAR和VARCHAR的影响:

    对于MyISAM存储引擎,最好使用固定长度的数据列代替可变长度的数据列。这样可以使整个表静态化,从而使数据检索更快,用存储空间换查询时间。

    对于InnoDB存储引擎:使用可变长的数据列,因为InnoDB数据表的存储格式不分固定长度和可变长度,因此使用CHAR不一定比使用VARCHAR更好,但由于VARCHAR是按照实际存的长度存储,比较节省空间,所以对磁盘I/O和数据存储总量比较好。

2.5 ENUM和SET

    ENUM只能取单值,它的数据列表示一个枚举集合。它的合法值列表最多有65535个成员。因此,在需要从多个值中选取一个时,可以使用ENUM。比如:性别字段适合定义成ENUM类型,每次只能从’男’ 或 ‘女’中取一个值。

    SET可取多值。它的合法取值列表最多允许有64个成员。空字符串也是一个合法的SET值。在需要取多个值的时候,适合使用SET类型,比如:要存储一个兴趣爱好,最好使用SET类型。

    ENUM和SET的值是以字符串形式出现的,但在内部,MySQL以数值的形式存储它们。

2.6 BLOB和TEXT

BLOB是二进制字符串,TEXT是非二进制字符串,二者均可存放大容量信息。BLOB主要存储图片、音频信息等,而TEXT只能存储纯文本文件,应分清二者存储的关系。

 

 

标签:

给我留言

Copyright © SEARU.ORG 保留所有权利.   Theme  Ality 网站地图 360网站安全检测平台

用户登录

分享到: