The collation dropdown in phpMyAdmin is deceptively calm. A database-default change can be quick, but it answers only “what should new tables inherit?” Converting existing columns is a separate data-definition operation that may rebuild tables, change comparison behavior, expose duplicate keys, and take locks. Before clicking, decide which layer you actually intend to change.

The four levels that can disagree

  • The server default is inherited when a database specifies nothing.

  • The database default is inherited by newly created tables.

  • The table default is inherited by newly created character columns.

  • A column collation controls comparisons for that column unless an expression applies another collation.

  • The client connection character set controls how application bytes are interpreted during communication.

Audit before changing anything

phpMyAdmin → SQL tabsql
SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = 'app_db';
 
SELECT TABLE_NAME, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'app_db' AND TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME;
 
SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME, COLUMN_TYPE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'app_db' AND COLLATION_NAME IS NOT NULL
ORDER BY TABLE_NAME, ORDINAL_POSITION;

Inventory proves the real scope

  • Replace app_db with the exact schema name and keep the quotes around it.

  • SCHEMATA shows the database defaults, not the encoding of every stored value.

  • A table collation is a default; columns may override it.

  • Binary strings and numeric/date columns have no text collation.

  • Export these results with the migration record so you can compare afterward.

Choose the target from application requirements

  • Use utf8mb4, not MySQL’s legacy three-byte utf8mb3, for full Unicode.

  • A suffix such as _ai_ci commonly means accent-insensitive and case-insensitive; _as_cs means accent-sensitive and case-sensitive.

  • Binary collations compare encoded values and are not a universal replacement for language-aware ordering.

  • MySQL 8 defaults and available collations can differ from MariaDB and older MySQL releases.

  • Changing sensitivity can make previously distinct unique-key values compare equal. Test production-like data.

Change only the database default in phpMyAdmin

  1. Sign in over HTTPS with an account that has the required ALTER privilege.

  2. Select the exact database in the navigation tree.

  3. Open the Operations tab.

  4. Find the database collation setting and choose the intended collation.

  5. Leave any “change all tables/columns” option disabled when you only want the default.

  6. Apply the change, then reload the Operations page and verify with SQL.

Equivalent database-default SQLsql
ALTER DATABASE `app_db`
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

What ALTER DATABASE does—and does not do

  • Backticks delimit the example identifier; never interpolate an untrusted database name into DDL.

  • The statement changes defaults stored for the schema.

  • Existing tables and columns keep their own definitions.

  • Stored routines created using database defaults retain those creation-time defaults and must be recreated if they should adopt new ones.

  • The example collation is MySQL 8-specific; choose an available, intentional collation for your server and application.

Convert an existing table only when necessary

One reviewed table conversionsql
ALTER TABLE `customers`
  CONVERT TO CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

Conversion is a data migration

  • CONVERT TO CHARACTER SET changes the table default and eligible CHAR, VARCHAR, and TEXT columns.

  • MySQL maps values from each column’s declared source character set to the target.

  • The operation uses copy-style behavior for relevant character-set changes and can consume time and disk space.

  • MySQL may widen a text type to retain its character capacity when the target uses more bytes.

  • Foreign keys, generated columns, indexes, triggers, replication, and application availability require review before execution.

Use phpMyAdmin bulk conversion cautiously

phpMyAdmin versions can offer a checkbox to change all table collations while changing the database setting. Treat that control as a batch of ALTER TABLE operations, not a display preference. On a small, backed-up development database it can be convenient; on production, generate and review a table-by-table migration with a maintenance or online-schema-change strategy suited to the workload.

Changing collation alone versus converting bytes

  • If the character set is already correct and only comparison rules should change, alter the appropriate table or column collation with a reviewed definition.

  • If the character set changes, MySQL must transcode values according to their declared source encoding.

  • If mojibake is already stored—UTF-8 bytes incorrectly declared as latin1, for example—a direct conversion can preserve or worsen corruption. Diagnose bytes on a copy first.

  • Never “repair” corrupted text by repeatedly switching dropdown values.

Verify definitions and behavior

Post-migration checkssql
SHOW CREATE DATABASE `app_db`;
SHOW CREATE TABLE `app_db`.`customers`;
 
SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'app_db' AND TABLE_NAME = 'customers'
  AND COLLATION_NAME IS NOT NULL;

Schema checks are necessary but not sufficient

  • Confirm the database, table, and column layers separately.

  • Test equality, ordering, search, unique constraints, and indexes with accents, case variants, emoji, and the application’s supported languages.

  • Verify the application connection negotiates utf8mb4; database defaults cannot correct a misconfigured client.

  • Compare row counts and critical checksums or business invariants after conversion.

  • Watch error logs, replica lag, query plans, and latency before reopening full traffic.

Frequent failures

  • Dropdown changed but old columns did not: only the database default changed; audit and migrate tables explicitly.

  • Unknown collation: the chosen name is unavailable on this MySQL/MariaDB version.

  • Duplicate entry during ALTER: the new collation considers existing indexed strings equal.

  • Incorrect string value: client, column, or target character-set support is inconsistent.

  • Garbled text after conversion: stored bytes did not match the declared source encoding or data was double-encoded.

  • Request times out: the browser/phpMyAdmin request ended while server DDL may still be running; inspect server state before retrying.

  • Foreign-key error: related columns no longer have compatible definitions or conversion order is wrong.

Primary references