Understanding Character Sets in MySQL: Databases, Tables, and Columns

When creating a MySQL database, you can explicitly set the character set and collation using the CREATE DATABASE statement; the system variables @@character_set_database and @@collation_database are updated accordingly. Example:

mysql> create database ytt_new2 default character set latin1 collate latin1_bin;

Altering an existing database's character set and collation changes the defaults for new objects but does not automatically modify objects created earlier, such as stored procedures, which retain the original settings and may cause encoding errors.

mysql> alter database ytt_new2 character set utf8 collate utf8_general_ci;

Parameters @@character_set_server and @@collation_server provide server‑level defaults that are inherited when no explicit charset is specified during database creation.

select @@character_set_server charset, @@collation_server collation union all select @@character_set_database, @@collation_database;

For tables, you can explicitly define a charset and collation at creation time, which takes precedence over database defaults:

create table t1(a1 int) charset latin1 collate latin1_bin;

If you omit explicit settings, tables inherit the database's charset and collation. This inheritance also applies to views and triggers, which rely on the client connection's charset settings.

set names gbk; create view v_t3 as select * from t3;

Columns can also have explicit charset and collation, but this is discouraged because it complicates data insertion and retrieval. Instead, let columns inherit the table's charset.

create table t1(a1 char(10) charset latin1 collate latin1_bin, a2 char(10) charset gbk collate gbk_bin, a3 char(10) charset utf8mb4 collate utf8mb4_bin);

When inserting data into columns with different charsets, you must use introducers to specify the source charset:

insert into t1 values (_latin1 'character ', _gbk '字符集合', _utf8mb4 '字符集合');

Changing a table's charset with ALTER TABLE ... CONVERT TO CHARACTER SET updates both the table default and the charset of its columns, whereas ALTER TABLE ... CHARSET only changes the table default, leaving column definitions unchanged.

alter table t2 convert to character set utf8;

The article concludes that understanding how character sets propagate from server to database, table, and column levels is essential for avoiding encoding issues in MySQL applications.