MySQL批量修改指定库的所有表的字符集 & collation

 

#!/bin/bash

host=192.168.100.13
user=root
password=password
declare -a database
database=(CJML_QxbEPC CJML_VIN Grab)

function bacula() {
        #echo $1
        mysql -h"$host" -u$user --password=$password --skip-column-names -e "show full tables from $1 where table_type = 'BASE TABLE'" 2>/dev/null | awk '{print $1}' | while read b;do
                #echo $b
                #echo -e database $1 '\t\t' table "\e[7m$b\e[0m" '\t\t'convert to character set utf8mb4 collation utf8mb4_general_ci
                printf '%s \e[5m%-15s\e[0m %s \e[7m%-30s\e[0m %s' database $1 table $b 'convert to character set utf8mb4 collation utf8mb4_general_ci'
                echo

                mysql -h$host -u$user --password=$password -e "set @@session.foreign_key_checks=0;alter table ${1}.$b convert to character set utf8mb4 collate utf8mb4_general_ci" 2>/dev/null;
        done
}


function main() {
        for b in ${database[@]};do
                bacula $b
        done
}

main