MySQL环境
字符集: utf8mb4
乱码重现
- 要执行的 python 代码
#!/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 表记录如下输出文章源自运维生存时间-https://www.ttlsa.com/mysql/inception-backup-database-chinese-messy-code-resolve/
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$_$ 中的数据已经乱码了文章源自运维生存时间-https://www.ttlsa.com/mysql/inception-backup-database-chinese-messy-code-resolve/
3.将sql中添加 set names utf8mb4; 如下部分代码文章源自运维生存时间-https://www.ttlsa.com/mysql/inception-backup-database-chinese-messy-code-resolve/
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 表记录文章源自运维生存时间-https://www.ttlsa.com/mysql/inception-backup-database-chinese-messy-code-resolve/
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)
由上面可以看到中文乱码已经解决文章源自运维生存时间-https://www.ttlsa.com/mysql/inception-backup-database-chinese-messy-code-resolve/
文章源自运维生存时间-https://www.ttlsa.com/mysql/inception-backup-database-chinese-messy-code-resolve/
昵称: HH文章源自运维生存时间-https://www.ttlsa.com/mysql/inception-backup-database-chinese-messy-code-resolve/
QQ: 275258836文章源自运维生存时间-https://www.ttlsa.com/mysql/inception-backup-database-chinese-messy-code-resolve/
ttlsa群交流沟通(QQ群②: 6690706 QQ群③: 168085569 QQ群④: 415230207(新) 微信公众号: ttlsacom)文章源自运维生存时间-https://www.ttlsa.com/mysql/inception-backup-database-chinese-messy-code-resolve/
感觉本文内容不错,读后有收获?文章源自运维生存时间-https://www.ttlsa.com/mysql/inception-backup-database-chinese-messy-code-resolve/
逛逛衣服店,鼓励作者写出更好文章。文章源自运维生存时间-https://www.ttlsa.com/mysql/inception-backup-database-chinese-messy-code-resolve/ 文章源自运维生存时间-https://www.ttlsa.com/mysql/inception-backup-database-chinese-messy-code-resolve/

评论