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]