python之MySQL MySQLdb 推荐使用姿势，解决中文乱码

2022-07-23 12:31:53

0.目录

2.setup
(1) 安装步骤，可以顺带安装mysql administrator和mysql query browser
(2) 安装完毕，修改 my.ini
(3) 重启 mysql 服务：
(4) cmd 确认可用：
(5) 安装 MySQLdb
(6) 脚本内部会先对 execute 的unicode query 参数进行编码，可以手动加一句 print repr(query) 方便调试，懒得打monkey_patch的话。。。

3.推荐使用姿势
(1)连接：
(2)创建数据库：不可省略COLLATE 'utf8_general_ci'，有(2)则(3)可以省略 DEFAULT CHARSET=utf8
(3)创建表

4.中文乱码或其他异常
(1)检查配置
(2)在Windows cmd 插入或查询中文出现乱码
(3)更改数据库编码
(4)更改表编码

1.参考

有关编码，中文字符的官方解答，必看！！！

A.11 MySQL 5.7 FAQ: MySQL Chinese, Japanese, and Korean Character Sets

mysql> status;

--------------

mysql  Ver 14.14 Distrib 5.7.20, for Linux (x86_64) using  EditLine wrapper

Connection id:          21

Current database:

Current user:           root@localhost

SSL:                    Not in use

Current pager:          stdout

Using outfile:          ''

Using delimiter:        ;

Server version:         5.7.20 MySQL Community Server (GPL)

Protocol version:       10

Connection:             Localhost via UNIX socket

Server characterset:    latin1

Db     characterset:    latin1

Client characterset:    utf8

Conn.  characterset:    utf8

UNIX socket:            /var/lib/mysql/mysql.sock

Uptime:                 4 days 23 hours 32 min 54 sec

Threads: 1  Questions: 186  Slow queries: 0  Opens: 141  Flush tables: 1  Open tables: 130  Queries per second avg: 0.000

--------------

2.setup

(1)安装步骤： Windows 7系统安装MySQL5.5.21图解，可以顺带安装mysql administrator和mysql query browser

14、在打开的字符编码的页面中，设置mysql要使用的字符编码，第一个是西文编码，第二个是多字节的通用utf8编码，第三个是手动，我们选择utf-8，如果在这里没有选择UTF-8这个编码的化，在使用JDBC连接数据库的时候，便会出现乱码，到那时需要加上如下的代码“useUnicode=true&characterEncoding=UTF-8 ”，才可以解决乱码问题，为了以后的方便所以设置为UTF-8编码，但是有一个问题，那就是当用哪个控制台插入汉字的时候，会报错，查询带汉字的表时，无法显示汉字，所在需要在每次进入MySQL后，设置一个系统参数“set names gbk”，即可解决该问题，接下来单击“next”，继续配置，如图所示：

(2) 安装完毕，修改 my.ini

客户端cmd打开mysql，执行插入中文报错或插入中文乱码解决方案

D:\Program Files\MySQL\MySQL Server 5.5

原来是latin1，修改如下：

# CLIENT SECTION  #will be read by MySQL client applications.

default-character-set=utf8

# SERVER SECTION    #will be used when a new schema or table

character-set-server=utf8

根据下文的 show variables like 'char%'; 显示：

CLIENT SECTION对应如下三项，可以被连接参数临时覆盖 --default-character-set=utf8
character_set_client | latin1
character_set_connection | latin1
character_set_results | latin1

SERVER SECTION对应如下二项：
character_set_database | latin1
character_set_server | latin1

(3) 重启 mysql 服务：

C:\Users\win7>net stop mysql

C:\Users\win7>net start mysql

(4) cmd 确认可用：

mysql -u root -p

回车然后输入密码

(5) 安装 MySQLdb

https://pypi.python.org/pypi/MySQL-python/1.2.5

http://www.lfd.uci.edu/~gohlke/pythonlibs/#mysql-python

(6) 脚本内部会先对 execute 的unicode query 参数进行编码，可以手动加一句 print repr(query) 方便调试，懒得打monkey_patch的话。。。

C:\Program Files\Anaconda2\Lib\site-packages\MySQLdb\cursors.py

class BaseCursor(object):

    def execute(self, query, args=None):

        if isinstance(query, unicode):

            query = query.encode(db.unicode_literal.charset)

        if args is not None:

            if isinstance(args, dict):

                query = query % dict((key, db.literal(item))

                                     for key, item in args.iteritems())

            else:

                query = query % tuple([db.literal(item) for item in args])

        print repr(query)  #手动加一句方便调试

3.推荐使用姿势

(1)连接：

Python调用MySQLdb插入中文乱码的问题

conn = MySQLdb.connect(host='localhost', user='root',passwd='123', db='account', charset='utf8') # OK

Python使用MySQL数据库(新)

如果连接数据库用localhost连接不上，可以换127.0.0.1

mysql -h localhost -u root -p --default-character-set=utf8

import MySQLdb
conn= MySQLdb.connect(
　　　　#host='localhost',   

       host='127.0.0.1',

       port = 3306,

       user='root',

       passwd='root',

       #db ='database_name',

       charset='utf8'

       )
curs = conn.cursor()

Mysql中文乱码问题完美解决方案

