MySQL入门

发表于 2019-12-09 更新于 2026-11-04 分类于 Python Python教程 Python基础本文字数： 79k 阅读时长 ≈ 2:24

MySQL 简介

MySQL 是一个开源的关系型数据库管理系统（RDBMS），用 SQL（结构化查询语言）来存取数据。它采用客户端/服务器模型：一个 mysqld 服务进程同时接受多个客户端连接。
「关系型」的意思是数据以二维表组织，表与表之间通过共同的列（键）建立关联，由数据库来保证这种关联的一致性（外键、唯一约束、事务）。

本文所有结论都在本机的 MySQL 9.7.4 上跑过：环境是 WSL2 里的 Ubuntu 26.04.1 LTS，服务端与客户端都是 Oracle 官方 apt 源的 9.7.4（9.7 是 LTS 线，源里的组件名就叫 mysql-9.7-lts），连接走 127.0.0.1:3306。
上手前需要先分清三样东西，很多「我连不上」的问题都是把它们混了：
- 服务器：mysqld 进程，真正存数据、执行 SQL 的那个程序。
- 客户端：mysql、mysqldump、Navicat、Python 的驱动库等，负责发 SQL、收结果。
- 账号：MySQL 自己维护的用户体系，一个用户是「用户名 + 允许的来源主机」的组合，比如 root@localhost 和 root@% 是两个不同账号。

MySQL 特点

开源：社区版按 GNU GPL 发布，可以免费使用和修改；Oracle 同时提供商业授权版本（双许可）。
跨平台：Linux、Windows、macOS 等都能运行。本文实测在 Linux（WSL2）上完成。
存储引擎可插拔：同一套 SQL 接口下可以换底层存储实现，事务、锁粒度、压缩特性由引擎决定（见「MySQL 分类」一节）。
性能与可扩展性：支持大表、分区表、一主多从复制、组复制（Group Replication）与 InnoDB Cluster，读压力和可用性都能横向扩。
安全性：账号按「用户 + 主机」粒度授权，支持 caching_sha2_password 认证、TLS 加密连接、数据脱敏与审计等企业特性。
生态与兼容性：SQL 大体遵循标准，同时有自己的方言；PHP、Python、Java、Go、C++ 等都有成熟驱动。
对开发者友好的数据类型：除传统类型外还支持 JSON（8.0+）与空间类型（GIS），能覆盖大部分场景。
大社区：官方手册、Bug 报告、第三方工具（MySQL Shell、Workbench、Navicat、DBeaver）都很齐全。

MySQL 分类

版本分类
- 社区版（MySQL Community Server）：GPL 开源，功能完整，个人和大多数生产环境用它就够了。
- 企业版（MySQL Enterprise Edition）：商业订阅，额外提供防火墙插件、企业级监控、克隆、脱敏、热备份（MySQL Enterprise Backup）与官方技术支持。
- 另外还有几个衍生分支值得知道：Percona Server（MySQL 的增强替换品）、MariaDB（MySQL 创始人另起的分支，与 MySQL 高度兼容但已分叉）、各发行版自带的 MySQL/MariaDB 打包版。
- 版本号规则：MySQL 9.7 Reference Manual 原文是 The MySQL release model is divided into two main tracks: LTS (Long-Term Support) and Innovation. All LTS and Innovation releases include bug and security fixes, and are considered production-grade quality.，两条线的区别是：
  - LTS：面向「要稳定特性 + 长支持期」的环境，系列内只做必要的修复，不改行为、不删功能（唯一例外是每条 LTS 线的第一个小版本，官方举例 Features can be removed (and added) only in the first LTS release (such as 8.4.0 LTS)）；按 Oracle 生命周期政策给 5 年 premier + 3 年 extended。
  - Innovation：面向要最新特性、能快速跟着升级的环境，会带新功能、重构、删掉已废弃的功能、向 SQL 标准靠拢，因此可能有需要应用配合改的行为变化，而且只支持到下一个 Innovation 版本。
- 9.x 不等于 Innovation —— 9.7 本身就是 LTS。当前两条现役 LTS 线是 8.4 与 9.7，第三方 EOL 表给的终止时间是 8.4 → 2032-04、9.7 → 2034-04（Oracle 官方页只给政策不给具体日期，日期以 EOL 表为参考）。
- 还有一条容易看漏的：9.7 是顺序版本号的最后一条线。官方原文 MySQL 9.7 is the final release line using the previous sequential versioning model，之后的 Innovation 与 LTS 都改用 YY.M 日历版本号（MySQL 26.7 表示 2026 年 7 月发布，27.7 表示 2027 年 7 月），并且「一旦被定为 LTS，它的 YY.M 号在整个支持周期里不再变」。所以现在在 Oracle 源里能同时看到 8.4、9.7、26.7、26.10 这几条线——看到两位数开头的版本号不要以为是笔误。
- 升级路径也是 LTS 语义的一部分：官方写「In-place upgrades and downgrades are supported within the same LTS series」，跨线只能 8.4.x LTS → 9.7.x LTS 这样逐条 LTS 线升，不能跳线；不同 Innovation 大版本之间不能直接原地升级，得先升到最近的 LTS 再跳。
- 这条区分在 apt 源上是有实体体现的。实测 curl -s http://repo.mysql.com/apt/ubuntu/dists/resolute/Release 里那一行 Components: 的全文：
  1
  2
  3
  Components: mysql-apt-config mysql-8.4-lts mysql-innovation mysql-cluster-8.4-lts
  mysql-cluster-innovation mysql-tools mysql-tools-preview mysql-9.7-lts
  mysql-cluster-9.7-lts
  可以看到 Oracle 用组件名把两条线分得很清楚：mysql-8.4-lts、mysql-9.7-lts 是两条现役 LTS，mysql-innovation 才是创新版线。本机 /etc/apt/sources.list.d/mysql.list 里写的是 resolute mysql-9.7-lts，所以 apt 只会给到 9.7 的补丁版本，永远装不到 Innovation 线——想跟创新版要把组件名换成 mysql-innovation。
- 至于「为什么本文用 Oracle 源而不是发行版仓库」：原因不是「Ubuntu 只有 8.0.x」（那是另一个错误说法，实测 Ubuntu 26.04 归档里是 8.4.11-0ubuntu0.26.04.1，resolute/main 还有 8.4.8），而是两条 LTS 线里 9.7 更新、支持期更长，且 Oracle 源的补丁跟进更快。真要保守，装 Ubuntu 仓库的 8.4.11 也完全合规。

存储引擎分类

本机 SHOW ENGINES 的实测结果（11 行）：

引擎	支持状态	事务	说明
InnoDB	DEFAULT	YES	默认引擎，支持事务、行级锁、外键
MyISAM	YES	NO	老默认引擎，只有表锁，读快、不支持事务与外键
MEMORY	YES	NO	数据全在内存，重启即丢，索引默认用 HASH
ARCHIVE	YES	NO	只追加（INSERT）+ 高压缩，适合日志归档
CSV	YES	NO	表就是 .CSV 文件，交换数据用
BLACKHOLE	YES	NO	写入全部丢弃，常用于复制过滤
MRG_MYISAM	YES	NO	把多个 MyISAM 表当一张表查
PERFORMANCE_SCHEMA	YES	NO	不是存业务数据，是存运行指标
ndbcluster	NO	NULL	NDB 集群引擎，未装集群插件时不可用
ndbinfo	NO	NULL	NDB 集群的信息表
FEDERATED	NO	NULL	跨库访问远程表，默认关闭

想确认某张表用什么引擎：SHOW TABLE STATUS 或 SHOW CREATE TABLE 表名。
建表时不写 ENGINE= 就用默认引擎；如果指定了不可用的引擎（比如 ndbcluster），默认会静默退化成 InnoDB，除非 sql_mode 里加了 NO_ENGINE_SUBSTITUTION。本机 sql_mode 默认就含 NO_ENGINE_SUBSTITUTION，所以这种替换会直接报错。

按照部署方式分类
- 本地部署：自己在物理机、虚拟机或容器里装 mysqld。本文就是这种（WSL2 里的 apt 包）。
- 云托管：由云厂商跑，你只管连接串，如 Amazon RDS for MySQL、Google Cloud SQL、Azure Database for MySQL、阿里云 RDS。备份、主从、监控都由平台代管。
按照使用方式分类
- 独立数据库服务：应用通过 TCP/socket 连它，这是绝大多数用法。
- 嵌入式：把服务端链接进自己的进程（历史上的 libmysqld）。MySQL 8.0 起官方已移除嵌入式服务器，新项目别指望这条路。
按照许可证分类
- GPL 开源许可：社区版，免费，但如果你把 MySQL 源码改造后分发衍生作品，要按 GPL 开源。
- 商业许可：企业版订阅，或者需要把 MySQL 嵌入自家闭口产品分发时的商业授权。
- 注意：「GPL 限制」约束的是分发，不是使用。自己装来跑业务不涉及这个问题。

SQL 分类

SQL（Structured Query Language）是操作关系数据库的标准语言。按用途通常分成五类，本文后面每个章节就对应一类：

数据查询语言（DQL）
- SELECT：查询数据。日常写得最多的一类。
数据定义语言（DDL）
- CREATE：创建数据库、表、视图、索引、存储过程。
- ALTER：修改已有对象的结构。
- DROP：删除对象。
- TRUNCATE：清空表的所有行，但保留表结构。
- RENAME：改名。
数据操纵语言（DML）
- INSERT：插入行。
- UPDATE：修改行。
- DELETE：删除行。
数据控制语言（DCL）
- GRANT：授权。
- REVOKE：回收权限。
- 建号与删号用 CREATE USER / DROP USER（严格说它们属于账户管理，很多资料也归进 DCL）。
事务控制语言（TCL）
- COMMIT：把当前事务的改动永久落盘。
- ROLLBACK：撤销当前事务的改动。
- SAVEPOINT / ROLLBACK TO SAVEPOINT / RELEASE SAVEPOINT：事务内部的部分回滚。
- SET TRANSACTION：设置事务特性（如隔离级别）。

两个容易混的点：
- TRUNCATE 归 DDL 而不是 DML，因为它本质是「重建表」，所以不能回滚、不触发行触发器、还会重置 AUTO_INCREMENT。
- DQL 有时被算进 DML 里（有些教材只分四类）。面试时按「五类」说更清楚，顺带说明 SELECT 单列出来是因为它不改数据。
另外还有一批「不是 SQL 标准、但每条命令都要用」的服务器命令：USE、SHOW、DESCRIBE、EXPLAIN、SOURCE，它们不属于上面五类。

MySQL 安装

先说一件容易把人问住的事：打开 MySQL Installer 下载页，Select Version 里最高只有 8.0.46，看不到 8.4 / 9.x。这不是页面没刷新，也不是找到了非官方站——Oracle 从 8.1 起就不再发布 MySQL Installer 了。该页原文（实测照抄）：

Note: MySQL 8.0 is the final series with MySQL Installer. As of MySQL 8.1,
use a MySQL product's MSI or Zip archive for installation. MySQL Server 8.1
and higher also bundle MySQL Configurator, a tool that helps configure
MySQL Server.

也就是说：dev.mysql.com/downloads/installer/ 这个地址是官方的，但它只是那个「图形化向导安装器」的历史终点；8.1 之后要装 Windows 版，得去 MySQL Community Server 的下载页拿 MSI 或 ZIP，配置工具也从 Installer 向导换成了随包附带的 MySQL Configurator。

正确的入口：MySQL Community Server 下载页。实测 Select Version 下拉里这几条并列：26.10.0（Innovation）、26.7.0（Innovation）、9.7.4 LTS、8.4.13 LTS、8.0.46（Legacy GA）——顺带能看出 9.7 与 8.4 都被官方标成 LTS。

Windows 能拿到的包（本机对下载直链做过 HEAD 校验，状态码都是 200，长度照抄）：

文件	大小	说明
`mysql-9.7.4-winx64.msi`	183,066,624 字节（约 175 MB）	9.7 LTS 的单文件 MSI，推荐
`mysql-9.7.4-winx64.zip`	344,052,123 字节（约 328 MB）	免安装的 ZIP，解压后自己初始化
`mysql-8.4.13-winx64.msi`	136,192,000 字节	8.4 LTS
`mysql-26.10.0-winx64.msi`	190,693,376 字节	日历版本号那条 Innovation 线
`mysql-installer-community-8.0.46.0.msi`	593,346,560 字节（页面标 565.9M）	老的完整 Installer，只到 8.0.46
`mysql-installer-web-community-8.0.46.0.msi`	2,248,704 字节（页面标 2.1M）	Installer 的 Web 版（几十 MB 那种说法已过时，实际 2 MB，装时联网现下）

直链规律是 https://dev.mysql.com/get/Downloads/MySQL-<系列>/mysql-<版本>-winx64.msi，实测可用，适合写进脚本或内网镜像同步。
注意 Installer 那两条是 x86 32-bit 的（它是个壳工具），而 Server 的 MSI/ZIP 是 x86 64-bit；老 32 位系统上现在只能走 Installer + 8.0.46 这条路。

下载：按上面的入口取 mysql-9.7.4-winx64.msi（或对应版本的 ZIP）。
安装：双击 MSI，安装类型选 Server only / Client only / Full / Custom；ZIP 版则是解压到如 C:\mysql-9.7.4-winx64，再自己建配置文件。
配置：新版由 MySQL Configurator 承担这一步（Installer 时代是向导内嵌），要定 root 密码、端口（默认 3306）、认证方式（默认 caching_sha2_password，老客户端连不上时才考虑 Legacy）。
服务：安装会注册一个名为 MySQL 的 Windows 服务并设为自启；也可以在「控制面板 → 管理工具 → 服务」里改。ZIP 版要自己注册：mysqld --install MySQL --defaults-file=C:\my.ini。
加 PATH：把 C:\Program Files\MySQL\MySQL Server 9.7\bin 加进系统 Path（目录名跟着版本号走，不是固定的 9.x），之后在任意目录都能敲 mysql。
验证：打开 CMD 或 PowerShell 输入
1
mysql -u root -p
回车后输密码，看到 mysql> 提示符即成功。
Windows 上的两个特有事项：
- 数据目录默认在 C:\ProgramData\MySQL\MySQL Server 9.7\Data，ProgramData 是隐藏目录。
- Windows 默认 lower_case_table_names=1（表名不区分大小写），Linux 默认是 0（区分）。同一套脚本在两个平台迁移时，最容易在表名大小写上翻车。
诚实标注：本节 Windows 部分我只做了官方页面文字核对 + 下载直链 HEAD 校验（大小与状态码如上，都是实测），没有真的在这台机器上跑一遍 MSI 安装；第 2~5 步的目录名与服务名以官方文档和你本机实际安装版本为准。

官方包：在下载页选 macOS 的 .dmg，里面是 .pkg 安装器，逐个装 MySQL Server、Preference Pane、Startup Item。

启动：系统设置里搜 MySQL 面板点 Start，或者命令行

1	sudo launchctl load -w /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist

Homebrew 方式：
1
2
brew install mysql
brew services start mysql
- Homebrew 装的是它自己编译的版本，路径在 /opt/homebrew（Apple Silicon）或 /usr/local（Intel），跟官方 .dmg 包不是一回事，别两个都装。
加 PATH：官方包会把命令放在 /usr/local/mysql/bin，需要手动加进 ~/.zshrc：
1
export PATH=$PATH:/usr/local/mysql/bin
验证：mysql -u root -p，能进 mysql> 即成功。

有两条路：发行版自己的仓库（简单，但只给发行版选定的那条 LTS 线）和 Oracle 官方 apt 源（LTS 与 Innovation 两条线都能选，靠组件名区分）。本机走的是第二条，因为 Ubuntu 26.04 仓库里的 mysql-server 是 8.4.11（apt-cache policy mysql-server 实测候选版本，resolute/main 里还有 8.4.8），而本文要跟的是同样属于 LTS 的 9.7。
- 换句话说这不是「LTS vs 最新版」的取舍，而是「同一条 LTS 政策下选哪条线」：8.4 与 9.7 都是 LTS，9.7 更新且支持到 2034，所以选它。
路线一：发行版仓库
1
2
3
sudo apt update
apt-cache policy mysql-server 2>&1 | head -3
sudo apt install mysql-server
- 实测本机（已加 Oracle 源时）Candidate 显示 9.7.4；临时屏蔽掉 /etc/apt/sources.list.d/mysql.list 再查，Ubuntu 归档给的是 8.4.11-0ubuntu0.26.04.1，另有 8.4.8-0ubuntu1 在 release 库里。
- 装完先跑安全脚本：
  1
  sudo mysql_secure_installation
- 启动与自启：
  1
  2
  sudo systemctl start mysql
  sudo systemctl enable mysql
- 这条路上 sudo mysql 能免密进（Ubuntu 的包用 auth_socket，靠 Unix socket 判断你是 root），跟下面 Oracle 包的行为不一样。

路线二：Oracle 官方源（本文实测路径）

加签名钥。必须用 2025 那份：
1
2
curl -fsSL https://repo.mysql.com/RPM-GPG-KEY-mysql-2025 \
| gpg --dearmor -o /usr/share/keyrings/mysql.gpg
- 用 RPM-GPG-KEY-mysql-2023 会失败：它是同一把钥（指纹 BCA43417C3B485DD128EC6D4B7B3B788A8D3785C），但自签有效期到 2025-10-22 已过，apt update 会报 EXPKEYSIG B7B3B788A8D3785C。

写源文件（resolute 是 Ubuntu 26.04 的代号）：

1 2	echo "deb [signed-by=/usr/share/keyrings/mysql.gpg] http://repo.mysql.com/apt/ubuntu/ resolute mysql-9.7-lts" \ \| sudo tee /etc/apt/sources.list.d/mysql.list

安装：

1 2	sudo apt update sudo apt install -y mysql-community-server

起服务并自启：

1
2
3

sudo systemctl start mysql
sudo systemctl enable mysql
systemctl is-active mysql

active

装完先确认这几件事（本机实测值）：

1 2	mysql --version ss -ltnp \| grep -E ':3306\|:33060'

1
2
3

mysql  Ver 9.7.4 for Linux on x86_64 (MySQL Community Server - GPL)
LISTEN 0  4096   *:3306    0.0.0.0:*   users:(("mysqld",pid=2604,fd=24))
LISTEN 0    70   *:33060   0.0.0.0:*   users:(("mysqld",pid=2604,fd=19))

3306 是传统协议端口，33060 是 X Protocol（mysqlx），给 MySQL Shell 和文档式访问用的。

配置文件位置（Oracle 包）：
1
2
grep -n includedir /etc/mysql/my.cnf
ls /etc/mysql/mysql.conf.d/
1
2
3
5:!includedir /etc/mysql/conf.d/
6:!includedir /etc/mysql/mysql.conf.d/
mysqld.cnf
- /etc/mysql/mysql.conf.d/mysqld.cnf 里放着 datadir、socket、pid-file、log-error；改完要 sudo systemctl restart mysql。
- 服务端读文件的顺序可以用 mysqld --verbose --help | head 看到，本机是 /etc/my.cnf → /etc/mysql/my.cnf → ~/.my.cnf。

一个真实的坑：Oracle 的包不支持 sudo mysql 免密登录，因为 root 账号用的是 caching_sha2_password 而不是 auth_socket：

1	sudo mysql

1	ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

正确姿势是显式带账号密码：mysql -u root -p（回车后再输密码，密码不会留在终端历史里）。

脚本里千万别写 mysql -u root -p"$PW"。这条是实测踩出来的：当 PW 是空串时，shell 会把 -p 和空串拼成一个参数 -p（用 set -- "-p$PW" 打印出来就是 [-p]），而裸 -p 的语义是「请交互式问我密码」，于是命令挂在那里等键盘输入——< /dev/null 喂不进去（它读的是控制终端而不是 stdin），timeout 也要等到 -k 的强杀才收场，看起来就像整个脚本卡死。

1
2
3

PW=""                      # 比如密码文件读不到（权限不够）时被赋空值
mysql -u root -p"$PW" -e "SELECT VERSION();"     # 卡住
mysql -u root --password="$PW" -e "SELECT VERSION();"  # 立刻失败并退出

1 2	mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

差别在于 --password= 是「显式给值」，空值就是「用空密码去连」，服务端直接拒绝、客户端马上退出；-p 不带值才是「问我」。

想避免密码出现在命令行（ps 里能看见，而且每次都会打那句 Using a password on the command line interface can be insecure. 警告），可以用环境变量：
1
MYSQL_PWD="$PW" mysql -u root -e "SELECT VERSION();"
实测同样返回 9.7.4，且不会打印那句 warning。生产上更规范的做法是 mysql_config_editor 建 login-path，或把凭据写进权限 600 的 ~/.my.cnf——这两条本机没有实测，只作为方向提示。

9.x 客户端不再支持 \G，这是本文实测到的一个明显变化：

1	mysql -u root -p -e "USE note_demo; SHOW CREATE TABLE users\G"

1	ERROR at line 1: Unknown command '\G'.

连 \g、\? 一起都不认（伪终端下也一样），只有 DELIMITER 这类不带反斜杠的客户端命令还在。

想竖排输出改用选项：

1	mysql -u root -p -E -e "USE note_demo; SHOW CREATE TABLE users"

*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  ...

或者用 --auto-vertical-output，让结果行数少时自动竖排。

千万别顺手装 MariaDB 的客户端：mariadb-common 与 mysql-community-client/mysql-community-server 冲突，apt install mariadb-client 会把已装好的 MySQL 两个包一起卸掉（数据目录还在，但服务没了），而且它会用 update-alternatives 把 /etc/mysql/my.cnf 指到 mariadb.cnf 上。本机踩过，恢复过程记录在下面的「踩坑与恢复」里。

apt-get remove --yes mariadb-common libmariadb3
dpkg --purge mariadb-common
rm -f /etc/mysql/mariadb.cnf
update-alternatives --remove my.cnf /etc/mysql/mariadb.cnf
sudo apt install --yes mysql-community-client mysql-community-server
sudo systemctl daemon-reload && sudo systemctl start mysql

数据类型

先记住一句总纲：MySQL 9.x 默认 sql_mode 是

1	SELECT @@sql_mode;

+--------------------------------------------------------------------------------------+
| @@sql_mode                                                                           |
+--------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------------------------------------------------+

里面有 STRICT_TRANS_TABLES，所以下面很多「超范围/超长」的写法是直接报错，而不是像老版本那样悄悄截断。本文把报错原文都记下来了，方便你对号入座。
NO_ZERO_DATE + NO_ZERO_IN_DATE 让 '0000-00-00' 这类零日期也进不来。

数字类型

数据类型	大小	用途	有符号范围	无符号范围
TINYINT	1 字节	小整数值	-128 到 127	0 到 255
SMALLINT	2 字节	较小的整数	-32768 到 32767	0 到 65535
MEDIUMINT	3 字节	中等大小的整数	-8388608 到 8388607	0 到 16777215
INT/INTEGER	4 字节	标准整数	-2147483648 到 2147483647	0 到 4294967295
BIGINT	8 字节	大整数	-9223372036854775808 到 9223372036854775807	0 到 18446744073709551615
FLOAT	4 字节	单精度浮点（近似值）	约 ±1.18E-38 到 ±3.40E+38	同有符号
DOUBLE	8 字节	双精度浮点（近似值）	约 ±2.23E-308 到 ±1.80E+308	同有符号
DECIMAL/NUMERIC	变长（最多 65 位精度）	定点数，精确小数，钱算这个	由精度决定	由精度决定

越界会怎样，本机实测（表 ints(t TINYINT, s SMALLINT, ...)）：

1	INSERT INTO ints VALUES (128, 0, 0, 0, 0);

1	ERROR 1264 (22003) at line 7: Out of range value for column 't' at row 1

1	INSERT INTO ints2 VALUES (-1); -- ints2.t 是 TINYINT UNSIGNED

1	ERROR 1264 (22003) at line 11: Out of range value for column 't' at row 1

UNSIGNED 把下界从负数挪到 0，上界翻倍；TINYINT UNSIGNED 就是 0～255。

显示宽度已经没用了：INT(11) 里的 11 只影响「补零显示」，不改变存储范围。MySQL 8.0.17 起废弃，本机实测建表会出告警，SHOW CREATE TABLE 里也被抹平：

1 2	CREATE TABLE widths (a INT(11), b INT UNSIGNED ZEROFILL); SHOW CREATE TABLE widths;

Query OK, 0 rows affected, 2 warnings (0.053 sec)

CREATE TABLE `widths` (
  `a` int DEFAULT NULL,
  `b` int(10) unsigned zerofill DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

a 的 (11) 直接没了；b 因为带 ZEROFILL 才保留 (10)。新代码别写 int(11)。

FLOAT(M,D) / DOUBLE(M,D) 这种「精度+标度」写法同样被废弃（实测 2 warnings），而 FLOAT(10) 这种只给一个数的写法不再有意义。要精确小数就用 DECIMAL。
DECIMAL(M,D)：M 是总位数（最大 65），D 是小数位。超精度直接报错：
1
2
CREATE TABLE dmax (x DECIMAL(65,30)); -- 成功
CREATE TABLE dbad (x DECIMAL(66,30)); -- 失败
1
ERROR 1426 (42000) at line 1: Too-big precision 66 specified for 'x'. Maximum is 65.
- 反过来，值超出列的容量也报错：DECIMAL(6,2) 存 12345.678 → ERROR 1264 Out of range value for column 'd'。

浮点是近似值这件事，实测最直观：

1
2
3

CREATE TABLE nums (d DECIMAL(6,2), f FLOAT, g DOUBLE);
INSERT INTO nums VALUES (9999.99, 1.0000000000000002, 1.0000000000000002);
SELECT d, f, g FROM nums;

+---------+------+------+
| d       | f    | g    |
+---------+------+------+
| 9999.99 |    1 |    1 |
+---------+------+------+

那个 1.0000000000000002 在 FLOAT/DOUBLE 里都变成了 1。所以「用 = 比较两个浮点计算结果」是不可靠的写法。

BIT(N)、BOOL/BOOLEAN：

1
2
3

CREATE TABLE flags (b BIT(3), flag BOOL, sw BOOLEAN);
INSERT INTO flags VALUES (b'101', 1, 0);
SELECT b+0, flag, sw FROM flags;

+-----+------+----+
| b+0 | flag | sw |
+-----+------+----+
|   5 |    1 |  0 |
+-----+------+----+

BIT 直接 SELECT 会显示成不可见的二进制字节，所以要 +0 或 CAST(b AS UNSIGNED) 才看得到 5。
BOOL/BOOLEAN 只是 TINYINT(1) 的同义词，存的是 1/0，不是真布尔。

日期和时间类型

数据类型	格式	用途	范围
DATE	`YYYY-MM-DD`	只表示日期	`1000-01-01` 到 `9999-12-31`
TIME	`HH:MM:SS[.fraction]`	一天中的时刻或时间间隔	`-838:59:59` 到 `838:59:59`
DATETIME	`YYYY-MM-DD HH:MM:SS[.fraction]`	日期+时间，不带时区	`1000-01-01 00:00:00` 到 `9999-12-31 23:59:59`
TIMESTAMP	同 DATETIME	时间点，会按时区换算	`1970-01-01 00:00:01` UTC 到 `2038-01-19 03:14:07` UTC
YEAR	`YYYY`	只存年份	`1901` 到 `2155`

基本写入与越界，实测：

CREATE TABLE times (d DATE, dt DATETIME, ts TIMESTAMP, tm TIME, y YEAR);
INSERT INTO times VALUES ('2026-11-03','2026-11-03 12:34:56','2026-11-03 12:34:56','12:34:56',2026);
SELECT * FROM times;
INSERT INTO times (d) VALUES ('2026-11-03 25:00:00');
INSERT INTO times (ts) VALUES ('1969-01-01 00:00:00');

+------------+---------------------+---------------------+----------+------+
| d          | dt                  | ts                  | tm       | y    |
+------------+---------------------+---------------------+----------+------+
| 2026-11-03 | 2026-11-03 12:34:56 | 2026-11-03 12:34:56 | 12:34:56 | 2026 |
+------------+---------------------+---------------------+----------+------+
ERROR 1292 (22007) at line 4: Incorrect date value: '2026-11-03 25:00:00' for column 'd' at row 1
ERROR 1292 (22007) at line 5: Incorrect datetime value: '1969-01-01 00:00:00' for column 'ts' at row 1

第二条报错就是 TIMESTAMP 下界（1970）挡住了 1969 年，DATETIME 则没这个问题。

TIMESTAMP 与 DATETIME 的本质差别（本机实测，同一个会话里改时区）：

SET time_zone = '+00:00';
INSERT INTO times (d, dt, ts) VALUES ('2026-11-03','2026-11-03 12:00:00','2026-11-03 12:00:00');
SET time_zone = '+08:00';
SELECT dt, ts FROM times WHERE d = '2026-11-03';

+---------------------+---------------------+
| dt                  | ts                  |
+---------------------+---------------------+
| 2026-11-03 12:00:00 | 2026-11-03 20:00:00 |
+---------------------+---------------------+

同一条记录：dt 原样不变（存的就是那串字面时间），ts 从 12:00 变成 20:00——因为 TIMESTAMP 内部存的是 UTC 秒数，读的时候按当前会话时区换算回来。
结论：跨时区协作、需要「绝对时刻」的字段用 TIMESTAMP；记录「当地几点发生的事」、或需要超出 2038 年的日期，用 DATETIME。

零日期被默认 sql_mode 拒绝：

1	INSERT INTO zd VALUES ('0000-00-00');

1	ERROR 1292 (22007) at line 1: Incorrect date value: '0000-00-00' for column 'd' at row 1

YEAR 与 TIME 的边界，实测：

1 2	INSERT INTO yr VALUES (1901),(2155),(1900),(2156); INSERT INTO tt VALUES ('-838:59:59'),('838:59:59'),('839:00:00');

1 2	ERROR 1264 (22003) at line 1: Out of range value for column 'y' at row 3 ERROR 1292 (22007) at line 1: Incorrect time value: '839:00:00' for column 't' at row 3

TIME 允许超过 24 小时甚至负数，所以它能表示「耗时 50 小时」这种间隔，这是它和 DATETIME 的分工。

字符串类型

数据类型	大小上限	用途和特征
CHAR(n)	0～255 字符	定长，短且长度整齐时用（如国家码、MD5）
VARCHAR(n)	0～65535 字节（整行共享这个额度）	变长，最常用的文本类型
TINYTEXT	255 字节	极短文本
TEXT	65,535 字节	长文本，如文章正文
MEDIUMTEXT	16,777,215 字节	更长文本
LONGTEXT	4,294,967,295 字节	极大文本
BINARY(n)	0～255 字节	定长二进制
VARBINARY(n)	0～65535 字节	变长二进制
TINYBLOB / BLOB / MEDIUMBLOB / LONGBLOB	255 / 64K / 16M / 4G 字节	二进制大对象
ENUM	最多 65535 个成员	单选：值必须是列出的之一
SET	最多 64 个成员	多选：可以是若干成员的组合

「65535」是整行的上限，不是单列的，而且按字节算。实测两个 VARCHAR(16383) CHARACTER SET utf8mb4（各占 65532 字节）放一张表就爆：

1 2	CREATE TABLE vbig (a VARCHAR(16383) CHARACTER SET utf8mb4, b VARCHAR(16383) CHARACTER SET utf8mb4); CREATE TABLE vbig2 (a VARCHAR(16384) CHARACTER SET utf8mb4);

1
2

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
ERROR 1074 (42000): Column length too big for column 'a' (max = 16383); use BLOB or TEXT instead

utf8mb4 一个字符最多 4 字节，所以 VARCHAR 声明的最大字符数就是 65535 / 4 = 16383。这就是「为什么 VARCHAR(30000) 建不起来」。

超长写入直接报错，不会截断：
1
2
CREATE TABLE strs (c CHAR(5), v VARCHAR(5), t TEXT);
INSERT INTO strs VALUES ('abcdef','abcdef',REPEAT('x',70000));
1
ERROR 1406 (22001) at line 40: Data too long for column 'c' at row 1
- TEXT 列收下了 70000 字节（LENGTH(t) 实测 70000），因为 TEXT 上限是 65535 字节、而行内长度限制对 BLOB/TEXT 不计入 65535 总额。
CHAR 会右补空格、取用时去掉尾部空格；VARCHAR 原样保留。所以 CHAR(5) 存 'ab ' 取出来是 'ab'。

ENUM / SET 的约束与报错：

CREATE TABLE e (size ENUM('S','M','L'), tags SET('a','b','c'));
INSERT INTO e VALUES ('M','a,c');
INSERT INTO e VALUES ('XL','a,d');
SELECT * FROM e;

ERROR 1265 (01000) at line 48: Data truncated for column 'size' at row 1
+------+--------+
| size | tags   |
+------+--------+
| M    | a,c    |
+------+--------+

ENUM 存的是 1/2 字节的序号而不是字符串，所以排序按定义顺序而不是字母顺序，ORDER BY size 得到的是 S、M、L。
成员数超过 64 个 SET 就装不下了；这两类字段以后要加值时得 ALTER TABLE ... MODIFY，属于线上变更，慎用。

空间类型

数据类型	描述
GEOMETRY	所有几何类型的超类型
POINT	单个点
LINESTRING	依次连接的点串
POLYGON	闭合区域
MULTIPOINT / MULTILINESTRING / MULTIPOLYGON	多个点 / 多条线 / 多个多边形
GEOMETRYCOLLECTION	上述对象的集合

读写用 WKT（文本形式）与函数互转，实测：

1
2
3

CREATE TABLE sp (p POINT);
INSERT INTO sp VALUES (ST_GeomFromText('POINT(1 2)'));
SELECT ST_AsText(p) FROM sp;

+--------------+
| ST_AsText(p) |
+--------------+
| POINT(1 2)   |
+--------------+

底层存储是 WKB（Well-Known Binary），文本形式是 WKT，两者靠 ST_GeomFromText() / ST_AsText() 转换。
建空间索引有两个硬性要求，实测踩到：列必须 NOT NULL，否则报 ERROR 1252: All parts of a SPATIAL index must be NOT NULL；改列时若已有 NULL 值，先报 ERROR 1138: Invalid use of NULL value，得把数据补齐再 ALTER。

列没声明 SRID 时，MySQL 会给出建议：

1 2	Warning 3674: The spatial index on column 'loc' will not be used by the query optimizer since the column does not have an SRID attribute. Consider adding an SRID attribute to the column.

也就是说：光建 SPATIAL INDEX 还不够，要真正被优化器用上，得写成 POINT SRID 4326。

空间类型在 InnoDB 上完全可用（INDEX_TYPE 实测为 SPATIAL），原笔记「主要由 MyISAM 支持」是 5.7 之前的情况。

JSON 类型

数据类型存储需求用途和特性

JSON 变长存 JSON 文档；写入时自动校验格式，内部按二进制解析存储，取键值不用重新解析文本
- 实测读写：
  1
  2
  3
  CREATE TABLE j (doc JSON);
  INSERT INTO j VALUES ('{"name":"Alice","age":30,"tags":["x","y"]}');
  SELECT doc->>'$.name' AS name, JSON_TYPE(doc) AS t, JSON_LENGTH(doc) AS n FROM j;
  1
  2
  3
  4
  5
  +-------+--------+---+
  | name | t | n |
  +-------+--------+---+
  | Alice | OBJECT | 3 |
  +-------+--------+---+
  - -> 取出来还是 JSON 值（带引号），->> 相当于 JSON_UNQUOTE(JSON_EXTRACT(...))，取出来是普通字符串。
- 非法 JSON 会被拒（ERROR 3140 The JSON document contains an error...），所以它天然是个校验器。
- 想给 JSON 里的某个键建索引，标准做法是「生成列 + 索引」：
  1
  2
  3
  ALTER TABLE j ADD name_v VARCHAR(50)
  GENERATED ALWAYS AS (doc->>'$.name') VIRTUAL,
  ADD INDEX idx_name (name_v);
  - 原笔记说「JSON 索引利用虚拟列技术」，指的正是这个写法——MySQL 没有直接对 JSON 路径建索引的语法。

字段修饰

NOT NULL：该列不许为空，插入时必须给值。
DEFAULT：默认值。插入时不给这列就用默认值。
AUTO_INCREMENT：整数列专用，插入时自动给一个递增的唯一值，通常配主键。
PRIMARY KEY：主键，一张表只能有一个，值唯一且不许 NULL。
UNIQUE：唯一约束，允许多个 NULL（见「索引」一节实测）。
INDEX / KEY：普通索引，两者同义。
FULLTEXT：全文索引，只能建在 CHAR/VARCHAR/TEXT 上。
COMMENT：给列或表加注释，会存进 information_schema。
CHARACTER SET：指定列的字符集。
COLLATE：指定列的排序规则。
FOREIGN KEY：外键，引用另一张表的主键，用来保证引用完整性。
UNSIGNED：无符号，只能存 0 和正数，上界翻倍。
ZEROFILL：用 0 补齐显示宽度，并且会自动带上 UNSIGNED。这个也已经废弃（实测建表出告警，见上面 int(10) unsigned zerofill）。

综合示例（本机实测建表成功）：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
    password VARCHAR(50) NOT NULL COMMENT '用户密码',
    email VARCHAR(100) DEFAULT 'user@example.com' COMMENT '用户邮箱',
    signup_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间'
);

