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

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.参考

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

mysql>status;--------------

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

Connection id:21

Current database:Current user: root@localhostSSL:Not in use

Currentpager: stdout

Using outfile:''Using delimiter: ;

Server version:5.7.20MySQL Community Server (GPL)

Protocol version:10Connection: 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 54sec

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

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

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

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

classBaseCursor(object):def execute(self, query, args=None):ifisinstance(query, unicode):

query=query.encode(db.unicode_literal.charset)if args is notNone:ifisinstance(args, dict):

query= query %dict((key, db.literal(item))for key, item inargs.iteritems())else:

query= query % tuple([db.literal(item) for item inargs])print repr(query) #手动加一句方便调试

3.推荐使用姿势

(1)连接：

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

如果连接数据库用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()

(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.00sec)

mysql>\s--------------

mysql Ver 14.14 Distrib 5.5.56, forWin64 (AMD64)

Connection id:743

Current database:Current user: root@localhostSSL:Not in useUsing delimiter: ;

Server version:5.5.56MySQL Community Server (GPL)

Protocol version:10Connection: localhost via TCP/IP

Server characterset: utf8

Db characterset: utf8

Client characterset: utf8

Conn. characterset: utf8

TCP port:3306Uptime:1 day 4 min 47sec

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

eries per secondavg: 85.055

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

mysql> use temp

Databasechanged

mysql> show create database temp;+----------+---------------------------------------------------------------+

| Database | Create Database |

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

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

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

1 row in set (0.00sec)

mysql> show full columns fromtest;+-------+----------+-----------------+------+-----+---------+----------------+---------------------------------+---------+

| 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 setnames

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,andcharacter_set_resultsto the given character set. Setting

character_set_connectiontocharset_name also sets collation_connectionto the default collation forcharset_name. See

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

The optional COLLATE clause may be usedtospecify a collation

explicitly.If given, the collation must one ofthe permitted

collationsforcharset_name.

charset_nameand collation_name may be quoted orunquoted.

Thedefault mapping can be restored by using a value of DEFAULT. Thedefault depends onthe server configuration.

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

means that they donot work for SETNAMES.

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

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

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

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)更改表编码

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