MySQL Configuration Deep Dive: Character Sets, Collations, Time Zones & SQL Modes with Hands-On Experiments

Experiment Environment and Scope

The experiments run on a Kylin Advanced Server V11 (Swan25), x86_64, with MySQL Community Server 8.0.46 at 192.168.19.201:3307. The author uses the local administrator root@localhost; configuration changes require Linux root privileges. A dedicated teaching database mysql_course_env is created. Initial checks confirm server version, absence of reserved teaching databases, and grants for [email protected] (which only has connection rights).

Reading Actual Values Before Changes

The author queries the current server character set, collation, global and session time zones, and SQL mode:

SELECT @@character_set_server AS server_charset,
       @@collation_server AS server_collation,
       @@GLOBAL.time_zone AS global_time_zone,
       @@SESSION.time_zone AS session_time_zone\G
SELECT @@SESSION.sql_mode AS sql_mode\G

Results show server charset utf8mb4, collation utf8mb4_0900_ai_ci, both global and session time zone set to SYSTEM (meaning the OS time zone, not a fixed offset like UTC or +08:00).

Setting Levels and Observation Methods

A table maps five setting levels to how they are observed and their scope:

Server defaults – character_set_server, collation_server – default for new databases when not specified.

Database, table, column – defined in CREATE DATABASE / CREATE TABLE – provide layered defaults for charset and collation.

Current connection – @@SESSION.time_zone, connection charset, session SQL mode – affect subsequent statements in this connection.

Global variables – @@GLOBAL.time_zone – source for new connections' initial session values.

Configuration file – my.cnf under [mysqld] – verified by restarting the service.

Changing one level does not automatically propagate to others; each variable's scope must be checked individually. The article avoids persisted variables; session experiments and config-file experiments are verified and rolled back separately.

Writing Configuration and Verifying New Connections

The author backs up /opt/mysql-course-8046/conf/my.cnf, then appends two lines under [mysqld]:

default-time-zone=+08:00
sql-mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

After validating the config with mysqld --validate-config (exit code 0), the service mysql-course is restarted. A new connection then shows both global and session time zone as +08:00 and the full SQL mode preserved. The article stresses that a successful config check does not mean running values have changed; only a new connection confirms the effect.

Character Sets vs. Collations

4.1 Connection Character Set and Byte Lengths

SET NAMES utf8mb4 COLLATE utf8mb4_0900_ai_ci;

sets client, connection, and result character sets plus the connection collation. The example string '数据库😀' yields CHAR_LENGTH = 4 (three Chinese characters + one emoji) and LENGTH = 13 bytes (3 × 3 bytes for the Chinese characters + 4 bytes for the emoji). utf8mb4 uses variable-length encoding (1–4 bytes per character), not fixed 4 bytes.

4.2 Same Character Set, Different Comparison Rules

Using explicit charset introducers and COLLATE clauses, the author compares 'A' vs 'a' and 'e' vs 'é' under two collations:

SELECT _utf8mb4'A' COLLATE utf8mb4_0900_ai_ci = _utf8mb4'a' AS ai_ci_case,
       _utf8mb4'A' COLLATE utf8mb4_0900_as_cs = _utf8mb4'a' AS as_cs_case,
       _utf8mb4'e' COLLATE utf8mb4_0900_ai_ci = _utf8mb4'é' AS ai_ci_accent,
       _utf8mb4'e' COLLATE utf8mb4_0900_as_cs = _utf8mb4'é' AS as_cs_accent;

Results: ai_ci_case = 1 (equal), as_cs_case = 0 (not equal), ai_ci_accent = 1 (equal), as_cs_accent = 0 (not equal). ai = accent-insensitive, as = accent-sensitive; ci = case-insensitive, cs = case-sensitive. The _utf8mb4 introducer forces the literal's charset; COLLATE sets the comparison rule for that expression.

4.3 Database Defaults Become New Table Defaults

A database is created with explicit charset and collation:

