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.".*'

i.e.

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

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.