Part IV — PostgreSQL and MySQL in Practice

Chapter 16. MySQL: Database Development

Chapter 15 went deep into PostgreSQL; this chapter is the same depth on MySQL. The engine's defining idea was Chapter 4's: one SQL layer over pluggable storage engines, with InnoDB as the transactional default — and that idea shapes everything here, from data types to indexing to execution plans. MySQL's dialect layer is smaller than PostgreSQL's (fewer exotic types, more function-name vocabulary), but its production footprint is enormous, and fluency in it — GROUP_CONCAT, INTERVAL arithmetic, AUTO_INCREMENT mechanics, EXPLAIN's type column — is squarely a hiring skill.

The chapter keeps the book's ledger discipline: standard features unmarked, MySQL-specific marked, and Chapter 17 tabulates the whole comparison.

After studying this chapter you will be able to:

• Explain MySQL's database/engine organization and choose storage engines deliberately.
• Describe InnoDB's clustered indexes, logs, and transaction support.
• Choose among MySQL's data types, including TIMESTAMP's semantics and limits.
• Use AUTO_INCREMENT and LAST_INSERT_ID correctly.
• Declare keys and constraints with MySQL's rules and history in mind.
• Apply MySQL's function dialect: CONCAT, GROUP_CONCAT, date arithmetic, JSON functions.
• Manage views and the stored-object family (procedures, functions, triggers, events).
• Read EXPLAIN and EXPLAIN ANALYZE output and build fitting indexes.
• Move data with LOAD DATA and INTO OUTFILE.
• Rehearse it all in practical exercises against the canonical database.

16.1 MySQL databases and storage engines

