mysql入门学习之四：mysql的数据类型
相关链接：
mysql入门学习之一：基本操作
http:///database/201212/173868.html
mysql入门学习之二：使用正则表达式搜索
http:///database/201212/173869.html
mysql入门学习之三：全文本搜索
http:///database/201212/173873.html
一、整型
整数类型是数据库中最基本的数据类型。标准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使用浮点数类型和定点数类型来表示小数。浮点数类型包括单精度浮点数(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) m+2 double型 同double型
或dec(m,d)
m:最大长度（包括小数部分，但不包括小数点）
d:小数点后保留长度
对于浮点数和定点数，当插入值的精度高于实际定义的精度，系统会自动进行四舍五入处理。其目的是为了使该值的精度达到要求。浮点数进行四舍五入不会报警，定点数出现警告。
在未指定精度的情况下，浮点数和定点数有其默认的精度。float类型和double类型默认会保存实际精度。这个精度与操作系统和硬件的精度有关。decimal类型默认整数位为10, 小数位为0，即默认为整数。
在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类型时有效范围内。
四、字符串类型
字符串类型是在数据库中存储字符串的数据类型。
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。这样，可以有效的节约系统的空间。
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`;
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`;
五、二进制类型
二进制类型是在数据库中存储二进制数据的数据类型。
二进制类型 取值范围
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类型是文本模式进行比较和排序的。
bitscn.com