mysql-design

MySQL Database Design Specification

Trigger Conditions

Design database table structures
Define field types and lengths
Create indexes
Name tables and fields

Part 1: Naming Conventions

Database Name Naming

Rule	Description
Lowercase letters	`youlai_admin`
Underscore separated	No camelCase
Business prefix	`youlai_`

Table Name Naming

Type	Prefix	Example
System table	`sys_`	`sys_user`, `sys_role`
Business table	`biz_`	`biz_order`, `biz_product`
Association table	`rel_` or main table prefix	`sys_user_role`, `rel_user_dept`
Configuration table	`cfg_`	`cfg_dict`, `cfg_config`
Log table	`log_`	`log_login`, `log_operation`

Field Naming

Type	Specification	Example
Primary key	`id`	`id`
Foreign key	Table name + `_id`	`user_id`, `dept_id`
Name	`xxx_name`	`user_name`, `dept_name`
Status	`status`	`status`
Type	`xxx_type`	`menu_type`, `log_type`
Time	`xxx_time`	`create_time`, `login_time`
Boolean	`is_xxx`	`is_deleted`, `is_enabled`
Quantity	`xxx_count`	`login_count`, `view_count`
Sort	`sort` or `order_num`	`sort`

Index Naming

Type	Prefix	Example
Primary key	`pk_`	`pk_user`
Unique index	`uk_`	`uk_username`
Regular index	`idx_`	`idx_dept_id`
Composite index	`idx_`	`idx_dept_status`
Full-text index	`ft_`	`ft_content`

Part 2: Field Type Specification

Primary key

`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键'

String Types

Scenario	Type	Length
Username	VARCHAR	50
Nickname	VARCHAR	50
Password (encrypted)	VARCHAR	100
Phone number	VARCHAR	20
Email	VARCHAR	100
URL	VARCHAR	500
Title	VARCHAR	200
Overview	VARCHAR	500
Detailed description	TEXT	-
JSON data	JSON	-

Numeric Types

Scenario	Type	Description
Primary key/ID	BIGINT	20 digits
Foreign key	BIGINT	Related primary key type
Quantity/Count	INT	-
Amount (cents)	BIGINT	Avoid floating-point precision issues
Sort number	INT	-
Status	TINYINT	0/1/2
Type	TINYINT	Enum value
Percentage	DECIMAL(5,2)	0.00-100.00

Time Types

Scenario	Type	Description
Creation time	DATETIME	`create_time`
Update time	DATETIME	`update_time`
Date	DATE	`birth_date`
Timestamp	BIGINT	Millisecond-level

Boolean Type

`is_deleted` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否删除(0否 1是)'
`is_enabled` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否启用(0否 1是)'

Part 3: Common Fields

Required fields for every table

`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`create_by` BIGINT UNSIGNED DEFAULT NULL COMMENT '创建人',
`update_by` BIGINT UNSIGNED DEFAULT NULL COMMENT '更新人',
`deleted` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '删除标志(0未删除 1已删除)',
PRIMARY KEY (`id`)

Optional fields

