MySQL数据类型大总结

1. 基本数据类型

MySQL所支持的数据类型：

整数类型	字节	最小值	最大值
tinyint	1	有符号-128，无符号0	有符号127， 无符号255
smallint	2	有符号-32768， 无符号0	有符号32767， 无符号65535
mediumint	3	有符号-8388608， 无符号0	有符号8388607，无符号1677215
int, integer	4	有符号-2147483648，无符号0	有符号2147483647，无符号4294967295
bigint	8	有符号-9223370236854775808， 无符号0	有符号9223370236854775807， 无符号 18446744073709551615

浮点数类型	字节	最小值	最大值
float	4	± 1.175494351E-38	± 3.402823466E+38
double	8	± 2.2250738585072014E-308	± 1.7976931348623157E+308

定点数类型	字节	描述
dec(M, D), decimal(M, D)	M+2	最大取值范围与Double相同， 给定decimal的有效取值范围由M和D决定

位类型	字节	最小值	最大值
bit	1-8	bit(1)	bit(64)

1.整数类型

create table xxx(
	id int,
	id2 int(5)
)

这里的int(5)指定的是数值的宽度（只有一个int则默认11位）
这里有一个修饰词是zerofill,就是说如果我指定数位宽度为5，那么不足5位就在前边用0补全
alter table xxx modify id int zerofill;
alter table xxx modify id2 int(5) zerofill;
这个时候我们查询里边的记录， 会发现不满位数的都在前边加了0
如果插入的数超过了数位怎么办？
不要慌， 如果我们指定int(3), 但是我们插入了99999，那么她还是会按照99999来保存， 只是如果你没到足够的数位，会帮你补齐而已

自增属性 auto_increment
我们可以指定某个属性是自增的，每插入一条，他就会自己加一，如果某个字段是自增的，那么他一定不会是null， 并且是primary key或者unique键，并且自增在一个表中最多有一个。

create table xxx(
	id int primary key auto_increment,
	name varchar(20)
)
-- 或者
create table xxx(
	id int auto_increment,
	name varchar(20),
	primary key(id)
)
--或者
create table xxx(
	id int auto_increment,
	name varchar(20),
	unique(id)
)

2. 小数类型

