mysql> create table a (name varchar(32));
Query OK, 0 rows affected (0.50 sec)mysql> insert into a values ('aaaaaa');
Query OK, 1 row affected (0.03 sec)mysql> insert into a values ('妹妹美');
Query OK, 1 row affected (0.06 sec)mysql> select count(distinct name) from a;
+----------------------+
| count(distinct name) |
+----------------------+
| 1 |
+----------------------+
1 row in set (0.05 sec)mysql>高手看看,只是为什么?为什么'aaaaaa'等于'妹妹美'?我竟以为count出来应该是2.
Query OK, 0 rows affected (0.50 sec)mysql> insert into a values ('aaaaaa');
Query OK, 1 row affected (0.03 sec)mysql> insert into a values ('妹妹美');
Query OK, 1 row affected (0.06 sec)mysql> select count(distinct name) from a;
+----------------------+
| count(distinct name) |
+----------------------+
| 1 |
+----------------------+
1 row in set (0.05 sec)mysql>高手看看,只是为什么?为什么'aaaaaa'等于'妹妹美'?我竟以为count出来应该是2.
MySQL 中文显示乱码
mysql> show full columns from user;
+------------------+------------------+-----------------+------+-----+----------
-----------+----------------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default
| Extra | Privileges | Comment |
+------------------+------------------+-----------------+------+-----+----------
-----------+----------------+---------------------------------+---------+
| ID | int(11) | NULL | NO | PRI | NULL
| auto_increment | select,insert,update,references | |
| UniqueName | varchar(32) | utf8_general_ci | NO | UNI |
| | select,insert,update,references | |
| Name | varchar(32) | utf8_general_ci | NO | |
| | select,insert,update,references | |
| ActorID | int(11) | NULL | NO | | 0
| | select,insert,update,references | |
| AccountID | int(11) | NULL | NO | | 0
| | select,insert,update,references | |
| Account | varchar(20) | utf8_general_ci | NO | MUL |
| | select,insert,update,references | |
| Scene | varchar(32) | utf8_general_ci | NO | |
| | select,insert,update,references | |
| HP | int(11) | NULL | NO | | 0
| | select,insert,update,references | |
| MP | int(11) | NULL | NO | | 0
| | select,insert,update,references | |
| EXP | int(11) | NULL | NO | | 0
| | select,insert,update,references | |
| AP | int(11) | NULL | NO | | 0
| | select,insert,update,references | |
| Level | int(11) | NULL | NO | | 0
| | select,insert,update,references | |
| DeleteDate | datetime | NULL | YES | | NULL
| | select,insert,update,references | |
| CreateDate | datetime | NULL | NO | | 0000-00-0
0 00:00:00 | | select,insert,update,references | |
| LastUpdateDate | datetime | NULL | NO | | 0000-00-0
0 00:00:00 | | select,insert,update,references | |
| Metier | int(11) | NULL | NO | | 0
| | select,insert,update,references | |
| MetierLevel | int(11) | NULL | NO | | 0
| | select,insert,update,references | |
| X | int(11) | NULL | NO | | 0
| | select,insert,update,references | |
| Y | int(11) | NULL | NO | | 0
| | select,insert,update,references | |
| Gold | int(11) | NULL | NO | | 0
| | select,insert,update,references | |
| SP | int(11) | NULL | NO | | 0
| | select,insert,update,references | |
| Str | smallint(6) | NULL | NO | | 0
| | select,insert,update,references | |
| Con | smallint(6) | NULL | NO | | 0
| | select,insert,update,references | |
| Dex | smallint(6) | NULL | NO | | 0
| | select,insert,update,references | |
| Int | smallint(6) | NULL | NO | | 0
表比较大,没有贴完。但是Collation部分看起来都是NULL,会不会是这里的问题?
另外贴出你的show variable like ....
-> ;
+--------------------------+----------------------------------------------------
-----+
| Variable_name | Value
|
+--------------------------+----------------------------------------------------
-----+
| character_set_client | latin1
|
| character_set_connection | latin1
|
| character_set_database | latin1
|
| character_set_filesystem | binary
|
| character_set_results | latin1
|
| character_set_server | latin1
|
| character_set_system | utf8
|
| character_sets_dir | C:\Program Files\MySQL\MySQL Server 5.1\share\chars
ets\ |
+--------------------------+----------------------------------------------------
-----+
8 rows in set (0.00 sec)
mysql> create table a (name varchar(32));
ERROR 1046 (3D000): No database selected
mysql> use fso;
Database changed
mysql> create table a (name varchar(32));
Query OK, 0 rows affected (0.22 sec)mysql> insert into a values ('aaaaaa');
Query OK, 1 row affected (0.06 sec)mysql> insert into a values ('妹妹美');
Query OK, 1 row affected (0.03 sec)mysql> select * from a;
+--------+
| name |
+--------+
| aaaaaa |
| 妹妹美 |
+--------+
2 rows in set (0.03 sec)mysql> select count(distinct name) from a;
+----------------------+
| count(distinct name) |
+----------------------+
| 1 |
+----------------------+
1 row in set (0.08 sec)mysql> show full columns for a;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'for a
' at line 1
mysql> show full columns from a;
+-------+-------------+-------------------+------+-----+---------+-------+------
---------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privi
leges | Comment |
+-------+-------------+-------------------+------+-----+---------+-------+------
---------------------------+---------+
| name | varchar(32) | latin1_swedish_ci | YES | | NULL | | selec
t,insert,update,references | |
+-------+-------------+-------------------+------+-----+---------+-------+------
---------------------------+---------+
1 row in set (0.05 sec)mysql>这就是结果。
我的问题在于,我需要从别人的数据库中操作数据。恰好别人的数据库中有两条记录的某一字段分别为'aaaaaa'和'妹妹美',结果判断下来两个字段值一样。我需要了解该如何区分这两列,谢谢
1. select count(distinct(hex(name))) from a;
2. 不用latin1默认字符集,改用gbk
修改my.ini, 更新如下项:
[client]
default-character-set=GBK
[msyqld]
default-character-set=utf8
并重新启动mysql数据库,进入连接以后,set names 'gbk',
再导入你要的数据,重新测试
+--------------------------+---------------------
| Variable_name | Value
+--------------------------+---------------------
| character_set_client | latin1
| character_set_connection | latin1
| character_set_database | latin1
| character_set_filesystem | binary
| character_set_results | latin1
| character_set_server | latin1
| character_set_system | utf8
| character_sets_dir | C:\Users\liuyann\myD
+--------------------------+---------------------
8 rows in set (0.00 sec)mysql> create table a (name varchar(32));
Query OK, 0 rows affected (0.08 sec)mysql> insert into a values ('aaaaaa');
Query OK, 1 row affected (0.03 sec)mysql> insert into a values ('妹妹美');
Query OK, 1 row affected (0.00 sec)mysql> select * from a;
+--------+
| name |
+--------+
| aaaaaa |
| 妹妹美 |
+--------+
2 rows in set (0.01 sec)mysql> select count(distinct name) from a;
+----------------------+
| count(distinct name) |
+----------------------+
| 1 |
+----------------------+
1 row in set (0.06 sec)mysql> select 'aaaaaa'='妹妹美';
+-------------------+
| 'aaaaaa'='妹妹美' |
+-------------------+
| 1 |
+-------------------+
1 row in set (0.05 sec)mysql> select count(distinct binary name) from a;
+-----------------------------+
| count(distinct binary name) |
+-----------------------------+
| 2 |
+-----------------------------+
1 row in set (0.22 sec)
1、在统一字符集的下重新插入记录;
2、用HEX转换一下,再做处理。
我最不解的是,如果是字符集不统一,应该是乱码呀,无论如何也不会出现
‘aaaaa’='妹妹美'的这种问题呀,毕竟在底层是不同的字节呀。谁能就事论事,说说根本原因
首先要回答一个问题 "aa" 与"AA' 和 "aA" 是不是相等? 肯定会有人说相同,也会有人说不同。那么当你查询 where col ='aa' 的时候,其它两种希望不希望查出?这就牵涉一个字符串的检对集的问题。 关于字符集,校对集建议先参考一下手册中的内容或者下贴中的介绍
http://blog.csdn.net/ACMAIN_CHM/archive/2009/05/12/4174186.aspx
MySQL 中文显示乱码
谢谢
假如用latin1_swedish_ci规则进行比较 ÃÃÃÃÃÀ 和aaaaaa是相等的
总的来说,程序的目的还是要区分出'aaaaaa'和'妹妹美'。
我的程序是C#写的,基于mysql.data。不知道我对不对,我认为如果程序中能够正确读到中文字符串,那么问题应该就解决了。
我修改了mysql的配置,得到的mysql的字符集配置如下
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 | C:\Program Files\MySQL\MySQL Server 5.1\share\chars
ets\ |
+--------------------------+----------------------------------------------------
-----+
8 rows in set (0.02 sec)再看以下的SQL操作:
mysql> use fso
Database changed
mysql> select UniqueName from user where id=200;
+----------------------+
| UniqueName |
+----------------------+
| oo脢芦脟茅禄颅脪芒oo |
+----------------------+
1 row in set (0.00 sec)mysql> set names latin1;
Query OK, 0 rows affected (0.00 sec)mysql> select UniqueName from user where id=200;
+--------------+
| UniqueName |
+--------------+
| oo诗情画意oo |
+--------------+
1 row in set (0.00 sec)mysql>当我set names latin1后,select出来的UniqueName列的中文能正常显示。我在C#程序中,也同样的首先set names latin1,然后再尝试select * from user。得到的却依然是乱码。
我也尝试了set names utf8; 和 set names gbk。得到的都是同样的乱码。我觉得程序中我如果能正确得到中文字符串,那么问题也应该解决了。这里现在是个瓶颈,程序里得到的始终是乱码。我思考了下,如果我把hex(UniqueName)也select出来,应该理论上可以解决问题。但是我还是想把这个乱码的问题搞清了。望高手解答。
MySQL 中文显示乱码
我看了这个帖子。
mysql> show full columns from user;
+------------------+------------------+-----------------+------+-----+----------
-----------+----------------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default
| Extra | Privileges | Comment |
+------------------+------------------+-----------------+------+-----+----------
-----------+----------------+---------------------------------+---------+
| ID | int(11) | NULL | NO | PRI | NULL
| auto_increment | select,insert,update,references | |
| UniqueName | varchar(32) | utf8_general_ci | NO | UNI |
| | select,insert,update,references | |
......这里可以看到UniqueName列的Collation是utf8_general_ci。应该是中文兼容的。接下来看字符集设置:
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 | C:\Program Files\MySQL\MySQL Server 5.1\share\chars
ets\ |
+--------------------------+----------------------------------------------------
-----+
8 rows in set (0.00 sec)mysql>我都设置成了utf8。在C#的程序中,连接字符串我没有添加charset=utf8。
不过我在C#代码片段中
cmd.CommandText = "set names utf8;";
cmd.ExecuteNonQuery();
cmd.CommandText = "select * from user;";
reader = cmd.ExecuteReader();我是设置了set names utf8。
可是最后得到的select *中UniqueName通过reader.GetString(<column index>)得到的还是乱码。感觉就是编码的问题,这一层窗户纸还没捅破。望高手继续赐教了
下面是我在对方数据库上查询的配置:
mysql> show variables like "char%";
+--------------------------+--------------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | D:\web\MySQL-5.0.51a\share\charsets\ |
+--------------------------+--------------------------------------+
8 rows in set (0.00 sec)mysql>下面是user表的具体信息
mysql> show full columns from user;
+------------------+------------------+-----------------+------+-----+----------
-----------+----------------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default
| Extra | Privileges | Comment |
+------------------+------------------+-----------------+------+-----+----------
-----------+----------------+---------------------------------+---------+
| ID | int(11) | NULL | NO | PRI | NULL
| auto_increment | select,insert,update,references | |
| UniqueName | varchar(32) | utf8_general_ci | NO | UNI |
| | select,insert,update,references | |
......
我是通过mysqldump -uroot -p****** --database fso -f > fso.sql的方式导出数据到我的数据库中在这种情况下,大家看看该如何做才能解决编码问题呢?谢谢。
非常感谢大家。
D:\web\MySQL-5.0.51a\bin>mysqldump.exe --default-character-set=utf8 -uroot -p×××××× --database fso -f > fso_utf8.sql下面是导出来的部分内容
INSERT INTO `user` VALUES (1,'麓贸脧潞','麓贸脧潞',我该用latin1导出还是utf8?我之前似乎测试过数据库里的数据不是utf8的。
INSERT INTO `user` VALUES (1,'大虾','大虾',1,那么在这个情况下,我导入我的数据库时mysql也该指定成--default-character-set=latin1?这种情况下,我就得在我的程序里设置成set names latin1?是这么个流程吗?
导出的sql文件是ANSI的,你可用工具软件查看编码类型,也可用记事本打开并在“另存为”对话框中查看
你可以用工具或自编程序将sql文件的编码转换成你的数据库要求的编码
关键还要看你后台数据库的默认编码。
从前边来看,好像是utf8, 对吗?
那你导入时,可以先用文本编辑器将导出的文件从ansi转为utf8编码。
然后使用命令行:--default-character-set=utf8导入。这之后,在你的程序 里头,使用set names gbk,完成自动转换。
我是使用的C#做的windows form application。
使用的是mysql.data。
我直接在对方的数据库上操作。
下面是数据库中的具体情况:
Your MySQL connection id is 1
Server version: 5.0.51a-community-nt-log MySQL Community Edition (GPL)Type 'help;' or '\h' for help. Type '\c' to clear the buffer.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 | D:\web\MySQL-5.0.51a\share\charsets\ |
+--------------------------+--------------------------------------+
8 rows in set (0.00 sec)mysql> use fso;
Database changed
mysql> select UniqueName from user where id=200;
+----------------------+
| UniqueName |
+----------------------+
| oo脢芦脟茅禄颅脪芒oo |
+----------------------+
1 row in set (0.00 sec)mysql> set names latin1;
Query OK, 0 rows affected (0.00 sec)mysql> select UniqueName from user where id=200;
+--------------+
| UniqueName |
+--------------+
| oo诗情画意oo |
+--------------+
1 row in set (0.00 sec)我现在的问题是如何在程序中正确得到中文?
string str = "Database=fso;Data Source=localhost;User Id=root;Password=123456;Allow Zero Datetime=True;Convert Zero Datetime=true;";
MySqlConnection conn = new MySqlConnection(str);
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
string names = (Latin1RadioButton.Checked ? Latin1RadioButton.Text :
(GBKRadioButton.Checked ? GBKRadioButton.Text : UTF8RadioButton.Text));
cmd.CommandText = "set names " + names + ";";
cmd.ExecuteNonQuery();
cmd.CommandText = "select UniqueName from user;";
MySqlDataReader r = cmd.ExecuteReader();
while (r.Read())
{
string s = r.GetString(0);
listBox.Items.Add(s);
listBox.SelectedIndex = listBox.Items.Count - 1;
}
conn.Close();我尝试了用set names latin1; set names gbk; set names utf8;三种方式读取数据。r.getstring(0)得到的始终是乱码。我想这里能正确得到中文,后面的问题都应该解决了。大家有没有什么主意?