mysql

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

MySQL Core Knowledge

MySQL 核心知识

Deep Knowledge: Use
mcp__documentation__fetch_docs
with technology:
mysql
for comprehensive documentation.

深度知识：调用
mcp__documentation__fetch_docs
工具并指定技术栈为
mysql
可获取完整文档。

Table Definition

表定义

sql

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE,
    INDEX idx_email (email),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

sql

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE,
    INDEX idx_email (email),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Common Queries

常用查询

sql

-- Pagination
SELECT * FROM users
WHERE is_active = TRUE
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;

-- Join
SELECT u.*, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.id;

-- Upsert
INSERT INTO users (email, name)
VALUES (?, ?)
ON DUPLICATE KEY UPDATE name = VALUES(name);

sql

-- 分页
SELECT * FROM users
WHERE is_active = TRUE
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;

-- 关联查询
SELECT u.*, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.id;

-- 写入更新（Upsert）
INSERT INTO users (email, name)
VALUES (?, ?)
ON DUPLICATE KEY UPDATE name = VALUES(name);

Key Differences from PostgreSQL

与 PostgreSQL 的核心差异

Feature	MySQL	PostgreSQL
Auto ID	`AUTO_INCREMENT`	`SERIAL`
Boolean	`TINYINT(1)`	`BOOLEAN`
Upsert	`ON DUPLICATE KEY`	`ON CONFLICT`
JSON	`JSON`	`JSONB` (indexed)

特性	MySQL	PostgreSQL
自增ID	`AUTO_INCREMENT`	`SERIAL`
布尔类型	`TINYINT(1)`	`BOOLEAN`
写入更新	`ON DUPLICATE KEY`	`ON CONFLICT`
JSON类型	`JSON`	`JSONB` (支持索引)

When NOT to Use This Skill

何时不应使用本技能

PostgreSQL-specific features - Use
```
postgresql
```
skill for JSONB, arrays, window functions
NoSQL operations - Use
```
mongodb
```
or
```
redis
```
skills for document/key-value stores
Oracle database - Use
```
oracle
```
skill for Oracle-specific features
SQL Server - Use
```
sqlserver
```
skill for T-SQL and SQL Server features
ORM abstractions - Use framework-specific skills (Prisma, TypeORM, Sequelize)

PostgreSQL 专属特性：如需使用 JSONB、数组、窗口函数等 PostgreSQL 特性，请使用
```
postgresql
```
技能
NoSQL 操作：如需操作文档/键值存储，请使用
```
mongodb
```
或
```
redis
```
技能
Oracle 数据库：如需使用 Oracle 专属特性，请使用
```
oracle
```
技能
SQL Server：如需使用 T-SQL 或 SQL Server 特性，请使用
```
sqlserver
```
技能
ORM 抽象操作：请使用对应框架的相关技能（Prisma、TypeORM、Sequelize）

Anti-Patterns

反模式

Anti-Pattern	Issue	Solution
`SELECT *` in production	Transfers unnecessary data, performance impact	Specify needed columns explicitly
Missing `WHERE` on UPDATE/DELETE	Modifies all rows unintentionally	Always include WHERE clause
Missing indexes on JOIN/WHERE columns	Full table scans, slow queries	Add indexes on frequently queried columns
MyISAM for transactional data	No transaction support, table-level locking	Use InnoDB engine
`LIKE '%pattern'`	Cannot use index, full scan	Use `LIKE 'pattern%'` or fulltext search
Missing `LIMIT` on large tables	Can crash application	Always paginate results
Using `ENUM` for frequently changing values	Requires ALTER TABLE to add values	Use lookup table instead
Missing foreign keys	Data integrity issues	Define proper FK constraints
N+1 query problem	One query per row in loop	Use JOINs or batch queries
Not using prepared statements	SQL injection risk, slower performance	Use parameterized queries

反模式	问题	解决方案
生产环境使用 `SELECT *`	传输不必要数据，影响性能	明确指定需要查询的列
UPDATE/DELETE 语句缺少 `WHERE` 条件	意外修改全表数据	必须添加 WHERE 子句
JOIN/WHERE 关联列缺少索引	全表扫描，查询缓慢	为高频查询的列添加索引
事务性数据使用 MyISAM 引擎	不支持事务，表级锁性能差	使用 InnoDB 引擎
使用 `LIKE '%pattern'` 模糊查询	无法命中索引，触发全表扫描	使用 `LIKE 'pattern%'` 或全文搜索
大表查询缺少 `LIMIT` 限制	可能导致应用崩溃	始终对结果进行分页
频繁变更的属性使用 `ENUM` 类型	添加可选值需要执行 ALTER TABLE	改用关联 lookup 表
缺少外键约束	存在数据一致性问题	定义合理的外键约束
N+1 查询问题	循环中逐行查询数据	使用 JOIN 或批量查询
不使用预编译语句	存在 SQL 注入风险，性能更低	使用参数化查询

Quick Troubleshooting

快速排查问题

Problem	Diagnostic	Fix
Slow queries	`EXPLAIN SELECT ...`	Add indexes, rewrite query, analyze execution plan
High CPU usage	`SHOW PROCESSLIST` to find slow queries	Optimize top queries, add indexes
Connection limit reached	`SHOW STATUS LIKE 'Threads_connected'`	Increase max_connections, use connection pooling
Lock wait timeout	Check `SHOW ENGINE INNODB STATUS`	Reduce transaction time, optimize queries
Disk space full	`SELECT table_schema, SUM(data_length+index_length) FROM information_schema.tables GROUP BY 1`	Archive old data, optimize tables
Replication lag	`SHOW SLAVE STATUS`	Increase resources, tune binlog settings
Table corruption	`CHECK TABLE table_name`	Run `REPAIR TABLE` or restore from backup
Deadlocks	Check error log and `SHOW ENGINE INNODB STATUS`	Reduce transaction scope, access tables in same order

问题	诊断方法	解决方案
查询缓慢	执行 `EXPLAIN SELECT ...`	添加索引，重写查询，分析执行计划
CPU 使用率过高	执行 `SHOW PROCESSLIST` 查找慢查询	优化Top查询，添加索引
达到连接数上限	执行 `SHOW STATUS LIKE 'Threads_connected'`	调大 max_connections 参数，使用连接池
锁等待超时	查看 `SHOW ENGINE INNODB STATUS` 输出	缩短事务执行时间，优化查询
磁盘空间不足	执行 `SELECT table_schema, SUM(data_length+index_length) FROM information_schema.tables GROUP BY 1`	归档旧数据，优化表
主从复制延迟	执行 `SHOW SLAVE STATUS`	提升资源配置，调整 binlog 设置
表损坏	执行 `CHECK TABLE table_name`	执行 `REPAIR TABLE` 或从备份恢复
死锁	查看错误日志和 `SHOW ENGINE INNODB STATUS` 输出	缩小事务范围，按相同顺序访问表

Reference Documentation

参考文档

Indexes
JSON

索引
JSON