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)
|
由上面可以看到中文乱码已经解决
文章转载来自：ttlsa.com
