MySQL数据库编码问题深入分析

MySQL字符集的原理介绍。摘录于官方文档。http://dev.mysql.com/doc/refman/5.1/zh/charset.html

不同的编码格式会导致同一字符,在不同字符集下的编码会不同。同样同一编码在不同的字符集中代码的字符也不相同。当你的MySQL返回的字符串的编码格式(字符集)与你的客户工具程序(mysql, php, query browser, …)当前使用的字符集不同时,就会造成乱码。

MySQL中默认字符集的设置有四级:服务器级,数据库级,表级 。最终是字段级 的字符集设置。注意前三种均为默认设置,并不代码你的字段最终会使用这个字符集设置。所以我们建议要用show create table table ; 或show full fields from tableName; 来检查当前表中字段的字符集设置。

MySQL中关于连接环境的字符集设置有  Client端,connection, results 通过这些参数,MySQL就知道你的客户端工具用的是什么字符集,结果集应该是什么字符集。这样MySQL就会做必要的翻译,一旦这些参数有误,自然会导致字符串在转输过程中的转换错误。基本上99%的乱码由些造成。

乱码后需要检查的信息:

1. 数据库表中字段的字符集设置 。show create table TableName 或 show full columns from tableName

mysql> show create table t1;
+——-+————————————
| Table | Create Table                       
+——-+————————————
| t1    | CREATE TABLE t1 (
  id int(11) NOT NULL,
  c1 varchar(30) DEFAULT NULL,
  PRIMARY KEY (id)    
) ENGINE=MyISAM DEFAULT CHARSET=gbk |
+——-+————————————
1 row in set (0.00 sec) 
                        
mysql> show full columns from t1;
+——-+————-+—————-+——+—–+-
| Field | Type        | Collation      | Null | Key | 
+——-+————-+—————-+——+—–+-
| id    | int(11)     | NULL           | NO   | PRI | 
| c1    | varchar(30) | gbk_chinese_ci | YES  |     | 
+——-+————-+—————-+——+—–+-
2 rows in set (0.00 sec)
 

mysql>

2. 当前联接系统参数  show variables like ‘char%’

mysql> show variables like ‘char%’;
+————————–+—————-
| Variable_name            | Value
+————————–+—————-
| character_set_client     | gbk
| character_set_connection | gbk
| character_set_database   | latin1
| character_set_filesystem | binary
| character_set_results    | gbk
| character_set_server     | latin1
| character_set_system     | utf8
| character_sets_dir       | C:\Program File
+————————–+—————-
8 rows in set (0.00 sec)
 

mysql>

1. 中文,请确保 表中该字段的字符集为中文兼容: 
 big5     | Big5 Traditional Chinese
 gb2312   | GB2312 Simplified Chinese
 gbk      | GBK Simplified Chinese
 utf8     | UTF-8 Unicode

2. 确保,联接参数与这个字段字符集一致,你可以用 set name ‘charsetname’; 
 比如, set name ‘gbk’;
 这条命令会同时修改 character_set_client,character_set_connection,character_set_results
 (如果你的这架MySQL中都为中文,则你可以在my.ini或my.cnf中加上或修改这个参数, 参数文件修改后需重启MySQL服务)
[mysql]
default-character-set=gbk

