Master MySQL: From Database Basics to Advanced Character Set & Collation Handling

Introduction

A database is a structured repository for storing and managing data; its concept originated over sixty years ago and has evolved from simple tables to massive systems that support diverse data‑management needs.

Database Types

Databases are broadly classified by storage medium into relational (SQL) and non‑relational (NoSQL) systems. Examples of relational databases include Oracle, DB2, SQL Server, MySQL, and Access. Non‑relational examples include Memcached, MongoDB, and Redis.

Relational Database Basics

A relational database is built on a mathematical relation model consisting of three elements: a two‑dimensional table structure (rows and columns), a set of SQL statements, and integrity constraints (field‑level and table‑level). Design follows the principle of mapping each entity to a table with fields (columns) and records (rows).

SQL Overview

SQL (Structured Query Language) is the command language for relational databases. It is divided into three families:

DDL – Data Definition Language (e.g., create, drop, alter)

DML – Data Manipulation Language (e.g., insert, delete, update, select)

DCL – Data Control Language (e.g., grant, revoke)

Different database products may implement minor variations of these commands.

MySQL Architecture

MySQL follows a client‑server model. The client connects to the server using parameters such as -h (host), -P (port), -u (user), and -p (password). Internally the server layers are: DBMS → Database → Table → Field.

SQL Basic Operations (Database Level)

Creating a database: create database TBL_ERROR_CODE charset utf8; Optional clauses: charset – character set (e.g., utf8, gbk) collate – collation (e.g., utf8_general_ci)

Other commands:

Show databases: show databases; Show databases matching a pattern: show databases like 'TBL%'; Show creation statement: show create database db_name; Alter database (only charset/collate can be changed): alter database db_name charset gbk; Drop database: drop database db_name; (must back up first)

Table Operations

Creating a table (with optional if not exists and table options):

create table if not exists test.student(
    name varchar(10),
    age int,
    grade varchar(10)
) charset utf8;

Table options include charset, collate, and storage engine (default InnoDB).

Querying tables:

Show all tables: show tables; Show tables matching a pattern: show tables like 'pattern'; Show create table: show create table tbl_name; Describe columns: desc tbl_name; Altering tables:

Rename table: rename table old_name to new_name; Change table options: alter table tbl_name engine=MyISAM; Add column: alter table tbl_name add column id int first; Modify column: alter table tbl_name modify name char(10) after id; Change column name/type: alter table tbl_name change old_col new_col varchar(10); Drop column: alter table tbl_name drop age; (irreversible if data exists)

Dropping tables (multiple tables can be removed in one statement):

drop table tbl1, tbl2;

Data Manipulation

Insert rows (full‑row syntax): insert into test values ('charies',18,'3.1'); Insert with column list (order independent): insert into test(age,name) values (18,'guo'); Select data:

All rows: select * from test; Specific columns with condition: select name,age,grade from test where age='18'; Update rows (always use a WHERE clause to avoid affecting the whole table): update test set age = 20 where name = 'guo'; Check success by examining the affected‑row count (>0 indicates a real update).

Delete rows: delete from test where grade = '3.1'; Deletion is irreversible; back up before executing.

Chinese Data and Character‑Set Issues

MySQL stores data in binary form; a character set defines the mapping between bytes and characters. When the client uses GBK (2 bytes per Chinese character) but the server defaults to UTF‑8 (3 bytes), inserting Chinese text fails or appears garbled.

Key commands to inspect character‑set settings:

show character set;               -- list all character sets
show variables like 'character_set%';  -- view server defaults and connection variables

Solution: set the session variables to GBK so the server receives and returns GBK‑encoded data.

set character_set_client = gbk;
set character_set_results = gbk;
set character_set_connection = gbk;   -- three statements are equivalent to
set names gbk;                       -- changes the three variables at once

After changing the session variables, new Chinese inserts succeed, but previously stored UTF‑8 data will appear as garbled characters when retrieved because the client now expects GBK.

Collation (Sorting) Issues

Collation determines how string comparison is performed. MySQL provides three main families: _bin – binary comparison (case‑sensitive) _cs – case‑sensitive _ci – case‑insensitive

List collations: show collation; Example: create two tables with different collations and observe sorting behavior.

create table my_collate_bin(
    name char(10)
) charset utf8 collate utf8_bin;

create table my_collate_ci(
    name char(10)
) charset utf8 collate utf8_general_ci;

insert into my_collate_bin values ('a'),('A'),('B'),('b');
insert into my_collate_ci values ('a'),('A'),('B'),('b');

select * from my_collate_bin order by name;
select * from my_collate_ci order by name;

The result shows that utf8_bin treats upper‑ and lower‑case as distinct, while utf8_general_ci treats them as equal.

Important notes: collations must be defined before data is inserted; changing a table’s collation after data exists has no effect.

Key Takeaways

Database and table creation syntax includes optional charset and collation clauses.

Session‑level character‑set settings ( set names) are convenient but reset after disconnect; for permanent changes edit the server configuration.

Always back up before dropping databases or tables.

Use appropriate collations for the desired sorting and comparison behavior.