正则表达式mysql查询特定的字符串

Question

hello i am trying to search ddw_0_op1 with mysql regex serach query

SELECT * FROM wp_postmeta WHERE meta_key RLIKE '(?=.*ddw_)(?=.*_op1)'

but in results its also displaying results from this field

_ddw_0_op1

but i want to display results only from this field ddw_0_op1 . how i can achieve that in this picture i want results from http links field i hope that makes sense now

Answer 1

With INDEX(meta_key), this may be faster:

WHERE meta_key LIKE   'ddw_%_op1'
  AND meta_key RLIKE '^ddw_[[:digit:]]+_op1$'

First the LIKE (without a leading wildcard) can use the index. Then the RLIKE will verify that only digits are in the middle (without using the index).

Since this smells like a WP schema, here are some more tips on wp_postmeta. (The tips won't speed up your ddw query, but may help others.)