mysql数据类型

数据类型是数据的一种属性，其可以决定数据的存储格式、有效范围和相应的限制。MySQL数据类型包括整数类型、浮点数类型、定点数类型、日期和时间类型、字符串类型、二进制数据类型。不同的数据类型有各自的特点，使用范围各不相同，而且存储方式也不一样。

1、整数类型

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

表1.1 MySQL的整数类型

整数类型	字节数	默认显示宽度	无符号数的取值范围	有符号数的取值范围
TINYINT	1	4	0~255	-128~127
SMALLINT	2	6	0~65535	-32768~32767
MEDIUMINT	3	9	0~16777215	-8388608~8388607
INT	4	11	0~4294967295	-2147483648~2147483647
INTEGER	4	11	0~4294967295	-2147483648~2147483647
BIGINT	8	20	0~18446744073709551615	-9223372036854775808~9223372036854775807

MySQL支持数据类型的名称后面指定该类型的显示宽度。基本形式： 数据类型 （显示宽度） 比如： mediumint(9)，宽度是指能够显示的最大数据的长度。每个整数类型都有默认的显示宽度。虽然可以设置显示宽度，但依然可以插入大于显示宽度的数据，这时设置的显示宽度失效，但数据宽度不能大于默认的显示宽度。

整数类型使用时，可以搭配使用zerofill参数。zerofill参数表示数字不足的显示空间由0来填补。使用zerofill参数时，MySQL会自动加上UNSIGNED属性。那该整数类型只能表示无符号数，其显示宽度比默认宽度小1。

整数类型还有一个AUTO INCREMENT属性，该属性可以使字段成为自增字段。具有该属性的字段，在插入新的记录时，该字段都会在前一条记录的基础上加1。

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

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

表2.1 MySQL的浮点数类型和定点数类型

整数类型	字节数	负数的取值范围	非负数的取值范围
FLOAT	4	-3.402823466E+38~-1.175494351E-38	0和1.175494351E-38~3.402823466E+38
DOUBLE	8	-1.7976931348623157E+308~ 202250738585072014E-308	0和202250738585072014E-308~ 1.7976931348623157E+308
DECIMAL(M,D) 或DEC(M,D)	M+2	同DOUBLE型	同DOUBLE型

DECIMAL型的取值范围与DOUBLE相同，但DECIMAL的有效取值范围由M和D决定。而且，DECIMAL型的字节数是M+2。也就是说定点数的存储空间是根据其精度决定的。

MySQL中可以指定浮点数和定点数的精度。基本形式： 数据类型（M,D） ，其中M参数称为精度，是数据的总长度，小数点不点位置；D参数成为标度，是指小数点后的长度。指定小数精度的方法不是浮点数的标准用法，是DECIMAL(M,D)的标准格式。

如果插入数据的精度高于定义的精度，系统会自动四舍五入，使值的精度达到要求，但FLOAT型和DOUBLE型在四舍五入时会报错，而DECIMAL会告警。

如果不指定精度，FLOAT型和DOUBLE型默认保存实际精度，与操作系统和硬件的精度有关。DECIMAL型默认整数位为10，小数位为0，即默认为整数。

定点数以字符串的形式存储，精度比浮点数高，而浮点数会出现误差，如对数据精度要求高，选择定点数（DECIMAL）较安全。

3、日期与时间类型

日期与时间类型是为了方便在数据库中存储日期与时间设计的，其中YEAR类型表示时间；DATE类型表示日期；TIME类型表示时间；DATETIME和TIMESTAMP表示日期和时间。

表3.1 MySQL的日期与时间类型

整数类型	字节数	取值范围	零值	格式
YEAR	1	1901~2155	0000	‘YYYY’或YYYY
DATE	4	1000-01-01~9999-12-31	0000:00:00	YYYY-MM-DD
TIME	3	-838:59:59~838:59:59	00:00:00	HH:MM:SS
DATETIME	8	1000-01-01 00:00:00~9999-12-31 23:59:59	0000-00-00 00:00:00	YYYY-MM-DD HH:MM:SS
TIMESTAMP	4	19700101080001~20380119111407	00000000000000	YYYY-MM-DD HH:MM:SS

