1

My question is specifically for MySQL 5.6 which does not have JSON search support.

I have thousands of records and I have to check the json field state from a JSON structure. I have to check that return all records where JSON field state does not contain -, hyphen. LIKE seems costly. Is there anyother way to do it?

Volatil3
  • 14,253
  • 38
  • 134
  • 263
  • Short answer: Use MySQL 5.7. This is usually a painless upgrade, as MySQL tends to go out of its way to preserve backwards compatibility. There's some new defaults that enforce rules that might cause errors you've never seen before, but those can be turned off if you prefer. If you're using JSON data you want to be using 5.7. – tadman Oct 20 '17 at 19:14

1 Answers1

1

If you're stuck on 5.6, then it looks like you're stuck using like rlike or regexp, sorry...

And if you're stuck on 5.6 and asking what will be fastest: I'd go the regexp route.

Source: comments and other answers from this post - How to search JSON data in mysql?

Dumb question, can you update to mysql 5.7? Your life will be a lot easier if you do.

MSC
  • 2,011
  • 1
  • 16
  • 22
  • I mentioned MySQL Version, the example you shared is for MySQL versions supports JSON search. – Volatil3 Oct 20 '17 at 18:18
  • @Volatil3 Some of the answers there use the new features, some don't. – Barmar Oct 20 '17 at 18:18
  • `REGEXP` or `RLIKE`, which is efficient provided tables thousands infact 100s of thousands of records. – Volatil3 Oct 20 '17 at 18:22
  • https://stackoverflow.com/questions/8153271/regexp-performance-compare-with-like-and if you use REGEXP you'll likely also want some other filter to filter you to a subset of rows so that you dont scan the entire table (unless theres nothing else you can filter on, in which case youre stuck scanning the entire table). From a performance standpoint, I suspect regexp is better optimized for large text, whereas like is good in certain cases because it can leverage indexes (which doesnt help you here cause there's no way you'd index a json blob field in your database). – MSC Oct 20 '17 at 18:23