1
{
2
  "mcpServers": {
3
    "mysql-local": {
4
      "command": "npx",
5
      "args": [
6
        "@f4ww4z/mcp-mysql-server",
7
        "--host", "127.0.0.1",
8
        "--port", "3306",
9
        "--user", "root",
10
        "--password", "your_password_here",
11
        "--database", "test_db"
12
      ]
13
    },
14
    "mysql-production": {
15
      "command": "npx",
16
      "args": [
17
        "@f4ww4z/mcp-mysql-server",
18
        "--host", "production-host.com",
19
        "--port", "3306",
20
        "--user", "app_user",
21
        "--password", "${MYSQL_PASSWORD}",
22
        "--database", "production_db"
23
      ]
24
    }
25
  }
26
}

1
{
2
  "mcpServers": {
3
    "mysql-secure": {
4
      "command": "npx",
5
      "args": [
6
        "@f4ww4z/mcp-mysql-server",
7
        "--host", "${MYSQL_HOST}",
8
        "--port", "${MYSQL_PORT}",
9
        "--user", "${MYSQL_USER}",
10
        "--password", "${MYSQL_PASSWORD}",
11
        "--database", "${MYSQL_DATABASE}"
12
      ]
13
    }
14
  }
15
}

1
@mysql-local 执行: SQL语句

1
{
2
  "mcpServers": {
3
    "mysql-secure": {
4
      "command": "npx",
5
      "args": [
6
        "@f4ww4z/mcp-mysql-server",
7
        "--host", "localhost",
8
        "--port", "3306",
9
        "--user", "${MYSQL_USER}",
10
        "--password", "${MYSQL_PASSWORD}",
11
        "--database", "${MYSQL_DATABASE}"
12
      ],
13
      "env": {
14
        "MYSQL_USER": "mcp_user",
15
        "MYSQL_PASSWORD": "from_env_var",
16
        "MYSQL_DATABASE": "app_db"
17
      }
18
    }
19
  }
20
}

1
-- 只读用户
2
CREATE USER 'mcp_readonly'@'localhost' IDENTIFIED BY 'readonly_pass';
3
GRANT SELECT ON app_database.* TO 'mcp_readonly'@'localhost';
4

5
-- 读写用户（需要写操作时）
6
CREATE USER 'mcp_readwrite'@'localhost' IDENTIFIED BY 'readwrite_pass';
7
GRANT SELECT, INSERT, UPDATE, DELETE ON app_database.* TO 'mcp_readwrite'@'localhost';
8

9
FLUSH PRIVILEGES;

1
-- 查看今日销售统计
2
@mysql-local 执行:
3
SELECT
4
    DATE(o.created_at) as order_date,
5
    COUNT(*) as total_orders,
6
    SUM(o.total_amount) as total_revenue,
7
    AVG(o.total_amount) as avg_order_value,
8
    COUNT(DISTINCT o.user_id) as unique_customers
9
FROM orders o
10
WHERE o.created_at >= CURDATE()
11
GROUP BY DATE(o.created_at);
12

13
-- 热销商品排行
14
@mysql-local 执行:
15
SELECT
16
    p.name as product_name,
17
    SUM(oi.quantity) as total_sold,
18
    SUM(oi.quantity * oi.price) as total_revenue,
19
    COUNT(DISTINCT oi.order_id) as order_count
20
FROM order_items oi
21
JOIN products p ON oi.product_id = p.id
22
WHERE oi.created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
23
GROUP BY p.id, p.name
24
ORDER BY total_sold DESC
25
LIMIT 10;

1
-- 用户留存率计算
2
@mysql-local 执行:
3
SELECT
4
    DATE(u.created_at) as signup_date,
5
    COUNT(*) as new_signups,
6
    COUNT(CASE WHEN u.last_login >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 END) as active_7_days,
7
    COUNT(CASE WHEN u.last_login >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 END) as active_30_days,
8
    ROUND(COUNT(CASE WHEN u.last_login >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 END) * 100.0 / COUNT(*), 2) as retention_7_days_pct,
9
    ROUND(COUNT(CASE WHEN u.last_login >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 END) * 100.0 / COUNT(*), 2) as retention_30_days_pct
