0
0

MySQL数据库设计规则

2026-06-03

MySQL数据库设计规则

1、字段命名

2、字段类型

1-10之间的10个数字,每个数字1个字节就能保存,选择tinyint更为合适。

1)、尽可能选择占用存储空间小的字段类型,在满足正常业务需求的情况下,从小到大,往上选。

2)、如果字符串长度固定,或者差别不大,可以选择char类型。如果字符串长度差别较大,可以选择varchar类型。

3)、是否字段,可以选择bit类型。

4)、枚举字段,可以选择tinyint类型。

5)、主键字段,可以选择bigint类型。

6)、金额字段,可以选择decimal类型。

7)、时间字段,可以选择timestamp或datetime类型。

3、字段长度

在mysql中除了varchar和char是代表字符长度之外,其余的类型都是代表字节长度。
假如我们定义的字段类型和长度是:bigint(4),bigint实际长度是8个字节。

4、字段个数

建议每表的字段个数,不要超过20个。(字段太多查询效率低)

5、主键

在创建表时,一定要创建主键。因为主键自带了主键索引,相比于其他索引,主键索引的查询效率最高,因为它不需要回表。此外,主键还是天然的唯一索引,可以根据它来判重。在单个数据库中,主键可以通过AUTO_INCREMENT,设置成自动增长的。但在分布式数据库中,特别是做了分库分表的业务库中,主键最好由外部算法(比如:雪花算法)生成,它能够保证生成的id是全局唯一的。除此之外,主键建议保存跟业务无关的值,减少业务耦合性,方便今后的扩展。不过我也见过,有些一对一的表关系,比如:用户表和用户扩展表,在保存数据时是一对一的关系。这样,用户扩展表的主键,可以直接保存用户表的主键。

6、存储引擎

Innodb

7、NOT NULL

在创建字段时,需要选择该字段是否允许为NULL。我们在定义字段时,应该尽可能明确该字段NOT NULL。为什么呢?我们主要以innodb存储引擎为例,myslam存储引擎没啥好说的。
主要有以下原因:

    在innodb中,需要额外的空间存储null值,需要占用更多的空间。

    null值可能会导致索引失效。

    null值只能用is null或者is not null判断,用=号判断永远返回false。

因此,建议我们在定义字段时,能定义成NOT NULL,就定义成NOT NULL。但如果某个字段直接定义成NOT NULL,万一有些地方忘了给该字段写值,就会insert不了数据。这也算合理的情况。但有一种情况是,系统有新功能上线,新增了字段。上线时一般会先执行sql脚本,再部署代码。由于老码中,不会给新字段赋值,则insert数据时,也会报错。由此,非常有必要给NOT NULL的字段设置默认值,特别是后面新增的字段。

8、索引

在建表时,除了指定主键索引之外,还需要创建一些普通索引。
例如:

create table product_sku(
  id int(10) primary key auto_increment,
  spu_id int(10) not null,
  brand_id int(10) not null,
  name varchar(15) not null
);

在创建商品表时,使用spu_id(商品组表)和brand_id(品牌表)的id。
像这类保存其他表id的情况,可以增加普通索引:

create table product_sku (
  id int(10) primary key auto_increment,
  spu_id int(10) not null,
  brand_id int(10) not null,
  name varchar(15) not null,
  KEY `ix_spu_id` (`spu_id`) USING BTREE,
  KEY `ix_brand_id` (`brand_id`) USING BTREE
);

后面查表的时候,效率更高。但索引字段也不能建的太多,可能会影响保存数据的效率,因为索引需要额外的存储空间。建议单表的索引个数不要超过:5个。如果在建表时,发现索引个数超过5个了,可以删除部分普通索引,改成联合索引。顺便说一句:在创建联合索引的时候,需要使用注意最左匹配原则,不然,建的联合索引效率可能不高。对于数据重复率非常高的字段,比如:状态,不建议单独创建普通索引。因为即使加了索引,如果mysql发现全表扫描效率更高,可能会导致索引失效。

9、时间字段

时间字段的类型,我们可以选择的范围还是比较多的,目前mysql支持:date、datetime、timestamp、varchar等。

varchar类型可能是为了跟接口保持一致,接口中的时间类型是String。

但如果哪天我们要通过时间范围查询数据,效率会非常低,因为这种情况没法走索引。

