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.

Follow

Get every new post delivered to your Inbox.

Join 1,667 other followers

%d bloggers like this: