MySQL：常用语法 / MySQL 核心知识技能梳理 / 数据库

一、数据库操作

1.1 创建与管理

-- 基本语法
CREATE DATABASE database_name;

-- 完整语法（指定字符集和排序规则）
-- 推荐使用 utf8mb4 以支持 emoji 和所有 Unicode 字符
CREATE DATABASE database_name
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci; -- 或 utf8mb4_general_ci (性能稍好，但排序精度稍低)

-- 如果不存在则创建（避免报错）
CREATE DATABASE IF NOT EXISTS database_name;

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

-- 选择/切换数据库
USE database_name;

-- 查看当前数据库
SELECT DATABASE();

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

1.2 修改数据库

-- 修改数据库字符集（注意：此操作不会转换已有表的字符集）
ALTER DATABASE database_name
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

二、表操作

2.1 数据类型（补充）

分类	类型	说明
整数	TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT	可选 UNSIGNED（无符号），INT(11) 中的 11 仅为显示宽度，与存储大小无关
小数	DECIMAL(M,D), FLOAT, DOUBLE	DECIMAL 用于精确计算（如金额），M是总位数，D是小数位数
字符串	CHAR(n), VARCHAR(n), TEXT, LONGTEXT	VARCHAR 变长，节省空间；TEXT 类型不能有默认值
日期时间	DATE, TIME, DATETIME, TIMESTAMP, YEAR	TIMESTAMP 受时区影响，范围较小；DATETIME 范围大，不受时区影响
二进制	BLOB, LONGBLOB, BINARY, VARBINARY	存储图片、文件等二进制数据
枚举	ENUM('a','b','c')	单选，内部存储为整数，最多 65535 个成员
集合	SET('a','b','c')	多选，最多 64 个成员
JSON	JSON	MySQL 5.7+ 支持，提供高效的 JSON 文档存储和查询
空间数据	GEOMETRY, POINT, LINESTRING, POLYGON	用于地理空间数据

2.2 创建表（增强）

-- 完整示例（包含更多约束和选项）
CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  username VARCHAR(50) NOT NULL COMMENT '用户名',
  email VARCHAR(100) NOT NULL COMMENT '邮箱',
  age TINYINT UNSIGNED DEFAULT 0 COMMENT '年龄',
  status ENUM('active', 'inactive', 'banned') NOT NULL DEFAULT 'active' COMMENT '状态',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  
  -- 约束
  PRIMARY KEY (id),
  UNIQUE KEY uk_username (username),
  UNIQUE KEY uk_email (email),
  INDEX idx_status_age (status, age), -- 复合索引
  
  -- 外键约束
  -- ON DELETE CASCADE: 主表删除时，从表关联行也删除
  -- ON DELETE SET NULL: 主表删除时，从表关联字段设为NULL
  -- ON UPDATE CASCADE: 主表主键更新时，从表关联字段也更新
  FOREIGN KEY (dept_id) REFERENCES departments(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';

-- 分区表（适用于大数据量）
CREATE TABLE sales_log (
  id INT AUTO_INCREMENT,
  sale_date DATE NOT NULL,
  amount DECIMAL(10, 2),
  PRIMARY KEY (id, sale_date) -- 分区键必须是主键的一部分
)
PARTITION BY RANGE (YEAR(sale_date)) (
  PARTITION p2022 VALUES LESS THAN (2023),
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025)
);

-- 创建表时指定存储引擎和字符集
-- ENGINE=InnoDB (支持事务、行锁、外键)
-- ENGINE=MyISAM (不支持事务，表锁，全文索引，旧版本默认)
-- ENGINE=Memory (数据存于内存，速度快，重启后丢失)

2.3 修改表

-- 修改表名
ALTER TABLE old_name RENAME TO new_name;
RENAME TABLE old_name TO new_name;

-- 修改列（重命名+改类型+改约束）
ALTER TABLE table_name CHANGE old_col_name new_col_name new_datatype [NOT NULL] [DEFAULT 'value'];

-- 仅修改列的约束或默认值（比 CHANGE 更高效）
ALTER TABLE table_name MODIFY col_name datatype NOT NULL DEFAULT 'new_default';

-- 添加/删除外键
ALTER TABLE table_name ADD CONSTRAINT fk_name FOREIGN KEY (col) REFERENCES other_table(id);
ALTER TABLE table_name DROP FOREIGN KEY fk_name;

-- 表选项
ALTER TABLE table_name ROW_FORMAT=DYNAMIC; -- 优化行存储格式
ALTER TABLE table_name COMMENT = '新的表注释';

三、数据操作（CRUD）

3.1 插入数据 (INSERT) 

-- 批量插入并忽略错误
INSERT IGNORE INTO table_name (id, name) VALUES (1, 'a'), (2, 'b');

-- 批量插入并更新（UPSERT）
INSERT INTO table_name (id, name, count) VALUES (1, 'a', 10), (2, 'b', 20)
ON DUPLICATE KEY UPDATE count = VALUES(count) + count; -- VALUES() 函数引用待插入的值

