How to use MySQL to search in php serialized fields

Well, first rule – you should not do this. But if there is good reason, consider using such query for searching in index-based arrays:

SELECT * FROM table WHERE your_field_here REGEXP '.*;s:[0-9]+:"your_value_here".*'

In case you have assoc array serialized you can use:

SELECT * FROM table WHERE your_field_here REGEXP '.*"array_key_here";s:[0-9]+:"your_value_here".*'

Of course it won’t be very fast but in small tables should be enough

6 thoughts on “How to use MySQL to search in php serialized fields”

obdev says:

November 29, 2014 at 20:31

Man, the solution is great!

Reply
David Giorgi says:

January 30, 2015 at 22:10

Brillant! Very handy ineed!

Reply
Oddy says:

July 10, 2015 at 06:36

Using this for a cron job in a table with a serialized field. Works absolutely fine, but should be used as a solution of last resort.

Reply
Sourav Basak says:

September 24, 2015 at 17:09

Hope this helps about searching a serialized string from mysql:

http://www.namasteui.com/search-from-serialize-field-in-mysql-database/

Reply
renuka says:

May 10, 2016 at 11:36

Hi,

We have to use that second query but it doesn’t return any rows. I want to check array key and array value.

Advice me.

Reply
Pooja Dahri says:

January 18, 2018 at 05:36

Save My Time …Thanks

Reply