MySQL数据库约束与关系设计及CRUD操作

数据库约束基础

1. 主键约束

主键是关系型数据库中最核心的约束条件，具有以下特征：

1. 唯一性：保证每行数据的唯一标识
2. 非空性：不允许存储NULL值
3. 不可变性：创建后不建议修改

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL
);

2. 外键约束

外键建立表之间的关联关系时需注意：

• 引用表和被引用表必须使用相同存储引擎
• 外键列和参照列必须具有相似的数据类型
• MySQL中默认启用外键约束检查（可通过SET FOREIGN_KEY_CHECKS控制）

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE
    ON UPDATE RESTRICT
);

3. 唯一约束

唯一约束的灵活应用场景：

• 用户注册邮箱/手机号
• 商品SKU编码
• 身份证号码存储
• 组合字段唯一性（多个字段组合必须唯一）

ALTER TABLE products 
ADD UNIQUE (sku_code);

4. 非空约束

设计规范建议：

1. 主键字段必须NOT NULL
2. 外键字段建议NOT NULL
3. 业务关键字段（如金额、数量等）必须NOT NULL
4. 可为NULL的字段需在应用层做特殊处理

5. 默认值约束

设置原则：

• 优先使用数据库默认值而不是应用层默认值
• 时间类型字段推荐使用CURRENT_TIMESTAMP
• 数值型字段默认值建议设为0
• 字符串字段默认空字符串优于NULL

CREATE TABLE logs (
    log_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    status TINYINT DEFAULT 0
);

6. 检查约束

MySQL 8.0开始完整支持检查约束：

CREATE TABLE employees (
    salary DECIMAL(10,2),
    CONSTRAINT chk_salary CHECK (salary > 0)
);

表关系设计模式

1. 一对一关系

实现方式对比：

方案	优点	缺点
主键同步	查询效率高	扩展性差
外键唯一约束	结构清晰	需要JOIN查询
字段合并	访问效率最高	违反第三范式

2. 一对多关系

设计要点：

• 在多的一方添加外键
• 索引优化：外键字段必须建立索引
• 删除策略选择：
  • CASCADE：级联删除
  • SET NULL：置空处理
  • RESTRICT：禁止删除

3. 多对多关系

中间表设计规范：

CREATE TABLE course_student (
    course_id INT NOT NULL,
    student_id INT NOT NULL,
    enroll_date DATE,
    PRIMARY KEY (course_id, student_id),
    FOREIGN KEY (course_id) REFERENCES courses(id),
    FOREIGN KEY (student_id) REFERENCES students(id)
);

数据操作全解

1. 数据插入

批量插入优化技巧：

INSERT INTO products (name, price)
VALUES 
    ('Mouse', 49.99),
    ('Keyboard', 129.99),
    ('Monitor', 899.99);

2. 数据删除

安全删除模式：

-- 启用安全模式
SET SQL_SAFE_UPDATES = 1;

-- 条件删除示例
DELETE FROM logs
WHERE create_time < '2020-01-01'
LIMIT 1000;

3. 数据更新

原子更新操作：

UPDATE accounts
SET balance = balance - 100.00
WHERE user_id = 123
AND balance >= 100.00;

4. 数据查询进阶

4.1 关联查询

-- 三表连接示例
SELECT 
    o.order_id,
    u.username,
    p.product_name,
    oi.quantity
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.id;

4.2 窗口函数

SELECT 
    employee_id,
    department_id,
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank
FROM employees;

4.3 JSON处理

SELECT 
    user_id,
    JSON_EXTRACT(profile, '$.address.city') AS city,
    JSON_CONTAINS(roles, '"admin"') AS is_admin
FROM users
WHERE JSON_TYPE(profile) = 'OBJECT';

查询优化策略

1. 索引优化原则

• 三星索引原则：
  1. WHERE条件匹配索引最左前缀
  2. ORDER BY子句使用索引排序
  3. 覆盖索引包含所有SELECT字段

2. 执行计划分析

关键指标解读：

• type列：从最优到最差 system > const > eq_ref > ref > range > index > ALL
• Extra列重要值： Using index：覆盖索引 Using filesort：需要优化排序 Using temporary：需要优化临时表

3. 分页优化

大数据量分页方案：

-- 传统分页
SELECT * FROM logs
ORDER BY id
LIMIT 1000000, 20;

-- 优化分页
SELECT * FROM logs
WHERE id > 1000000
ORDER BY id
LIMIT 20;

事务与锁机制

1. 事务控制

隔离级别对比：

级别	脏读	不可重复读	幻读	性能
READ UNCOMMITTED	✓	✓	✓	最高
READ COMMITTED	×	✓	✓	高
REPEATABLE READ	×	×	✓	中
SERIALIZABLE	×	×	×	最低

2. 锁类型

锁的兼容矩阵：

	X	IX	S	IS
X	×	×	×	×
IX	×	✓	×	✓
S	×	×	✓	✓
IS	×	✓	✓	✓

存储引擎对比

InnoDB vs MyISAM

特性对比表：

特性	InnoDB	MyISAM
事务支持	✓	×
外键约束	✓	×
行级锁	✓	×（表锁）
崩溃恢复	支持	有限支持
全文索引(MySQL5.6+)	✓	✓
存储限制	64TB	256TB
内存使用	高	低

数据库设计规范

命名规范建议

1. 表名：小写复数形式，下划线分隔（users、order_items）
2. 字段名：小写单数，下划线分隔（created_at）
3. 主键：id（单表）或 table_id（多表关联）
4. 外键：referenced_table_id
5. 索引：idx_columnname / uniq_columnname

字段类型选择

数值类型选择指南：

类型	存储空间	范围	适用场景
TINYINT	1 byte	-128~127	状态值、布尔值
SMALLINT	2 bytes	-32768~32767	中等范围数值
MEDIUMINT	3 bytes	-8388608~8388607	较大范围数值
INT	4 bytes	±21亿	常用整数类型
BIGINT	8 bytes	±922亿亿	极大数值需求
DECIMAL(M,N)	变长	精确小数	金融数据