> ## Content Index
> Fetch the complete content index at: https://blog.vercanti.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# MySQL 基础完全指南
- URL: https://blog.vercanti.com/mysql-ji-chu-wan-quan-zhi-nan/
- Published: 2026-08-28T14:35:36.000Z
- Updated: 2026-08-28T14:59:10.000Z
- Description: 常用约束 注意：视图不存储数据，每次查询都会执行底层 SELECT。包含 GROUP BY、DISTINCT、聚合函数、UNION 的视图通常不可更新。 pymysql.connect() 参数表 游标方法参数表 PooledDB 参数表 MySQL 的 utf8 最多只支持 3 字节，无法存储 emoji 和部分汉字（需要 4 字节）。应始终使用 utf8mb4。 EXPLAIN 先于优化：每次优化查询前用 EXPLAIN 确认执行计划，不凭直觉加索引，重点关注 type（ALL 为全表扫描）、key（实际使用的索引）、rows（估算扫描行数）。 复合
- Author: yellowdog
- Tags: 数据库

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

---

## 一、基础概念

### 数据库对象层级

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

```

### SQL 语句分类

| 类别  | 全称                           | 说明      | 代表语句                             |
| --- | ---------------------------- | ------- | -------------------------------- |
| DDL | Data Definition Language     | 定义数据库结构 | CREATE / DROP / ALTER / TRUNCATE |
| DML | Data Manipulation Language   | 操作数据    | INSERT / UPDATE / DELETE         |
| DQL | Data Query Language          | 查询数据    | SELECT                           |
| DCL | Data Control Language        | 权限控制    | GRANT / REVOKE                   |
| TCL | Transaction Control Language | 事务控制    | COMMIT / ROLLBACK / SAVEPOINT    |

### 常用数据类型

| 类型                   | 说明                       | 示例                    |
| -------------------- | ------------------------ | --------------------- |
| INT / BIGINT         | 整数                       | age INT               |
| DECIMAL(M,D)         | 精确小数，M位总长，D位小数           | price DECIMAL(10,2)   |
| FLOAT / DOUBLE       | 浮点数（不精确）                 | score FLOAT           |
| VARCHAR(N)           | 可变长字符串，最多N字符             | name VARCHAR(100)     |
| CHAR(N)              | 定长字符串                    | code CHAR(6)          |
| TEXT / LONGTEXT      | 大文本                      | content TEXT          |
| DATE                 | 日期 YYYY-MM-DD            | birthday DATE         |
| DATETIME             | 日期时间 YYYY-MM-DD HH:MM:SS | created\_at DATETIME  |
| TIMESTAMP            | 时间戳，自动时区转换               | updated\_at TIMESTAMP |
| BOOLEAN / TINYINT(1) | 布尔值（MySQL 用 TINYINT 模拟）  | is\_active BOOLEAN    |
| JSON                 | JSON 文档（MySQL 5.7+）      | meta JSON             |

---

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

### 数据库操作

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

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

-- 选择数据库
USE shop;

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

```

### CREATE TABLE

```sql
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;

```

**常用约束**

| 约束              | 说明                  |
| --------------- | ------------------- |
| PRIMARY KEY     | 主键，唯一且非空            |
| NOT NULL        | 不允许为 NULL           |
| UNIQUE          | 唯一约束                |
| DEFAULT value   | 默认值                 |
| AUTO\_INCREMENT | 自增（整数主键常用）          |
| FOREIGN KEY     | 外键约束                |
| CHECK (expr)    | 检查约束（MySQL 8.0.16+） |

### ALTER TABLE

```sql
-- 添加列
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

```sql
-- 插入单行
INSERT INTO users (username, email, age) VALUES ('alice', 'alice@example.com', 25);

-- 插入多行
INSERT INTO users (username, email, age) VALUES
    ('bob',   'bob@example.com',   30),
    ('carol', 'carol@example.com', 28);

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

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

```

### UPDATE

```sql
-- 基础更新
UPDATE users SET age = 26, email = 'alice2@example.com' 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

```sql
-- 条件删除
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 基础

```sql
-- 基础查询
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 条件运算符