-- 使用 SET 语法插入（更灵活）
INSERT INTO table_name SET id = 1, name = 'test';

3.2 查询数据 (SELECT) 

-- 强制使用指定索引（Index Hint）
SELECT * FROM users FORCE INDEX (idx_email) WHERE email = 'test@example.com';
SELECT * FROM users IGNORE INDEX (idx_status) WHERE status = 'active';

-- 查询锁定
SELECT * FROM orders WHERE status = 'pending' FOR UPDATE; -- 排他锁（写锁）
SELECT * FROM orders WHERE status = 'pending' LOCK IN SHARE MODE; -- 共享锁（读锁）

-- 分组后筛选 (HAVING vs WHERE)
-- WHERE 在分组前筛选行
-- HAVING 在分组后筛选组
SELECT department, AVG(salary) as avg_sal
FROM employees
WHERE hire_date > '2020-01-01'  -- 先筛选出2020年后入职的员工
GROUP BY department
HAVING AVG(salary) > 5000;      -- 再筛选出平均工资大于5000的部门

-- 流程控制函数
SELECT
  name,
  IF(score >= 60, '及格', '不及格') AS result,
  CASE
    WHEN score >= 90 THEN '优秀'
    WHEN score >= 80 THEN '良好'
    WHEN score >= 60 THEN '及格'
    ELSE '不及格'
  END AS grade
FROM exam_results;

3.3 连接查询 (JOIN) 

-- STRAIGHT_JOIN: 强制按 FROM 子句中表的顺序进行连接
SELECT * FROM table1 STRAIGHT_JOIN table2 ON table1.id = table2.id;

3.4 子查询

-- 关联子查询（效率较低，慎用）
SELECT * FROM users u WHERE (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) > 10;
-- 优化为 JOIN
SELECT u.* FROM users u JOIN (SELECT user_id, COUNT(*) as order_count FROM orders GROUP BY user_id HAVING order_count > 10) o_count ON u.id = o_count.user_id;

3.5 窗口函数 (MySQL 8.0+)

-- 窗口函数框架: FUNCTION() OVER (PARTITION BY ... ORDER BY ... [frame_clause])
-- frame_clause: ROWS/RANGE BETWEEN start AND end
--   - UNBOUNDED PRECEDING: 分区开始
--   - N PRECEDING: 当前行前N行
--   - CURRENT ROW: 当前行
--   - N FOLLOWING: 当前行后N行
--   - UNBOUNDED FOLLOWING: 分区结束

-- 计算移动平均（最近3行）
SELECT
  date,
  amount,
  AVG(amount) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3_days
FROM daily_sales;

-- 计算累计占比
SELECT
  category,
  amount,
  SUM(amount) OVER (PARTITION BY category ORDER BY date) AS cumulative_sum,
  amount / SUM(amount) OVER (PARTITION BY category) AS percentage_of_total
FROM sales;

四、索引与性能优化

4.1 索引类型

B-Tree 索引：默认索引类型，适用于全值匹配、范围查询、排序。

Hash 索引：仅 Memory 引擎显式支持，仅适用于精确匹配（=），速度快。

全文索引 (FULLTEXT)：用于 CHAR, VARCHAR, TEXT 列的文本搜索。

CREATE FULLTEXT INDEX idx_content ON articles(title, body);
SELECT * FROM articles WHERE MATCH(title, body) AGAINST('MySQL 性能优化' IN NATURAL LANGUAGE MODE);

空间索引 (SPATIAL)：用于地理空间数据类型。

降序索引 (Descending Index)：MySQL 8.0+ 支持，优化 ORDER BY col DESC 查询。

CREATE INDEX idx_created_at_desc ON logs(created_at DESC);

隐藏索引 (Invisible Index)：MySQL 8.0+ 支持，优化器会忽略此索引，用于测试删除索引的影响。

CREATE INDEX idx_name ON users(name) INVISIBLE;
ALTER TABLE users ALTER INDEX idx_name VISIBLE;

函数索引 (Function-Based Index)：MySQL 8.0+ 支持，对表达式结果建索引。

CREATE INDEX idx_lower_email ON users((LOWER(email)));
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';

4.2 性能优化建议

-- 1. 使用 EXPLAIN 分析查询计划
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
EXPLAIN FORMAT=JSON SELECT ...; -- 更详细的JSON格式输出
EXPLAIN ANALYZE SELECT ...; -- MySQL 8.0+ 实际执行查询并显示成本和时间

-- 2. 避免 SELECT *，只取需要的列
SELECT id, name FROM users;

-- 3. 避免在 WHERE 子句中对索引列使用函数或计算
-- 错误: WHERE YEAR(created_at) = 2023 (索引失效)
-- 正确: WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01' (索引有效)

-- 4. 使用 EXISTS 代替 IN (对于大子查询)
-- WHERE id IN (SELECT user_id FROM orders)
-- 优化为:
-- WHERE EXISTS (SELECT 1 FROM orders WHERE user_id = users.id)

-- 5. 合理使用索引覆盖 (Covering Index)
-- 索引包含查询所需的所有列，无需回表
CREATE INDEX idx_covering ON orders (user_id, amount, status);
SELECT user_id, amount FROM orders WHERE user_id = 123; -- 只需扫描索引

-- 6. 分批处理大量数据更新/删除
-- DELETE FROM logs WHERE created_at < '2024-01-01' LIMIT 10000;
-- 在循环中执行，避免长事务和锁竞争

-- 7. 使用合适的 JOIN 算法
-- Block Nested-Loop (BNL) Join: 驱动表结果集存入 join buffer
-- MySQL 会自动选择，可通过 `optimizer_switch` 调整

-- 8. 优化 COUNT(*) 查询
-- InnoDB 中 COUNT(*) 需要全表扫描，可近似查询或使用缓存/触发器维护计数
SELECT COUNT(*) FROM users; -- 慢
SELECT TABLE_ROWS FROM information_schema.tables WHERE table_name = 'users'; -- 快但不精确

五、高级对象

5.1 视图 (VIEW)

-- 创建视图
CREATE VIEW active_users_view AS
SELECT id, username, email FROM users WHERE status = 'active';

-- 可更新视图（需满足一定条件，如不含聚合、DISTINCT、GROUP BY等）
CREATE VIEW user_email_view AS SELECT id, email FROM users;
UPDATE user_email_view SET email = 'new@example.com' WHERE id = 1; -- 会更新基表

-- 查看视图定义
SHOW CREATE VIEW view_name;

5.2 存储过程与函数

-- 存储过程：可以执行复杂的逻辑，无返回值（通过OUT参数返回）
DELIMITER //
CREATE PROCEDURE GetUserOrders(IN userId INT, OUT orderCount INT)
BEGIN
  SELECT COUNT(*) INTO orderCount FROM orders WHERE user_id = userId;
END //
DELIMITER ;

-- 函数：必须有返回值，不能修改数据库状态（除非是 DETERMINISTIC 或 NOT DETERMINISTIC）
DELIMITER //
CREATE FUNCTION CalculateDiscount(price DECIMAL(10,2), level INT)
RETURNS DECIMAL(10,2)
DETERMINISTIC -- 声明为确定性函数，可被优化
BEGIN
  DECLARE discount DECIMAL(10,2);
  IF level > 3 THEN
    SET discount = 0.2;
  ELSEIF level > 1 THEN
    SET discount = 0.1;
  ELSE
    SET discount = 0;
  END IF;
  RETURN price * (1 - discount);
END //
DELIMITER ;

5.3 触发器 (TRIGGER)

-- 创建触发器
-- NEW 和 OLD 是伪记录，分别代表新/旧行数据
CREATE TRIGGER before_user_update
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
  IF OLD.email != NEW.email THEN
    INSERT INTO user_audit_log (user_id, old_email, new_email, change_time)
    VALUES (OLD.id, OLD.email, NEW.email, NOW());
  END IF;
END;

-- 查看触发器
SHOW TRIGGERS;
SHOW TRIGGERS LIKE 'user%';

5.4 事件调度器 (Event Scheduler)

-- 开启事件调度器
SET GLOBAL event_scheduler = ON;

-- 创建定时任务（例如：每天凌晨1点归档日志）
CREATE EVENT archive_logs_daily
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 01:00:00'
DO
BEGIN
  INSERT INTO logs_archive SELECT * FROM logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);
  DELETE FROM logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);
END;

-- 查看事件
SHOW EVENTS;

-- 修改/删除事件
ALTER EVENT archive_logs_daily DISABLE; -- 禁用
DROP EVENT archive_logs_daily;

六、事务与锁

6.1 事务控制

-- 事务特性 (ACID)
-- Atomicity (原子性), Consistency (一致性), Isolation (隔离性), Durability (持久性)

-- 隔离级别
-- READ UNCOMMITTED (读未提交): 脏读
-- READ COMMITTED (读已提交): 不可重复读 (Oracle, SQL Server 默认)
-- REPEATABLE READ (可重复读): 幻读 (MySQL InnoDB 默认)
-- SERIALIZABLE (串行化): 性能最差，最安全

-- 设置事务隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 事务操作
START TRANSACTION; -- 或 BEGIN
-- ... SQL 语句 ...
COMMIT; -- 提交
-- ROLLBACK; -- 回滚

-- 保存点 (Savepoint)
SAVEPOINT sp1;
-- ... SQL 语句 ...
ROLLBACK TO SAVEPOINT sp1; -- 回滚到保存点
RELEASE SAVEPOINT sp1; -- 释放保存点

6.2 锁机制

