How to find serialized values in your MySQL database

As outlined in the last blog post, we needed to convert serialized data into JSON format to make Magento happy. Since Magento’s Magento\Framework\DB\AggregatedFieldDataConverter implementation needs to know which database fields to convert, we had to find them. The easiest way seems to be to go through the whole database and figure out which fields contain serialized data. Doing this by hand is no fun, so I wrote a little script that helped me with that task:

<?php

$tablesAndFields = [];

$pdo = new PDO(
    'mysql:host=127.0.0.1;dbname=magento',
    'root',
    'magento',
);

$result = $pdo->query('SHOW TABLES');
$tables = $result->fetchAll(PDO::FETCH_COLUMN);

foreach($tables as $table) {
    $result = $pdo->query('DESCRIBE '.$table);
    $description = $result->fetchAll();
    $fields = [];
    foreach ($description as $field) {
        if ((!isset($field[0])) || (!isset($field[1]))) {
            echo "Missing info for table '".$table."' and field '".$field."'!\n";
            continue;
        }

        if ((strpos($field[1], 'varchar') !== false) ||
             (strpos($field[1], 'text') !== false)) {
            $fields[] = $field[0];
        }
    }

    // check if the fields contain serialized data
    foreach ($fields as $field) {
        $result = $pdo->query('SELECT COUNT(`'.$field.'`) as cnt FROM '. $table .
              'WHERE `'.$field."` like '%:{s:%' OR `".$field."` like ':\"s:' OR `".
              $field."` like 'a:0:{}'");
        if ($result === false) {
            continue;
        }

        $count = $result->fetchAll();
        if (is_array($count) && isset($count[0], $count[0][0]) &&
            $count[0][0] > 0) {
            if(!isset($tablesAndFields[$table])) {
                $tablesAndFields[$table] = [];
            }
            $tablesAndFields[$table][] = $field;
        }
    }
}

var_dump($tablesAndFields);