10
FROM users u
11
WHERE u.created_at >= DATE_SUB(NOW(), INTERVAL 60 DAY)
12
GROUP BY DATE(u.created_at)
13
ORDER BY signup_date DESC;

1
-- 查询执行时间统计
2
@mysql-local 执行:
3
SELECT
4
    query,
5
    exec_count,
6
    total_latency,
7
    avg_latency,
8
    max_latency
9
FROM performance_schema.events_statements_summary_by_digest
10
ORDER BY total_latency DESC
11
LIMIT 10;
12

13
-- 表锁等待统计
14
@mysql-local 执行:
15
SELECT
16
    object_schema,
17
    object_name,
18
    count_read,
19
    count_write,
20
    sum_timer_wait
21
FROM performance_schema.table_lock_waits_summary_by_table
22
ORDER BY sum_timer_wait DESC
23
LIMIT 10;

1
// 基于数据库结构生成API
2
const express = require('express');
3
const app = express();
4

5
// 从数据库获取表结构
6
app.get('/api/schema/:table', async (req, res) => {
7
    const tableName = req.params.table;
8
    const query = `
9
        SELECT column_name, data_type, is_nullable, column_default
10
        FROM information_schema.columns
11
        WHERE table_schema = DATABASE() AND table_name = ?
12
        ORDER BY ordinal_position
13
    `;
14

15
    // 使用MCP查询结果
16
    const columns = await executeMCPQuery(query, [tableName]);
17
    res.json({ table: tableName, columns });
18
});

1
// 基于数据库表生成TypeScript接口
2
interface User {
3
    id: number;
4
    username: string;
5
    email: string;
6
    created_at: Date;
7
    updated_at: Date;
8
    status: 'active' | 'inactive' | 'banned';
9
}
10

11
// 自动生成CRUD API调用
12
class DatabaseService {
13
    async getUsers(limit: number = 10) {
14
        const query = `SELECT * FROM users ORDER BY created_at DESC LIMIT ${limit}`;
15
        return await this.executeQuery(query);
16
    }
17
}

1
-- 检查查询是否使用索引
2
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';
3

4
-- 创建复合索引
5
CREATE INDEX idx_users_email_status ON users(email, status);
6

7
-- 查看索引使用情况
8
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage;

1
-- 高效分页查询（使用游标）
2
SELECT * FROM users
3
WHERE id > last_seen_id
4
ORDER BY id
5
LIMIT 20;
6

7
-- 避免OFFSET大数值
8
-- 不推荐：SELECT * FROM users LIMIT 10000, 20;

1
{
2
  "mcpServers": {
3
    "mysql-pooled": {
4
      "command": "npx",
5
      "args": [
6
        "@f4ww4z/mcp-mysql-server",
7
        "--host", "localhost",
8
        "--port", "3306",
9
        "--user", "app_user",
10
        "--password", "password",
11
        "--database", "app_db",
12
        "--connectionLimit", "10",
13
        "--acquireTimeout", "60000",
14
        "--timeout", "60000"
15
      ]
16
    }
17
  }
18
}

1
{
2
  "mcpServers": {
3
    "mysql-monitored": {
4
      "command": "npx",
5
      "args": [
6
        "@f4ww4z/mcp-mysql-server",
7
        "--host", "localhost",
8
        "--port", "3306",
9
        "--user", "monitor_user",
10
        "--password", "password",
11
        "--database", "app_db",
12
        "--queryTimeout", "30000"
13
      ]
14
    }
15
  }
16
}

1
-- 自然语言生成SQL
2
@mysql-local 生成: 显示最近7天注册用户的活跃度
3

4
-- AI优化建议
5
@mysql-local 优化: SELECT * FROM large_table WHERE created_at > '2024-01-01'
6

7
-- 自动数据洞察
8
@mysql-local 分析: 用户表中的数据模式

1
// 自动生成图表配置
2
const chartConfig = {
3
    type: 'line',
4
    data: {
5
        labels: await getDateLabels(),
6
        datasets: [{
7
            label: '每日新用户',
8
            data: await getDailyNewUsers()
9
        }]
10
    }
11
};