18.1 系统数据库 · 波仔的 AI 编程实战课

📄 一句话概括

MySQL 一装好就有四个系统库：information_schema 管结构、mysql 管权限、 performance_schema 管性能、sys 是它的人话版。 本沙箱一个都没有：结构账本是 sqlite_master 那张表，外加一组 pragma 命令。 「先查账本再动手」是接陌生库的第一条纪律——比问人快，也比猜准。

📝 本章和前面几章的关系

这一节几乎没有新语法，讲的是「元数据去哪儿查」。 但它决定了你排查问题的速度： 知道账本在哪，一眼就能看出「这索引是谁建的、这字段改了没」。 两边差得比较远：MySQL 用「库 + 表」组织元数据（information_schema 里的视图）， SQLite 用「一张表 + 一组 pragma」。 所以这一节的写法要分开记。

一、MySQL 的四个系统库

登录 MySQL 敲一句 show databases， 除了你自己建的库，一定还会看到四个名字很怪的。 它们不是给人用的，是给数据库自己用的。 第一个 information_schema：结构信息的账本。 有哪些库、哪些表、每张表哪些列、哪些索引、哪些视图、哪些触发器，全在这里。 它里面的东西全是视图，不是真表——所以只能查，不能改。 第二个 mysql：用户、密码、权限。 第 16 章讲的 create user、grant、revoke，动的就是这个库里的表。 它也是「不该手改」的典型案例。 第三个 performance_schema：运行时的性能数据。 每条语句跑了多久、锁等了多久、内存分给了谁，都记在这。 它是「事后复盘慢查询」的主要证据来源。 第四个 sys：把上面那个库包装成一堆好读的视图。 performance_schema 里的表列名很长、单位也绕， sys 直接给你「哪条 SQL 最慢、哪个表访问最多」这种一句话结论。 分工可以这么记：结构看 information_schema；权限看 mysql；性能看后两个。 四个库，四件事，互不重叠。

二、本沙箱一句都跑不了

这几条都是纯 MySQL 语法，沙箱连词都不认识。 Demo 里三条报错可以分成两类： show databases、use mysql 报的是 syntax error—— show 和 use 在 SQLite 里根本不是关键字。 select … from information_schema.tables 报的是「没这张表」——注意这个区别： 它说明那个写法本身是合法的 SQL， 只是沙箱里没有这个库。 语法错和缺对象，是两种完全不同的错，排查时别搞混。

三、沙箱的账本叫 sqlite_master

SQLite 只有一个库文件，所以它不需要 show databases。 它的全部结构记在一张叫 sqlite_master 的表里，建表、建索引、建视图都会往里加一行。 这张表有五列，Demo 里那三行正好一表一索引一视图：

列	意思	备注
`type`	表／索引／视图／触发器	四种取值
`name`	对象自己的名字	索引名、表名
`tbl_name`	挂在哪张表上	表就是自己，视图也是自己
`rootpage`	存在第几页	视图是 0，不占存储
`sql`	当初建它的那句原话	整个库的结构靠它记着

rootpage 那列最容易被忽略： 它告诉你这条记录在文件的第几页。 视图是 0，因为视图不存数据，只在查询时展开； 表和索引都是正数。 这个数在做文件级排查时会用到，平时不用管。 sql 那列最有价值。 Demo 里单独打了一句视图的原话： create view v1 as select id, a from t1，一字不改。 表和索引也一样。 这还解释了一件 SQLite 的特色： 它改表结构的语句很少，只有 alter table 加列和改名两种。 因为「改结构」等于「改这句原话」 —— 原话里可能藏着约束、默认值、排序规则，改起来容易出错。 MySQL 的 alter table 花样多得多，代价是要重建表。

四、一条 pragma 顶一张表

