抱歉,我掉了节假日,修正下我想知道满足查询条件:周一|周三|平时白天|节假日|寒假, 所有值的结果数据库:id name1 周二|周三|平时白天2 周一|周二|周三|平时白天|平时夜晚|节假日|暑假|寒假 select id from table where name?????结果:2应该是这样的,不知道这么写,用程序也可以的~
看来还得我出马。 不要让我来解释为什么这么写,请参考正则式的说明mysql> select * from t_mlsml; +------+---------------------------------------------------+ | id | ugzsj | +------+---------------------------------------------------+ | 1 | 周二|周三|平时白天 | | 2 | 周一|周二|周三|平时白天|平时夜晚|节假日|暑假|寒假 | +------+---------------------------------------------------+ 2 rows in set (0.00 sec)mysql> select * from t_mlsml where '周一|周三|平时白天|节假日|寒假' not regexp concat('[^',ugzsj,']'); +------+---------------------------------------------------+ | id | ugzsj | +------+---------------------------------------------------+ | 2 | 周一|周二|周三|平时白天|平时夜晚|节假日|暑假|寒假 | +------+---------------------------------------------------+ 1 row in set (0.00 sec)mysql>mysql> select * from t_mlsml; +------+----------------------------------------------------+ | id | ugzsj | +------+----------------------------------------------------+ | 1 | 周二|周四|周五|周日|平时夜晚|节假日|暑假|任意时间| | | 2 | 周一|周二|周五|周日|平时夜晚|节假日|暑假| | | 3 | 周三|周五|周日|平时夜晚|寒假| | +------+----------------------------------------------------+ 3 rows in set (0.00 sec)mysql> select * from t_mlsml where '周二|周五|平时夜晚|暑假' not regexp concat('[^',ugzsj,']'); +------+----------------------------------------------------+ | id | ugzsj | +------+----------------------------------------------------+ | 1 | 周二|周四|周五|周日|平时夜晚|节假日|暑假|任意时间| | | 2 | 周一|周二|周五|周日|平时夜晚|节假日|暑假| | +------+----------------------------------------------------+ 2 rows in set (0.00 sec)mysql>
not regexp concat('[^',ugzsj,']');果然是高手,一般人连正则都不清楚,现在还反向,强人,学习了~
TO:ACMAIN_CHM 百密一疏,这个正则写的还不够完善,还是出现了漏洞,下面这个按条件 '周五|暑假' id为2的不该出现~~mysql> select * from t; +------+--------------------------------+ | id | ugzsj | +------+--------------------------------+ | 1 | 周二|周四|周五|周日|平时夜晚|?| | 2 | 周二|周五|周日|平时夜晚|节假日 | | 3 | 周五|周日|假日|暑假 | | 4 | 周五|周三|夜晚|暑假 | +------+--------------------------------+ 4 rows in set (0.00 sec)mysql> select * from t where '周五|暑假' not regexp concat('[^',ugzsj,']'); +------+--------------------------------+ | id | ugzsj | +------+--------------------------------+ | 2 | 周二|周五|周日|平时夜晚|节假日 | | 3 | 周五|周日|假日|暑假 | | 4 | 周五|周三|夜晚|暑假 | +------+--------------------------------+ 3 rows in set (0.00 sec)
你用的什么字符集,检查一下你的字符集设置。mysql> select * from t_mlsml; +------+--------------------------------+ | id | ugzsj | +------+--------------------------------+ | 1 | 周二|周四|周五|周日|平时夜晚|? | | 2 | 周二|周五|周日|平时夜晚|节假日 | | 3 | 周五|周日|假日|暑假 | | 4 | 周五|周三|夜晚|暑假 | +------+--------------------------------+ 4 rows in set (0.00 sec)mysql> select * from t_mlsml where'周五|暑假' not regexp concat('[^',ugzsj,']');+------+---------------------+ | id | ugzsj | +------+---------------------+ | 3 | 周五|周日|假日|暑假 | | 4 | 周五|周三|夜晚|暑假 | +------+---------------------+ 2 rows in set (0.00 sec)mysql>
+------+---------------------------------------------------+
| id | ugzsj |
+------+---------------------------------------------------+
| 1 | 周二|周三|平时白天 |
| 2 | 周一|周二|周三|平时白天|平时夜晚|节假日|暑假|寒假 |
+------+---------------------------------------------------+
2 rows in set (0.00 sec)mysql> select * from t_mlsml where '周一|周三|平时白天|节假日|寒假' not regexp concat('[^',ugzsj,']');
+------+---------------------------------------------------+
| id | ugzsj |
+------+---------------------------------------------------+
| 2 | 周一|周二|周三|平时白天|平时夜晚|节假日|暑假|寒假 |
+------+---------------------------------------------------+
1 row in set (0.00 sec)mysql>mysql> select * from t_mlsml;
+------+----------------------------------------------------+
| id | ugzsj |
+------+----------------------------------------------------+
| 1 | 周二|周四|周五|周日|平时夜晚|节假日|暑假|任意时间| |
| 2 | 周一|周二|周五|周日|平时夜晚|节假日|暑假| |
| 3 | 周三|周五|周日|平时夜晚|寒假| |
+------+----------------------------------------------------+
3 rows in set (0.00 sec)mysql> select * from t_mlsml where '周二|周五|平时夜晚|暑假' not regexp concat('[^',ugzsj,']');
+------+----------------------------------------------------+
| id | ugzsj |
+------+----------------------------------------------------+
| 1 | 周二|周四|周五|周日|平时夜晚|节假日|暑假|任意时间| |
| 2 | 周一|周二|周五|周日|平时夜晚|节假日|暑假| |
+------+----------------------------------------------------+
2 rows in set (0.00 sec)mysql>
百密一疏,这个正则写的还不够完善,还是出现了漏洞,下面这个按条件 '周五|暑假' id为2的不该出现~~mysql> select * from t;
+------+--------------------------------+
| id | ugzsj |
+------+--------------------------------+
| 1 | 周二|周四|周五|周日|平时夜晚|?|
| 2 | 周二|周五|周日|平时夜晚|节假日 |
| 3 | 周五|周日|假日|暑假 |
| 4 | 周五|周三|夜晚|暑假 |
+------+--------------------------------+
4 rows in set (0.00 sec)mysql> select * from t where '周五|暑假' not regexp concat('[^',ugzsj,']');
+------+--------------------------------+
| id | ugzsj |
+------+--------------------------------+
| 2 | 周二|周五|周日|平时夜晚|节假日 |
| 3 | 周五|周日|假日|暑假 |
| 4 | 周五|周三|夜晚|暑假 |
+------+--------------------------------+
3 rows in set (0.00 sec)
+------+--------------------------------+
| id | ugzsj |
+------+--------------------------------+
| 1 | 周二|周四|周五|周日|平时夜晚|? |
| 2 | 周二|周五|周日|平时夜晚|节假日 |
| 3 | 周五|周日|假日|暑假 |
| 4 | 周五|周三|夜晚|暑假 |
+------+--------------------------------+
4 rows in set (0.00 sec)mysql> select * from t_mlsml where'周五|暑假' not regexp concat('[^',ugzsj,']');+------+---------------------+
| id | ugzsj |
+------+---------------------+
| 3 | 周五|周日|假日|暑假 |
| 4 | 周五|周三|夜晚|暑假 |
+------+---------------------+
2 rows in set (0.00 sec)mysql>
实践已经说明了一切。如果我是租的数据库空间那该如何解决这问题了~
设置你的字符集就是了。
关于字符集设置可以参考下贴中字符集设置部分。
http://blog.csdn.net/ACMAIN_CHM/archive/2009/05/12/4174186.aspx
MySQL 中文显示乱码