问题描述
SELECT data
FROM test
WHERE col REGEXPasdf_ [0-9] +
LIMIT 1
$ 1
$ p> SELECT data
FROM test
WHERE col LIKEasdf_%
LIMIT 1
... 1行设置(0.01秒)
$ b
regexp 可以给我确切的结果,如果我使用像 sql,我必须过滤数据。有没有一些方法可以改进?
顺便说一句:测试有200万行并且长大了。
请将您的 regexp 字符串更改为^ asdf_ [0-9] + 。LIKE 被锚定(即 LIKE'asdf _%'说以asdf_开头的字符串),而 REGEXP 不是( REGEXP'asdf_ [0-9] +' c> REGEXP'asdf_ [0-9] +'就像是说 LIKE'%asdf _%'。
我认为 REGEXP 仍然会比 LIKE 慢一些,但是希望线锚的开始会大大加快速度。
SELECT data FROM test WHERE col REGEXP "asdf_[0-9]+" LIMIT 1
...1 row in set (1 min 43.12 sec)
SELECT data FROM test WHERE col LIKE "asdf_%" LIMIT 1
...1 row in set (0.01 sec)
regexp can give me the exact result, I have to filter data if I use like sql. Is there some way to improve?
BTW: test has 2 million rows and grow up.
Try changing your regexp string to "^asdf_[0-9]+".
LIKE is anchored (ie LIKE 'asdf_%' says "a string starting with asdf_"), whereas REGEXP is not (REGEXP 'asdf_[0-9]+' looks for that anywhere within the string).
Note that doing REGEXP 'asdf_[0-9]+' is like saying LIKE '%asdf_%'.
I think REGEXP will still be a bit slower than LIKE, but hopefully the start of line anchor will greatly speed it up.
这篇关于MySQL的正则表达式比like更慢的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!