Inception备份库表数据中文乱码解决
亿网MySQL环境
字符集: utf8mb4
乱码重现
- 要执行的 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 ) |
由上面可以看到中文乱码已经解决
文章转载来自：trustauth.cn