A MySQL database is a namespace that is also a directory in the data directory — on Linux, ls /var/lib/mysql/university shows one .ibd file per InnoDB table. CREATE DATABASE and CREATE SCHEMA are synonyms (Chapter 4's vocabulary note), and the information_schema is the standard catalog, implemented since 8.0 as InnoDB tables — queryable like anything else.

The pluggable engine layer, SHOW ENGINES:

 Engine      Support
------------ --------
 InnoDB      DEFAULT   ← transactions, row locks, crash recovery
 MyISAM      YES       ← tables only; historical default
 MEMORY      YES       ← in-memory, hash indexes; resets on restart
 CSV         YES       ← comma-value files as tables
 ARCHIVE     YES       ← compressed insert-only

CREATE TABLE ... ENGINE=InnoDB is explicit, and since 5.5 it is the default — which is a sentence of history worth knowing: two decades of MySQL deployments were built on MyISAM, whose table locks and lack of transactions made the "MySQL can't do transactions" reputation that InnoDB long since buried. The rule today is simple: InnoDB for everything; the others exist for niches (MEMORY for scratch lookups, CSV for interchange, ARCHIVE for audit dumps) and their choice must be argued per table.

16.2 InnoDB and transaction support

InnoDB is the database inside the database — full ACID (Chapter 18 does semantics; this is mechanics):

• Clustered primary key. Table rows are stored in the B-tree leaf pages of the primary key — the table is its PK index. Lookups by PK are single-tree descents; but every secondary index stores the PK value instead of a row pointer, so a secondary-index hit costs two B-tree lookups (secondary → PK → row). This one fact explains most MySQL indexing practice (Section 16.8, Chapter 19).
• Redo log (WAL). Changes are written to a small, sequential redo log before data pages — crash recovery replays it (Chapter 4's WAL, implemented).
• Undo log and MVCC. Old row versions live in undo records; readers see consistent snapshots without blocking writers (Chapter 18). The READ COMMITTED versus REPEATABLE READ difference is which snapshot rules apply — MySQL's default is REPEATABLE READ, PostgreSQL's is READ COMMITTED (Chapter 17 compares).
• Row-level locks (with gap locks at REPEATABLE READ), the buffer pool (the memory cache — innodb_buffer_pool_size is the single most important MySQL tuning setting), and the doublewrite buffer protecting against partial page writes.

The transactional summary: START TRANSACTION; ... COMMIT/ROLLBACK; — and one famous caveat: DDL statements cause an implicit commit in MySQL, where PostgreSQL wraps DDL in transactions like any other statement (Chapter 17's sharpest behavioral difference).

16.3 MySQL data types

The families with MySQL's specifics:

The TIMESTAMP/DATETIME choice is MySQL's classic date decision: TIMESTAMP stores UTC and converts to the session's time_zone — right for events ("enrolled at 14:03") — while DATETIME stores the literal clock reading — right for scheduled wall-clock facts ("the exam is at 09:00"). TIMESTAMP's 2038 range limit is real (the Unix-seconds storage) and one more reason new designs increasingly prefer DATETIME (or BIGINT epoch millis) for far-future events. explicit_defaults_for_timestamp (default ON in 8.0) ended the legacy surprise of auto-updating first TIMESTAMP columns.

16.4 AUTO_INCREMENT columns

MySQL's generator, one per table, always attached to a key column:

CREATE TABLE applicant (
    applicant_id INT AUTO_INCREMENT PRIMARY KEY,
    full_name    VARCHAR(60) NOT NULL
) AUTO_INCREMENT = 100;

INSERT INTO applicant (full_name) VALUES ('Dilruba Karim');
SELECT LAST_INSERT_ID();

+------------------+
| LAST_INSERT_ID() |
+------------------+
|              100 |
+------------------+

The mechanics that matter: one AUTO_INCREMENT column per table; values are assigned in insert order and are not reused after DELETE (gaps are normal, and in 8.0 the counter survives restarts); LAST_INSERT_ID() is per-session — safe under concurrency because it returns your last value, not the table's; multi-row inserts take consecutive values but LAST_INSERT_ID() returns the first. The reload/reset knob is the AUTO_INCREMENT = n table option. Chapter 9's comparison holds: PostgreSQL's identity columns are the standard spelling; AUTO_INCREMENT is the MySQL one — and the two map onto each other in any migration (Chapter 17).

16.5 Primary keys, foreign keys, and constraints

The grammar is standard (Chapter 9) with MySQL's rules: the primary key is the clustered index — choose it for lookup patterns, not just identity (an AUTO_INCREMENT surrogate or a monotonically increasing natural key keeps inserts fast; a random UUIDv4 PK splits B-tree pages — UUIDv7-style ordered identifiers are the fix in high-volume systems). Foreign keys require InnoDB on both tables — a MyISAM child parses the FK clause and ignores it, the classic silent-integrity trap in legacy schemas; the child side gets an index automatically if none exists. All referential actions (CASCADE, RESTRICT, SET NULL, SET DEFAULT, NO ACTION) work — with immediate (never deferred) checking: MySQL has no DEFERRABLE, so PostgreSQL's choreography trick has no MySQL equivalent. CHECK constraints enforce from 8.0.16 — the canonical schema's grade-scale CHECK works on current MySQL; on older versions it parsed and vanished. Constraints take names via CONSTRAINT name ..., and SHOW CREATE TABLE enrollment; prints the whole declaration, names and all — the fastest schema review command in the client.

16.6 MySQL-specific functions and operators

The dialect layer — smaller in kind, larger in vocabulary:

-- Aggregation into a string: MySQL's GROUP_CONCAT
SELECT GROUP_CONCAT(full_name ORDER BY gpa DESC SEPARATOR ', ') AS honor_line
FROM   student
WHERE  major_dept_id = 1 AND gpa IS NOT NULL;

 honor_line
----------------------------------------------------------
 Arif Mahmud, Nusrat Jahan, Tanvir Alam, Rakib Hasan

• String set: CONCAT, CONCAT_WS(', ', a, b), LENGTH (bytes) vs CHAR_LENGTH (characters), LOCATE(sub, s), SUBSTRING_INDEX(s, ',', 2), FIND_IN_SET(x, list), REPLACE, TRIM.
• Control flow: IF(cond, a, b) — the function — and IFNULL(a, b) beside the standard COALESCE and NULLIF.
• Dates — the vocabulary to memorize: NOW(), CURDATE(), YEAR(d), MONTHNAME(d), DAYNAME(d); arithmetic is DATE_ADD(d, INTERVAL 1 YEAR) / DATE_SUB (the + INTERVAL shorthand also works); differences are DATEDIFF(a, b) in days and TIMESTAMPDIFF(YEAR, a, b) in the unit you name; formatting and parsing are DATE_FORMAT(d, '%Y-%m') and STR_TO_DATE(s, '%Y-%m-%d').
• JSON functions on the JSON type: the path operators -> and ->> (extract / extract-unquoted), JSON_EXTRACT(doc, '$.courses[1]') (the -> longhand), JSON_UNQUOTE, JSON_CONTAINS, JSON_ARRAYAGG/JSON_OBJECTAGG (build JSON from rows — aggregation's mirror image of GROUP_CONCAT).
• Conversion: CAST(x AS type) and CONVERT(x, type) (plus CONVERT(s USING utf8mb4)).

One gotcha earns bold type: **GROUP_CONCAT truncates at group_concat_max_len, default 1024 bytes** — class lists and tag joins silently lose their tails until you SET SESSION group_concat_max_len = 100000;. Every long GROUP_CONCAT in production carries that line.

16.7 Views and stored database objects

MySQL views are stored queries (standard semantics): updatable when built from one table without aggregates/DISTINCT, with WITH CHECK OPTION guarding scope — the Chapter 13 rules hold. SHOW CREATE VIEW name; prints the definition.

The stored-object family — the database's procedural layer, developed fully in Chapter 22 — previews here because it includes the piece that replaces missing features: events. The MySQL Event Scheduler runs stored SQL on a calendar:

SET GLOBAL event_scheduler = ON;      -- the scheduler runs enabled events

DELIMITER $$
CREATE EVENT refresh_dept_stats
ON SCHEDULE EVERY 1 HOUR
DO
BEGIN
    TRUNCATE TABLE dept_gpa_stats;
    INSERT INTO dept_gpa_stats
    SELECT major_dept_id, COUNT(*), AVG(gpa)
    FROM   student GROUP BY major_dept_id;
END$$
DELIMITER ;

Scheduled SQL is MySQL's native answer to PostgreSQL's materialized views (Chapter 13's emulation), to summary tables (Chapter 25), and to maintenance jobs (Chapter 21) — the same mechanism, three jobs. The family's other members — stored procedures (CREATE PROCEDURE), functions, and triggers — share this event's dialect and appear with full treatment in Chapter 22.

16.8 Indexing and query execution plans

Chapter 19 measures; this is the reading lesson. MySQL's EXPLAIN output columns:

 id | select_type | table | type  | possible_keys | key     | rows | Extra
----+-------------+-------+-------+---------------+---------+------+------------------------------
  1 | SIMPLE      | student | ALL  | NULL          | NULL    | 12   |
  1 | SIMPLE      | student | ref  | gpa_idx       | gpa_idx | 2    | Using where

• type is the access quality ladder: const/eq_ref (unique/primary lookup, best) → ref (non-unique index) → range (index scan with bounds) → index (full index scan) → **ALL (full table scan — the row to eliminate)**.
• key shows the index actually chosen (possible_keys lists candidates); rows is the estimated rows examined.
• Extra carries the verdicts: **Using index** means a covering index answered the query without touching the table (with InnoDB's clustered PK, a covering secondary index saves the second lookup — this is the MySQL indexing payoff); Using filesort and Using temporary flag sorting/spilling that may want an index.

EXPLAIN ANALYZE (8.0.18+) adds actual rows and timings — the PostgreSQL counterpart of the same name. Index mechanics to know: prefix indexes (ON student (full_name(10))) for long VARCHAR keys (trade: no covering, no ORDER BY); functional key parts (8.0.13: ((LOWER(full_name)))) bring expression indexes to MySQL; FULLTEXT indexes serve text search; and SHOW INDEX FROM student; lists what exists, including the PK and the auto-created FK indexes of Section 16.5.

16.9 Importing and exporting data

MySQL's bulk mover is LOAD DATA — the COPY counterpart, with the server-client split spelled by a keyword:

LOAD DATA INFILE '/var/lib/mysql-files/departments.csv'
INTO TABLE department
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

SELECT student_id, full_name, gpa FROM student
INTO OUTFILE '/var/lib/mysql-files/students.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

LOAD DATA INFILE (server-side) is gated by secure_file_priv — the directory the server may touch, the equivalent of PostgreSQL's server-path visibility rule; **LOAD DATA LOCAL INFILE** reads client-side files (gated by local_infile, a security-sensitive switch — Chapter 20). The one-line-per-table export route is mysqldump --tab (writes CSV + schema), and mysqlimport loads those files back. The discipline is unchanged from Chapter 15: after any load, verify the counts — 5, 6, 12, 10, 13, 28 for the canonical dataset.

16.10 Practical MySQL exercises

Six exercises mirroring Chapter 15's set, in MySQL dialect.

1 — A transcript line. GROUP_CONCAT a student's record (mind the max_len):

SET SESSION group_concat_max_len = 10000;
SELECT s.full_name,
       GROUP_CONCAT(c.course_id, ':', COALESCE(e.grade, 'IP')
                    ORDER BY cs.section_year, c.course_id SEPARATOR ', ') AS record
FROM   student s
JOIN   enrollment e  ON e.student_id = s.student_id
JOIN   course_section cs ON cs.section_id = e.section_id
JOIN   course c ON c.course_id = cs.course_id
WHERE  s.student_id = 21100001
GROUP  BY s.full_name;

  full_name   |                     record
--------------+------------------------------------------------
 Nusrat Jahan | CSE215:A, CSE221:A-, MAT116:B+

2 — AUTO_INCREMENT in practice. Create the Section 16.4 applicant table, insert three rows across two statements, and capture LAST_INSERT_ID() after each — then DELETE one row, INSERT again, and show the value is not reused.

3 — Tenure with TIMESTAMPDIFF. Every instructor's whole-year tenure to 2026-10-09.

SELECT full_name, hire_date,
       TIMESTAMPDIFF(YEAR, hire_date, '2026-10-09') AS tenure_years
FROM   instructor ORDER BY tenure_years DESC;

     full_name      | hire_date  | tenure_years
--------------------+------------+--------------
 Nazmul Chowdhury   | 2015-03-10 |           11
 Mahmudul Islam     | 2016-09-05 |           10
 Ahmed Kabir        | 2018-01-15 |            8
 Sharmin Ahmed      | 2019-02-20 |            7
 Farhana Rahman     | 2020-08-01 |            6
 Tahmina Karim      | 2021-01-10 |            5

4 — JSON columns. Add meta JSON to course (dev database); store {"prereqs": [], "accredited": true} for CSE221; query with ->> and JSON_CONTAINS.

5 — EXPLAIN before and after. EXPLAIN the GPA filter (type = ALL), add the index, re-EXPLAIN (ref or range), and note the Extra column's change.

6 — LOAD DATA round-trip. Export student with INTO OUTFILE, rebuild student_copy, LOAD DATA it back, and verify counts and equality (12 rows; a NOT IN / LEFT JOIN ... IS NULL equivalence check both ways).

Chapter Summary

• Databases are namespaces and directories; storage engines are per-table choices — InnoDB for everything, others argued per niche; information_schema is real tables since 8.0.
• InnoDB: clustered PK (table = PK B-tree; secondary indexes store the PK), redo/undo logs, MVCC, REPEATABLE READ default, buffer pool; DDL commits implicitly.
• Types: UNSIGNED integers, DECIMAL for money, TEXT/BLOB families, ENUM/SET as domain-as-type, TIMESTAMP (UTC, 1970–2038) vs DATETIME (literal, wide range), JSON.
• AUTO_INCREMENT: one per table, no reuse, per-session LAST_INSERT_ID, AUTO_INCREMENT=n reset, 8.0 persists counters.
• Keys: PK choice is a physical decision (clustered); FKs need InnoDB both sides; checks immediate (no deferrable); CHECK enforces from 8.0.16; SHOW CREATE TABLE is the review command.
• Functions: CONCAT/CONCAT_WS, LENGTH vs CHAR_LENGTH, IF/IFNULL, DATE_ADD/INTERVAL, DATEDIFF/TIMESTAMPDIFF, DATE_FORMAT/STR_TO_DATE, GROUP_CONCAT (max_len!), -> / ->>, JSON_ARRAYAGG/OBJECTAGG.
• Views follow standard rules; events give scheduled SQL — the materialized-view emulation and maintenance mechanism.
• EXPLAIN: type ladder (const→ref→range→index→ALL), key/rows, Extra's Using index (covering)/filesort/temporary; prefix and functional key parts; clustered-PK double lookup is why covering matters.
• LOAD DATA INFILE/LOCAL INFILE and INTO OUTFILE move bulk rows under secure_file_priv/local_infile gates; verify counts after every load.

Key Terms

Laboratory Exercises

1. Run SHOW ENGINES; and SHOW CREATE TABLE enrollment;; identify the engine, the constraint names, and the auto-created indexes in the SHOW INDEX output. Expected result: InnoDB everywhere; named FK constraints; indexes on PK (clustered), the composite PK, and FK columns.
2. TIMESTAMP vs DATETIME: create a log_test table with both types, set time_zone to '+00:00', insert NOW(), change the session zone to '+06:00', select again — explain the difference in one sentence. Expected result: TIMESTAMP column shifts by six hours with the session; DATETIME does not — stored-literal versus UTC-converted.
3. AUTO_INCREMENT drill: Section 16.10 exercise 2 in full, recording every id and LAST_INSERT_ID() value. Expected results: ids 100, 101, 102 (first statement returns 100); after deleting 102 and inserting, the new id is 103 — not reused.
4. Dialect practice: rebuild Chapter 15's tenure and honor-line queries in MySQL (TIMESTAMPDIFF, DATE_FORMAT, GROUP_CONCAT with SEPARATOR and ORDER BY), and verify outputs against the PostgreSQL versions. Expected results: tenure 11, 10, 8, 7, 6, 5; honor line 'Arif Mahmud, Nusrat Jahan, Tanvir Alam, Rakib Hasan' — identical content, different spellings.
5. EXPLAIN study: run the before/after index exercise of Section 16.10 exercise 5; then make a covering index for SELECT student_id, gpa FROM student WHERE gpa > 3.8 (gpa first, student_id second) and catch Using index in Extra. Expected result: ALL → ref/range; the covering index yields Using index with no table access.
6. Data round-trip: LOAD DATA a CSV of department (exported via INTO OUTFILE or Workbench) into department_copy, verify 5 rows and equality, then clean up. Expected result: 5 rows loaded, contents identical, secure_file_priv path respected.

Review Questions and Exercises

1. Why does a secondary-index lookup cost two B-tree descents in InnoDB, and what eliminates the second? Secondary leaves store the PK, so the row needs a PK-tree lookup too; a covering index answers from the secondary alone (Extra: Using index).
2. What happens to an open transaction when you run ALTER TABLE in MySQL, and how does PostgreSQL differ? MySQL's DDL commits it implicitly; PostgreSQL treats DDL as ordinary transactional statements, rollable with everything else.
3. Choose and justify: TIMESTAMP or DATETIME for (a) enrollment timestamps, (b) next semester's exam schedule. (a) TIMESTAMP — event instants, UTC-correct across zones; (b) DATETIME — a wall-clock fact that must not shift with session zones.
4. Why is LAST_INSERT_ID() safe under concurrency while MAX(id) is not? It is per-session state — your last generated value; MAX(id) reads the table and races other sessions' inserts.
5. A legacy MyISAM child table declares a FOREIGN KEY. What actually happens, and what is the risk? The clause parses and is ignored — no enforcement; silent referential drift until converted to InnoDB.
6. Write the MySQL spelling of "hire date plus one year" two ways. *DATE_ADD(hire_date, INTERVAL 1 YEAR) and hire_date + INTERVAL 1 YEAR.*
7. Why does a long GROUP_CONCAT lose data, and what is the fix? group_concat_max_len (default 1024 bytes) truncates; raise it per session or globally.
8. What does EXPLAIN's type = ALL mean, and which ladder rungs would you accept for a hot query? Full table scan; const/eq_ref/ref (or range for bounded scans) — ALL only for tiny tables.
9. Contrast MySQL events with PostgreSQL materialized views as refresh mechanisms. Events are scheduled SQL re-running INSERT/SELECT into a summary table (any logic, staleness by calendar); matviews are stored query results refreshed on demand (CONCURRENTLY without blocking readers).
10. Which two settings gate LOAD DATA, and which side (server/client) does each govern? secure_file_priv — the server's allowed file area for INFILE/OUTFILE; local_infile — whether client-side LOCAL loads are enabled at all.
11. Convert to MySQL: PostgreSQL's string_agg(full_name, ', ' ORDER BY gpa DESC). *GROUP_CONCAT(full_name ORDER BY gpa DESC SEPARATOR ', ') — plus the max_len line for safety.*
12. Why does a random UUID primary key hurt InnoDB specifically, and what is the modern fix? Clustered PK: random keys scatter inserts across the B-tree (page splits, cold cache); ordered identifiers (UUIDv7-style) restore append-like behavior.

Mini-Project

Build the MySQL workbook, mysql_workbook.sql — the Chapter 15 workbook's mirror in MySQL dialect, ten numbered exercises with expected outputs in comments and standard-vs-MySQL tags: (1) engine and SHOW CREATE TABLE audit of the canonical schema; (2) the TIMESTAMP/DATETIME demonstration; (3) AUTO_INCREMENT plus a two-table document stream (insert into parent, capture LAST_INSERT_ID, use it in the child — the pattern Chapter 23's application code will repeat); (4) the tenure and honor-line reports (TIMESTAMPDIFF, DATE_FORMAT, GROUP_CONCAT); (5) the JSON meta column with ->>, JSON_CONTAINS, and JSON_OBJECTAGG building a department catalog; (6) an event-scheduled summary table refresh, observed twice; (7) EXPLAIN studies — ALL→ref, then a covering index with Using index, each plan pasted in a comment; (8) prefix and functional key parts on suitable columns with justifications; (9) the LOAD DATA round-trip with equality verification; (10) a free-choice feature (FIND_IN_SET, SUBSTRING_INDEX, CONVERT) applied to university data. Run it end to end in your MySQL university_dev; then run Chapter 15's workbook beside it and write the closing comparison note — the two workbooks are Chapter 17's raw material.