`remark` VARCHAR(500) DEFAULT NULL COMMENT '备注',
`version` INT NOT NULL DEFAULT 0 COMMENT '乐观锁版本号',
`tenant_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '租户ID',

Part 4: Index Specification

Index Principles

Principle	Description
Leftmost prefix	Composite indexes are created in query order
High selectivity	Choose columns with high cardinality
Limit quantity	No more than 5 indexes per table
Composite priority	Composite indexes are better than multiple single-column indexes

Required indexes

-- 主键索引
PRIMARY KEY (`id`)

-- 外键索引
INDEX `idx_dept_id` (`dept_id`)

-- 状态索引（常用于筛选）
INDEX `idx_status` (`status`)

-- 创建时间索引（常用于排序）
INDEX `idx_create_time` (`create_time`)

-- 唯一索引（业务唯一字段）
UNIQUE INDEX `uk_username` (`username`)

Composite index

-- 组合索引：部门 + 状态
INDEX `idx_dept_status` (`dept_id`, `status`)

-- 组合索引：用户 + 创建时间
INDEX `idx_user_time` (`user_id`, `create_time`)

Index Pitfalls

Avoid	Reason
`LIKE '%xxx%'`	Prefix fuzzy matching does not use the index
`OR` Condition	May not use the index; use UNION instead
Function operation	`WHERE YEAR(create_time) = 2024` Does not use the index
Type conversion	Comparing a string with a number causes implicit conversion
`NOT IN`	Use instead `NOT EXISTS` or `LEFT JOIN ... IS NULL`

Part 5: Table Design Standards

User table example

CREATE TABLE `sys_user` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `username` VARCHAR(50) NOT NULL COMMENT '用户名',
  `password` VARCHAR(100) NOT NULL COMMENT '密码',
  `nickname` VARCHAR(50) NOT NULL COMMENT '昵称',
  `mobile` VARCHAR(20) DEFAULT NULL COMMENT '手机号',
  `email` VARCHAR(100) DEFAULT NULL COMMENT '邮箱',
  `gender` TINYINT NOT NULL DEFAULT 0 COMMENT '性别(0未知 1男 2女)',
  `avatar` VARCHAR(500) DEFAULT NULL COMMENT '头像URL',
  `status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态(1正常 0禁用)',
  `dept_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '部门ID',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_by` BIGINT UNSIGNED DEFAULT NULL COMMENT '创建人',
  `update_by` BIGINT UNSIGNED DEFAULT NULL COMMENT '更新人',
  `deleted` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '删除标志(0未删除 1已删除)',
  PRIMARY KEY (`id`),
  UNIQUE INDEX `uk_username` (`username`),
  INDEX `idx_dept_id` (`dept_id`),
  INDEX `idx_status` (`status`),
  INDEX `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';

Association table example

CREATE TABLE `sys_user_role` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
  `role_id` BIGINT UNSIGNED NOT NULL COMMENT '角色ID',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE INDEX `uk_user_role` (`user_id`, `role_id`),
  INDEX `idx_role_id` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户角色关联表';

Part 6: Field Default Values

Field type	Default value	Description
String	`NULL` or `''`	Use NULL if not required
Numeric	`0`	-
Boolean	`0` or `1`	Based on business needs
Time	`CURRENT_TIMESTAMP`	-
Foreign key	`NULL`	Optional association

NULL vs NOT NULL

Scenario	Choice
Required field	`NOT NULL`
Optional field	`NULL`
Foreign key (optional)	`NULL`
Status field	`NOT NULL DEFAULT 1`

Part 7: Character Set and Engine

Character set

DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Setting	Description
`utf8mb4`	Supports 4-byte characters (emoji)
`utf8mb4_unicode_ci`	Case-insensitive collation

Storage engine

ENGINE=InnoDB

Engine	Applicable scenario
InnoDB	Transactions, foreign keys, row locks
MyISAM	Read-only, full-text index (legacy)
Memory	Temporary tables, caching

Part 8: SQL Writing Standards

SELECT

-- ✅ 指定字段
SELECT id, username, nickname FROM sys_user WHERE status = 1;

-- ❌ 避免 SELECT *
SELECT * FROM sys_user;

-- ✅ 分页查询
SELECT id, username FROM sys_user WHERE status = 1 LIMIT 0, 20;

-- ✅ 批量插入
INSERT INTO sys_user (username, nickname) VALUES ('user1', '用户1'), ('user2', '用户2');

UPDATE

-- ✅ 带 WHERE 条件
UPDATE sys_user SET nickname = '新昵称' WHERE id = 1;

-- ✅ 乐观锁
UPDATE sys_user SET nickname = '新昵称', version = version + 1 WHERE id = 1 AND version = 1;

-- ❌ 避免全表更新
UPDATE sys_user SET status = 0;

DELETE

-- ✅ 逻辑删除
UPDATE sys_user SET deleted = 1 WHERE id = 1;

-- ✅ 物理删除（慎用）
DELETE FROM sys_user_role WHERE user_id = 1;

Part 9: Performance Optimization

Query optimization

Optimization point	Description
Avoid `SELECT *`	Query only the fields you need
Avoid `LIKE '%xxx%'`	Use `LIKE 'xxx%'` or full-text index
Avoid large OFFSET	`LIMIT 10000, 20` Use cursor-based pagination instead
Avoid `COUNT(*)`	Use caching or estimation for large tables
Batch operations	Use batch insert/update instead of looping one by one

Table design optimization

Optimization point	Description
Vertical partitioning	Move large fields to an extension table
Horizontal partitioning	Partition large tables according to rules
Appropriate redundancy	Reduce join queries
Archive history	Migrate cold data

Part 10: Design Checklist

Table names use lowercase + underscores
Field names use lowercase + underscores
Primary key uses BIGINT AUTO_INCREMENT
Includes common fields (create_time, update_time, deleted)
Add an index to foreign key fields
Add a unique index to unique business fields
Add an index to status fields
Use the utf8mb4 character set
Use the InnoDB engine
Add COMMENT to every field
Add COMMENT to the table
No more than 5 indexes

About this skill

Trigger Conditions

Part 1: Naming Conventions

Database Name Naming

Table Name Naming

Field Naming

Index Naming

Part 2: Field Type Specification

Primary key

String Types

Numeric Types

Time Types

Boolean Type

Part 3: Common Fields

Required fields for every table

Optional fields

Part 4: Index Specification

Index Principles

Required indexes

Composite index

Index Pitfalls

Part 5: Table Design Standards

User table example

Association table example

Part 6: Field Default Values

NULL vs NOT NULL

Part 7: Character Set and Engine

Character set

Storage engine

Part 8: SQL Writing Standards

SELECT

UPDATE

DELETE

Part 9: Performance Optimization

Query optimization

Table design optimization

Part 10: Design Checklist