MySQL 数据库字符集修改

mysql_change_charset.groovy

import groovy.sql.GroovyResultSet
import groovy.sql.Sql

import java.sql.ResultSet

/**
 * 这个程序用来修改 MySQL 数据库表中字段的字符集标注。适用于原先没有标注(latin1)，但存储的却是 GBK 编码的表格。
 * 
 * 如果原本存储的是 UTF-8 数据，把程序中的 gbk 替换为 utf8 即可。
 */
 
def ignore_tables = [
    'user_cache',
    'user_view',
    'online_user_view',
    'online_user',
    'online_user_test',
    'online_mobile',
    'rad_online_user',
    'rad_proxy_online_user']

def sql = Sql.newInstance("jdbc:mysql://localhost:3306/ipt_authd_remote?useUnicode=true&characterEncoding=UTF-8", "root", "123321", "com.mysql.jdbc.Driver")

BufferedWriter writer = new BufferedWriter(new FileWriter("res.sql"))

sql.eachRow("show tables") { GroovyResultSet table ->
    def name = table.getString(1)

    if (!(name in ignore_tables)) {
        String cmd = "show full columns FROM `${name}`"

        String alterCmd1 = "alter table `${name}`"
        String alterCmd2 = "alter table `${name}`"

        boolean isFirst = true
        boolean found = false
        sql.eachRow(cmd) { GroovyResultSet column ->
            String columnName = column.getString(1)
            String columnType = column.getString(2)
            String collationName = column.getString(3)
            String isNullable = column.getString(4)
            String defaultValue = column.getString(6)

            if ((columnType.startsWith('varchar') || columnType.startsWith('char')) && collationName.startsWith('latin1')) {
                found = true

                if (!isFirst) {
                    alterCmd1 += ', '
                    alterCmd2 += ", "
                } else {
                    isFirst = false
                }

                alterCmd1 += " modify column `${columnName}` " + columnType.replace('varchar', 'char').replace('char', 'varbinary')
                if (isNullable == 'YES') alterCmd1 += " NULL " else alterCmd1 += " NOT NULL "

                alterCmd2 += " modify column `${columnName}` ${columnType} character set 'gbk' collate " + collationName.replace('latin1_swedish', 'gbk_chinese').replace('latin1', 'gbk')
                if (isNullable == 'YES') alterCmd2 += " NULL " else alterCmd2 += " NOT NULL "
                if (defaultValue == 'NULL') alterCmd2 += " default null"
                else if (defaultValue) alterCmd2 += " default '${defaultValue}'"
            }

            if ((columnType.startsWith('text')) && collationName.startsWith('latin1')) {
                found = true

                if (!isFirst) {
                    alterCmd1 += ', '
                    alterCmd2 += ", "
                } else {
                    isFirst = false
                }

                alterCmd1 += " modify column `${columnName}` blob"
                if (isNullable == 'YES') alterCmd1 += " NULL " else alterCmd1 += " NOT NULL "

                alterCmd2 += " modify column `${columnName}` ${columnType} character set 'gbk' collate " + collationName.replace('latin1_swedish', 'gbk_chinese').replace('latin1', 'gbk')
                if (isNullable == 'YES') alterCmd2 += " NULL " else alterCmd2 += " NOT NULL "
                if (defaultValue == 'NULL') alterCmd2 += " default null"
                else if (defaultValue) alterCmd2 += " default '${defaultValue}'"
            }

        }
        alterCmd1 += ";"
        alterCmd2 += ";"

        writer.write("-- ${name}")
        writer.newLine()

        if (found) {
            writer.newLine()
            writer.write(alterCmd1)
            writer.newLine()
            writer.write(alterCmd2)
            writer.newLine()
            writer.newLine()
            writer.flush()
        }

        writer.write("alter table `${name}` default character set 'gbk';")
        writer.newLine()
    }

}

writer.close()