下面是一个完整的 Bash 脚本解决方案,可以计算 MySQL 数据库中所有表的行数。
#!/bin/bash
# 配置 MySQL 连接参数
DB_HOST="localhost"
DB_USER="username"
DB_PASS="password"
DB_NAME="database_name"
# 获取所有表名
TABLES=$(mysql -h $DB_HOST -u $DB_USER -p$DB_PASS $DB_NAME -e "SHOW TABLES;" | tail -n +2)
# 遍历每个表并计算行数
for TABLE in $TABLES; do
COUNT=$(mysql -h $DB_HOST -u $DB_USER -p$DB_PASS $DB_NAME -e "SELECT COUNT(*) FROM $TABLE;" | tail -n +2)
echo "$TABLE: $COUNT 行"
done
#!/bin/bash
# 配置 MySQL 连接参数
DB_HOST="localhost"
DB_USER="username"
DB_PASS="password"
DB_NAME="database_name"
# 直接查询 INFORMATION_SCHEMA 获取表行数
mysql -h $DB_HOST -u $DB_USER -p$DB_PASS $DB_NAME -e "
SELECT
TABLE_NAME,
TABLE_ROWS
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_SCHEMA = '$DB_NAME';
"
#!/bin/bash
# 配置 MySQL 连接参数
DB_HOST="localhost"
DB_USER="username"
DB_PASS="password"
DB_NAME="database_name"
# 临时文件存储 SQL 语句
SQL_FILE="/tmp/count_rows.sql"
# 生成包含所有 COUNT 查询的 SQL 文件
echo "SELECT 'Database Summary' AS '';" > $SQL_FILE
mysql -h $DB_HOST -u $DB_USER -p$DB_PASS $DB_NAME -e "SHOW TABLES" | tail -n +2 | while read TABLE; do
echo "SELECT '$TABLE' AS '表名', COUNT(*) AS '行数' FROM $TABLE;" >> $SQL_FILE
done
# 执行生成的 SQL 文件
mysql -h $DB_HOST -u $DB_USER -p$DB_PASS $DB_NAME < $SQL_FILE
# 清理临时文件
rm $SQL_FILE
count_table_rows.sh
chmod +x count_table_rows.sh
./count_table_rows.sh
如果需要更详细的输出,可以修改脚本以包含表大小等信息:
#!/bin/bash
DB_HOST="localhost"
DB_USER="username"
DB_PASS="password"
DB_NAME="database_name"
mysql -h $DB_HOST -u $DB_USER -p$DB_PASS $DB_NAME -e "
SELECT
TABLE_NAME AS '表名',
TABLE_ROWS AS '行数',
ROUND(DATA_LENGTH/1024/1024, 2) AS '数据大小(MB)',
ROUND(INDEX_LENGTH/1024/1024, 2) AS '索引大小(MB)',
ROUND((DATA_LENGTH+INDEX_LENGTH)/1024/1024, 2) AS '总大小(MB)'
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_SCHEMA = '$DB_NAME'
ORDER BY
(DATA_LENGTH+INDEX_LENGTH) DESC;
"
这个高级版本会显示每个表的行数以及存储空间使用情况,并按总大小降序排列。