MySQL设置UTF8字符
2013-03-31 19:20 会被淹死的鱼 阅读(3630) 评论(0) 编辑 收藏 举报最近看了下Django, 发现数据库中文编码问题, 需要修改数据库的编码, 统一为UTF8, 这样可以解决乱码问题
修改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语句中的反引号[`]是用来转义的
参考:
![](http://pic.cnblogs.com/face/u216123.jpg)
作者:icejoywoo
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利.
短网址: http://goo.gl/ZiZCi