SHOW CREATE TABLE users 的实际结果：

CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `username` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户名',
  `password` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户密码',
  `email` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT 'user@example.com' COMMENT '用户邮箱',
  `signup_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  KEY `idx_composite` (`username`,`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

三个细节：列上的 UNIQUE 变成了独立的 UNIQUE KEY username；username 没写 NOT NULL 的表会补上字符集相关的 COLLATE；signup_date 实际是 NULL DEFAULT CURRENT_TIMESTAMP（TIMESTAMP 的默认值行为由 explicit_defaults_for_timestamp 决定）。

AUTO_INCREMENT 的真实行为，实测：

CREATE TABLE ai (id INT AUTO_INCREMENT PRIMARY KEY, x VARCHAR(10));
INSERT INTO ai (x) VALUES ('a'),('b');
SELECT LAST_INSERT_ID();
INSERT INTO ai (id, x) VALUES (100, 'c');
INSERT INTO ai (x) VALUES ('d');
SELECT id, x FROM ai;

+-----------------+
| LAST_INSERT_ID() |
+-----------------+
|               1 |
+-----------------+
+-----+---+
| id  | x |
+-----+---+
|   1 | a |
|   2 | b |
| 100 | c |
| 101 | d |
+-----+---+

一次插入多行时 LAST_INSERT_ID() 返回的是这批里第一个自增值（1），不是最后一行。
手工插一个更大的 id（100）之后，计数器立刻抬到 101，所以别以为自增是「按行数递增」。

字符集及存储引擎

字符集（Character Set）

字符集决定「字符 ↔ 字节」怎么映射，排序规则（collation）决定「怎么比大小、要不要区分大小写和重音」。

utf8 这个名字在 MySQL 9.7 里已经查不到了，实测：

1	SHOW CHARACTER SET WHERE Charset IN ('utf8','utf8mb3','utf8mb4','latin1');

+---------+----------------------+--------------------+--------+
| Charset | Description          | Default collation  | Maxlen |
+---------+----------------------+--------------------+--------+
| latin1  | cp1252 West European | latin1_swedish_ci  |      1 |
| utf8mb3 | UTF-8 Unicode        | utf8mb3_general_ci |      3 |
| utf8mb4 | UTF-8 Unicode        | utf8mb4_0900_ai_ci |      4 |
+---------+----------------------+--------------------+--------+

只剩 utf8mb3（每字符最多 3 字节）和 utf8mb4（最多 4 字节）。老脚本里写的 utf8 是 utf8mb3 的别名，8.0 起就建议别用了。

utf8mb3 存不下 4 字节字符，这才是它和 utf8mb4 的真正差别（不是「存不下中文」）：

1
2
3

CREATE TABLE cs_a (s VARCHAR(20) CHARACTER SET utf8mb3);
CREATE TABLE cs_b (s VARCHAR(20) CHARACTER SET utf8mb4);
-- 分别写入「中文」和 emoji 🐡

cs_a  写入 中文   -> OK   字符数=2 字节数=6
cs_a  写入 🐡     -> DataError (1366, "Incorrect string value: '\xF0\x9F\x90\xA1' for column 's' at row 1")
cs_b  写入 中文   -> OK   字符数=2 字节数=6
cs_b  写入 🐡     -> OK   字符数=1 字节数=4

常用汉字都在 3 字节区，所以 utf8mb3 也能存中文；但 emoji、生僻字（CJK 扩展 B 区）是 4 字节，只有 utf8mb4 收得下。

排序规则实测对比（本机可用的四种）：

1 2	SHOW COLLATION WHERE Charset='utf8mb4' AND Collation IN ('utf8mb4_general_ci','utf8mb4_unicode_ci','utf8mb4_0900_ai_ci','utf8mb4_bin');

+--------------------+---------+-----+---------+----------+---------+---------------+
| Collation          | Charset | Id  | Default | Compiled | Sortlen | Pad_attribute |
+--------------------+---------+-----+---------+----------+---------+---------------+
| utf8mb4_0900_ai_ci | utf8mb4 | 255 | Yes     | Yes      |       0 | NO PAD        |
| utf8mb4_bin        | utf8mb4 |  46 |         | Yes      |       1 | PAD SPACE     |
| utf8mb4_general_ci | utf8mb4 |  45 |         | Yes      |       1 | PAD SPACE     |
| utf8mb4_unicode_ci | utf8mb4 | 224 |         | Yes      |       8 | PAD SPACE     |
+--------------------+---------+-----+---------+----------+---------+---------------+

_ci 结尾表示 case insensitive（不区分大小写），_bin 按字节比较（区分大小写）。
服务端默认是 utf8mb4_0900_ai_ci（基于 Unicode 9.0 算法，ai = accent insensitive），比 utf8mb4_general_ci 排序更准、比 unicode_ci 更快。

注意 NO PAD 与 PAD SPACE 的差别：0900_ai_ci 不补空格，尾部空格会参与比较。实测同一条等式换两种规则结果相反：

SELECT CAST('abc' AS CHAR(10)) COLLATE utf8mb4_general_ci
     = CAST('abc  ' AS CHAR(10)) COLLATE utf8mb4_general_ci AS pad_space_eq,
       CAST('abc' AS CHAR(10)) COLLATE utf8mb4_0900_ai_ci
     = CAST('abc  ' AS CHAR(10)) COLLATE utf8mb4_0900_ai_ci AS no_pad_eq;

+--------------+-----------+
| pad_space_eq | no_pad_eq |
+--------------+-----------+
|            1 |         0 |
+--------------+-----------+

也就是说 'abc' = 'abc ' 在 general_ci（PAD SPACE）下为真，在 0900_ai_ci（NO PAD）下为假。从老版本迁上来时这类「相等判断突然不成立」很难查。

中文排序结果取决于排序规则，实测 utf8mb4_unicode_ci 下：

CREATE TABLE zh (id INT PRIMARY KEY, w VARCHAR(20))
    CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
INSERT INTO zh VALUES (1,'啊'),(2,'爱'),(3,'八'),(4,'张');
SELECT w FROM zh ORDER BY w;

+------+
| w    |
+------+
| 八   |
| 啊   |
| 张   |
| 爱   |
+------+

顺序是「八啊张爱」，也就是按 Unicode 码点，不是按拼音。想按拼音排序要用中文专用排序规则，本机实测它存在：

1	SHOW COLLATION WHERE Collation LIKE '%zh%';

+-----------------------+---------+-----+---------+----------+---------+---------------+
| Collation             | Charset | Id  | Default | Compiled | Sortlen | Pad_attribute |
+-----------------------+---------+-----+---------+----------+---------+---------------+
| utf8mb4_zh_0900_as_cs | utf8mb4 | 308 |         | Yes      |       0 | NO PAD        |
+-----------------------+---------+-----+---------+----------+---------+---------------+

名字里 as = accent sensitive（区分声调）、cs = case sensitive（区分大小写）。把列的 COLLATE 换成它，ORDER BY 才按拼音排。

指定字符集的写法（实测可用）：

