MySql 编码设置实际应用代码示例

以下的文章主要讲述的是MySql 编码设置的实际操作步骤，我们大家都知道MySQL 4.1的实际应用字符集所支持(Character Set Support)主要有2个方面：即，字符集(Character set)与其实际的排序方式(Collation)。 对于实际应用的相关字符集的支持细化到四个层次:

以下的文章主要讲述的是MySql 编码设置的实际操作步骤，我们大家都知道MySQL 4.1的实际应用字符集所支持(Character Set Support)主要有2个方面：即，字符集(Character set)与其实际的排序方式(Collation)。

对于实际应用的相关字符集的支持细化到四个层次:

服务器(server)，数据库(database)，数据表(table)和连接(connection)。

查看系统的字符集和排序方式的设定可以通过下面的两条命令或mysql> status 。

<ol class="dp-xml">
<li class="alt"><span><span>mysql</span><span class="tag">></span><span> SHOW VARIABLES LIKE 'character_set_%';   </span></span></li>
<li><span>+--------------------------+----------------------------+   </span></li>
<li class="alt"><span>| Variable_name | Value |   </span></li>
<li><span>+--------------------------+----------------------------+   </span></li>
<li class="alt"><span>| character_set_client | latin1 |   </span></li>
<li><span>| character_set_connection | latin1 |   </span></li>
<li class="alt"><span>| character_set_database | latin1 |   </span></li>
<li><span>| character_set_results | latin1 |   </span></li>
<li class="alt"><span>| character_set_server | latin1 |   </span></li>
<li><span>| character_set_system | utf8 |   </span></li>
<li class="alt"><span>| character_sets_dir | /usr/share/mysql/charsets/ |   </span></li>
<li><span>+--------------------------+----------------------------+   </span></li>
<li class="alt"><span>7 rows in set (0.00 sec)   </span></li>
<li><span> </span></li>
<li class="alt">
<span>mysql</span><span class="tag">></span><span> SHOW VARIABLES LIKE 'collation_%';   </span>
</li>
<li><span>+----------------------+-------------------+   </span></li>
<li class="alt"><span>| Variable_name | Value |   </span></li>
<li><span>+----------------------+-------------------+   </span></li>
<li class="alt"><span>| collation_connection | latin1_swedish_ci |   </span></li>
<li><span>| collation_database | latin1_swedish_ci |   </span></li>
<li class="alt"><span>| collation_server | latin1_swedish_ci |   </span></li>
<li><span>+----------------------+-------------------+   </span></li>
<li class="alt"><span>3 rows in set (0.00 sec)  </span></li>
</ol>

上面列出的值就是系统的默认值。(很奇怪系统怎么默认是latin1的瑞典语排序方式)...

当我们按照原来的方式通过PHP存取MySQL数据库时，就算设置了表的默认字符集为utf8并且通过UTF-8编码发送查询，你会发现存入数据库的仍然是乱码。问题就出在这个connection连接层上。MySql 编码设置解决方法是在发送查询前执行一下下面这句：

1.SET NAMES 'utf8';

它相当于下面的三句指令：

<ol class="dp-xml">
<li class="alt"><span><span>SET </span><span class="attribute">character_set_client</span><span> = </span><span class="attribute-value">utf8</span><span>;   </span></span></li>
<li>
<span>SET </span><span class="attribute">character_set_results</span><span> = </span><span class="attribute-value">utf8</span><span>;   </span>
</li>
<li class="alt">
<span>SET </span><span class="attribute">character_set_connection</span><span> = </span><span class="attribute-value">utf8</span><span>;  </span>
</li>
</ol>

2. 创建数据库

<ol class="dp-xml"><li class="alt"><span><span>mysql</span><span class="tag">></span><span> create database name character set utf8;  </span></span></li></ol>

3. 创建表

<ol class="dp-xml">
<li class="alt"><span><span>CREATE TABLE `type` (   </span></span></li>
<li><span>`id` int(10) unsigned NOT NULL auto_increment,   </span></li>
<li class="alt"><span>`flag_deleted` enum('Y','N') character set utf8 NOT NULL default 'N',   </span></li>
<li><span>`flag_type` int(5) NOT NULL default '0',   </span></li>
<li class="alt"><span>`type_name` varchar(50) character set utf8 NOT NULL default '',   </span></li>
<li><span>PRIMARY KEY (`id`)   </span></li>
<li class="alt">
<span>) DEFAULT </span><span class="attribute">CHARSET</span><span>=</span><span class="attribute-value">utf8</span><span>;  </span>
</li>
</ol>

4. 修改数据库成utf8的.

<ol class="dp-xml"><li class="alt"><span><span>mysql</span><span class="tag">></span><span> alter database name character set utf8;  </span></span></li></ol>

5. 修改表默认用utf8.

<ol class="dp-xml"><li class="alt"><span><span>mysql</span><span class="tag">></span><span> alter table type character set utf8;  </span></span></li></ol>

6. 修改字段用utf8

 

<ol class="dp-xml"><li class="alt"><span><span>mysql</span><span class="tag">></span><span> alter table type modify type_name varchar(50) CHARACTER SET utf8;  </span></span></li></ol>

以上的相关内容就是对MySql 编码设置的介绍，望你能有所收获。