从用户的视角观察koishi的database业务，以及一些个人的实践经验（也许有用？）

VincentZyu233 · 2025 年10 月 6 日 19:31

从用户的视角观察koishi的database业务，以及一些个人的实践经验（也许有用？）
（友善交流为主，希望互相交换意见，如果有错误或者其他不足请指出）

点击这里直接去github查看：

VincentZyu233/shuyeyun-note/blob/1bf900e0686617eb382f702aa2270306cb4db20f/notes/koishi_dev/db_test/db_test.md

## My env:

### winver
![winver](winver.png)

### docker version:
```shell
PS C:\WINDOWS\system32> docker --version
Docker version 28.4.0, build d8eb465
```

### mysql command:
(powershell) ↓
```shell

mkdir A:\TMP\mysql_docker_test\data
mkdir A:\TMP\mysql_docker_test\logs
mkdir A:\TMP\mysql_docker_test\config

docker run -d `

此文件已被截断。显示原始文件

VincentZyu233 · 2025 年10 月 6 日 19:35

以下是原文:

My env:

winver

docker version:

PS C:\WINDOWS\system32> docker --version
Docker version 28.4.0, build d8eb465

mysql command:

(powershell) ↓


mkdir A:\TMP\mysql_docker_test\data
mkdir A:\TMP\mysql_docker_test\logs
mkdir A:\TMP\mysql_docker_test\config

docker run -d `
  --name mysql-test `
  -p 3306:3306 `
  -e MYSQL_ROOT_PASSWORD=[Test12345] `
  mysql:latest

因为挂载目录可能会遇到一些权限问题，所以先不持久化，后面再说：
持久化的参数:

  -v D:\TMP\mysql_docker_test\data:/var/lib/mysql `   # <-- 1. 主数据目录
  -v D:\TMP\mysql_docker_test\logs:/var/log/mysql `   # <-- 2. 日志目录
  -v D:\TMP\mysql_docker_test\config:/etc/mysql/conf.d ` # <-- 3. 配置目录

看看运行状态

PS C:\WINDOWS\system32> docker ps -a | Select-String -Pattern "mysql"

b92ae46816d7   mysql:latest                          "docker-entrypoint.s鈥?   8 minutes ago   Up 8 minutes             0.0.0.0:3306->3306/tcp, [::]:3306->3306/tcp   mysql
-test

create a new koishi instance

PS G:\GGames\Minecraft\shuyeyun\qq-bot\koishi-dev> yarn create koishi
yarn create v1.22.22
[1/4] Resolving packages...
[2/4] Fetching packages...
[3/4] Linking dependencies...
[4/4] Building fresh packages...

success Installed "create-koishi@6.3.2" with binaries:
      - create-koishi
[########################################################################################################] 104/104
  Create Koishi  v6.3.2

√ Project name: ... koishi-test-2
  Scaffolding project in koishi-test-2 ...
  Done.

? Install and start it now? » (Y/n)

dispose koishi-plugin-database-sqlite

stop_plugin_sqlite

install and configure koishi-plugin-database-mysql

huh? 等下？这是需要我手动创建一个名字叫做koishi的database吗？

2025-10-07 02:59:04 [W] mysql > SELECT version(), @@GLOBAL.time_zone
2025-10-07 02:59:04 [E] app ER_BAD_DB_ERROR: Unknown database 'koishi'
                            at MySQLDriver.query (G:\GGames\Minecraft\shuyeyun\qq-bot\koishi-dev\koishi-test-2\node_modules\@minatojs\driver-mysql\lib\index.cjs:484:19)
                            at MySQLDriver.start (G:\GGames\Minecraft\shuyeyun\qq-bot\koishi-dev\koishi-test-2\node_modules\@minatojs\driver-mysql\lib\index.cjs:295:59)
                            at G:\GGames\Minecraft\shuyeyun\qq-bot\koishi-dev\koishi-test-2\node_modules\minato\lib\index.cjs:1883:18
                            at process.processTicksAndRejections (node:internal/process/task_queues:105:5)

66,等我手动创建一个名字叫做koishi的database试试看

PS C:\WINDOWS\system32> docker exec -it mysql-test bash
bash-5.1# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 9.4.0 MySQL Community Server - GPL

Copyright (c) 2000, 2025, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE koishi CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Query OK, 1 row affected (0.023 sec)

mysql>

done! 现在再重载一下koishi-plugin-database-mysql试试看:

2025-10-07 03:04:49 [I] mysql auto creating table user
2025-10-07 03:04:49 [I] mysql auto creating table binding
2025-10-07 03:04:49 [I] mysql auto creating table channel
2025-10-07 03:04:49 [I] mysql auto creating table analytics.message
2025-10-07 03:04:49 [I] mysql auto creating table analytics.command

great！成了！

再去mysql shell看看：

mysql> USE koishi;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SHOW TABLES;
+-------------------+
| Tables_in_koishi  |
+-------------------+
| analytics.command |
| analytics.message |
| binding           |
| channel           |
| user              |
+-------------------+
5 rows in set (0.002 sec)

mysql>

done!

little conclusion:

看到配置项里面有一个叫做：database 要访问的数据库名
那这个时候就可以猜出一个结论了。

先梳理SQLite3等和MySQL等这两类数据库的特点：

文件即数据库 (File-as-Database)

比如SQLite3,H2, 它们将整个database（所有tables、indexes、views、triggers等等的集合）存储在一个或几个本地文件中
客户端-服务器数据库 (Client-Server Database)

比如MySQL, PostgreSQL, MongoDB, 一个服务器实例可以管理多个database

所以得出结论：
一个koishi实例只需要一个database

VincentZyu233 · 2025 年10 月 6 日 20:37

有关database的删除/重置操作，如果使用SQL语句，一些简单测试过的实践:

点击这里直接去github查看：

github.com

VincentZyu233/shuyeyun-note/blob/93c4aed49edf655185e71fc22d364425810839a4/notes/koishi_dev/db_test/delete_op.md

现在考虑以下场景：
一个koishi应用，想要删除某个table，甚至删除全部的数据库内容 应该如何操作？

先回顾一下刚刚得出的结论：
```markdown
先梳理SQLite3等和MySQL等 这两类数据库的特点： 

  - 文件即数据库 (File-as-Database)
    > 比如`SQLite3`,`H2`,  它们将整个database（所有tables、indexes、views、triggers等等的集合）存储在一个或几个本地文件中
  
  - 客户端-服务器数据库 (Client-Server Database)
    > 比如`MySQL`, `PostgreSQL`, `MongoDB`, 一个服务器实例可以管理多个database
