整数类型
类型名称 | 存储需求 | 有符号大小 | 无符号大小 | 默认宽度 |
---|---|---|---|---|
tingint | 1个字节 | -128~127 | 0~255 | 4 |
smallint | 2 | -32768~32767 | 0~65535 | 6 |
mediumint | 3 | -8388608~8388607(8百万) | 0~16777215(一千六百万) | 9 |
int | 4 | 10位数(2开头) | 0~4294967295 | 11 |
bigint | 8 | 19位数(9开头) | 20位数 | 20 |
无符号定义方法(在后面加unsigned)
create table test
(
id int(11) unsigned,
name varchar(10) ,
age tinyint(4)
)
id的类型是int(11),11表示宽度,小于这个宽度是会用空格填充,大于时会显示全部
例如int(4) 如果数据大于9999时也会全部显示,不会限制数据的大小,最大还是int的最大值
浮点数类型和定点小数类型
decimal没有指定进度默认是(10,0)
日期与时间类型
类型名称 | 日期格式 | 日期范围 | 存储需求 |
---|---|---|---|
YEAR | YYYY | 1901~2155 | 1字节 |
TIME | HH:MM:SS | -838:59:59~838:59:59 | 3字节 |
DATE | YYYY-MM-DD | 1000-01-01~9999-01-01 | 3字节 |
DATETIME | YYYY-MM-DD HH:MM:SS | 1000-01-01 00:00:00~9999-01-01 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位数字效果都是一样
-
使用两位字符串"00"~"69"将被转化为2000~2069,"70~99"将会被转化为1970~1999,"0"将会被转化为2000
-
使用两位数字表示year,1~69将被转化为2001~2069,70~99将会被转化为1970~1999,0和00将会被转化为0000,非法的year将会被转化为0000
TIME
-
'D HH:MM:SS'格式的字符串,还可以使用下面的非严格语法:'HH:MM:SS' 'HH:MM' 'D HH:MM' 'D HH'或 'SS'插入数据库时,D将会被转化为小时格式为"D+24H" 注意:使用'D :HH'时要用两位数
-
'HHMMSS'注意MM和SS的值不能超出范围 1122会被解释成00:11:12而不是而'11:12'被解释成11:12:00
DATE
-
'YYYY-MM-DD'或者'YYYYMMDD'表示年月日
-
'YY-MM-DD'或者'YYMMDD'注意年的变化00~69变为2000~2069而70~99变为1970~1999数字(不可使用YY-MM-DD
)和字符串格式一样
-
使用current_date和now()获得当前时间
-
mysql允许"不严格"语法**使用任何标点符号可以用作日期部分之间的间隔号例如'98-12@31'
DATETIME
-
'YYYY-MM-DD HH:MM:SS'和'YYYYMMDDHHMMSS'字符串表示
-
'YY-MM-DD HH:MM:SS'和'YYMMDDHHMMSS'字符串表示年的计算和date一样
-
YYYYMMDDHHMMSS和YYMMDDHHMMSS数字表示数字0开头可以省略
-
同样允许不严格语法
TIMESTAMP
-
显示宽度固定在19个字符
-
存储格式与datatime一样但是时间范围是比较要注意的
-
和datetime的另一个区别是:datatime存储日期数据时,按照输入格式存储,和时区无关,timestamp会根据时区转换到世界标准时间,检索时再转换回当前和时间,即查询时,不同时区查询到的时间是不一样的
字符串类型
文本字符串类型
类型名称 | 说明 | 存储需求 |
---|---|---|
char(M) | 固定长度的非二进制字符串 | M字节;1<=M<=255 |
varchar(M) | 变长非二进制字符 | L+1个字节,在此L<=M;1<=M<=65535 |
tinytext | 非常小的非二进制字符串 | L+1字节,在此L<2^8 |
text | 小的非二进制字符串 | L+2字节,在此L<2^16 |
mediumtext | 中等大小的非二进制字符串 | L+3字节,在此L<2^24 |
longtext | 大的非二进制字符串 | L+4字节,在此L<2^32 |
enum | 枚举类型,只能有一个枚举字符串值 | 1或者2个字节,取决于枚举值的数目(最大值65535) |
set | 一个设置,字符串对象可以有0个或者多个set成员 | 1,2,3,4或者8个字节,取决于集合成员的数量(最多64个成员) |
char和varchar
-
两者都是变长类型,其存储需求取决于列值实际长度,而不是取决于类型最大可能尺寸
-
M固定长度,不够时用空格填充,多余时会被截断(不严格模式时)
-
char存储需求固定为M但是varcahr会根据实际长度存储
text
-
保存text列值时,不删除尾部空格
enum
create table test
(
e enum('good','nice','ok')
)
insert into test values(1) #只能存入固定字段,null和字段索引值
insert into test values('good')
-
在内部用整数表示
set
-
与枚举相同,内部用整数存储,定义也和枚举相同
-
不同点在于set可以存多个字符的联合
-
重复的会被自动删除
create table test
(
e set('good','nice','ok')
)
insert into test values('good'),('good,nice'),('good,nice,nice') #分别存入good good,nice good,nice
二进制字符串类型
类型名 | 说明 | 存储需求 |
---|---|---|
bit(M) | 位字段类型 | 大约(M+7)/8个字节 |
bigary(M) | 固定长度二进制字符串 | M字节 |
varbinary(M) | 可变长度二进制字符串 | M+1字节 |
tinyblob(M) | 非常小的blob | L+1字节,L<2^8 |
blob(M) | 小的blob | L+1字节,L<2^16 |
mediumblob(M) | 中等大小blob | L+1字节,L<2^24 |
longblob(M) | 大的blob | L+1字节,L<2^32 |
bit
-
M表示每个值得位数,默认值为1,例如bit(6),存入1时,内部存入000001
-
bin()函数将数字转换为2进制
binary和varbinary
-
和char和varchar类似,binary固定存储M个字节,不够的会用\0补齐达到指定长度,而varbinary只会在后面加一个\0
blob
-
存储的是二进制大对象,和text比较
-
blob没有字符集,排序基于列值字符的数值,text有字符集,根据字符集对值进行比较
字符集概念:https://www.cnblogs.com/chyingp/p/mysql-character-set-collation.html
如何选择数据类型
浮点数和定点数
浮点数优势在于长度一定的情况下,可以存更大范围的数据,但是容易造成误差(特别是计算时),对于精度较高时建议使用decimal,decimal在mysql中使用字符串存储的,用于定义数据要求较高的数据
日期与时间类型
注意timestamp范围使用,范围是比较小的,如果插入记录时需要插入当前时间,使用timesatmp是方便的,另外timestamp在空间上比datetime更加有效
char和varchar
-
char是固定长度,var插入是可变长度,因为时候固定长度,所以处理速度比较快,缺点是空间,不过对于InnoDB引擎不区分固定长度和可变长度,有些引擎区分
-
char会自动删除尾部空格,varchar不会
enum和set
-
enum最多可取65535,set最多可取64
-
单项选择是可用enum,多选是可用set
blob和text
-
blob(二进制)主要存储图片,音频等,text只能存储纯文本