Unserialize through query at database level itself

Question

I have a column value stored in the database as:

a:2:{i:0;s:2:"US";i:1;s:2:"19";}

I want to unserialize it during the mysql query rather than using the php unserialize function after fetching the data. I want to do it this way so I can join another table with the serialized value. This would avoid executing a separate query after unserializing it with php, just for the joined data.

Answer 1

MySQL doesn't know what a PHP serialization is. You can't do it.

Answer 2

You can use SUBSTRING_INDEX

For example, if you have a record like this:

a:5:{s:9:"invoiceid";s:1:"8";s:8:"balance";i:5;s:14:"broughtforward";i:3;s:6:"userid";s:5:"13908";s:10:"customerid";s:1:"3";} 

You can use the below SELECT statement:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',1),':',-1) AS fieldname1, SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',2),':',-1) AS fieldvalue1, SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',3),':',-1) AS fieldname2, SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',4),':',-1) AS fieldvalue2, SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',5),':',-1) AS fieldname3, SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',6),':',-1) AS fieldvalue3, SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',7),':',-1) AS fieldname4, SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',8),':',-1) AS fieldvalue4, SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',9),':',-1) AS fieldname5, SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',10),':',-1) AS fieldvalue5 FROM table; 

Check this for reference: How to unserialize data using mysql without using php