MySQL命令大全：从基础到高级的完整指南

一、数据库基础操作

1.1 数据库创建与管理

-- 创建数据库
CREATE DATABASE shop DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 查看数据库列表
SHOW DATABASES;

-- 选择数据库
USE shop;

-- 修改数据库字符集
ALTER DATABASE shop CHARACTER SET = utf8mb4;

-- 删除数据库
DROP DATABASE IF EXISTS old_shop;

字符集选择建议：

• utf8mb4 支持完整Unicode（包括emoji）
• 排序规则推荐使用utf8mb4_unicode_ci（通用排序）或utf8mb4_bin（二进制精确匹配）

1.2 表结构设计

CREATE TABLE products (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL COMMENT '商品名称',
    price DECIMAL(10,2) UNSIGNED NOT NULL,
    stock INT UNSIGNED DEFAULT 0,
    category_id INT UNSIGNED,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_category (category_id),
    CONSTRAINT fk_category FOREIGN KEY (category_id) REFERENCES categories(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

字段类型选择指南：

• 数值类型：INT(存储大小选择), DECIMAL(精确计算)
• 字符串类型：VARCHAR(变长), CHAR(定长)
• 时间类型：TIMESTAMP(自动时区转换), DATETIME(大范围存储)
• JSON类型（MySQL 5.7+）

二、数据操作语言(DML)

2.1 插入数据

-- 单行插入
INSERT INTO products (name, price, category_id) 
VALUES ('无线鼠标', 89.90, 3);

-- 多行插入
INSERT INTO products (name, price, category_id) VALUES
('机械键盘', 299.00, 3),
('游戏耳机', 199.00, 3),
('4K显示器', 1599.00, 2);

-- INSERT IGNORE（忽略重复错误）
INSERT IGNORE INTO products (id, name) VALUES (1, '测试商品');

-- REPLACE INTO（替换重复记录）
REPLACE INTO products (id, name) VALUES (1, '新商品');

2.2 更新数据

-- 基础更新
UPDATE products SET price = price * 0.9 WHERE category_id = 3;

-- 使用CASE条件更新
UPDATE orders SET status = 
    CASE 
        WHEN payment_status = 1 THEN '已付款'
        WHEN ship_status = 1 THEN '已发货'
        ELSE '待处理'
    END;

-- 联表更新
UPDATE products p
JOIN categories c ON p.category_id = c.id
SET p.stock = p.stock + 100
WHERE c.name = '电脑配件';

2.3 删除数据

-- 条件删除
DELETE FROM products WHERE stock = 0;

-- 清空表（重置AUTO_INCREMENT）
TRUNCATE TABLE temp_logs;

-- 联表删除
DELETE p 
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
WHERE oi.product_id IS NULL;

三、查询优化技巧

3.1 索引使用原则

-- 查看执行计划
EXPLAIN SELECT * FROM products WHERE category_id = 3;

-- 创建组合索引
CREATE INDEX idx_price_stock ON products(price, stock);

-- 强制索引使用
SELECT * FROM products FORCE INDEX (idx_price_stock) 
WHERE price BETWEEN 100 AND 500;

索引设计策略：

1. 最左前缀原则
2. 避免在索引列上使用函数
3. 区分度高的列优先
4. 覆盖索引优化

3.2 复杂查询示例

-- 窗口函数（MySQL 8.0+）
SELECT 
    id, 
    name,
    price,
    RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS price_rank
FROM products;

-- 递归CTE（公用表表达式）
WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, 1 AS level
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, c.parent_id, ct.level + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

-- JSON数据处理
SELECT 
    id,
    JSON_EXTRACT(properties, '$.color') AS color,
    JSON_CONTAINS(properties, '"red"', '$.colors') AS has_red
FROM products
WHERE JSON_TYPE(properties) = 'OBJECT';

四、事务与锁机制

4.1 事务控制

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

-- 提交或回滚
COMMIT;
-- ROLLBACK;

事务隔离级别：

-- 查看当前隔离级别
SELECT @@transaction_isolation;

-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

4.2 锁机制实战

-- 显式加锁
SELECT * FROM products WHERE id = 1 FOR UPDATE;

-- 锁等待超时设置
SET innodb_lock_wait_timeout = 50;

-- 死锁检测
SHOW ENGINE INNODB STATUS;

锁优化建议：

1. 尽量使用索引查询
2. 控制事务大小
3. 访问资源的顺序一致
4. 设置合理的超时时间

五、存储引擎对比

特性	InnoDB	MyISAM
事务支持	✔️	❌
行级锁	✔️	❌（表锁）
外键约束	✔️	❌
崩溃恢复	支持	有限支持
全文索引	✔️（5.6+）	✔️
数据压缩	❌	✔️
适用场景	OLTP	读密集型

迁移存储引擎：

ALTER TABLE my_table ENGINE = InnoDB;

六、备份与恢复

6.1 mysqldump使用

# 全库备份
mysqldump -u root -p --single-transaction --routines --triggers --all-databases > full_backup.sql

# 单表备份
mysqldump -u root -p shop products > products.sql

# 恢复数据
mysql -u root -p shop < backup.sql

6.2 二进制日志管理

-- 查看当前日志状态
SHOW BINARY LOGS;

-- 执行时间点恢复
mysqlbinlog --start-datetime="2023-01-01 00:00:00" binlog.000001 | mysql -u root -p

七、性能调优

7.1 参数优化示例

# my.cnf 配置建议
[mysqld]
innodb_buffer_pool_size = 80% of total RAM
innodb_log_file_size = 512M
max_connections = 500
query_cache_type = 0  # 8.0版本已移除
thread_cache_size = 100

7.2 慢查询分析

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;

-- 查看慢查询
SELECT * FROM mysql.slow_log;

-- 使用pt-query-digest分析
pt-query-digest /var/lib/mysql/mysql-slow.log

八、高可用架构

8.1 主从复制配置

-- 主库配置
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'password';

-- 从库配置
CHANGE MASTER TO
MASTER_HOST='master_host',
MASTER_USER='repl_user',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=107;

START SLAVE;

-- 查看复制状态
SHOW SLAVE STATUS\G

8.2 读写分离方案

-- 使用MySQL Router
mysqlrouter --bootstrap root@localhost:3306 --directory myrouter

-- 应用层分库分表（推荐ShardingSphere）

九、安全加固

9.1 用户权限管理

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

-- 权限回收
REVOKE DELETE ON shop.* FROM 'dev_user'@'%';

-- 查看权限
SHOW GRANTS FOR 'report_user'@'%';

9.2 审计与加密

-- 开启审计日志
INSTALL PLUGIN audit_log SONAME 'audit_log.so';
SET GLOBAL audit_log_policy = ALL;

-- 数据加密
CREATE TABLE sensitive_data (
    id INT PRIMARY KEY,
    credit_card VARBINARY(255)
);

INSERT INTO sensitive_data 
VALUES (1, AES_ENCRYPT('1234-5678-9012-3456', 'encryption_key'));

十、版本新特性

10.1 MySQL 8.0 重要更新

-- 窗口函数
SELECT 
    name, 
    price,
    AVG(price) OVER (PARTITION BY category_id) AS avg_price
FROM products;

-- 通用表表达式（CTE）
WITH discounted_products AS (
    SELECT id, price * 0.8 AS discount_price
    FROM products
)
SELECT * FROM discounted_products WHERE discount_price > 100;

-- 原子DDL操作
ALTER TABLE products 
    ADD COLUMN promo_price DECIMAL(10,2),
    ADD INDEX idx_promo (promo_price);

10.2 升级注意事项

1. 验证字符集兼容性
2. 检查已弃用功能的替代方案
3. 测试存储过程和函数
4. 备份原有配置文件
5. 使用mysql_upgrade工具