Bot.js Pro $mysql 远程数据库模块使用指南

本文介绍 Bot.js Pro 的 $mysql 模块使用方法，适用于Bot.js Pro 9 版本及以上，初期仅支持 MySQL 5.7，MySQL 8.0+ 后续支持。
MySQL API 与 SQLite API 保持一致，便于迁移和统一操作。完整 API 文档请参考 Bot.js Pro 官方文档

---

🧠 $mysql 模块说明

• MySQL API 与 SQLite API 保持一致，操作方法、示例均相似。

⚠️ 注意：当前版本仅支持 MySQL 5.7。

---

📦 完整测试脚本示例

1️⃣ 连接数据库

let db = $mysql.open({
    host: "192.168.0.152",
    port: 3306,
    user: "root",
    password: "123456",
    database: "test",
    version: 5
}, {
    onOpen: function (db) {
        log("数据库打开时");
        // 可在这里初始化表
        // db.execSQL("CREATE TABLE STUDENT (...)");
    }
});

console.show();

---

2️⃣ 数据库初始化

log("=== 数据库初始化 ===");
db.execSQL("DROP TABLE IF EXISTS STUDENT");
db.execSQL(`CREATE TABLE STUDENT (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    age INT NOT NULL,
    score INT
)`);
log("创建表 STUDENT 成功");

---

3️⃣ 插入数据

log("\n=== 插入数据 ===");
db.insert("STUDENT", { name: "张三", age: 18, score: 90 });
db.insert("STUDENT", { name: "李四", age: 19, score: 60 });
db.insert("STUDENT", { name: "王五", age: 20 });
db.insert("STUDENT", { name: "赵六", age: 21, score: 85 });

---

4️⃣ 查询数据

// 查询所有数据
let allCursor = db.rawQuery("SELECT * FROM STUDENT", []);
log(allCursor.all());

// 查询单个数据
let singleCursor = db.rawQuery("SELECT * FROM STUDENT LIMIT 1", []);
log(singleCursor.single());

// 条件查询
let ageCursor = db.rawQuery("SELECT * FROM STUDENT WHERE age > ?", [19]);
log(ageCursor.all());

---

5️⃣ 修改数据

let updateCount = db.update("STUDENT", { score: 70 }, "name = ?", ["李四"]);
log("修改影响行数:", updateCount);

let liSiCursor = db.rawQuery("SELECT * FROM STUDENT WHERE name = ?", ["李四"]);
log(liSiCursor.single());

---

6️⃣ 删除数据

let deleteCount = db.delete("STUDENT", "score > ?", [80]);
log("删除行数:", deleteCount);

let afterDeleteCursor = db.rawQuery("SELECT * FROM STUDENT", []);
log(afterDeleteCursor.all());

---

7️⃣ 遍历数据

let traverseCursor = db.rawQuery("SELECT * FROM STUDENT ORDER BY age", []);
while (traverseCursor.moveToNext()) {
    let student = traverseCursor.pick();
    log("学生:", student.name, "年龄:", student.age, "分数:", student.score);
}
traverseCursor.close();

---

8️⃣ 事务操作

db.transaction(function (tx) {
    db.insert("STUDENT", { name: "事务学生1", age: 22, score: 95 });
    db.insert("STUDENT", { name: "事务学生2", age: 23, score: 88 });
}).on("begin", function () { log("事务开始"); })
  .on("commit", function () { log("事务提交成功"); })
  .on("rollback", function () { log("事务回滚"); })
  .on("end", function () {
      log("事务结束");
      let afterTxCursor = db.rawQuery("SELECT * FROM STUDENT", []);
      log(afterTxCursor.all());
  });

---

9️⃣ 高级查询

// 分组查询
let groupCursor = db.rawQuery(
    "SELECT age, COUNT(*) as count, AVG(score) as avg_score FROM STUDENT GROUP BY age", []
);
log(groupCursor.all());

// 排序查询
let orderCursor = db.rawQuery(
    "SELECT * FROM STUDENT WHERE score IS NOT NULL ORDER BY score DESC", []
);
log(orderCursor.all());

---

🔧 数据库信息查询

log("数据库版本:", db.getVersion());
log("是否在事务中:", db.inTransaction());
log("数据库是否打开:", db.isOpen());
log("自动提交状态:", db.getAutoCommit());

// 查询所有表信息
let tablesCursor = db.rawQuery(
    "SELECT TABLE_NAME, TABLE_TYPE, ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ?",
    ["cloud"]
);
log(tablesCursor.all());

---

🔹 批量操作

let students = [
    { name: "批量1", age: 24, score: 78 },
    { name: "批量2", age: 25, score: 82 },
    { name: "批量3", age: 26, score: 91 }
];

students.forEach((student, index) => {
    let id = db.insert("STUDENT", student);
    log(`插入第${index+1}个学生，ID: ${id}`);
});

---

🔹 清理和关闭

db.execSQL("DROP TABLE IF EXISTS STUDENT");
db.close();
log("数据库已关闭");

---

⚠️ 错误处理示例

try {
    db.execSQL("SELECT 1");
} catch (e) {
    log("预期错误:", e.message);
}

---

✅ 总结

本文整理了 Bot.js Pro $mysql 模块的完整使用示例，包括：

• 数据库连接、创建表
• CRUD 操作（增删改查）
• 条件查询与高级查询
• 事务操作与批量操作
• 数据库信息获取与错误处理

参考官方文档 Bot.js Pro MySQL API 了解最新 API 更新。

---