目录

• 修改mysql server配置
• 转换数据库/表/字段

修改mysql server配置

修改/etc/my.cnf

[mysqld]
...
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

转换数据库/表/字段

• 安装MySQL-python包，添加python的mysql库支持
  yum install MySQL-python

• 执行下面的python脚本转换数据库、表和字段的编码设置

#! /usr/bin/env python
import MySQLdb
import sys

host = raw_input(">>> host[localhost]:")
if host.strip() == "":
  host = "localhost"

dbname = raw_input(">>> database[shopxxb2b2c]:")
if dbname.strip() == "":
  dbname = "shopxxb2b2c"

user = raw_input(">>> user[root]:")
if user.strip() == "":
  user = "root"

passwd = raw_input(">>> password:")
if passwd.strip() == "":
  print("- You input the empty password")

print("===========================================================")
print("Try to upgrade db:{} with user:{} at:{}".format(dbname, user, host))
print("===========================================================")
confirm = raw_input(">>> confirm[Y/y]:")
if confirm.lower() != "y":
  print("Abort")
  sys.exit(1)

db = MySQLdb.connect(host=host, user=user, passwd=passwd, db=dbname)
cursor = db.cursor()

cursor.execute("ALTER DATABASE `%s` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci'" % dbname)

sql = "SELECT DISTINCT(table_name) FROM information_schema.columns WHERE table_schema = '%s'" % dbname
cursor.execute(sql)

results = cursor.fetchall()
for row in results:
  try:
    sql = "ALTER TABLE `%s` convert to character set DEFAULT COLLATE DEFAULT" % (row[0])
    cursor.execute(sql)
  except:
    cursor.execute("DESC `%s`" % (row[0]))
    desc = cursor.fetchall()
    for elem in desc:
      if "varchar" in elem[1]: #data type of the columns
        try:
            cursor.execute("ALTER TABLE `%s` MODIFY `%s` VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci" % (row[0], elem[0]))
        except:
            print("ALTER TABLE `%s` MODIFY `%s` VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci" % (row[0], elem[0]))
            continue
db.close()