mysql - Mysql显示汉字为乱码，而Pymysql取数据正常

问题描述

我使用 pymysql 插入数据（中文）。代码正在运行，当我使用 fetchall 并打印结果时结果正常。但是，当我手动登录mysql时，发现使用“select * from TB”时数据是乱码。字符编码应该没问题，因为当我手动将中文插入新表时它可以工作。

请问有什么建议吗？

当我运行“show create database taoya”和“show create table TB”时，我检查了编码是“utf-8”。

变量如下：

    +--------------------------------------+--------------------------------+
| Variable_name                        | Value                          |
+--------------------------------------+--------------------------------+
| character_set_client                 | utf8mb4                        |
| character_set_connection             | utf8mb4                        |
| character_set_database               | utf8mb4                        |
| character_set_filesystem             | binary                         |
| character_set_results                | utf8mb4                        |
| character_set_server                 | utf8mb4                        |
| character_set_system                 | utf8                           |
| character_sets_dir                   | /usr/share/mysql-8.0/charsets/ |
| validate_password.special_char_count | 1                              |
+--------------------------------------+--------------------------------+

代码如下：

mysql= pymysql.connect(host=xx,user=xx,password=xxx, db="taoya",charset="utf8mb4")

try:
    with mysql.cursor() as cur:
        # cur.execute("set names 'utf8'")
        cur.execute("DROP TABLE IF EXISTS TB")
        sql = '''CREATE TABLE IF NOT EXISTS TB(
        Commody CHAR(70) NOT NULL,
        Store   CHAR(20),
        Address CHAR(20),
        Price CHAR(20),
        Paid CHAR(40))
        DEFAULT CHARSET=utf8mb4
        '''
        cur.execute(sql)

        insertsql="INSERT INTO TB (Commody,Store,Address, Price,Paid) VALUES (%s, %s,%s,%s,%s)"
        data= ("牛 啊","hi","你好","$33","3人")

        cur.execute(insertsql,data)

        cur.execute("SELECT * FROM TB;")
        result = cur.fetchall()
        print(result)
        mysql.commit()
finally:
    mysql.close()

结果没问题：

(('牛 啊', 'hi', '你好', '$33', '3人'),)

从mysql方面：

mysql> select * from TB;
+------------+-------+---------+-------+------+
| Commody    | Store | Address | Price | Paid |
+------------+-------+---------+-------+------+
| 鐗涢€鍟   | hi    | 浣犲ソ    | $33   | 3浜 |
+------------+-------+---------+-------+------+

标签： mysqlpymysql

Python 2

#coding:utf-8  #for .py file
import sys
import locale
import os
import codecs

reload(sys)
print sys.getdefaultencoding() + "  - sys.getdefaultencoding()"
sys.setdefaultencoding('utf8')
print sys.getdefaultencoding() + "  - sys.getdefaultencoding()"
print sys.stdout.encoding + " - sys.stdout.encoding:"

Linux?

echo 中文汉字;
env|grep LANG
export LANG=zh_CN.UTF-8

Don't forget to check your Terminal tool's encoding setting.

MySQL config:

check:
show variables like 'character%'; 
SHOW VARIABLES LIKE 'collation%';

for client:
SET character_set_client = 'utf8mb4';
SET character_set_connection = 'utf8mb4';
SET character_set_results = 'utf8mb4';

/*
for server:
SET collation_connection = 'utf8mb4_general_ci';
SET collation_database = 'utf8mb4_general_ci';
SET collation_server = 'utf8mb4_general_ci';
*/

mysql - Mysql显示汉字为乱码，而Pymysql取数据正常

问题描述

解决方案

推荐阅读