mysql-database-skill（MySQL 数据库技能）

MySQL Database Skill

Use the mysql CLI to connect to and interact with MySQL databases. Use the -e flag to execute SQL statements and the -s (--silent) flag to produce clean output suitable for processing. Combine with -r (--raw) to avoid escaping, and pipe the result to jq for reliable JSON formatting.

快速使用场景

场景 1: 查询数据（最常用）

mysql -h <host> -u <user> --database <db> -s -r -e "SELECT * FROM users LIMIT 10;" 2>$null

场景 2: 查看表结构

mysql -h <host> -u <user> --database <db> -s -r -e "DESCRIBE users;" 2>$null

场景 3: 插入/更新/删除数据

# 插入
mysql -h <host> -u <user> --database <db> -s -r -e "INSERT INTO users (name, email) VALUES ('Test', 'test@example.com');" 2>$null

# 更新
mysql -h <host> -u <user> --database <db> -s -r -e "UPDATE users SET status=1 WHERE id=1;" 2>$null

# 删除
mysql -h <host> -u <user> --database <db> -s -r -e "DELETE FROM users WHERE id=1;" 2>$null

场景 4: 统计数据报表

mysql -h <host> -u <user> --database <db> -s -r -e "SELECT COUNT(*) as total, SUM(amount) as revenue FROM orders WHERE DATE(create_time)=CURDATE();" | jq -s '.'

场景 5: 导出数据到文件

mysql -h <host> -u <user> --database <db> -s -r -e "SELECT * FROM users INTO OUTFILE '/tmp/users.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\
';" 2>$null

场景 6: 执行 SQL 脚本文件

mysql -h <host> -u <user> --database <db> -s -r < script.sql 2>$null

数据库连接

基础连接

mysql -h <hostname> -P <port> -u <username> --database <database-name> -s -r

示例 (连接本地数据库):

MYSQL_PWD=yourpassword mysql -h 127.0.0.1 -u app_user --database app_db -s -r

从 JDBC URL 解析连接参数

用户可能提供 JDBC URL 格式：jdbc:mysql://host:port/database，需要解析为 mysql CLI 参数：

jdbc:mysql://nexus.syrinxchina.com:3306/test3
  → -h nexus.syrinxchina.com -P 3306 --database test3

# 示例：从 JDBC URL 构建连接
JDBC_URL="jdbc:mysql://nexus.syrinxchina.com:3306/test3"
HOST=$(echo $JDBC_URL | sed -n 's/.*:\/\/\([^:]*\):\([0-9]*\)\/\(.*\)/\1/p')
PORT=$(echo $JDBC_URL | sed -n 's/.*:\/\/\([^:]*\):\([0-9]*\)\/\(.*\)/\2/p')
DB=$(echo $JDBC_URL | sed -n 's/.*:\/\/\([^:]*\):\([0-9]*\)\/\(.*\)/\3/p')
mysql -h "$HOST" -P "$PORT" -u root --database "$DB" -s -r

连接参数表

连接示例 (完整参数):

MYSQL_PWD=password mysql -h 192.168.1.100 -P 3306 -u admin --database mydb --ssl-mode=REQUIRED --connect-timeout=10 -s -r

使用配置文件

创建 ~/.my.cnf 简化频繁连接：

[client]
host = 127.0.0.1
port = 3306
user = app_user
database = app_db
password = yourpassword
ssl-mode = DISABLED

mysql --defaults-extra-file=~/.my.cnf -s -r -e "SELECT 1;"

数据操作

查询 (SELECT)

mysql -h <host> -u <user> --database <db> -s -r -e "SELECT * FROM your_table LIMIT 5;" | jq -R -s 'split("\
") | map(select(. != "")) | map(split("\	")) | {headers: .[0], rows: .[1:]}'

更推荐的方法 (在SQL内生成JSON):

mysql -h <host> -u <user> --database <db> -s -r -e "SELECT JSON_OBJECT('id', id, 'name', name) FROM users LIMIT 5;" | jq -s '.'

输出：

[
 {"id": 1, "name": "Alice"},
 {"id": 2, "name": "Bob"}
]

插入 (INSERT)

mysql -h <host> -u <user> --database <db> -s -r -e "INSERT INTO users (name, email) VALUES ('New User', 'new@example.com'); SELECT JSON_OBJECT('last_insert_id', LAST_INSERT_ID());" | jq .