information_schema 里查列要查 columns 这张视图， SQLite 里用一句 pragma：pragma table_info(表名)。 它给六列，依次是： 序号、列名、类型、能不能空、默认值、是不是主键。 Demo 里 id 那行最后一列是 1， 说明它是主键；a 那行是 1 在第 4 列， 说明它 not null。 这两个位置很容易看串，得对着列名读。 看索引用 pragma index_list(表名)，五列：序号、索引名、是否唯一、来源、是否部分索引。 第四列 origin 最好用，它告诉你索引是怎么来的，三个值：c 是 create index 手建的； u 是 unique 约束自动建的； pk 是主键自动建的。 第 12 章删索引时踩过的那个坑，靠这一列就能提前看出来—— origin 是 u 或 pk 的索引删不掉。 看挂了几个库用 pragma database_list， 三列：序号、库名、文件路径。 内存库的路径是空的，因为它不在磁盘上； attach 挂上来的库会显示真实路径。 第 17 章做跨库备份时用的就是这个。

五、把这几样拼成 information_schema

「列一遍这个库里所有的表」，MySQL 一句话：

SELECT table_name FROM information_schema.tables
WHERE table_schema = 'school';

沙箱里要从 sqlite_master 里筛，而且必须滤掉系统表：

SELECT name FROM sqlite_master
WHERE type = 'table' AND name NOT LIKE 'sqlite_%'

not like 'sqlite_%' 不能省。 SQLite 自己会建 sqlite_sequence（自增用的）之类的表， 它们也在账本里，但不算你的业务表。 MySQL 那边对应的是「系统库和业务库分开」， 所以不用滤。 「列一遍所有的列」就麻烦一点—— 得把 sqlite_master 和 pragma_table_info 连起来。 Demo 里那句 join pragma_table_info(m.name) 是关键： pragma 后面加下划线、当成函数写在 from 里，它就能当表用。 这叫表值函数，是 SQLite 特有的写法，MySQL 里没有。 这一句的价值在于「一次看全库」： 接手一个几十张表的库，跑一遍就知道每张表有哪些列、什么类型。 比一张一张 pragma table_info 快得多。

六、系统库为什么不能手改

MySQL 里改权限的老办法是直接 update mysql.user， 然后 flush privileges。 这个做法现在明确不推荐—— 版本一升级，表结构就变，你那条语句直接报废； 而且很容易漏改关联的表，留下一个「半生效」的权限状态。 正确做法是用 grant 和 revoke， 让数据库自己去改那几张表。 它知道该改哪几处、该刷哪个缓存。 第 16 章就是这么讲的。 沙箱的 sqlite_master 更狠，直接只读。 Demo 里 update 和 delete 两句都被拦下， 报的是 table sqlite_master may not be modified。 这个保护由一个开关控制：pragma writable_schema，默认 0。 把它设成 1 就能改， 但改完数据库基本就废了—— 因为账本和真实数据会脱节，之后每一句查询都可能读到不存在的东西。 除非你在做数据恢复，否则永远别碰它。

七、动手 Demo：从「查账本」开始

Demo 建一张表、一个索引、一个视图，然后从三个角度查这本账： 账本本身、某张表的列、全库的表清单。 这三段对应 MySQL 的：select * from information_schema.tables、 select * from information_schema.columns where table_name = 't1'、以及「列一遍所有表」。

import sqlite3

c = sqlite3.connect(":memory:")
c.execute("CREATE TABLE t1(id INTEGER PRIMARY KEY, a TEXT NOT NULL)")
c.execute("CREATE INDEX idx_t1_a ON t1(a)")
c.execute("CREATE VIEW v1 AS SELECT id, a FROM t1")
c.commit()

# 1) 账本里有什么
sql = ("SELECT type, name, tbl_name, rootpage, length(sql) "
       "FROM sqlite_master")
for row in c.execute(sql):
    print(row)

# 2) 一句 pragma 看一张表的列
for row in c.execute("PRAGMA table_info(t1)"):
    print(row)

# 3) 拼出 information_schema.tables 的效果
sql = ("SELECT name FROM sqlite_master "
       "WHERE type = 'table' AND name NOT LIKE 'sqlite_%'")
print(c.execute(sql).fetchall())

实跑输出：

