Databases and Table Management

DDL changes structure, not rows#

Data Definition Language statements build and change the shape of the database. Two things to know up front: in MySQL, most DDL commits immediately and cannot be rolled back, and several of them take locks that can freeze a busy table.

Creating a database#

CREATE DATABASE IF NOT EXISTS shop
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;
USE shop;

In MySQL, "database" and "schema" mean the same thing. Always set utf8mb4. The older utf8 alias is three bytes per character and can't store emoji or some CJK characters.

Creating tables#

CREATE TABLE customers (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name       VARCHAR(100) NOT NULL,
  email      VARCHAR(255) NOT NULL,
  city       VARCHAR(80),
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_customers_email (email)
) ENGINE=InnoDB;

Name constraints explicitly (uq_customers_email). Auto-generated names are fine until you need to drop one in a migration.

Useful inspection commands:

SHOW TABLES;
DESCRIBE customers;
SHOW CREATE TABLE customers\G

SHOW CREATE TABLE is the truth. It shows defaults, engine, and charset that DESCRIBE hides.

Altering tables#

ALTER TABLE customers ADD COLUMN phone VARCHAR(20) AFTER email;
ALTER TABLE customers MODIFY COLUMN city VARCHAR(120);
ALTER TABLE customers RENAME COLUMN phone TO mobile;
ALTER TABLE customers DROP COLUMN mobile;

Some ALTER operations are instant or "in place" in MySQL 8; others rebuild the whole table. On a big table, that rebuild can take minutes. You can ask MySQL to refuse the slow path:

ALTER TABLE customers ADD COLUMN notes TEXT, ALGORITHM=INPLACE, LOCK=NONE;

If it can't honor that, it errors instead of locking you out. That's the behavior you want in production.

DROP, TRUNCATE, RENAME#

DROP TABLE IF EXISTS temp_import;
TRUNCATE TABLE logs;
RENAME TABLE logs TO logs_old, logs_new TO logs;

RENAME TABLE with multiple pairs is atomic, which makes it a clean way to swap a rebuilt table into place.

Common mistakes#

• Running DROP DATABASE on the wrong connection. Read the prompt and SELECT DATABASE(); first.
• Using TRUNCATE expecting it to be rollback-safe.
• Altering a 200 million row table at peak traffic without checking the algorithm.

Practice#

Create the shop database and the customers table above. Then create products(id, name, category, price, stock) yourself with sensible types. Add a sku column, rename it to code, then drop it. Finally, create a scratch table, insert two rows, TRUNCATE it, and insert one more. What id does the new row get, and why?