mySQL query to find multiple strings in any order within a single field?

Question

In one column of a database, we store the parameters that we used to hit an API, for example if the API call was sample.api/call?foo=1&bar=2&foobar=3 then the field will store foo=1&bar=2&foobar=3

It'd be easy enough to make a query to check 2 or 3 of those values if it was guaranteed that they'd be in that order, but that's not guaranteed. There's a possibility that call could have been made with the parameters as bar=2&foo=1&foobar=3 or any other combination.

Is there a way to make that query without saying:

SELECT * FROM table
WHERE value LIKE "%foo=1%"
AND value LIKE "%bar=2%"
AND value LIKE "%foobar=3%"

I've also tried

SELECT * FROM table
WHERE "foo=1" IN (value)

but that didn't yield any results at all.

Edit: I should have previously mentioned that I won't necessarily be always looking for the same parameters.

With the given table design there is no better way than your query with three LIKEs (or FrankerZ's query with `REGEXP` for that matter). What's so bad about that? It will be a full table scan, alright, but full table scans can still be quite fast. — Thorsten Kettner, Jan 25 '18 at 21:39
because I'm going to be putting the Query in a Spring Boot mapper and I didn't want to inject the "AND" clauses. I just wanted to put the key/value pairs so something like CONTAINS("Foo=1","bar=2") would make it easy just to use the entire inside of the parentheses as a parameter @ThorstenKettner — realmature, Jan 25 '18 at 21:47
@ThorstenKettner Also not super worried about hits to speed. Obviously performance is always worth considering but in this case entries are cleaned out every so often so the table never gets big enough for it to really be a huge issue — realmature, Jan 25 '18 at 23:01

Blue · Answer 1 · 2018-01-25T21:55:13.300

But why?

The problem with doing simple LIKE statements is this:

SELECT * FROM table
WHERE value LIKE "%foo=1%"

This will match the value asdffoo=1 and also foo=13. One hacky solution is to do this:

SELECT * FROM `api`
WHERE `params` REGEXP '(^|&)foo=1(&|$)'
AND `params` ...

Be aware, this does not use indexes. If you have a large dataset, this will need to do a row scan and be extremely slow!

Alternatively, if you can store your info in the database differently, you can utilize the FIND_IN_SET() function.

-- Store in DB as foo=1,bar=2,foobar=3
SELECT * FROM `api`
WHERE FIND_IN_SET(`params`, 'foo=1')
AND FIND_IN_SET(`params`, 'bar=2')
...

The only other solution would be to involve either another table, something like the following, and following the solution on this page:

CREATE TABLE `endpoints` (
  `id` int(6) unsigned NOT NULL AUTO_INCREMENT,
  `url` varchar(200) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `params` (
  `id` int(6) unsigned NOT NULL AUTO_INCREMENT,
  `endpoint` int(6) NOT NULL,
  `param` varchar(200) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_param` (`param`) 
) DEFAULT CHARSET=utf8;

The last and final recommendation is to upgrade to 5.7, and utilize JSON functionality. Insert the data as a JSON object, and search it as demonstrated in this question.

I'm not worried about matching anything weird like that. There will almost certainly be either nothing (if the value is at the beginning of the string) or an & (if it is not at the beginning). There won't ever be any other text before "foo" that would throw it off in such a way. — realmature, Jan 25 '18 at 21:39
Also, the values following the = are unique and of set length — realmature, Jan 25 '18 at 21:40
@realmature Do you have any ability to update the database to 5.7? I can provide you a much better solution — Blue, Jan 25 '18 at 21:45

score 0 · Answer 2 · answered Apr 28 '22 at 15:07

This is completely impossible to do properly.

Problem 1. bar and foobar overlap

so if you search for bar=2, you will match on foobar=2. This is not what you want.

This can be fixed by prepending a leading & when storing the get query string.

Problem 2. you don't know how many characters are in the value. SO you must also have an end of string character. Which is the same & character. so you need it at the beginning and end.

You now see the issue.

even if you sort the parameters before storing it all to the database, you still cant do LIKE "%&bar=2&%&foo=1&%&foobar=3&%", because the first match can overlap the second.

even after the corrections, you still have to use three LIKES to match the overlapping strings.

mySQL query to find multiple strings in any order within a single field?

2 Answers2