mysql查询语句,奇怪的问题.

[黑心]

数据库表内容table :
id idname idpath
1 bbb \5\aaa\bbb
2 aaa \5\aaa
3 abc \5\aaa\abc

语句:

select * from table where idpath='\\\5\\\aaa'
有1个结果.
select * from table where idpath='\\\5\\\aaa\\\abc'
有1个结果.
select * from table where idpath='\\\5\\\aaa\\\bbb'
有0个结果.问题在这儿,为什么会没有结果?

[ACMAIN_CHM]

1 bbb \5\aaa\bbb

后面有没有空格之类的？

[黑心]

嗯,,没有空格的.

[wwwwb]

什么字符集
select * from table where idpath='\\\5\\\aaa\\bbb'

[黑心]

| character_set_client | latin1
|
| character_set_connection | latin1
|
| character_set_database | gb2312
|
| character_set_filesystem | binary
|
| character_set_results | latin1
|
| character_set_server | latin1
|
| character_set_system | utf8

[ACMAIN_CHM]

提供你的测试数据。


参考一下这个贴子http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html

1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句

这样想帮你的人可以直接搭建和你相同的环境，并在给出方案前进行测试，避免文字描述理解上的误差。

[黑心]

CREATE TABLE `table` (
`Id` int(11) NOT NULL auto_increment,
`idName` varchar(250) default NULL,
`idPath` varchar(250) default NULL,
`idUserId` int(11) default NULL,
`idAddTime` datetime default NULL,
PRIMARY KEY (`Id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


用这个Navicat for MySQL直接修改和添加的数据.

[ACMAIN_CHM]

就怕加的数据不一样。
你可以用导出功能生成INSERT语句。

[九月茅桃]

mysql> create table tb
-> select 1 as id, 'bbb' as idname, '\5\aaa\bbb' as idpath union all
-> select 2, 'aaa', '\5\aaa' union all
-> select 3, 'abc', '\5\aaa\abc';
ERROR 1046 (3D000): No database selected
mysql> use test;
Database changed
mysql> create table tb
-> select 1 as id, 'bbb' as idname, '\5\aaa\bbb' as idpath union all
-> select 2, 'aaa', '\5\aaa' union all
-> select 3, 'abc', '\5\aaa\abc';
Query OK, 3 rows affected (0.11 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from table where idpath='\\\5\\\aaa'
-> ;
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 'table
where idpath='\\\5\\\aaa'' at line 1
mysql> select * from tb where idpath='\\\5\\\aaa';
Empty set (0.02 sec)

mysql> select * from tb where idpath='\\\5\\\aaa\\\abc';
Empty set (0.00 sec)

mysql> select * from tb where idpath='\\\5\\\aaa\\\bbb';
Empty set (0.00 sec)

mysql>


为什么我的都是null

[黑心]

INSERT INTO table
(`Id`, `idName`, `idPath`, `idUserId`, `idAddTime`)
VALUES
(3, 'bbb', '\\5\\aaa\\z', 5, "2011-10-14 17:49:15");

INSERT INTO table
(`Id`, `idName`, `idPath`, `idUserId`, `idAddTime`)
VALUES
(4, 'aaa', '\\5\\aaa', 5, "2011-10-14 18:08:39");

INSERT INTO table
(`Id`, `idName`, `idPath`, `idUserId`, `idAddTime`)
VALUES
(5, 'd', '\\5\\aaa\\b', 1, "2011-10-19 14:40:21");


导出怎么有两个"\\",晕.

[ACMAIN_CHM]

[Quote]为什么我的都是null[/Quote]注意对\的转义。


[Quote]导出怎么有两个"\\",晕.[/Quote]MYSQL命令中 \\ 代表 \

[ACMAIN_CHM]

mysql> select * from `table`;
----+--------+----------+----------+---------------------+
Id | idName | idPath | idUserId | idAddTime |
----+--------+----------+----------+---------------------+
3 | bbb | \5\aaa\z | 5 | 2011-10-14 17:49:15 |
4 | aaa | \5\aaa | 5 | 2011-10-14 18:08:39 |
5 | d | \5\aaa\b | 1 | 2011-10-19 14:40:21 |
----+--------+----------+----------+---------------------+
rows in set (0.00 sec)

mysql> select * from `table` where idPath='\\5\\aaa\\z';
+----+--------+----------+----------+---------------------+
| Id | idName | idPath | idUserId | idAddTime |
+----+--------+----------+----------+---------------------+
| 3 | bbb | \5\aaa\z | 5 | 2011-10-14 17:49:15 |
+----+--------+----------+----------+---------------------+
1 row in set (0.00 sec)

ysql>
mysql> select * from table where idpath='\\\5\\\aaa';
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 'table
where idpath='\\\5\\\aaa'' at line 1
mysql>

[黑心]

嗯,,三根杠不行"\\\",有些字符读不出来.

"\\"只能用两根扛.

[黑心]

顺便问一下,三根杠"\\\"和两根扛"\\"的区别是什么?

[黑心]

谢谢各位
谢谢ACMAIN_CHM


结贴.