SQL查询：获取具有最大编号的字符串| 解析？

Question

I have a database which looks like this:

Do not ask my why, but this is written by a WordPress plugin so I have to handle with it. What I want is to get every link where the meta_key = product_shops_X_link. But every post has a variable amount of product shop links.

In this picture I tried Where meta_key Like "%_link%". But if I do a query like that I also get all meta_keys where the product_shops_X_link field has a _ before the product...

What I want is just all product_shops_X_link fields. Now there a two ways I think.

Way 1: Get the highest number of the meta key fields Where meta_key Like "%product_shops_X_link%" OR meta_key Like "%_product_shops_X_link%". It doesn't matter of _product or just product. The numbers are equal. If there is just a product_shops_0_link field there must be also a _product_shops_0_link field.

Way2: Create a pattern which only searches for all product_shops_X_link fields without the beginning of _product and give me ALL product_shops_X_link fields. Whatever number is contained in the field.

But in both cases I do not know how to code that.. Does anyone knows how to code that or is there maybe a better way?

Greetings and Thank You!

Answer 1

If you don't want entries starting with _product then you can exclude _ or % from your LIKE pattern, e.g.:

SELECT *
FROM links
WHERE meta_key LIKE 'product_%';