mysql 更改数据库字符编码的方法

mysql如何更改数据库字符编码？借助客户端工具很容易就可以更改了，下面来看一下。

01

打开navicat客户端工具，在左边找到要更改的数据库。

02

右键点击这个数据库，在菜单上点击数据库属性选项。

03

在d出的属性窗口上，点击默认字符集下的下拉框。

04

在d出的下拉选项里，点击选中要使用的字符集就行了，比如选择常用的utf8字符集，点击ok按钮就行了。

查看字符集设置

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.02 sec)

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| /usr/share/mysql/charsets/ |

+--------------------------+----------------------------+

8 rows in set (0.02 sec)

mysql>

其他的一些设置方法：

修改数据库的字符集

mysql>use mydb

mysql>alter database mydb character set utf-8

创建数据库指定数据库的字符集

mysql>create database mydb character set utf-8

通过配置文件修改:

修改/var/lib/mysql/mydb/db.opt

default-character-set=latin1

default-collation=latin1_swedish_ci

为

default-character-set=utf8

default-collation=utf8_general_ci

重起MySQL:

[root@bogon ~]# /etc/rc.d/init.d/mysql restart

通过MySQL命令行修改:

mysql>set character_set_client=utf8

Query OK, 0 rows affected (0.00 sec)

mysql>set character_set_connection=utf8

Query OK, 0 rows affected (0.00 sec)

mysql>set character_set_database=utf8

Query OK, 0 rows affected (0.00 sec)

mysql>set character_set_results=utf8

Query OK, 0 rows affected (0.00 sec)

mysql>set character_set_server=utf8

Query OK, 0 rows affected (0.00 sec)

mysql>set character_set_system=utf8

Query OK, 0 rows affected (0.01 sec)

mysql>set collation_connection=utf8

Query OK, 0 rows affected (0.01 sec)

mysql>set collation_database=utf8

Query OK, 0 rows affected (0.01 sec)

mysql>set collation_server=utf8

Query OK, 0 rows affected (0.01 sec)

查看:

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| /usr/share/mysql/charsets/ |

+--------------------------+----------------------------+

8 rows in set (0.03 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.04 sec)

---