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

3,525 阅读5分钟

前言

在业务需求研发工作中,根据产品来进行设计表是必须的,但是我们可能对字段类型常常认识模拟两可,知道时间类型使用TIMESTAMP和DATETIME,但是却不知道TIMESTAMP的上限快到了,到时候需要涉及表字段的修改,下面通过梳理一些字段类型的坑,让我们在涉及表中,更清楚知道为什么要用这个字段类型,他的好处是什么。


提示:以下是本篇文章正文内容

一、整型类型有哪些?

signed表示这个值是有符号的,数据库的默认选项。

类型占用空间最小值~最大值(signed)最小值~最大值(unsigned)
TINYINT1-128~1270~255
SMALLINT2-32768~327670~65535
MEDIUMINT3-8388608~83886070~16777215
INT4-2147483648~21474836470~4294967295
BIGINT8-9223372036854775808~92233720368547758070~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 、 DoubleDECIMAL
看看他会出现什么问题,新增一个金额字段,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~655361或2个字节记录字符串长度

字符集

默认字符集设置为UTF-8,但是因着emoji 表情字符。 推荐把 MySQL 的默认字符集设置为 UTF8MB4。

select CAST(0xF09F988E as char charset utf8mb4) as emoji;
😎

业务实战遇到的问题

账户密码存储设计

  1. 密码存储通过函数MD5来进行加密,虽然不可逆,但是他有固定的MD5值,通过暴力破解可以破解简单的密码。
  2. 需要加盐(salt),推荐:动态盐+非固定加密算法(非动态盐存在泄漏的风险)
  3. 推荐格式:saltsalt cryption_algorithm$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 数据类型进行反范式设计,提升存储效率。