NaturalBornCamper NaturalBornCamper - 1 year ago 83
MySQL Question

MySQL extract several substrings in SELECT, an arbitrary number of times

So the previous coder of my project decided to put PHP-seralized info in the MySQL database like such:




And I need to extract using MySQL directly what's between the quotes, to display this:

Format: NTSC, License: Home


Size: S

So I need to basically keep what's between the quotes and concatenate it. I could use something like Looking to extract data between parentheses in a string via MYSQL or MySQL: Use REGEX to extract string But I can't because the number of attributes is arbitrary (0,1,2,3,...) instead of always 2 for example.

Any ways to do this?

Answer Source

Unserialize in PHP: Best option is just to unserialize it, concatenate it and hand it to your client (For performance you would narrow down the rows before bringing them to PHP).


/* To unserialize and print */
$arr=unserialize($data); $out="";
foreach ($arr as $k => $val) {
    $out.=$k.":".$val." ";
print "$out<br>";

Pattern Matching in PHP: Next option is to regex match it in PHP (There isn't much benefit of doing so though. The unserialize is better). I will leave this example here, just in case if you want to do something with regular expression matches.


preg_match_all('/"(.*?)"/',$data, $result);

for ($i = 0; $i < count($result[0]); $i+=2) {
    print $result[1][$i].":".$result[1][$i+1]." ";
print "$out<br>";

Export,Process,Import: If the table column is no more being written in the serialized format, and if it is only being read (to get back the legacy data), you could export it as csv file, process the data in perl, add a new column to contain the "Tag=Value" format separated by blanks, and imported the file back into database with a new column in which the new data is inserted. If you need it, I could supply you with a perl script to parse/convert the serialized data.

Pattern Matching in MySql using UDFs: If, on other hand, if you just want to use regular expressions in MySql query itself (to extract the tag/values), you can do so by using this extension. It needs to be build from sources, installed.

In response to question about RLIKE: If you know all possible formats of your serialized data (if it looks simple enough for you), you could run regular expression matches before bringing the rows into PHP (if performance matters).

create table tt (data varchar(100));
insert into tt values ("a:2:{s:6:\"Format\";s:4:\"NTSC\";s:7:\"License\";s:4:\"Home\";}");

select data from tt where data rlike '"Format";s:[0-9]+:"NTSC"';
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download