MySQL 中所谓的数据类型，是从系统的角度出发，为了方便对数据进行统一的分类，能够使用统一的方式进行管理，更好地利用有限的空间的一种手段。在 SQL 中数据类型分成了三大类：数值类型、字符串类型和时间日期类型。

数值型数据都是数据，系统将数据型分为整数型和小数型。

一、整数型

在 UTF-8 编码环境下，一个英文字符等于一个字节，一个中文字符（含繁体）等于三个字节。

在 SQL 中要更多考虑如何节省磁盘空间，所以系统将整型又细分成了5类

tinyint：迷你整型，1个字节存储，表示的状态最多为255种
smallint：小整型，2个字节存储，表示的状态最多为65535种
mediumint：中整型，3个字节存储
int、integer：标准整型，使用4个字节存储（常用）
bigint：大整形，使用8个字节存储

创建一张整型表

SQL 中的数值类型全部都是默认有符号，区分正负，有的时候需要给数据类型限定：int unsigned，无符号，从零开始。

-- 创建整型表
create table my_int(
int_1 tinyint,
int_2 smallint,
int_3 int,
int_4 bigint
)charset utf8;

-- 插入数据
insert into my_int values(100,100,100,100); -- 有效数据
insert into my_int values('a','b','199','f'); -- 无效数据
insert into my_int values(255,10000,100000,1000000); -- 错误：255超出tinyint的范围

-- 给表增加一个无符号类型
alter table my_int add int_5 tinyint unsigned; -- 添加无符号类型

-- 再次插入数据
insert into my_int values(127,10000,100000,1000000,255);

-- 指定字段显示宽度
alter table my_int add int_6 tinyint(1) unsigned; -- 指定显示宽度为1
alter table my_int add int_7 tinyint(2) zerofill; -- 显示宽度为2,0填充
insert into my_int values(1,1,1,1,1,1,1)

查看表结构的时候后，发现每个字段的数据类型之后都会自带一个括号，代表显示宽度，显示宽度没有特别的含义，只是默认的告诉用户可以显示的形式而已，不会改变数据本身的数值大小。显示宽度的意义在于当数据不够显示宽度的时候，会自动让数据变成对应的显示宽度。零填充（zerofill参数）会自动导致数值添加无符号（unsigned）属性，保证数据格式。

二、小数型

小数型，带有小数点或者范围超出整型的数值类型。SQL 中将小数型分为浮点型和定点型。浮点型：小数点浮动，精度有限；定点型：小数点固定，精度固定，不会丢失精度。

浮点型（float）

浮点型数据是一种精度型数据，超出指定范围之后会丢失精度（会自动四舍五入），分为float单精度（占4个字节）和double双精度（占用8个字节）。

-- 创建浮点数据表
create table my_float(
f1 float,
f2 float(10,2),
f3 float(6,2)  -- 6位在精度范围之内
)charset utf8;

-- 插入数据
insert into my_float values(1000.10,1000.10,1000.10);
insert into my_float values(1234567890,12345678.90,1234.56);	
insert into my_float values(3e38,3.01e7,1234.56);
insert into my_float values(9999999999,99999999.99,9999.99); 

-- 超出长度插入数据
insert into my_float values(123456,1234.12345678,123.9876543); -- 小数部分超出没有问题，自动四舍五入
insert into my_float values(123456,1234.12,12345.56); -- 整数部分超出

浮点数如果不写精度和标度，则会按照实际的精度值显示；float(M,D)则表示标注了精度和标度，M 代表数据总长度（精度），D 代表小数部分长度（标度），整数部分长度为M-D。浮点型数据的插入，整型部分是不能超出长度的；但是小数部分是可以超出长度的，超出范围的小数的部分系统会自动四舍五入并保留合法的小数部分长度。

定点型（decimal）

绝对保证整数部分不会被四舍五入，不会丢失精度，小数部分有可能会丢失精度。定点型在不指定精度时，默认的整数位为10，默认的小数位为0。定点数的整数部分一定不能超出长度，小数部分的长度可以随意，超出的部分会被系统自动截断。

-- 创建定点数表
create table my_decimal(
f1 float(10,2),
d1 decimal(10,2)
)charset utf8;

1
2
3

-- 插入数据
insert into my_decimal values(12345678.90,12345678.90); -- 有效数据
insert into my_decimal values(1234.123456,1234.1234567); --  小数部分超出没事

1 2	insert into my_decimal values(99999999.99,99999999.99); -- 有效数据 insert into my_decimal values(99999999.99,99999999.999); -- 进位超出范围

三、时间日期类型

datatime：年月日时分秒，YYYY-MM-DD HH:MM:SS，显示宽度固定为19个字符，表示的范围从1000到9999年
date：年月日，datetime中的date部分
time：时分秒，指某个区间之内
timestamp：时间戳，在更新数据的时候会自动更新为当前系统时间
year：年份，在4位格式中，允许的值是1901-2155之间的年份

-- 创建时间日期表
create table my_date(
d1 datetime,
d2 date,
d3 time,
d4 timestamp,
d5 year
)charset utf8;

-- 插入数据
insert into my_date values('2017-05-25 21:28:54','2017-05-25 ','21:28:54','2017-05-25 21:28:54',2017);

-- 时间改为负数
insert into my_date values('2017-05-25 21:28:54','2017-05-25 ','-21:28:54','2017-05-25 21:28:54',2017);
insert into my_date values('2017-05-25 21:28:54','2017-05-25 ','-211:28:54','2017-05-25 21:28:54',2017);
insert into my_date values('2017-05-25 21:28:54','2017-05-25 ','-2 11:28:54','2017-05-25 21:28:54',2017);

-- year可以使用两位或者四位
insert into my_date values('2017-05-25 21:28:54','2017-05-25 ','21:28:54','2017-05-25 21:28:54',69);
insert into my_date values('2017-05-25 21:28:54','2017-05-25 ','21:28:54','2017-05-25 21:28:54',70);

四、字符串类型

在 SQL 中，将字符串类型分成了6类：char,varchar,text,blob,enum和set。

定长字符型（char）

char 磁盘在定义结构的时候，就已经确定了最终的数据的存储长度。char(L)，L代表长度，可以存储的长度，单位为字符，最大长度可以为255。比如在 utf8 环境下，中文字段char(4)需要4*3=12个字节。

变长字符串（varchar）

varchar，变长字符串，在分配空间的时候，按照最大的分配空间，但是实际上用了多少，是根据具体的数据来确定的。varchar(L)，L 表示字符长度，理论长度是65536个字节，会多出1到2个字节来确定存储的实际长度。varchar(10)：确实存了 10 个汉字，utf8环境，10*3+1=31个字节，好处是可以节省存储空间。

如果字符串实际上长度超过255，则既不使用定长也不使用变长，使用文本字符串text。

定长与变长的实际存储空间（UTF8），假设实际存储数据是abcd，在char(4)下占用43个字节，在varchar(4)下占用43+1个字节。假设实际存储数据是a，在char(4)下占用43（长度固定）（长度是可以变化的），varchar(4)下占用13+1个字节。假设实际存储数据是abcde，在char(4)和varchar(4)下是无法存储成功的，因为数据全部超出了长度4。

如何选择定长或者变长字符串？？？

数据基本上长度都一样，如身份证号码、手机号码、电话号码，一般选择定长型字符串，如果选用定长字符串，查找的效率比较高，但是比较浪费磁盘空间。

如果不同数据的长度有变化，比如姓名、地址等的长度就会有两个或者三个，一般选用变长型字符串。变长型字符串则比较节省磁盘空间，但是效率比较低。

文本型字符串（text）

通常说超过255个字符就会使用文本字符串，也就是数据量非常大。文本字符串根据存储的数据格式进行分类：text（存储文字）和blob（二进制数据，通常不用）。

-- text占用10个字节长度
create table my_text(
name varchar(21841) not null, -- 21841 * 3 + 2 = 65525
content text not null         -- 10 
)charset utf8;

枚举类型（enum）

枚举类型（enum）是将所有可能出现的结果都设计好，实际上存储的数据必须是规定好数据中的一个。按照元素出现的顺序，从1开始编号。枚举在进行数据规范的时候，系统会自动建立一个数字会枚举元素的对应关系，然后在进行数据插入的时候，系统自动将字符转换成对应的数字存储，然后在进行数据提取的时候，系统自动将数值转换成对应的字符串显示（这也是效率比较低的原因）。

枚举的使用方式：

1
2
3

# 定义：enum（可能出现的元素列表） 
# 存储数据，只能存储定义好的数据
enum('男','女','不男不女','保密');

-- 创建枚举表
create table my_enum(
gender enum('男','女','保密')
)charset utf8;

1
2
3

-- 加入数据
insert into my_enum values('男'),('保密'); -- 有效数据
insert into my_enum values('male'); -- 无效数据

枚举数据类型的作用之一是规范数据格式，数据只能是规定的数据中的一个，作用之二是节省存储空间（枚举通常有一个别名：单选框），在 MySQL 中系统会自动转换数据格式的

枚举实际存储的是数值，所以在插入时可以直接插入数值。证明字段存储的数据是数值：将数据加上0，如果能够正常返回则证明字段存储的是数值。

集合字符串（set）

集合字符串与枚举十分相似，两者实际存储的都是数值，而不是字符串（集合是多选）
集合中每一个元素都是对应一个二进制位。

1
2
3

# 集合使用方式
# 定义：set(元素列表)
# 元素列表可以是一个或者多个，使用逗号分隔

1
2
3

-- 插入数据
insert into my_set values('篮球,足球,乒乓球');
insert into my_set values(3);

每一个元素都是对应一个二进制位，被选中为1没有则为0，最后反过来；集合中插入数据时与插入的顺序没有关系；集合的强大在于能够规范数据和节省空间。

五、MySQL 记录长度

MySQL 中规定任何一条记录最长不能超过65535个字节，utf8下varchar的实际最多有21844个字符，gbk下最多32766个字符。

MySQL 记录中如果有任何一个字段允许为空，那么系统会自动从整个记录中保留一个字节来存储null，如果想释放null这个字节，必须保证所有的字段都不能为空。所以我们可以通过这种方式来占用utf8或gbk下全部的65535个字符。

列属性

真正约束字段的是数据类型，但是数据类型的约束十分单一，需要有一些额外的约束来更加保证数据的合法性。

空属性

NULL（默认）和NOT NULL（不为空），尽可能在数据库汇总要保证所有的数据不能为空，空数据没有意义并且不能参与运算。

列描述

列描述comment用来给数据库管理员了解列属性，没有实际意义，是专门来描述字段，会根据表创建语句保存

默认值

某一种数据会经常性地出现某个具体的值，可以在一开始就指定好