Wordpress mysql select all rows which contain serialised data

February 23, 2015

Wordpress’s ecosystem rather evilly stores settings for plugins and themes by PHP serialised arrays in it’s MySQL backend. The following mysql snippets might be useful to you when debugging serialised data. It should return you all of the mysql rows with serialised data in them.

SELECT option_id, option_value FROM wp_options WHERE option_value LIKE '\%:\{\%';

Perhaps you could also expand on this to ignore rows which have empty serialised data sets.

SELECT option_id, option_value FROM wp_options WHERE option_value LIKE '\%:\{\%' and option_value NOT LIKE '\%:\{\}%';
About me

Hello! I'm David Heward, how are you going? I'm a Senior Devops/Build Engineer, specialising in AWS & Cloud Automation. Based in London. Strong 10+ year background in Software development. Have a read of my blog. Have a look at some of my working projects. Contact me at @davehewy or on Linkedin.