MySQL数据类型详解

2017-12-16 10:40:00
linefo
原创
2410

摘自MySQL5.6官方文档:https://dev.mysql.com/doc/refman/5.6/en/numeric-type-overview.html


  数值类型(Numeric Type


【前言】


语法解释:

M:对整型类型(integer)而言,指定其最大显示位数(最多255位),需要注意的是,该显示位数和类型的存储范围没有关系,仅是显示的位数;而对浮点类型(floating-point)和定点类型(fixed-point)来说,该值指定了它们能够储存的数字总位数(在允许的类型存储范围内)。

UNSIGNED:无符号属性,即值不包含负数的情况,添加该属性以后,取值范围会有改变(变成从0开始,但跨度不变)。

ZEROFILL:设置填充零,如int(4)中,设置该属性后,如果写入的值为1,则会变成0001(即填充满4位),设置该属性后,会自动添加UNSIGNED(无符号)属性。


注意:

当你在整型类型(integer)中使用减法时,如果其中有一个为unsigned属性,则结果一般也会是unsigned属性(除非no_unsigned_subtraction被激活)


【类型】


BIT [ ( M ) ]


一个比特位类型,只接受二进制数字,M值设定位数,范围从1到64,M的默认值是1。

示例:在bit(8)中储存101010这个二进制值


引申:
从数据库读取值时,无论是何种类型的数据,在 php 中是读取出来得到的是都是string类型(默认)。
而bit类型的值,取出来后会转为十进制显示,如101010会显示为42。
另外,如果含有ZEROFILL(填充零)属性,则从数据库取出的值会依据情况填充零(如int(4)中,1取出后是0001,至少在PHP中取出是这样,因为是string类型),有些数据库操作程序(如Navicat)不一定会显示填充的零,但实际上是填充的了。


TINYINT [ ( M ) ] , [ UNSIGNED ] , [ ZEROFILL ]


非常小的整数类型。取值范围从 -128 to 127,如果包含UNSIGNED属性,则取值范围从 0 to 255。

示例:在tinyint(4)中储存120这个值


SMALLINT [(M)] , [UNSIGNED] [ZEROFILL]


一个小的整数类型。 范围从 -32768 to 32767,无符号则为 0 to 65535。


MEDIUMINT [(M)] [UNSIGNED] [ZEROFILL]


一个中等大小的整数类型。范围从 -8388608 to 8388607, 无符号则为 0 to 16777215。


INT [(M)] [UNSIGNED] [ZEROFILL]


一个通常意义的整数类型,取值范围 -8388608 to 8388607,无符号则为 0 to 16777215.


INTEGER [(M)] [UNSIGNED] [ZEROFILL]


等同INT类型


BIGINT [(M)] [UNSIGNED] [ZEROFILL]


一个大的整数类型。取值范围从 -9223372036854775808 to 9223372036854775807, 无符号则为 0 to 18446744073709551615.


DECIMAL [(M[,D])] [UNSIGNED] [ZEROFILL]


定点类型的数据。M代表整数部分+小数部分的最大位数(默认值10,最大值65),D代表小数部分的位数(默认值0,最大值30),如decimal(5,1)的取值范围是-999.9到9999.9(可以理解-这个符号占一个位置)


DEC [(M[,D])] [UNSIGNED] [ZEROFILL]

NUMERIC [(M[,D])] [UNSIGNED] [ZEROFILL]

FIXED [(M[,D])] [UNSIGNED] [ZEROFILL]


等同DECIMAL类型,主要是为了兼容其他数据库。


FLOAT [(M,D)] [UNSIGNED] [ZEROFILL]


M和D两个参数的意义和decimal一致,取值范围比decimal更大,忽略M和D,则小数点后面超过7位的部分会四舍五入


DOUBLE [(M,D)] [UNSIGNED] [ZEROFILL]


M和D两个参数的意义和float一致,但取值范围比float更大,忽略M和D,则小数点后面超过15位的部分会四舍五入


PS:关于float、double、decimal的区别
(1)使用decimal的情况多,因为存储要求精度高的数值,float和double可能会有损失
(2)默认情况下(不填入M和D参数),存储小数点超过7位的值时,float会把超过7位的部分四舍五入,然后再存入数据库;double在默认情况下存储,xiaoshudian超过15位的部分会四舍五入再存入。
(3)因为double类型占据的空间大于float类型,所以在存储超过float类型上限的值时,double类型的精度更高。
(4)无法存储超过decimal所设置小数点上限的值,float和double可以(参考(2))


  日期类型(Date Type)和时间(Time Type)类型


新特性:
MySQL从5.6.4版本开始,最高支持 TIME 、 DATETIME、 TIMESTAMP 三种类型精确到微妙(6位数),通过语法名称(fsp)设置位数


语法解释:

fsp:对 TIME 、 DATETIME、 TIMESTAMP 三种类型设置的秒以后的精确位数,最多达6位(微秒级)


DATE


一个日期。支持的范围从 '1000-01-01' to '9999-12-31' , MySQL显示DATE类型是根据“YYYY-MM-DD”这种格式的,但允许通过合法的数字(如时间戳)或字符串写入到日期(DATE)列。

示例:存储一个时间


DATETIME [(fsp)]


日期和时间的结合类型,范围从 '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999',MySQL显示DATETIME类型是根据 'YYYY-MM-DD HH:MM:SS[.fraction]' 的格式,但允许通过合法的数字或字符串写入到DATETIME列。[.fraction]部分即更精确的时间,通过fsp设置,其默认值为0。

示例:在DATETIME(4)中存储一个时间




TIMESTAMP [(fsp)]


另一种日期和时间的结合类型,语法、格式都和 DATETIME 一致,不同主要在下面这几点:

(1)存储范围不同,TIMESTAMP的存储范围是 '1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.999999'。需要注意的是,写入超出范围的时间,是插入成功的,而实际保存的值为'0000-00-00 00:00:00.00000',只会返回一个警告信息。

(2)存储和读取上有区别,TIMESTAMP 存储时会转化为UTC(世界标准时间),而读取时会根据客户端的时区进行转化,而 DATETIME 没有这个过程,读取时按输入的内容原样输出。(这个还没能理解机制和使用,后面补充学习

(3)可以设置两个特殊的属性,DEFAULT CURRENT_TIMESTAMP(默认写入当前时间,插入数据时会触发),ON UPDATE CURRENT_TIMESTAMP(当行数据有更新时,TIMESTAMP类型的列也自动更新为当前时间)

(4)和explicit_defaults_for_timestamp设置相关的变化(这个也后面补


TIME [(fsp)]


时间类型,范围从 '-838:59:59.000000' to '838:59:59.000000'  , MySQL显示TIME类型是根据 'HH:MM:SS[.fraction]' 的格式,

可以用合法的数字和字符串写入TIME列。


YEAR [(2|4)]


表示年的类型,有2位显示和4位显示两种情况,默认是4位的,需要注意的是,这只是显示的不同,对实际的值没有影响。

4位的显示范围:1901 to 2155, and 0000

2位的显示范围:70 to 69(即1970 to 2069)

允许用合法的数字或字符串写入YEAR列



注意:
SUM()和AVG()聚合函数不能与时间值一起工作,具体情况后面补



  字符串类型(String Type)


【前言】

字符串类型有比较多的属性


注意:
这里会有 字符长度 和 字节长度 两种情况,它们的概念是不同的
字符长度,无论是字母、中文、数字,单个字符长度都为1
字节长度和编码密切相关,并且单个字母、中文、数字占据的字节通常都是不同的(即使在不同编码下)
(所谓字符长度,最后也是存成字节的,这样处理是为了方便统计)



属性解释:

CHARACTER SET / CHARSET:设置编码(MySQL的默认编码是UTF-8:每个字符1~4个字节)

COLLATE:排序规则


[NATIONAL] CHAR [(M)] [CHARACTER SET charset_name] [COLLATE collation_name]


这是一个定长的字符串类型,M代表设置的字符长度(从0 to 255),如果M缺省,默认值是1。无法存入字符数超过M值的数据,存储时,总会按照我们规定的长度,从右边用空格填补;

实际检索时,会自动删掉所有后面的空格(包括非填补的),通过修改 PAD_CHAR_TO_FULL_LENGTH 的模式值可以改变。

CHAR类型可以指定字符长度为0,意义后面补;


[NATIONAL] VARCHAR (M) [CHARACTER SET charset_name] [COLLATE collation_name]


这是一个可变长度的字符串,M值代表设置的最大字符长度(从0字节 到 65535字节,在MySQL5.0之前的版本只能最大只能到255,并且M代表的是字节长度),无法存入字符数超过M值的数据,能存储的字符数要看编码的情况,如utf-8编码中,如果一个字符占据3个字节(当然不是每个字符都一样),那么最多可存储的该字符数就是21,884。

在MySQL实际存储中,VARCHAR类型可能需要1个或2个字节的前缀字节。

另外,不会像CHAR那样,删除后面的空格。

虽然M值代表的是字符数,但取值范围还是用字节数处理的,这点要注意了


BINARY [(M)]


该类型类似CHAR类型,但存储二进制数据(没有字符集限制),M指定的是字节长度,缺省值是1。


VARBINARY (M)


该类型类似VARCHAR类型,但存储二进制数据(没有字符集限制),M指定的是字节长度


关于BINARY、VARBINARY
也是和CHAR以及VARCHAR一样用来存储字符串,区别好像是BINARY、VARBINARY直接将字符串存成了二进制数据,主要在长度、互相比较、和排序几种情况中有区别,具体等以后再补。



TINYBLOB


TINYBLOB类型最大存储255(2^8-1)字节长度,该类型会使用1字节存储前缀,前缀用于指定字节数


BLOB [(M)]


BLOB类型最大存储65,535 (2^16 − 1)字节长度,该类型会使用2字节存储前缀,前缀用于指定字节数;

这里还提供了可选的长度M,该值用于指定可容纳的字节长。


MEDIUMBLOB


MEDIUMBLOB类型最大存储16,777,215 (2^24 − 1)字节长度,会使用3个字节作为前缀


LONGBLOB


LONGBLOB类型最大存储4,294,967,295 or 4GB (2^32 − 1) 字节长度,其有效长度取决于在客户机/服务器中,协议和有效内存配置的最大包大小,会使用4个字节作为前缀。


TINYTEXT [CHARACTER SET charset_name] [COLLATE collation_name]


最大存储255(2^8-1)字符长度,该类型会使用1字节存储前缀,前缀用于指定字节数


TEXT  [(M)] [CHARACTER SET charset_name] [COLLATE collation_name]


最大存储65,535 (2^16 − 1)字符长度,使用2个字节作为前缀;

提供一个可选的M值,控制字符的长度。


MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name]


最大存储16,777,215 (2^24 − 1)字符长度,会使用3个字节的长度作为前缀


LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name]


最大存储4,294,967,295 or 4GB (2^32 − 1)字符长度,其有效长度取决于在客户机/服务器中,协议和有效内存配置的最大包大小。会使用4个字节作为前缀。


ENUM('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name]


枚举类型,在内部其实是整型数据(integer),一个枚举类型最大提供65535个选择(实际中,会限制不超过3000个),

后面补......


SET('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name]


后面补......



最后编辑:linefo 于 2019-06-17 16:41:45

关键字

文章分类
联系我们
联系人: Mr.Chen
QQ: 185391277