对于MySQL的数据类型的一个整体认识

在MySQL中 将数据类型分为了三类: 字符、数值以及时间日期

整体认知

字符型

字符型分了6类 分别为 char、varchar、text、blob、enum、set 前三常用 后面的不怎么用到

字符串类型

定长字符串

定长字符串char 二维表在定义结构的时候 就已经确定了最终的数据存储长度

char(L): L表示length 可以存储的长度，单位为字符最大长度值为255
char(3) 在utf8环境下 需要3*3=9个字节

变长字符串

变长字符串：varchar 按照最大的空间分配 但是实际上最终使用了多少，根据具体的数据来确定
varchar(L)：L表示长度 理论长度65536个字符，但是会多出1到2个字节来确定存储的实际长度， 但是实际上如果长度超过255 即不使用定长也不使用变长 使用文本字符串text
varchar(10) 表示存了10个汉字 如果在UTF8环境 10*3 +1 =31（bytes） 存储了3个汉字 3*3+1=10（bytes）
定长和变长的存储时间空间（UTF8）

应该怎么选择定长还是变长字符串呢？

• 定长的磁盘空间比较浪费 但是效率高 如果数据基本上确定长度都一样 就使用定长 比如身份证、电话号码、手机号码
• 变长的磁盘空间比较节省 但是效率低 如果数据不能确定长度（不同数据有变化） 就使用变长 比如姓名 地址

文本字符串

如果数据非常大 通常说超过255个字符就会使用文本字符串
文本字符串根据存储的数据的格式进行分类：text和blob

• text 存储文字（二进制的数据实际上都是存储路径）
• blob 存储二进制(通常不用)

枚举字符串

枚举类型 enum 事先将所有可能出现的结果都设计好 实际上存储的数据必须是规定好的数据中的一个
枚举的使用方式:

• 定义：enum（可能出现的元素列表） 比如： enum('男','女', '保密')
• 使用：存储数据 只能存储上面定义好的数据

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

-- 插入数据 数据只能是规定数据中的其中一个
insert into my_enumtype values('男'),('保密'); -- 符合格式
insert into my_enumtype values('male', 'secret'); -- 错误 不存在

-- 将字段取出 进行+0运算 枚举实际存的是数值 并不是字符串本身
select gender + 0, gender from my_enumtype;

-- 数值插入枚元素 1 表示男  2 表示 女 
insert into my_enumtype values(1),(2);

集合字符串

集合和枚举很类似 我实际存储的的数值 而不是字符串（集合是多选）
集合的使用方式：

• 定义： set(元素列表)
• 使用： 可以使用元素列表中的元素(多个) 使用逗号分割

/*
    集合中每一个元素都是对应一个对应二进制位
*/
-- 创建集合表
create table my_settype(
    hobby set('coding', 'reading', 'lol', 'sleep')
);

-- 插入数据 可以使用多个元素字符串组合 也可以使用数字
insert into my_settype values(3); --  代表lol 3 = 1+2 coding+reading
insert into my_settype values('reading,lol');

数值型

数值型的数据 都是数值
分为了整数型和小数型

整形

• tinyint 迷你整形 （常用）
• smallint 小整形
• mediumint 中整形
• int 标准整形 （常用）
• bigint 大整形

整形

--  创建一张整形表
create table my_inttype(
    i1 tinyint ,
    i2 smallint,
    i3 mediumint,
    i4 int,
    i5 bigint
);

-- 插入数据 只能插入对应的整数
insert into my_inttype values(10, 10, 10, 10, 10); -- 有效数据
-- 这样行不行
insert into my_inttype values('100', '100', '100', '100', '100');
insert into my_inttype values('a', 'qwe', '100', '100', '100'); -- 无效 数据类型不对

insert into my_inttype values(255, 255, 255, 255, 255); -- 不行 超出范围
/*
    sql的数值类型全部都是默认有符号 分正负
    有时候需要使用无符号数据 需要给数据类型限定 int unsigned;  从0开始
*/
-- 添加无符号
alter table my_inttype add i6 tinyint unsigned;

-- 插入数据
insert into my_inttype values(1, 255, 255, 255, 255);  

-- 指定显示宽度为 1  显示宽度不控制大小
alter table my_inttype add i7 tinyint(1) unsigned;

insert into my_inttype values(127, 0, 0, 0, 0, 255, 255);

