mysql数据类型

mysql数据类型


mysql支持多种数据类型,包括数值类型、浮点型、日期/时间类型和字符串类型等

整数类型

类型 存储需求 取值范围 无符号取值范围
tinyint 1字节 -2^7 ~ 2^7 - 1 0 ~ 2^8-1
smallint 2字节 -2^15 ~ 2^15-1 0 ~ 2^16-1
mediumint 3字节 -2^23 ~ 2^23-1 0 ~ 2^24-1
int(integer) 4字节 -2^31 ~ 2^31-1 0 ~ 2^32-1
bigint 8字节 -2^63 ~ 2^63-1 0 ~ 2^64-1

注意
不同的整数类型,取值范围不同,所需要的存储空间也不同。因此,要根据实际需要选择最合适的数据类型。

浮点数类型和定点数类型

浮点数类型可以用(M,D)来表示,M为精度,表示总共的位数;D为标度,表示小数的位数。其中FLOAT为单精度浮点数类型,DOUBLE为双精度浮点数类型。

类型名称 存储需求 取值范围 无符号的取值范围
FLOAT 4字节 -3.402823466E+38到-1.175494351E-38 0和1.175494351E-38到3.402823466E+38
DOUBLE 8字节 -1.7976931348623157E+308到-2.2250738585072014E-308 0和2.2250738585072014E-308到 1.7976931348623157E+308

注意
这些是理论限制,基于IEEE标准。实际的范围根据硬件或操作系统的不同可能稍微小些。

在mysql中,还可以使用定点数表示小数。

类型名称 存储需求 说明
DECIMAL(M,D), DEC M+2字节 压缩的严格定点数

DECIMAL类型不同于FLOAT和DOUBLE,它实际是以字符串存储的。它的有效取值范围由M和D决定。如果改变M而固定D,其取值范围将随M的变大而变大。如果固定M而改变D,则其取值范围将随D的变大而变小(精度增加)。

注意
DECIMAL的默认M值是10,D值是0。
DECIMAL整数最大位数(M)为65。支持的十进制数的最大位数(D)是30。
所有DECIMAL列的基本计算(+,-,*,/)用65位精度完成。

日期类型和时间类型

类型名称 日期格式 日期范围 存储需求
YEAR YY / YYYY 1970到2069 / 1901到2155 1字节
DATE YYYY-MM-DD 1000-01-01到9999-12-31 3字节
TIME HH:MM:SS ‘-838:59:59’到’838:59:59’ 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’到’2038-01-19 03:14:07’ 4字节

注意
TIMESTAMP为UTC时间,转换为东八区就是1970-01-01 08:00:01到2038-01-19 11:14:07

1. YEAR

两位或四位格式的年。默认是四位格式。

四位

可以用四位字符串或四位数字表示,范围是1901~2155。

两位

可以用两位字符串或两位数字表示,范围是00~99。

  • 两位字符串:00 ~ 99。00 ~ 69和70 ~ 99范围的值分别为2000 ~ 2069和1970 ~ 1999。
  • 两位数字:1 ~ 99。1 ~ 69和70 ~ 99范围的值分别为2001 ~ 2069和1970 ~ 1999。

注意
两位整数范围与字符串范围稍有不同。使用数字表示时,0值被转换为0000,而不是2000。

2. DATE

DATE类型用在只需要日期信息时,没有时间部分。格式为YYYY-MM-DD。
指定方式:

  • 以YYYY-MM-DD或YYYYMMDD两种格式的字符串表示
  • 以YY-MM-DD或YYMMDD两种格式的字符串表示。这里的YY表示两位的年值。因为不知道是哪个世纪,MySQL使用以下规则解释年值:00 ~ 69和70 ~ 99分别转换为2000 ~ 2069和1970 ~ 1999。
  • 以YYYYMMDD或YYMMDD两种格式的数字表示。年与前文相似,00 ~ 69和70 ~ 99分别转换为2000 ~ 2069和1970 ~ 1999。
  • 使用CURRENT_DATE或NOW()插入当前日期。

3. TIME

TIME类型用在只需要时间信息时。其小时部分会如此大的原因是它不仅可以表示一天的时间(必须小于24小时),还可以表示某个事件过去的时间或两个事件之间的时间间隔(可以大于24小时,或者为负)。
指定TIME值的方式:

  • ‘D HH:MM:SS’ 格式的字符串。也可以使用下面任一种非严格语法:‘HH:MM:SS’/‘HH:MM’/‘D HH:MM’/‘D HH’或’SS’。D表示日,取值范围是0 ~ 34。插入时,D被转换为小时保存,格式为D*24+HH。
  • 'HHMMSS’格式的、没有间隔符的字符串或者数值。