表锁 (Table Lock)：开销小，加锁快，无死锁，但并发度低。MyISAM 默认。

LOCK TABLES table_name READ; -- 共享锁
LOCK TABLES table_name WRITE; -- 排他锁
UNLOCK TABLES;

行锁 (Row Lock)：开销大，加锁慢，会出现死锁，但并发度高。InnoDB 支持。

记录锁 (Record Lock)：锁住单条索引记录。

间隙锁 (Gap Lock)：锁住索引区间，防止幻读。

临键锁 (Next-Key Lock)：记录锁 + 间隙锁。

行锁是在需要时自动加的，也可以手动控制：

SELECT * FROM table_name WHERE id = 1 FOR UPDATE; -- 排他行锁
SELECT * FROM table_name WHERE id = 1 LOCK IN SHARE MODE; -- 共享行锁

死锁 (Deadlock)：两个或多个事务相互等待对方释放锁。InnoDB 有死锁检测机制，会自动回滚代价较小的事务。

七、用户、权限与安全

-- 创建用户
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'StrongPassword123!';
CREATE USER 'readonly_user'@'%' IDENTIFIED BY 'password';

-- 授予权限
-- 权限级别: *.* (所有库所有表), db_name.* (指定库所有表), db_name.table_name (指定表)
GRANT SELECT, INSERT ON my_app.* TO 'app_user'@'192.168.1.%';
GRANT SELECT ON my_app.products TO 'readonly_user'@'%';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION; -- 允许授权给他人

-- 撤销权限
REVOKE INSERT ON my_app.* FROM 'app_user'@'192.168.1.%';

-- 查看权限
SHOW GRANTS FOR 'app_user'@'192.168.1.%';
SHOW GRANTS FOR CURRENT_USER;

-- 刷新权限（修改授权表后需要）
FLUSH PRIVILEGES;

-- 删除用户
DROP USER 'app_user'@'192.168.1.%';

八、备份、恢复与复制

8.1 备份与恢复

# 逻辑备份 (mysqldump)
# 备份整个数据库
mysqldump -u root -p --single-transaction --routines --triggers --events my_app > my_app_backup.sql

# 备份指定表
mysqldump -u root -p my_app users orders > users_orders_backup.sql

# 恢复
mysql -u root -p my_app < my_app_backup.sql
# 或在 mysql 命令行中
SOURCE /path/to/my_app_backup.sql;

--single-transaction: 对于 InnoDB 表，可以在不锁表的情况下进行一致性备份。

--routines, --triggers, --events: 备份存储过程、触发器和事件。

8.2 二进制日志 (Binary Log)

记录所有对数据库的更改（DDL 和 DML），用于主从复制和数据恢复。

在 my.cnf 或 my.ini 中配置：

ini

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW # ROW, STATEMENT, MIXED

查看二进制日志：

SHOW BINARY LOGS;
SHOW BINLOG EVENTS IN 'mysql-bin.000003';

使用 mysqlbinlog 工具恢复数据：

bash
mysqlbinlog /var/log/mysql/mysql-bin.000003 | mysql -u root -p

九、JSON 操作 (MySQL 5.7+)

-- 创建表
CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  info JSON
);

-- 插入 JSON
INSERT INTO products (info) VALUES
('{"name": "Laptop", "price": 1200, "specs": {"ram": "16GB", "storage": "512GB SSD"}, "tags": ["electronics", "computer"]}'),
('{"name": "Mouse", "price": 25}');

-- 查询 JSON (-> 提取， ->> 提取并去引号)
SELECT
  info->'$.name' AS name_json,         -- "Laptop"
  info->>'$.name' AS name_string,      -- Laptop
  info->>'$.specs.ram' AS ram,         -- 16GB
  JSON_EXTRACT(info, '$.price') AS price
FROM products;

-- 更新 JSON
UPDATE products SET info = JSON_SET(info, '$.price', 1150) WHERE id = 1;
UPDATE products SET info = JSON_INSERT(info, '$.discount', 0.1) WHERE id = 1;
UPDATE products SET info = JSON_REMOVE(info, '$.specs.storage') WHERE id = 1;
UPDATE products SET info = JSON_ARRAY_APPEND(info, '$.tags', 'gadget') WHERE id = 1;

-- 查询 JSON 数组
SELECT info->'$.tags[0]' FROM products WHERE id = 1; -- "electronics"
SELECT JSON_CONTAINS(info->'$.tags', '"computer"') FROM products; -- 1 (true)
SELECT JSON_SEARCH(info, 'one', 'Mouse') FROM products; -- "$.name"

十、常用系统命令与工具

-- 查看服务器状态
SHOW STATUS LIKE 'Threads_connected'; -- 当前连接数
SHOW STATUS LIKE 'Innodb_row_lock_waits'; -- InnoDB 行锁等待次数
SHOW VARIABLES LIKE 'max_connections'; -- 最大连接数

-- 查看进程列表
SHOW PROCESSLIST;
KILL [process_id]; -- 终止某个连接

-- 查看表状态
SHOW TABLE STATUS FROM database_name LIKE 'table_name';

-- 分析表
ANALYZE TABLE table_name; -- 更新索引的基数信息
OPTIMIZE TABLE table_name; -- 整理碎片（仅对MyISAM和InnoDB有效）

-- 检查表
CHECK TABLE table_name;

-- 查看字符集和排序规则
SHOW CHARACTER SET;
SHOW COLLATION;

十一、一个MySQL数据库的容量是多大，可以放多少张表，多少行数据

MySQL 的容量非常灵活，并没有一个固定的“数据库大小上限”，它主要受限于你的硬盘空间和操作系统

维度	理论上限	实际约束
单个数据库最大容量	无限（受文件系统限制）	受硬盘物理空间限制，以及文件系统单文件大小限制（如 FAT32 的 4GB 限制，NTFS 几乎无限制）
单表最大容量	256 TB（InnoDB 默认页大小 16KB）	实际受硬盘限制，且大表会影响性能
单表最大行数	2^64 - 1（约 1844 亿亿）	受主键类型和硬盘空间限制，实际远小于理论值
单表最大列数	1017 列（InnoDB）	受行大小限制，实际通常远小于这个数
单行最大大小	65535 字节（约 64KB）	这是硬性限制，但不包括 BLOB/TEXT 等大字段类型
索引最大长度	767 字节（InnoDB 默认） / 3072 字节（开启参数后）	受 innodb_large_prefix 参数影响
主键字段最大长度	3072 字节	受 innodb_large_prefix 和 innodb_page_size 影响
自增主键最大值	2^31 - 1（约 21.47 亿）（int 类型）或 2^63 - 1（bigint 类型）	使用 int 类型自增主键时，行数不能超过 21.47 亿

说明：数据库总容量没有理论上限，完全取决于你的硬盘大小和操作系统限制。比如你挂载一个 2TB 的硬盘，数据库最大就能到 2TB 左右。

不同存储引擎的容量对比
对比项	InnoDB（默认）	MyISAM	Memory
单表最大容量	256 TB（页大小 16KB）	256 TB（受操作系统文件大小限制）	受 max_heap_table_size 限制（默认 16MB）
单表最大行数	2^64 - 1	2^64 - 1	受内存大小限制，约几百万行
是否支持事务	✅ 支持	❌ 不支持	❌ 不支持
行级锁	✅ 支持	❌ 仅表级锁	❌ 仅表级锁
崩溃恢复	✅ 支持	❌ 不支持	❌ 不支持
适用场景	生产环境所有场景	读多写少的历史表、日志表	临时表、缓存表

不同存储引擎的容量对比
数据量级别	建议存储方式	性能表现	说明
小于 100 万行	单表直接存储	查询极快（毫秒级）	不需要考虑任何优化
100 万 ~ 500 万行	单表存储 + 合理索引	查询较快（几十毫秒）	需要建立正确索引，定期维护
500 万 ~ 2000 万行	单表 + 分区表/归档	查询中速（百毫秒级）	建议对历史数据做归档或分区（如按月分区）
2000 万 ~ 1 亿行	分库分表（按业务维度）	查询中速（需设计好分片策略）	必须分库分表，建议使用 ShardingSphere、MyCat
> 1 亿行	分库分表 + 大数据组件	中等速度，依赖架构设计	建议用 TiDB 或 OLAP 数据库（如 ClickHouse）

突破 2000 万行限制的常用方案
方案	适用场景	核心原理
分区表（Partition）	单表数据量大，但可按时间/范围等业务维度拆分	物理上分成多个子表，逻辑上对外暴露一张表
分库分表	数据量巨大（亿级、十亿级）	按业务键（如用户ID）将数据分散到多个物理库/表
使用 TiDB	需要 MySQL 协议兼容、自动水平扩展	分布式数据库，自动分片和负载均衡
使用 ClickHouse	海量数据的分析查询场景	列式存储，压缩比高，查询极快
数据归档	历史数据不常查询	将老旧数据迁移到冷存储，主表只保留近期数据

总结
单个 MySQL 数据库最多能有多大？	没有上限，受硬盘空间限制。
最多能放多少张表？	受文件系统限制，InnoDB 理论支持最多 40亿 个表，实际受文件描述符限制。
最多能放多少行数据？	InnoDB 理论支持 1844 亿亿 行，实际受硬盘空间和索引结构限制。
单表超过 2000 万行怎么办？	考虑分区、分库分表或迁移到分布式数据库。
生产环境应该用什么？	绝大多数业务用 InnoDB，常规表控制在 2000 万行以内，达到或超过该数量及时进行分区或分库分表。

十二、Web服务器和SQL数据库服务器

浏览器（HTTP）
    ↓
Web服务器（Nginx，处理HTTP协议）
    ↓
