sql
CREATE DATABASE IF NOT EXISTS app_db CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;Notes
Using `utf8mb4` is the standard choice for full Unicode support.
MySQL DDL and Schema Design Cheatsheet
create, alter, rename, and drop databases and tables in MySQL, with practical patterns for primary keys, unique keys, and foreign keys.
Export
Copy the compact sheet, download it, or print it.
Download
`D` dense toggle · `C` copy all
create and alter tables
Core DDL for databases, tables, columns, constraints, and defaults.
Create a users table
Create a table with an auto-increment primary key and timestamps.
sqlANYcreate-tableusersprimary-key
sql
CREATE TABLE users (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
email VARCHAR(255) NOT NULL,
full_name VARCHAR(255) NOT NULL,
is_active TINYINT(1) NOT NULL DEFAULT 1,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uq_users_email (email)
) ENGINE=InnoDB;Notes
A common baseline table definition for application data.
sql
ALTER TABLE users ADD COLUMN phone VARCHAR(50) NULL AFTER email;Notes
`AFTER` controls physical column position in MySQL table metadata.
sql
ALTER TABLE users MODIFY COLUMN phone VARCHAR(100) NULL;Notes
Use `MODIFY COLUMN` when you are changing the definition but not renaming the column.
Rename a column
Rename a column while specifying its full definition.
sqlANYalter-tablerename-column
sql
ALTER TABLE users CHANGE COLUMN full_name display_name VARCHAR(255) NOT NULL;Notes
In MySQL, `CHANGE COLUMN` renames a column and requires the new full definition.
sql
ALTER TABLE users DROP COLUMN phone;Notes
Be careful in production because this is destructive.
sql
RENAME TABLE users TO app_users;Notes
Useful during refactors or when aligning schema names with application conventions.
sql
DROP TABLE IF EXISTS old_logs;Notes
Always double-check the target table before dropping in shared environments.
constraints and keys
Primary keys, unique keys, foreign keys, and check-style patterns.
sql
ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);Notes
Unique constraints are commonly used for logins, slugs, and external identifiers.
Create a table with a foreign key
Reference a parent table and enforce relational integrity.
sqlANYforeign-keyorderscascade
sql
CREATE TABLE orders (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id BIGINT UNSIGNED NOT NULL,
status VARCHAR(50) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY idx_orders_user_id (user_id),
CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE CASCADE
ON UPDATE RESTRICT
) ENGINE=InnoDB;Notes
The explicit index on the foreign key column helps join and lookup performance.
sql
ALTER TABLE orders DROP FOREIGN KEY fk_orders_user;Notes
You may also need to drop the supporting index separately if you no longer need it.
Create a table with a composite primary key
Use multiple columns as the logical primary key.
sqlANYcomposite-keyjunction-table
sql
CREATE TABLE user_roles (
user_id BIGINT UNSIGNED NOT NULL,
role_code VARCHAR(50) NOT NULL,
granted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (user_id, role_code)
) ENGINE=InnoDB;Notes
Composite keys are common in mapping or junction tables.
sql
DROP DATABASE IF EXISTS sandbox_db;Notes
This is intentionally destructive and should be reserved for controlled environments.
More in MySQL
MySQL Procedures, Triggers, and Events Cheatsheet
stored procedures, stored functions, delimiters, call, triggers, and scheduled events in MySQL.
MySQL Functions, JSON, and Date Cheatsheet
now, date_add, date_sub, date_format, timestampdiff, json_extract, json_set, json_object, concat, coalesce, and case in MySQL.
MySQL Backup, Restore, Import, and Export Cheatsheet
mysqldump, mysql restore, load data infile, select into outfile, mysqlimport, and MySQL Shell dump/load workflows.
MySQL Users, Grants, and Privileges Cheatsheet
create users, alter passwords, grant privileges, show grants, revoke access, and manage MySQL accounts safely.
MySQL Transactions and Locking Cheatsheet
start transaction, commit, rollback, savepoints, select for update, isolation levels, and InnoDB lock troubleshooting.
MySQL Indexes, EXPLAIN, and Performance Cheatsheet
index design, show index, explain, explain analyze, analyze table, and practical performance tuning workflows in MySQL.