3.1 YEAR 类型

 使用4位字符串或数字插入效果一样，超出范围就会插入 0000 。

 使用2位字符串插入：'00'~'69' 转换为2000~2069，'70'~'99' 转换为1970~1999，'0'和'00'效果一样。

 使用2位数字插入：1~69 转换为2000~2069，70~99 转换为1970~1999，0 转换为 0000。

3.2 DATE类型

 'YYYY-MM-DD'或'YYYYMMDD' 格式的字符串插入，支持间隔符 '-'换成别的符号。

 'YY-MM-DD' 或者 'YYMMDD'格式的字符串插入，其中'YY'的取值，'00'~'69'转换为2000~2069，'70'~'99'转换为2000~2069，与YEAR类型类似。支持间隔符 '-'换成别的符号。

 YYYYMMDD或YYMMDD格式的数字插入，其中YY 的取值，00~69转换为2000~2069，70~99转换为2000~2069，输入的值为0，DATA类型会转换为0000-00-00。

 使用CURRENT_DATE 或 NOW()来插入当前系统日期。

3.3 TIME类型

 'D HH:MM:SS' 格式的字符串插入，其中D表示天数，取值范围是0-34。保存时，小时的值等于（D*24+HH）。

 'HH:MM:SS' 格式的字符串或HHMMSS格式的数值插入，如果输入0和'0'，TIME会转换为 0000:00:00。

 使用CURRENT_TIME或者NOW() 输入当前系统时间，想要插入当前系统时间，就选CURRENT_TIME或者NOW() 。

3.4 DATETIME类型

从形式上可以看出，DATETIME类型可以直接用DATE类型和TIME类型组合而成。用法就不再描写。

插入当前系统时间使用NOW()。

3.5 TIMESTAMP类型

与DATETIME大致类似，不同点如下

 使用CURRENT_TIMESTAMP和NOW()来输入当前日期与时间。

 输入NULL时，系统会输入系统当前日期与时间。

 无任何输入时，系统会输入系统当前日期与时间。

4、字符串类型

字符串类型是在数据库中存储字符串的数据类型，包括 CHAR、VARCHAR、TEXT、ENUM、SET。

4.1 CHAR类型和VARCHAR类型

CHAR类型和VARCHAR类型都是在创建表时指定了最大长度，其基本形式： 字符串类型(M) ,字符串类型参数指定CHAR或VARCHAR类型；M 参数指定该字符串的最大长度为M。

 CHAR类型的长度是固定的，在创建表时就指定了，其长度是0~255的任意值。其占用的空间为指定时的固定长度。

 VARCHAR类型的长度是可变的，在创建表时指定了最大长度，其最大值是0~65535的任意值，其长度在0~最大值之间。占用空间为字符串的实际长度加 1。可以节约系统的空间。

4.2 TEXT类型

TEXT类型是一种特殊的字符串类型。TEXT只能保存字符数据，如新闻的内容等。TEXT类型包括TINYTEXT、TEXT、MEDIUMTEXT、LOGTEXT。

表4.1 各种TEXT类型的对比

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

4.3 ENUM类型

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

属性名  ENUM('值1'，'值2',......,'值n')，其中，属性名参数指字段的名称；"值n"参数表示列表中的第n个值，这些值末尾的空格将会被系统直接删除。ENUM类型的值只能取列表中的一个元素。其取值列表中最多能有65535个值。列表中的每个值都有一个顺序排列的编号，MySQL中存入的是这个编号，而不是列表中的值。

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

如果只能选取列表中一个值，就选择ENUM类型，如果需要选取列表中多个值的组合，选择SET类型。

4.4 SET类型

在创建列表时，SET类型的取值范围就以列表的形式指定了，其基本形式：属性名 SET('值1','值2',......'值n') ，其中，属性名参数指字段的名称；"值n"参数表示列表中的第n个值，这些值的末尾的空格将会被系统直接删除。其基本形式与ENUM类型一样。SET类型的值可以取列表中的一个元素或者多个元素的组合。取多个元素时，不同元素之间用逗号隔开。SET类型的值最多只能有64个元素构成的组合。同ENUM类型一样，列表中每个值都有一个顺序的编号。MySQL中存入的是这个编号，而不是列表中的值。

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

插入值必须是在定义集合中的元素

5、二进制类型

二进制类型是在数据库中存储二进制数据的数据类型。二进制类型包括BINARY、VARBINARY、BIT、BLOB（BLOB、TINYBLOB、MEDIUMBLOB、LONGBLOB）。

