linux修改/etc/my.cnf
win系统修改my.ini文件
注意: 5.1版本在mysqld下设置default-character-set=utf8, 5.5的设置变了, 我使用的环境是mysql 5.5
[client]
default
-
character
-
set
=utf8
[mysqld]
character
-
set
-server=utf8
init_connect=
'SET NAMES utf8'
[mysql]
default
-
character
-
set
=utf8
|
修改完成之后, 重启数据库, 可以查看
mysql> \s
--------------
mysql Ver 14.14 Distrib 5.5.27,
for
Win32 (x86)
Connection
id: 33
Current
database
:
Current
user
: root@localhost
SSL:
Not
in
use
Using delimiter: ;
Server version: 5.5.27 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: 3
min
53 sec
Threads: 1 Questions: 282 Slow queries: 0 Opens: 61 Flush tables: 1
Open
tables: 54 Queries per
second
avg
: 1.210
--------------
|
这样保证连接mysql之后, 默认都使用了UTF8编码
也可以查看全局变量
mysql> SHOW VARIABLES
LIKE
'character_set_%'
;
+
--------------------------+---------------------------------------------+
| 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 | XXX\mysql\share\charsets\ |
+
--------------------------+---------------------------------------------+
8
rows
in
set
(0.00 sec)
|
mysql> SHOW VARIABLES
LIKE
'collation_%'
;
+
----------------------+-----------------+
| Variable_name | Value |
+
----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+
----------------------+-----------------+
3
rows
in
set
(0.00 sec)
|
已经建立的数据库和表需要进行修改和更新, 分别对数据库和表进行, 数据量小可以考虑导出为sql语句后修改
修改数据库, 表和字段的sql语法参考如下
修改数据库, 以test数据库为例
mysql>
alter
database
`test`
character
set
utf8;
|
修改表为utf8, 以book表为例
mysql>
alter
table
`book`
character
set
utf8;
|
以book表的title字段为例
mysql>
alter
table
`books_book`
modify
`title`
varchar
(100)
character
set
utf8;
|
其中sql语句中的反引号[`]是用来转义的
转载于:https://blog.51cto.com/mmchy/1977183