CREATE DATABASE mydb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE mytable (
    id INT PRIMARY KEY,
    text VARCHAR(100)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

优先级是：列 > 表 > 库 > 服务器。库/表上写的会作为「下一层的默认」继续往下传。

列上单独写的 COLLATE 如果和表默认字符集不同，就会出现「一张表里混着两种字符集」，实测原笔记那段 DDL 就是这样：

CREATE TABLE `users` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `email` varchar(255) COLLATE utf8_bin NOT NULL,
    `password` varchar(255) COLLATE utf8_bin NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1;

1
2
3

SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA='py_demo' AND TABLE_NAME='users';

1 2	('email', 'utf8mb3', 'utf8mb3_bin') ('password', 'utf8mb3', 'utf8mb3_bin')

它建表能成功，但两列实际是 utf8mb3，表默认却是 utf8mb4。以后往这两列写 emoji 就会报 1366。改成 utf8mb4_bin 才是你想要的。

存储引擎（Storage Engine）
- InnoDB：默认。支持事务（ACID）、行级锁、外键、崩溃恢复，还有聚簇索引（主键就是数据本身）。绝大多数场景选它。
- MyISAM：老默认引擎。只有表锁、不支持事务和外键，全文索引早期靠它；读多写极少的老系统才用。
- Memory：数据放内存，重启就没了；默认用 HASH 索引，等值查询极快，适合临时表和字典缓存。
- Archive：只允许 INSERT 和顺序 SELECT，高压缩，适合日志归档。
- CSV / BLACKHOLE / MRG_MYISAM：分别是「表就是 CSV 文件」「写入全部丢弃」「多张 MyISAM 拼成一张」。
- 指定引擎（实测）：
  1
  2
  3
  4
  CREATE TABLE mytable2 (
  id INT PRIMARY KEY,
  value VARCHAR(100)
  ) ENGINE = InnoDB;
- 引擎不可用时的行为由 sql_mode 决定，实测两种结果：
  1
  CREATE TABLE eng_test (id INT) ENGINE=NDB;
  1
  ERROR 1286 (42000): Unknown storage engine 'NDB'
  1
  2
  3
  SET sql_mode='';
  CREATE TABLE eng_test2 (id INT) ENGINE=NDB;
  SHOW WARNINGS;
  1
  2
  3
  Query OK, 0 rows affected, 2 warnings
  Warning 1286 Unknown storage engine 'NDB'
  Warning 1266 Using storage engine InnoDB for table 'eng_test2'
  - 默认 sql_mode 含 NO_ENGINE_SUBSTITUTION，所以是报错；把它去掉就退化成「悄悄换成 InnoDB」，这类静默替换在排查「为什么我的表不支持事务」时很坑，建议保持默认。
- 改已有表的引擎：
  1
  ALTER TABLE Employees ENGINE = InnoDB;

索引

简介
- 索引是帮 MySQL 快速定位数据的辅助结构，好比书的目录：不用逐页翻，先看目录找到页码。
- 但索引不是免费的：它占磁盘，而且每次 INSERT/UPDATE/DELETE 都要顺带维护索引，所以写性能会下降。
- 索引是否真的被用上，用 EXPLAIN 看，本机实测：
  1
  EXPLAIN SELECT * FROM idx_demo WHERE email = 'a@x';
  1
  -> Index lookup on idx_demo using idx_email (email = 'a@x') (cost=0.35 rows=1)
  - MySQL 8.0.18 起 EXPLAIN 默认输出这种「树形」格式，比老版那一大堆列好读：using idx_email 说明走了索引，rows=1 是预估扫描行数。

分类

INDEX（普通索引）
- 最基本的索引，没有任何约束，只为加速查询。
- 同一列允许多行有相同值，一张表也可以建多个普通索引。
UNIQUE（唯一索引）
- 要求索引列的值唯一，插重复值会报错。
- 唯一性约束和加速都能拿到，业务上「手机号、邮箱不许重复」就用它。
- NULL 不受唯一性约束，所以一列里可以有多个 NULL。实测：
  1
  2
  INSERT INTO idx_demo (username, email) VALUES (NULL,'a@x'),(NULL,'b@x'),('c@x','c@x');
  INSERT INTO idx_demo (username) VALUES ('c@x');
  1
  2
  Query OK, 3 rows affected -- 两个 NULL 都进去了
  ERROR 1062 (23000): Duplicate entry 'c@x' for key 'idx_demo.uq_username'
  - 报错里给的是 表名.索引名，这是 8.0 之后的格式，老版本只写索引名。
  - 顺带一个实用结论：想用唯一索引做「可空字段不许重复」，得配合 NOT NULL，否则 NULL 之间不算重复。
PRIMARY KEY（主键索引）
- 特殊的唯一索引，一张表只能有一个，列不许 NULL。
- InnoDB 里主键是聚簇索引：叶子节点直接放整行数据，所以主键选得小、选得递增，二级索引才更省空间（每个二级索引的叶子存的是主键值）。
- 建议用 INT/BIGINT AUTO_INCREMENT 或单调递增的 ID 做主键，别用 UUID 或随机字符串——插入会到处分裂页。

FULLTEXT（全文索引）

用于自然语言搜索，能建在 CHAR、VARCHAR、TEXT 列上。

InnoDB 从 5.6 起就支持全文索引，原笔记「主要由 MyISAM 支持」已经过时。实测：

CREATE FULLTEXT INDEX ft_bio ON idx_demo (bio);
UPDATE idx_demo SET bio='MySQL database management system' WHERE id=1;
UPDATE idx_demo SET bio='NoSQL databases such as MongoDB' WHERE id=2;
SELECT id, bio FROM idx_demo WHERE MATCH(bio) AGAINST('MySQL');
SELECT id, bio FROM idx_demo WHERE MATCH(bio) AGAINST('database' IN NATURAL LANGUAGE MODE);

+----+----------------------------------+
| id | bio                              |
+----+----------------------------------+
|  1 | MySQL database management system |
+----+----------------------------------+
+----+----------------------------------+
| id | bio                              |
+----+----------------------------------+
|  1 | MySQL database management system |
+----+----------------------------------+

MATCH(...) AGAINST(...) 必须配套使用，且列上的全文索引要包含 MATCH 里的全部列，否则报 ERROR 1191: Can't find FULLTEXT index matching the column list。
默认是自然语言模式（按相关度排序）；要「必须包含某词」用布尔模式 AGAINST('+MySQL -NoSQL' IN BOOLEAN MODE)。
默认分词按空格和标点，中文需要 ngram 解析器（WITH PARSER ngram），否则一整句被当成一个词，搜不出来。

B-Tree 索引
- 最常见的类型，支持等值、范围、前缀匹配和排序。InnoDB、MyISAM 的默认都是它。
- USING BTREE 可以显式写，实测语法是「索引名 USING BTREE 表名(列)」：
  1
  CREATE INDEX idx_email_bt ON idx_demo (email) USING BTREE;
  1
  Query OK, 0 rows affected, 1 warning
  - 有告警是因为本机这张表上 email 已经有 idx_email 索引了（见下面「重复索引」），跟 USING BTREE 无关。
HASH 索引
- 基于哈希表，只做等值比较（=、<=>、IN()），不支持范围（>、<、BETWEEN）和排序，因此也帮不上 ORDER BY。
- 只有 MEMORY 引擎能显式建 HASH 索引，实测：
  1
  2
  CREATE TABLE mem_t (id INT, k VARCHAR(20), INDEX USING HASH (k)) ENGINE=MEMORY;
  SHOW INDEX FROM mem_t;
  1
  2
  Table Non_unique Key_name Seq_in_index Column_name Index_type
  mem_t 1 k 1 k HASH
- 在 InnoDB 上写 USING HASH 不报错但也没用，实测告警原文：
  1
  Note 3502 This storage engine does not support the HASH index algorithm, storage engine default was used instead.
  随后查 information_schema.STATISTICS，INDEX_TYPE 是 BTREE。
- 另外 InnoDB 有个「自适应哈希索引」（innodb_adaptive_hash_index），是引擎对热点等值查询自己按需建的，不需要也不能由你手工指定。本机实测该变量默认是 0（关闭）——8.4 起 Oracle 就把它默认关了，因为高并发下它可能反而成为争用点。
R-Tree（空间索引）
- 给 GEOMETRY 类列用的，INDEX_TYPE 显示为 SPATIAL。
- InnoDB 支持，实测建成功；前提是列必须 NOT NULL，且建议带 SRID（否则有 3674 告警，见「空间类型」一节）。
Composite（复合索引）
- 一个索引覆盖多个列，遵循最左前缀原则：(a,b,c) 能服务 a、a,b、a,b,c 形式的条件，但单靠 b 或 c 用不上。
- 列的顺序很重要：把「等值查询的列」放前面，「范围查询或排序的列」放后面。

示例一

CREATE TABLE idx_demo (
    id INT AUTO_INCREMENT,
    username VARCHAR(50),
    email VARCHAR(100),
    PRIMARY KEY (id),                          -- 主键索引
    UNIQUE INDEX uq_username (username),       -- 唯一索引
    INDEX idx_email (email)                    -- 普通索引
);

id 是主键，自动获得唯一且非空的聚簇索引，每行靠它区分。
username 上有唯一索引，用户名不许重复（但可以有多个 NULL）。
email 上是普通索引，只加速查询，不要求唯一。

建完查一下。下面这张表 idx_clean 就是上面那段 DDL 建出来的、插了 3 行之后 SHOW INDEX FROM idx_clean 的真实输出：

+-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table     | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| idx_clean |          0 | PRIMARY     |            1 | id          | A         |           3 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| idx_clean |          0 | uq_username |            1 | username    | A         |           3 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| idx_clean |          1 | idx_email   |            1 | email       | A         |           3 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

Non_unique 为 0 表示唯一；Null 为 YES 表示这列允许空值（注意主键那行是空的，因为主键不许空）。
Cardinality 是「不同值的大致数量」，由统计信息估算，长期不准会让优化器选错索引，用 ANALYZE TABLE 刷新。

索引太多时这张表会很长，可以只看关键列：

SELECT INDEX_NAME, COLUMN_NAME, SEQ_IN_INDEX, NON_UNIQUE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'note_demo' AND TABLE_NAME = 'idx_clean'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

示例二

-- 单列索引
CREATE INDEX idx_email ON idx_demo (email);

-- 复合索引
CREATE INDEX idx_user_email ON idx_demo (username, email);

-- 全文索引
CREATE FULLTEXT INDEX ft_bio ON idx_demo (bio);

-- 显式指定 B-Tree
CREATE INDEX idx_email_bt ON idx_demo (email) USING BTREE;

-- 前缀索引：只索引前若干字符，省空间
CREATE INDEX idx_email_prefix ON idx_demo (email(10));

前缀索引实测能建（email(10)），代价是不能再用于 ORDER BY，且区分度取决于前 10 个字符。
删索引有两种写法，实测都可用：
1
2
ALTER TABLE idx_demo DROP INDEX idx_email_prefix;
DROP INDEX idx_email ON idx_demo;
- DROP INDEX 索引名 ON 表名 是 MySQL 特有的方言，标准 SQL 没有这个形式。

注意事项

索引占磁盘空间，用 SHOW TABLE STATUS LIKE '表名' 看 Index_length。实测那张 3 行、3 个索引的 idx_clean：

+-----------+--------+------------+------+----------------+-------------+--------------+----------------+
| Name      | Engine | Row_format | Rows | Avg_row_length | Data_length | Index_length | Auto_increment |
+-----------+--------+------------+------+----------------+-------------+--------------+----------------+
| idx_clean | InnoDB | Dynamic    |    3 |           5461 |       16384 |        32768 |              4 |
+-----------+--------+------------+------+----------------+-------------+--------------+----------------+

3 行数据占了 16KB（一个 InnoDB 页），两个二级索引又占了 32KB——索引比数据还大，这就是「别乱建索引」的量化依据。
Auto_increment 是 4，也就是下一个自增值；Rows 与 Avg_row_length 都是估算值，InnoDB 的统计不是精确数。

索引加快读，但拖慢写：每次改动数据都要同步更新所有相关索引。
重复索引会被明确警告，实测：
1
2
Warning 1831 Duplicate index 'idx_hash2' defined on the table 'note_demo.idx_demo'.
This is deprecated and will be disallowed in a future release.
- 常见成因是「主键/外键已经自动建了索引，你又手工建一个同列的」。比如给外键列再建一个普通索引，多半是多余的，先 SHOW INDEX 看一眼再动手。
不是列越多索引越好：区分度低（比如性别）的列单独建索引通常没用，优化器宁可全表扫。
WHERE 里对索引列做函数运算、隐式类型转换、用 LIKE '%xx' 前导通配，都会让索引失效。
定期用 EXPLAIN 验证关键查询真的走了索引；ANALYZE TABLE 刷新统计信息；确认不用的索引该删就删。

数据定义语言(DDL)

简介
- DDL 负责「结构」：建、改、删数据库/表/视图/索引/存储过程。它操作的是对象定义，不是行数据。
- 一条最重要的规则：DDL 会隐式提交当前事务。实测在一个事务里执行 CREATE TABLE 之后再 ROLLBACK，前面那条 DELETE 已经落盘、撤不回来了（详见「事务控制语言(TCL)」一节）。

CREATE

建库与选库
1
2
CREATE DATABASE ExampleDatabase;
USE ExampleDatabase;
- USE 只是把当前会话的默认库切过去，不影响权限；跨库查询随时可以写 库名.表名。
- 重复执行 CREATE DATABASE 会报 ERROR 1007 (HY000): Can't create database 'ExampleDatabase'; database exists，加 IF NOT EXISTS 就变成一条告警。
- 建库时最好显式写字符集，否则继承服务器默认（本机是 utf8mb4 / utf8mb4_0900_ai_ci）：
  1
  CREATE DATABASE mydb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

建表

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    BirthDate DATE,
    Position VARCHAR(50)
);

这张表后面会反复用到，注意 EmployeeID 是 INT PRIMARY KEY 而没有 AUTO_INCREMENT——所以插入时必须自己给 id，否则报错（实测见下面的存储过程一例）。

表名在 Linux 上区分大小写：本机 @@lower_case_table_names = 0，实测同一个库里 Employees 和 employees 是两张不同的表：

1	SHOW TABLES LIKE '%mploy%';

+-------------------------------+
| Tables_in_note_demo (%mploy%) |
+-------------------------------+
| Employees                     |
| employees                     |
+-------------------------------+

Windows 上默认 lower_case_table_names=1（不区分），所以「在 Windows 写好的脚本搬到 Linux 就找不到表」多半是这个原因。

建索引

1	CREATE INDEX idx_lastname ON Employees (LastName);

建视图

视图是一张「虚拟表」，本体是一段保存下来的 SELECT，每次查它都现场执行。

原笔记这里写错了：它用了 SQL Server 的 + 拼字符串。在 MySQL 里 + 只做数值加法，字符串会被转成数字，结果全是 0。实测：

1
2
3

CREATE VIEW FullNames AS
SELECT EmployeeID, FirstName + ' ' + LastName AS FullName FROM Employees;
SELECT * FROM FullNames;

+------------+----------+
| EmployeeID | FullName |
+------------+----------+
|          1 |        0 |
|          2 |        0 |
+------------+----------+

正确写法是 CONCAT()：

1
2
3

CREATE VIEW FullNames2 AS
SELECT EmployeeID, CONCAT(FirstName, ' ', LastName) AS FullName FROM Employees;
SELECT * FROM FullNames2;

+------------+--------------+
| EmployeeID | FullName     |
+------------+--------------+
|          1 | John Smith   |
|          2 | Ada Lovelace |
+------------+--------------+

顺带：CONCAT 遇到 NULL 会整体返回 NULL，所以给可空列拼接要写成 CONCAT_WS(' ', FirstName, LastName) 或包一层 IFNULL()。

建存储过程

原笔记的写法在本机跑不通，因为过程体里有分号，客户端会提前把语句切开：

CREATE PROCEDURE AddEmployee (
    IN p_FirstName VARCHAR(50),
    IN p_LastName VARCHAR(50),
    IN p_BirthDate DATE,
    IN p_Position VARCHAR(50)
) BEGIN
INSERT INTO
    Employees (FirstName, LastName, BirthDate, Position)
VALUES
    (p_FirstName, p_LastName, p_BirthDate, p_Position);
END;

1 2	ERROR 1064 (42000) at line 3: You have an error in your SQL syntax; ... near '' at line 10 ERROR 1064 (42000) at line 13: You have an error in your SQL syntax; ... near 'END' at line 1

必须用 DELIMITER 临时改掉结束符（DELIMITER 是客户端命令，不是 SQL）：

DELIMITER //
CREATE PROCEDURE AddEmployee (
    IN p_FirstName VARCHAR(50),
    IN p_LastName VARCHAR(50),
    IN p_BirthDate DATE,
    IN p_Position VARCHAR(50)
)
BEGIN
    INSERT INTO Employees (FirstName, LastName, BirthDate, Position)
    VALUES (p_FirstName, p_LastName, p_BirthDate, p_Position);
END //
DELIMITER ;

1	Query OK, 0 rows affected (0.012 sec)

调用它，实测暴露了建表时没写 AUTO_INCREMENT 的后果：
1
CALL AddEmployee('Tom','Hanks','1956-07-09','Actor');
1
ERROR 1364 (HY000): Field 'EmployeeID' doesn't have a default value
- 过程本身没问题，是 EmployeeID 既没默认值又没传值。把建表语句改成 EmployeeID INT AUTO_INCREMENT PRIMARY KEY 就能跑通。

ALTER

加列（三种位置）

1
2
3

ALTER TABLE Employees ADD Email VARCHAR(255);            -- 追加到末尾
ALTER TABLE Employees ADD Email VARCHAR(255) FIRST;      -- 放到最前面
ALTER TABLE Employees ADD Email VARCHAR(255) AFTER FirstName;  -- 放到某列之后

实测 DESCRIBE Employees 的三种结果（只列 Field 顺序）：

1
2
3

EmployeeID, FirstName, LastName, BirthDate, Position, Email
Email, EmployeeID, FirstName, LastName, BirthDate, Position
EmployeeID, FirstName, Email, LastName, BirthDate, Position

加 NOT NULL 列时必须同时给 DEFAULT，否则在严格模式下已有行无法填值，会报错。

改列类型：MODIFY 与 CHANGE
1
2
ALTER TABLE Employees MODIFY COLUMN BirthDate DATETIME;
ALTER TABLE Employees CHANGE COLUMN FirstName GivenName VARCHAR(50);
实测：
1
2
Query OK, 2 rows affected (0.154 sec) -- MODIFY：DATE 变 DATETIME，重建了 2 行
Query OK, 0 rows affected (0.036 sec) -- CHANGE：只改名，类型没变所以 0 行
- 两者的区别就一句话：MODIFY 只改定义、不改名；CHANGE 必须把「旧名新名新定义」三段都写全，所以它既能改名也能改类型。
- CHANGE 里如果新旧名写成一样，就等价于 MODIFY。
- 改类型会重建列数据（2 rows affected 就是这个意思），大表上要谨慎，考虑 ALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE 或 pt-online-schema-change。

删列

1	ALTER TABLE Employees DROP COLUMN Email;

删索引再建
1
2
3
DROP INDEX idx_lastname ON Employees;
SHOW INDEX FROM Employees;
CREATE INDEX idx_firstname ON Employees (FirstName);
- 实测 DROP INDEX 之后 SHOW INDEX 只剩 PRIMARY 一行；重复执行 CREATE INDEX idx_firstname ... 会报 ERROR 1061 (42000): Duplicate key name 'idx_firstname'。
改视图
1
2
CREATE OR REPLACE VIEW FullNames AS
SELECT EmployeeID, CONCAT(FirstName, ' ', LastName) AS FullName FROM Employees;
- 原笔记说这条是「MySQL 和 PostgreSQL 通用」，方向没错：MySQL 确实支持 CREATE OR REPLACE VIEW（Oracle 不支持这个语法）。

改存储过程

MySQL 没有 ALTER PROCEDURE 来改过程体，标准做法就是「先删再建」，原笔记这段是对的：

DROP PROCEDURE IF EXISTS AddEmployee;
DELIMITER //
CREATE PROCEDURE AddEmployee (
    IN p_FirstName VARCHAR(50),
    IN p_LastName VARCHAR(50),
    IN p_BirthDate DATE,
    IN p_Position VARCHAR(50),
    IN p_Email VARCHAR(255)
)
BEGIN
    INSERT INTO Employees (FirstName, LastName, BirthDate, Position, Email)
    VALUES (p_FirstName, p_LastName, p_BirthDate, p_Position, p_Email);
END //
DELIMITER ;

注意这里给 Email 传值，前提是前面已经用 ALTER TABLE ... ADD Email 加过这一列，否则报 ERROR 1054: Unknown column 'Email' in 'field list'。

DROP
1
2
3
4
DROP TABLE IF EXISTS Employees;
DROP VIEW IF EXISTS FullNames;
DROP DATABASE IF EXISTS ExampleDatabase;
DROP PROCEDURE IF EXISTS AddEmployee;
- 删索引的两条等价写法：
  1
  2
  ALTER TABLE Employees DROP INDEX idx_lastname;
  DROP INDEX idx_lastname ON Employees;
- IF EXISTS 的意义：不存在时不报错，只留一条 Note 1051 Unknown table 告警，脚本能继续跑。生产脚本里几乎都应该带上。
- DROP TABLE 是不可逆的结构删除，删完连表定义一起没了；只想清数据用 TRUNCATE 或 DELETE。
TRUNCATE
1
TRUNCATE TABLE Employees;
- 行为上像「不带 WHERE 的 DELETE」，但机制完全不同：
  - TRUNCATE 是 DDL，隐式提交、不能回滚，也不触发行触发器。
  - TRUNCATE 会重置 AUTO_INCREMENT 计数器；DELETE 不会。
  - TRUNCATE 需要 DROP 权限，DELETE 只需要 DELETE 权限。
- 有外键引用这张表时 TRUNCATE 会被拒绝，实测：
  1
  TRUNCATE TABLE fk_parent;
  1
  2
  ERROR 1701 (42000) at line 75: Cannot truncate a table referenced in a foreign key constraint
  (`note_demo`.`fk_child`, CONSTRAINT `fk_p`)
  - 想强来就先 SET FOREIGN_KEY_CHECKS=0;（会破坏一致性，慎用），或者改用 DELETE FROM。

COMMENT

表注释和列注释都能加，但位置不能写错。原笔记这段是错的：

CREATE TABLE EmpComment (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    FirstName VARCHAR(100),
    LastName VARCHAR(100),
    DepartmentID INT,
    COMMENT 'A table to store employee data'
);

1
2
3

ERROR 1064 (42000) at line 55: You have an error in your SQL syntax; ...
near ''A table to store employee data'
)' at line 6

表级选项（COMMENT、ENGINE、CHARSET）必须写在右括号之后：

CREATE TABLE EmpComment2 (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    FirstName VARCHAR(100),
    LastName VARCHAR(100),
    DepartmentID INT
) COMMENT 'A table to store employee data';

实测注释确实落到了元数据里：

1 2	SELECT TABLE_NAME, TABLE_COMMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA='note_demo' AND TABLE_NAME LIKE 'EmpComment%';

+-------------+--------------------------------+
| TABLE_NAME  | TABLE_COMMENT                  |
+-------------+--------------------------------+
| EmpComment2 | A table to store employee data |
+-------------+--------------------------------+

列注释写在列定义末尾（COMMENT '...'），实测正常，见「字段修饰」一节。

后补注释：

ALTER TABLE Employees
    ADD COLUMN BirthDate DATE COMMENT 'The birth date of the employee';

ALTER TABLE Employees
    CHANGE COLUMN DepartmentID DepartmentID INT COMMENT 'Reference to the department table';

CREATE INDEX idx_lastname ON Employees (LastName)
    COMMENT 'Index to improve search performance on last name';

