批量修改数据库表

#!/bin/bash

export MYSQL_PWD='***'
DBRUN="mysql -A -N -h ***.***.***.*** -P 3306 -u username"


sql_tables_info="
SELECT table_schema, table_name FROM information_schema.TABLES
WHERE table_type = 'base table' AND table_name REGEXP 'e_email_[0-9]+' AND table_schema LIKE 'test%'
ORDER BY table_schema, table_name;
"
(
$DBRUN -e "$sql_tables_info" | (
    while read line
    do
        database_name=$( echo $line | awk  '{print $1}' )
        table_name=$( echo $line | awk  '{print $2}' )
        sqlstr_add_faild="
            ALTER TABLE ${database_name}.${table_name}
            MODIFY COLUMN subject  varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '邮件主题' AFTER reply_name;
        "
        $DBRUN -e "$sqlstr_add_faild"

        if [ $? -eq 0 ]; then
            echo "$( date "+%Y-%m-%d %H:%M:%S" ):[${database_name}.${table_name}]:succceed!!!"
#            echo -e ${sqlstr_add_faild} >> add_e_email_number_sql.succeed
        else
            echo "$( date "+%Y-%m-%d %H:%M:%S" ):[${database_name}.${table_name}]:failed!!!"
            echo -e ${sqlstr_add_faild} >> add_e_email_number_sql.err
        fi
   done
)
) >> add_faild_e_email-test.log 2>&1