详解MySQL数据类型
基本语法，有了语法，我们才可以组织逻辑
数据类型，即在特定场景下选择合适的数据类型，到底是⽤整型还是浮点型还是字符串，每种数据机构占多少字节，最⼤值是多少。这点只针对强类型的语⾔，像js这种弱类型的语⾔，是不需要考虑这⼀点的
希望通过⼀篇⽂章的梳理，可以把MySQL数据结构这块都归纳清楚。
整型
先从最基本的数据类型整型说起，⾸先⽤⼀张表格归纳⼀下：
数据类型字节数带符号最⼩值带符号最⼤值不带符号最⼩值不带符号最⼤值
TINYINT1-1281270255
SMALLINT2-3276832767065535
MEDIUMINT3-83886088388607016777215
INT4-2147483648214748364704294967295
BIGINT8-92233720368547758089223372036854775807018446744073709551616
即使是带符号的BIGINT，其实也已经是⼀个天⽂数字了，什么概念，9223372036854775807我们随便举下例⼦：
以byte为例可以表⽰8589934592GB-->8388608TB-->8192PB
以毫秒为例可以表⽰292471208年
所以从实际开发的⾓度，我们⼀定要为合适的列选取合适的数据类型，即到底⽤不⽤得到这种数据类型？举个例⼦：
⼀个枚举字段明明只有0和1两个枚举值，选⽤TINYINT就⾜够了，但在开发场景下却使⽤了BIGINT，这就造成了资源浪费
简单计算⼀下，假使该数据表中有100W数据，那么总共浪费了700W字节也就是6.7M左右，如果更多的表这么做了，那么浪费的更多
要知道，MySQL本质上是⼀个存储，以Java为例，可以使⽤byte类型的地⽅使⽤了long类型问题不⼤，因为绝⼤多数的对象在程序中都是短命对象，⽅法执⾏完毕这块内存区域就被释放了，7个字节实际上
不存在浪不浪费⼀说。但是MySQL作为⼀个存储，8字节的BIGINT放那⼉就放那⼉了，占据的空间是实实在在的。
最后举个例⼦：
1 drop table if exists test_tinyint;
2 create table test_tinyint (
3    num tinyint
4 ) engine=innodb charset=utf8;
5
6 insert into test_tinyint values(-100);
7 insert into test_tinyint values(255);
执⾏第7⾏的代码时候报错"Out of range value for column 'num' at row 1"，即很清楚的我们可以看到插⼊的数字范围越界了，这也同样反映出MySQL中整型默认是带符号的。
把第3⾏的num字段定义改为"num tinyint unsigned"第7的插⼊就不会报错了，但是第6⾏的插⼊-100⼜报错了，因为⽆符号整型是⽆法表⽰负数的。
整型（N）形式
在开发中，我们会碰到有些定义整型的写法是int(11)，这种写法从我个⼈开发的⾓度看我认为是没有多⼤⽤，不过作为⼀个知识点做⼀下讲解吧。
int(N)我们只需要记住两点：varchar2最大长度
⽆论N等于多少，int永远占4个字节
N表⽰的是显⽰宽度，不⾜的⽤0补⾜，超过的⽆视长度⽽直接显⽰整个数字，但这要整型设置了unsigned zerofill才有效
下⾯举个例⼦，写⼀段SQL：
drop table if exists test_int_width;
create table test_int_width (
a int(5),
b int(5) unsigned,
c int(5) unsigne
d zerofill,
d int(8) unsigned zerofill
) engine=innodb charset=utf8;
insert into test_int_width values(1, 1, 1, 1111111111);
select * from test_int_width;
从上⾯的两点，我们应该预期结果应该是1,1,00001,1111111111
我们看⼀下结果：
不符合预期是吧，因为这个问题我也有过困扰，后来查了⼀下貌似是Navicat⼯具本⾝的问题，我们使⽤控制台就不会有这个问题了：
不过实际⼯作场景中反正我是没有碰到过指定zerofill的，也不知道具体应⽤场景，如果有使⽤这种写法的朋友可以留⾔告知具体在哪种场景下⽤到了这种写法。
浮点型
整型之后，下⾯是浮点型，在MySQL中浮点型有两种，分别为float、double，它们三者⽤⼀张表格总结⼀下：
数据类型字节数备注
float4单精度浮点型
double8双精度浮点型
下⾯还是⽤SQL来简单看⼀下float和double型数据，以float为例，double同理：
drop table if exists test_float;
create table test_float (
num float(5, 2)
) engine=innodb charset=utf8;
insert into test_float values(1.233);
insert into test_float values(1.237);
insert into test_float values(10.233);
insert into test_float values(100.233);
insert into test_float values(1000.233);
insert into test_float values(10000.233);
insert into test_float values(100000.233);
select * from test_float;
显⽰结果为：
从这个结果我们总结⼀下float(M,D)、double(M、D)的⽤法规则：
D表⽰浮点型数据⼩数点之后的精度，假如超过D位则四舍五⼊，即1.233四舍五⼊为1.23，1.237四舍五⼊为1.24
M表⽰浮点型数据总共的位数，D=2则表⽰总共⽀持五位，即⼩数点前只⽀持三位数，所以我们并没有看到1000.23、10000.233、100000.233这三条数据的插⼊，因为插⼊都报错了
当我们不指定M、D的时候，会按照实际的精度来处理。
定点型
介绍完float、double两种浮点型，我们介绍⼀下定点型的数据类型decimal类型，有了浮点型为什么我们还需要定点型？写⼀段SQL看⼀下就明⽩了：
drop table if exists test_decimal;
create table test_decimal (
float_num float(10, 2),
double_num double(20, 2),
decimal_num decimal(20, 2)
) engine=innodb charset=utf8;
insert into test_decimal values(1234567.66, 1234567899000000.66, 1234567899000000.66);
insert into test_decimal values(1234567.66, 12345678990000000.66, 12345678990000000.66);
运⾏结果为：
看到float、double类型存在精度丢失问题，即写⼊数据库的数据未必是插⼊数据库的数据，⽽decimal⽆论写⼊数据中的数据是多少，都不会存在精度丢失问题，这就是我
们要引⼊decimal类型的原因，decimal类型常见于银⾏系统、互联⽹⾦融系统等对⼩数点后的数字⽐较敏感的系统中。
最后讲⼀下decimal和float/double的区别，个⼈总结主要体现在两点上：
float/double在db中存储的是近似值，⽽decimal则是以字符串形式进⾏保存的
decimal(M,D)的规则和float/double相同，但区别在float/double在不指定M、D时默认按照实际精度来处理⽽decimal在不指定M、D时默认为decimal(10, 0)
⽇期类型
接着我们看⼀下MySQL中的⽇期类型，MySQL⽀持五种形式的⽇期类型：date、time、year、datetime、timestamp，⽤⼀张表格总结⼀下这五种⽇期类型：
数据类型字节数格式备注
date3yyyy-MM-dd存储⽇期值
time3HH:mm:ss存储时分秒
year1yyyy存储年
datetime8yyyy-MM-dd HH:mm:ss存储⽇期+时间
timestamp4yyyy-MM-dd HH:mm:ss存储⽇期+时间，可作时间戳
下⾯我们还是⽤SQL来验证⼀下：
drop table if exists test_time;
create table test_time (
date_value date,
time_value time,
year_value year,
datetime_value datetime,
timestamp_value timestamp
) engine=innodb charset=utf8;
insert into test_time values(now(), now(), now(), now(), now());
看⼀下插⼊后的结果：
MySQL的时间类型的知识点⽐较简单，这⾥重点关注⼀下datetime与timestamp两种类型的区别：
上⾯列了，datetime占8个字节，timestamp占4个字节
由于⼤⼩的区别，datetime与timestamp能存储的时间范围也不同，datetime的存储范围为1000-01-01 00:00:00——9999-12-31 23:59:59，timestamp存储的时间范围为19700101080001——20380119111407
datetime默认值为空，当插⼊的值为null时，该列的值就是null；timestamp默认值不为空，当插⼊的值为null的时候，mysql会取当前时间
datetime存储的时间与时区⽆关，timestamp存储的时间及显⽰的时间都依赖于当前时区
在实际⼯作中，⼀张表往往我们会有两个默认字段，⼀个记录创建时间⽽另⼀个记录最新⼀次的更新时间，这种时候可以使⽤timestamp类型来实现：
create_time timestamp default current_timestamp comment "创建时间",
update_time timestamp default current_timestamp on update current_timestamp comment "修改时间",
char和varchar类型
最后看⼀下常⽤到的字符型，说到MySQL字符型，我们最熟悉的应该就是char和varchar了，关于char和varchar的对⽐，我总结⼀下：
1. char是固定长度字符串，其长度范围为0~255且与编码⽅式⽆关，⽆论字符实际长度是多少，都会按照指定长度存储，不够的⽤空格补⾜；varchar为可变长度字符
串，在utf8编码的数据库中其长度范围为0~21844
2. char实际占⽤的字节数即存储的字符所占⽤的字节数，varchar实际占⽤的字节数为存储的字符+1或+2或+3
3. MySQL处理char类型数据时会将结尾的所有空格处理掉⽽varchar类型数据则不会
关于第⼀点、第⼆点，稍后专门开⼀个篇幅解释，关于第三点，写⼀下SQL验证⼀下：
drop table if exists test_string;
create table test_string (
char_value char(5),
varchar_value varchar(5)
) engine=innodb charset=utf8;
insert into test_string values('a', 'a');
insert into test_string values(' a', ' a');
insert into test_string values('a ', 'a ');
insert into test_string values(' a ', ' a ');
使⽤length函数来看⼀下结果：
验证了我们的结论，char类型数据并不会取最后的空格。
varchar型数据占⽤空间⼤⼩及可容纳最⼤字符串限制探究
接上⼀部分，我们这部分来探究⼀下varchar型数据实际占⽤空间⼤⼩是如何计算的以及最⼤可容纳的字符串为多少，⾸先要给出⼀个结论：这部分和具体编码⽅式有关，且MySQL版本我现在使⽤的是5.7，当然5.0之后的都是可以的。
先写⼀段SQL创建表，utf8的编码格式：
drop table if exists test_varchar;
create table test_varchar (
varchar_value varchar(100000)
) engine=innodb charset=utf8;
执⾏报错：
Column length too big for column 'varchar_value' (max = 21845); use BLOB or TEXT instead
按照提⽰，我们把⼤⼩改为21845，执⾏依然报错：
Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs 改为21844就不会有问题，因此在utf8编码下我们可以知道varchar(M)，M最⼤=21844。那么gbk呢：
drop table if exists test_varchar;
create table test_varchar (
varchar_value varchar(100000)
) engine=innodb charset=gbk;
同样的报错：
Column length too big for column 'varchar_value' (max = 32767); use BLOB or TEXT instead
把⼤⼩改为32766，也是和utf8编码格式⼀样的报错：
Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs 可见gbk的编码格式下，varchar(M)最⼤的M=32765，那么为什么会有这样的区别呢，分点详细解释⼀下：
MySQL要求⼀个⾏的定义长度不能超过65535即64K
对于未指定varchar字段not null的表，会有1个字节专门表⽰该字段是否为null
varchar(M)，当M范围为0<=M<=255时会专门有⼀个字节记录varchar型字符串长度，当M>255时会专门有两个字节记录varchar型字符串的长度，把这⼀点和上⼀点结合，那么65535个字节实际可⽤的为65535-3=65532个字节
所有英⽂⽆论其编码⽅式，都占⽤1个字节，但对于gbk编码，⼀个汉字占两个字节，因此最⼤M=65532/2=32766；对于utf8编码，⼀个汉字占3个字节，因此最⼤M=65532/3=21844，上⾯的结论都成⽴
举⼀反三，对于utfmb4编码⽅式，1个字符最⼤可能占4个字节，那么varchar(M)，M最⼤为65532/4=16383，可以⾃⼰验证⼀下
同样的，上⾯是表中只有varchar型数据的情况，如果表中同时存在int、double、char这些数据，需要把这些数据所占据的空间减去，才能计算varchar(M)型数据M最⼤等于多少。
varchar、text和blob
最后讲⼀讲text和blob两种数据类型，它们的设计初衷是为了存储⼤数据使⽤的，因为之前说了，MySql单⾏最⼤数据量为64K。
先说⼀下text，text和varchar是⼀组既有区别⼜有联系的数据类型，其联系在于当varchar(M)的M⼤于某
些数值时，varchar会⾃动转为text：M>255时转为tinytext
M>500时转为text
M>20000时转为mediumtext
所以过⼤的内容varchar和text没有区别，同事varchar(M)和text的区别在于：
单⾏64K即65535字节的空间，varchar只能⽤63352/65533个字节，但是text可以65535个字节全部⽤起来
text可以指定text(M)，但是M⽆论等于多少都没有影响
text不允许有默认值，varchar允许有默认值
varchar和text两种数据类型，使⽤建议是能⽤varchar就⽤varchar⽽不⽤text（存储效率⾼），varchar(M)的M有长度限制，之前说过，如果⼤于限制，可以使⽤mediumtext（16M）或者longtext（4G）。
⾄于text和blob，简单过⼀下就是text存储的是字符串⽽blob存储的是⼆进制字符串，简单说blob是⽤于存储例如图⽚、⾳视频这种⽂件的⼆进制数据的。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。