被 mysql 4.1.x 的字符集问题搞的快疯了,请求帮助!
装了 MySQL 4.1.7 ,看到有选择字符集的,就选了 gbk,然后用 phpmyadmin 建了几个表,都使用 gbk的字符集,使用 gbk_bin 的 collation(phpmyadmin里这个翻译为 整理)。
在 MySQL 命令行里执行 select * from user_acc where name='aa',出现如下错误
ERROR 1267 (HY000): Illegal mix of collations (gbk_bin,IMPLICIT) and (latin1_swe
dish_ci,COERCIBLE) for operation '='
去查了资料,发现是字符集的问题。按照找到的资料,将 my.ini 里的参数做如下变动
[client]
default-character-set=gbk
default-collation=gbk_bin
[mysqld]
default-character-set=gbk
default-collation=gbk_bin
重启 MySQL 服务,再进入 MySQL 命令行,还是同样的错误
查看 当前环境变量,发现 关键的 还是没变
mysql> show variables like "%char%";
+--------------------------+-----------------------------------------------+
| Variable_name | Value |
+--------------------------+-----------------------------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | gbk |
| character_set_results | latin1 |
| character_set_server | gbk |
| character_set_system | utf8 |
| character_sets_dir | D:\WWW\MySQL\MySQL Server 4.1\share\charsets/ |
+--------------------------+-----------------------------------------------+
7 rows in set (0.00 sec)
mysql> show variables like "%colla%";
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci | <= 这个关键
| collation_database | gbk_bin |
| collation_server | gbk_bin |
+----------------------+-------------------+
3 rows in set (0.00 sec)
在命令行中执行 set collation_connection=gbk_bin;
+----------------------+---------+
| Variable_name | Value |
+----------------------+---------+
| collation_connection | gbk_bin |
| collation_database | gbk_bin |
| collation_server | gbk_bin |
+----------------------+---------+
3 rows in set (0.00 sec)
然后执行 select * from user_acc where name='aa',没有错误
在 php 的连接数据库的 conn.php 做如下改动
/********************************************************/
<?
$conn = new mysqli("localhost", "dbAcc", "dbPass", "dbName");
if(mysqli_connect_errno())
{
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
// 添加以防止字符集问题
$conn->query("SET character_set_client = gbk");
$conn->query("SET character_set_connection = gbk");
$conn->query("SET character_set_database = gbk");
$conn->query("SET character_set_results = gbk");
$conn->query("SET character_set_server = gbk");
$conn->query("SET collation_connection = gbk_bin");
$conn->query("SET collation_database = gbk_bin");
$conn->query("SET collation_server = gbk_bin");
?>
/********************************************************/
使用另一个 test.php
/********************************************************/
<?php include_once('conn.php')?>
<?
$sSQL = "show variables like '%char%'";
if($rs = $conn->query($sSQL))
{
while($row = $rs->fetch_row())
{
printf ("%s = %s<br>", $row[0], $row[1]);
}
$rs->close();
}
$sSQL = "show variables like '%coll%'";
if($rs = $conn->query($sSQL))
{
while($row = $rs->fetch_row())
{
printf ("%s = %s<br>", $row[0], $row[1]);
}
$rs->close();
}
$sSQL = "select * from user_acc where name='aa'";
if($rs = $conn->query($sSQL))
{
printf("Query OK!");
$rs->close();
}
?>
<?php include_once('close.php')?>
/********************************************************/
页面显示
character_set_client = gbk
character_set_connection = gbk
character_set_database = gbk
character_set_results = gbk
character_set_server = gbk
character_set_system = utf8
character_sets_dir = D:\WWW\MySQL\MySQL Server 4.1\share\charsets/
collation_connection = gbk_bin
collation_database = gbk_bin
collation_server = gbk_bin
Query OK!
本来以为这样就搞定了,接下来的事把我搞疯了
在 MySQL 的命令行里 执行
mysql>insert user_acc (name,password) values('中文','test');
mysql>select * from user_acc;
+----+------+----------+---------------------+---------+-------+--------+
| id | name | password | lastlogin | lastip | level | enable |
+----+------+----------+---------------------+---------+-------+--------+
| 1 | ???? | test | 2004-11-21 15:28:36 | 0.0.0.0 | 0 | 1 |
+----+------+----------+---------------------+---------+-------+--------+
在页面 test.php 中 select user_acc 显示出来也是 ???
然后在 test.php 中 update user_acc set name='中文' where id=1
重新 在页面 select user_acc
页面显示正确。
难道 MySQL 命令行不可以直接使用中文么?如果是这样的话,那以后需要导出导入大量数据时,怎么做?
我原来都是先导出到文件,然后命令行里执行 source xxx.sql 的。
顺便说下系统配置:
Windows 2000 Server
Apache 2.x PHP 5.0.2 MySQL 4.1.7