MySQL数据库表的CRUD操作完全指南：从基础到高效实践

一、核心操作全景图

二、创建操作（Create）

2.1 表结构创建

-- 创建电商用户表
CREATE TABLE `users` (
  `user_id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
  `username` VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
  `email` VARCHAR(100) NOT NULL UNIQUE CHECK(email LIKE '%@%') COMMENT '邮箱',
  `balance` DECIMAL(10,2) DEFAULT 0.00 COMMENT '账户余额',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 创建索引
CREATE INDEX idx_username ON users(username);
CREATE FULLTEXT INDEX idx_email ON users(email);

2.2 数据插入

-- 单条插入
INSERT INTO users (username, email, balance)
VALUES ('john_doe', 'john@example.com', 100.00);

-- 批量插入
INSERT INTO users (username, email) VALUES
('alice', 'alice@example.com'),
('bob', 'bob@example.com'),
('charlie', 'charlie@example.com');

-- 插入冲突处理
INSERT INTO users (username, email)
VALUES ('john_doe', 'new_john@example.com')
ON DUPLICATE KEY UPDATE 
  email = VALUES(email),
  updated_at = NOW();

三、查询操作（Read）

3.1 基础查询

-- 条件查询
SELECT user_id, username, balance 
FROM users
WHERE balance > 50.00
  AND created_at BETWEEN '2023-01-01' AND '2023-12-31';

-- 聚合查询
SELECT 
  COUNT(*) AS total_users,
  AVG(balance) AS avg_balance,
  MAX(created_at) AS last_created
FROM users;

-- 分页查询
SELECT * FROM users
ORDER BY created_at DESC
LIMIT 10 OFFSET 20;

3.2 高级查询

-- 多表JOIN
SELECT 
  o.order_id,
  u.username,
  SUM(oi.quantity * oi.unit_price) AS total
FROM orders o
JOIN users u ON o.user_id = u.user_id
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_id
HAVING total > 1000.00;

-- 窗口函数
SELECT 
  username,
  balance,
  RANK() OVER (ORDER BY balance DESC) AS wealth_rank,
  balance - LAG(balance) OVER (ORDER BY created_at) AS balance_diff
FROM users;

-- JSON查询
SELECT 
  user_id,
  JSON_EXTRACT(profile, '$.address.city') AS city,
  JSON_CONTAINS(profile, '{"hobbies": "reading"}') AS has_reading
FROM users
WHERE JSON_VALUE(profile, '$.age') > 18;

四、更新操作（Update）

4.1 数据修改

-- 条件更新
UPDATE users
SET balance = balance * 1.05
WHERE created_at < '2023-06-01'
  AND balance < 500.00;

-- JOIN更新
UPDATE orders o
JOIN users u ON o.user_id = u.user_id
SET o.status = 'canceled'
WHERE u.username = 'john_doe';

-- JSON更新
UPDATE users
SET profile = JSON_SET(
  profile,
  '$.address.street', '123 Main St',
  '$.phone', '+123456789'
)
WHERE user_id = 1001;

4.2 事务处理

START TRANSACTION;

UPDATE accounts 
SET balance = balance - 500.00
WHERE user_id = 1001;

UPDATE products
SET stock = stock - 1
WHERE product_id = 2005;

INSERT INTO orders (user_id, total_amount)
VALUES (1001, 500.00);

COMMIT;

五、删除操作（Delete）

5.1 数据删除

-- 条件删除
DELETE FROM login_logs
WHERE login_time < '2022-01-01';

-- 级联删除
DELETE u, o, oi
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
LEFT JOIN order_items oi ON o.order_id = oi.order_id
WHERE u.user_id = 1001;

-- 快速清空
TRUNCATE TABLE temp_sessions;

5.2 软删除实现

-- 添加删除标记列
ALTER TABLE users
ADD COLUMN is_deleted TINYINT DEFAULT 0 COMMENT '删除标记';

-- 标记删除
UPDATE users
SET is_deleted = 1
WHERE user_id = 1001;

-- 查询时过滤
SELECT * FROM users
WHERE is_deleted = 0;

六、性能优化实践

6.1 索引策略

-- 慢查询分析
EXPLAIN ANALYZE
SELECT * FROM users
WHERE YEAR(created_at) = 2023;

-- 索引优化
ALTER TABLE users
ADD INDEX idx_created_at (created_at);

-- 强制索引
SELECT * FROM users FORCE INDEX (idx_username)
WHERE username LIKE 'john%';

6.2 批量操作优化

-- 批量插入优化
INSERT INTO audit_logs (event_type, details)
VALUES 
  ('login', '{"ip": "192.168.1.1"}'),
  ('logout', '{"time": "2023-08-01"}'),
  ('purchase', '{"amount": 50}');

-- 分批次删除
DELETE FROM big_table
WHERE id < 1000000
LIMIT 1000;

七、安全最佳实践

7.1 防止SQL注入

# Python示例：使用参数化查询
cursor.execute(
    "SELECT * FROM users WHERE username = %s AND password = %s",
    (username, hashed_password)
)

7.2 权限控制

-- 创建只读用户
CREATE USER 'report_user'@'%' IDENTIFIED BY 'SecurePass123!';
GRANT SELECT ON ecommerce.* TO 'report_user'@'%';

-- 列级权限控制
GRANT UPDATE (email, phone) ON users TO 'support_user'@'localhost';

八、常见问题解决方案

8.1 死锁处理

-- 查看死锁日志
SHOW ENGINE INNODB STATUS;

-- 设置锁超时
SET innodb_lock_wait_timeout = 30;

-- 重试机制示例（伪代码）
attempts = 0
while attempts < 3:
    try:
        execute_transaction()
        break
    except Deadlock:
        rollback()
        sleep(random.uniform(0.1, 0.5))
        attempts += 1

8.2 数据恢复

-- 使用binlog恢复
mysqlbinlog --start-datetime="2023-08-01 09:00:00" \
            --stop-datetime="2023-08-01 10:00:00" \
            binlog.000001 | mysql -u root -p

-- 从备份恢复
system mysqldump -u root -p dbname > backup.sql
mysql -u root -p dbname < backup.sql

九、现代CRUD扩展

9.1 JSON类型操作

-- 创建JSON列
ALTER TABLE products
ADD COLUMN specs JSON;

-- JSON路径查询
SELECT 
  product_id,
  specs->>'$.cpu' AS cpu_model,
  JSON_EXTRACT(specs, '$.memory.size') AS memory_size
FROM products
WHERE specs->>'$.brand' = 'Apple';

9.2 生成列（Generated Columns）

ALTER TABLE orders
ADD COLUMN total_weight DECIMAL(10,2) AS 
  (SELECT SUM(weight * quantity) FROM order_items WHERE order_id = orders.order_id);

---

完整代码示例已托管至GitHub：https://github.com/example/mysql-crud-guide
配套工具包包含：

• 数据库初始化脚本
• 压力测试工具集
• 慢查询分析工具

欢迎在评论区提交您遇到的CRUD难题，我们将挑选典型问题进行深度解析！