MySQL Database Design and SQL Writing Standards

1. Background and Purpose

The guideline explains why MySQL requires specific standards to leverage its strengths and mitigate weaknesses, aiming to help developers, QA, and operations create stable, healthy online services.

2. Design Specifications

2.1 Database Design

Database names must be ≤32 characters, follow the pattern system_subsystem , and use a uniform prefix for related tables. Character set must be explicitly set to utf8 or utf8mb4 when creating a database:

create database db1 default character set utf8;

2.1.2 Table Structure

Table and column names must be ≤32 characters, lowercase, and use only letters, numbers, and underscores. All tables must use the InnoDB engine, have comments, and include NOT NULL with appropriate DEFAULT values. Primary keys are mandatory and should be id int/bigint auto_increment . Intermediate tables start with tmp_ , backup tables with bak_ . Large tables (>1 000 000 rows) require DBA review before alter table operations.

2.1.3 Column Data Type Optimization

Use bigint for auto‑increment columns, tinyint / smallint for low‑cardinality status fields, store IPs as int , avoid enum / set , prefer varchar over text , and store timestamps as timestamp or Unix epoch int . Money fields should be stored as int (cents) to save space.

2.1.4 Index Design

InnoDB tables must have a primary key id int/bigint auto_increment . Index names use prefixes: pk_ , uk_ / uq_ , idx_ . Use BTREE for InnoDB/MyISAM, limit index length to 64 KB, keep ≤7 indexes per table, and avoid redundant indexes.

2.1.5 Partitioning

Partition keys must be indexed. Limit partitions per table to 1024, total partition files to 2 GB, and total size to 50 GB. Use time‑based or modulo partitioning for large tables.

2.1.6 Character Set

All databases, tables, and columns must use the same charset, either utf8 or utf8mb4 , and front‑end applications must match this setting.

2.1.7 DAO Layer Recommendations

Avoid ORM models; write manual SQL with bound variables. Ensure connection timeout and retry logic, configure pool sizes, and consider master‑slave latency when reading.

2.1.8 Example CREATE TABLE

A well‑structured table definition:

CREATE TABLE user (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(11) NOT NULL COMMENT '用户id',
  `username` varchar(45) NOT NULL COMMENT '真实姓名',
  `email` varchar(30) NOT NULL COMMENT '用户邮箱',
  `nickname` varchar(45) NOT NULL COMMENT '昵称',
  `avatar` int(11) NOT NULL COMMENT '头像',
  `birthday` date NOT NULL COMMENT '生日',
  `sex` tinyint(4) DEFAULT '0' COMMENT '性别',
  `short_introduce` varchar(150) DEFAULT NULL COMMENT '一句话介绍自己，最多50个汉字',
  `user_resume` varchar(300) NOT NULL COMMENT '用户提交的简历存放地址',
  `user_register_ip` int NOT NULL COMMENT '用户注册时的源ip',
  `create_time` timestamp NOT NULL COMMENT '用户记录创建的时间',
  `update_time` timestamp NOT NULL COMMENT '用户资料修改的时间',
  `user_review_status` tinyint NOT NULL COMMENT '用户资料审核状态',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user_id` (`user_id`),
  KEY `idx_username`(`username`),
  KEY `idx_create_time`(`create_time`,`user_review_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='网站用户基本信息';

3. SQL Writing Guidelines

3.1 DML Statements

Always specify column lists in SELECT and INSERT . Use batch inserts with ≤5000 rows, prefer UNION ALL over UNION , limit IN lists to 500 values, and avoid hints in production.

3.2 Multi‑Table Joins

Cross‑database joins are prohibited. Update statements must not contain joins. Limit joins to three tables in production and use aliases.

3.3 Transactions

Keep transaction size small (≤2000 rows, ≤5 statements), use repeatable‑read isolation, and base updates on primary or unique keys to avoid gap locks. Separate external calls from transactions.

3.4 Ordering and Grouping

Minimize use of ORDER BY , GROUP BY , and DISTINCT ; when used, ensure the result set is ≤1000 rows and leverage indexes.

3.5 Prohibited SQL in Production

High‑risk statements such as UPDATE ... LIMIT , correlated subqueries, procedures, functions, triggers, views, events, foreign keys, INSERT ... ON DUPLICATE KEY UPDATE , and multi‑table updates are disallowed.