前言
在业务需求研发工作中，根据产品来进行设计表是必须的，但是我们可能对字段类型常常认识模拟两可，知道时间类型使用TIMESTAMP和DATETIME，但是却不知道TIMESTAMP的上限快到了，到时候需要涉及表字段的修改，下面通过梳理一些字段类型的坑，让我们在涉及表中，更清楚知道为什么要用这个字段类型，他的好处是什么。
提示：以下是本篇文章正文内容
一、整型类型有哪些？
signed表示这个值是有符号的，数据库的默认选项。
| 类型 | 占用空间 | 最小值~最大值（signed） | 最小值~最大值（unsigned） |
|---|---|---|---|
| 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~18446744073709551615 |
可以看出来signed和unsigned的区别，需要注意signed范围超出。
例： 新建一张表
create table new_schema.sale
(
sale_date date not null
primary key,
sale_count int unsigned null
);
-- init
INSERT INTO `sale` (`sale_date`, `sale_count`) VALUES ('2021-06-15', 2000);
INSERT INTO `sale` (`sale_date`, `sale_count`) VALUES ('2021-07-15', 3000);
INSERT INTO `sale` (`sale_date`, `sale_count`) VALUES ('2021-08-15', 4000);
INSERT INTO `sale` (`sale_date`, `sale_count`) VALUES ('2021-09-15', 5000);
INSERT INTO `sale` (`sale_date`, `sale_count`) VALUES ('2021-10-15', 6000);
INSERT INTO `sale` (`sale_date`, `sale_count`) VALUES ('2021-01-15', 7000);
INSERT INTO `sale` (`sale_date`, `sale_count`) VALUES ('2021-02-15', 1999);
一切都很正常，我们插入一条负数
-- 插入一条负数
insert into sale(sale_date, sale_count) values('2021-03-15' ,-1000);
==[22001][1264] Data truncation: Out of range value for column 'sale_count' at row 1==
显示超出的范围，在业务开发中，是很危险的。
实数类型
| 浮点类型 | 高精度类型 |
|---|---|
| Float 、 Double | DECIMAL |
| 看看他会出现什么问题，新增一个金额字段，6位整数，2位小数 |
alter table sale
add money DECIMAL(6,2) null;
UPDATE `sale` t SET t.`money` = 1234567.99 WHERE t.`sale_date` = '2021-01-15';
==[22001][1264] Data truncation: Out of range value for column 'money' at row 1==
然而，我在这里故意多加了一位数，造成了报错，超出了范围。 所以，DECIMAL适合在明确位数的情况下使用，一般场合我们更多考虑INT整型。
业务场景：整型自增设计
表设计时加上auto_increment实现自增
create table new_schema.t
(
id int auto_increment
primary key,
a int null,
b int null
);
-- 插入最大值
INSERT INTO t(id) VALUES (2147483647);
-- 自增
INSERT INTO t(id) VALUES (null);
自增报错：
==[23000][1062] Duplicate entry '2147483647' for key 'PRIMARY'== 解决方案：使用BIGINT
业务实战遇到的问题
资金字段设计
在海量互联网业务的设计标准中，并不推荐用 DECIMAL 类型，而是更推荐将 DECIMAL 转化为 整型类型。也就是说，资金类型更推荐使用用分单位存储，而不是用元单位存储。如1元在数据库中用整型类型 100 存储。
- DECIMAL字段长度设计不够，需要改造。
- 类型 DECIMAL 是通过二进制实现的一种编码方式，计算效率远不如整型来的高效。
- 需要额外的空间和计算开销，数据量比较大的时候，考虑BIGINT来代替
整数类型总结
- 注意数据库设计signed范围超过问题。
- 设计自增主键为INT类型，要注意范围超过问题，使用BITINT避免到达上限值再次插入报错。
- 设计金额字段，考虑整型，单位为分，这样性能更好，内存更紧凑。
二、字符串类型有哪些？
CHAR(N)、VARCHAR(N)、BINARY、BLOB、TEXT、ENUM、SET
==N代表字节==
| 类型 | 字节数 | 额外字节数 |
|---|---|---|
| CHAR(定长) | 0~255 | - |
| VARCHAR(可伸缩) | 0~65536 | 1或2个字节记录字符串长度 |
字符集
默认字符集设置为UTF-8,但是因着emoji 表情字符。 推荐把 MySQL 的默认字符集设置为 UTF8MB4。
select CAST(0xF09F988E as char charset utf8mb4) as emoji;
😎
业务实战遇到的问题
账户密码存储设计
- 密码存储通过函数MD5来进行加密，虽然不可逆，但是他有固定的MD5值，通过暴力破解可以破解简单的密码。
- 需要加盐（salt），推荐：动态盐+非固定加密算法（非动态盐存在泄漏的风险）
- 推荐格式：cryption_algorithm$value
字符串需要避免的问题
- 字符串类型作为标识列，消耗空间，比数字类型慢
- 随机字符串也会导致语句变慢，因为不是随机字符串不是顺序插入，容易造成页分裂和索引碎片。
字符串类型总结
- 设计时如果没有特殊情况，推荐直接使用VARCHAR，存储类似MD5这样大的定长比较适合CHAR
- 设计时默认字符集设置为UTF8MB4
- 修改表中已有列的字符集，使用命令 ALTER TABLE ... CONVERT TO ....；
- 密码存储设计，要注意泄漏的风险，采用动态盐+动态算法+字符串的形式进行存储。
三、你了解非结构存储类型吗？
非结构存储类型指的是：JSON（JavaScript Object Notation），主要用于互联网应用服务之间的数据交换。MySQL 支持RFC 7159定义的 JSON 规范，主要有JSON 对象和JSON 数组两种类型。
JSON对象
{
"JSON对象":{
"A": a,
"B": b
}
}
JSON数组
[
{
"A": "a",
"B": "b"
},
{
"A1": "a1",
"B1": "b1"
}
]
版本差异
相对于5.7版本，8.0版本做了JSON的日志性能瓶颈优化
JSON处理函数
网上已经有很多有优秀的函数整理 处理函数Link
->> 表达式 代替
JSON_UNQUOTE(JSON_EXTRACT(loginInfo,"$.cellphone")) cellphone,
select
userId,
loginInfo->>"$.cellphone" as cellphone,
loginInfo->>"$.wxchat" as cellphone
from UserLogin a ;
如何创建虚拟索引？
第一步：创建一个虚拟列
alter table UserLogin add column cellphone varchar(255) as (loginInfo->>"$.cellphone");
打开表结构，可以看到一个解析JSON的列
cellphone varchar(255) as (json_unquote(json_extract(`loginInfo`, '$.cellphone')))
第二步：加上索引
alter table UserLogin add unique index idx_cellphone(cellphone);
JSON总结
优点：灵活无序定义。 缺点：灵活过度 有的时候我们可以通过 JSON 数据类型进行反范式设计，提升存储效率。