索引注释会出现在 SHOW INDEX 的 Index_comment 列里。

RENAME

RENAME TABLE old_table_name TO new_table_name;

RENAME TABLE old_table_name1 TO new_table_name1,
             old_table_name2 TO new_table_name2;

RENAME TABLE current_database.old_table_name TO new_database.new_table_name;

一条语句可以同时改多张表的名字，而且是原子的。
跨库改名要求两个库都在同一台服务器上，且账号有相应权限。
反引号是给「名字里有特殊字符或关键字」用的，比如 RENAME TABLE `old-name` TO `new-name`;。原笔记写的 RENAME TABLE \old_prefix_%` TO `new_prefix_%`里那个%` 不是通配符，就是表名里的一个普通字符，别指望它批量改名。
改库名没有直接命令，标准做法是建新库、RENAME TABLE 把每张表搬过去、再删旧库。

数据操作语言(DML)

本节统一用下面这张表，grade 允许为空：

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    grade VARCHAR(50)
);

INSERT

指定列插入

1	INSERT INTO students (name, age, grade) VALUES ('Alice Smith', 20, 'A');

1	Query OK, 1 row affected (0.008 sec)

省略某些列（吃默认值或 NULL）
1
INSERT INTO students (name, age) VALUES ('Bob Johnson', 23);
- 没列出来的 grade 是 NULL（该列可空且无默认值）。实测查全表能看到这一行 grade 位置是 NULL。

一次插入多行

INSERT INTO students (name, age, grade) VALUES
    ('Chris Green', 22, 'B'),
    ('Diana Red', 19, 'A'),
    ('Evan Brown', 21, 'C');

1 2	Query OK, 3 rows affected Records: 3 Duplicates: 0 Warnings: 0

从别的表灌数据（INSERT ... SELECT）
1
2
3
4
INSERT INTO students (name, age, grade)
SELECT name, age, grade
FROM transfer_students
WHERE transfer_date > '2023-01-01';
1
2
Query OK, 1 row affected
Records: 1 Duplicates: 0 Warnings: 0
- 实测只搬来 1 行（Frank，日期 2023-05-01），另一行 Gina 是 2022-03-01 被条件滤掉了。
- SELECT 的列数、顺序要和 INSERT 的列清单对上，否则报 ERROR 1136: Column count doesn't match value count at row 1。

只插默认值：`DEFAULT VALUES` 在 MySQL 里不存在

原笔记这条写法直接报错，实测：

1	INSERT INTO students DEFAULT VALUES;

1 2	ERROR 1064 (42000) at line 19: You have an error in your SQL syntax; ... near 'DEFAULT VALUES' at line 1

那是 PostgreSQL/标准 SQL 的写法。MySQL 里能用的只有「列清单 + DEFAULT」这一种，实测：

1	INSERT INTO students (name) VALUES (DEFAULT);

写成 INSERT INTO students (); VALUES ();（空列清单）同样报 ERROR 1064 ... near '()' at line 1，所以别照搬 PG 的习惯。
实测插进去之后全表是：

+----+-------------+------+-------+
| id | name        | age  | grade |
+----+-------------+------+-------+
|  1 | Alice Smith |   20 | A     |
|  2 | Bob Johnson |   23 | NULL  |
|  3 | Chris Green |   22 | B     |
|  4 | Diana Red   |   19 | A     |
|  5 | Evan Brown  |   21 | C     |
|  6 | Frank       |   20 | B     |
|  7 | NULL        | NULL | NULL  |
+----+-------------+------+-------+

主键/唯一键冲突时的三种处理（原笔记没提，但天天用）

先让表里只有一行 id=1, name='A1', age=20, grade='A'，然后：

1 2	INSERT IGNORE INTO students (id, name) VALUES (1, 'X'); SELECT * FROM students;

Query OK, 0 rows affected, 1 warning
+----+------+------+-------+
| id | name | age  | grade |
+----+------+------+-------+
|  1 | A1   |   20 | A     |
+----+------+------+-------+

INSERT IGNORE 是「撞了就跳过，别报错」，原来的行纹丝不动。副作用是它会把别的错误也降级成告警，容易掩盖真问题。

1 2	REPLACE INTO students (id, name) VALUES (1, 'X'); SELECT * FROM students;

Query OK, 2 rows affected
+----+------+------+-------+
| id | name | age  | grade |
+----+------+------+-------+
|  1 | X    | NULL | NULL  |
+----+------+------+-------+

注意 2 rows affected 和 age、grade 变成 NULL：REPLACE 是先删旧行再插新行，没写出来的列就是默认值。它还会触发 ON DELETE CASCADE、让自增值跳号，不能当 UPDATE 用。

1
2
3

INSERT INTO students (id, name, age) VALUES (1, 'Y', 30)
    ON DUPLICATE KEY UPDATE name = VALUES(name), age = VALUES(age);
SELECT * FROM students;

Query OK, 2 rows affected, 2 warnings
+----+------+------+-------+
| id | name | age  | grade |
+----+------+------+-------+
|  1 | Y    |   30 | NULL  |
+----+------+------+-------+

冲突时只更新 SET 里点名的列，其余列保持原样。这里 grade 之所以还是 NULL，是因为上一步的 REPLACE 已经把它清掉了，本轮没有给 grade 赋值。
那 2 条告警是 VALUES() 函数在这种语境里已废弃。8.0.19 起改用「行别名」：
1
2
INSERT INTO students (id, name, age) VALUES (1, 'Z', 40) AS new
ON DUPLICATE KEY UPDATE name = new.name, age = new.age;
1
Query OK, 2 rows affected -- 没有 warnings
- 新写法读起来也更直白：AS new 给「这次想插的那一行」起个名，冲突时用 new.列名 引用它。
ON DUPLICATE KEY UPDATE 命中更新时返回的 affected rows 是 2（这是 MySQL 的计数约定，不是 bug），别拿它当「影响了几行」的判断依据。

UPDATE

语法：UPDATE 表 SET 列=值 [WHERE 条件] [LIMIT n]。下面的例子都用这张表：

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    position VARCHAR(50),
    salary DECIMAL(10,2)
);
INSERT INTO employees (name, position, salary) VALUES
    ('Ann', 'Software Developer', 60000),
    ('Ben', 'Software Developer', 80000),
    ('Cai', 'Sales Manager', 70000),
    ('Dan', 'Intern', 20000),
    ('Eve', 'Intern', 22000),
    ('Fay', 'Intern', 21000);

注意这是小写的 employees，和大写的 Employees 不是同一张表（Linux 下区分大小写）。

改单行的某一列
1
UPDATE employees SET salary = 70000 WHERE id = 4;
1
2
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
- Rows matched 是「条件命中多少行」，Changed 是「真正发生变化的行数」。新值等于旧值时会出现 matched: 1, changed: 0。

用列的旧值算新值

1	UPDATE employees SET salary = salary * 1.1 WHERE position = 'Software Developer';

1 2	Query OK, 2 rows affected Rows matched: 2 Changed: 2 Warnings: 0

一次改多列

1	UPDATE employees SET position = 'Senior Software Developer', salary = 90000 WHERE id = 4;

按条件批量改

1 2	UPDATE employees SET salary = 75000 WHERE salary < 50000; UPDATE employees SET salary = salary + (salary * 0.05) WHERE position = 'Sales Manager';

1 2	Rows matched: 2 Changed: 2 -- 两个 Intern 被提到 75000 Rows matched: 1 Changed: 1 -- Sales Manager 涨 5%

用 `LIMIT` 限制改动行数

1	UPDATE employees SET salary = 30000 WHERE position = 'Intern' LIMIT 2;

1	Rows matched: 2 Changed: 2 Warnings: 0

改完之后 SELECT * FROM employees 的实测结果：

+----+------+---------------------------+----------+
| id | name | position                  | salary   |
+----+------+---------------------------+----------+
|  1 | Ann  | Software Developer        | 66000.00 |
|  2 | Ben  | Software Developer        | 88000.00 |
|  3 | Cai  | Sales Manager             | 73500.00 |
|  4 | Dan  | Senior Software Developer | 90000.00 |
|  5 | Eve  | Intern                    | 30000.00 |
|  6 | Fay  | Intern                    | 30000.00 |
+----+------+---------------------------+----------+

LIMIT 不带 ORDER BY 时改的是哪两行不保证，要可控必须写 ORDER BY。

忘记写 WHERE 就是全表更新。生产库上建议开着 sql_safe_updates，它会让「不带 WHERE」或「WHERE 里没有索引列」的 UPDATE/DELETE 直接报错。实测：
1
2
3
SET SQL_SAFE_UPDATES = 1;
UPDATE students SET age = 1;
DELETE FROM students;
1
2
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table
without a WHERE that uses a KEY column.
- 想临时放行就 SET SQL_SAFE_UPDATES = 0;，或者给条件里加一个主键范围。

DELETE

1	DELETE FROM students WHERE id = 5;

按条件删：

1	DELETE FROM students WHERE grade NOT IN ('A', 'B', 'C');

NULL 不会被 NOT IN 命中，实测删完之后 grade 为 NULL 的那行（id=2）还在：

+----+-------------+------+-------+
| id | name        | age  | grade |
+----+-------------+------+-------+
|  1 | Alice Smith |   20 | A     |
|  2 | Bob Johnson |   23 | NULL  |
|  3 | Chris Green |   22 | B     |
|  4 | Diana Red   |   19 | A     |
|  6 | Frank       |   20 | B     |
|  7 | NULL        | NULL | NULL  |
+----+-------------+------+-------+

三值逻辑：NULL NOT IN (...) 的结果是 UNKNOWN，不是真，所以不删。要连空值一起删得写 WHERE grade IS NULL OR grade NOT IN ('A','B','C')。

限量删：

1	DELETE FROM students WHERE age < 20 LIMIT 2;

删全表的两种写法：
1
2
DELETE FROM students WHERE 1=1;
DELETE FROM students;
- 两条都实测可用，删完 SELECT COUNT(*) 是 0。
DELETE 是 DML，可以回滚（在事务里）、会触发 BEFORE/AFTER DELETE 触发器、不会重置自增计数器。实测：
1
2
3
4
DELETE FROM students;
ALTER TABLE students AUTO_INCREMENT = 1;
INSERT INTO students (name, age) VALUES ('Zoe', 20);
SELECT id, name FROM students;
1
2
3
4
5
+----+------+
| id | name |
+----+------+
| 1 | Zoe |
+----+------+
- 如果不写中间那句 ALTER TABLE ... AUTO_INCREMENT = 1，新行的 id 会接着往上走（实测是 8）。

数据查询语言(DQL)

本节全部在 note_demo.students 上跑，表里固定这 5 行（grade 允许为空）：

1	SELECT * FROM students;

+----+-------------+------+-------+
| id | name        | age  | grade |
+----+-------------+------+-------+
|  1 | Alice Smith |   20 | A     |
|  2 | Bob Johnson |   23 | NULL  |
|  3 | Chris Green |   22 | B     |
|  4 | Diana Red   |   19 | A     |
|  5 | Evan Brown  |   21 | C     |
+----+-------------+------+-------+

基础查询
1
SELECT * FROM students;
- * 是「所有列」的简写，方便但浪费：列一变代码就跟着变，而且多读的数据要过网络。正式代码里建议写清列名。
- SELECT 后面不带 FROM 也能用（SELECT 1+1;），常用来验证连接是否活着。

选择特定列

1	SELECT name, age FROM students;

+-------------+------+
| name        | age  |
+-------------+------+
| Alice Smith |   20 |
| Bob Johnson |   23 |
| Chris Green |   22 |
| Diana Red   |   19 |
| Evan Brown  |   21 |
+-------------+------+

排除重复记录

1	SELECT DISTINCT grade FROM students;

+-------+
| grade |
+-------+
| A     |
| NULL  |
| B     |
| C     |
+-------+

DISTINCT 作用于它后面整张列表，不是单个列：SELECT DISTINCT grade, age 是按「grade+age 组合」去重。
NULL 也参与去重，所以结果里会出现一行 NULL。

去重计数要用 COUNT(DISTINCT 列)，实测三个数字含义不同：

1	SELECT COUNT(*) AS 总行数, COUNT(DISTINCT grade) AS 不同grade数, COUNT(grade) AS grade非空数 FROM students;

+-----------+----------------+----------------+
| 总行数    | 不同grade数    | grade非空数    |
+-----------+----------------+----------------+
|         5 |              3 |              4 |
+-----------+----------------+----------------+

COUNT(*) 数所有行；COUNT(列) 只数该列非 NULL 的行；COUNT(DISTINCT 列) 也不数 NULL（所以是 3 而不是 4）。

条件查询

1	SELECT * FROM students WHERE age > 20;

+----+-------------+------+-------+
| id | name        | age  | grade |
+----+-------------+------+-------+
|  2 | Bob Johnson |   23 | NULL  |
|  3 | Chris Green |   22 | B     |
|  5 | Evan Brown  |   21 | C     |
+----+-------------+------+-------+

判空只能用 IS NULL / IS NOT NULL，写 grade = NULL 永远不成立（实测 WHERE grade = 'A' AND age >= 20 得 1 行，而 WHERE grade IS NULL 得的是 Bob Johnson 那行）。

常用谓词，实测：

SELECT * FROM students WHERE age IN (20, 22);        -- 枚举
SELECT * FROM students WHERE age BETWEEN 20 AND 22;  -- 闭区间，两端都含
SELECT * FROM students WHERE name LIKE '%o%';        -- 含字母 o
SELECT * FROM students WHERE name LIKE 'B_b%';       -- B + 任意一个字符 + b

IN (20,22)        -> Alice Smith(20), Chris Green(22)         2 行
BETWEEN 20 AND 22 -> Alice Smith(20), Chris Green(22), Evan Brown(21)  3 行
LIKE '%o%'        -> Bob Johnson, Evan Brown                  2 行
LIKE 'B_b%'       -> Bob Johnson                              1 行

LIKE 里 % 是任意长度（含 0），_ 是恰好一个字符；默认大小写不敏感（utf8mb4_unicode_ci），所以 %o% 也命中了 Brown 里的 o。
想匹配真正的下划线要写 '\_'，实测 WHERE name LIKE 'B\_b%' 返回 Empty set——因为没有一个名字是「B 下划线 b」开头。
BETWEEN 对字符串按排序规则比较，对日期同样适用（BETWEEN '2026-01-01' AND '2026-12-31'）。

排序结果

1	SELECT name, age FROM students ORDER BY age ASC;

+-------------+------+
| name        | age  |
+-------------+------+
| Diana Red   |   19 |
| Alice Smith |   20 |
| Evan Brown  |   21 |
| Chris Green |   22 |
| Bob Johnson |   23 |
+-------------+------+

1	SELECT name, age FROM students ORDER BY age DESC;

+-------------+------+
| name        | age  |
+-------------+------+
| Bob Johnson |   23 |
| Chris Green |   22 |
| Evan Brown  |   21 |
| Alice Smith |   20 |
| Diana Red   |   19 |
+-------------+------+

ASC 是默认（可省略），DESC 必须写。

多列排序时后面的列只在前面的列相等时才起作用，实测 NULL 排在最前（MySQL 里 NULL 视为最小）：

1	SELECT name, grade, age FROM students ORDER BY grade ASC, age DESC;

+-------------+-------+------+
| name        | grade | age  |
+-------------+-------+------+
| Bob Johnson | NULL  |   23 |
| Alice Smith | A     |   20 |
| Diana Red   | A     |   19 |
| Chris Green | B     |   22 |
| Evan Brown  | C     |   21 |
+-------------+-------+------+

grade 相同的 Alice(20) 和 Diana(19) 按 age DESC 排好了。

不加 ORDER BY 时行的顺序不保证（取决于存储引擎和执行计划），要稳定顺序就必须自己写。

限制返回的结果数量

1	SELECT name, age FROM students LIMIT 3;

+-------------+------+
| name        | age  |
+-------------+------+
| Alice Smith |   20 |
| Bob Johnson |   23 |
| Chris Green |   22 |
+-------------+------+

分页两种等价写法，实测结果相同：

1 2	SELECT name, age FROM students LIMIT 2 OFFSET 2; SELECT name, age FROM students LIMIT 2, 2;

+-------------+------+
| name        | age  |
+-------------+------+
| Chris Green |   22 |
| Diana Red   |   19 |
+-------------+------+

LIMIT 偏移量, 条数 是 MySQL 方言，LIMIT 条数 OFFSET 偏移量 更接近标准，推荐后者。

取「最大那一条」要配 ORDER BY，否则 LIMIT 1 拿到的是任意一行：

1	SELECT name, age FROM students ORDER BY age DESC LIMIT 1;

+-------------+------+
| name        | age  |
+-------------+------+
| Bob Johnson |   23 |
+-------------+------+

深分页（LIMIT 1000000, 10）会很慢，因为前 100 万行也要扫。改用「记住上一页最后一条的主键」：WHERE id > 上次最大id ORDER BY id LIMIT 10。

计算列

SELECT 里可以直接写表达式，结果作为一列返回：

1	SELECT name, age * 2 AS double_age, age + 1 AS next_age FROM students;

