Search from serialized field in mysql database


Suppose you have a serialize string that have various values in different position and you need to search from serialized field. You want particular key’s value using MySQL query. It is very easy with MySQL “%like%” statement but “%like%” fetches more matches which you do not require.

MySQL has no understanding of PHP’s serialized format. For example, LIKE is your only option there.

Solution: split up your storage field into individual columns now that you want to SQL-query them individually after all.

Search from serialized field: Suppose you have a following serialize string in database:

a:9:{s:2:"m1";s:4:"1217";s:2:"m2";s:8:"9986-961";s:2:"m3";s:19:"1988-03-07 00:00:00";s:2:"m4";s:0:"";s:2:"m5";s:0:"";s:2:"m6";s:0:"";s:2:"m7";s:3:"104";s:2:"m8";s:6:"150000";s :2:"m9";s:18:"Ok Then, Yes It Is";}

And you need the row in which the m9 value is ‘Yes It Is’.

So the sql will be like:

SELECT * FROM table WHERE field REGEXP '.*"array_key";s:[0-9]+:".array_value.".*'


SELECT * FROM table WHERE field REGEXP '.*"m9";s:[0-9]+:".Ok Then, Yes It Is.".*'
Search from serialized field

Unless your items have a very unique way of identifying them then you’ll probably be better off to store the serialized data as a table or something else.

The sensible thing to do is either:

  • Retrieve the array into PHP, unserialize it and search in it
  • Forget about storing the data in MySQL as serialized and store it as a regular table and index it for fast search

Read Also: SQl MATCH AGAINST find results with only more than 3 characters in the result

Serializing content in MySQL typically involves storing structured data as a serialized or binary format within a database column. This is useful when you have complex data structures, such as arrays or objects, that you want to store in a single column.

It’s important to note that while serializing data can be useful for storing complex structures, it also has some drawbacks. Serialized data is not human-readable, and querying or updating specific parts of the serialized data can be challenging. In some cases, it might be more efficient and maintainable to normalize your data and use separate tables for related information.

Additionally, when using serialized data, you should be careful with security and input validation to prevent vulnerabilities like SQL injection or code execution from the deserialized content.

3 thoughts on “Search from serialized field in mysql database”

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

    My serialize data
    a:42:{i:4;s:7:”Samsung”;i:44;s:12:”Blossom Pink”;i:112;s:3:”Bar”;i:74;s:11:”Loudspeaker”;i:37;s:13:”Galaxy Note 4″;i:46;s:3:”Yes”;i:47;s:15:”Single Sim, GSM”;i:72;s:18:”SM-N910GZIEINS/INU”;i:94;s:16:”Yes, 3840 x 2160″;i:456;s:54:”F1.9 Selfie (90 Degree), Wide Selfie Mode (120 Degree)”;i:102;s:11:”HD, Full HD”;i:50;s:10:”Yes, 16 MP”;i:51;s:11:”Yes, 3.7 MP”;i:457;s:96:”Voice Commands (Smile, Cheese, Capture, Shoot), Smart OIS, Fast Auto Focus, Live HDR (Rich Tone)”;i:104;s:27:”Yes, Full HD, 4K – Ultra HD”;i:103;s:96:”Adapt Sound, Sound Alive, Wise Voice 2.0, Extra Volume 2.0, 3 Mics (Directional Voice Recording)”;i:95;s:70:”Yes, Supports WMA, eAAC+, Vorbis, FLAC, AAC, AAC+, AMR-WB, MP3, AMR-NB”;i:53;s:2:”3G”;i:96;s:7:”Android”;i:84;s:9:”Yes, v4.1″;i:105;s:3:”Yes”;i:85;s:5:”Email”;i:55;s:22:”Yes, 802.11 a/b/g/n/ac”;i:86;s:3:”Yes”;i:56;s:3:”Yes”;i:58;s:122:”Accelerometer, Geo-magnetic, Proximity Sensor, Gyroscope, Barometer, Hall Sensor, RGB Ambient Light Sensor, Gesture Sensor”;i:98;s:3:”Yes”;i:88;s:3:”Yes”;i:99;s:3:”Yes”;i:107;s:54:”Samsung WatchON, Advanced S Note Widgets, Smart Select”;i:60;s:27:”Quad HD, 2560 X 1440 Pixels”;i:109;s:23:”Vivid and Clear Display”;i:33;s:8:”3220 mAh”;i:61;s:17:”78.6×153.5×8.5 mm”;i:108;s:5:”176 g”;i:20;s:87:”1 year manufacturer warranty for Phone and 6 months warranty for in the box accessories”;i:65;s:20:”microSD, upto 128 GB”;i:66;s:8:”3 GB RAM”;i:67;s:5:”32 GB”;i:101;s:38:”GSM – 900, 1800; UMTS – 2100; 4G – LTE”;i:69;s:21:”Android v4.4 (KitKat)”;i:70;s:18:”2.7 GHz, Quad Core”;}

Leave a Reply

Your email address will not be published. Required fields are marked *