指定的数值必须是有意义的,如’101112’被理解为10:11:12,但’109712’是不合法的,存储时将变为00:00:00。

冒号差异

  • 如果没有冒号,MySQL假定最右边两位表示秒。(MySQL解释TIME值为过去的时间而不是当天的时间。)例如,'1112’和1112表示00:11:12,'12’和12表示00:00:12。
  • 如果有冒号,则被看作当天的时间。即,'11:12’表示11:12:00,而不是00:11:12。

4. DATETIME

DATETIME类型用在需要同时包含日期和时间信息时。格式为:YYYY-MM-DD HH:MM:SS。指定方式:

  • 以YYYY-MM-DD HH:MM:SS或者YYYYMMDDHHMMSS两种格式的字符串
  • 以YY-MM-DD HH:MM:SS或者YYMMDDHHMMSS两种格式的字符串
  • 以YYYYMMDDHHMMSS或者YYMMDDHHMMSS两种格式的数字

5. TIMESTAMP

TIMESTAMP的显示格式与DATETIME相同,也是YYYY-MM-DD HH:MM:SS。但TIMESTAMP的取值范围小于DATETIME的取值范围,为’1970-01-01 00:00:01’ UTC到’2038-01-19 03:14:07’ UTC。世界标准时间(Coordinated Universal Time),即UTC。
DATETIME类型或TIMESTAMP类型与DATE类型可以相互转换,未包含的时间部分会被设置为00:00:00,多出的部分会被删除。

区别
TIMESTAMP与DATETIME除了存储字节和范围不同,还有存储格式不同。DATETIME按照实际输入的格式存储,与时区无关;而TIMESTAMP存储是以UTC格式保存,存储时对当前时区进行转换,检索时再转换回当前时间。即,查询时根据当前时区不同,显示时间值不同。

字符串类型

MySQL支持两类字符串数据:文本字符串和二进制字符串。文本字符串可以进行区分或不区分大小写的串比较,也可以进行模式匹配查找。字符串类型有:CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT。

类型 说明 最大长度 存储需求
CHAR(M) 固定长度的字符串 255 M字节,1<=M<=255
VARCHAR(M) 变长字符串 65535 L+1字节,L<=M和1<=M<=255
TINYTEXT 非常小的字符串 255(2^8-1)字符 L+1字节,L<28
TEXT 字符串 65535(2^16-1)字符 L+2字节,L<216
MEDIUMTEXT 中等字符串 16777215(2^24-1)字符 L+3字节,L<224
LONGTEXT 大字符串 4294967295或4GB(2^32-1)字符 L+4字节,L<232

CHAR和VARCHAR类型

CHAR(M)为固定长度字符串,M表示列长度,M的范围是0 ~ 255。保存CHAR值时在右侧填充空格以达到指定的长度;检索CHAR值时,尾部的空格将被删除。在存储或检索过程中,不进行大小写转换。
VARCHAR(M)是长度可变的字符串,M表示最大列长度,M的范围是0 ~ 65535。VARCHAR最大实际长度由最长的行大小和使用的字符集确定,而其实际占用的空间为字符串的长度加1.
下表显示了将各种字符串值保存到CHAR(4)和VARCHAR(4)列后的结果,说明了CHAR和VARCHAR之间的差别:

CHAR(4) 存储需求 VARCHAR(4) 存储需求
‘’ ’ ’ 4个字节 ‘’ 1个字节
‘ab’ 'ab ’ 4个字节 'ab ’ 3个字节
‘abcd’ ‘abcd’ 4个字节 ‘abcd’ 5个字节
‘abcdefgh’ ‘abcd’ 4个字节 ‘abcd’ 5个字节

请注意上表中最后一行的值只适用不使用严格模式时;如果MySQL运行在严格模式,超过列长度不的值不保存,并且会出现错误。

TEXT类型

TEXT列保存非二进制字符串。TEXT类型有四种:TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT。不同类型的存储空间和数据长度不同。

二进制类型