+-------------+------------+----------+
| name        | double_age | next_age |
+-------------+------------+----------+
| Alice Smith |         40 |       21 |
| Bob Johnson |         46 |       24 |
| Chris Green |         44 |       23 |
| Diana Red   |         38 |       20 |
| Evan Brown  |         42 |       22 |
+-------------+------------+----------+

计算列不会写回表里，只是这一次查询的结果。

IFNULL(列, 替代值) 用来处理 NULL，实测拼字符串很好用：

1	SELECT CONCAT(name, ' (', IFNULL(grade, '未评'), ')') AS 展示 FROM students;

+----------------------+
| 展示                 |
+----------------------+
| Alice Smith (A)      |
| Bob Johnson (未评)   |
| Chris Green (B)      |
| Diana Red (A)        |
| Evan Brown (C)       |
+----------------------+

少了 IFNULL 的话，Bob 那行会因为 CONCAT 遇 NULL 返回 NULL 而整列变空。

聚合函数

1
2
3

SELECT AVG(age) AS 平均, SUM(age) AS 合计, MIN(age) AS 最小,
       MAX(age) AS 最大, COUNT(*) AS 行数
FROM students;

+---------+--------+--------+--------+--------+
| 平均    | 合计   | 最小   | 最大   | 行数   |
+---------+--------+--------+--------+--------+
| 21.0000 |    105 |     19 |     23 |      5 |
+---------+--------+--------+--------+--------+

AVG 的结果是 DECIMAL，小数位保留 4 位（21.0000），不是浮点。

空结果集上的聚合，实测三兄弟表现不一样：

1	SELECT COUNT(age) AS 计数, AVG(age) AS 平均, SUM(age) AS 合计 FROM students WHERE age > 100;

+--------+--------+--------+
| 计数   | 平均   | 合计   |
+--------+--------+--------+
|      0 |   NULL |   NULL |
+--------+--------+--------+

COUNT 永远给数（0），AVG/SUM 给 NULL。所以「SUM 结果为 NULL」不代表 0，代码里要 IFNULL(SUM(x),0)。

分组结果

1	SELECT age, COUNT(*) AS number_of_students FROM students GROUP BY age;

+------+--------------------+
| age  | number_of_students |
+------+--------------------+
|   20 |                  1 |
|   23 |                  1 |
|   22 |                  1 |
|   19 |                  1 |
|   21 |                  1 |
+------+--------------------+

分组后的输出顺序同样不保证（这里就是按内部顺序而不是 age 大小），要有序得再加 ORDER BY。

NULL 会自成一组，实测：

1	SELECT grade, COUNT(*) AS n, AVG(age) AS 平均年龄 FROM students GROUP BY grade;

+-------+---+--------------+
| grade | n | 平均年龄     |
+-------+---+--------------+
| A     | 2 |      19.5000 |
| NULL  | 1 |      23.0000 |
| B     | 1 |      22.0000 |
| C     | 1 |      21.0000 |
+-------+---+--------------+

ONLY_FULL_GROUP_BY 的约束（本机默认开启）：SELECT 里的非聚合列必须出现在 GROUP BY 里，否则报错。实测：

1	SELECT name, age, COUNT(*) FROM students GROUP BY age;

1
2
3

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains
nonaggregated column 'note_demo.students.name' which is not functionally dependent on
columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

把它关掉（SET sql_mode='';）同一条 SQL 就能跑，但每组返回的 name 是「组里任意一行的值」，结果不可预期：

+-------------+------+----------+
| name        | age  | COUNT(*) |
+-------------+------+----------+
| Alice Smith |   20 |        1 |
| Bob Johnson |   23 |        1 |
| Chris Green |   22 |        1 |
| Diana Red   |   19 |        1 |
| Evan Brown  |   21 |        1 |
+-------------+------+----------+

正确解法是把列补进 GROUP BY，或者对它用聚合函数（MAX(name)、GROUP_CONCAT(name)）。老脚本迁移到 8.0+ 时这是最常见的报错。

使用 HAVING 子句过滤分组
- WHERE 在分组前过滤行，HAVING 在分组后过滤组，所以 HAVING 里可以用聚合函数。
  1
  SELECT age, COUNT(*) AS n FROM students GROUP BY age HAVING age > 20;
  1
  2
  3
  4
  5
  6
  7
  +------+---+
  | age | n |
  +------+---+
  | 23 | 1 |
  | 22 | 1 |
  | 21 | 1 |
  +------+---+
  - 这条其实该写 WHERE，用它只是演示；HAVING age > 20 能引用分组列。
  - 真正需要 HAVING 的是这种：
    1
    SELECT grade, COUNT(*) AS n FROM students GROUP BY grade HAVING COUNT(*) > 1;
    1
    2
    3
    4
    5
    +-------+---+
    | grade | n |
    +-------+---+
    | A | 2 |
    +-------+---+
    - 换成 WHERE COUNT(*) > 1 会直接报 ERROR 1111: Invalid use of group function，因为 WHERE 阶段还没有聚合结果。
- 执行顺序（面试常问）：FROM → WHERE → GROUP BY → 聚合 → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT。这也解释了为什么 WHERE 里不能用 SELECT 里起的别名，而 ORDER BY 里可以。

连接查询

1
2
3

SELECT students.name, courses.course_name
FROM students
INNER JOIN courses ON students.id = courses.student_id;

见「多表联合查询」一节，那里有完整的数据、实测结果和各种 JOIN 的对比。

子查询
1
2
3
SELECT *
FROM students
WHERE id IN (SELECT student_id FROM courses);
- IN 后面跟子查询是安全的写法；换成 = 只在子查询恰好返回一行时才行，实测返回多行会报 ERROR 1242: Subquery returns more than 1 row（详见「多表联合操作」一节）。

复合条件查询

1	SELECT * FROM students WHERE name LIKE '%a%' AND age > 18;

+----+-------------+------+-------+
| id | name        | age  | grade |
+----+-------------+------+-------+
|  1 | Alice Smith |   20 | A     |
|  4 | Diana Red   |   19 | A     |
|  5 | Evan Brown  |   21 | C     |
+----+-------------+------+-------+

命中这三行是因为名字里有字母 a（Alice、Diana、Evan），Chris Green 和 Bob Johnson 里没有。
AND 优先级高于 OR，混用时务必加括号：WHERE (a OR b) AND c。
不加括号写 WHERE a OR b AND c 实际是 a OR (b AND c)，这是「结果莫名变多」的经典原因。

使用别名
1
2
3
SELECT s.name AS student_name, c.course_name AS course
FROM students AS s
INNER JOIN courses AS c ON s.id = c.student_id;
- 表别名一旦定义，SELECT/WHERE/ON 里就只能用别名，再写原表名会报 ERROR 1054: Unknown column 'students.name'。
- 列别名可以省 AS（s.name student_name），但建议写上，可读性好。
- 别名里想带空格或中文要加引号：AS "学生姓名"。

数据控制语言(DCL)

MySQL 的账号是「用户名 + 来源主机」的组合，root@localhost 与 root@% 是两个互不相干的账号。% 是通配符，代表任意主机。

创建用户

1	CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

密码强度默认不校验（本机实测）：空密码和 '123' 都能建成功。
1
2
CREATE USER 'nopw'@'localhost' IDENTIFIED BY '';
CREATE USER 'weakpw'@'localhost' IDENTIFIED BY '123';
1
Query OK, 0 rows affected -- 两条都成功
- 想加校验要显式装组件：INSTALL COMPONENT 'file://component_validate_password';。没装时查它会报 ERROR 1193: Unknown system variable 'validate_password.enabled'（本机就是这么报的）。
老的 mysql_native_password 插件在 9.x 已经不再加载，实测：
1
CREATE USER 'nativepw'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Aa12345678!';
1
ERROR 1524 (HY000): Plugin 'mysql_native_password' is not loaded
- 8.0.34 起废弃、8.4 默认关闭、9.0 起移除。老客户端连不上时应该升级驱动，而不是回头找这个插件。

建完账号看一眼：

1	SELECT user, host, plugin, account_locked, password_expired FROM mysql.user ORDER BY user;

+------------------+-----------+-----------------------+----------------+------------------+
| user             | host      | plugin                | account_locked | password_expired |
+------------------+-----------+-----------------------+----------------+------------------+
| mysql.infoschema | localhost | caching_sha2_password | Y              | N                |
| mysql.session    | localhost | caching_sha2_password | Y              | N                |
| mysql.sys        | localhost | caching_sha2_password | Y              | N                |
| newuser          | localhost | caching_sha2_password | N              | N                |
| nopw             | localhost | caching_sha2_password | N              | N                |
| root             | localhost | caching_sha2_password | N              | N                |
| weakpw           | localhost | caching_sha2_password | N              | N                |
+------------------+-----------+-----------------------+----------------+------------------+

mysql.* 那三个是系统内部账号，account_locked=Y 是正常状态，别删也别解锁。
直接 SELECT * FROM mysql.user 会把 authentication_string（密码哈希）也带出来，不要把它贴到日志或截图里。

授权用户(GRANT)

1 2	GRANT SELECT ON note_demo.students TO 'user1'@'localhost'; GRANT ALL PRIVILEGES ON note_demo.* TO 'user2'@'localhost';

权限级别由 ON 后面的对象决定：*.*（全局）、库.*（库级）、库.表（表级）、还可以细到列级 GRANT SELECT (col1, col2) ON ...。

授权后立刻生效，不需要 FLUSH PRIVILEGES。实测授权前后的 SHOW GRANTS：

1	SHOW GRANTS FOR 'newuser'@'localhost';

+--------------------------------------------------------------+
| Grants for newuser@localhost                                 |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO `newuser`@`localhost`                  |
| GRANT SELECT ON `note_demo`.`students` TO `newuser`@`localhost` |
+--------------------------------------------------------------+

第一行 USAGE ON *.* 是「只有连接权、没有任何权限」的占位说法，每个账号都有一条。
再授库级权限后，表级那条并不会被合并掉，两行会同时存在（实测如此），看着像重复，其实作用域不同。

查看授权
1
2
SHOW GRANTS; -- 当前会话自己的权限
SHOW GRANTS FOR 'username'@'host'; -- 指定账号
- root 的 SHOW GRANTS 输出很长，实测有三条：全局权限一大串（SELECT, INSERT, ..., DROP ROLE）、一条包含几十个动态权限（SUPER 之外的细粒度权限，如 SYSTEM_VARIABLES_ADMIN）的、以及 GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION。
- 想知道「这个账号能对这张表干什么」，用 SHOW GRANTS 比翻 mysql.tables_priv 直观。
刷新权限
1
FLUSH PRIVILEGES;
- 它的作用是「让服务端重新加载授权表」。只有当你直接用 UPDATE/INSERT 改了 mysql.* 授权表时才需要它。
- 用 CREATE USER / GRANT / REVOKE 这些语句时不需要 FLUSH PRIVILEGES，它们自己就会生效。原笔记在每段 DCL 后面都跟一句 FLUSH PRIVILEGES，属于无害但多余的写法。

取消授权(REVOKE)

1 2	REVOKE SELECT ON note_demo.students FROM 'user1'@'localhost'; REVOKE ALL PRIVILEGES ON note_demo.* FROM 'user2'@'localhost';

REVOKE 的语法跟 GRANT 对称，介词用 FROM（GRANT ... TO，REVOKE ... FROM）。

实测回收干净之后只剩那条占位的 GRANT USAGE ON *.*：

+----------------------------------------------+
| Grants for newuser@localhost                 |
+----------------------------------------------+
| GRANT USAGE ON *.* TO `newuser`@`localhost`  |
+----------------------------------------------+

想让某个连接立刻失去已有会话的权限是做不到的：已授权的权限在会话建立时就取好了，回收只影响新连接。要立刻生效就 KILL 掉那个会话。

删除用户

1 2	DROP USER 'username'@'host'; DROP USER IF EXISTS 'user1'@'localhost', 'user2'@'localhost';

一条 DROP USER 可以带多个账号，逗号分隔。

账号不存在时不加 IF EXISTS 会报错，加了就只剩一条告警，实测两条的原文：

1
2
3

DROP USER 'ghost'@'localhost';
DROP USER IF EXISTS 'ghost2'@'localhost';
SHOW WARNINGS;

1 2	ERROR 1396 (HY000): Operation DROP USER failed for 'ghost'@'localhost' (User account does not exist) Note 3162 Authorization ID 'ghost2'@'localhost' does not exist.

DROP USER 会连带清掉该账号的权限；但它不会删这个账号建的表，那些对象的属主信息还留在 DEFINER 里，视图/存储过程可能因此报「找不到定义者」。

root 用户远程登录

原笔记的做法是直接改授权表：
1
2
3
USE mysql;
UPDATE user SET Host='%' WHERE User='root' AND Host='localhost';
FLUSH PRIVILEGES;
- 这段本机没有执行，因为把 root@localhost 改成 root@% 等于给 root 开了全网入口，而且改完还得靠 FLUSH 才生效，风险明显大于收益。

推荐做法是另建一个允许远程的账号，root 保持只从本机走 socket：

1 2	CREATE USER 'note_remote'@'%' IDENTIFIED BY 'Aa12345678!'; GRANT SELECT, INSERT ON note_demo.* TO 'note_remote'@'%';

实测授权与连接都正常：

1	SHOW GRANTS FOR 'note_remote'@'%';

+----------------------------------------------------------+
| Grants for note_remote@%                                 |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO `note_remote`@`%`                  |
| GRANT SELECT, INSERT ON `note_demo`.* TO `note_remote`@`%` |
+----------------------------------------------------------+

1	mysql -u note_remote -p'Aa12345678!' -h 127.0.0.1 -P 3306 -e "SELECT CURRENT_USER(); SHOW DATABASES;"

+----------------+
| CURRENT_USER() |
+----------------+
| note_remote@%  |
+----------------+
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
| note_demo          |
+--------------------+

注意 SHOW DATABASES 只列出它被授权的库，这就是最小权限的实际效果。

如果确实需要一个能远程的 root，写法是 CREATE USER 'root'@'%' IDENTIFIED BY '...' 再 GRANT ALL ON *.* TO 'root'@'%' WITH GRANT OPTION;——但这等于把整台数据库交给任何能连上 3306 的主机，除非你非常清楚自己在做什么（比如容器里的一次性实例），否则别开。
远程连不上时的排查顺序：ss -ltnp | grep 3306 看是否 bind-address 只绑了 127.0.0.1 → 防火墙/安全组 → 账号的 host 是否匹配 → 认证插件客户端是否支持。

事务控制语言(TCL)

说明
- 事务是把多条语句打包成「要么全成、要么全撤」的单位。InnoDB 支持事务，MyISAM/MEMORY 不支持（实测 SHOW ENGINES 里它们的 Transactions 列是 NO），用它们写的表上 ROLLBACK 是空操作。
- 默认 autocommit = 1：每条语句自己就是一个事务，执行完立刻落盘。所以「事务」需要显式 START TRANSACTION 才有意义。
  1
  SELECT @@autocommit;
  1
  2
  3
  4
  5
  +------------+
  | autocommit |
  +------------+
  | 1 |
  +------------+
BEGIN 或 START TRANSACTION
1
START TRANSACTION; -- 或者 BEGIN; 两者等价
- BEGIN / ROLLBACK / COMMIT 在 MySQL 里都能开事务，但 BEGIN 容易和存储过程里的 BEGIN...END 块混淆，脚本里统一用 START TRANSACTION 更清楚。
COMMIT
1
COMMIT;
- 把当前事务的所有改动永久保存，并结束事务。

`ROLLBACK`

ROLLBACK;

撤销当前事务里尚未提交的改动。实测一组对照：

START TRANSACTION;
INSERT INTO accounts VALUES (3,103,-100.00);
SELECT id, user_id, amount FROM accounts;
ROLLBACK;
SELECT id, user_id, amount FROM accounts;

|  1 |     101 |  500.00 |
|  2 |     102 |  300.00 |
|  3 |     103 | -100.00 |      -- 事务内看得到
...
|  1 |     101 |  500.00 |
|  2 |     102 |  300.00 |      -- 回滚后没了

SAVEPOINT
1
SAVEPOINT sp1;
- 在事务内部打一个可回退的点，不必把整个事务全撤。

`ROLLBACK TO SAVEPOINT`

1	ROLLBACK TO SAVEPOINT sp1;

只撤销 sp1 之后的改动，事务本身仍然没结束，还能继续写或最终 COMMIT。实测：

START TRANSACTION;
INSERT INTO accounts VALUES (5,105,1.00);
SAVEPOINT sp1;
INSERT INTO accounts VALUES (6,106,2.00);
ROLLBACK TO SAVEPOINT sp1;
SELECT id FROM accounts WHERE id IN (5,6);
COMMIT;
SELECT id FROM accounts WHERE id IN (5,6);

+----+
| id |
+----+
|  5 |      -- 回滚到 sp1：6 没了，5 还在
+----+
...
|  5 |      -- 提交后 5 永久保留

`RELEASE SAVEPOINT`

1	RELEASE SAVEPOINT sp2;

