How to Create a Table in MySQL

Here’s an example of creating a users table in MySQL:

CREATE TABLE users (
  id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, -- Auto incrementing IDs
  name VARCHAR(100), -- String column of up to 100 characters
  preferences JSON, -- JSON columns are great for storing unstructured data and are supported starting MySQL version 5.7.8
  created_at TIMESTAMP -- Always store time in UTC
);

Within the parentheses are called column definitions separated by commas. The minimum required fields for a column definition are column name and data type, which is what is shown above for columns name, preferences, and created_at. The id column has extra fields to identify it as the primary key column and use an auto-incrementing feature to assign it values.

This is also a chance to specify not null constraints, default values, and an optional ENGINE keyword:

CREATE TABLE users (
  id INTEGER AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) not null,
  active boolean default true
) ENGINE=INNODB;

Storage engines are MySQL components that handle the SQL operations for different table types. This allows developers to extend capabilities of MySQL. When not specified, the default engine used is INNODB.