字符集(character set)决定一段字节能不能被解释成字符,排序规则(collation)决定两个已经存好的字符串算不算相等、谁排在前面。库级的这两个参数会作为默认值往表继承,再往列继承,所以很多“查询结果莫名其妙”的根子在建库那句 SQL 上。
建库语句只有两个可选参数
CREATE DATABASE [IF NOT EXISTS] db_name
[[DEFAULT] CHARACTER SET charset_name]
[[DEFAULT] COLLATE collation_name];方括号是可选项,DEFAULT 可写可不写,写了意思一样。关键字大小写不敏感,create database 和 CREATE DATABASE 等价,文档习惯写大写。三个最小案例:
CREATE DATABASE db1;
CREATE DATABASE db2 CHARSET=utf8;
CREATE DATABASE db3 CHARSET=utf8 COLLATE utf8_general_ci;db1 不指定任何参数,取服务器当前的 character_set_server 和 collation_server。课件说“不指定时系统默认字符集 utf8、校验规则 utf8_general_ci”,这是那台机器在 my.cnf 里写了 character-set-server=utf8 的结果,不是 MySQL 的通用默认值。5.7 的 character_set_server 默认是 latin1;8.0 改成 utf8mb4,排序规则是 utf8mb4_0900_ai_ci。换一台没改过配置的机器,db1 的默认值就跟着变。
IF NOT EXISTS 只压掉“库已存在”这一个错误,库名写错、权限不足都照常报。建库时把字符集和排序规则写全,比依赖默认值可靠。
utf8 这个名字在 MySQL 里只有三个字节
SHOW VARIABLES LIKE 'character_set_database';
SHOW VARIABLES LIKE 'collation_database';
SHOW CHARSET;
SHOW COLLATION;SHOW CHARSET 的 Maxlen 列给出硬限制:utf8 是 3,utf8mb4 是 4。MySQL 的 utf8 是 utf8mb3 的别名,官方文档把它标成废弃别名;汉字大多是 3 字节,勉强够用,emoji 和部分生僻字是 4 字节,utf8mb3 读不了。严格模式下写入会报 Incorrect string value,非严格模式下变成 ?。新库一律用 utf8mb4。
排序规则的名字拆开后缀就能读:
| 后缀 | 含义 |
|---|---|
_ci | 不区分大小写(case insensitive) |
_cs | 区分大小写 |
_bin | 按字节比较,不做语言层面的归一 |
_ai / _as | 不区分 / 区分重音 |
utf8_general_ci 是“utf8 字符集 + general 通用规则 + 不区分大小写”。8.0 新建库推荐 utf8mb4_0900_ai_ci(基于 Unicode 9.0 的规则,比较更细);需要在 5.7 和 8.0 之间通用,就写 utf8mb4_general_ci。
character_set_database 和 collation_database 跟着当前默认库走,切个库值就变,它不是“这个库的定义”。要看库的定义,用后面讲的 SHOW CREATE DATABASE。
两个库、同样四行数据,查出不同结果
CREATE DATABASE test1 COLLATE utf8_general_ci;
CREATE DATABASE test2 COLLATE utf8_bin;
USE test1;
CREATE TABLE person (name varchar(20));
INSERT INTO person VALUES ('a'), ('A'), ('b'), ('B');
USE test2;
CREATE TABLE person (name varchar(20));
INSERT INTO person VALUES ('a'), ('A'), ('b'), ('B');只写 COLLATE 时,字符集从排序规则的名字推断,两个库都是 utf8,区别只在比较方式。课件的这段示例里 use test2 后面漏了分号,在 mysql 客户端里会继续显示 -> 等你把语句补完,把下一行 create table 当成同一条语句的一部分,直接报语法错误。
USE test1;
SELECT * FROM person WHERE name = 'a';
-- a、A 两行
USE test2;
SELECT * FROM person WHERE name = 'a';
-- 只有 a 一行课件这段示例的输出有一处对不上:test2 的结果框里只有一行 a,下面写的却是 2 rows in set。_bin 下 'a' 与 'A' 字节不同,只能命中一行,那个 2 rows 是从上一段复制过来没改的。
排序结果也跟着变:
USE test2;
SELECT * FROM person ORDER BY name;
-- A、B、a、b,大写字母的字节值更小test1 那边看到的是 a、A、b、B。但在 _ci 下 a 和 A 被判为相等,相等键之间的先后顺序 SQL 并不保证,换个版本或执行计划,完全可能变成 A、a、B、b。把一次输出当成确定规律,是这一节最容易带偏的地方。
排序规则还决定“什么算重复”。给两个表的 name 加唯一索引:
ALTER TABLE test1.person ADD UNIQUE (name);
-- ERROR 1062 (23000): Duplicate entry 'A' for key 'name'
ALTER TABLE test2.person ADD UNIQUE (name);
-- 成功,四行都被当成不同的值GROUP BY 和 DISTINCT 同理,_ci 下 a 与 A 归成一组,_bin 下是两组。做用户名系统时 _ci 能顺手挡住 Admin 和 admin 的重复注册;做区分大小写的标识符系统就得用 _bin 或 _cs。
SHOW CREATE DATABASE 里的四个细节
SHOW DATABASES;
SHOW CREATE DATABASE mytest;输出大致是:
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| mytest | CREATE DATABASE `mytest` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+课件的这张输出首列写成了 mysql,语句体却是 CREATE DATABASE `mytest` ,复制粘贴时串了行,首列应当是 mytest。
反引号包的是标识符,不是装饰。库名撞上 order、select 这类保留字时,不加反引号会直接语法错误;单引号包字符串,两种引号不要混用。
/*!40100 DEFAULT CHARACTER SET utf8 */ 是版本化注释,不是普通注释。服务器版本大于等于 4.01.00 时才执行里面的内容,低于就整体跳过。备份文件里到处是这种写法,删掉会改变导入行为。
SHOW DATABASES 里除了自己建的库,还有 information_schema、mysql、performance_schema、sys。前两个存元数据和账号权限,后两个是性能采集与视图封装,都不该放业务表。库名最长 64 个字符,只能用字母、数字、下划线、美元符号,不能以数字开头。Linux 上库名和表名默认区分大小写(lower_case_table_names=0),Windows 默认不区分,同一个项目从 Windows 搬到 Linux 报“表不存在”,多半是大小写没统一。
想看所有库的字符集,不用一个个查:
SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME NOT IN ('mysql','sys','information_schema','performance_schema');ALTER DATABASE 改的是默认值,不是旧表
ALTER DATABASE mytest CHARSET=gbk;
SHOW CREATE DATABASE mytest;执行完返回 Query OK, 1 row affected,再看建库语句,末尾确实变成 gbk。但库里已经建好的表不会跟着转码,已有数据也不会。这条语句改的只是“以后新建对象用什么默认值”。
要让旧表跟着转,得逐表执行:
ALTER TABLE person CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;CONVERT TO 会重建表并转换已有数据,大表上锁表、吃磁盘,执行前先备份。如果数据在写入时就已经用错编码,CONVERT TO 救不回来,只能导出重导。
改排序规则也走 ALTER DATABASE:ALTER DATABASE mytest COLLATE utf8mb4_general_ci;,同样只管之后新建的对象。
连接层还有四个变量:
SHOW VARIABLES LIKE 'character_set_client';
SHOW VARIABLES LIKE 'character_set_connection';
SHOW VARIABLES LIKE 'character_set_results';
SHOW VARIABLES LIKE 'character_set_server';client 是服务器按什么编码解释客户端发来的字节,connection 是服务器内部处理用的,results 是返回给客户端的编码。SET NAMES utf8mb4; 一次改前三个,只影响当前会话。乱码的排查顺序是写入、存储、读取三层依次确认,只改一层往往只是把乱码换了一种样子。
DROP DATABASE [IF EXISTS] db_name; 会连库目录一起删掉,里面的表和数据级联消失,没有回收站。执行前确认库名,IF EXISTS 只防“库不存在”,不防“库名打错”。
mysqldump 的参数决定恢复时要补几步
整库备份:
mysqldump -P3306 -u root -p -B mytest > /backup/mytest.sql-B 是 --databases,导出文件开头会带 CREATE DATABASE 和 USE,恢复时不用手动建库。单表备份不能带 -B,否则表名会被当成库名:
mysqldump -u root -p mytest person > /backup/person.sql多个库就在 -B 后面列名字:
mysqldump -u root -p -B db1 db2 db3 > /backup/multi.sql课件写的 -p 密码 中间带空格,是个会直接改变语义的错误:-p 后面的内容会被当成要备份的库名,报 Unknown database '密码'。要么写 -p123456,要么只写 -p 交互输入,后者不会把密码留在 shell 历史里。另外 -P 是大写、表示端口,小写 -p 是密码,两个写反时错误信息常常看起来像密码错。
恢复有两种写法:
mysql -u root -p mytest < /backup/mytest.sqlSOURCE /backup/mytest.sql;SOURCE 是客户端命令,不是 SQL,不必加分号;Windows 路径写成 D:/backup/mytest.sql,反斜杠会被当转义符。备份时没带 -B,恢复前要先 CREATE DATABASE 再 USE,否则报 1046。
mysqldump 默认导出触发器,存储过程和事件要显式加参数:
mysqldump -u root -p --routines --events --triggers -B mytest > /backup/mytest.sqlInnoDB 表可以加 --single-transaction 拿一致性快照且不锁表,MyISAM 不受这个参数保护。备份完先看文件:head -n 20 /backup/mytest.sql,开头有建库语句、大小不是 0,再找个临时库真导一次,比看命令返回 0 可靠。
谁连着这台库
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;
SHOW PROCESSLIST\G输出里 Id 是连接号,User 是账号,Host 是来源地址,db 是当前默认库,Command 为 Sleep 表示空闲,Time 是这条连接处于当前状态多少秒,Info 是正在执行的语句。SHOW PROCESSLIST 会截断长语句,SHOW FULL PROCESSLIST 显示完整内容,\G 让每行竖着排。
出现来源不明的 Host,或者大量长时间 Sleep 的连接,先当异常处理:可能是密码泄露后被外部程序连上,也可能只是应用的连接池没回收。8.0 里没有 PROCESS 权限的账号只能看到自己的线程,看不到别人,排查前先确认权限。
还没有评论 — 第一条由你来留。