server.ts•17 kB
import { Server } from '@modelcontextprotocol/sdk/server/index.js';
import { StdioServerTransport } from '@modelcontextprotocol/sdk/server/stdio.js';
import {
CallToolRequestSchema,
ErrorCode,
ListToolsRequestSchema,
McpError,
} from '@modelcontextprotocol/sdk/types.js';
import { DatabaseManager } from './database.js';
import { getConfig, validateConfig } from './config.js';
import { exportData, importData, formatQueryResult, validateSQL, generateCreateTableSQL } from './utils.js';
import { MySQLConfig, ExportOptions, ImportOptions } from './types.js';
export class MySQLMCPServer {
private server: Server;
private dbManager: DatabaseManager;
constructor() {
this.server = new Server(
{
name: 'mysql-mcp',
version: '1.0.0',
},
{
capabilities: {
tools: {},
},
}
);
this.dbManager = new DatabaseManager();
this.setupToolHandlers();
}
private setupToolHandlers(): void {
this.server.setRequestHandler(ListToolsRequestSchema, async () => {
return {
tools: [
{
name: 'mysql_connect',
description: '连接到 MySQL 数据库',
inputSchema: {
type: 'object',
properties: {
host: { type: 'string', description: 'MySQL 服务器地址' },
port: { type: 'number', description: 'MySQL 端口号', default: 3306 },
user: { type: 'string', description: '用户名' },
password: { type: 'string', description: '密码' },
database: { type: 'string', description: '数据库名称（可选）' },
},
required: ['host', 'user', 'password'],
},
},
{
name: 'mysql_disconnect',
description: '断开 MySQL 数据库连接',
inputSchema: {
type: 'object',
properties: {},
},
},
{
name: 'mysql_list_databases',
description: '列出所有数据库',
inputSchema: {
type: 'object',
properties: {},
},
},
{
name: 'mysql_create_database',
description: '创建数据库',
inputSchema: {
type: 'object',
properties: {
name: { type: 'string', description: '数据库名称' },
charset: { type: 'string', description: '字符集（可选）', default: 'utf8mb4' },
collation: { type: 'string', description: '排序规则（可选）', default: 'utf8mb4_unicode_ci' },
},
required: ['name'],
},
},
{
name: 'mysql_drop_database',
description: '删除数据库',
inputSchema: {
type: 'object',
properties: {
name: { type: 'string', description: '数据库名称' },
},
required: ['name'],
},
},
{
name: 'mysql_list_tables',
description: '列出指定数据库的所有表',
inputSchema: {
type: 'object',
properties: {
database: { type: 'string', description: '数据库名称（可选，使用当前连接的数据库）' },
},
},
},
{
name: 'mysql_describe_table',
description: '查看表结构',
inputSchema: {
type: 'object',
properties: {
tableName: { type: 'string', description: '表名称' },
database: { type: 'string', description: '数据库名称（可选）' },
},
required: ['tableName'],
},
},
{
name: 'mysql_create_table',
description: '创建表',
inputSchema: {
type: 'object',
properties: {
sql: { type: 'string', description: '创建表的 SQL 语句' },
},
required: ['sql'],
},
},
{
name: 'mysql_drop_table',
description: '删除表',
inputSchema: {
type: 'object',
properties: {
tableName: { type: 'string', description: '表名称' },
},
required: ['tableName'],
},
},
{
name: 'mysql_query',
description: '执行 SQL 查询',
inputSchema: {
type: 'object',
properties: {
sql: { type: 'string', description: 'SQL 查询语句' },
params: {
type: 'array',
description: '参数化查询的参数（可选）',
items: { type: 'string' }
},
},
required: ['sql'],
},
},
{
name: 'mysql_insert',
description: '插入数据到表',
inputSchema: {
type: 'object',
properties: {
tableName: { type: 'string', description: '表名称' },
data: {
type: 'array',
description: '要插入的数据数组',
items: { type: 'object' }
},
},
required: ['tableName', 'data'],
},
},
{
name: 'mysql_update',
description: '更新表数据',
inputSchema: {
type: 'object',
properties: {
tableName: { type: 'string', description: '表名称' },
data: { type: 'object', description: '要更新的数据' },
where: { type: 'object', description: '更新条件' },
},
required: ['tableName', 'data', 'where'],
},
},
{
name: 'mysql_delete',
description: '删除表数据',
inputSchema: {
type: 'object',
properties: {
tableName: { type: 'string', description: '表名称' },
where: { type: 'object', description: '删除条件' },
},
required: ['tableName', 'where'],
},
},
{
name: 'mysql_export_data',
description: '导出表数据到文件',
inputSchema: {
type: 'object',
properties: {
tableName: { type: 'string', description: '表名称' },
filePath: { type: 'string', description: '导出文件路径' },
format: { type: 'string', enum: ['csv', 'json'], description: '导出格式' },
where: { type: 'string', description: '查询条件（可选）' },
},
required: ['tableName', 'filePath', 'format'],
},
},
{
name: 'mysql_import_data',
description: '从文件导入数据到表',
inputSchema: {
type: 'object',
properties: {
tableName: { type: 'string', description: '表名称' },
filePath: { type: 'string', description: '导入文件路径' },
format: { type: 'string', enum: ['csv', 'json'], description: '文件格式' },
truncateFirst: { type: 'boolean', description: '是否先清空表', default: false },
},
required: ['tableName', 'filePath', 'format'],
},
},
],
};
});
this.server.setRequestHandler(CallToolRequestSchema, async (request: any) => {
const { name, arguments: args } = request.params;
try {
switch (name) {
case 'mysql_connect':
return await this.handleConnect(args as any);
case 'mysql_disconnect':
return await this.handleDisconnect();
case 'mysql_list_databases':
return await this.handleListDatabases();
case 'mysql_create_database':
return await this.handleCreateDatabase(args as any);
case 'mysql_drop_database':
return await this.handleDropDatabase(args as any);
case 'mysql_list_tables':
return await this.handleListTables(args as any);
case 'mysql_describe_table':
return await this.handleDescribeTable(args as any);
case 'mysql_create_table':
return await this.handleCreateTable(args as any);
case 'mysql_drop_table':
return await this.handleDropTable(args as any);
case 'mysql_query':
return await this.handleQuery(args as any);
case 'mysql_insert':
return await this.handleInsert(args as any);
case 'mysql_update':
return await this.handleUpdate(args as any);
case 'mysql_delete':
return await this.handleDelete(args as any);
case 'mysql_export_data':
return await this.handleExportData(args as any);
case 'mysql_import_data':
return await this.handleImportData(args as any);
default:
throw new McpError(ErrorCode.MethodNotFound, `未知的工具: ${name}`);
}
} catch (error) {
throw new McpError(
ErrorCode.InternalError,
`执行工具 ${name} 时发生错误: ${error instanceof Error ? error.message : String(error)}`
);
}
});
}
private async handleConnect(args: MySQLConfig): Promise<any> {
const config = { ...getConfig(), ...args };
validateConfig(config);
await this.dbManager.connect(config);
return {
content: [
{
type: 'text',
text: `成功连接到 MySQL 服务器: ${config.host}:${config.port}`,
},
],
};
}
private async handleDisconnect(): Promise<any> {
await this.dbManager.disconnect();
return {
content: [
{
type: 'text',
text: '已断开 MySQL 连接',
},
],
};
}
private async handleListDatabases(): Promise<any> {
const databases = await this.dbManager.listDatabases();
return {
content: [
{
type: 'text',
text: `数据库列表:\n${JSON.stringify(databases, null, 2)}`,
},
],
};
}
private async handleCreateDatabase(args: { name: string; charset?: string; collation?: string }): Promise<any> {
await this.dbManager.createDatabase(args.name, args.charset, args.collation);
return {
content: [
{
type: 'text',
text: `成功创建数据库: ${args.name}`,
},
],
};
}
private async handleDropDatabase(args: { name: string }): Promise<any> {
await this.dbManager.dropDatabase(args.name);
return {
content: [
{
type: 'text',
text: `成功删除数据库: ${args.name}`,
},
],
};
}
private async handleListTables(args: { database?: string }): Promise<any> {
const tables = await this.dbManager.listTables(args.database);
return {
content: [
{
type: 'text',
text: `表列表:\n${JSON.stringify(tables, null, 2)}`,
},
],
};
}
private async handleDescribeTable(args: { tableName: string; database?: string }): Promise<any> {
const columns = await this.dbManager.describeTable(args.tableName, args.database);
return {
content: [
{
type: 'text',
text: `表 ${args.tableName} 结构:\n${JSON.stringify(columns, null, 2)}`,
},
],
};
}
private async handleCreateTable(args: { sql: string }): Promise<any> {
await this.dbManager.createTable(args.sql);
return {
content: [
{
type: 'text',
text: '成功创建表',
},
],
};
}
private async handleDropTable(args: { tableName: string }): Promise<any> {
await this.dbManager.dropTable(args.tableName);
return {
content: [
{
type: 'text',
text: `成功删除表: ${args.tableName}`,
},
],
};
}
private async handleQuery(args: { sql: string; params?: any[] }): Promise<any> {
validateSQL(args.sql);
const result = await this.dbManager.query(args.sql, args.params);
return {
content: [
{
type: 'text',
text: `${formatQueryResult(result)}\n\n查询结果:\n${JSON.stringify(result.rows, null, 2)}`,
},
],
};
}
private async handleInsert(args: { tableName: string; data: any[] }): Promise<any> {
if (!Array.isArray(args.data) || args.data.length === 0) {
throw new Error('数据必须是非空数组');
}
const columns = Object.keys(args.data[0]);
const placeholders = columns.map(() => '?').join(', ');
const values = args.data.map(row => columns.map(col => row[col]));
let affectedRows = 0;
for (const rowValues of values) {
const sql = `INSERT INTO \`${args.tableName}\` (\`${columns.join('`, `')}\`) VALUES (${placeholders})`;
const result = await this.dbManager.query(sql, rowValues);
affectedRows += result.affectedRows || 0;
}
return {
content: [
{
type: 'text',
text: `成功插入 ${affectedRows} 行数据到表 ${args.tableName}`,
},
],
};
}
private async handleUpdate(args: { tableName: string; data: any; where: any }): Promise<any> {
const setClause = Object.keys(args.data).map(key => `\`${key}\` = ?`).join(', ');
const whereClause = Object.keys(args.where).map(key => `\`${key}\` = ?`).join(' AND ');
const sql = `UPDATE \`${args.tableName}\` SET ${setClause} WHERE ${whereClause}`;
const params = [...Object.values(args.data), ...Object.values(args.where)];
const result = await this.dbManager.query(sql, params);
return {
content: [
{
type: 'text',
text: `成功更新 ${result.affectedRows} 行数据`,
},
],
};
}
private async handleDelete(args: { tableName: string; where: any }): Promise<any> {
const whereClause = Object.keys(args.where).map(key => `\`${key}\` = ?`).join(' AND ');
const sql = `DELETE FROM \`${args.tableName}\` WHERE ${whereClause}`;
const params = Object.values(args.where);
const result = await this.dbManager.query(sql, params);
return {
content: [
{
type: 'text',
text: `成功删除 ${result.affectedRows} 行数据`,
},
],
};
}
private async handleExportData(args: { tableName: string; filePath: string; format: 'csv' | 'json'; where?: string }): Promise<any> {
let sql = `SELECT * FROM \`${args.tableName}\``;
if (args.where) {
sql += ` WHERE ${args.where}`;
}
const result = await this.dbManager.query(sql);
await exportData(result, { format: args.format, filePath: args.filePath });
return {
content: [
{
type: 'text',
text: `成功导出 ${result.rows.length} 行数据到文件: ${args.filePath}`,
},
],
};
}
private async handleImportData(args: { tableName: string; filePath: string; format: 'csv' | 'json'; truncateFirst?: boolean }): Promise<any> {
if (args.truncateFirst) {
await this.dbManager.query(`TRUNCATE TABLE \`${args.tableName}\``);
}
const data = await importData({
format: args.format,
filePath: args.filePath,
tableName: args.tableName,
});
if (data.length === 0) {
return {
content: [
{
type: 'text',
text: '导入文件为空，没有数据需要导入',
},
],
};
}
const columns = Object.keys(data[0]);
const placeholders = columns.map(() => '?').join(', ');
let importedRows = 0;
for (const row of data) {
const values = columns.map(col => row[col]);
const sql = `INSERT INTO \`${args.tableName}\` (\`${columns.join('`, `')}\`) VALUES (${placeholders})`;
const result = await this.dbManager.query(sql, values);
importedRows += result.affectedRows || 0;
}
return {
content: [
{
type: 'text',
text: `成功导入 ${importedRows} 行数据到表 ${args.tableName}`,
},
],
};
}
async run(): Promise<void> {
const transport = new StdioServerTransport();
await this.server.connect(transport);
console.error('MySQL MCP Server 已启动');
}
}