应用服务器（Spring Boot，处理业务逻辑）
    ↓
JDBC驱动（把Java调用翻译成MySQL协议）
    ↓
SQL数据库服务器（MySQL，处理MySQL协议）
    ↓
返回数据

十三、MySQL数据库服务器的作用与职责

作用维度	具体职责	通俗解释
监听与连接管理	• 在默认端口（3306）上监听来自客户端的TCP/IP连接请求 • 认证客户端身份（验证用户名、密码、来源IP） • 为每个连接分配线程或进程，维护连接池	像个值班的门卫，登记来访者身份，为每个来访者分配一个专属“接待员”。
SQL指令解析与执行	• 接收并解析客户端发来的SQL语句（SELECT、INSERT等） • 检查SQL语法和语义是否正确 • 生成执行计划，选择最优算法（如索引查找、全表扫描）	像个翻译官，把“数据库语言”翻译成机器能执行的指令。
存储引擎交互	• 调用底层存储引擎（如InnoDB、MyISAM） • 负责数据的实际读写、索引维护、事务日志记录	像个仓库管理员，指挥叉车工（存储引擎）把货物（数据）放到指定货架（磁盘）上。
事务管理与锁控制	• 管理事务的ACID特性（原子性、一致性、隔离性、持久性） • 实现MVCC（多版本并发控制），提供不同隔离级别 • 管理表锁、行锁等，保证并发操作的数据一致性	像个裁判，保证多个人同时操作同一张表时，数据不乱套。
结果集返回	• 将查询到的数据按客户端要求的格式（如结果集、JSON、CSV）返回 • 返回操作状态（如“受影响的行数”）	像个送货员，把打包好的数据结果安全送回给客户端。
安全与权限管理	• 管理用户账户、角色和权限（如SELECT、INSERT权限） • 记录操作日志，提供审计功能	像个保安队长，决定谁能进哪个房间（数据库），能拿什么东西（表/字段）。
缓存与性能优化	• 管理查询缓存（Query Cache） • 管理InnoDB缓冲池，预读和缓存数据页 • 通过慢查询日志、执行计划分析，辅助性能调优	像个管家，把常用的东西（数据/指令）放在随手能拿到的地方（缓存），以提高效率。
数据备份与恢复	• 提供逻辑备份工具（如mysqldump） • 维护二进制日志（binlog），用于数据恢复和主从复制	像个档案员，定期给重要资料做副本，并记录所有的“改动日志”。
高可用与复制	• 支持主从复制，将数据同步到备用服务器 • 支持组复制、InnoDB Cluster等高可用方案	像个备胎管理员，确保一旦主服务器出问题，备用车（从库）能立刻顶上。
MySQL数据库服务器是一个集门卫、翻译官、仓库管理员、裁判、送货员、保安队长、管家、档案员、备胎管理员于一体的“全能型后台服务进程”，它负责安全、高效、可靠地处理所有对数据库的访问请求和数据操作。
数据库服务器“天生就懂”自己的专属协议（比如MySQL协议），所以在和它通信时，确实不需要像处理HTTP请求那样，再去做一次“翻译”。

十四、常见数据库的通信协议与JDBC驱动

数据库	使用的通信协议	JDBC驱动类（示例）	连接URL格式（示例）
MySQL	MySQL协议 (基于TCP)； 所以不需要像HTTP协议那样做“协议转换”或“翻译”	com.mysql.cj.jdbc.Driver (MySQL 8+)	jdbc:mysql://localhost:3306/mydb
PostgreSQL	PostgreSQL协议 (基于TCP)	org.postgresql.Driver	jdbc:postgresql://localhost:5432/mydb
SQL Server	TDS (Tabular Data Stream) 协议	com.microsoft.sqlserver.jdbc.SQLServerDriver	jdbc:sqlserver://localhost:1433;databaseName=mydb
Oracle	Oracle Net协议	oracle.jdbc.driver.OracleDriver	jdbc:oracle:thin:@//localhost:1521/orcl
你可以看到，连接URL的通用格式是 jdbc:子协议:子名称，这里的“子协议”就指明了具体要和哪个数据库通信。

十五、JDBC与HTTP的对比

对比维度	Java应用 → SQL数据库 (JDBC)	Java应用 → Web服务 (HTTP)
通信协议	MySQL协议 / TDS协议 / Oracle Net协议等	HTTP / HTTPS
主要用途	执行SQL语句，存取关系型数据	调用RESTful API，交换数据（如JSON/XML）
中间件/工具	JDBC驱动程序	HTTP客户端（如HttpClient, RestTemplate）
典型端口	3306 (MySQL), 1433 (SQL Server), 5432 (PostgreSQL)	80 (HTTP), 443 (HTTPS)
Java应用与SQL数据库的通信是一个多语言、多协议的世界。JDBC标准提供了统一的编程接口，而其底层的驱动则负责根据数据库类型，自动切换并使用其对应的专属网络协议，如MySQL协议或TDS协议。