| 运算符                   | 说明                 | 示例                        |
| --------------------- | ------------------ | ------------------------- |
| \= != <>              | 等于/不等于             | age = 18                  |
| \> \>= < <=           | 比较                 | age >= 18                 |
| BETWEEN a AND b       | 范围（含两端）            | age BETWEEN 18 AND 30     |
| IN (...)              | 枚举                 | age IN (18, 20, 25)       |
| NOT IN (...)          | 不在枚举中              | age NOT IN (1, 2)         |
| LIKE                  | 模糊匹配（%任意多字符，\_单字符） | email LIKE '%@gmail.com'  |
| IS NULL / IS NOT NULL | NULL 判断            | phone IS NULL             |
| EXISTS (subquery)     | 子查询存在性             | WHERE EXISTS (SELECT ...) |

### 聚合函数

| 函数                  | 说明        | 示例                                |
| ------------------- | --------- | --------------------------------- |
| COUNT(\*)           | 行数（含NULL） | COUNT(\*)                         |
| COUNT(col)          | 非NULL行数   | COUNT(email)                      |
| COUNT(DISTINCT col) | 去重非NULL计数 | COUNT(DISTINCT age)               |
| SUM(col)            | 求和        | SUM(total)                        |
| AVG(col)            | 平均值       | AVG(age)                          |
| MAX(col)            | 最大值       | MAX(age)                          |
| MIN(col)            | 最小值       | MIN(age)                          |
| GROUP\_CONCAT(col)  | 组内值拼接     | GROUP\_CONCAT(name SEPARATOR ',') |

```sql
-- 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;

```

### 常用字符串函数

| 函数                            | 说明          |
| ----------------------------- | ----------- |
| CONCAT(s1, s2, ...)           | 拼接字符串       |
| LENGTH(s) / CHAR\_LENGTH(s)   | 字节长度 / 字符长度 |
| UPPER(s) / LOWER(s)           | 大小写转换       |
| TRIM(s) / LTRIM(s) / RTRIM(s) | 去除空格        |
| SUBSTRING(s, pos, len)        | 截取子串        |
| REPLACE(s, old, new)          | 替换          |
| INSTR(s, substr)              | 子串位置        |
| FORMAT(num, d)                | 数字格式化       |

### 常用日期函数

| 函数                            | 说明     |
| ----------------------------- | ------ |
| NOW()                         | 当前日期时间 |
| CURDATE()                     | 当前日期   |
| DATE(datetime)                | 提取日期部分 |
| YEAR(d) / MONTH(d) / DAY(d)   | 提取年月日  |
| DATE\_ADD(d, INTERVAL n unit) | 日期加法   |
| DATEDIFF(d1, d2)              | 日期差（天） |
| DATE\_FORMAT(d, fmt)          | 日期格式化  |

```sql
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 笛卡尔积

```

```sql
-- 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;

```

---

## 六、子查询

```sql
-- 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;

```

---

## 七、索引

### 索引类型

| 类型           | 说明                      |
| ------------ | ----------------------- |
| PRIMARY KEY  | 主键索引，唯一且非空，一张表只有一个      |
| UNIQUE INDEX | 唯一索引，值唯一（允许NULL，多行NULL） |
| INDEX / KEY  | 普通索引                    |
| FULLTEXT     | 全文索引（InnoDB 5.6+）       |
| COMPOSITE    | 联合索引（多列）                |

```sql
-- 创建索引
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 = 'alice@example.com';

```

### EXPLAIN 输出关键字段

| 字段    | 说明                                                    |
| ----- | ----------------------------------------------------- |
| type  | 访问类型：ALL（全表扫描）< index < range < ref < eq\_ref < const |
| key   | 实际使用的索引                                               |
| rows  | 估算扫描行数                                                |
| Extra | 附加信息，Using filesort/Using temporary 需优化               |

### 联合索引最左前缀原则

```sql
-- 联合索引 (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）

| 特性               | 说明                 |
| ---------------- | ------------------ |
| Atomicity（原子性）   | 事务内所有操作要么全成功，要么全回滚 |
| Consistency（一致性） | 事务前后数据库状态保持一致      |
| Isolation（隔离性）   | 并发事务互不干扰           |
| Durability（持久性）  | 提交后数据永久保存          |

### 事务隔离级别

| 级别                       | 脏读 | 不可重复读 | 幻读             |
| ------------------------ | -- | ----- | -------------- |
| READ UNCOMMITTED         | 可能 | 可能    | 可能             |
| READ COMMITTED           | 不会 | 可能    | 可能             |
| REPEATABLE READ（MySQL默认） | 不会 | 不会    | InnoDB通过MVCC避免 |
| SERIALIZABLE             | 不会 | 不会    | 不会             |

```sql
-- 查看/设置隔离级别
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;

