各位好,我想用mysql的存储过程或函数实现一个号码预留的功能,就像QQ那样把一些好的号码都留下来比如 100000,666666等,想到了几个预留原则:
1.连号,AAAAAAA,如2222222
2.ABCDEFG、GFEDCBA,如2345678、9876543
3.AAABCCC,如2224333、6667888
4.AAAABBB,如2222444、6666444
5.xAAAAAx,如3555556
请问应该怎么写呢?我想到了正则表达式,但是没写出来,请各位多多指教,谢谢。
1.连号,AAAAAAA,如2222222
2.ABCDEFG、GFEDCBA,如2345678、9876543
3.AAABCCC,如2224333、6667888
4.AAAABBB,如2222444、6666444
5.xAAAAAx,如3555556
请问应该怎么写呢?我想到了正则表达式,但是没写出来,请各位多多指教,谢谢。
insert into test values
('100000'),('666666'),('ABCDEFG'),('GFEDCBA'),('AAABCCC');mysql> select * from test where phone regexp '0{4,}';
+--------+
| phone |
+--------+
| 100000 |
+--------+
1 row in set (0.00 sec)
+-------------+
| n |
+-------------+
| 13862222222 |
| 13861234567 |
| 13544444449 |
| 13688888858 |
| 15820345678 |
| 15975553333 |
| 15975552222 |
| 15975626666 |
| 15820233333 |
| 15820228888 |
| 15920115555 |
| 15920332222 |
| 15920303333 |
| 15920306666 |
| 15920396666 |
| 15920383333 |
| 15920323333 |
| 15920363333 |
| 15920326666 |
| 15818818888 |
| 15818823333 |
| 15920993333 |
| 15920803333 |
| 15920802222 |
| 15920806666 |
| 15918888887 |
| 15920888880 |
| 15920565688 |
| 15920888889 |
| 15925566886 |
| 15922339995 |
+-------------+
31 rows in set (0.00 sec)mysql> -- 1.连号,AAAAAAA,如2222222
mysql> select * from t_yyhakusu
-> where n regexp '1111111|2222222|3333333|4444444|5555555|6666666|7777777|8888888|9999999|0000000';
+-------------+
| n |
+-------------+
| 13862222222 |
| 13544444449 |
+-------------+
2 rows in set (0.00 sec)mysql> -- 2.ABCDEFG、GFEDCBA,如2345678、9876543
mysql> select * from t_yyhakusu
-> where n regexp '1234567|2345678|3456789|4567890|7654321|8765432|9876543|0987654';
+-------------+
| n |
+-------------+
| 13861234567 |
+-------------+
1 row in set (0.00 sec)mysql> -- 3.AAABCCC,如2224333、6667888
mysql> select * from t_yyhakusu
-> where n regexp '(111|222|333|444|555|666|777|888|999|000)(111|222|333|444|555|666|777|888|999|000)';
+-------------+
| n |
+-------------+
| 13862222222 |
| 13544444449 |
| 13688888858 |
| 15975553333 |
| 15975552222 |
| 15918888887 |
+-------------+
6 rows in set (0.00 sec)mysql> -- 4.AAAABBB,如2222444、6666444
mysql> select * from t_yyhakusu
-> where n regexp '(1111|2222|3333|4444|5555|6666|7777|8888|9999|0000)(111|222|333|444|555|666|777|888|999|000)';
+-------------+
| n |
+-------------+
| 13862222222 |
| 13544444449 |
+-------------+
2 rows in set (0.00 sec)mysql>
mysql> -- 5.xAAAAAx,如3555556
mysql> select * from t_yyhakusu
-> where n regexp '11111|22222|33333|44444|55555|66666|77777|88888|99999|00000';
+-------------+
| n |
+-------------+
| 13862222222 |
| 13544444449 |
| 13688888858 |
| 15820233333 |
| 15918888887 |
| 15920888880 |
| 15920888889 |
+-------------+
7 rows in set (0.00 sec)mysql>
就目前所知,没有。PHP 中也一样,毕竟你最终是要到数据库中来查询不是PHP中。
1.连号,AAAAAAA,如2222222
select *
from test
where number REGEXP '1{7}|2{7}|3{7}|4{7}|5{7}|6{7}|7{7}|8{7}|9{7}';2.ABCDEFG、GFEDCBA,如2345678、9876543
这个情况太多,如果使用正则表达式会非常长,MySQL不支持正则表达式向前或向后预搜索。3.AAABCCC,如2224333、6667888
select *
from test
where number REGEXP '(1{3}|2{3}|3{3}|4{3}|5{3}|6{3}|7{3}|8{3}|9{3})?(1{3}|2{3}|3{3}|4{3}|5{3}|6{3}|7{3}|8{3}|9{3})';4.AAAABBB,如2222444、6666444
select *
from test
where number REGEXP '(1{4}|2{4}|3{4}|4{4}|5{4}|6{4}|7{4}|8{4}|9{4})(1{3}|2{3}|3{3}|4{3}|5{3}|6{3}|7{3}|8{3}|9{3})';5.xAAAAAx,如3555556
select *
from test
where number REGEXP '?(1{5}|2{5}|3{5}|4{5}|5{5}|6{5}|7{5}|8{5}|9{5})?';
2.ABCDEFG、GFEDCBA,如2345678、9876543
select *
from test
where number REGEXP '0123456|1234567|2345678|3456789|4567890|9876543|8765432|7654321|6543210';