Unserialize through query at database level itself

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.

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

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

How about this? This is a MySQL user-defined function with embedded php:

CREATE FUNCTION unserialize_php RETURNS STRING SONAME 'unserialize_php.so';

Usage example:

SELECT unserialize_php('O:8:"stdClass":2:{s:1:"a";s:4:"aaaa";s:1:"b";s:4:"bbbb";}', "$obj->a") 
AS 'unserialized';

+--------------+
| unserialized |
+--------------+
| aaaa         |
+--------------+
1 row in set (0.00 sec)

drop function unserialize_php;

Source: https://github.com/junamai2000/mysql_unserialize_php

You can create a MySQL user-defined function and call zend_eval_string inside of the function so that you can bring back PHP variables to a MySQL result. I implemented a sample program. You can try it.

From http://www.blastar.biz/2013/11/28/how-to-use-mysql-to-search-in-php-serialized-fields/

Standard array

SELECT * FROM table WHERE your_field_here REGEXP '.*;s:[0-9]+:"your_value_here".*'

Associative array

SELECT * FROM table WHERE your_field_here REGEXP '.*"array_key_here";s [0-9]+:"your_value_here".*'

Unserialize through query at database level itself

Related

Recent Posts