更新 (UPDATE)

mysql -h <host> -u <user> --database <db> -s -r -e "UPDATE users SET status = 'active' WHERE signup_date < '2026-01-01'; SELECT JSON_OBJECT('rows_affected', ROW_COUNT());" | jq .

删除 (DELETE)

mysql -h <host> -u <user> --database <db> -s -r -e "DELETE FROM sessions WHERE last_activity < DATE_SUB(NOW(), INTERVAL 30 DAY); SELECT JSON_OBJECT('rows_affected', ROW_COUNT());" | jq .

高级查询与 JSON 输出

统计摘要查询

mysql -h <host> -u <user> --database <db> -s -r -e "
SELECT JSON_OBJECT(
 'total_users', (SELECT COUNT(*) FROM users),
 'active_users', (SELECT COUNT(*) FROM users WHERE status = 'active'),
 'avg_posts', (SELECT AVG(post_count) FROM user_stats)
) AS report;
" | jq .

通用 JSON 输出模式

单行结果：

mysql ... -s -r -e "SELECT JSON_OBJECT('key1', column1, 'key2', column2) FROM ..." | jq .

多行结果：

mysql ... -s -r -e "SELECT JSON_ARRAYAGG(JSON_OBJECT('id', id, 'name', name)) FROM users;" | jq .

批量执行 SQL 文件

mysql -h <host> -u <user> --database <db> -s -r < script.sql 2>$null

带变量执行：

mysql -h <host> -u <user> --database <db> -s -r -e "source script.sql;"

批量导入 CSV：

mysql -h <host> -u <user> --database <db> -s -r -e "LOAD DATA LOCAL INFILE 'data.csv' INTO TABLE my_table FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\
' (col1, col2, col3);"

事务支持

# 提交事务
mysql -h <host> -u <user> --database <db> -s -r -e "
START TRANSACTION;
INSERT INTO orders (user_id, total) VALUES (1, 100.50);
UPDATE inventory SET stock = stock - 1 WHERE product_id = 42;
COMMIT;
SELECT JSON_OBJECT('status', 'committed') AS result;
" | jq .

事务回滚：

mysql -h <host> -u <user> --database <db> -s -r -e "
START TRANSACTION;
INSERT INTO users (name) VALUES ('Test');
ROLLBACK;
SELECT JSON_OBJECT('rolled_back', true) AS result;
" | jq .

错误处理

常见错误码

超时配置

mysql -h <host> -u <user> --database <db> --connect-timeout=5 --read-timeout=30 --write-timeout=30 -s -r -e "SELECT * FROM large_table;"

连接测试模式

mysql -h <host> -u <user> --database <db> -s -r -e "SELECT 1 AS connected;" 2>&1 | grep -q "connected" && echo "连接成功" || echo "连接失败"

数据库与表操作

创建数据库

mysql -h <host> -u <user> -s -r -e "CREATE DATABASE IF NOT EXISTS new_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

列出所有数据库

mysql -h <host> -u <user> -s -r -e "SHOW DATABASES;"

列出表

mysql -h <host> -u <user> --database <db> -s -r -e "SHOW TABLES;"

查看表结构

mysql -h <host> -u <user> --database <db> -s -r -e "DESCRIBE users;"

查看索引

mysql -h <host> -u <user> --database <db> -s -r -e "SHOW INDEX FROM users;"

查看建表语句

mysql -h <host> -u <user> --database <db> -s -r -e "SHOW CREATE TABLE users;" | jq -s '.'

DESCRIBE TABLE 详解

查看单表结构

mysql -h <host> -u <user> --database <db> -s -r -e "DESCRIBE users;"

输出字段说明：

格式化输出为 JSON

mysql -h <host> -u <user> --database <db> -s -r -e "
SELECT JSON_ARRAYAGG(JSON_OBJECT(
  'column', Field,
  'type', Type,
  'nullable', NULL,
  'key', Key,
  'default', Default,
  'extra', Extra
)) AS columns FROM information_schema.columns
WHERE table_schema = '<database>' AND table_name = '<table>'
ORDER BY ordinal_position;" | jq .

查看表的所有信息（含注释）

