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) 变长 精确小数 金融数据
正文到此结束
评论插件初始化中...
Loading...