Description:
----[For better reports, please attach the log file after submitting. You can find it in C:\Users\Tiger\AppData\Roaming\MySQL\Workbench\log\wb.log]
using python 2.7 , MySQLdb to insert values into mysql. before inserting, coding is UTF-8, but using MySQL WorkBenck to query, messy codes of Chinese characters happens.
However, I use python, MySQLdb to select some data from mysql, it's normal.the example in the following:
sql = "select * from user_profile where user_id= 'b6f247e7112f4bf0a5a66ffdbf89d29e'"
cursor.execute(sql)
I get:
('b6f247e7112f4bf0a5a66ffdbf89d29e', '\xe5\x90\x89\xe8\xb7\xaf', 'male', '\xe6\x88\x91\xe7\x88\xb1\xe6\xa0\x91\xe6\x9c\xa8', '', '\xe7\x94\x9f\xe7\x89\xa9\xe5\xb7\xa5\xe7\xa8\x8b', '', '', '', '', 'description', 0L, 71L, 0L, 21L, 166L, 0L, 20L, 28L')
print('\xe8\x90\x8c\xe8\x99\xab')
print('\xe6\x88\x91\xe7\x88\xb1\xe6\xa0\x91\xe6\x9c\xa8')
print('\xe7\x94\x9f\xe7\x89\xa9\xe5\xb7\xa5\xe7\xa8\x8b')
I get:
萌虫
我爱树木
生物工程
however:
print(u'\xe8\x90\x8c\xe8\x99\xab')
print(u'\xe6\x88\x91\xe7\x88\xb1\xe6\xa0\x91\xe6\x9c\xa8')
print(u'\xe7\x94\x9f\xe7\x89\xa9\xe5\xb7\xa5\xe7\xa8\x8b')
èè«
æç±æ æ¨
çç©å·¥ç¨
name = '\xe8\x90\x8c\xe8\x99\xab'
name2 = '萌虫'
print(name2==name)
True
How to repeat:
Mess codes like:
'b6f247e7112f4bf0a5a66ffdbf89d29e', 'å‰è·¯', 'male', 'æˆ‘çˆ±æ ‘æœ¨', '', '生物工程', '', '', '', '', 'description', '0', '71', '0', '21', '166', '0', '20', '28''
while in python:
å‰è·¯萌虫
æˆ‘çˆ±æ ‘æœ¨我爱树木
生物工程生物工程
Suggested fix:
I using pycharm 5.0 to develop, the file encoding is utf-8, and has
# -*- coding: UTF-8 -*-
in my.ini of mysql, mysql and mysqldb is also set default utf-8:
[mysql]
default-character-set=utf8
[mysqld]
default-character-set=utf8
in creating table also set character setting default utf-8:
CREATE TABLE IF NOT EXISTS `xxx`.`user_profile` (
`user_id` varchar(50) NOT NULL,
`name` VARCHAR(30) CHARACTER SET 'utf8' NULL,
`gender` VARCHAR(10) CHARACTER SET 'utf8' NULL,
`short_desc` VARCHAR(45) CHARACTER SET 'utf8' NULL,
`location` VARCHAR(45) CHARACTER SET 'utf8' NULL,
`industry` VARCHAR(60) CHARACTER SET 'utf8' NULL,
`company` varchar(30) CHARACTER SET 'utf8' NULL,
`job` VARCHAR(45) CHARACTER SET 'utf8' NULL,
`school` VARCHAR(100) CHARACTER SET 'utf8' NULL,
`major` VARCHAR(100) NULL,
PRIMARY KEY (`user_id`)) DEFAULT CHARACTER SET = 'utf8' COLLATE utf8_general_ci
ENGINE = InnoDB
Description: ----[For better reports, please attach the log file after submitting. You can find it in C:\Users\Tiger\AppData\Roaming\MySQL\Workbench\log\wb.log] using python 2.7 , MySQLdb to insert values into mysql. before inserting, coding is UTF-8, but using MySQL WorkBenck to query, messy codes of Chinese characters happens. However, I use python, MySQLdb to select some data from mysql, it's normal.the example in the following: sql = "select * from user_profile where user_id= 'b6f247e7112f4bf0a5a66ffdbf89d29e'" cursor.execute(sql) I get: ('b6f247e7112f4bf0a5a66ffdbf89d29e', '\xe5\x90\x89\xe8\xb7\xaf', 'male', '\xe6\x88\x91\xe7\x88\xb1\xe6\xa0\x91\xe6\x9c\xa8', '', '\xe7\x94\x9f\xe7\x89\xa9\xe5\xb7\xa5\xe7\xa8\x8b', '', '', '', '', 'description', 0L, 71L, 0L, 21L, 166L, 0L, 20L, 28L') print('\xe8\x90\x8c\xe8\x99\xab') print('\xe6\x88\x91\xe7\x88\xb1\xe6\xa0\x91\xe6\x9c\xa8') print('\xe7\x94\x9f\xe7\x89\xa9\xe5\xb7\xa5\xe7\xa8\x8b') I get: 萌虫 我爱树木 生物工程 however: print(u'\xe8\x90\x8c\xe8\x99\xab') print(u'\xe6\x88\x91\xe7\x88\xb1\xe6\xa0\x91\xe6\x9c\xa8') print(u'\xe7\x94\x9f\xe7\x89\xa9\xe5\xb7\xa5\xe7\xa8\x8b') èè« æç±æ æ¨ çç©å·¥ç¨ name = '\xe8\x90\x8c\xe8\x99\xab' name2 = '萌虫' print(name2==name) True How to repeat: Mess codes like: 'b6f247e7112f4bf0a5a66ffdbf89d29e', 'å‰è·¯', 'male', 'æˆ‘çˆ±æ ‘æœ¨', '', '生物工程', '', '', '', '', 'description', '0', '71', '0', '21', '166', '0', '20', '28'' while in python: å‰è·¯萌虫 æˆ‘çˆ±æ ‘æœ¨我爱树木 生物工程生物工程 Suggested fix: I using pycharm 5.0 to develop, the file encoding is utf-8, and has # -*- coding: UTF-8 -*- in my.ini of mysql, mysql and mysqldb is also set default utf-8: [mysql] default-character-set=utf8 [mysqld] default-character-set=utf8 in creating table also set character setting default utf-8: CREATE TABLE IF NOT EXISTS `xxx`.`user_profile` ( `user_id` varchar(50) NOT NULL, `name` VARCHAR(30) CHARACTER SET 'utf8' NULL, `gender` VARCHAR(10) CHARACTER SET 'utf8' NULL, `short_desc` VARCHAR(45) CHARACTER SET 'utf8' NULL, `location` VARCHAR(45) CHARACTER SET 'utf8' NULL, `industry` VARCHAR(60) CHARACTER SET 'utf8' NULL, `company` varchar(30) CHARACTER SET 'utf8' NULL, `job` VARCHAR(45) CHARACTER SET 'utf8' NULL, `school` VARCHAR(100) CHARACTER SET 'utf8' NULL, `major` VARCHAR(100) NULL, PRIMARY KEY (`user_id`)) DEFAULT CHARACTER SET = 'utf8' COLLATE utf8_general_ci ENGINE = InnoDB