date类型主要是为了保存日期,比如:2020-08-20,不适合保存日期和时间,比如:2020-08-20 12:12:20。

而datetime和timestamp类型更适合我们保存日期和时间。

但它们有略微区别。

timestamp:用4个字节来保存数据,它的取值范围为1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07。此外,它还跟时区有关。

datetime:用8个字节来保存数据,它的取值范围为1000-01-01 00:00:00 ~ 9999-12-31 23:59:59。它跟时区无关。

优先推荐使用datetime类型保存日期和时间,可以保存的时间范围更大一些。

温馨提醒一下,在给时间字段设置默认值是,建议不要设置成:0000-00-00 00:00:00,不然查询表时可能会因为转换不了,而直接报错。

10、金额字段

mysql中有多个字段可以表示浮点数:float、double、decimal等。

而float和double可能会丢失精度,因此推荐大家使用decimal类型保存金额。

一般我们是这样定义浮点数的:decimal(m,n)。

其中n是指小数的长度,而m是指整数加小数的总长度。

假如我们定义的金额类型是这样的:decimal(10,2),则表示整数长度是8位,并且保留2位小数。

11、唯一索引

唯一索引在我们实际工作中,使用频率相当高。

你可以给单个字段,加唯一索引,比如:组织机构code。

也可以给多个字段,加一个联合的唯一索引,比如:分类编号、单位、规格等。

单个的唯一索引还好,但如果是联合的唯一索引,字段值出现null时,则唯一性约束可能会失效。

创建唯一索引时,相关字段一定不能包含null值,否则唯一性会失效。

12、字符集

mysql中支持的字符集有很多,常用的有:latin1、utf-8、utf8mb4、GBK等。

这4种字符集情况如下:

字符集 长度 说明
GBK 2 支持中文,但是不是国际通用字符集
UTF-8 3 支持中英文混合场景,是国际通用字符集
latin1 1 MySQL默认字符集
utf8mb4 4 完全兼容UTF-8,用四个字节存储更多的字符

latin1容易出现乱码问题,在实际项目中使用比较少。

而GBK支持中文,但不支持国际通用字符,在实际项目中使用也不多。

从目前来看,mysql的字符集使用最多的还是:utf-8和utf8mb4。

其中utf-8占用3个字节,比utf8mb4的4个字节,占用更小的存储空间。

但utf-8有个问题:即无法存储emoji表情,因为emoji表情一般需要4个字节。

由此,使用utf-8字符集,保存emoji表情时,数据库会直接报错。

所以,建议在建表时字符集设置成:utf8mb4,会省去很多不必要的麻烦。

13、排序规则

不知道,你关注过没,在mysql中创建表时,有个COLLATE参数可以设置。

例如:

CREATE TABLE `order` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `code` varchar(20) COLLATE utf8mb4_bin NOT NULL,
  `name` varchar(30) COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `un_code` (`code`),
  KEY `un_code_name` (`code`,`name`) USING BTREE,
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

它是用来设置排序规则的。

字符排序规则跟字符集有关,比如:字符集如果是utf8mb4,则字符排序规则也是以:utf8mb4_开头的,常用的有:utf8mb4_general_ci、utf8mb4_bin等。

其中utf8mb4_general_ci排序规则,对字母的大小写不敏感。说得更直白一点,就是不区分大小写。

而utf8mb4_bin排序规则,对字符大小写敏感,也就是区分大小写。

说实话,这一点还是非常重要的。

假如order表中现在有一条记录,name的值是大写的YOYO,但我们用小写的yoyo去查,例如:

select * from order where name='yoyo';

如果字符排序规则是utf8mb4_general_ci,则可以查出大写的YOYO的那条数据。

如果字符排序规则是utf8mb4_bin,则查不出来。

由此,字符排序规则一定要根据实际的业务场景选择,否则容易出现问题。

14、大字段

我们在创建表时,对一些特殊字段,要额外关注,比如:大字段,即占用较多存储空间的字段。

比如:用户的评论,这就属于一个大字段,但这个字段可长可短。

但一般会对评论的总长度做限制,比如:最多允许输入500个字符。

如果直接定义成text类型,可能会浪费存储空间,所以建议将这类字段定义成varchar类型的存储效率更高。

当然,我还见过更大的字段,即该字段直接保存合同数据。

一个合同可能会占几Mb。

在mysql中保存这种数据,从系统设计的角度来说,本身就不太合理。