mysql学习笔记03-数据类型

1. 整型

先从最基本的数据类型整型说起，首先用一张表格归纳一下：

数据类型	字节数	带符号最小值	带符号最大值	不带符号最小值	不带符号最大值
TINYINT	1	-128	127	0	255
SMALLINT	2	-32768	32767	0	65535
MEDIUMINT	3	-8388608	8388607	0	16777215
INT	4	-2147483648	2147483647	0	4294967295
BIGINT	8	-9223372036854775808	9223372036854775807	0	18446744073709551616

所以从实际开发的角度，我们一定要为合适的列选取合适的数据类型，即到底用不用得到这种数据类型？举个例子：

• 一个枚举字段明明只有0和1两个枚举值，选用TINYINT就足够了，但在开发场景下却使用了BIGINT，这就造成了资源浪费

要知道，MySQL本质上是一个存储，以Java为例，可以使用byte类型的地方使用了long类型问题不大，因为绝大多数的对象在程序中都是短命对象，方法执行完毕这块内存区域就被释放了，7个字节实际上不存在浪不浪费一说。但是MySQL作为一个存储，8字节的BIGINT放那儿就放那儿了，占据的空间是实实在在的。

最后举个例子：

drop table if exists test_tinyint;
create table test_tinyint (
    num tinyint
) engine=innodb charset=utf8;
 
insert into test_tinyint values(-100);
insert into test_tinyint values(255);

第二个insert语句报错"Out of range value for column 'num' at row 1"，即插入的数字范围越界了，这也同样反映出MySQL中整型默认是带符号的。

把tinyint字段定义改为"num tinyint unsigned"第二个插入就不会报错了，但是第一个插入-100又报错了，因为无符号整型是无法表示负数的。

整型（N）形式

在开发中，我们会碰到有些定义整型的写法是int(11)，这种写法是没用的（只有varchar和浮点型才有用），因为int(N)中，无论N等于多少，int永远占4个字节，其他整型同理，因此从开发的角度而言，整型的这个几乎无用。

2. 浮点型

在MySQL中浮点型有两种，分别为float、double，如下所示：

数据类型	字节数	备注
float	4	单精度浮点型
double	8	双精度浮点型

float(M,D)、double(M、D)的用法规则：

• D表示浮点型数据小数点之后的精度，假如超过D位则四舍五入，float(5, 2)即1.233四舍五入为1.23，1.237四舍五入为1.24
• M表示浮点型数据总共的位数，float(5, 2)D=2则表示总共支持五位，即小数点前只支持三位数，所以插入类似1000.23、10000.233、100000.233 这种数据会报错。
• 当我们不指定M、D的时候，会按照实际的精度来处理。如字段类型为float(10, 2)，插入1234567.66时，可能在数据库中存储的是1234567.62。注意只可能会丢失小数点后的精度。

定点型（decimal）

float、double类型存在精度丢失问题，即写入数据库的数据未必是插入数据库的数据，而decimal不会存在精度丢失问题，感觉decimal是以字符串形式存储的浮点数，decimal类型常见于银行系统、互联网金融系统等对小数点后的数字比较敏感的系统中。

3. 日期类型

MySQL支持五种形式的日期类型：date、time、year、datetime、timestamp：

数据类型	字节数	格式	备注
year	1	yyyy	存储年
date	3	yyyy-MM-dd	存储日期值
time	3	HH:mm:ss	存储时分秒
datetime	8	yyyy-MM-dd HH:mm:ss	存储日期+时间
timestamp	4	yyyy-MM-dd HH:mm:ss	存储日期+时间，可作时间戳

datetime与timestamp两种类型的区别：

• datetime占8个字节，timestamp占4个字节
• datetime的存储范围为1000-01-01 00:00:00——9999-12-31 23:59:59，timestamp存储的时间范围为1970-01-01 08:00:01——2038-01-19 11:14:07
• 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 "修改时间",

4. char和varchar类型

char和varchar的区别：

1. char是固定长度字符串，无论字符实际长度是多少，都会按照指定长度存储，不够的用空格补足；varchar为可变长度字符串，在utf8编码的数据库中其长度范围为0~21844
2. char实际占用的字节数等于存储字符所占用的字节数，varchar实际占用的字节数为存储的字符+1或+2或+3
3. MySQL处理char类型数据时会将结尾的所有空格处理掉而varchar类型数据则不会

char类型数据时会将结尾的所有空格处理掉而varchar类型数据则不会，验证SQL如下：

create table test_string (
    char_value char(5),
    varchar_value varchar(5)
) engine=innodb charset=utf8;

insert into test_string values('a', 'a');//两个字段长度都为1
insert into test_string values(' a', ' a');//两个字段长度都为2
insert into test_string values('a ', 'a ');//第一个长度为1，第二个为2
insert into test_string values(' a ', ' a ');//第一个长度为2，第二个为3

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

把大小改为32767，也是和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=32766，那么为什么会有这样的区别呢，分点详细解释一下：

注意：下述的的M表示表中所有varchar(N)的N之和。

• MySQL要求一个行的定义长度不能超过65535即64K
• 一个表中的字段，如果有一个或多个字段可以为空，那么会有1个字节专门记录字段是否为空这个状态 
• 当M范围为0<=M<=255时会专门有一个字节记录varchar型字符串长度，当M>255时会专门有两个字节记录varchar型字符串的长度，把这一点和上一点结合，那么65535个字节实际可用的为65535-3=65532个字节
• 所有英文无论其编码方式，都占用1个字节，但对于gbk编码，一个汉字占两个字节，因此最大M=65532/2=32766；对于utf8编码，一个汉字占3个字节，因此最大M=65532/3=21844，上面的结论都成立

同样的，上面是表中只有varchar型数据的情况，如果表中同时存在int、double、char这些数据，需要把这些数据所占据的空间减去，才能计算varchar(M)型数据M最大等于多少。

 

5. text和blob

它们是为了存储大数据使用的，因为MySql单行最大数据量为64K。

text存储的是字符串，可以是任意长度的字符串；而blob存储的是二进制字符串，简如图片、音视频这种文件的二进制数据的。

如果一个表中有字段为text或者blob时，存储方式为在一行数据中的该字段处，用一个占有4个字节的指针指向对应的text或者blob。