MySQL笔记 | 4.MySQL数据库设计-字段类型

系列文章目录

提示：所有文章的目录
1.了解SQL的执行过程
2.Docker下搭建MySQL&查看BinLog文件
3.MySQL中涉及的锁

---

前言

在业务需求研发工作中，根据产品来进行设计表是必须的，但是我们可能对字段类型常常认识模拟两可，知道时间类型使用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 存储。

1. DECIMAL字段长度设计不够，需要改造。
2. 类型 DECIMAL 是通过二进制实现的一种编码方式，计算效率远不如整型来的高效。
3. 需要额外的空间和计算开销，数据量比较大的时候，考虑BIGINT来代替

整数类型总结

1. 注意数据库设计signed范围超过问题。
2. 设计自增主键为INT类型，要注意范围超过问题，使用BITINT避免到达上限值再次插入报错。
3. 设计金额字段，考虑整型，单位为分，这样性能更好，内存更紧凑。

二、字符串类型有哪些？

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;
😎

业务实战遇到的问题

账户密码存储设计

1. 密码存储通过函数MD5来进行加密，虽然不可逆，但是他有固定的MD5值，通过暴力破解可以破解简单的密码。
2. 需要加盐（salt），推荐：动态盐+非固定加密算法（非动态盐存在泄漏的风险）
3. 推荐格式：$salt $cryptio{n}_{a}lgorithm$value

字符串需要避免的问题

1. 字符串类型作为标识列，消耗空间，比数字类型慢
2. 随机字符串也会导致语句变慢，因为不是随机字符串不是顺序插入，容易造成页分裂和索引碎片。

字符串类型总结

1. 设计时如果没有特殊情况，推荐直接使用VARCHAR，存储类似MD5这样大的定长比较适合CHAR
2. 设计时默认字符集设置为UTF8MB4
3. 修改表中已有列的字符集，使用命令 ALTER TABLE … CONVERT TO …；
4. 密码存储设计，要注意泄漏的风险，采用动态盐+动态算法+字符串的形式进行存储。

三、你了解非结构存储类型吗？

非结构存储类型指的是：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 数据类型进行反范式设计，提升存储效率。