--------------------------------------------------------------------
第八十一课：系统数据库——数据库自己也有一本账
--------------------------------------------------------------------

  MySQL 一装好就有四个库
  **登录 MySQL 敲 `show databases`**，**除了自己建的库，还会看到四个。**
  **`information_schema`**：**结构信息的账本。** **有哪些库、哪些表、哪些列，全在这。**
  **它里面全是视图，没有真表**——**所以只能查，不能改。**
  **`mysql`**：**用户、密码、权限。** **第 16 章的 `create user`、`grant` 改的就是它。**
  **`performance_schema`**：**运行时的性能数据。** **语句跑多久、锁等多久，都记在这。**
  **`sys`**：**把上面那个库包装成一堆好读的视图**，**比如「哪条 SQL 最慢」一句就查出来。**
  **分工可以这么记**：**结构看 `information_schema`**；**权限看 `mysql`**；**性能看后两个。**

  **本沙箱一个也没有**，**因为这些是 MySQL 自己的东西。** **SQLite 的设计里，账本只有一张表。**

  原码先试着跑一句
  SQL> SHOW DATABASES
  报错：near "SHOW": syntax error
  SQL> USE mysql
  报错：near "USE": syntax error
  SQL> SELECT table_name FROM information_schema.tables LIMIT 3
  报错：no such table: information_schema.tables
  **前两句是纯 MySQL 语法**，**沙箱连词都不认识。**
  **第三句报的不是语法错，是「没这张表」**——**说明那个写法本身是合法的**，只是沙箱里没有这个库。

  沙箱的账本叫 sqlite_master
  **建一张表、一个索引、一个视图**，**然后查这本账：**
  SQL> SELECT type, name, tbl_name, rootpage, length(sql) AS 语句长度 FROM sqlite_master
  type      name         tbl_name     rootpage     语句长度
  ----------------------------------------------------------
  table     t1           t1           2            56
  index     idx_t1_a     t1           3            30
  view      v1           v1           0            38
  （3 行）
  **五列的意思**：
  **`type`**：**这一行是表、索引还是视图。**
  **`name`**：**对象自己的名字。**
  **`tbl_name`**：**它挂在哪张表上。** **索引、触发器才有意义，表就是自己。**
  **`rootpage`**：**记在第几页。** **视图是 `0`，因为它不占存储。**
  **`sql`**：**当初建它的那句原话。** **整个库的结构就靠它记着。**

  看一句原话
  | CREATE VIEW v1 AS SELECT id, a FROM t1
  **视图的 `sql` 就是当初那句 `create view`**，**一字不改。** **表和索引也一样。**
  **这也解释了一件事**：**SQLite 改表结构的语句很少。**
  **只有 `alter table` 加列和改名两种。** **因为改结构等于改这句原话，不好糊弄。**

  一条 pragma 顶一张表
  **`information_schema` 里查列要查 `columns` 表**，**沙箱里用一句 `pragma`：**
  SQL> PRAGMA table_info(t1)
  cid     name     type        notnull     dflt_value     pk
  -------------------------------------------------------------------
  0       id       INTEGER     0           NULL           1
  1       a        TEXT        1           NULL           0
  （2 行）
  **六列依次是**：**序号、列名、类型、能不能空、默认值、是否主键。**
  **`pragma table_info` 只能看一张表**，**想一次看全库得配合 `sqlite_master`**，**下面会写。**

  SQL> PRAGMA index_list(t1)
  seq     name         unique     origin     partial
  ---------------------------------------------------
  0       idx_t1_a     0          c          0
  （1 行）
  **五列**：**序号、索引名、是否唯一、来源、是否部分索引。**
  **第四列 `origin` 说明索引怎么来的**，**三个值**：
  **`c` 是 `create index` 手建的**；**`u` 是 `unique` 约束建的**；**`pk` 是主键建的。**

  SQL> PRAGMA database_list
  seq     name     file
  ----------------------
  0       main
  （1 行）
  **给出「现在挂着几个库」**：**序号、库名、文件路径。**
  **内存库路径是空的**，**因为它不在磁盘上。** **`attach` 挂上来的库会显示真实路径。**

  把这几样拼成 information_schema
  **想列一遍这个库里所有的表**，**MySQL 一句话就行**：
  MySQL 原码（本沙箱不执行）：
  | SELECT table_name FROM information_schema.tables
  | WHERE table_schema = 'school';
  **沙箱里要从 `sqlite_master` 里筛**：
  SQL> SELECT name AS table_name FROM sqlite_master
       WHERE type = 'table' AND name NOT LIKE 'sqlite_%'
  table_name
  -----------
  t1
  （1 行）
  **`not like 'sqlite_%'` 是滤掉系统自己建的表**，**比如自增用的 `sqlite_sequence`。**

  **想列一遍所有的列就麻烦一点**——**得把 `sqlite_master` 和 `pragma_table_info` 连起来。**
  SQL> SELECT m.name AS 表, p.cid AS 序, p.name AS 列, p.type AS 类型 FROM sqlite_master m
       JOIN pragma_table_info(m.name) p WHERE m.type = 'table' AND m.name NOT LIKE 'sqlite_%'
       ORDER BY m.name, p.cid
  表     序     列     类型
  -----------------------------
  t1     0      id     INTEGER
  t1     1      a      TEXT
  （2 行）
  **`pragma_table_info(m.name)` 能当表用**，**这是 SQLite 特有的表值函数写法**，**MySQL 里没有。**

  系统库为什么不能手改
  **MySQL 里改权限的老办法是直接 `update mysql.user`**，**再 `flush privileges`。**
  **这个做法现在明确不推荐**——**版本一升级表结构就变，你的语句就废了。**
  **正确做法是用 `grant` 和 `revoke`**，**让数据库自己去改那张表。**

  **沙箱的 `sqlite_master` 更狠，直接只读**：
  SQL> UPDATE sqlite_master SET name = 'x' WHERE type = 'table'
  报错：table sqlite_master may not be modified
  SQL> DELETE FROM sqlite_master WHERE type = 'table'
  报错：table sqlite_master may not be modified
  **两句都是 `table sqlite_master may not be modified`。**
  **这个保护由一个开关控制**：`pragma writable_schema`，**默认 `0`。**
  **设成 `1` 就能改**，**但改完库基本就废了**，**除非你确切知道自己在写什么。**

  这一节真正要记住的
  **「数据库自己也有账本」这件事比语法重要。**
  **因为「这表怎么建的、这索引哪来的」这类问题**，**答案都在这本账里**，**不用去翻建表脚本。**
  **类型、默认值、约束也都在。**
  **以后接手一个陌生的库**，**第一件事就是查这本账**。 **这比问人快，也比猜准。**

