Windows下MySQL UTF8 编码设置(解决中文乱码)

1 MySQL设置

在根目录下面有几个已经写好的"my-"开头的ini文件，选一个适合你的，如：my-small.ini。复制一份，将文件名修改为my.ini，添加以下内容：

[client]
#password  = your_password
port    = 3306
socket    = D:/servers/MySQL Server 5.5/tmp/mysql.sock
loose-default-character-set=utf8
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
loose-default-character-set=utf8
character-set-server = utf8
basedir=D:/servers/MySQL Server 5.5
datadir=D:/servers/MySQL Server 5.5/data
port    = 3306
socket    = D:/servers/MySQL Server 5.5/tmp/mysql.sock
skip-external-locking
key_buffer_size = 16K
max_allowed_packet = 1M
table_open_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 128K

注意loose-,新版mysql不支持default-character-set=utf8 的设置http://forums.mysql.com/read.php?103,189835,237318 

重启MySQL

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:\servers\MySQL Server 5.5\share\charsets\ |
+--------------------------+---------------------------------------------+
8 rows in set (0.00 sec)

2 建库设置

mysql> create database test1
    -> character set utf8
    -> COLLATE utf8_general_ci ;

COLLATE utf8_general_ci 可以不写,MySQL可以自动校对

3 建表设置

CREATE TABLE `city` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `code` int(11) NOT NULL DEFAULT '0',
  `parent_code` int(11) NOT NULL DEFAULT '0',
  `city_name` varchar(100) NOT NULL DEFAULT '',
  `py_name` varchar(100) NOT NULL DEFAULT '',
  `province` varchar(100) DEFAULT NULL,
  `country` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `code` (`code`,`py_name`,`city_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

4 Java访问URL设置

jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8"

修改mysql默认字符集的方法