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.参考
有关编码，中文字符的官方解答，必看！！！
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)连接：
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.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]
