Introduction

In MySQL, a table's collation controls how text is compared and sorted. If you are debugging case sensitivity, unexpected ORDER BY behavior, or string-comparison mismatches between tables, checking the table collation is one of the first things to do.

The important detail is that collation exists at more than one level: database, table, and even individual column. A table check is useful, but it is not always the whole story.

Check Table Collation with SHOW TABLE STATUS

A quick way to inspect a table's collation is SHOW TABLE STATUS.

SHOW TABLE STATUS LIKE 'users';

In the result, look for the Collation field. That value is the default collation for the table.

This method is convenient when you are already in a MySQL client and want a broad view of table metadata such as engine, row count, and collation in one place.

Query information_schema Directly

If you want a queryable form that fits scripts or admin tooling, use information_schema.tables.

1SELECT table_schema,
2       table_name,
3       table_collation
4FROM information_schema.tables
5WHERE table_schema = 'app_db'
6  AND table_name = 'users';

This is often the better choice when you are checking many tables or building migration diagnostics.

Check Column Collations Too

A common mistake is assuming the table collation fully determines comparison behavior. Individual text columns can override it.

1SELECT column_name,
2       character_set_name,
3       collation_name
4FROM information_schema.columns
5WHERE table_schema = 'app_db'
6  AND table_name = 'users';

If one column uses utf8mb4_bin while the table default is utf8mb4_unicode_ci, comparisons on that column may behave differently than expected.

That is why debugging collation problems often requires both table-level and column-level inspection.

Why Collation Matters

Collation affects:

• case sensitivity
• accent sensitivity
• sort order
• string equality and grouping behavior

For example, a case-insensitive collation such as utf8mb4_general_ci will treat Alice and alice as equal for many comparison operations. A binary collation will not.

That can change query results in subtle ways, especially around WHERE, ORDER BY, GROUP BY, and unique indexes.

See Database Defaults Too

If new tables are being created with surprising collations, check the database defaults.

1SELECT schema_name,
2       default_character_set_name,
3       default_collation_name
4FROM information_schema.schemata
5WHERE schema_name = 'app_db';

A table often inherits its default character set and collation from the database at creation time unless you override it explicitly.

Change a Table Collation

If you need to change the table default collation, use ALTER TABLE.

ALTER TABLE users
CONVERT TO CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

This is more than a metadata tweak. It can rebuild text columns and may affect index behavior, sorting, and comparisons. Run it carefully on production tables, especially large ones.

A Practical Debugging Pattern

When collation issues appear, check in this order:

1. database default collation
2. table default collation
3. individual column collations
4. session or query-level overrides

That process helps you find the layer where behavior diverges instead of assuming the problem lives only at the table level.

Common Pitfalls

• Checking only the table collation and forgetting that columns can override it.
• Assuming the database default still matches tables created long ago.
• Confusing character set with collation; they are related but not the same thing.
• Changing collation on a live table without considering its impact on indexes and application behavior.
• Debugging case-sensitivity issues at the application layer when the real cause is a database collation choice.

Summary

• Use SHOW TABLE STATUS LIKE 'table_name' or information_schema.tables to check a table's collation.
• Check columns too, because they can have different collations from the table default.
• Collation affects sorting, equality, case sensitivity, and accent sensitivity.
• Database defaults influence new tables but do not automatically change old ones.
• If behavior is surprising, inspect database, table, and column collation together.