database.ts•4.93 kB
import mysql from 'mysql2/promise';
import { MySQLConfig, QueryResult, DatabaseInfo, TableInfo, ColumnInfo } from './types.js';
export class DatabaseManager {
private pool: mysql.Pool | null = null;
private config: MySQLConfig | null = null;
constructor() {}
/**
* 连接到 MySQL 数据库
*/
async connect(config: MySQLConfig): Promise<void> {
try {
this.config = config;
this.pool = mysql.createPool({
host: config.host,
port: config.port,
user: config.user,
password: config.password,
database: config.database,
connectionLimit: config.connectionLimit || 10,
multipleStatements: true
});
// 测试连接
const connection = await this.pool.getConnection();
await connection.ping();
connection.release();
console.log(`已成功连接到 MySQL 服务器: ${config.host}:${config.port}`);
} catch (error) {
throw new Error(`连接 MySQL 失败: ${error instanceof Error ? error.message : String(error)}`);
}
}
/**
* 断开数据库连接
*/
async disconnect(): Promise<void> {
if (this.pool) {
await this.pool.end();
this.pool = null;
console.log('已断开 MySQL 连接');
}
}
/**
* 执行 SQL 查询
*/
async query(sql: string, params?: any[]): Promise<QueryResult> {
if (!this.pool) {
throw new Error('数据库未连接，请先调用 connect() 方法');
}
try {
const [rows, fields] = await this.pool.execute(sql, params);
if (Array.isArray(rows)) {
return {
rows: rows as any[],
fields: fields as any[]
};
} else {
// 对于 INSERT, UPDATE, DELETE 等操作
const result = rows as mysql.ResultSetHeader;
return {
rows: [],
fields: [],
affectedRows: result.affectedRows,
insertId: result.insertId
};
}
} catch (error) {
throw new Error(`SQL 执行失败: ${error instanceof Error ? error.message : String(error)}`);
}
}
/**
* 获取所有数据库列表
*/
async listDatabases(): Promise<DatabaseInfo[]> {
const result = await this.query(`
SELECT
SCHEMA_NAME as name,
DEFAULT_CHARACTER_SET_NAME as charset,
DEFAULT_COLLATION_NAME as collation
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')
ORDER BY SCHEMA_NAME
`);
return result.rows as DatabaseInfo[];
}
/**
* 创建数据库
*/
async createDatabase(name: string, charset?: string, collation?: string): Promise<void> {
let sql = `CREATE DATABASE \`${name}\``;
if (charset) {
sql += ` CHARACTER SET ${charset}`;
}
if (collation) {
sql += ` COLLATE ${collation}`;
}
await this.query(sql);
}
/**
* 删除数据库
*/
async dropDatabase(name: string): Promise<void> {
await this.query(`DROP DATABASE \`${name}\``);
}
/**
* 获取指定数据库的所有表
*/
async listTables(database?: string): Promise<TableInfo[]> {
const dbName = database || this.config?.database;
if (!dbName) {
throw new Error('请指定数据库名称');
}
const result = await this.query(`
SELECT
TABLE_NAME as name,
ENGINE as engine,
TABLE_ROWS as rows,
DATA_LENGTH as dataLength,
INDEX_LENGTH as indexLength,
TABLE_COMMENT as comment
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = ?
AND TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME
`, [dbName]);
return result.rows as TableInfo[];
}
/**
* 获取表结构信息
*/
async describeTable(tableName: string, database?: string): Promise<ColumnInfo[]> {
const dbName = database || this.config?.database;
if (!dbName) {
throw new Error('请指定数据库名称');
}
const result = await this.query(`
SELECT
COLUMN_NAME as field,
COLUMN_TYPE as type,
IS_NULLABLE as \`null\`,
COLUMN_KEY as \`key\`,
COLUMN_DEFAULT as \`default\`,
EXTRA as extra,
COLUMN_COMMENT as comment
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
ORDER BY ORDINAL_POSITION
`, [dbName, tableName]);
return result.rows as ColumnInfo[];
}
/**
* 创建表
*/
async createTable(sql: string): Promise<void> {
await this.query(sql);
}
/**
* 删除表
*/
async dropTable(tableName: string): Promise<void> {
await this.query(`DROP TABLE \`${tableName}\``);
}
/**
* 检查连接状态
*/
isConnected(): boolean {
return this.pool !== null;
}
/**
* 获取当前配置
*/
getConfig(): MySQLConfig | null {
return this.config;
}
}