MySQL CREATE TABLE Guide with Examples
For someone with an empty MySQL database who needs their first tables in it; the statements and the client output are shown for each step.
On this page
A MySQL database with no tables in it holds nothing. CREATE TABLE gives it one: you name the table, then list its columns with a data type each.
CREATE TABLE employees (
id INT,
first_name VARCHAR(50),
last_name VARCHAR(50)
);
The statements below run on MySQL 8.4. If the database itself does not exist yet, start with MySQL CREATE DATABASE.
The CREATE TABLE syntax
A column definition is a name, a type, and whatever else you want to say about that column. Types come from the MySQL type list: INT for whole numbers, VARCHAR(n) for text with a length limit, DATE for a calendar date, and so on. The MySQL manual is explicit about two things worth knowing before the first statement. Tables are created in the default database using the InnoDB storage engine, so you get transactions and foreign keys without asking for them. And a column that says neither NULL nor NOT NULL "is treated as though NULL had been specified", which means every column accepts a missing value until you say otherwise.
Running the same CREATE TABLE twice is an error, because the table is already there. Adding IF NOT EXISTS turns that error into a no-op, which is what a setup script that may run more than once wants. It compares names and nothing else: the manual warns that "there is no verification that the existing table has a structure identical to that indicated by the CREATE TABLE statement", so a table left over from an older version of the script is kept as it is. CREATE TABLE behaves much as CREATE TABLE does across SQL engines, and the engine-specific parts are the type names and the table options.
Create the table from the mysql client
The account you log in with needs the CREATE privilege for the table. Root has it; an account you just made does not, until someone grants it, and MySQL CREATE USER and GRANT covers that. Log in, then select the database the table belongs in, because CREATE TABLE writes into whichever database is currently selected:
mysql -u username -p
USE dbname;
CREATE TABLE users (
user_id INT,
username VARCHAR(50),
email VARCHAR(100)
);
Ask the server what it now has, rather than trusting that no error means what you think it means:
SHOW TABLES;
| Tables_in_dbname |
|---|
| employees |
| users |
The column heading carries the database name, which is a quick way to catch the mistake of creating a table in the wrong place after forgetting the USE.
Data types, NULL and DEFAULT in a column definition
Two clauses decide what MySQL accepts into a column. NOT NULL says a value has to be there on every insert. DEFAULT says which value the column takes when an insert leaves it out:
CREATE TABLE products (
product_id INT NOT NULL,
product_name VARCHAR(100) NOT NULL,
status VARCHAR(20) DEFAULT 'Active'
);
Insert a product without naming its status and the default fills the gap:
INSERT INTO products (product_id, product_name) VALUES (1, 'Desk lamp');
SELECT * FROM products;
| product_id | product_name | status |
|---|---|---|
| 1 | Desk lamp | Active |
Leave the same insert without a product_name and MySQL rejects the statement, because that column is NOT NULL and has no default to fall back on:
INSERT INTO products (product_id) VALUES (2);
ERROR 1364 (HY000): Field 'product_name' doesn't have a default value
The error names the column that was left without a value. The rejection itself comes from strict SQL mode, which the manual lists among the modes enabled by default in MySQL 8.4. With strict mode turned off the same insert is accepted with a warning, and product_name holds the implicit default for its type, the empty string. The pairing is the useful one: NOT NULL on the columns the row is meaningless without, DEFAULT on the ones where a sensible value beats a blank. If you would rather pick types in a diagram than type them out, the options are compared in the best MySQL database design tools.
Create the same table in DbSchema
DbSchema is a MySQL client and visual designer, and it builds the table from a dialog instead of a statement. Start it, connect to the MySQL database, then right-click the diagram canvas and choose New Table. Connecting reverse-engineers the existing schema into a design model kept in a file on your computer; the table you add next is written to MySQL as well, because the connection is live.
Double-click the table header on the diagram to open the Table Dialog and fill in the columns: a name, a type, the length, and the flags that produce NOT NULL, a default value, or MySQL's AUTO_INCREMENT. The Indexes tab is where you flag the primary key, and the Options tab holds the MySQL table options, engine and charset among them, so the DDL stays out of your way.
The next table is quicker to draw than to type. Get DbSchema from https://dbschema.com/download.html, open your MySQL database in it, and add that table from the diagram. Connecting, reverse-engineering, the interactive diagrams and the SQL editor are in the free Community Edition.
Create MySQL tables without hand-writing the DDL
DbSchema reverse-engineers your MySQL database, lets you add tables and columns visually, and shows you the SQL it generates before anything runs. The Community Edition is free.
