系列文章目录
提示:所有文章的目录
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 存储。
- 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),推荐:动态盐+非固定加密算法(非动态盐存在泄漏的风险)
- 推荐格式:$salt c r y p t i o n a l g o r i t h m cryption_algorithm cryptionalgorithmvalue
字符串需要避免的问题
- 字符串类型作为标识列,消耗空间,比数字类型慢
- 随机字符串也会导致语句变慢,因为不是随机字符串不是顺序插入,容易造成页分裂和索引碎片。
字符串类型总结
- 设计时如果没有特殊情况,推荐直接使用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 数据类型进行反范式设计,提升存储效率。