表5.1 MySQL的二进制类型

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

5.1 BINARY和VARBINARY类型

BINARY类型和VARBINARY类型都是在创建表时指定了最大长度，其基本形式：字符串类型(M)，其中，"字符串类型"参数指定了数据类型为BINARY类型还是VARBINARY类型；M 参数指定了该二进制数的最大字节长度为M。

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

 VARBINARY类型的长度是可变的，在创建表时指定了最大长度。指定好了VARBINARY类型的最大值后，其长度可以在0到最大长度之间。VARBINARY类型实际占用空间为实际长度加1，可以节约系统空间

5.2 BIT类型

BIT类型在创建表时指定了最大长度，其基本形式：BIT(M) ，其中，M 参数指定了该二进制数的最大字节长度为M，M的最大值为64。在查询BIT类型的数据时，要用 BIN(字段+0)来将值转换为二进制显示。

5.3 BLOB类型

BLOB类型是一种特殊的二进制类型。BLOB可以用来保存数据量很大的二进制数据，如图片等。

BLOB类型包括 TINYBLOB、MEDIUMBLOB、LONGBLOB。这几种BLOB类型最大的区别就是能够保存的最大长度不同。LOGBLOB的长度最大，TINYBLOB的长度最小。

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

BLOB类型主要用来存储图片、PDF文档等二进制文件。通常情况下，可以将图片、PDF文档都可以存储在文件系统中，然后在数据库中存储这些文件的路径。这种方式存储比直接存储在数据库中简单，但是访问速度比存储在数据库中慢。

6、如何选择数据类型

在MySQL中创建表时，需要考虑为字段选择哪种数据类型是最合适的，只有选择了合适的数据类型，才能提高数据库的效率。

6.1 整数类型和浮点数类型

整数类型和浮点数类型最大区别在于能否表达小数。

不同整数类型的取值范围不同。

浮点数类型，DOUBLE类型精度高于FLOAT类型，如需要精确到小数点后10位以上，要选择DOUBLE类型。

6.2 浮点数类型和定点数类型

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

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

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

6.3 CHAR类型和VARCHAR类型

CHAR长度是固定的，而VARCHAR类型长度是在范围内可变的。因此，VARCHAR类型占用空间比CHAR类型小。而且VARCHAR类型比CHAR类型灵活。对于长度变化大的字符串类型，最好选择VARCHAR类型。

虽然CHAR类型占用空间比较大，但CHAR类型处理速度比VARCHAR类型快，因此，对于长度变化不大和查询速度要求高的字符串类型，最好选择CHAR类型。

6.4 时间和日期类型

YEAR类型只表示年份，如果只需要记录年份，选择YEAR类型可以节约空间。

TIME类型只表示时间，如果只需要记录时间，选择TIME类型最合适。

DATE类型只表示日期。如果只需要记录日期，选择DATE类型最合适。

如果需要记录日期和时间，可以选择DATETIME类型和TIMESTAMP类型。DATETIME类型表示的时间范围比TIMESTAMP类型大。因此，若需要的时间范围比较大，选择DATETIME类型。TIMESTAMP类型的时间是根据时区来显示的，如果需要显示的时间与时区对应，选择TIMESTAMP类型。

6.5 ENUM类型和SET类型

ENUM类型最多可以有65535个成员，而SET类型最多只能包含64个成员。两者的取值只能在成员列表中选取。ENUM类型只能从成员中选择一个，而SET类型可以选择多个。

因此，对于多个值中选取一个的，可以选择ENUM类型。例如，"性别" 字段就可以定义成ENUM类型，因为只能在"男"和"女"中选其中一个。对于可以选取多个值的字段，可以选择SET类型。例如，"爱好"字段就可以选择SET类型，因为可能有多种爱好。

6.6 TEXT类型和BLOB类型

TEXT类型与BLOB类型很类似，TEXT类型只能存储字符数据，而BLOB类型用于存储二进制数据。如果要存储文章等纯文本的数据，应该选择TEXT类型。如果需要存储图片等二进制的数据，应该选择BLOB类型。

TEXT类型包括TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这4者最大的不同是内容长度不同。TINYTEXT类型允许的长度最小，LONGTEXT类型允许的长度最大。BLOB类型也是如此。