类型名称 说明 最大长度 存储需求
BIT(M) 位字段类型 64 大约(M+7)/8个字节
BINARY(M) 固定长度二进制字符串 M个字节
VARBINARY(M) 变长二进制字符串 M+1个字节
TINYBLOB(M) 小BLOB 255(2^8-1)字节 L+1字节,在此L<2^8
BLOB(M) BLOB 65535(2^16-1)字节 L+2字节,在此L<2^16
MEDIUMBLOB(M) 中等BLOB 16777215(2^24-1)字节 L+3字节,在此L<2^24
LONGBLOB(M) 大BLOB 4294967295或4GB(2^32-1)字节 L+4字节,在此L<2^32

BIT

BIT类型为位字段类型,M默认是1。

BINARY和VARBINARY

BINARY和VARBINARY类型类似于CHAR和VARCHAR,不同的是它们包含二进制字节字符串。
BINARY长度是固定的。保存时,不足最大长度的,将在它们右边填充值补齐以达到最大长度。

填充值是0x00(零字节),比较时,0x00字节和空格是不同的,0x00<空格。

BLOB

BLOB是一个二进制大对象,用来存储可变数量的数据。
BLOB列没有字符集,排序和比较基于列值字节的数值。

复合数据类型

MySQL数据库执行两种复合数据类型,分别是ENUM类型和SET类型。
一个ENUM类型只允许从一个集合中取得一个值,而SET类型允许 从一个集合中取得任意多个值。

ENUM类型

ENUM是一个字符串对象,其值为表创建时在列规定中显示枚举的一列值。其语法格式:

column_name ENUM(’value1', 'value2', ... , 'valuen')

创建表时,ENUM成员值尾部的空格将被自动删除。

ENUM类型字段在取值时,只能在指定的枚举列表中取,而且一次只能取一个。
在某些情况下,ENUM值也可以为NULL或空字符串(‘’)。

  • 如果将一个非法值插入ENUM列,将插入空字符串以作为特殊错误值。该字符串与普通空字符串不同,它有数值值0。
  • 如果将ENUM列声明允许NULL,NULL值作为该列的一个有效值,并且默认值也是NULL。如果ENUM列声明为NOT NULL,其默认值为允许的值列的第一个元素。

ENUM值在内部用整数表示,每个枚举值有一个索引值:

  • 列表值所允许的成员值从1开始编号
  • 空字符串错误值的索引值是0

MySQL存储的就是这个索引值。ENUM最多有65535个元素。
如果在数值上下文中检索一个ENUM值,将返回列值的索引。

drop table if exists enumtest;
create table enumtest(col enum('one', 'two', 'three'));
insert into enumtest values('one'),('two'),('three'),(NULL);
# 检索ENUM
select col, col+0 from enumtest;

将一个数字保存到ENUM列,数字被视为索引,并且保存的值是该索引对应的枚举成员。注意,这不适用于LOAD DATA。
不建议使用类似数字的枚举值来定义一个ENUM列,因为这很容易引起混淆。

SET类型

SET是一个字符串对象,可以有零个或多个值,其值为表创建时规定的一列值。

SET ('value1', 'value2', ... , 'valuen')

指定多个SET成员的列值时,各成员之间用逗号隔开。列值本身不能包含逗号,且尾部的空格将被自动删除。SET最多可以有64个成员。
与ENUM类型相同,SET值在内部用整数表示,列表中每一个值都有一个索引编号。SET值保存时,数值的低阶位对应第一个SET成员。如果在数值上下文中检索SET值,检索的值的位设置成对应组成列值的SET成员。

drop table if exists set1;
create table set1(id int primary key auto_increment, col set('a', 'b', 'c', 'd'));
insert into set1(col) values('a'),('b'),('c'), ('d'), ('a,b'), ('a,c'), ('a,d');
# 从SET列检索数值值
select *, col + 0 from set1;
# 查看SET列所有可能的值
show columns from set1 like 'col';

对于指定为SET(‘a’,‘b’,‘c’,‘d’)的列,成员有下面的十进制和二进制值:

SET成员 十进制值 二进制值
‘a’ 1 0001
‘b’ 2 0010
‘c’ 4 0100
‘d’ 8 1000

如果你为该列分配一个数值值9,其二进制形式为1001,因此第1个成员a和第4个成员d被选择,结果为a,d。

转载请说明出处内容投诉
CSS教程_站长资源网 » mysql数据类型

发表评论

欢迎 访客 发表评论

一个令你着迷的主题!

查看演示 官网购买