MySQL支持的数据类型主要有数值类型,日期/时间类型,字符串类型,复合类型。
数据类型
数值类型包括整数类型TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT,浮点小数数据类型FLOAT和DOUBLE,定点小数类型DECIMAL。
时间日期类型包括YEAR、TIME、DATE、DATETIME和TIMESTAMP。
字符串类型包括CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT。
复合类型包括ENUM(枚举),SET。
整数数据类型:
类型名称 | 说明 | 存储需求 |
---|---|---|
TINYINT | 很小的整数 | 1个字节 |
SMALLINT | 小的整数 | 2个字节 |
MEDIUMINT | 中等大小的整数 | 3个字节 |
INT(INTEGER) | 普通大小的整数 | 4个字节 |
BIGINT | 大整数 | 8个字节 |
1个字节代表8bits,因此TINYINT无符号数最大为2**8-1即255,INT无符号数最大为2**32-1。
浮点数类型和定点数类型:
浮点型和定点型用来表示小数,浮点型有单精度浮点型(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的增大而增大,DECIMAL的存储空间并不是固定的,而由其精度值M决定,占用M+2个字节。
不论是定点型还是浮点型,如果指定的精度超过了精度范围,则会四舍五入进行处理。
FLOAT和DOUBLE如何不指定精度,默认按照实际的精度(由计算机硬件和操作系统决定),DECIMAL不指定精度会默认为(10,0)。
MySQL中,定点数以字符串的形式储存,在对精度要求较高时(如货币,科学计算等)使用DECIMAL比较好,另外浮点数之间基尼险那个减法和比较运算时容易出问题,应尽量避免使用浮点数进行比较。
日期/时间类型:
类型名称 | 日期格式 | 日期范围 | 存储需求 |
---|---|---|---|
YEAR | YYYY | 1901~2015 | 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 00:00:01 UTC~2038-01-19 03:14:07 UTC | 4字节 |
YEAR:
可以以4位字符串或4位数字格式表示YEAR,例如输入“2010”或2010,插入到数据库的值均为2010。
以2位字符串格式表示的YEAR,范围为“00”到“99”,“00”~“69”被转化为2000~2069,“70”~“99”被转化为1970~1999。“0”和“00”作用相同,插入超过取值范围的值将被转化为2000。
以2位数字表示的YEAR,范围为1~99。1~69和70~99分别被转化为2001~2069和1970~1999,注意在这里0将被转化为0000,而不是2000。
TIME:
TIME小时部分如此大是因为TIME类型不仅可以用于表示一天的时间(必须小于24小时),还可能是某个事件过去的时间或者两个事件之间的间隔时间(可以大于24小时,甚至为负)。可以使用各种格式指定TIME值:
(1)“D HH:MM:SS”格式。还可以使用下面的任何一种“非严格”的语法:“HH:MM:SS”、“HH:MM”、“D HH:MM”、"D HH"或“SS”。这里D表示日,可取0~34之间的值,插入数据库时,D被转化为小时保存,格式为“D*24 +HH”。
(2)“HHMMSS”格式的、没有间隔符的字符串或者HHMMSS格式的数值,假定是有意义的时间,例如“102311”表示“10:23:11”,而“107898”是不合法的,储存为00:00:00。
超出TIME范围但合法的值被转化为范围最接近的端点,无效的这被转化为“00:00:00”。
DATE:
使用CURRENT_DATE或者NOW()插入当前系统时间。
MySQL允许任何标点符号都可以作为日期部分之间的间隔符,例如“12-11-12”、“12/11/12”、“12.11.12”、“12@11@12”是等价的,这些值均可以正常插入数据库。
DATETIME:
MySQL允许任何标点符号都可以作为日期部分或时间部分之间的间隔符,例如“12-11-12 11:30:23”、“12/11/12 11+30+23”是等价的,均可正常插入数据库。
TIMESTAMP:
TIMESATMP列的取值范围小于DATETIME的取值范围,因此在插入数据时注意保证数据取值的合法性。DATETIME在储存日期数据时,按实际输入的格式储存,即输入什么就储存什么,与时区无关;而TIMESTAMP值的储存 是以UTC(世界标准时间)格式保存,储存时对当前时区进行转换,检索时在转换回当前时区,即查询时,根据当前时区的不同,显示的时间值也不同。
字符串类型:
类型名称 | 说明 | 储存需求 |
---|---|---|
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^24 |
LONGTEXT | 大的非二进制字符串 | L+4字节,L<2^32 |
CAHR和VARCHAR:
CHAR(M)类型的数据列里,每个值都占用M个字节,如果某个长度小于M,MySQL就会在它的右边用空格字符补足(在检索操作中那些填补出来的空格字符将被去掉)。在VARCHAR(M)类型的数据列里,每个值只占用刚好够用的字节再加上一个用来记录其长度的字节(即总长度为L+1字节)。 CHAR是固定长度,所以他的处理速度比VARCHAR要快,但他的缺点是浪费存储空间,所以对储存不大,但在速度上有要求的可以使用CHAR,反之使用VARCHAR实现。
储存引擎对于选择CHAR和VARCHAR的影响:
MyISAM:最好用CHAR代替VARCHAR,这样可以使整个表静态化,从而使检索更快,以空间换时间。
InnoDB:使用VARCHAR,因为InnoDB的存储格式不分固定长度和可变长度,因此使用VARCHAR不一定比使用CHAR更好,但由于VARCHAR是按照实际的长度储存,比较节省空间,所以对磁盘I/O和数据存储总量比较好。
TEXT:
TEXT列保存非二进制字符串,如文章评论,内容等。
二进制类型:
类型名称 | 说明 | 存储需求 |
---|---|---|
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 |
复合类型:
类型名称 | 说明 | 存储需求 |
---|---|---|
ENUM | 枚举类型 | 1或2个字节,取决于枚举值的数目(最大值65535) |
SET | 一个设置,字符串可以有0个或多个SET成员 | 1,2,3,4或8个字节,取决于集合成员的数量(最多64个成员) |
EMUN:
ENUM是一个字符串对象,其值为表创建时在列规定中枚举的一列值。语法格式:
字段名 ENUM("值1","值2",..."值n")
ENUM类型在取值时,只能在指定的枚举列表中取,而且一次只能取一个。ENUM值在内部用整数表示,每个枚举值均有一个索引值:列表值所允许的成员值从1开始,最多可有65535个元素,查看索引值:
SELECT 字段名+0 FROM 表名;
SET:
SET是一个字符串对象,可以有0个或多个值,SET列最多有64个成员,其值为表创建时规定的一列值。指定包括多个SET成员的SET值时,成员之间用逗号隔开,语法格式:
字段名 SET("值1","值2",..."值n")
SET值在内部同样用整数表示,列表中每一个值都有一个索引编号,SET类型的列可从定义的列值中选择多个字符串的联合。
如果插入SET字段中列值有重复,MySQL会自动去除重复的值;插入SET字段的值的顺序并不重要,MySQL存入数据库时按照定义的顺序储存;如果插入了不正确的值,默认情况下,MySQL会忽略这些值,并给出警告。