A script to change all tables and fields to the utf-8-bin collation in MYSQL

A script to change all tables and fields to the utf-8-bin collation in MYSQL

  1. Blog
  2. 15 November 2026
  3. 4 min read

Stefan Bogdanescu

Founder & Senior Architect

Automating Collation Migration in MySQL

When managing a legacy database, you might encounter tables or columns using older character sets like latin1 or specific collations such as utf8_general_ci. As applications evolve to support internationalization, migrating these assets to utf8mb4 with the utf8mb4_bin collation becomes necessary. The goal here is straightforward: apply a global change across all tables and fields without manually inspecting every schema definition.

While manual updates via SQL queries are feasible for small databases, they scale poorly. A script or query set is required to iterate through metadata and execute ALTER TABLE statements dynamically. It is important to distinguish between the character set (utf8mb4) and the collation (utf8mb4_bin). The former defines the repertoire of characters supported, while the latter dictates how those characters are sorted and compared. Setting the collation to utf8mb4_bin enforces a byte-level comparison, which is often preferred for string indexes where case sensitivity and accent insensitivity should be strictly defined by the underlying bytes rather than linguistic rules.

The MySQL Information Schema Approach

The most robust method to achieve this involves querying the information_schema tables. This approach avoids hardcoding table names and works regardless of how many objects exist in the database. You can start by identifying which tables need modification. A query targeting information_schema.COLUMNS allows you to filter for columns currently using a different collation.

However, simply running an aggregate update is not possible because each column requires its own ALTER TABLE ... MODIFY statement. Therefore, a procedural approach—either via SQL stored procedures or a client-side script—is necessary. In a PHP environment, you can connect to the database, iterate through the results of a query joining TABLES and COLUMNS, and construct the appropriate ALTER statements.

The logic for constructing the statement must handle two scenarios: updating existing columns with a new collation and altering the table default. If a table does not have a table-level collation defined, it inherits from the database or server default. To ensure consistency, you often need to alter both the table level and every column level, although modern MySQL versions sometimes allow skipping column-level changes if the table is set correctly. However, explicit control is safer for legacy migrations.

Implementation Considerations and Pitfalls

Before executing bulk ALTER TABLE operations, consider the performance impact. Modifying a large InnoDB table requires copying the data to a new version of the table structure, which can lock the table depending on the MySQL version and isolation level. In MySQL 5.6 and later, online DDL allows these operations without locking the entire table for reads or writes, but it does incur significant I/O overhead.

A common mistake is attempting to change a column's collation while retaining its existing data type without explicitly specifying the CHARACTER SET as well. While changing just the collation is technically valid in many cases, best practice during a migration is to ensure the character set is also updated to utf8mb4 to support the full range of emojis and special characters that the application may eventually require.

Another critical detail involves primary keys. If a column is defined as CHARACTER SET utf8mb4 COLLATE utf8mb4_bin, it can be indexed. However, if you alter an existing column's collation without changing the character set, you risk creating inconsistencies where the index uses utf8mb4_bin but the column storage remains latin1. Always ensure the character set and collation are updated together in the ALTER TABLE statement to prevent encoding mismatches.

Scripting the Migration

You can encapsulate this logic in a PHP script that connects via PDO or the MySQLi extension, selects the target database, and loops through the schema. The script should generate the SQL commands, perhaps logging them first for review before execution, to avoid accidental data loss if an error occurs during a long-running operation.

<?php
$pdo = new PDO('mysql:host=localhost;dbname=your_database', 'user', 'pass');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $pdo->prepare("
    SELECT 
        TABLE_NAME, 
        COLUMN_NAME, 
        CHARACTER_SET_NAME, 
        COLLATION_NAME 
    FROM information_schema.COLUMNS 
    WHERE TABLE_SCHEMA = ? 
    AND (CHARACTER_SET_NAME != 'utf8mb4' OR COLLATION_NAME LIKE '%bin%' OR COLLATION_NAME NOT LIKE '%utf8mb4%')
");

$stmt->execute([getenv('DB_NAME')]);
$columns = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($columns as $col) {
    // Construct the ALTER statement for columns
    $sql = "ALTER TABLE {$col['TABLE_NAME']} MODIFY COLUMN `{$col['COLUMN_NAME']}` 
            VARCHAR({$col['COLUMN_NAME_LENGTH'] ?? 255}) 
            CHARACTER SET utf8mb4 COLLATE utf8mb4_bin";
    
    echo "Executing: $sql\n";
    // Uncomment the line below to actually run the query
    // $pdo->exec($sql);
}
?>

By automating this process, you eliminate the risk of manual oversight and ensure that every field adheres to the utf8mb4_bin standard. This approach is particularly useful when preparing a database for high-performance search requirements or strict data integrity checks.

Our network