MySQL数据库约束与关系设计及CRUD操作
数据库约束基础
1. 主键约束
主键是关系型数据库中最核心的约束条件,具有以下特征:
- 唯一性:保证每行数据的唯一标识
- 非空性:不允许存储NULL值
- 不可变性:创建后不建议修改
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. 非空约束
设计规范建议:
- 主键字段必须NOT NULL
- 外键字段建议NOT NULL
- 业务关键字段(如金额、数量等)必须NOT NULL
- 可为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. 索引优化原则
- 三星索引原则:
- WHERE条件匹配索引最左前缀
- ORDER BY子句使用索引排序
- 覆盖索引包含所有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 |
内存使用 | 高 | 低 |
数据库设计规范
命名规范建议
- 表名:小写复数形式,下划线分隔(users、order_items)
- 字段名:小写单数,下划线分隔(created_at)
- 主键:id(单表)或 table_id(多表关联)
- 外键:referenced_table_id
- 索引: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) | 变长 | 精确小数 | 金融数据 |
正文到此结束
相关文章
热门推荐
评论插件初始化中...