Atomically update serialized PHP arrays in MySQL

Okay, okay, it’s hard to find a use case for this when it’s so obvious that the correct way to handle one-to-many is with JOIN. But if you’re already committed to your schema and you decide you need to append serialized PHP data to a row atomically, you can cons serialized values with this query:

INSERT INTO tbl
  …
  serialized = "i:1;"
  ON DUPLICATE KEY UPDATE
    serialized = CONCAT(
      'a:3:{i:0;s:4:"cons";i:1;',
      VALUES(serialized),
      'i:2;',
      serialized,
      '}'
    )

After you have performed this three times with the serialized values 1, 2, and 3, the row contains this:

'a:3:{i:0;s:4:"cons";i:1;i:3;i:2;a:3:{i:0;s:4:"cons";i:1;i:2;i:2;a:2:{i:0;s:4:"cons";i:1;i:1;}}}'

After unserializing, deconstruct it with this function:

function decons($list) {
    $res = array();
    while ( $list != array() ) {
        if ( $list[0] === 'cons' ) {
            array_unshift( $res, $list[1] );
            $list = $list[2];
        } else {
            array_unshift( $res, $list );
            break;
        }
    }
    return $res;
}

The result:

array(1, 2, 3)

I haven’t actually used it (probably never will) but you are welcome to try this at home!

Proving that this is stupid is left as an exercise for the reader.

Leave a comment

1 Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,665 other followers

%d bloggers like this: