Advanced MySQL Skills: Storage Engines, Data Types, and Character Sets

After covering basic SQL commands in a previous MySQL introduction, this article dives into advanced MySQL skills, focusing on storage engines, data type selection, and character sets.

MySQL Storage Engines

The storage engine is the component that manages how data is stored on disk. MySQL supports many engines, each offering capabilities such as concurrency, transaction support, integrity constraints, physical storage, indexing, and performance optimizations.

Commonly used engines include MyISAM, InnoDB, BDB, MEMORY, MERGE, EXAMPLE, NDB Cluster, ARCHIVE, CSV, BLACKHOLE, and FEDERATED. The default engine can be queried with default-table-type or changed in the configuration file. show variables like 'table_type'; To list all supported engines: show engines \g When creating a table you can specify the engine explicitly:

create table cxuan002(id int(10),name varchar(20)) engine = MyISAM;

To view a table’s engine use show create table. Since MySQL 5.1 the default engine is InnoDB; you can verify it with: show variables like 'default_storage_engine'; Changing an existing table’s engine:

alter table cxuan003 engine = myisam;

Engine Features

MyISAM: no transaction or foreign‑key support, table‑level locking, stores data in .MYD and indexes in .MYI files, supports Full‑Text, B‑Tree, and R‑Tree indexes.

InnoDB: supports ACID transactions, row‑level locking, foreign keys, stores data and indexes together in B+‑tree pages, provides crash‑recovery logs.

MEMORY: keeps data in memory, uses .frm for definition and .MYD for data, default index type is HASH.

MERGE: a logical union of MyISAM tables, stores definition in .frm and composition in .MRG.

Choosing the Right Storage Engine

MyISAM – read‑heavy workloads with few writes.

InnoDB – needs transactions, foreign keys, high concurrency.

MEMORY – small, frequently accessed tables.

MERGE – bypasses size limits of a single MyISAM table.

Selecting Appropriate Data Types

CHAR stores fixed‑length strings, padding with spaces; VARCHAR stores variable‑length strings and adds a length byte. Example tables illustrate storage bytes for different values.

TEXT and BLOB store large amounts of data; TEXT holds character data while BLOB holds binary data. Use OPTIMIZE TABLE to defragment after deletions, or create synthetic hash indexes for faster searches.

Floating‑point types (FLOAT, DOUBLE) may lose precision; DECIMAL provides exact numeric storage. Examples show rounding differences between FLOAT and DECIMAL.

Date and time types include DATE, TIME, DATETIME, TIMESTAMP, and YEAR. TIMESTAMP is timezone‑aware, while DATE stores only the calendar date, etc.

MySQL Character Sets

Character sets define how text is encoded. Common sets are ASCII, ISO‑8859‑1, GBK, UTF‑8, UTF‑16, and UTF‑32. You can list available sets with: show character set; Or query the information schema:

select character_set_name, default_collate_name, description, maxlen from information_schema.character_sets;

Choosing the proper charset is crucial for storage efficiency and portability.