```

## 先看SQLite3:
#### 删除整个db文件=删除整个databse
在 SQLite 中，database 就是那个 .db 文件，所以想要彻底删除整个数据库，最简单直接的方式就是 直接删除掉koishi根目录中的 `./data/koishi.db 文件`, 然后重新启用`koishi-plugin-database-sqlite` 即可. koishi会自动重建的(

![koishi_auto_recreate_sqlite_db_file](koishi_auto_recreate_sqlite_db_file.png)

此文件已被截断。显示原始文件

VincentZyu233 · 2025 年10 月 6 日 20:38

以下是原文:

现在考虑以下场景：
一个koishi应用，想要删除某个table，甚至删除全部的数据库内容应该如何操作？

先回顾一下刚刚得出的结论：

先梳理SQLite3等和MySQL等 这两类数据库的特点： 

  - 文件即数据库 (File-as-Database)
    > 比如`SQLite3`,`H2`,  它们将整个database（所有tables、indexes、views、triggers等等的集合）存储在一个或几个本地文件中
  
  - 客户端-服务器数据库 (Client-Server Database)
    > 比如`MySQL`, `PostgreSQL`, `MongoDB`, 一个服务器实例可以管理多个database

先看SQLite3:

删除整个db文件=删除整个databse

在 SQLite 中，database 就是那个 .db 文件，所以想要彻底删除整个数据库，最简单直接的方式就是直接删除掉koishi根目录中的 ./data/koishi.db 文件, 然后重新启用koishi-plugin-database-sqlite 即可. koishi会自动重建的(

koishi_auto_recreate_sqlite_db_file

接下来是删除某个表格
先用sqlite shell 看看某个表格的结构，比如这个token表格:

PS G:\GGames\Minecraft\shuyeyun\qq-bot\koishi-dev\koishi-test-2\data> ls        

    Directory: G:\GGames\Minecraft\shuyeyun\qq-bot\koishi-dev\koishi-test-2\data

Mode                 LastWriteTime         Length Name
----                 -------------         ------ ----
d----           2025/10/7     2:47                assets
d----           2025/10/7     2:47                locales
d----           2025/10/7     2:47                logs
-a---           2025/10/7     3:57          53248 koishi.db
-a---           2025/10/7     2:47            137 telemetry.json

PS G:\GGames\Minecraft\shuyeyun\qq-bot\koishi-dev\koishi-test-2\data> sqlite3 .\koishi.db
SQLite version 3.50.4 2025-07-30 19:33:53
Enter ".help" for usage hints.
sqlite> .tables;
Error: unknown command or invalid arguments:  "tables;". Enter ".help" for help
sqlite> .tables 
analytics.command  binding            token
analytics.message  channel            user
sqlite> .schema token
CREATE TABLE `token` (`inc` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, `id` INTEGER NULL DEFAULT 0, `type` TEXT NULL DEFAULT '', `token` TEXT NULL DEFAULT '', `expiredAt` INTEGER NULL DEFAULT 0, `createdAt` INTEGER NULL, `lastUsedAt` INTEGER NULL, `userAgent` TEXT NULL DEFAULT '', `address` TEXT NULL DEFAULT '', UNIQUE (`token`));
sqlite>

先试试看`DELETE FROM token;`, 看看schema会不会保留

sqlite> DELETE FROM token;
sqlite> .schema token      
CREATE TABLE `token` (`inc` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, `id` INTEGER NULL DEFAULT 0, `type` TEXT NULL DEFAULT '', `token` TEXT NULL DEFAULT '', `expiredAt` INTEGER NULL DEFAULT 0, `createdAt` INTEGER NULL, `lastUsedAt` INTEGER NULL, `userAgent` TEXT NULL DEFAULT '', `address` TEXT NULL DEFAULT '', UNIQUE (`token`));
sqlite> SELECT * FROM token;
sqlite> SELECT * FROM token;
2|0|password|Y2mm3UTZcM5WRkIq6VZPj3bCI68YEiG6K5Z3reHh|1760385762014|1759780962014|1759780962014|Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/141.0.0.0 Safari/537.36 Edg/141.0.0.0|127.0.0.1
sqlite>

cool, 可以看到，虽然token表格中的所有entries消失了，但是此表格的schema还是会保留的。

再试试看`DROP TABLE token`,这次应该不会保留了

sqlite> SELECT * FROM token;
1|0|password|EJitXatSYCotMdVrhSEbtziJMm3u21O6HXhpfzup|1760386193107|1759781393107|1759781393107|Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/141.0.0.0 Safari/537.36 Edg/141.0.0.0|127.0.0.1
sqlite> .schema token        
CREATE TABLE `token` (`inc` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, `id` INTEGER NULL DEFAULT 0, `type` TEXT NULL DEFAULT '', `token` TEXT NULL DEFAULT '', `expiredAt` INTEGER NULL DEFAULT 0, `createdAt` INTEGER NULL, `lastUsedAt` INTEGER NULL, `userAgent` TEXT NULL DEFAULT '', `address` TEXT NULL DEFAULT '', UNIQUE (`token`));
sqlite> .tables
analytics.command  binding            token
analytics.message  channel            user
sqlite> DROP TABLE token;
sqlite> SELECT * FROM token;
Parse error: no such table: token
sqlite> .schema token
sqlite> .tables              
analytics.command  binding            user
analytics.message  channel
sqlite>

果然，可以见到， DROP TABLE的效果 “更强”，直接这个table名都不保留，schema也没了。那么理所应当的，此时如果重启koishi-plugin-database-sqlite，koishi检测到这个token表没了，就会重新创建一个:

2025-10-07 04:09:14 [I] loader unload plugin database-sqlite:fm4156
2025-10-07 04:09:14 [I] loader apply plugin database-sqlite:fm4156
2025-10-07 04:09:14 [I] auth creating admin account
2025-10-07 04:09:14 [I] sqlite auto creating table token
2025-10-07 04:09:17 [I] loader unload plugin database-sqlite:fm4156
2025-10-07 04:09:18 [I] loader apply plugin database-sqlite:fm4156
2025-10-07 04:09:18 [I] auth creating admin account

再看Mysql:

先试试看`TRUNCATE TABLE table_name;`, 他会保留table的schema、index等等:

mysql> SHOW TABLES; # 查看所有tables
+-------------------+
| Tables_in_koishi  |
+-------------------+
| analytics.command |
| analytics.message |
| binding           |
| channel           |
| token             |
| user              |
+-------------------+
6 rows in set (0.002 sec)

mysql> DESCRIBE token; # 查看token这个表格的schema
+------------+-----------------+------+-----+---------+----------------+
| Field      | Type            | Null | Key | Default | Extra          |
+------------+-----------------+------+-----+---------+----------------+
| inc        | int unsigned    | NO   | PRI | NULL    | auto_increment |
| id         | int unsigned    | YES  |     | 0       |                |
| type       | varchar(255)    | YES  |     |         |                |
| token      | varchar(255)    | YES  | UNI |         |                |
| expiredAt  | bigint unsigned | YES  |     | 0       |                |
| createdAt  | datetime(3)     | YES  |     | NULL    |                |
| lastUsedAt | datetime(3)     | YES  |     | NULL    |                |
| userAgent  | varchar(255)    | YES  |     |         |                |
| address    | varchar(255)    | YES  |     |         |                |
+------------+-----------------+------+-----+---------+----------------+
9 rows in set (0.002 sec)

mysql> SELECT * FROM token; # 查看token表格的所有entry
Empty set (0.001 sec)

# 诶？忘记登录webui了，先登录一下

mysql> SELECT * FROM token; # ok 现在看到东西了
+-----+------+----------+------------------------------------------+---------------+-------------------------+-------------------------+-------------------------------------------------------------------------------------------------------------------------------+-----------+
| inc | id   | type     | token                                    | expiredAt     | createdAt               | lastUsedAt              | userAgent                                                                                                                     | address   |
+-----+------+----------+------------------------------------------+---------------+-------------------------+-------------------------+-------------------------------------------------------------------------------------------------------------------------------+-----------+
|   1 |    1 | password | Whr6PaHIo5JswlpHjfAT5TxLnmtzkZOH89hWZeBj | 1760386520044 | 2025-10-07 04:15:20.044 | 2025-10-07 04:15:20.044 | Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/141.0.0.0 Safari/537.36 Edg/141.0.0.0 | 127.0.0.1 |
+-----+------+----------+------------------------------------------+---------------+-------------------------+-------------------------+-------------------------------------------------------------------------------------------------------------------------------+-----------+
1 row in set (0.000 sec)

mysql> TRUNCATE TABLE token; # 用这个指令重置一下表格
Query OK, 0 rows affected (0.105 sec)

mysql> SHOW TABLES; # 可以看到table名还在
+-------------------+
| Tables_in_koishi  |
+-------------------+
| analytics.command |
| analytics.message |
| binding           |
| channel           |
| token             |
| user              |
+-------------------+
6 rows in set (0.002 sec)

mysql> DESCRIBE token; # 表的结构还在
+------------+-----------------+------+-----+---------+----------------+
| Field      | Type            | Null | Key | Default | Extra          |
+------------+-----------------+------+-----+---------+----------------+
| inc        | int unsigned    | NO   | PRI | NULL    | auto_increment |
| id         | int unsigned    | YES  |     | 0       |                |
| type       | varchar(255)    | YES  |     |         |                |
| token      | varchar(255)    | YES  | UNI |         |                |
| expiredAt  | bigint unsigned | YES  |     | 0       |                |
| createdAt  | datetime(3)     | YES  |     | NULL    |                |
| lastUsedAt | datetime(3)     | YES  |     | NULL    |                |
| userAgent  | varchar(255)    | YES  |     |         |                |
| address    | varchar(255)    | YES  |     |         |                |
+------------+-----------------+------+-----+---------+----------------+
9 rows in set (0.002 sec)

mysql> SELECT * FROM token; # 就是entry会清空
Empty set (0.002 sec)

mysql>

得出结论：truncate指令会保留表名、表的schema等等，但是会清空表格的所有entries.

再试试看`DROP TABLE table_name`, 这次应该会更强

mysql> SHOW TABLES; # 查看所有表名，现在有token
+-------------------+
| Tables_in_koishi  |
+-------------------+
| analytics.command |
| analytics.message |
| binding           |
| channel           |
| token             |
| user              |
+-------------------+
6 rows in set (0.002 sec)

mysql> DESCRIBE token; # 看一下token表的结构
+------------+-----------------+------+-----+---------+----------------+
| Field      | Type            | Null | Key | Default | Extra          |
+------------+-----------------+------+-----+---------+----------------+
| inc        | int unsigned    | NO   | PRI | NULL    | auto_increment |
| id         | int unsigned    | YES  |     | 0       |                |
| type       | varchar(255)    | YES  |     |         |                |
| token      | varchar(255)    | YES  | UNI |         |                |
| expiredAt  | bigint unsigned | YES  |     | 0       |                |
| createdAt  | datetime(3)     | YES  |     | NULL    |                |
| lastUsedAt | datetime(3)     | YES  |     | NULL    |                |
| userAgent  | varchar(255)    | YES  |     |         |                |
| address    | varchar(255)    | YES  |     |         |                |
+------------+-----------------+------+-----+---------+----------------+
9 rows in set (0.004 sec)

mysql> SELECT * FROM token; # awa 又忘记登录了
Empty set (0.000 sec)

mysql> SELECT * FROM token; # 登录以后能看到entry
+-----+------+----------+------------------------------------------+---------------+-------------------------+-------------------------+-------------------------------------------------------------------------------------------------------------------------------+-----------+
| inc | id   | type     | token                                    | expiredAt     | createdAt               | lastUsedAt              | userAgent                                                                                                                     | address   |
+-----+------+----------+------------------------------------------+---------------+-------------------------+-------------------------+-------------------------------------------------------------------------------------------------------------------------------+-----------+
|   1 |    1 | password | QbeFek12magMVK8MmHdYnRcKeBDljvhZaFb0A7Ti | 1760386754360 | 2025-10-07 04:19:14.360 | 2025-10-07 04:19:14.360 | Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/141.0.0.0 Safari/537.36 Edg/141.0.0.0 | 127.0.0.1 |
+-----+------+----------+------------------------------------------+---------------+-------------------------+-------------------------+-------------------------------------------------------------------------------------------------------------------------------+-----------+
1 row in set (0.000 sec)

mysql> DROP TABLE token; # 这次直接drop
Query OK, 0 rows affected (0.042 sec)

mysql> SHOW TABLES; # token表格不见了
+-------------------+
| Tables_in_koishi  |
+-------------------+
| analytics.command |
| analytics.message |
| binding           |
| channel           |
| user              |
+-------------------+
5 rows in set (0.002 sec)

mysql> DESCRIBE token;
ERROR 1146 (42S02): Table 'koishi.token' doesn't exist
mysql> SELECT * FROM token;
ERROR 1146 (42S02): Table 'koishi.token' doesn't exist
mysql>

得出结论：DROP语句更强，表名、表的结构等直接不见了，所以如果要清空一个表的所有行，大部分场景我认为用TRUNCATE就够了, 用DROP一定要足够小心谨慎，因为容易导致更严重的问题，比如表的schema没了，可能导致其对接的上层测试程序(比如koishi) 出现非预期行为。只看数据库本身的话，如果直接drop，如果涉及到级联删除（Cascade Delete）将会导致更多的麻烦。参考链接：Foreign key - Wikipedia

最后试试看`DROP DATABASE koishi`

这个操作，我想在某些程度上应该可以对标 sqlite中的直接删除db文件？试试看:


mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| koishi             |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.001 sec)

mysql> DROP DATABASE koishi;
Query OK, 5 rows affected (0.179 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.001 sec)

mysql>

然后重启koishi-plugin-database-mysql:


2025-10-07 04:28:35 [I] loader unload plugin database-mysql:3qqfrs
2025-10-07 04:28:35 [I] loader apply plugin database-mysql:3qqfrs
2025-10-07 04:28:35 [W] mysql > SELECT version(), @@GLOBAL.time_zone
2025-10-07 04:28:35 [E] app ER_BAD_DB_ERROR: Unknown database 'koishi'
                            at MySQLDriver.query (G:\GGames\Minecraft\shuyeyun\qq-bot\koishi-dev\koishi-test-2\node_modules\@minatojs\driver-mysql\lib\index.cjs:484:19)
                            at MySQLDriver.start (G:\GGames\Minecraft\shuyeyun\qq-bot\koishi-dev\koishi-test-2\node_modules\@minatojs\driver-mysql\lib\index.cjs:295:59)
                            at G:\GGames\Minecraft\shuyeyun\qq-bot\koishi-dev\koishi-test-2\node_modules\minato\lib\index.cjs:1883:18
                            at process.processTicksAndRejections (node:internal/process/task_queues:105:5)

哦哦哦，忘记重新建一个koishi表了, 这就建:


mysql> CREATE DATABASE koishi CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Query OK, 1 row affected (0.020 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| koishi             |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.001 sec)

mysql>

再重新加载koishi-plugin-database-mysql:

2025-10-07 04:30:19 [I] loader unload plugin database-mysql:3qqfrs
2025-10-07 04:30:19 [I] loader apply plugin database-mysql:3qqfrs
2025-10-07 04:30:20 [I] auth creating admin account
2025-10-07 04:30:20 [I] mysql auto creating table user
2025-10-07 04:30:20 [I] mysql auto creating table binding
2025-10-07 04:30:20 [I] mysql auto creating table channel
2025-10-07 04:30:20 [I] mysql auto creating table analytics.message
2025-10-07 04:30:20 [I] mysql auto creating table analytics.command
2025-10-07 04:30:20 [I] mysql auto creating table token

great!

Conclusion:

Remember，在做重要的数据库操作前，一定要记得备份，比如在koishi中，可以直接把koishi.db复制一份。

如果在开发/测试环境，想要清空，某个表格的内容，SQLite3最好用DELETE FROM而不是DROP TABLE；MySql最好用TRUNCATE TABLE而不是DROP TABLE.

开发测试/环境，如果想要重置整个koishi database的内容，SQLite3可以直接删除整个koishi.db文件;MySQL可以使用DROP DATABASE koishi

再次提醒，做重要的操作之前，一定要先备份好数据库，以防出事(

从用户的视角 观察koishi的database业务， 以及 一些个人的实践经验（也许有用？）