小数类型分为浮点数(float, double)和定点数(decimal) , 其中定点数在MySQL中以字符串的形式来存放， 比浮点数更准确，适合用于存放货币这些精确度高的数据
小数后边的两个属性(M, D) 分别表示精度和标度，M 表示整数部分加小数部分一共M位数（不算小数点，比如999.9999）D则是小数位数
MySQL保存的时候是会进行四舍五入的， 如果在float(7, 4)存999.00009，那么结果就是999.0001. 并且浮点数后边跟上(M, D）的用法是非标准用法， 如果用于数据库迁移， 那么最好不要这么使用。
float和double在不指定M 和D的时候，默认是实际的精度（由实际的硬件和操作系统决定）
decimal在不指定精度的时候，默认整数位是10， 默认小数位是0

create table xxx(
	id float(5, 2) default null,
	id2 double(5, 2) default null,
	id3 decimal(5, 2) default null
)

如果我们给三个数都插入1.23
那么都可以存成1.23 。 假如是1.234呢？
那么结果也都是1.23， 因为小数位不够，就要进行四舍五入， 并且decimal插入的时候还会有warning，可以通过show warnings查看
warning说道，这个数据会被截断，只留下1.23

这个时候我们把精度都去掉,再次插入1.23

alter table xxx modify id float;
alter table xxx modify id2 double;
alter table xxx modify id3 decimal;

可以发现float和double可以正常插入，但是decimal会被截断， 因为decimal默认小数位为0嘛（默认decimal(10, 0)）

3.位类型

BIT类型就是存放字段值，bit(M)可以存放多位二进制数， M范围从1到64， 默认是M =1
值得注意的是，我们用select 是查询不到位的值的， 可以用bin() 或者hex()函数进行读取

create table xxx(
	id bit(1)
)  -- 创建表

insert into xxx values (1);  -- 插入一条数据
select * from xxx;   --这样看不到结果

select bin(id), hex(id) from xxx;
--这样就可以看到结果，bin()表示二进制形式， hex()表示十六进制形式

在插入数据的时候，会先转换为二进制数， 如果位数合理，那么就会成功插入， 否则会失败，如果我们在bit(1)的字段插入2就会失败， bit(1)最大才是1（只有0,1两种选择）

2. 日期时间类型

用来表示年月日用date
用来表示年月日时分秒， 则用datetime
用来表示时分秒， 用time

日期和时间类型	字节	最小值	最大值
date	4	1000-01-01	9999-12-31
datetime	8	1000-01-01 00:00:00	9999-12-31 23:59:59
timestamp	4	19700101080001	2038年的某个时刻(万一到了就又要更新哈哈哈哈)
time	3	-838:59:59	838:59:59
year	1	1901	2155

如果需要经常更新日期为当前系统时间，那么用时间戳合适timestamp
timestamp值返回后显示为YYYY-MM-DD HH:MM:SS 格式
如果只是表示年份，可以用year来表示， 他比date占用更少空间， year有2位或者4位的格式的年， 默认是四位表示， 四位中范围是1901-2155,两位则是70-69（1970-2069）不过2位的格式从5.5.27版本以后不再支持

常用的是date, time, datetime这三个

create table xxx (
	d date,
	t time,
	dt datetime
)
-- 插入数据
insert into xxx values (now(),now(),now());

--查看结果
select * from xxx;

可以看到这三种类型的字符串格式

timestamp也可以用来表示日期

create table xxx(
	id timestamp
)

-- 查看xxx表的描述
desc xxx;   //可以看到时间戳默认值是当前的系统时间戳

值得注意的是，如果我们给时间戳字段插入null， 那么他会保存当前的时间

如果表中有多个timestamp类型，那么系统只给表中第一个时间戳设置默认值， 其他的则默认是0，也可以修改其他的为其他常量时间值， 但是不可是current_stamp
时区问题
timestamp是和时区相关的，当插入日期的时候，会先转换为本地时区存放，从数据库取出的时候也会先将日期转换为本地时区，这样两个时区的人看到的值不一样

查看时区 show variables like 'time_zone'

当前时区跟随系统
我们可以设置时区set time_zone='+8:00'设置时区为东八区， 也就是中国的时间

时间就切换到了中国

时间格式
日期的插入方式有很多，整数(2000), 字符串(2020-02-02), 或者函数(now())等都可以表示

YYYY-MM-DD HH:MM:SS格式的字符串，允许不严格语法， 只要可以作为日期的分隔符就可以，比如
2020-01-01 12:01:01
2020-01-01 12:1:1
2020@01@01 12+01+01
2020/02/02 113034
等格式都可以表示

YYYYMMDDHHMMSS格式因为没有分隔符， 只要分隔开表示的时间合法就可以

3.字符串类型

MySQL提供多种字符串的存储， 不同版本略有差异

字符串类型	字节	描述及存储要求
char(M)	M	M为0-255之间的整数
varchar(M)		M为0-65535之间的整数， 值得长度+1个字节
tinyblob		允许长度0-255字节， 值的长度+1个字节
blob		允许长度0-65535字节， 值的长度+3个字节
mediumblob		允许长度0-167772150字节，值的长度+3个字节
longblob		允许长度0-4294967295字节，值的长度+4字节
tinytext		允许长度0-255字节，值的长度+2个字节
text		允许长度0-65535字节，值的长度+2个字节
mediumtext		允许长度0-167772150字节，值的长度+3字节
longtext		允许长度0-4294967295字节，值的长度+4字节
varbinary(M)		允许长度0-M个自己的变长字符串， 值的长度+1个字节
binary(M)	M	允许长度0-M个字节的定长字节字符串

1. char和varchar类型

这两个都是用来保存MySQL中较短的字符串的， 主要区别在于存储方式不同，char的长度固定为创建表的时候声明的长度， 但是varchar则是可变的长字符串。在索引的时候char会删除尾部的多余空格， 但是varchar却保留。

create table xxx(
	v varchar(4),
	c char(4)
)

insert into xxx values ('ab  ', 'ab  ')
select length(v), lrngth(c) from xxx;

可以看到length(v)结果是4而length©是2

2. binary和varbinary类型

binary和varbinary类似于char和varchar，只不过binary保存的是二进制字符串，具体保存如下

create table xxx(
	c binary(3)
)

insert into xxx set c='a';

select *, hex(c), c='a',c='a\0', c='a\0\0' from xxx;

结果

可以发现，存放binary的时候， 如果位数不够就用\0来补全

3. enum类型

enum即枚举类型， 他的值的范围需要在创建表的时候通过枚举方式显式指定， 对于1-255个成员需要一个字节存储， 255-65535则需要2个字节存储，最多允许有65535个成员

create table xxx (
	gender enum('m', 'f')
)

insert into xxx values ('m'),('1'),('F'),(null);

select * from xxx;

结果是m ,m , f, null;
我们插入m,M都是m, 而f, F都是f,忽略大小写，如果插入没有被枚举的数的时候，就默认按第一个来存， 这个要注意。