mysql 支持utf8_mysql 支持utf8

因为要搞多语言版的项目，数据库是Mysql-4.1.9，使用PHP来操作Mysql，为了能使用utf8，今天搞了一天，终于搞定，期间也参考了好多资料，似乎都有问题，也许是没有找到真正的有用的资料，后来经过自己的“小聪明”，终于搞定！

注意：mysql 3.X的版本不支持utf8！

下面分别从mysql的安装，建数据库，建表以及PHP的连接来介绍！

1.安装mysql

很简单，我现在已经做好了yum升级

yum -y Mysql-server就OK了

2.创建数据库

进入mysql

mysql> create database bixuan character set utf8;

Query OK, 1 row affected (0.01 sec)

# 这里是创建了一个bixuan的数据库，其编码用：utf8

mysql>

# 下面是创建表，SQL语句如下

代码:

CREATE TABLE `type` (

`id` int(10) unsigned NOT NULL auto_increment,

`flag_deleted` enum('Y','N') character set utf8 NOT NULL default 'N',

`flag_type` int(5) NOT NULL default '0',

`type_name` varchar(50) character set utf8 NOT NULL default '',

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

这样，数据库：bixuan和表type以及type表里的字段flag_deleted和type_name都支持了utf8！

如果修改数据库成utf8的：

mysql> alter database bixuan character set utf8;

修改表默认用utf8：

mysql> alter table type character set utf8;

修改字段用utf8：

mysql> alter table type modify type_name varchar(50) CHARACTER SET utf8;

-----------------------------------------

修改配置文件,试数据库支持utf8

修改my.cnf

[client]

default-character-set = utf8

[mysqld]

default-character-set=utf8

[mysql]

default-character-set=utf8

修改后,重起mysql,查看参数

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)

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       | /opt/aimm/aimm/mysql5/share/mysql/charsets/ |

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

如果不行

[mysqld]

default-character-set=utf8

skip-character-set-client-handshake

就ok

1，设置mysql默认编码

首先查询一下show variables like 'character%';

确定所有的character都设置成utf-8

mysql>show variables like 'character%';

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

| 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/ |

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

2，

a) 设置web容器的编码格式。为你的servlet的doGet或doPost方法开始处加入如下代码：

request.setCharacterEncoding("utf-8");

response.setCharacterEncoding("utf-8");

b) 为每个jsp页面指定其编码格式。

c) 在连接数据库用的URL后加入：useUnicode=true;characterEncoding=utf-8　如：

url="jdbc:mysql:///db1?useUnicode=true&characterEncoding=utf-8"，

如果是xml文件中     url="jdbc:mysql:///db1?useUnicode=true&characterEncoding=utf-8"，

3,

如果还出现乱码，则有可能是 表中的字段 的编码不是utf-8;

如果出现乱码的表是 book

mysql> show create table book;

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

| Table | Create Table|

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

|book | create table 'book'(

'id'  int(10) unsigned NOT NULL AUTO_INCREMENT,

'bookname'  varchar(45)  CHARACTER SET latin1 NOT NULL,

'source'  varchar(45)   CHARACTER SET latin1 NOT NULL,

PRIMARY KEY('id')

) ENGINE= MyISAM DEFAULT CHARSET=utf8|

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

如果发现字段的字符集不是UTF8. 则对其进行修改

采用其他客户端工具来设定，例如 Navicat/MySQL Front/PhpMyAdmin

或者采用 ALTER 语法来转换字段的字符集ALTER TABLE book CHANGE bookname bookname CHAR(20) CHARACTER SET utf8;我在网上找到很多前2点的资料，但是怎么弄都还是乱码，最后才发现原来是字段的原因，折腾死了都。参考资料：