(2)创建数据库：不可省略COLLATE 'utf8_general_ci'，有(2)则(3)可以省略 DEFAULT CHARSET=utf8

curs.execute("""

CREATE DATABASE database_name

CHARACTER SET 'utf8'

COLLATE 'utf8_general_ci';

""")

#确认：
curs.execute('SHOW DATABASES')
curs.fetchall()

(3)创建表

curs.execute('USE database_name')

curs.execute("""

CREATE TABLE table_name(

    id int not null auto_increment,

    name char(50) null,

    cate char(50) null,

    date datetime null,

    primary key(id)

    )engine=InnoDB DEFAULT CHARSET=utf8;

""")

#确认：
curs.execute('SHOW TABLES')

4.中文乱码或其他异常

(1)检查配置

mysql> show variables like 'char%';

+--------------------------+---------------------------------------------------------+

| Variable_name            | Value                                                   |

+--------------------------+---------------------------------------------------------+

| character_set_client     | utf8                                                    |

| character_set_connection | utf8                                                    |

| character_set_database   | utf8                                                    |

| character_set_filesystem | binary                                                  |

| character_set_results    | utf8                                                    |

| character_set_server     | utf8                                                    |

| character_set_system     | utf8                                                    |

| character_sets_dir       | D:\Program Files\MySQL\MySQL Server 5.5\share\charsets\ |

+--------------------------+---------------------------------------------------------+

8 rows in set (0.00 sec)

mysql> \s

--------------

mysql  Ver 14.14 Distrib 5.5.56, for Win64 (AMD64)

Connection id:          743

Current database:

Current user:           root@localhost

SSL:                    Not in use

Using delimiter:        ;

Server version:         5.5.56 MySQL Community Server (GPL)

Protocol version:       10

Connection:             localhost via TCP/IP

Server characterset:    utf8

Db     characterset:    utf8

Client characterset:    utf8

Conn.  characterset:    utf8

TCP port:               3306

Uptime:                 1 day 4 min 47 sec

Threads: 4  Questions: 7373195  Slow queries: 1323  Opens: 4725  Flush tables: 1  Open tables: 0  Qu

eries per second avg: 85.055

--------------

mysql> use temp

Database changed

mysql> show create database temp;

+----------+---------------------------------------------------------------+

| Database | Create Database                                               |

+----------+---------------------------------------------------------------+

| temp     | CREATE DATABASE `temp` /*!40100 DEFAULT CHARACTER SET utf8 */ |

+----------+---------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> show full columns from test;

+-------+----------+-----------------+------+-----+---------+----------------+----------------------

-----------+---------+

| Field | Type     | Collation       | Null | Key | Default | Extra          | Privileges

           | Comment |

+-------+----------+-----------------+------+-----+---------+----------------+----------------------

-----------+---------+

| id    | int(11)  | NULL            | NO   | PRI | NULL    | auto_increment | select,insert,update,

references |         |

| name  | char(50) | utf8_general_ci | YES  |     | NULL    |                | select,insert,update,

references |         |

| cate  | char(50) | utf8_general_ci | YES  |     | NULL    |                | select,insert,update,

references |         |

| date  | datetime | NULL            | YES  |     | NULL    |                | select,insert,update,

references |         |

+-------+----------+-----------------+------+-----+---------+----------------+----------------------

-----------+---------+

4 rows in set (0.19 sec)

mysql> show variables like 'char%';

除了character_set_filesystem | binary，一般要求其他项都是 utf8，通过按照前文的 2.setup 修改 my.ini 可以永久生效。

mysql> set names utf8;

一次性修改 character_set_client, character_set_connection, and character_set_results

mysql> help set names

Name: 'SET NAMES'

Description:

Syntax:

SET NAMES {'charset_name'

    [COLLATE 'collation_name'] | DEFAULT}

This statement sets the three session system variables

character_set_client, character_set_connection, and

character_set_results to the given character set. Setting

character_set_connection to charset_name also sets collation_connection

to the default collation for charset_name. See

http://dev.mysql.com/doc/refman/5.5/en/charset-connection.html.

The optional COLLATE clause may be used to specify a collation

explicitly. If given, the collation must one of the permitted

collations for charset_name.

charset_name and collation_name may be quoted or unquoted.

The default mapping can be restored by using a value of DEFAULT. The

default depends on the server configuration.

ucs2, utf16, and utf32 cannot be used as a client character set, which

means that they do not work for SET NAMES.

URL: http://dev.mysql.com/doc/refman/5.5/en/set-names.html

(2)在Windows cmd 插入或查询中文出现乱码

mysql insert中文乱码无法插入ERROR 1366 (HY000): Incorrect string value: '\xB1\

需要每次连接后输入如下：

set character_set_client = gbk;

set character_set_results = gbk;

(3)更改数据库编码

ALTER DATABASE db_name CHARACTER SET [=] charset_name

ALTER DATABASE db_name COLLATE [=] collation_name

仅对当前连接有效，实际意义？？？：set character_set_database = utf8;

(4)更改表编码

MySQL 中文显示乱码看评论！

ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]

码农公寓

0.目录

1.参考

2.setup

3.推荐使用姿势

4.中文乱码或其他异常

相关文章