mysql> select id from test -> where content regexp '40'; +----+ | id | +----+ | 1 | | 3 | | 5 | +----+ 3 rows in set (0.00 sec)mysql> mysql> select id from test -> where content regexp '35'; +----+ | id | +----+ | 2 | | 4 | +----+ 2 rows in set (0.00 sec)mysql>
select id from test where content regexp '31'; 看看结果, 应该是没有符合条件的记录, 但是他匹配了‘货号’的315122-005,所以不是如此简单的
mysql> select id from test -> where content regexp '尺码.*<span style=\"color: #993300\">.*31.*</span>'; Empty set (0.02 sec)mysql> select id from test -> where content regexp '尺码.*<span style=\"color: #993300\">.*35.*</span>'; +----+ | id | +----+ | 2 | | 4 | +----+ 2 rows in set (0.00 sec)mysql>
<table border="0" cellpadding="0" cellspacing="0" width="100%">
<tbody>
<tr>
<td class="ch" align="right" height="32" valign="middle" width="18%">颜色</td>
<td class="ch" colspan="2" height="32" valign="middle"> <span style="color: rgb(153, 51, 0);">白</span></td>
</tr>
<tr>
<td class="ch" align="right" height="32" valign="middle">货号</td>
<td class="ch" height="32" valign="middle" width="32%"> <span style="color: rgb(153, 51, 0);">305895-112</span></td>
<td class="ch" height="32" valign="middle" width="25%"> </td>
</tr>
<tr>
<td class="ch" align="right" height="32" valign="middle">尺码</td>
<td colspan="2" style="color: rgb(204, 0, 0);" height="32" valign="middle"> <span style="color: rgb(153, 51, 0);">40 41 42 43 44 45 </span></td>
</tr>
</tbody>
</table>
相当于是显示在页面上的产品信息, 我现在页面上有个按尺码检索的按钮, 要求比如输入40自动匹配这条记录,没有40尺码的产品就不要, 现在我是想用MYSQL 正则过滤条件, 但是不知道怎么写才能取匹配这个尺码内容也就是td的内容。
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
CREATE TABLE `test` (
`id` bigint(20) NOT NULL,
`content` longtext CHARACTER SET utf8
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;INSERT INTO `test` VALUES ('1', '<table cellspacing=\"0\" cellpadding=\"0\" width=\"100%\" border=\"0\">\n <tbody>\n <tr>\n <td class=\"ch\" valign=\"middle\" align=\"right\" width=\"18%\" height=\"32\">颜色</td>\n <td class=\"ch\" valign=\"middle\" colspan=\"2\" height=\"32\"> <span style=\"color: #993300\">货号</span></td>\n </tr>\n <tr>\n <td class=\"ch\" valign=\"middle\" align=\"right\" height=\"32\">è´§å·</td>\n <td class=\"ch\" valign=\"middle\" width=\"32%\" height=\"32\"> <span style=\"color: #993300\"><span style=\"\"><span style=\"\"><span style=\"\">315122-005</span></span></span></span></td>\n <td class=\"ch\" valign=\"middle\" width=\"25%\" height=\"32\"> </td>\n </tr>\n <tr>\n <td class=\"ch\" valign=\"middle\" align=\"right\" height=\"32\">尺码</td>\n <td valign=\"middle\" colspan=\"2\" height=\"32\" style=\"color: #c00\"> <span style=\"color: #993300\">40 41 42 43 44 45 </span></td>\n </tr>\n </tbody>\n</table>');
INSERT INTO `test` VALUES ('2', '<table cellspacing=\"0\" cellpadding=\"0\" width=\"100%\" border=\"0\">\n <tbody>\n <tr>\n <td class=\"ch\" valign=\"middle\" align=\"right\" width=\"18%\" height=\"32\">颜色</td>\n <td class=\"ch\" valign=\"middle\" colspan=\"2\" height=\"32\"> <span style=\"color: #993300\">货号</span></td>\n </tr>\n <tr>\n <td class=\"ch\" valign=\"middle\" align=\"right\" height=\"32\">è´§å·</td>\n <td class=\"ch\" valign=\"middle\" width=\"32%\" height=\"32\"> <span style=\"color: #993300\"><span style=\"\"><span style=\"\"><span style=\"\">315122-005</span></span></span></span></td>\n <td class=\"ch\" valign=\"middle\" width=\"25%\" height=\"32\"> </td>\n </tr>\n <tr>\n <td class=\"ch\" valign=\"middle\" align=\"right\" height=\"32\">尺码</td>\n <td valign=\"middle\" colspan=\"2\" height=\"32\" style=\"color: #c00\"> <span style=\"color: #993300\">39 38 37 36 44 35 </span></td>\n </tr>\n </tbody>\n</table>');
INSERT INTO `test` VALUES ('3', '<table cellspacing=\"0\" cellpadding=\"0\" width=\"100%\" border=\"0\">\n <tbody>\n <tr>\n <td class=\"ch\" valign=\"middle\" align=\"right\" width=\"18%\" height=\"32\">颜色</td>\n <td class=\"ch\" valign=\"middle\" colspan=\"2\" height=\"32\"> <span style=\"color: #993300\">货号</span></td>\n </tr>\n <tr>\n <td class=\"ch\" valign=\"middle\" align=\"right\" height=\"32\">è´§å·</td>\n <td class=\"ch\" valign=\"middle\" width=\"32%\" height=\"32\"> <span style=\"color: #993300\"><span style=\"\"><span style=\"\"><span style=\"\">315122-005</span></span></span></span></td>\n <td class=\"ch\" valign=\"middle\" width=\"25%\" height=\"32\"> </td>\n </tr>\n <tr>\n <td class=\"ch\" valign=\"middle\" align=\"right\" height=\"32\">尺码</td>\n <td valign=\"middle\" colspan=\"2\" height=\"32\" style=\"color: #c00\"> <span style=\"color: #993300\">40 41 42 43 44 45 </span></td>\n </tr>\n </tbody>\n</table>');
INSERT INTO `test` VALUES ('4', '<table cellspacing=\"0\" cellpadding=\"0\" width=\"100%\" border=\"0\">\n <tbody>\n <tr>\n <td class=\"ch\" valign=\"middle\" align=\"right\" width=\"18%\" height=\"32\">颜色</td>\n <td class=\"ch\" valign=\"middle\" colspan=\"2\" height=\"32\"> <span style=\"color: #993300\">货号</span></td>\n </tr>\n <tr>\n <td class=\"ch\" valign=\"middle\" align=\"right\" height=\"32\">è´§å·</td>\n <td class=\"ch\" valign=\"middle\" width=\"32%\" height=\"32\"> <span style=\"color: #993300\"><span style=\"\"><span style=\"\"><span style=\"\">315122-005</span></span></span></span></td>\n <td class=\"ch\" valign=\"middle\" width=\"25%\" height=\"32\"> </td>\n </tr>\n <tr>\n <td class=\"ch\" valign=\"middle\" align=\"right\" height=\"32\">尺码</td>\n <td valign=\"middle\" colspan=\"2\" height=\"32\" style=\"color: #c00\"> <span style=\"color: #993300\">39 38 37 36 44 35 </span></td>\n </tr>\n </tbody>\n</table>');
INSERT INTO `test` VALUES ('5', '<table cellspacing=\"0\" cellpadding=\"0\" width=\"100%\" border=\"0\">\n <tbody>\n <tr>\n <td class=\"ch\" valign=\"middle\" align=\"right\" width=\"18%\" height=\"32\">颜色</td>\n <td class=\"ch\" valign=\"middle\" colspan=\"2\" height=\"32\"> <span style=\"color: #993300\">货号</span></td>\n </tr>\n <tr>\n <td class=\"ch\" valign=\"middle\" align=\"right\" height=\"32\">è´§å·</td>\n <td class=\"ch\" valign=\"middle\" width=\"32%\" height=\"32\"> <span style=\"color: #993300\"><span style=\"\"><span style=\"\"><span style=\"\">315122-005</span></span></span></span></td>\n <td class=\"ch\" valign=\"middle\" width=\"25%\" height=\"32\"> </td>\n </tr>\n <tr>\n <td class=\"ch\" valign=\"middle\" align=\"right\" height=\"32\">尺码</td>\n <td valign=\"middle\" colspan=\"2\" height=\"32\" style=\"color: #c00\"> <span style=\"color: #993300\">40 41 42 43 44 45 </span></td>\n </tr>\n </tbody>\n</table>');现在我要查找尺码标签的里的指定内容, 比如输入40尺码搜索, 那就是1,3,5的记录选出来, 如果是35尺码,那就是2,4的记录选出来。
各位大虾帮帮忙哈
-> where content regexp '40';
+----+
| id |
+----+
| 1 |
| 3 |
| 5 |
+----+
3 rows in set (0.00 sec)mysql>
mysql> select id from test
-> where content regexp '35';
+----+
| id |
+----+
| 2 |
| 4 |
+----+
2 rows in set (0.00 sec)mysql>
看看结果, 应该是没有符合条件的记录, 但是他匹配了‘货号’的315122-005,所以不是如此简单的
-> where content regexp '尺码.*<span style=\"color: #993300\">.*31.*</span>';
Empty set (0.02 sec)mysql> select id from test
-> where content regexp '尺码.*<span style=\"color: #993300\">.*35.*</span>';
+----+
| id |
+----+
| 2 |
| 4 |
+----+
2 rows in set (0.00 sec)mysql>