账本三行，rootpage 分别是 2、3、0。 t1 是表（占第 2 页）、 idx_t1_a 是索引（占第 3 页）、v1 是视图（不占页，所以是 0）。 length(sql) 那列是「原话有多长」。 表是 56、索引 30、视图 38——这三句原话就是整个库的全部结构信息。 pragma table_info(t1) 给了两行六列。 id 那行最后一列是 1（主键）， a 那行第 4 列是 1（not null）。 注意「能不能空」和「是不是主键」在两头，中间隔着类型和默认值。 最后那句筛出 t1 一张表。 如果这里不写 not like 'sqlite_%'， 自增表、sqlite_stat1 这些也会冒出来。

30 秒实验

把 Demo 第一段那句 length(sql) 换成 sql，输出会长什么样？
给 t1 加上 unique 约束再跑 pragma index_list，看 origin 那列变成什么。
pragma writable_schema 设成 1 之后改一行 name，再用 pragma table_info 查那张表，会发生什么？

常见误区

以为 information_schema 是「真的库」。 它里面全是视图，只能查不能改，改结构要用 alter table。
直接 update mysql.user 改权限。 版本一升级表结构就变，应该用 grant／revoke。
查 SQLite 表清单时不滤 sqlite_%。 系统表会混进业务表清单里，按它生成代码或做迁移就会出错。

自测三问（点开对答案）

1. MySQL 的四个系统库分别管什么？ —— `information_schema` 管结构元数据，`mysql` 管用户与权限， `performance_schema` 管运行时性能数据，`sys` 是它的易读视图封装。 2. SQLite 的结构信息记在哪？ —— 记在 `sqlite_master` 这一张表里，五列：`type`／`name`／`tbl_name`／`rootpage`／`sql`。列和索引的细节再用 `pragma table_info`、`pragma index_list` 查。 3. 为什么「语法错」和「没这张表」要分清？ —— 语法错说明写法本身不合法，得改语句；「没这张表」说明语句合法、只是对象不存在，要查库名、表名或权限。混在一起排查会走弯路。

账本会查了，下一篇讲一个天天用、也天天写错的语法：18.2 分页查询。

☐ （本篇完，下一篇 18.2 分页查询）