只是删掉这个保存点（释放名字），不会提交也不会撤销它之后的改动。删过之后再回退到它，实测报错：
1
2
3
4
START TRANSACTION;
SAVEPOINT sp2;
RELEASE SAVEPOINT sp2;
ROLLBACK TO SAVEPOINT sp2;
1
ERROR 1305 (42000): SAVEPOINT sp2 does not exist

简单示例
1
2
3
4
START TRANSACTION;
INSERT INTO accounts (user_id, amount) VALUES (101, -100.00);
INSERT INTO accounts (user_id, amount) VALUES (102, 100.00);
COMMIT;
- 典型的转账写法：两条 INSERT 要么一起成功、要么一起撤销。
- 原笔记在这段之后又跟了一句 ROLLBACK;，容易让人以为「提交后还能撤」。实测提交之后再 ROLLBACK 只是结束一个空事务，已经提交的改动不会消失：
  1
  2
  3
  COMMIT;
  ROLLBACK;
  SELECT id, user_id, amount FROM accounts;
  1
  2
  3
  | 1 | 101 | 500.00 |
  | 2 | 102 | 300.00 |
  | 4 | 104 | 100.00 | -- 刚提交的那条还在
- 真实转账场景应该用 UPDATE ... SET amount = amount - ? WHERE id = ? AND amount >= ?，再检查 ROW_COUNT()，而不是插两条流水。
禁用自动提交
1
SET autocommit = 0;
- 关掉之后，任何写操作都自动处在一个事务里，直到你显式 COMMIT 或 ROLLBACK。
- 实测一个关键现象：未提交的改动自己看得到，别人看不到；ROLLBACK 之后自己也就看不到了：
  1
  2
  3
  4
  5
  6
  SET autocommit = 0;
  INSERT INTO accounts VALUES (9,109,9.00);
  SELECT id FROM accounts WHERE id = 9;
  ROLLBACK;
  SELECT id FROM accounts WHERE id = 9;
  SET autocommit = 1;
  1
  2
  3
  4
  5
  6
  +----+
  | id |
  +----+
  | 9 | -- 未提交，本会话可见
  +----+
  Empty set -- 回滚后消失
- 用完记得改回来（SET autocommit = 1;），否则这个连接会一直挂着事务，长事务会撑大 undo log 并阻塞清理。
- 注意 SET autocommit=0 只影响当前会话；SET GLOBAL autocommit=0 才影响新连接，一般不要动全局。
两个必须知道的坑（实测）
- DDL 会隐式提交。 事务里执行 CREATE TABLE 之后，前面的写操作就再也回滚不了：
  1
  2
  3
  4
  5
  6
  DELETE FROM accounts WHERE id = 1;
  START TRANSACTION;
  DELETE FROM accounts WHERE id = 2;
  CREATE TABLE implicit_t (x INT);
  ROLLBACK;
  SELECT id FROM accounts ORDER BY id;
  1
  2
  3
  4
  5
  6
  +----+
  | id |
  +----+
  | 4 |
  | 5 | -- id=1 和 id=2 都被删掉了，回滚没起作用
  +----+
  - 同类会隐式提交的还有 ALTER/DROP/TRUNCATE/RENAME TABLE、START TRANSACTION、USE、SET autocommit、LOCK TABLES、LOAD DATA INFILE、DDL 类的 GRANT/DROP USER 等。
- ROLLBACK 不在事务里时不报错。 实测单独执行 ROLLBACK 返回 Query OK, 0 rows affected，什么也没撤销。所以别把「执行了 ROLLBACK」当成「撤销成功了」。
- 隔离级别默认是 REPEATABLE READ，实测：
  1
  2
  3
  SELECT @@transaction_isolation;
  SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
  SELECT @@transaction_isolation;
  1
  2
  3
  4
  5
  6
  7
  +-----------------+
  | @@transaction_isolation |
  +-----------------+
  | REPEATABLE-READ |
  +-----------------+
  ...
  | READ-COMMITTED |
  - 值里用的是连字符（REPEATABLE-READ），不是空格，写比较条件时注意。

多表联合查询

这一节用两张小表，数据就是原笔记给的那套，实测结果全部照抄本机输出：

CREATE TABLE stu (id INT PRIMARY KEY, name VARCHAR(50), age INT);
INSERT INTO stu VALUES (1,'Alice',22),(2,'Bob',23),(3,'Carol',22);

CREATE TABLE crs (id INT PRIMARY KEY, course_name VARCHAR(50), student_id INT);
INSERT INTO crs VALUES (1,'Math',1),(2,'Science',2),(3,'Math',2),(4,'History',3);

两张表的内容（SELECT * 实测）：

+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  1 | Alice |  22 |
|  2 | Bob   |  23 |
|  3 | Carol |  22 |
+----+-------+-----+

+----+-------------+------------+
| id | course_name | student_id |
+----+-------------+------------+
|  1 | Math        |          1 |
|  2 | Science     |          2 |
|  3 | Math        |          2 |
|  4 | History     |          3 |
+----+-------------+------------+

原笔记这里有一处笔误：crs 的表头画成 | id | course_name | student_id | 但分隔线少了一列，而且 student_id 列宽对不齐，不影响理解。

关系是：crs.student_id 指向 stu.id。Alice 选了 1 门，Bob 选了 2 门，Carol 选了 1 门。

内连接（INNER JOIN）

1
2
3

SELECT stu.name, crs.course_name
FROM stu
INNER JOIN crs ON stu.id = crs.student_id;

+-------+-------------+
| name  | course_name |
+-------+-------------+
| Alice | Math        |
| Bob   | Science     |
| Bob   | Math        |
| Carol | History     |
+-------+-------------+
4 rows in set

只保留两边都能配上的行。Bob 有两门课，所以出现两次。
如果某人一门课都没选，他就不会出现在结果里（这正是内连接和左连接的分界）。

隐式内连接（Implicit Inner Join）
1
2
3
SELECT stu.name, crs.course_name
FROM stu, crs
WHERE stu.id = crs.student_id;
1
2
3
4
5
6
7
8
+-------+-------------+
| name | course_name |
+-------+-------------+
| Alice | Math |
| Bob | Science |
| Bob | Math |
| Carol | History |
+-------+-------------+
- 结果与 INNER JOIN 完全一致，这是老式写法：把两张表并列在 FROM 里，连接条件写在 WHERE。
- 风险在于漏掉 WHERE 就变成笛卡尔积（3×4=12 行），而且没有任何报错。现代写法推荐显式 JOIN：连接条件写漏了会直接语法报错，更安全。
左外连接（LEFT JOIN）
1
2
3
SELECT stu.name, crs.course_name
FROM stu
LEFT JOIN crs ON stu.id = crs.student_id;
1
2
3
4
5
6
7
8
+-------+-------------+
| name | course_name |
+-------+-------------+
| Alice | Math |
| Bob | Math |
| Bob | Science |
| Carol | History |
+-------+-------------+
- 左表（stu）的行全部保留，右表配不上就填 NULL。
- 注意输出的行顺序和内连接不一样（Bob Math 排在 Bob Science 前面）：JOIN 的执行计划不同，顺序就不同。要顺序必须写 ORDER BY。
- 想找「一门课都没选的人」：
  1
  2
  3
  SELECT stu.name FROM stu
  LEFT JOIN crs ON stu.id = crs.student_id
  WHERE crs.id IS NULL;
  - 本数据集里三人都选课了，所以是 Empty set；把 Carol 的课删掉就能查出她。

右外连接（RIGHT JOIN）

1
2
3

SELECT stu.name, crs.course_name
FROM stu
RIGHT JOIN crs ON stu.id = crs.student_id;

+-------+-------------+
| name  | course_name |
+-------+-------------+
| Alice | Math        |
| Bob   | Science     |
| Bob   | Math        |
| Carol | History     |
+-------+-------------+

右表（crs）的行全部保留，左表配不上填 NULL。
A RIGHT JOIN B 与 B LEFT JOIN A 等价。实际项目里几乎只用 LEFT JOIN，把表序调一下就行，可读性更好。

全连接（FULL JOIN）

MySQL 不支持 FULL [OUTER] JOIN，写了也不报「不支持」，而是报一个让人摸不着头脑的错，实测：
1
2
SELECT stu.name, crs.course_name
FROM stu FULL JOIN crs ON stu.id = crs.student_id;
1
ERROR 1054 (42S22): Unknown column 'stu.name' in 'field list'
- 原因是解析器把 FULL 当成了 stu 的表别名（FROM stu FULL = FROM stu AS FULL），于是 stu.name 这个引用就不存在了。

标准替代写法是「左连接 UNION 右连接」，实测：

SELECT stu.name, crs.course_name
FROM stu LEFT JOIN crs ON stu.id = crs.student_id
UNION
SELECT stu.name, crs.course_name
FROM stu RIGHT JOIN crs ON stu.id = crs.student_id;

+-------+-------------+
| name  | course_name |
+-------+-------------+
| Alice | Math        |
| Bob   | Math        |
| Bob   | Science     |
| Carol | History     |
+-------+-------------+
4 rows in set

只有 4 行是因为本数据集里两边都能配上，两个结果集完全相同；UNION 又会去重，所以合并后还是 4 行。
想保留重复行要用 UNION ALL（更快，不必排序去重）。

更省的做法是「左连接 + 右连接里只补左连接没覆盖的那部分」：

SELECT stu.name, crs.course_name FROM stu LEFT JOIN crs ON stu.id = crs.student_id
UNION ALL
SELECT stu.name, crs.course_name FROM stu RIGHT JOIN crs ON stu.id = crs.student_id
WHERE stu.id IS NULL;

自连接（SELF JOIN）

1
2
3

SELECT a.name AS student_name_a, b.name AS student_name_b
FROM stu a
INNER JOIN stu b ON a.age = b.age AND a.id != b.id;

+----------------+----------------+
| student_name_a | student_name_b |
+----------------+----------------+
| Carol          | Alice          |
| Alice          | Carol          |
+----------------+----------------+
2 rows in set

同一张表当两张用，靠两个不同的别名区分。这里找的是「同龄的不同人」：Alice 和 Carol 都是 22 岁。
结果是成对出现两次（A→B 和 B→A）。只想要一份，把 a.id != b.id 换成 a.id < b.id，实测就只剩 Alice | Carol 一行。
典型用途：员工-经理同表（e.manager_id = m.id）、分类的父子关系、比较同类记录。

多表联合操作

子查询

标量子查询：只能返回一行一列

SELECT name
FROM stu
WHERE id = (
    SELECT student_id
    FROM crs
    WHERE course_name = 'Math'
);

1	ERROR 1242 (21000): Subquery returns more than 1 row

原笔记这条查询在自己的示例数据上是跑不通的：Math 有两条记录（student_id 1 和 2），子查询返回 2 行，而 = 只接受单个值。

三种修法，实测前两种可用：

1
2
3

SELECT name FROM stu WHERE id IN (SELECT student_id FROM crs WHERE course_name = 'Math');
SELECT name FROM stu WHERE id = (SELECT MIN(student_id) FROM crs WHERE course_name = 'Math');
SELECT name FROM stu WHERE id = (SELECT student_id FROM crs WHERE course_name = 'Math' LIMIT 1);

1
2
3

IN 写法    -> Alice, Bob            2 行
MIN 写法   -> Alice                 1 行
LIMIT 写法 -> Alice 或 Bob（不保证）1 行

IN 是绝大多数场景的正解。
LIMIT 1 不加 ORDER BY 时取到哪一行不确定，只是「让它别报错」，不是「让它正确」。

IN 子查询
1
2
3
4
5
6
SELECT name
FROM stu
WHERE id IN (
SELECT student_id
FROM crs
);
1
2
3
4
5
6
7
8
+-------+
| name |
+-------+
| Alice |
| Bob |
| Carol |
+-------+
3 rows in set
- 只要子查询里出现过这个 student_id 就命中。
- 如果子查询的结果里含 NULL，NOT IN 会整体失效（一行都查不出来），这是三值逻辑的坑；要用 NOT EXISTS 代替。
EXISTS 子查询
1
2
3
4
5
6
7
SELECT name, age
FROM stu
WHERE EXISTS (
SELECT 1
FROM crs
WHERE stu.id = crs.student_id
);
1
2
3
4
5
6
7
+-------+------+
| name | age |
+-------+------+
| Alice | 22 |
| Bob | 23 |
| Carol | 22 |
+-------+------+
- EXISTS 只关心「有没有」，不关心返回什么，所以里面习惯写 SELECT 1。
- 它是相关子查询：外层每一行都会执行一次内层。数据量大时通常比 IN 更可控（优化器也常把它改写成半连接）。

`SELECT` 列表里的子查询

SELECT name, (
    SELECT COUNT(*)
    FROM crs
    WHERE stu.id = crs.student_id
) AS courses_count
FROM stu;

+-------+---------------+
| name  | courses_count |
+-------+---------------+
| Alice |             1 |
| Bob   |             2 |
| Carol |             1 |
+-------+---------------+

每行都要跑一次子查询，行数一多就慢。同样的需求用 GROUP BY 更合适：

1
2
3

SELECT s.name, COUNT(c.id) AS courses_count
FROM stu s LEFT JOIN crs c ON s.id = c.student_id
GROUP BY s.id, s.name;

派生表（FROM 里的子查询）
1
2
3
4
5
6
SELECT age, COUNT(*) AS students_count
FROM (
SELECT age
FROM stu
) AS age_groups
GROUP BY age;
1
2
3
4
5
6
+------+----------------+
| age | students_count |
+------+----------------+
| 22 | 2 |
| 23 | 1 |
+------+----------------+
- FROM 里的子查询必须有别名（这里叫 age_groups），否则报 ERROR 1248 (42000): Every derived table must have its own alias。
- 这个例子本身是绕路（直接 GROUP BY age 就行），但它演示了「先把中间结果算出来，再基于它继续查」的写法。

联合更新

UPDATE crs
INNER JOIN stu ON crs.student_id = stu.id
SET crs.course_name = 'Mathematics'
WHERE stu.age > 22;

用另一张表的条件来改这张表，MySQL 支持在 UPDATE/DELETE 里写 JOIN。

实测执行后 SELECT * FROM crs：

+----+-------------+------------+
| id | course_name | student_id |
+----+-------------+------------+
|  1 | Math        |          1 |
|  2 | Mathematics |          2 |
|  3 | Mathematics |          2 |
|  4 | History     |          3 |
+----+-------------+------------+

只有 Bob（23 岁，age > 22）的两门课被改名，Alice 和 Carol 的没动。

注意 SET 里要写清「表名.列名」，两张表有同名列时不写前缀会报歧义。

清空数据

三种「清数据」的写法差别很大，选错代价不小：

写法	归类	能回滚	重置自增	触发触发器	需要权限
`DELETE FROM 表`	DML	能（在事务里）	不重置	触发	`DELETE`
`TRUNCATE TABLE 表`	DDL	不能（隐式提交）	重置为 1	不触发	`DROP`
`DROP TABLE 表`	DDL	不能	表没了	不触发	`DROP`

DELETE 语句
1
DELETE FROM table_name;
- 不带 WHERE 就是删全表，逐行删，所以慢、会产生大量 undo/redo、还会触发行触发器。
- 好处是可以回滚，也适合「删一部分」。
- 删完自增计数器不会归零，实测（表里原有 8 行，DELETE FROM students 之后再插一行）：
  1
  | 8 | Zoe | -- 不写 ALTER 的话新行是 8
TRUNCATE 语句
1
TRUNCATE TABLE table_name;
- 快，因为它不逐行删（InnoDB 的做法是 drop 表再重建），所以：不能回滚、不触发行触发器、AUTO_INCREMENT 归 1。
- 有外键指向这张表时会直接被拒（实测 ERROR 1701，见「外键」一节）。
DROP TABLE 语句
1
DROP TABLE table_name;
- 连表结构一起删。想保留结构就别用它。
说明
- 只想把自增值拉回来（DELETE 之后），用：
  1
  ALTER TABLE students AUTO_INCREMENT = 1;
  - 实测有效，但有个限制：设的值不能小于当前表里已有的最大 id，否则会被静默抬到 max(id)+1。表清空后设 1 才真的生效。
- 生产环境清数据之前，先 SELECT COUNT(*) 确认影响范围，再开事务（START TRANSACTION; DELETE ...; 看结果对不对，再 COMMIT）——这也是选 DELETE 而不是 TRUNCATE 的理由之一。
- 想「清空但不留痕迹」还得考虑二进制日志体积：TRUNCATE 只记一条语句，DELETE 全表会记下每一行。

外键索引

外键用来让数据库自己保证「引用的行必须存在」。它只在支持事务的引擎（InnoDB）上有效，MyISAM 会接受语法但不执行约束。

实测建一对父子表：

CREATE TABLE students (
    student_id INT AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (student_id)
);