/* 当数据不够显示宽度的时候 填充 自动是无符号的 */
alter table my_inttype add i8 tinyint(2) zerofill; -- 显示宽度为2 ,0 填充
insert into my_inttype values(6, 6, 6, 6, 6, 6, 6, 6);

小数型

带有小数点的数 MySQL讲小数细分了两种 浮点型和定点型

• 浮点型 --> float、double
  小数点浮动 精度有限 会丢失精度
• 定点型 --> decimal
  小数点固定 精度固定 不会丢失精度
  
  小数型

浮点型

浮点型是一种精度型数据 超出指定范围后 会丢失精度（自动四舍五入）
浮点型: 理论分为两种精度

• float 单精度 占用4个字节存储数据 精度范围大概为7位左右
• double 双精度 占用8个字节存储数据 精度范围大概为15位左右

/*
    浮点数 直接float表示没有小数部分
    float(M, D) M表示总长度 D表示小数部分长度 整数部分为M-D
*/
-- 创建浮点数表
create table my_floattype(
    f1 float,
    f2 float(10, 2), -- 10位精度范围之外
    f3 float(6, 2) -- 6位精度范围之内
);
-- 插入数据
insert into my_floattype values(100.10, 100.10, 100.10); -- 符号条件
insert into my_floattype values(999999999, 99999999.99, 9999.99); --最大值
insert into my_floattype values(123456789, 12345678.90, 1234.56); -- 符和条件
insert into my_floattype values(3e38, 3.01e7, 1234.56); 

-- 整数部分不能超出长度  但是小数部分可以（会自动四舍五入）
insert into my_floattype values(1234.5678, 12345.6789,1234.6789); -- 小数部分超出
insert into my_floattype values(123456, 1234.12,12345.12); -- 整数部分超出  会报错

注意 浮点数 超出精度范围 一定会四舍五入
如果因为系统四舍五入导致进位然后整数部分超出指定的长度，那么系统也允许成立

定点型

定点型 绝对保证整数部分不会被四舍五入（不会丢失精度） 小数部分有可能（理论上也不会丢失精度）

/*
    定点型  
    M 最大是65  D 最大是30  默认是10  以浮点数做为对比
*/
create table my_decimaltype(
    d1 decimal, -- 默认是decimal(10, 0)
    d2 decimal(10, 2),
    f1 float(10, 2)
);
-- 插入数据
insert into my_decimaltype values(1234567.89, 1234567.89, 1234567.89);-- 有效数据
insert into my_decimaltype values(1234.5678, 123.456789, 123.456789); -- 小数部分超出 小数部分四舍五入

insert into my_decimaltype values(9999999999, 99999999.99, 99999999.99); -- 没有问题
insert into my_decimaltype values(9999999999, 99999999.999, 99999999.999); -- 进位导致长度溢出 会警告 
-- 查看警告
show warnings;

时间日期类型

时间日期

-- 创建一张时间日期类型的表
create table my_datetype(
    d1 datetime,
    d2 timestamp,
    d3 date,
    d4 time,
    d5 year
)

-- 插入数据
insert into my_datetype values('2011-11-11 11:11:11','2008-08-08 08:08:08', '2017-12-12', '12:12:12', 2020); -- 标准的

-- 时间可以使用负数
insert into my_datetype values('2011-11-11 11:11:11','2008-08-08 08:08:08', '2017-12-12', '-12:12:12', 2020); 
-- 时间可以使用负数
insert into my_datetype values('2011-11-11 11:11:11','2008-08-08 08:08:08', '2017-12-12', '-112:12:12', 2020); 

-- 时间可以使用负数
insert into my_datetype values('2011-11-11 11:11:11','2008-08-08 08:08:08', '2017-12-12', '-1 12:12:12', 2020);  -- 过去一天  应该是 -36:12:12
 

--  年可以使用两位
insert into my_datetype values('2011-11-11 11:11:11','2008-08-08 08:08:08', '2017-12-12', '-1 12:12:12', 70);  
insert into my_datetype values('2011-11-11 11:11:11','2008-08-08 08:08:08', '2017-12-12', '-1 12:12:12', 69);  

-- 只有当前所在的记录被更新  当前这个字段一定会更新
update my_datetype set  d1 = '2017-12-19 14:53:13' where d5 = 2018;

-- 获取当前时间戳
select unix_timestamp();

以上就是对于MySQL的数据类型的一些基本认识和操作