CREATE TABLE

CREATE TABLE

CREATE TABLE and Column Definitions

Each column in CREATE TABLE is a name, a type (Data Types) and attributes such as NOT NULL, DEFAULT, AUTO_INCREMENT, UNIQUE, COMMENT or INVISIBLE; multi-column keys follow the columns. Since MySQL 8.0.13 524 a default may be an expression in parentheses:

Expression defaults, and the stored definition of the sample customers tableSQL

CREATE TABLE coupons (
  code        CHAR(8) NOT NULL DEFAULT (UPPER(LEFT(REPLACE(UUID(), '-', ''), 8))),
  valid_until DATE    NOT NULL DEFAULT (CURRENT_DATE + INTERVAL 30 DAY),
  PRIMARY KEY (code)
);
SHOW CREATE TABLE customers\G

Output

*************************** 1. row ***************************
       Table: customers
Create Table: CREATE TABLE `customers` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL,
  `name` varchar(100) NOT NULL,
  `country` char(2) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Two rows inserted with INSERT INTO coupons () VALUES (), () got codes 0977974F and 0977AB21, both valid until 2026-10-23. SHOW CREATE TABLE prints what the server stored, not what sample-db.sql typed: the inline UNIQUE became a key named email, the engine and collation came from server defaults, and AUTO_INCREMENT=9 follows the eight sample customers.

CREATE TABLE customers_archive LIKE customers copies the definition and indexes, without foreign keys or rows; CREATE TABLE country_counts AS SELECT ... copies rows with bare column types. CREATE TABLE IF NOT EXISTS on an existing table only raises Note 1050 and never compares definitions, so it hides schema drift.

MENU