CREATE TABLE enrollments (
    enrollment_id INT AUTO_INCREMENT,
    student_id INT,
    course_name VARCHAR(100),
    PRIMARY KEY (enrollment_id),
    CONSTRAINT fk_student
    FOREIGN KEY (student_id)
    REFERENCES students(student_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

CONSTRAINT fk_student 给约束起名，方便以后单独删它；不写的话 MySQL 自动生成一个内部名字。
ON DELETE CASCADE / ON UPDATE CASCADE 是「父行删了/改了，子行跟着删/改」。其他可选值：
- RESTRICT（默认，等价于 NO ACTION）：不许动父行。
- SET NULL：把子行的外键列置为 NULL（列必须可空）。
- SET DEFAULT：MySQL 解析但不实现。

外键自动建索引
1
CREATE INDEX idx_student_id ON enrollments (student_id);
- InnoDB 建外键时会自动给外键列建索引，所以这条语句其实是多余的重复索引，实测会给出：
  1
  2
  Warning 1831 Duplicate index 'idx_student_id' defined on the table 'note_demo.enrollments'.
  This is deprecated and will be disallowed in a future release.
- 结论：外键列不用再手工建索引（除非你要改成复合索引），先 SHOW INDEX 看一眼。

约束生效的实测

1	DELETE FROM fk_parent;

1 2	ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`note_demo`.`fk_child`, CONSTRAINT `fk_p` FOREIGN KEY (`parent_id`) REFERENCES `fk_parent` (`id`))

1	TRUNCATE TABLE fk_parent;

1 2	ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`note_demo`.`fk_child`, CONSTRAINT `fk_p`)

有了 ON DELETE CASCADE 之后，删父行会连带删子行，实测：

1 2	DELETE FROM students WHERE name = 'P1'; SELECT * FROM enrollments;

+---------------+------------+-------------+
| enrollment_id | student_id | course_name |
+---------------+------------+-------------+
|             2 |          2 | Bio         |
+---------------+------------+-------------+

ON UPDATE CASCADE 同理，改父表主键会同步改子表外键：

1 2	UPDATE students SET id = 999 WHERE name = 'P2'; SELECT * FROM enrollments;

1	\| 2 \| 999 \| Bio \|

临时关闭检查（导入数据、做批量迁移时用，用完必须打开）：
1
2
3
SET FOREIGN_KEY_CHECKS = 0;
-- ... 批量操作 ...
SET FOREIGN_KEY_CHECKS = 1;
- 它是会话级的。关掉期间插进去的孤儿数据，重新打开后不会被自动清理，只会让后续删除报错。

用 `information_schema` 查外键

SELECT CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME,
       REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'note_demo' AND REFERENCED_TABLE_NAME IS NOT NULL;

排查「为什么删不掉这行」时，这条比翻建表脚本快。

Python 操作 MySQL

简介
- Python 连 MySQL 常见两个库：
  - mysql-connector-python：Oracle 官方出品，纯 Python，导入名是 mysql.connector。
  - PyMySQL：社区维护的纯 Python 驱动，导入名是 pymysql，接口与老的 MySQLdb 兼容，Django/SQLAlchemy 常用它。
- 本机实测版本：pymysql 2.2.8、mysql-connector-python 26.10.0，连接的是 127.0.0.1:3306 上的 MySQL 9.7.4，账号是只授权了 py_demo 库的 pyuser@%。
- 连接 caching_sha2_password 账号时，PyMySQL 需要 cryptography 包才能完成完整认证（本机已具备）。老代码里那种 passwd= 参数名两家库不一样：connector 用 passwd，PyMySQL 用 password。
安装
1
pip install mysql-connector-python
或者：
1
pip install PyMySQL
- 国内网络慢时加镜像：pip install -i https://mirrors.ustc.edu.cn/pypi/web/simple PyMySQL。

示例

`mysql-connector-python`

import mysql.connector

# 创建数据库连接
db_connection = mysql.connector.connect(
    host="127.0.0.1",       # 数据库服务器
    user="pyuser",          # 数据库用户名
    passwd="Aa12345678!",   # 数据库密码
    database="py_demo"      # 数据库名
)

# 创建一个cursor对象来执行操作
cursor = db_connection.cursor()

# 创建一个新表
cursor.execute(
    "CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")

# 插入数据
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("John", "Highway 21")
cursor.execute(sql, val)

# 提交更改，否则无法保存新记录
db_connection.commit()

print(cursor.rowcount, "record inserted.")

# 查询数据
cursor.execute("SELECT * FROM customers")
myresult = cursor.fetchall()  # fetchall() 获取所有记录

for x in myresult:
    print(x)

# 关闭连接
cursor.close()
db_connection.close()

本机实跑输出：

1 2	1 record inserted. ('John', 'Highway 21')

占位符是 %s，不是 Python 的字符串格式化，不要写成 f-string 拼接——那是 SQL 注入的入口。驱动会自己处理转义。
不 commit() 的话连接关掉后插入就消失了，两个库都是默认不自动提交。

`PyMySQL`：基础增删改查

import pymysql

# 配置数据库连接参数
host_name = "127.0.0.1"
db_user = "pyuser"
db_password = "Aa12345678!"
db_name = "py_demo"

# 创建连接
connection = pymysql.connect(
    host=host_name, user=db_user, password=db_password, database=db_name)

# 创建cursor对象
cursor = connection.cursor()

# 创建一个新表
cursor.execute(
    "CREATE TABLE IF NOT EXISTS people "
    "(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), age INT)")

# 插入数据
insert_query = "INSERT INTO people (name, age) VALUES (%s, %s)"
values = ("John Doe", 30)
cursor.execute(insert_query, values)

# 提交到数据库执行
connection.commit()
print(f"{cursor.rowcount} record(s) inserted.")

# 查询数据
cursor.execute("SELECT * FROM people")
for record in cursor.fetchall():
    print(record)

# 更新数据
cursor.execute("UPDATE people SET age = %s WHERE name = %s", (31, "John Doe"))
connection.commit()
print(f"{cursor.rowcount} record(s) updated.")

# 删除数据
cursor.execute("DELETE FROM people WHERE name = %s", ("John Doe",))
connection.commit()
print(f"{cursor.rowcount} record(s) deleted.")

# 关闭
cursor.close()
connection.close()

本机实跑输出：

1 record(s) inserted.
((1, 'John Doe', 30),)
1 record(s) updated.
1 record(s) deleted.

三个 rowcount 都是 1，说明「受影响行数」这个属性在 execute() 之后立刻读才有意义。
删完之后再插，id 会从 2 继续（实测 information_schema.TABLES 里 AUTO_INCREMENT = 2），不会因为表空了就归 1。

「不提交会怎样」实测：插一行不 commit，本会话查得到，rollback() 之后就没了。

cursor.execute("INSERT INTO people (name, age) VALUES ('NoCommit', 1)")
cursor.execute("SELECT name FROM people")   # 看得到 ('NoCommit',)
connection.rollback()
cursor.execute("SELECT name FROM people")   # 空

`PyMySQL`：字典游标与 `with` 写法

CREATE TABLE `users` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `email` varchar(255) COLLATE utf8_bin NOT NULL,
    `password` varchar(255) COLLATE utf8_bin NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1;

这段老 DDL 在 9.7 上能建成功，但埋了两个雷，实测 information_schema.COLUMNS：
1
2
('email', 'utf8mb3', 'utf8mb3_bin')
('password', 'utf8mb3', 'utf8mb3_bin')
- utf8_bin 被解析成了 utf8mb3_bin，于是这两列是 3 字节的 utf8mb3，而表默认却是 utf8mb4。往这两列写 emoji 会报 ERROR 1366: Incorrect string value: '\xF0\x9F\x90\xA1' for column 's'。
- int(11) 的显示宽度也已经被丢弃（SHOW CREATE TABLE 里是 int）。
- 正确写法是把列上的 COLLATE utf8_bin 改成 COLLATE utf8mb4_bin（或者干脆不写，继承表默认）。

配套的 Python 代码：

import pymysql.cursors

# 连接数据库
connection = pymysql.connect(host='127.0.0.1',        # 数据库服务器地址
                             user='pyuser',           # 数据库登录用户名
                             password='Aa12345678!',  # 数据库登录密码
                             database='py_demo',      # 要操作的数据库名
                             charset='utf8mb4',       # 数据库编码
                             cursorclass=pymysql.cursors.DictCursor)  # 字典游标

# with 语句自动管理资源，确保使用完毕后正确关闭连接
with connection:
    with connection.cursor() as cursor:
        sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
        cursor.execute(sql, ('webmaster@python.org', 'very-secret'))

    # PyMySQL 默认不是自动提交，需要手动 commit()
    connection.commit()

    with connection.cursor() as cursor:
        sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
        cursor.execute(sql, ('webmaster@python.org',))
        result = cursor.fetchone()
        print(result)

本机实跑输出：

1	{'id': 1, 'password': 'very-secret'}

DictCursor 让每行变成 dict，取列靠名字而不是下标，实测整行是：
1
{'id': 1, 'email': 'webmaster@python.org', 'password': 'very-secret'}
一个真实的坑：with connection: 结束时会关闭连接，所以在 with 块之后再拿游标用会报 InterfaceError。原笔记那种「with 里面套一层再 commit」是对的，别在块外继续查询。
生产代码建议再补两点：connect_timeout=5（否则网络异常时会挂很久）、用 contextlib.closing 或连接池（如 DBUtils、SQLAlchemy）而不是手工复用单个连接。

数据库的备份与恢复

mysqldump 是逻辑备份工具，导出一串能重建库表的 SQL。本机版本 mysqldump Ver 9.7.4。

备份数据库

备份单个库（导出到文件）：
1
mysqldump -u username -p mydatabase > mydatabase_backup.sql
- -u 后是用户名，-p 表示「执行后提示输密码」（-p 和密码之间不能有空格，写成 -pABC 是直接给密码，会进 shell 历史和进程列表，不推荐）。
- 也可以用 --result-file=/path/x.sql 让 mysqldump 自己写文件，避免 shell 重定向踩到换行/编码问题。

备份库里的特定表：

1	mysqldump -u username -p mydatabase table1 table2 > tables_backup.sql

备份多个数据库：
1
mysqldump -u username -p --databases database1 database2 > multiple_databases_backup.sql
- 带 --databases 时导出文件里会包含 CREATE DATABASE 和 USE 语句；不带就只是表和数据，恢复时必须先自己选好目标库。

备份所有数据库：

1	mysqldump -u username -p --all-databases > all_databases_backup.sql

常用调整参数，本机实测可用：
- --no-data：只导结构。
- --no-create-info：只导数据。
- --routines / --triggers / --events：把存储过程、函数、定时任务一起带上（实测 --routines 之后备份里出现 CREATE DEFINER=...PROCEDURE AddEmployee）。
- --single-transaction：对 InnoDB 做一致性快照备份，不锁表。
- --where="条件"：只导满足条件的行，实测 --where="age=22" 只导出 Alice 和 Carol 两行。
- --all-databases：整台服务器。
- --result-file=路径：由 mysqldump 自己写文件。
- --add-drop-table：默认开启，在建表前加 DROP TABLE IF EXISTS。
- --column-statistics=0：跨版本备份时不去查目标实例上可能不存在的统计信息表。
- --source-data[=#]：把二进制日志位置写进备份（mysqldump --help 里能看到该选项，本机未验证输出内容）。

9.x 必踩的坑：GTID 会写进备份文件

本机开了 GTID，直接 mysqldump --databases 会看到三行告警：

Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of
all transactions, even those changed suppressed parts of the database. If you don't want
to restore GTIDs, pass --set-gtid-purged=OFF. ...
Warning: A dump from a server that has GTIDs enabled will by default include the GTIDs of
all transactions, even those that were executed during its extraction and might not be
represented in the dumped data. This might result in an inconsistent data dump.
In order to ensure a consistent backup of the database, pass --single-transaction or
--lock-all-tables or --source-data.

备份文件里因此多出这两句：

1 2	SET @@SESSION.SQL_LOG_BIN= 0; SET @@GLOBAL.GTID_PURGED=/!80000 '+'/ '5005dec0-d790-11f1-9034-00155dc0b3b0:1-155';

拿这种文件往另一个实例里灌，实测直接失败：

1 2	ERROR 3546 (HY000) at line 24: @@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with @@GLOBAL.GTID_EXECUTED

解法是显式关掉：
1
2
mysqldump -u root -p --set-gtid-purged=OFF --single-transaction \
--databases mydatabase > mydatabase_backup.sql
- 实测加了这个参数之后备份文件里 GTID_PURGED 出现次数为 0，恢复一路顺利。

恢复数据库

恢复单个库（文件里没有 CREATE DATABASE 时）：
1
mysql -u username -p mydatabase < mydatabase_backup.sql
- 目标库必须已经存在，否则报 ERROR 1049 (42000): Unknown database 'mydatabase'（实测就是这么报的）。
恢复用 --databases 导出的备份（文件自带 CREATE DATABASE / USE）：
1
mysql -u username -p < all_databases_backup.sql
- 这时不要在命令行上指定库名，让文件里的 USE 决定去向。

完整演练（本机实测）：

mysqldump -u root -p --set-gtid-purged=OFF --single-transaction \
  --databases note_demo > /tmp/backup.sql          # 637 行、约 24K
mysql -u root -p -e "CREATE DATABASE note_demo_r;"
sed 's/note_demo/note_demo_r/g' /tmp/backup.sql | mysql -u root -p
mysql -u root -p --table -e "SELECT id, name FROM note_demo_r.stu ORDER BY id;"

+----+-------+
| id | name  |
+----+-------+
|  1 | Alice |
|  2 | Bob   |
|  3 | Carol |
+----+-------+

表、视图、数据都回来了（information_schema 里能看到恢复出来的视图 FullNames）。

备份文件里会带上对象的定义者，实测这份备份里 DEFINER= 出现 2 次（视图和存储过程各一次）：
1
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost`
- 换一个没有 root@localhost 的账号或实例恢复时，可能报 ERROR 1449: The user specified as a definer does not exist。常见处理是恢复前把 DEFINER=…`` 子句 sed 掉，或者在目标实例上补建同名账号。

注意事项
- 备份账号至少要有这些权限：SELECT、LOCK TABLES、SHOW VIEW、RELOAD（用 --single-transaction 时可省 LOCK TABLES）、TRIGGER、PROCESS。用 root 图省事，但最好单独建一个只做备份的账号。
- InnoDB 表用 --single-transaction，别用 --lock-all-tables（那会把整库锁住）。
- 备份很大时先估算磁盘：本机 note_demo 全库备份 637 行、约 24 KB，生产上可能是几十 GB。
- mysqldump 是逻辑备份，恢复要重放 SQL，大库很慢。要快速恢复得配合物理备份（MySQL Enterprise Backup、xtrabackup）或直接拷数据目录（必须先干净停服）。
- 恢复前先在一个空实例上演练一遍，验证「备份真的能恢复」；没验证过的备份等于没有备份。
- 二进制日志（binlog）不在 mysqldump 的范围内，要做「恢复到任意时间点」得另外备份 binlog（--source-data=2 会把当前位置写进备份文件头部）。

MySQL 简介

MySQL 特点

MySQL 分类

版本分类

存储引擎分类

按照部署方式分类

按照使用方式分类

按照许可证分类

SQL 分类

MySQL 安装

数据类型

数字类型

日期和时间类型

字符串类型

空间类型

JSON 类型

字段修饰

字符集及存储引擎

字符集（Character Set）

存储引擎（Storage Engine）

索引

简介

分类

INDEX（普通索引）

UNIQUE（唯一索引）

PRIMARY KEY（主键索引）

FULLTEXT（全文索引）

B-Tree 索引

HASH 索引

R-Tree（空间索引）

Composite（复合索引）

示例一

示例二

注意事项

数据定义语言(DDL)

简介

CREATE

建库与选库

建表

建索引

建视图

建存储过程

ALTER

加列（三种位置）

改列类型：MODIFY 与 CHANGE

删列

删索引再建

改视图

改存储过程

DROP

TRUNCATE

COMMENT

RENAME

数据操作语言(DML)

INSERT

指定列插入

省略某些列（吃默认值或 NULL）

一次插入多行

从别的表灌数据（INSERT ... SELECT）

只插默认值：DEFAULT VALUES 在 MySQL 里不存在

主键/唯一键冲突时的三种处理（原笔记没提，但天天用）

UPDATE

改单行的某一列

用列的旧值算新值

一次改多列

按条件批量改

用 LIMIT 限制改动行数

DELETE

数据查询语言(DQL)

基础查询

选择特定列

排除重复记录

条件查询

排序结果

限制返回的结果数量

计算列

聚合函数

分组结果

使用 HAVING 子句过滤分组

连接查询

改列类型：`MODIFY` 与 `CHANGE`

从别的表灌数据（`INSERT ... SELECT`）

只插默认值：`DEFAULT VALUES` 在 MySQL 里不存在

用 `LIMIT` 限制改动行数

`BEGIN` 或 `START TRANSACTION`

`COMMIT`

`ROLLBACK`

`SAVEPOINT`

`ROLLBACK TO SAVEPOINT`

`RELEASE SAVEPOINT`

`IN` 子查询

`EXISTS` 子查询

`SELECT` 列表里的子查询

派生表（`FROM` 里的子查询）

`DELETE` 语句

`TRUNCATE` 语句

`DROP TABLE` 语句

用 `information_schema` 查外键

`mysql-connector-python`

`PyMySQL`：基础增删改查

`PyMySQL`：字典游标与 `with` 写法