mysql set utf8_MySQL设置UTF8字符

最近看了下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语句中的反引号[`]是用来转义的

参考: