HTML表中的mysql输出-Shell脚本

debugcn 发表于 Dev

乔斯纳·古普塔（Jyotsna gupta）

我正在执行一个mysql查询，在shell脚本中返回两列

TABLES=$($MYSQL -u $MUSER -p$MPASS -h $MHOST $MDB -e "SELECT table_name, ROUND(((data_length + index_length) / 1024 / 1024 / 1024), 2) AS table_size FROM information_schema.tables WHERE table_schema = 'inventory' AND CREATE_TIME < DATE(NOW()) -INTERVAL 7 DAY" | awk '{ print $1}' | grep -v '^table_name' )

如何将其存储在2D数组中并将结果显示为html表？

作为一种解决方法，我正在执行两个mysql查询并将结果存储在两个单独的数组中

    TABLES=$($MYSQL -u $MUSER -p$MPASS -h $MHOST $MDB -e "SELECT table_name FROM information_schema.tables WHERE table_schema = 'inventory' AND CREATE_TIME < DATE(NOW()) -INTERVAL 7 DAY" | awk '{ print $1}' | grep -v '^table_name' )

    declare -a TABLE_SIZE=($($MYSQL -u $MUSER -p$MPASS -h $MHOST $MDB -e "SELECT ROUND(((data_length + index_length) / 1024 / 1024 / 1024), 2) AS table_size FROM information_schema.tables WHERE table_schema = 'inventory'  AND CREATE_TIME < DATE(NOW()) -INTERVAL 7 DAY" | awk '{ print $1}' | grep -v '^table_size' ))
    #IF there are no tables in databse
    if [ "$TABLES" == "" ]
    then
            echo "Error - No table found in $MDB database!"
            exit 3
    fi
    i=0
    for t in $TABLES
    do
            #echo "Deleting $t table from $MDB database..."
            str="<tr><td> $t </td><td>${TABLE_SIZE[$i]}</td></tr>"
            i=`expr $i + 1`
            table_array+=$str
    done
    table_array+="</table>"

阿曼

一种选择是mysql使用-H选项和-N选项为您执行此操作，如果您不希望使用列名。

mysql -H -N -u $MUSER -p $MPASS -h $MHOST $MDB < your_sql

如果您需要其他html定制，则可以执行以下操作：

#!/bin/bash

# Set the internal file separator to newline.
IFS=$'\n'

echo "<table>"

# Loop over each row.
for line in $(mysql -N ... < your.sql)
do
  echo "  <tr>"

  # Set the internal file separator to space/tab.
  IFS=$' \t'

  # Create columns array.
  columns=($line)

  echo "    <td>${columns[0]}</td>"
  echo "    <td>${columns[1]}</td>"

  echo "  </tr>"
done

echo "</table>"

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。