Drupal 10 - Convert collation of all tables

drupal-mysql-convert-collation-all-tables.md

$database = \Drupal::database();
$charset = $database->getConnectionOptions()['charset'] ?? 'utf8mb4';
$collation = $database->getConnectionOptions()['collation'] ?? 'utf8mb4_general_ci';

echo PHP_EOL;

$tables = $database->query('SHOW TABLE STATUS where Collation <> :collation', [':collation' => $collation ])->fetchAllAssoc('Name');
if (count($tables)) {
  echo 'Converting collation of ', count($tables), ' tables to ', $collation, PHP_EOL;
  foreach (array_keys($tables) as $table_name) {
    echo '- altering table ', $table_name, PHP_EOL;
    $database->query('ALTER TABLE ' . $table_name . ' CONVERT TO CHARACTER SET ' . $charset . ' COLLATE ' . $collation . ';');
  }

  $leftover_tables = $database->query('SHOW TABLE STATUS where Collation <> :collation', [':collation' => $collation ])->fetchAllAssoc('Name');
  if (!count($leftover_tables)) {
    echo 'DONE', PHP_EOL;  
  }
  else {
    echo count($leftover_tables), ' tables not using the collation ', $collation, PHP_EOL;
    foreach (array_keys($leftover_tables) as $table_name) {
      echo ' - ', $table_name, PHP_EOL;
    }
  }
}
else {
  echo 'All tables are already using the collation ', $collation, PHP_EOL;
}