十六、Java MySQL 对比 浏览器 → Java后端 (HTTP)

对比点	Java → MySQL (JDBC)	浏览器 → Java后端 (HTTP)
通信双方	Java应用 (JDBC驱动)	浏览器 (JS代码) → Java后端 (Spring MVC)
应用层协议	MySQL协议 (直接用)	HTTP协议 (直接用)
是否有翻译？	❌ 没有！ 因为双方从设计之初就约定好使用MySQL协议，天生就懂。	❌ 也没有！ 因为浏览器和Java后端也都原生支持HTTP协议，天生就懂。
那“翻译”在哪？	在应用内部：JDBC驱动把Java方法调用转化成MySQL协议数据包。这属于“内部流程”，不是“协议转换”。	在后端内部：Spring MVC把HTTP请求里的JSON数据，解析成Java对象。同样属于“内部流程”，不是“协议转换”。
你会发现，真正需要“翻译”的情况，是当通信双方使用完全不同的应用层协议时。 例如，把一个HTTP请求转换成内部微服务能懂的gRPC协议，这才叫协议转换。

十七、JDBC 核心知识一览表

维度	具体内容	通俗解释
1. 是什么	Java数据库连接（Java Database Connectivity），是Java提供的一套标准API，用于让Java程序与各种关系型数据库进行通信。	它是Java程序与数据库之间的“通用翻译器”。无论底层是哪种数据库，Java程序都通过这套统一的API来操作，而不用关心每种数据库的“方言”。
2. 核心用途	• 建立与数据库的连接 • 发送SQL语句（查询、更新等） • 接收并处理数据库返回的结果	它是Java程序访问数据库的唯一官方通道。没有它，Java程序就无法操作数据库。
3. 关键组件	DriverManager：管理并注册JDBC驱动，负责建立连接。 Connection：代表与特定数据库的物理连接会话。 Statement / PreparedStatement：用于执行SQL语句的对象。 ResultSet：代表查询结果集的数据表。	• DriverManager：像一个出租车调度中心，帮你叫一辆能开往指定数据库（目的地）的“车”（连接）。 • Connection：就是你叫到的那辆“车”，代表你和数据库之间的一个网络连接。 • Statement：是车上的“方向盘”，用来操控“车”（执行SQL）去拿数据。 • ResultSet：是车开回来后，你手里拿到的那份“购物清单”，里面是查询到的数据行。
4. 工作流程	1. 注册驱动：加载数据库驱动类。 2. 获取连接：通过DriverManager获取Connection。 3. 创建语句：基于Connection创建Statement/PreparedStatement。 4. 执行查询：执行SQL，获取ResultSet。 5. 处理结果：遍历ResultSet，处理数据。 6. 关闭资源：反向关闭ResultSet、Statement、Connection。	1. 告诉调度中心你要去哪（注册驱动）。 2. 调度中心派一辆车给你（获取连接）。 3. 你手握方向盘（创建语句）。 4. 开车到目的地并执行任务（执行查询）。 5. 你拿到购物清单（处理结果）。 6. 还车、交回方向盘、扔掉清单（关闭资源）。
5. 常用方法	• DriverManager.getConnection()：建立连接。 • Connection.createStatement()：创建普通语句。 • Connection.prepareStatement(sql)：创建预编译语句（防SQL注入）。 • Statement.executeQuery(sql)：执行查询，返回ResultSet。 • Statement.executeUpdate(sql)：执行更新（INSERT/UPDATE/DELETE）。 • ResultSet.next()：移动光标到下一行。 • ResultSet.getString("列名")：获取指定列的值。	—
6. 重要特性	• 平台无关：JDBC API是Java标准库的一部分，不依赖特定数据库。 • 数据库无关：通过更换驱动，同一套代码可连接不同数据库。 • 预编译（PreparedStatement）：可防止SQL注入，并提升性能。 • 事务支持：支持手动提交和回滚事务。	—
7. 事务管理	• 默认自动提交（每条SQL独立事务）。 • 可通过 connection.setAutoCommit(false) 开启手动事务。 • connection.commit() 提交事务。 • connection.rollback() 回滚事务。	默认是“每办一件事就立刻结账”；手动事务是“办完一系列事，确认无误后统一结账，出问题就全部撤销”。
8. 主要异常	SQLException（及其子类），几乎所有JDBC操作都可能抛出此异常。	这是JDBC的通用“警报”，表明与数据库通信或操作过程中出现了问题。
9. 最佳实践	• 使用PreparedStatement代替Statement，防止SQL注入。 • 在finally块中关闭资源（或使用try-with-resources）。 • 使用连接池（如HikariCP）管理连接，提高性能。 • 妥善处理事务边界。	—
10. 进阶框架	MyBatis：半ORM（对象关系映射）框架，灵活控制SQL。 Hibernate / JPA：全ORM框架，以操作对象的方式操作数据库。 Spring Data JPA：在JPA之上进一步简化数据访问层代码。	JDBC是“手动挡”；MyBatis是“自动挡但能随时手动介入”；Hibernate/JPA是“自动驾驶”。
JDBC是Java生态中最基础、最重要的数据库访问技术。它不仅是一门技术，更是一个思想标准——用统一的接口，连接不同的数据库。理解了JDBC，你就明白了Java应用与数据库交互的底层逻辑，这为后续学习任何ORM框架（如MyBatis、Hibernate）都打下了坚实的基础。

十八、Java服务器、Web服务器、数据库的协作关系

浏览器（HTTP）
    ↓
Web服务器（Nginx：接收HTTP请求，转发给Java应用）
    ↓
Java应用服务器（Tomcat/Spring Boot）
    │
    ├── 底层容器：把HTTP请求解析成Java对象（这是翻译）
    ├── Controller：接收Java对象，调用业务逻辑
    ├── Service：执行业务计算
    ├── DAO：通过JDBC驱动，用MySQL协议请求数据库
    ↓
MySQL服务器：执行SQL，返回数据（用MySQL协议）
    ↓
Java应用服务器：把结果封装成JSON/HTML，通过HTTP响应返回给Web服务器/浏览器

浏览器	顾客	说“我想要一杯拿铁”（发送HTTP请求）
Web服务器（Nginx）	前台服务员	接待顾客，记录需求，把“拿铁”的指令传给后厨
Java应用服务器	后厨	接到“做一杯拿铁”的指令（解析后的HTTP请求），按照配方（业务逻辑）制作拿铁（查数据库、计算），把做好的拿铁（结果数据）交给前台
MySQL服务器	食材仓库	存放原料（数据），后厨需要时去取（执行SQL）

十九、查看 Mysql 版本

# 查看版本（不连接）

mysql --version

# 连接数据库（会提示输入密码）

mysql -u root -p

# 用管道方式导入 SQL（PowerShell 写法）

Get-Content backend/src/main/resources/db/schema.sql | mysql -u root -p mysql_learn

# 或者切换到 cmd 执行（支持 < 重定向）

cmd /c "mysql -u root -p < backend/src/main/resources/db/schema.sql"

二十、查看数据库启动状态

Get-Service -Name "*mysql*" | Format-Table Name, Status, StartType -AutoSize

属性	值	说明
服务名	mysql	Windows 系统服务中注册的名称
状态	Running	服务当前正在运行中，可正常接受连接
启动类型	Automatic	系统启动时自动启动，无需每次手动开启

二十一、如果后端项目连不上 MySQL，用下面这张表逐项排查

排查项	检查方法	正常结果	异常时的处理
MySQL 服务状态	services.msc 查看	Running	手动启动服务
端口是否监听	netstat -ano | findstr 3306	显示 LISTENING 状态	检查端口占用或 MySQL 配置文件
命令行能否登录	mysql -u root -p	输入密码后进入 mysql> 提示符	检查用户名/密码是否正确
数据库是否存在	SHOW DATABASES; 查看结果	能看到项目配置的数据库名（如 mysql_learn）	如果不存在则需要手动创建
连接 URL 是否正确	检查 application.yml 中的 url	jdbc:mysql://127.0.0.1:3306/数据库名?...	核对 IP、端口、数据库名、参数

二十二、Windows 下手动管理 MySQL 服务的命令

22.1、验证是否链接成功

mysql -u root -p -e "SELECT 1"

22.2、启动

# 需要管理员权限的终端

net start mysql

# 不用管理员权限，直接启动 mysqld 进程

"C:\mysql\mysql-9.7.1-winx64\bin\mysqld.exe" --console

# 通过 PowerShell 提权执行

Start-Process powershell -Verb RunAs -ArgumentList "net start mysql"

22.3、停止

net stop mysql

# 通过 PowerShell 提权执行

Start-Process powershell -Verb RunAs -ArgumentList "net stop mysql"

# 通过 Windows 服务管理界面（最简单）
Win + R 输入 services.msc 回车
找到 mysql 服务
右键 → 停止

22.4、重启

# 需要管理员权限的终端

net stop mysql; net start mysql

二十三、数据库怎么链接的 / application-dev.yml

。。

二十四、建表、导数据

CREATE DATABASE 就是创建这个"文件夹"。

文件夹创建好了，里面的表和数据由 schema.sql（建表）和 data.sql（导数据）负责填充。

二十五、修改密码

-- 先登录

mysql -u root -p

-- 登录后执行

ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码';

FLUSH PRIVILEGES;

mysql -u root -p -e "ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码'; FLUSH PRIVILEGES;"

二十六、DDL 是啥

DDL 是 Data Definition Language（数据定义语言）的缩写，是 SQL 中用于定义和管理数据库结构的一组命令。

简单说，DDL 就是 "建表、改表、删表" 用的 SQL 语句。