MySQL 基础完全指南

官方文档：https://dev.mysql.com/doc/refman/8.0/en/
最后更新：2026-03-05

一、基础概念

数据库对象层级

MySQL Server
└── Database（数据库）
    └── Table（表）
        ├── Column（列/字段）
        └── Row（行/记录）

SQL 语句分类

常用数据类型

二、数据库与表操作（DDL）

数据库操作

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

-- 查看所有数据库
SHOW DATABASES;

-- 选择数据库
USE shop;

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

CREATE TABLE

CREATE TABLE users (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username    VARCHAR(50)  NOT NULL UNIQUE,
    email       VARCHAR(100) NOT NULL,
    age         TINYINT UNSIGNED DEFAULT 0,
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

常用约束

ALTER TABLE

-- 添加列
ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;

-- 修改列类型
ALTER TABLE users MODIFY COLUMN age SMALLINT UNSIGNED DEFAULT 0;

-- 重命名列（MySQL 8.0+）
ALTER TABLE users RENAME COLUMN age TO user_age;

-- 删除列
ALTER TABLE users DROP COLUMN phone;

-- 添加索引
ALTER TABLE users ADD INDEX idx_username (username);

-- 删除索引
ALTER TABLE users DROP INDEX idx_username;

三、数据操作（DML）

INSERT

-- 插入单行
INSERT INTO users (username, email, age) VALUES ('alice', '[email protected]', 25);

-- 插入多行
INSERT INTO users (username, email, age) VALUES
    ('bob',   '[email protected]',   30),
    ('carol', '[email protected]', 28);

-- 插入或更新（主键/唯一键冲突时更新）
INSERT INTO users (username, email) VALUES ('alice', '[email protected]')
ON DUPLICATE KEY UPDATE email = VALUES(email);

-- 从查询结果插入
INSERT INTO archive_users SELECT * FROM users WHERE created_at < '2024-01-01';

UPDATE

-- 基础更新
UPDATE users SET age = 26, email = '[email protected]' WHERE username = 'alice';

-- 多表联合更新
UPDATE users u
JOIN orders o ON u.id = o.user_id
SET u.age = u.age + 1
WHERE o.total > 1000;

-- LIMIT 限制更新行数（防误操作）
UPDATE users SET age = 0 WHERE age IS NULL LIMIT 100;

DELETE

-- 条件删除
DELETE FROM users WHERE username = 'alice';

-- 删除全表数据（保留表结构，可回滚）
DELETE FROM users;

-- TRUNCATE：清空表，不可回滚，重置AUTO_INCREMENT
TRUNCATE TABLE users;

-- 多表联合删除
DELETE u FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 'cancelled';

四、查询操作（DQL）

SELECT 基础

-- 基础查询
SELECT id, username, email FROM users;

-- 全列查询（生产环境避免用 *）
SELECT * FROM users;

-- 别名
SELECT username AS name, email AS mail FROM users;

-- 去重
SELECT DISTINCT age FROM users;

-- 条件过滤
SELECT * FROM users WHERE age > 18 AND email LIKE '%@example.com';

-- 排序
SELECT * FROM users ORDER BY age DESC, username ASC;

-- 分页
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;  -- 第3页，每页10条
SELECT * FROM users ORDER BY id LIMIT 20, 10;         -- 等价写法

WHERE 条件运算符

聚合函数

-- GROUP BY 分组
SELECT age, COUNT(*) AS cnt, AVG(id) AS avg_id
FROM users
GROUP BY age
HAVING cnt > 2          -- HAVING 过滤分组结果（WHERE 在分组前过滤）
ORDER BY cnt DESC;

常用字符串函数

常用日期函数

SELECT DATE_FORMAT(created_at, '%Y-%m-%d') AS date,
       COUNT(*) AS cnt
FROM users
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY date;

五、JOIN 连接

连接类型对比

表A    表B
1      2
2      3
3      4

INNER JOIN：  2, 3         （交集）
LEFT JOIN：   1, 2, 3      （A全部 + B匹配）
RIGHT JOIN：  2, 3, 4      （B全部 + A匹配）
CROSS JOIN：  A×B 笛卡尔积

-- INNER JOIN：只保留两边都有匹配的行
SELECT u.username, o.id AS order_id, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN：保留左表所有行，右表无匹配则 NULL
SELECT u.username, o.id AS order_id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- 查找左表中没有关联右表的行（常用于找孤立数据）
SELECT u.username
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

-- 多表 JOIN
SELECT u.username, o.id, p.name AS product
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

-- 自连接（同一张表）
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

六、子查询

-- WHERE 子查询
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

-- 标量子查询（返回单值）
SELECT username,
       (SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS order_cnt
FROM users u;

-- EXISTS 子查询（存在性检查，通常比 IN 更高效）
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.total > 500
);

-- FROM 子查询（派生表，必须起别名）
SELECT age_group, COUNT(*) AS cnt
FROM (
    SELECT CASE
        WHEN age < 18 THEN '未成年'
        WHEN age < 30 THEN '青年'
        ELSE '中年及以上'
    END AS age_group
    FROM users
) AS age_data
GROUP BY age_group;

七、索引

索引类型

-- 创建索引
CREATE INDEX idx_email ON users(email);
CREATE UNIQUE INDEX idx_username ON users(username);
CREATE INDEX idx_name_age ON users(username, age);   -- 联合索引

-- 删除索引
DROP INDEX idx_email ON users;

-- 查看表的索引
SHOW INDEX FROM users;

-- 分析查询是否使用索引
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

EXPLAIN 输出关键字段

联合索引最左前缀原则

-- 联合索引 (a, b, c)
-- 有效使用：a | a,b | a,b,c | a,c（部分使用）
-- 失效：b | c | b,c（没有 a）

CREATE INDEX idx_abc ON t(a, b, c);

SELECT * FROM t WHERE a = 1;              -- 使用索引
SELECT * FROM t WHERE a = 1 AND b = 2;   -- 使用索引
SELECT * FROM t WHERE b = 2;             -- 不使用索引

八、事务

事务四大特性（ACID）

事务隔离级别

-- 查看/设置隔离级别
SELECT @@transaction_isolation;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 手动事务
START TRANSACTION;              -- 或 BEGIN

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

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

-- 保存点
START TRANSACTION;
INSERT INTO users (username) VALUES ('test1');
SAVEPOINT sp1;
INSERT INTO users (username) VALUES ('test2');
ROLLBACK TO sp1;   -- 回滚到保存点，test1保留，test2回滚
COMMIT;

九、视图

-- 创建视图
CREATE VIEW v_user_orders AS
SELECT u.id, u.username, COUNT(o.id) AS order_cnt, SUM(o.total) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

-- 查询视图（与普通表一样）
SELECT * FROM v_user_orders WHERE order_cnt > 5;

-- 更新视图定义
CREATE OR REPLACE VIEW v_user_orders AS
SELECT u.id, u.username FROM users u;

-- 删除视图
DROP VIEW IF EXISTS v_user_orders;

注意：视图不存储数据，每次查询都会执行底层 SELECT。包含 GROUP BY、DISTINCT、聚合函数、UNION 的视图通常不可更新。

十、Python 集成

pymysql

import pymysql
from pymysql.cursors import DictCursor

# 参数说明
connection = pymysql.connect(
    host='127.0.0.1',      # str，数据库主机
    port=3306,             # int，端口，默认3306
    user='root',           # str，用户名
    password='secret',     # str，密码
    database='shop',       # str，数据库名
    charset='utf8mb4',     # str，字符集，默认'utf8mb4'
    cursorclass=DictCursor,# cursor类型，DictCursor返回字典，默认Cursor返回元组
    autocommit=False,      # bool，自动提交，默认False
    connect_timeout=10,    # int，连接超时秒数，默认10
    read_timeout=30,       # int，读取超时秒数
    write_timeout=30,      # int，写入超时秒数
)

pymysql.connect() 参数表

# 基础 CRUD 示例
import pymysql
from pymysql.cursors import DictCursor
from contextlib import contextmanager

@contextmanager
def get_connection():
    conn = pymysql.connect(
        host='127.0.0.1',
        user='root',
        password='secret',
        database='shop',
        charset='utf8mb4',
        cursorclass=DictCursor,
    )
    try:
        yield conn
    finally:
        conn.close()

# 查询
with get_connection() as conn:
    with conn.cursor() as cur:
        cur.execute('SELECT * FROM users WHERE age > %s', (18,))
        rows = cur.fetchall()          # 返回列表[dict]
        row  = cur.fetchone()          # 返回单行 dict 或 None
        rows = cur.fetchmany(size=10)  # 返回指定数量

# 插入
with get_connection() as conn:
    with conn.cursor() as cur:
        sql = 'INSERT INTO users (username, email) VALUES (%s, %s)'
        cur.execute(sql, ('alice', '[email protected]'))
        conn.commit()
        new_id = cur.lastrowid         # 获取自增主键

# 批量插入（executemany）
with get_connection() as conn:
    with conn.cursor() as cur:
        sql = 'INSERT INTO users (username, email) VALUES (%s, %s)'
        data = [('bob', '[email protected]'), ('carol', '[email protected]')]
        cur.executemany(sql, data)
        conn.commit()

# 事务
with get_connection() as conn:
    try:
        with conn.cursor() as cur:
            cur.execute('UPDATE accounts SET balance = balance - %s WHERE id = %s', (100, 1))
            cur.execute('UPDATE accounts SET balance = balance + %s WHERE id = %s', (100, 2))
        conn.commit()
    except Exception:
        conn.rollback()
        raise

游标方法参数表

连接池（DBUtils）

from dbutils.pooled_db import PooledDB
import pymysql

# PooledDB 参数表
pool = PooledDB(
    creator=pymysql,           # 数据库驱动
    maxconnections=20,         # 最大连接数，0/None 不限制
    mincached=5,               # 启动时最小空闲连接数
    maxcached=10,              # 最大空闲连接数，0/None 不限制
    maxshared=0,               # 最大共享连接数，0=不共享
    blocking=True,             # 超出最大连接时是否阻塞等待
    maxusage=None,             # 单连接最多复用次数
    setsession=[],             # 建立连接时执行的SQL列表
    # pymysql 连接参数
    host='127.0.0.1',
    user='root',
    password='secret',
    database='shop',
    charset='utf8mb4',
)

# 使用连接池
conn = pool.connection()
try:
    with conn.cursor() as cur:
        cur.execute('SELECT 1')
finally:
    conn.close()   # 归还给连接池，不是真正关闭

PooledDB 参数表

十一、最佳实践

1. 永远使用参数化查询防止 SQL 注入

# 错误：字符串拼接
sql = f"SELECT * FROM users WHERE username = '{username}'"  # 危险！

# 正确：参数化
cur.execute('SELECT * FROM users WHERE username = %s', (username,))

2. 合理设计索引

-- 高频查询字段加索引
-- 区分度低的字段（如 gender）单独索引意义不大
-- 联合索引按查询频率和选择性排序（高选择性字段放前面）
-- 不要对频繁更新的字段建过多索引（影响写性能）

3. 分页大偏移量优化

-- 问题：LIMIT 1000000, 10 需要扫描100万行
SELECT * FROM users ORDER BY id LIMIT 1000000, 10;  -- 慢

-- 优化：游标分页（记录上次最大ID）
SELECT * FROM users WHERE id > 1000000 ORDER BY id LIMIT 10;  -- 快

4. 避免 SELECT *

-- 明确列出需要的字段，减少数据传输，避免覆盖索引失效
SELECT id, username FROM users;

5. 批量操作代替循环单条

# 慢：循环单条插入
for item in data:
    cur.execute('INSERT INTO t (a) VALUES (%s)', (item,))

# 快：批量插入
cur.executemany('INSERT INTO t (a) VALUES (%s)', [(item,) for item in data])
conn.commit()

6. 事务控制与连接管理

# 使用上下文管理器确保连接关闭
# 显式控制事务边界
# 捕获异常后务必回滚
try:
    cur.execute(...)
    conn.commit()
except Exception:
    conn.rollback()
    raise

7. 字符集统一设置

-- 建库/建表/连接全部用 utf8mb4（支持 emoji 和完整 Unicode）
-- 不要混用 utf8 和 utf8mb4
CREATE DATABASE db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

十二、常见陷阱与注意事项

1. NULL 的比较

-- 错误：NULL 不能用 = 比较
SELECT * FROM users WHERE phone = NULL;   -- 永远返回空

-- 正确
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

-- NULL 参与运算结果也是 NULL
SELECT NULL + 1;    -- NULL
SELECT NULL = NULL; -- NULL（不是 TRUE）

2. TRUNCATE vs DELETE

TRUNCATE TABLE users;  -- 不可回滚，重置AUTO_INCREMENT，无WHERE条件
DELETE FROM users;     -- 可回滚，不重置AUTO_INCREMENT，可加WHERE

3. utf8 vs utf8mb4

MySQL 的 utf8 最多只支持 3 字节，无法存储 emoji 和部分汉字（需要 4 字节）。应始终使用 utf8mb4。

4. DATETIME vs TIMESTAMP

5. IN 大列表性能

-- IN 子查询如果内表很大，可能比 JOIN 慢
-- 超过1000个值的 IN 列表应考虑临时表或 JOIN
SELECT * FROM users WHERE id IN (SELECT user_id FROM big_table);

-- 改写为 JOIN
SELECT u.* FROM users u JOIN big_table bt ON u.id = bt.user_id;

6. 隐式类型转换导致索引失效

-- 字段是 VARCHAR，传入 INT，导致全表扫描
SELECT * FROM users WHERE username = 123;  -- 应传字符串 '123'

-- 函数操作导致索引失效
SELECT * FROM users WHERE YEAR(created_at) = 2024;  -- 索引失效
-- 改写为范围查询
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

7. 游标忘记关闭

# 始终使用 with 语句管理游标
with conn.cursor() as cur:
    cur.execute('SELECT 1')
# with 结束自动关闭游标

8. executemany 与事务

# executemany 本身不提交，需手动 commit
cur.executemany(sql, data)
conn.commit()   # 不要忘记！

9. 连接超时与重连

# 长时间不操作后连接可能断开（MySQL 默认 wait_timeout=8小时）
# 使用连接池时应配置 ping 检测或使用 reconnect 机制

import pymysql

conn = pymysql.connect(...)
conn.ping(reconnect=True)   # 检测并自动重连

10. EXPLAIN 分析慢查询

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;   -- 超过1秒记入慢日志

-- 分析执行计划
EXPLAIN SELECT * FROM users WHERE email LIKE '%@gmail.com';
-- type=ALL 说明全表扫描，需优化

最佳实践

EXPLAIN 先于优化：每次优化查询前用 EXPLAIN 确认执行计划，不凭直觉加索引，重点关注 type（ALL 为全表扫描）、key（实际使用的索引）、rows（估算扫描行数）。

复合索引遵循最左前缀：INDEX(a, b, c) 支持 WHERE a=?、WHERE a=? AND b=?，不支持 WHERE b=? 单独使用；ORDER BY a, b 可走索引，ORDER BY b 不行。

避免在索引列上使用函数：WHERE DATE(created_at) = '2024-01-01' 无法使用 created_at 上的索引，改为 WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'。

VARCHAR 长度按实际需求设置：VARCHAR(255) 在内存中排序时会按最大长度分配缓冲区，实际字段最大长度 100 就设 VARCHAR(100)，减少 filesort 内存占用。

事务中锁的粒度尽量小：SELECT 后立即 UPDATE 的场景用 SELECT ... FOR UPDATE 锁定相关行，事务尽快提交；避免长时间持锁导致其他请求等待。

常见陷阱

陷阱：IN 子查询比 JOIN 慢

现象： 用 WHERE id IN (SELECT user_id FROM orders WHERE ...) 查询比等效 JOIN 慢数倍。
原因： MySQL 的 IN 子查询在某些版本中会对外层每行执行一次子查询（相关子查询），等价于 N+1。
解决： 改为 INNER JOIN 或 EXISTS，让优化器选择更好的执行计划。

陷阱：LIMIT 大偏移量慢

现象： LIMIT 100000, 20 查询随着偏移量增大越来越慢。
原因： MySQL 必须扫描 100000 行后才丢弃，再取 20 行，偏移越大扫描越多。
解决： 用游标分页（记录上次最大 ID）替代 OFFSET：WHERE id > last_id LIMIT 20。

陷阱：字符集不匹配导致索引失效

现象： 两表 JOIN 后 EXPLAIN 显示全表扫描，尽管 JOIN 字段都有索引。
原因： 两列字符集或排序规则（collation）不同（如 utf8mb4_unicode_ci vs utf8mb4_general_ci），MySQL 需要隐式转换，导致索引失效。
解决： 统一全库字符集为 utf8mb4，统一 collation 为 utf8mb4_unicode_ci，JOIN 时确保类型完全一致。

参见

• MySQL高级优化
• Redis完全指南
• PostgreSQL完全指南
• 数据库设计规范