CREATE DATABASE mysql_course_env CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE mysql_course_env;
CREATE TABLE env_text (id INT PRIMARY KEY, label VARCHAR(32));
INSERT INTO env_text VALUES (1, '数据库😀');

SHOW CREATE TABLE env_text

reveals the table inherited utf8mb4 and utf8mb4_0900_ai_ci. The stored row shows CHAR_LENGTH(label) = 4 and LENGTH(label) = 13, matching the connection-level test. The article notes that connection settings, database defaults, and actual table definitions must be inspected separately; changing only the connection does not alter the table's definition.

Time Zone Conversion with Fixed Dates

A table with TIMESTAMP and DATETIME columns is created and populated in a +08:00 session:

SET SESSION time_zone = '+08:00';
CREATE TABLE env_time (id INT PRIMARY KEY, ts TIMESTAMP, dt DATETIME);
INSERT INTO env_time VALUES (1, '2026-01-01 08:00:00', '2026-01-01 08:00:00');

Then the session time zone is switched to +00:00 and the row is queried: ts (TIMESTAMP) displays 2026-01-01 00:00:00 – converted from stored UTC to the session's +00:00. dt (DATETIME) still displays 2026-01-01 08:00:00 – no conversion occurs.

Switching back to +08:00 makes both columns show 08:00:00. The article explains: TIMESTAMP values are converted to UTC on write and back to the session time zone on read; DATETIME values are stored and retrieved literally. The final SET SESSION time_zone = DEFAULT; reverts to the current global time zone (which at that point is +08:00, not the original SYSTEM).

SQL Mode Affects Data Validation

The author saves the current session SQL mode, then sets a strict minimal mode:

SET @saved_sql_mode = @@SESSION.sql_mode;
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
CREATE TABLE env_score (id INT PRIMARY KEY, score TINYINT UNSIGNED) ENGINE=InnoDB;
INSERT INTO env_score VALUES (1, 200);
SELECT id, score FROM env_score ORDER BY id;
SET SESSION sql_mode = @saved_sql_mode;

TINYINT UNSIGNED

range is 0–255; 200 is accepted. The article notes that a compatibility warning (Warning 3135) appears because date/zero-division modes are missing alongside strict mode; the full mode set in the config file avoids this. The user variable @saved_sql_mode is connection-specific and cannot restore another connection's mode.

Restoring Configuration, Exercises, and Cleanup

The original my.cnf is restored from backup, the service is restarted, and a new connection confirms global time zone returns to SYSTEM, full SQL mode remains, and bind address 0.0.0.0:3307 persists. The author poses three review questions: 1. Why is '数据库😀' 13 bytes not 16? (Variable-length encoding.) 2. Why are e/é equal under ai_ci? (Accent-insensitive collation.) 3. Why does time zone switch only change ts display? ( TIMESTAMP converts; DATETIME does not.) Finally, the teaching database is dropped with DROP DATABASE mysql_course_env; and verified gone.

Appendix: Errors and Warnings

9.1 Data Range Error: ERROR 1264

With strict mode active, inserting 300 into TINYINT UNSIGNED fails:

INSERT INTO env_score VALUES (2, 300);
-- ERROR 1264 (22003): Out of range value for column 'score' at row 1

The row is not saved (InnoDB single-row insert). The fix is to supply a valid value (e.g., 100), not to disable strict mode. If business needs exceed 255, the column type must be redesigned.

9.2 Mode Combination Warning: Preserving Real Diagnostics

Setting the minimal strict mode produces Warning 3135: NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO should be used with strict mode and may be merged into it in future versions. This warning is distinct from the range error. The article's config file always uses the full mode set; the minimal set isolates the session experiment.

Official References

[1] Server System Variables

[2] Server Configuration Validation

[3] Connection Character Sets

[4] utf8mb4 Character Set

[5] String Functions (CHAR_LENGTH)

[6] Collation Naming Conventions

[7] Database Character Set and Collation

[8] Time Zone Support

[9] Integer Types

[10] SQL Mode