Understanding MySQL Collation and Character Set: Effects on Data Retrieval and Storage

Excerpt

References to MySQL documentation on character sets and collations.

Background

The client reported that query results did not meet expectations because case sensitivity was not behaving as desired.

Introduction

Collation is a set of rules that define how characters in a character set are compared. At minimum, collations specify whether comparisons are case‑sensitive and which encoding is used. The most common rule to consider is case sensitivity.

Default Selection of Character Set and Collation

If you specify only a character set without a collation, MySQL uses the default collation for that character set. Conversely, if you specify only a collation, MySQL uses the character set associated with that collation.

Viewing Character Sets and Their Default Collations

SHOW CHARACTER SET;

or

SELECT * FROM INFORMATION_SCHEMA.CHARACTER_SETS;

Viewing Collations Belonging to a Character Set

SHOW COLLATION WHERE Charset = 'utf8mb4'; -- replace utf8mb4 with the desired character set.

Viewing a Database's Default Character Set and Collation

USE db_name;

SELECT @@character_set_database, @@collation_database;

or

SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'db_name';

Setting Character Set and Collation for an Object

(click to view larger image)

Explanation

If the client queries data stored in a table, whether the search is case‑sensitive depends on the COLLATE clause defined for the column.

If the client queries a literal string, case sensitivity depends on the value of collation_connection .

Data insertion is not affected by collation; only data retrieval is.

Test Cases

Test Environment

MySQL Version: 8.0.28

Setup

create database if not exists test_db;
use test_db;
drop table if exists test_db.a;
create table test_db.a(i char(1)) engine=InnoDB default charset=utf8mb4;

Test: Verify Whether Collation Affects Data Insertion

Check if the values returned from a column are influenced by the column's collation.

alter table test_db.a change i i char(1) collate utf8mb4_0900_ai_ci;  -- case‑insensitive collation
insert into test_db.a values('a'),('A');
select * from test_db.a;  -- both 'a' and 'A' are stored
select * from test_db.a where i='a';  -- returns both rows
truncate table test_db.a;
alter table test_db.a change i i char(1) collate utf8mb4_0900_as_cs;  -- case‑sensitive collation
insert into test_db.a values('a'),('A');
select * from test_db.a;  -- both rows stored
select * from test_db.a where i='a';  -- returns only the row with 'a'
truncate table test_db.a;

Result Screenshots

Conclusion

Data insertion is not affected by the collation defined on a table column, but searching (querying) records is influenced by the column's collation.

Keywords: #collation #character set #MySQL