mysql -h <host> -u <user> --database <db> -s -r -e "
SELECT COLUMN_NAME, DATA_TYPE, COLUMN_TYPE, IS_NULLABLE, COLUMN_KEY, COLUMN_DEFAULT, EXTRA, COLUMN_COMMENT
FROM information_schema.columns
WHERE table_schema = '<database>' AND table_name = '<table>'
ORDER BY ordinal_position;" | jq -s '.'

快速查看主键和自增字段

mysql -h <host> -u <user> --database <db> -s -r -e "
SELECT COLUMN_NAME, DATA_TYPE, EXTRA
FROM information_schema.columns
WHERE table_schema = '<database>'
  AND table_name = '<table>'
  AND (COLUMN_KEY = 'PRI' OR EXTRA LIKE '%auto_increment%')
ORDER BY COLUMN_KEY DESC, ordinal_position;" | jq -s '.'

EXPLAIN 查询分析（重要！）

分析 SELECT 查询执行计划：

mysql -h <host> -u <user> --database <db> -s -r -e "EXPLAIN SELECT * FROM users WHERE phone = '13800138000';" | jq -s '.'

输出字段说明：

type 性能排序（从快到慢）：

const > eq_ref > ref > range > index > ALL

ALL 是全表扫描，需要优化（加索引）。

EXPLAIN ANALYZE（MySQL 8.0+）

mysql -h <host> -u <user> --database <db> -s -r -e "EXPLAIN ANALYZE SELECT * FROM users WHERE phone = '13800138000';" | jq -s '.'

比 EXPLAIN 更详细，包含实际运行时间和实际行数。

查看表的索引详情

mysql -h <host> -u <user> --database <db> -s -r -e "SHOW INDEX FROM users;" | jq -s '.'

返回每个索引的：索引名、列名、唯一性、基数、索引类型

查看表大小和行数

mysql -h <host> -u <user> --database <db> -s -r -e "
SELECT
  table_name,
  table_rows,
  ROUND(data_length / 1024 / 1024, 2) AS 'data_size_mb',
  ROUND(index_length / 1024 / 1024, 2) AS 'index_size_mb',
  ROUND((data_length + index_length) / 1024 / 1024, 2) AS 'total_size_mb'
FROM information_schema.tables
WHERE table_schema = '<database>'
ORDER BY (data_length + index_length) DESC;" | jq -s '.'

查看数据库中所有表的基本信息

mysql -h <host> -u <user> --database <db> -s -r -e "
SELECT
  TABLE_NAME AS 'table',
  TABLE_ROWS AS 'rows',
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS 'size_mb',
  ROUND(DATA_FREE / 1024 / 1024, 2) AS 'free_mb',
  ENGINE,
  TABLE_COMMENT
FROM information_schema.tables
WHERE table_schema = '<database>'
  AND table_type = 'BASE TABLE'
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;" | jq -s '.'

free_mb 过大说明表有碎片，可以定期 OPTIMIZE TABLE 回收空间。

环境变量配置

export MYSQL_PWD="yourpassword"
export MYSQL_HOST="127.0.0.1"
export MYSQL_USER="app_user"
export MYSQL_DATABASE="app_db"

mysql -s -r -e "SELECT 1;"

完整示例脚本

#!/bin/bash
# 查询用户统计数据（带错误处理）

DB_HOST="${MYSQL_HOST:-127.0.0.1}"
DB_USER="${MYSQL_USER:-app_user}"
DB_NAME="${MYSQL_DATABASE:-app_db}"

QUERY="
SELECT JSON_OBJECT(
  'timestamp', NOW(),
  'summary', JSON_OBJECT(
    'total_users', (SELECT COUNT(*) FROM users),
    'active_users', (SELECT COUNT(*) FROM users WHERE status = 'active'),
    'new_today', (SELECT COUNT(*) FROM users WHERE DATE(created_at) = CURDATE())
  )
) AS report;
"

mysql -h "$DB_HOST" -u "$DB_USER" --database "$DB_NAME" -s -r -e "$QUERY" 2>&1 | jq .

安全建议

1. 禁止在命令行中直接写密码（进程列表可见）
2. 使用 MYSQL_PWD 环境变量或配置文件
3. 生产环境强制使用 SSL (--ssl-mode=REQUIRED)
4. 配置文件权限设置为 chmod 600 ~/.my.cnf
5. 查询操作使用只读账号

重要提示: 使用 -s -r (--silent --raw) 组合确保 mysql 客户端输出纯净数据，是生成有效 JSON 的前提。