【Shell】Mysql表记录数量统计

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

#!/bin/bash

# MySQL连接信息
MYSQL_USER="root"
MYSQL_PASSWORD="xjrmd@2022"
MYSQL_HOST="192.168.0.254"
MYSQL_DB="moda"
MYSQL_PORT=3316

# 连接到MySQL并获取所有表名
tables=$(mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -h$MYSQL_HOST -P$MYSQL_PORT $MYSQL_DB -e "SHOW TABLES;" | awk '{if (NR!=1) {print $1}}')

# 创建一个数组来存储表名和记录数量
table_counts=()

# 遍历所有表，计算记录数量并存储在数组中
for table in $tables
do
    count=$(mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -h$MYSQL_HOST -P$MYSQL_PORT $MYSQL_DB -e "SELECT COUNT(*) FROM $table;" | awk 'NR!=1')

    # 将记录数量除以一万并保留一位小数，然后添加“万”单位
    count_in_10_thousands=$(awk -v count="$count" 'BEGIN { printf "%.1f", count / 10000 }')
    table_counts+=("$table:$count_in_10_thousands 万")

done

# 根据记录数量对表进行排序并输出
# IFS=$'\n' sorted_table_counts=($(sort -t':' -k2n <<<"${table_counts[*]}"))
# unset IFS

# 根据记录数量对表进行排序（倒序）并输出
IFS=$'\n' sorted_table_counts=($(sort -t':' -k2nr <<<"${table_counts[*]}"))
unset IFS

# 输出排序后的结果
for table_count in "${sorted_table_counts[@]}"
do
    table=$(echo $table_count | cut -d':' -f1)
    count=$(echo $table_count | cut -d':' -f2)
    echo "Table: $table, Record Count: $count"
done