```

---

## 九、视图

```sql
-- 创建视图
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

```python
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() 参数表**

| 参数               | 类型    | 默认值         | 说明              |
| ---------------- | ----- | ----------- | --------------- |
| host             | str   | 'localhost' | 数据库主机地址         |
| port             | int   | 3306        | 端口号             |
| user             | str   | —           | 用户名             |
| password         | str   | ''          | 密码              |
| database         | str   | None        | 默认数据库           |
| charset          | str   | ''          | 字符集（推荐 utf8mb4） |
| cursorclass      | class | Cursor      | 游标类型            |
| autocommit       | bool  | False       | 是否自动提交          |
| connect\_timeout | int   | 10          | 连接超时（秒）         |
| read\_timeout    | int   | None        | 读取超时（秒）         |
| write\_timeout   | int   | None        | 写入超时（秒）         |
| ssl              | dict  | None        | SSL 配置          |
| unix\_socket     | str   | None        | Unix socket 路径  |

```python
# 基础 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', 'alice@example.com'))
        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', 'bob@example.com'), ('carol', 'carol@example.com')]
        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

```

**游标方法参数表**

| 方法                       | 参数                             | 说明                |
| ------------------------ | ------------------------------ | ----------------- |
| execute(sql, args)       | sql: str，args: tuple/list/dict | 执行单条 SQL，args 防注入 |
| executemany(sql, args)   | sql: str，args: list\[tuple\]   | 批量执行              |
| fetchone()               | —                              | 获取一行，无数据返回 None   |
| fetchall()               | —                              | 获取全部行             |
| fetchmany(size)          | size: int，默认 cursor.arraysize  | 获取指定行数            |
| callproc(procname, args) | procname: str，args: tuple      | 调用存储过程            |

### 连接池（DBUtils）

```python
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 参数表**

| 参数             | 类型     | 默认值   | 说明          |
| -------------- | ------ | ----- | ----------- |
| creator        | module | —     | 数据库驱动模块     |
| maxconnections | int    | 0     | 最大连接数，0表示不限 |
| mincached      | int    | 0     | 初始化时最小空闲连接  |
| maxcached      | int    | 0     | 最大空闲连接，0不限  |
| maxshared      | int    | 0     | 最大共享连接，0不共享 |
| blocking       | bool   | False | 超限时是否阻塞     |
| maxusage       | int    | None  | 连接最大使用次数    |
| setsession     | list   | \[\]  | 会话初始化SQL    |
| reset          | bool   | True  | 归还时重置连接状态   |
| failures       | tuple  | None  | 触发重连的异常类型   |

---

## 十一、最佳实践

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

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

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

```

### 2\. 合理设计索引

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

```

### 3\. 分页大偏移量优化

```sql
-- 问题：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 \*

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

```

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

```python
# 慢：循环单条插入
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\. 事务控制与连接管理

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

```

### 7\. 字符集统一设置

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

```

---

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

### 1\. NULL 的比较

```sql
-- 错误：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

```sql
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

|         | DATETIME                 | TIMESTAMP                |
| ------- | ------------------------ | ------------------------ |
| 范围      | 1000-01-01 \~ 9999-12-31 | 1970-01-01 \~ 2038-01-19 |
| 时区      | 不转换，存什么取什么               | 存储 UTC，取出时转换为当前时区        |
| 大小      | 8 字节                     | 4 字节                     |
| 2038年问题 | 无                        | 有                        |

### 5\. IN 大列表性能

```sql
-- 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\. 隐式类型转换导致索引失效

```sql
-- 字段是 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\. 游标忘记关闭

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

```

### 8\. executemany 与事务

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

```

### 9\. 连接超时与重连

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

import pymysql

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

```

### 10\. EXPLAIN 分析慢查询

```sql
-- 开启慢查询日志
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高级优化](https://blog.vercanti.com/mysql-gao-ji-you-hua/)
- [Redis完全指南](https://blog.vercanti.com/redis-wan-quan-zhi-nan/)
- [PostgreSQL完全指南](https://blog.vercanti.com/postgresql-wan-quan-zhi-nan/)
- [数据库设计规范](https://blog.vercanti.com/shu-ju-ku-she-ji-gui-fan/)