- A+
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) |
由上面可以看到中文乱码已经解决
昵称: HH
QQ: 275258836
ttlsa群交流沟通(QQ群②: 6690706 QQ群③: 168085569 QQ群④: 415230207（新） 微信公众号: ttlsacom)
感觉本文内容不错，读后有收获？
微信公众号
扫一扫关注运维生存时间公众号，获取最新技术文章~
