模糊查询 结尾查询语句

[nianzhang747]

SELECT * FROM pet WHERE name REGEXP '^.{5}$';
SELECT * FROM pet WHERE name REGEXP 'fy$';
$是代表以fy为结尾的name字段。
但是结果为空集，select * from pet;
+-----------+----------+-----------+------+------------+------------+
| name | owner | species | sex | birth | death |
+-----------+----------+-----------+------+------------+------------+
| Fluffy | Harold | cat | | 1993-02-04 | NULL |
| Claws | Gwen | cat | | 1994-03-17 | NULL |
| Buffy | Harold | dog | | 1989-05-13 | NULL |
| Fang | Benny | dog | | 1990-08-27 | NULL |
| Bowser | Diane | dog | | 1979-08-31 | 1995-07-29 |
| Chirpy | Gwen | BIRD | | 1998-09-11 | NULL |
| Whistler | Gwen | bird | | 1997-12-09 | NULL |
| Slim | Benny | snake | | 1996-04-29 | NULL |
| Puffball | Diane | hamster | | 1999-03-30 | NULL |
+-----------+----------+-----------+------+------------+------------+
9 rows in set (0.02 sec)

[wfnychuan]

mysql> select * from pet;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1990-08-31 | 1995-07-29 |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
| Puffball | Diane  | hamster | f    | 1999-03-30 | NULL       |
+----------+--------+---------+------+------------+------------+
9 rows in set

mysql> select * from pet where name regexp 'fy$';
+--------+--------+---------+-----+------------+-------+
| name   | owner  | species | sex | birth      | death |
+--------+--------+---------+-----+------------+-------+
| Fluffy | Harold | cat     | f   | 1993-02-04 | NULL  |
| Buffy  | Harold | dog     | f   | 1989-05-13 | NULL  |
+--------+--------+---------+-----+------------+-------+
2 rows in set

mysql> 

我在自己机子上可以

[nianzhang747]

你是怎么把我的数据导入你的数据库的？
还有你mysql版本是多少？我的为什么为空？

[nianzhang747]

mysql>select * from pet;
+-----------+----------+-----------+------+------------+------------+
| name | owner | species | sex | birth | death |
+-----------+----------+-----------+------+------------+------------+
| Fluffy | Harold | cat | | 1993-02-04 | NULL |
| Claws | Gwen | cat | | 1994-03-17 | NULL |
| Buffy | Harold | dog | | 1989-05-13 | NULL |
| Fang | Benny | dog | | 1990-08-27 | NULL |
| Bowser | Diane | dog | | 1979-08-31 | 1995-07-29 |
| Chirpy | Gwen | BIRD | | 1998-09-11 | NULL |
| Whistler | Gwen | bird | | 1997-12-09 | NULL |
| Slim | Benny | snake | | 1996-04-29 | NULL |
| Puffball | Diane | hamster | | 1999-03-30 | NULL |
+-----------+----------+-----------+------+------------+------------+
9 rows in set (0.00 sec)

mysql>select * from pet where name regexp 'fy$';
Empty set (0.02 sec)

[wfnychuan]

我的MYSQL 版本是5.1.32。
我的数据是以前一本PHP教程里的。不是从你这导入的。我发现你也有。就在我的MYSQL上试了一下。

不知道结果为什么会不一样。

[nianzhang747]

...
winsonshen来啊！
看看什么问题

[denniswwh]

我在5.0.51及5.1.26上试都有结果。。。。

[ACMAIN_CHM]

show create table pet;
show variables like 'char%';

看一下，是否是字符集引起的？

[nianzhang747]

谢谢