hello,大家好,我是千羽
MySQL 是一款广泛使用的开源数据库管理系统,它以高性能、易用性和稳定性而著称。无论是初创公司还是大型企业,都依赖 MySQL 来管理和存储数据。对于初学者来说,了解并掌握 MySQL 的基本建表规约是至关重要的,它不仅能够帮助你建立高效、可靠的数据库结构,还能为你未来的数据管理工作打下坚实的基础。
在开始详细介绍 MySQL 的建表规约之前,我们需要先了解为什么需要遵循这些规约。以下是几个关键原因:
【强制】 表达是与否概念的字段,必须使用 is_xxx
的方式命名,数据类型为 unsigned tinyint
(1 表示是,0 表示否)。
说明:任何字段如果为非负数,必须是 unsigned
。这样可以确保数据的范围更大,减少存储空间。
正例:表达逻辑删除的字段名 is_deleted
,1 表示删除,0 表示未删除。
【强制】 表名、字段名必须使用小写字母或数字,禁止出现数字开头,禁止两个下划线中间只出现数字。
说明:MySQL 在 Windows 下不区分大小写,但在 Linux 下默认是区分大小写。因此,数据库名、表名、字段名,都不允许出现任何大写字母,避免节外生枝。
正例:aliyun_admin
, rdc_config
, level3_name
反例:AliyunAdmin
, rdcConfig
, level_3_name
【强制】 表名不使用复数名词。
说明:表名应该仅仅表示表里面的实体内容,不应该表示实体数量,对应于 DO 类名也是单数形式,符合表达习惯。
【强制】 禁用保留字,如 desc
、range
、match
、delayed
等,请参考 MySQL 官方保留字。
【强制】 主键索引名为 pk_字段名
;唯一索引名为 uk_字段名
;普通索引名则为 idx_字段名
。
说明:pk_
即 primary key;uk_
即 unique key;idx_
即 index 的简称。
【强制】 小数类型为 decimal
,禁止使用 float
和 double
。
说明:float
和 double
在存储的时候,存在精度损失的问题,很可能在值的比较时,得到不正确的结果。如果存储的数据范围超过 decimal
的范围,建议将数据拆成整数和小数分开存储。
【强制】 如果存储的字符串长度几乎相等,使用 char
定长字符串类型。
【强制】 varchar
是可变长字符串,不预先分配存储空间,长度不要超过 5000,如果存储长度大于此值,定义字段类型为 text
,独立出来一张表,用主键来对应,避免影响其它字段索引效率。
【强制】 表必备三字段:id
, gmt_create
, gmt_modified
。
说明:其中 id
必为主键,类型为 unsigned bigint
、单表时自增、步长为 1。gmt_create
, gmt_modified
的类型均为 datetime
类型,前者现在时表示主动创建,后者过去分词表示被动更新。
【推荐】 表的命名最好是加上“业务名称_表的作用”。
正例:alipay_task
/ force_project
/ trade_config
【推荐】 库名与应用名称尽量一致。
【推荐】 如果修改字段含义或对字段表示的状态追加时,需要及时更新字段注释。
【推荐】 字段允许适当冗余,以提高查询性能,但必须考虑数据一致。冗余字段应遵循:
varchar
超长字段,更不能是 text
字段。正例:商品类目名称使用频率高,字段长度短,名称基本一成不变,可在相关联的表中冗余存储类目名称,避免关联查询。
【推荐】 单表行数超过 500 万行或者单表容量超过 2GB,才推荐进行分库分表。
说明:如果预计三年后的数据量根本达不到这个级别,请不要在创建表时就分库分表。
【参考】 合适的字符存储长度,不但节约数据库表空间、节约索引存储,更重要的是提升检索速度。
正例:如下表,其中无符号值可以避免误存负数,且扩大了表示范围。
对象 | 年龄区间 | 类型 | 字节 |
---|---|---|---|
人 | 150岁之内 | unsigned tinyint | 1 |
龟 | 数百岁 | unsigned smallint | 2 |
恐龙化石 | 数千万岁 | unsigned int | 4 |
太阳 | 约50亿年 | unsigned bigint | 8 |
为了更好地理解上述规约,我们以一个实际的数据库设计为例,来解析这些规约如何应用。
假设我们要设计一个电商系统的数据库,其中包含用户表、商品表和订单表。
用户表(user):
CREATE TABLE user (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
password VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL,
is_active TINYINT(1) UNSIGNED NOT NULL DEFAULT 1,
gmt_create DATETIME NOT NULL,
gmt_modified DATETIME NOT NULL
) ENGINE=InnoDB;
商品表(product):
CREATE TABLE product (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
description TEXT,
price DECIMAL(10, 2) NOT NULL,
stock INT UNSIGNED NOT NULL,
gmt_create DATETIME NOT NULL,
gmt_modified DATETIME NOT NULL
) ENGINE=InnoDB;
订单表(order):
CREATE TABLE `order` (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT UNSIGNED NOT NULL,
product_id BIGINT UNSIGNED NOT NULL,
quantity INT UNSIGNED NOT NULL,
total_price DECIMAL(10, 2) NOT NULL,
order_status TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
gmt_create DATETIME NOT NULL,
gmt_modified DATETIME NOT NULL,
FOREIGN KEY (user_id) REFERENCES user(id),
FOREIGN KEY
(product_id) REFERENCES product(id)
) ENGINE=InnoDB;
在上述设计中,我们遵循了多个建表规约:
is_active
和 order_status
,数据类型为 unsigned tinyint
。id
,并且 id
字段类型为 unsigned bigint
,自动增长。decimal
类型,避免了精度损失。参考链接:https://github.com/alibaba/p3c/blob/master/Java%E5%BC%80%E5%8F%91%E6%89%8B%E5%86%8C(%E9%BB%84%E5%B1%B1%E7%89%88).pdf