Ever got to that stage in a project when your client suddenly decides that he wants to search on a field that had been hidden away in as an object in a database. I have.

This project holds user information in a MySQL table. There is a column for the billing address which is a BLOB holding a serialized PHP object. For debugging purposes, I use a TEXT type instead which allows me to see the serialized object as a string. Objects serialized with the PHP serialize() function contain an explicit definition of the structure of the object so that PHP can recreate it, then associate it with a PHP class when it is loaded from the database.

My concern with searching the BLOB equivalent is that there is all the semantic information in the BLOB indicating data-types, array sizes etc. for the serialized object. If the client (for some reason) searched for ”{”, he would find that all of the data is retrieved as ”{” characters are used to delimit serialized arrays for example. Similarly ”:” is used as a separator.

Fortunately, I wrote customised serialization routines for the field just in case our client wanted to search it. These routines allow the BLOB to be searched without interference from the structural information. The only downside is that BLOB searching is case sensitive; TEXT searching is case insensitive. So, when the application goes live and I don’t need human readable storage, I may just change the type from BLOB to TEXT.