MySQL Create Table
Once the database exists, define your tables. Same caveat as creating a database — in real apps, use a migration tool. But the SQL is the same.
Via PHP
PHP
$pdo->exec(<<<'SQL'
CREATE TABLE customers (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(80) NOT NULL,
email VARCHAR(120) NOT NULL UNIQUE,
country CHAR(2),
active TINYINT(1) NOT NULL DEFAULT 1,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
SQL);
Pick types deliberately
| Need | Type |
|---|---|
| Surrogate ID | INT UNSIGNED or BIGINT UNSIGNED |
| Short text | VARCHAR(n) |
| Long text | TEXT |
| Money | DECIMAL(10, 2) — never FLOAT |
| Boolean | TINYINT(1) |
| Country code | CHAR(2) |
| Timestamps | DATETIME with DEFAULT CURRENT_TIMESTAMP |
Constraints worth declaring
NOT NULLby default — make NULL the exception.UNIQUEon natural keys (email, slug, SKU).FOREIGN KEY (customer_id) REFERENCES customers(id)for relationships.CHECK (price >= 0)for invariants (MySQL 8.0.16+).
Joining tables
SQL
CREATE TABLE orders (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
customer_id INT UNSIGNED NOT NULL,
total DECIMAL(10, 2) NOT NULL CHECK (total >= 0),
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT
) ENGINE=InnoDB;
Tip: Use migrations. Tables defined in checked-in PHP files (instead of running ad-hoc CREATE TABLE) survive every environment change and code review.
Example
Example
<?php $sql = 'CREATE TABLE customers ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(80) NOT NULL, email VARCHAR(120) UNIQUE )'; $pdo->exec($sql);Try it Yourself »
Exercise
Modern transactional storage engine.
ENGINE=
Six letters; CamelCase.
Discussion
Loading…