3. PHP 乱码, 同样 mysql_query(“set name ‘gbk'”); 其它API也类似。

4. phpmyadmin里乱码 
phpMyAdmin的config.inc.php中有没有设置$cfg[‘DefaultCharset’]=’utf-8′;

5. Windows操作系统中命令行(”DOS”窗口)下。 
 在你的DOS窗中的左上角标题栏片左键,属性,
 在字体中,选择“宋体”,确认
 mysql中 set names ‘gbk’;

6. ADO.NET, ADO中 ,可以连接字符串中加入CharSet=UTF8;类似指令以说明connection的字符集。
 Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword; CharSet=UTF8;

7. SQL Manager for MySQL

用EMS建数据库,

 Character Set设为utf-8

 client charset设UTF-8

 Font charset 设为GB2312_CHARSET

8. jdbcodbc桥接 http://java.sun.com/j2se/1.4.2/docs/guide/jdbc/bridge.html

       // Load the JDBC-ODBC bridge driver 
       Class.forName(sun.jdbc.odbc.JdbcOdbcDriver) ;// setup the properties 
       java.util.Properties prop = new java.util.Properties();
       prop.put(
  charSet   Big5  );
       prop.put(
  user  , username);
       prop.put(
  password  , password);// Connect to the database 
       con = DriverManager.getConnection(url, prop);

       

9.  PHP 5.2 版本以上解决乱码问题的一个方法 (由 ljf_ljf [Mark Liang] 提供)

    $conn = mysql_connect (  192.168.1.133  ,  root  ,  123456  ) or
        
die (  Could not connect:  . mysql_error ());$program_char =  utf8  ;$conn . mysql_select_db (  test  );
    
// $conn.mysql_query(‘SET @@character_set_results = “‘.$program_char.'”‘); 
    
    mysql_set_charset(
 $program_char , $conn ); 
    
$charset = mysql_client_encoding ( $conn );
    
printf (  current character set is %s <br>  , $charset );
    
$result = mysql_query (  SELECT id, task_no,pack_path FROM tb_workplan where id = 1  , $conn );
    
while ( $row = mysql_fetch_array ( $result , MYSQL_BOTH)) {
        
printf (  ID: %s <br> task_no: %s  <br> pack_path :%s <BR>  , $row [  id  ] , $row [ 1 ] , $row [  pack_path  ]);  
    }
    
$conn . mysql_free_result ( $result );
    
$conn . mysql_close ();

    

9.  存储过程参数乱码

create procedure t ( aa char(10) charset ‘gbk’)

 

+————-+——————+——+—–+———+—————-+
| Field       | Type             | Null | Key | Default | Extra          |
+————-+——————+——+—–+———+—————-+
| employee_id | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| last_name   | varchar(20)      | YES  |     | NULL    |                |
| first_name  | varchar(20)      | YES  |     | NULL    |                |
| birth       | date             | YES  |     | NULL    |                |
| sex         | enum(‘m’,’f’)    | YES  |     | m       |                |
| emmail      | varchar(39)      | YES  |     | NULL    |                |
+————-+——————+——+—–+———+—————-+

如果只是这样的话,就会出现这样的错误:报告的错误是:sqle=com.mysql.jdbc.MysqlDataTruncation:
Data truncation: Data too long
for column ‘last_name’ at row 1 从 employee!
有的情况是:| name        |
+————-+
| ??          |
| 54243654321 |
| ??          |
| ??          |
+————-+出现问号!

前面我们已经说过了,mysql默认的编码是latin1,不是我们所需要的gbk,所以我们要修改成为utf8,因为若要正确显示中文繁、简、日文、韩文 使用utf8,修改方法如下:
  ALTER DATABASE sample ####这里修改整个数据库的编码
       CHARACTER SET utf8
       DEFAULT CHARACTER SET utf8
       COLLATE utf8_general_ci
       DEFAULT COLLATE utf8_general_ci;

当然了,你也可在在建数据库的时候指定编码,比如:

CREATE DATABASE sample

       CHARACTER SET utf8
       DEFAULT CHARACTER SET utf8
       COLLATE utf8_general_ci
       DEFAULT COLLATE utf8_general_ci ;

接下来要做的是打开mysql所在的目录下的my.nin

在[mysqld]段加入一下代码改成:

default-character-set=utf8

 启动mysql,输入:

执行下列语句,看看结果是不是下面的:
mysql> show variables like ‘%character%’;
+————————–+——————————————-+
| Variable_name            | Value                                     |
+————————–+——————————————-+
| character_set_client     | latin1                                    |
| character_set_connection | latin1                                    |
| character_set_database   | utf8                                      |
| character_set_results    | latin1                                    |
| character_set_server     | utf8                                                                         
| character_set_system     | utf8                                      |
| character_sets_dir       | C:\MySQL\MySQL Server 5.0\share\charsets\ |
+————————–+——————————————-+

mysql> show variables like ‘%collation%’;
+———————-+——————-+
| Variable_name        | Value             |
+———————-+——————-+
| collation_connection | latin1_swedish_ci |
| collation_database   | utf8_general_ci   |
| collation_server     | utf8_general_ci   |
+———————-+——————-+

 

接着你再看看执行那个Mysql.html 文件:这回你可以看到的是
mysql> select * from employee;
+————-+———–+————+————+——+————–+
| employee_id | last_name | first_name | birth      | sex  | emmail       |
+————-+———–+————+————+——+————–+
|          12 | ?         | ??         | 1978-12-11 | f    | aaa@asdf.com |
|          13 | ?         | ??         | 1978-12-11 | f    | aaa@asdf.com |
|          14 | ?         | ??         | 1978-12-11 | f    | aaa@asdf.com |
|          15 | ?         | ??         | 1978-12-11 | f    | aaa@asdf.com |
|          16 | ?         | ??         | 1978-12-11 | f    | aaa@asdf.com |
|          17 | ?         | ??         | 1978-12-11 | f    | aaa@asdf.com |
+————-+———–+————+————+——+————–+

这当然不是我们希望看到的,我们需要的现实完美正确的中文:
我们还有最后一招:
mysql> SET NAMES ‘gbk’ ;
Query OK, 0 rows affected (0.00 sec)
因为我们需要的是gbk.
看看mysql中的character设置情况:
mysql> SHOW VARIABLES LIKE ‘%character%’ ;
+————————–+——————————————-+
| Variable_name            | Value                                     |
+————————–+——————————————-+
| character_set_client     | gbk                                       |
| character_set_connection | gbk                                       |
| character_set_database   | utf8                                      |
| character_set_results    | gbk                                       |
| character_set_server     | utf8                                      |
| character_set_system     | utf8                                      |
| character_sets_dir       | C:\MySQL\MySQL Server 5.0\share\charsets\ |
+————————–+——————————————-+
7 rows in set (0.00 sec)
mysql> SHOW VARIABLES LIKE ‘%collation%’ ;
+———————-+—————–+
| Variable_name        | Value           |
+———————-+—————–+
| collation_connection | gbk_chinese_ci  |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+———————-+—————–+

这个才是我们最终需要的。
在来查看执行完Mysql_jstl.jsp后的数据库中的结果:
mysql> select * from employee;
|          14 | 王        | 彭给       | 1978-12-11 | f    | aaa@asdf.com |
|          15 | 田        | 王光       | 1978-12-11 | f    | aaa@asdf.com |
|          16 | 息        | 存入       | 1978-12-11 | f    | aaa@asdf.com |
|          17 | 往        | 小杯       | 1978-12-11 | f    | aaa@asdf.com |
+————-+———–+————+————+——+————–+

可以高兴得看到了中文,并且在浏览器中也显示正确。

但是仅仅这样的话,当你重新启动mysql的时候
所有的设置又失效了。
mysql> show variables like ‘%character%’;
+————————–+——————————————-+
| Variable_name            | Value                                     |
+————————–+——————————————-+
| character_set_client     | latin1                                    |
| character_set_connection | latin1                                    |
| character_set_database   | utf8                                      |
| character_set_results    | latin1                                    |
| character_set_server     | utf8                                      |
| character_set_system     | utf8                                      |
| character_sets_dir       | C:\MySQL\MySQL Server 5.0\share\charsets\ |
+————————–+——————————————-+

mysql> SHOW VARIABLES LIKE ‘%collation%’ ;
+———————-+——————-+
| Variable_name        | Value             |
+———————-+——————-+
| collation_connection | latin1_swedish_ci |
| collation_database   | utf8_general_ci   |
| collation_server     | utf8_general_ci   |
+———————-+——————-+
重新读取又出现乱码:
mysql> select * from employee;
+————-+———–+————+————+–
| employee_id | last_name | first_name | birth      | s
+————-+———–+————+————+–
|          12 | ?         | ??         | 1978-12-11 | f
|          13 | ?         | ??         | 1978-12-11 | f
|          14 | ?         | ??         | 1978-12-11 | f
|          15 | ?         | ??         | 1978-12-11 | f
|          16 | ?         | ??         | 1978-12-11 | f
|          17 | ?         | ??         | 1978-12-11 | f
+————-+———–+————+————+–
所以我们需要在客户端设置系统能识别中文的编码gbk并没有保存到my.ini文件中。所以要修改my.ini文件

在[mysql]段加入一下代码改成:default-character-set=gbk 这样设置就得到保存了。
重启就可以了。
mysql> show variables like ‘%character%’;
+————————–+—————-
| Variable_name            | Value
+————————–+—————-
| character_set_client     | gbk
| character_set_connection | gbk
| character_set_database   | utf8
| character_set_results    | gbk
| character_set_server     | utf8
| character_set_system     | utf8
| character_sets_dir       | C:\MySQL\MySQL
+————————–+—————-
mysql> SHOW VARIABLES LIKE ‘%collation%’ ;
+———————-+—————–+
| Variable_name        | Value           |
+———————-+—————–+
| collation_connection | gbk_chinese_ci  |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+———————-+—————–+

您可能还喜欢...