【MySQL】Inception备份库表数据中文乱码解决

MySQL环境

字符集: utf8mb4

乱码重现

1. 要执行的 python 代码

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40

#!/usr/bin/python # -*- coding: utf-8 -*- import MySQLdb import sys reload ( sys ) sys . setdefaultencoding ( 'utf-8' ) sql = "" "     /*--user=HH;--password=oracle;--host=192.168.1.233;--execute=1;--enable-remote-backup;--port=3307;*/     inception_magic_start;     use test;     ALTER TABLE alifeba_user         MODIFY username VARCHAR(50) NOT NULL DEFAULT '' COMMENT '用户名',         MODIFY realname VARCHAR(50) NOT NULL DEFAULT '' COMMENT '真实姓名';     inception_magic_commit; " "" try :      conn = MySQLdb . connect ( host = '127.0.0.1' ,                          user = 'HH' ,                          passwd = 'oracle' ,                          db = 'inception' ,                          port = 6669 )      cur = conn . cursor ( )      ret = cur . execute ( sql )      result = cur . fetchall ( )      num_fields = len ( cur . description )      field_names = [ i [ 0 ] for i in cur . description ]      print ' | ' . join ( field_names )      for row in result :          print ' | ' . join ( [ str ( col ) for col in row ] )      cur . close ( )      conn . close ( ) except MySQLdb . Error , e :      err_msg = 'Mysql Error {arg1}: {arg2}' . format (                                      arg1 = e . args [ 0 ] ,                                      arg2 = e . args [ 1 ] )      print err_msg

2.执行后查看备份库中 $_$inception_backup_information$_$ 和 alifeba_user 表记录如下输出

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

SELECT * FROM $ _ $ inception_backup_information $ _ $ \ G * * * * * * * * * * * * * * * * * * * * * * * * * * * 1. row * * * * * * * * * * * * * * * * * * * * * * * * * * *          opid_time : 1473822723_81_1 start_binlog_file : start_binlog_pos : 0    end_binlog_file :    end_binlog_pos : 0      sql_statement : ALTER TABLE alifeba_user          MODIFY username VARCHAR ( 50 ) NOT NULL DEFAULT '' COMMENT '用户å<U+0090><U+008D>' ,          MODIFY realname VARCHAR ( 50 ) NOT NULL DEFAULT '' COMMENT '真实姓å<U+0090><U+008D>'              host : 192.168.1.233            dbname : test          tablename : alifeba_user              port : 3307              time : 2016 - 09 - 14 11 : 12 : 03              type : ALTERTABLE 1 row in set ( 0.00 sec ) SELECT * FROM alifeba _user \ G                       * * * * * * * * * * * * * * * * * * * * * * * * * * * 1. row * * * * * * * * * * * * * * * * * * * * * * * * * * *                  id : 1 rollback_statement : ALTER TABLE ` test ` . ` alifeba_user ` CHANGE COLUMN ` username ` ` username ` varchar ( 50 ) NOT NULL DEFAULT '' COMMENT '用户名' , CHANGE COLUMN ` realname ` ` realname ` varchar ( 50 ) NOT NULL DEFAULT '' COMMENT '真实姓名' ;

从上面的输出可以看到 $_$inception_backup_information$_$ 中的数据已经乱码了

3.将sql中添加 set names utf8mb4; 如下部分代码

1 2 3 4 5 6 7 8 9 10

sql = "" "     /*--user=HH;--password=oracle;--host=192.168.1.233;--execute=1;--enable-remote-backup;--port=3307;*/     inception_magic_start;     use test;     set names utf8mb4;     ALTER TABLE alifeba_user         MODIFY username VARCHAR(50) NOT NULL DEFAULT '' COMMENT '用户名',         MODIFY realname VARCHAR(50) NOT NULL DEFAULT '' COMMENT '真实姓名';     inception_magic_commit; " ""

4.执行修改后的代码再次查看 $_$inception_backup_information$_$ 和 alifeba_user 表记录

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

SELECT * FROM $ _ $ inception_backup_information $ _ $ \ G * * * * * * * * * * * * * * * * * * * * * * * * * * * 1. row * * * * * * * * * * * * * * * * * * * * * * * * * * *          opid_time : 1473823848_90_2 start_binlog_file : start_binlog_pos : 0    end_binlog_file :    end_binlog_pos : 0      sql_statement : ALTER TABLE alifeba_user          MODIFY username VARCHAR ( 50 ) NOT NULL DEFAULT '' COMMENT '用户名' ,          MODIFY realname VARCHAR ( 50 ) NOT NULL DEFAULT '' COMMENT '真实姓名'              host : 192.168.1.233            dbname : test          tablename : alifeba_user              port : 3307              time : 2016 - 09 - 14 11 : 30 : 48              type : ALTERTABLE 1 row in set ( 0.00 sec ) SELECT * FROM alifeba _user \ G * * * * * * * * * * * * * * * * * * * * * * * * * * * 1. row * * * * * * * * * * * * * * * * * * * * * * * * * * *                  id : 1 rollback_statement : ALTER TABLE ` test ` . ` alifeba_user ` CHANGE COLUMN ` username ` ` username ` varchar ( 50 ) NOT NULL DEFAULT '' COMMENT '用户名' , CHANGE COLUMN ` realname ` ` realname ` varchar ( 50 ) NOT NULL DEFAULT '' COMMENT '真实姓名' ;          opid_time : 1473823848_90_2 1 row in set ( 0.00 sec )

由上面可以看到中文乱码已经解决