MENU


Table Definitions

This page covers the statements that create, alter, rename, empty, and remove tables, plus the table-level features of generated columns, tablespaces, compression, and encryption.

CREATE TABLE

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
  (create_definition,...)
  [table_options]
  [partition_options]

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
  [(create_definition,...)]
  [table_options]
  [IGNORE | REPLACE]
  [AS] SELECT ...

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
  { LIKE old_tbl_name | (LIKE old_tbl_name) }

The three forms of CREATE TABLE: from column definitions, from a SELECT result, or by copying another table's structure with LIKE.


A create_definition is one of: a column definition, an {INDEX | KEY} clause, a {FULLTEXT | SPATIAL} [INDEX | KEY] clause, a PRIMARY KEY clause, a UNIQUE [INDEX | KEY] clause, a FOREIGN KEY clause, or a check-constraint definition. Index- and key-related clauses are covered on the Indexes page.

A column definition takes the form data_type [NOT NULL | NULL] [DEFAULT {literal | (expr)}] [VISIBLE | INVISIBLE] [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY] [COMMENT 'string'] [COLLATE collation_name] [COLUMN_FORMAT {FIXED | DYNAMIC | DEFAULT}] [ENGINE_ATTRIBUTE [=] 'string'] [SECONDARY_ENGINE_ATTRIBUTE [=] 'string'] [STORAGE {DISK | MEMORY}] [reference_definition] [check_constraint_definition], or, for a generated column, data_type [COLLATE collation_name] [GENERATED ALWAYS] AS (expr) [VIRTUAL | STORED] [NOT NULL | NULL] [VISIBLE | INVISIBLE] [UNIQUE [KEY]] [[PRIMARY] KEY] [COMMENT 'string'] [reference_definition] [check_constraint_definition] – see Generated Columns below. The available data_type values are the numeric, string, date/time, and spatial types described on the Data Types page.

A reference_definition (used by FOREIGN KEY, see Indexes) is REFERENCES tbl_name (key_part,...) [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE] [ON DELETE reference_option] [ON UPDATE reference_option], where reference_option is one of RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT. A check_constraint_definition is [CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]; note the CHECK clause is currently ignored by the server.

To create a table in a particular database, qualify it as db_name.tbl_name. CREATE TABLE t2 LIKE t1; copies just the structure of t1, whereas CREATE TABLE t2 SELECT * FROM t1; copies structure and data.

A TEMPORARY table is valid only for the current connection; two connections can use the same temporary table name without conflict. IF NOT EXISTS prevents an error when the table already exists – if a table of the same name exists, the new definition is not adopted. IGNORE and REPLACE control how rows that duplicate a unique key value are handled when copying a table via SELECT.

Integer and floating-point columns can have the AUTO_INCREMENT attribute. Inserting NULL or 0 into such a column sets it to the largest existing value for the column plus 1 (inserting a negative number is treated as inserting a large positive number). A table can have only one AUTO_INCREMENT column, it must be indexed, and it must not have a DEFAULT value.

As of MySQL 8.4, AUTO_INCREMENT on a FLOAT or DOUBLE column is no longer supported – what had been a deprecation warning in earlier 8.0 releases is now a hard ER_WRONG_FIELD_SPEC error. Always use an integer type (INT, BIGINT, and so on) for an AUTO_INCREMENT column; a table upgraded from an older MySQL version that still has AUTO_INCREMENT on a FLOAT or DOUBLE column must have that attribute removed, or the column converted to an integer type, before it will work on 8.4 or later.

BLOB or TEXT columns should not have the DEFAULT attribute. NOT NULL requires the column to be filled on insert; the default is to allow NULL. The DEFAULT value must be a constant, not a function or expression – CURRENT_TIMESTAMP is the exception. A COMMENT value can be up to 1024 characters.

KEY and PRIMARY KEY are the same thing. A table can have only one PRIMARY KEY, which is a NOT NULL unique index; it is stored first, followed by UNIQUE indexes, then other indexes. To span a PRIMARY KEY across multiple columns, use a separate PRIMARY KEY(index_col_name,...) clause. The name of a PRIMARY KEY is always PRIMARY. Unnamed indexes are assigned the name of their first indexed column, with an optional numeric suffix (_2, _3, ...).

For CHAR, VARCHAR, BINARY, and VARBINARY, an index prefix length can be given with col_name(length) syntax; a prefix length is mandatory for indexes on BLOB and TEXT columns.

Table Options

table_option:
  AUTOEXTEND_SIZE [=] value | AUTO_INCREMENT [=] value | AVG_ROW_LENGTH [=] value
| [DEFAULT] CHARACTER SET [=] charset_name | CHECKSUM [=] {0|1} | [DEFAULT] COLLATE [=] collation_name
| COMMENT [=] 'string' | COMPRESSION [=] {'ZLIB'|'LZ4'|'NONE'} | CONNECTION [=] 'connect_string'
| {DATA|INDEX} DIRECTORY [=] 'path' | DELAY_KEY_WRITE [=] {0|1} | ENCRYPTION [=] {'Y'|'N'}
| ENGINE [=] engine_name | ENGINE_ATTRIBUTE [=] 'string' | INSERT_METHOD [=] {NO|FIRST|LAST}
| KEY_BLOCK_SIZE [=] value | MAX_ROWS [=] value | MIN_ROWS [=] value | PACK_KEYS [=] {0|1|DEFAULT}
| PASSWORD [=] 'string' | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
| START TRANSACTION | SECONDARY_ENGINE_ATTRIBUTE [=] 'string' | STATS_AUTO_RECALC [=] {DEFAULT|0|1}
| STATS_PERSISTENT [=] {DEFAULT|0|1} | STATS_SAMPLE_PAGES [=] value | tablespace_option
| UNION [=] (tbl_name[,tbl_name]...)

Table-level options that can follow the column definitions in CREATE TABLE and ALTER TABLE.


AUTO_INCREMENT sets the table's initial AUTO_INCREMENT value. AVG_ROW_LENGTH gives MySQL an approximate average row length; for MyISAM, MySQL multiplies MAX_ROWS by AVG_ROW_LENGTH to estimate table size. Setting CHECKSUM to 1 makes MySQL maintain a live checksum for every row, easing detection of corrupted tables. Setting DELAY_KEY_WRITE to 1 delays key updates until the table is closed (MyISAM only).

For a MERGE table, INSERT_METHOD FIRST or LAST routes insertions to the first or last underlying table respectively; NO prevents insertions. For compressed InnoDB tables, KEY_BLOCK_SIZE gives the page size in kilobytes; 0 uses the default compressed page size. MAX_ROWS and MIN_ROWS are hints to the storage engine about expected row counts.

Setting PACK_KEYS to 1 in MyISAM tables produces smaller indexes at the cost of slower updates and faster reads; by default strings are packed but not numbers. PASSWORD is unused. UNION lets a MERGE table access a collection of identical MyISAM tables as one.

Values for CHARACTER SET and COLLATE, and tablespace_option, are covered further below and under Server Commands.

ALTER TABLE

ALTER TABLE tbl_name
  [alter_option [, alter_option] ...]
  [partition_options]

General shape of ALTER TABLE; alter_option accepts table_options plus the clauses below.


Key alter_option clauses: ADD [COLUMN] col_name column_definition [FIRST|AFTER col_name]; ADD [COLUMN] (col_name column_definition,...); ADD {INDEX|KEY}, ADD {FULLTEXT|SPATIAL} [INDEX|KEY], ADD PRIMARY KEY, ADD UNIQUE, and ADD FOREIGN KEY (see Indexes); ADD [CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]; DROP {CHECK|CONSTRAINT} symbol; ALTER {CHECK|CONSTRAINT} symbol [NOT] ENFORCED; ALGORITHM [=] {DEFAULT|INSTANT|INPLACE|COPY}; ALTER [COLUMN] col_name {SET DEFAULT {literal|(expr)} | SET {VISIBLE|INVISIBLE} | DROP DEFAULT}; ALTER INDEX index_name {VISIBLE|INVISIBLE}; CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]; [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]; CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]; {DISABLE|ENABLE} KEYS; {DISCARD|IMPORT} TABLESPACE; DROP [COLUMN] col_name; DROP {INDEX|KEY} index_name; DROP PRIMARY KEY; DROP FOREIGN KEY fk_symbol; FORCE; LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}; MODIFY [COLUMN] col_name column_definition [FIRST|AFTER col_name]; ORDER BY col_name [, col_name] ...; RENAME COLUMN old_col_name TO new_col_name; RENAME {INDEX|KEY} old_index_name TO new_index_name; RENAME [TO|AS] new_tbl_name; {WITHOUT|WITH} VALIDATION.

Setting ALGORITHM=COPY makes a temporary copy of the original table during the alteration: MySQL waits for pending modifications, alters the copy, deletes the original, and renames the new table in; updates and writes issued after the ALTER TABLE are stalled until the new table is ready. ALGORITHM=INPLACE uses the in-place technique for clauses and engines that support it, and fails otherwise. ALGORITHM=DEFAULT is the same as specifying no ALGORITHM at all.

LOCK=DEFAULT gives maximum concurrency. LOCK=NONE permits concurrent reads and writes where supported, erroring otherwise. LOCK=SHARED permits concurrent reads but blocks writes, erroring if concurrent reads aren't supported. LOCK=EXCLUSIVE blocks both reads and writes.

ALTER TABLE cannot change a table's storage engine to MERGE or BLACKHOLE, to prevent data loss. ORDER BY reorders the rows of the new table after the alteration.

VARCHAR column size can be increased with an in-place ALTER TABLE, e.g. ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(255);. To give a column the AUTO_INCREMENT feature, first attach it to a key, then change it: ALTER TABLE User ADD KEY (id,email); ALTER TABLE User CHANGE COLUMN id id INT AUTO_INCREMENT;. The AUTO_INCREMENT counter can be reset with ALTER TABLE tablename AUTO_INCREMENT = 1;.

RENAME TABLE

RENAME TABLE tbl_name TO new_tbl_name [, tbl_name2 TO new_tbl_name2] ...

Renames one or more tables atomically; no other session can access a table while it is being renamed.


Renames occur left to right. To swap two table names: RENAME TABLE old_table TO tmp_table, new_table TO old_table, tmp_table TO new_table;. RENAME TABLE can also move a table between databases: RENAME TABLE this_db.tbl_name TO that_db.tbl_name;. A TEMPORARY table cannot be renamed with this statement.

TRUNCATE / DROP TABLE

TRUNCATE [TABLE] tbl_name

Empties a table completely.


DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ... [RESTRICT | CASCADE]
Removes one or more tables. IF EXISTS prevents an error for non-existent tables. RESTRICT and CASCADE are not supported in MySQL 5.7. TEMPORARY drops only TEMPORARY tables, does not end an ongoing transaction, and does not check access rights.

Generated Columns

GENERATED ALWAYS specifies that a column's value is calculated automatically whenever a row is inserted or updated. STORED (see the worked example) tells MySQL to physically store the computed value in the table, which can improve query performance by avoiding recomputation on every access. When a generated column is defined without STORED, it is a VIRTUAL column – the computed value is not stored, but calculated on the fly each time it is accessed, which is useful for columns derived from other columns but can carry a runtime cost.

A virtual column is deterministic if its value depends only on its own input values and not on external factors such as random numbers or system variables. A deterministic virtual column can be indexed like any other column, as shown in the example below with an index on a STORED generated column. See also Indexes.

Table Space

A tablespace is a logical storage area used to group one or more database objects, such as tables and indexes, together – a physical file or set of files on disk that stores the data for those objects. The tablespace_option used inside table_options is TABLESPACE tablespace_name [STORAGE DISK] or [TABLESPACE tablespace_name] STORAGE MEMORY.

CREATE [UNDO] TABLESPACE tablespace_name
  -- InnoDB and NDB:
  [ADD DATAFILE 'file_name'] [AUTOEXTEND_SIZE [=] value]
  -- InnoDB only:
  [FILE_BLOCK_SIZE = value] [ENCRYPTION [=] {'Y'|'N'}]
  -- NDB only:
  USE LOGFILE GROUP logfile_group [EXTENT_SIZE [=] extent_size]
  [INITIAL_SIZE [=] initial_size] [MAX_SIZE [=] max_size] [NODEGROUP [=] nodegroup_id] [WAIT] [COMMENT [=] 'string']
  -- InnoDB and NDB:
  [ENGINE [=] engine_name]

Creating a tablespace. ENGINE_ATTRIBUTE is reserved for future use.


MySQL 8.0.21 requires that every implicit file-per-table datafile be created in a known directory, just like a general tablespace; this rule applies only to new implicit tablespaces. Known directories are defined by the settings datadir, innodb_data_home_dir, innodb_undo_directory, and innodb_directories.

ALTER [UNDO] TABLESPACE tablespace_name
  -- NDB only: {ADD|DROP} DATAFILE 'file_name' [INITIAL_SIZE [=] size] [WAIT]
  -- InnoDB and NDB: [RENAME TO tablespace_name]
  -- InnoDB only: [AUTOEXTEND_SIZE [=] 'value'] [SET {ACTIVE|INACTIVE}] [ENCRYPTION [=] {'Y'|'N'}]
  -- InnoDB and NDB: [ENGINE [=] engine_name]

DROP [UNDO] TABLESPACE tablespace_name [ENGINE [=] engine_name]

Altering and dropping a tablespace.


Compression

Data compression enables smaller database size, reduced I/O, and improved throughput, at the small cost of increased CPU utilization. It is especially valuable for read-intensive applications on systems with enough RAM to keep frequently used data in memory. InnoDB supports page-level compression for tables that reside in file-per-table tablespaces (see the worked example, which also shows the older Barracuda file-format based approach and the newer COMPRESSION table option).

Encryption

Tables can be encrypted at rest for an extra layer of security. To enable table encryption, first configure early-plugin-load=keyring_file.dll (keyring_file.so on Linux) in the MySQL configuration, then install the keyring plugin before encrypting any table:

INSTALL PLUGIN keyring_file SONAME "keyring_file.dll";
SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'keyring%';
CREATE TABLE t5 (c1 INT) ENCRYPTION='Y';

Installing the keyring plugin, then creating an encrypted table. See also Database, Server, Plugin for [UN]INSTALL PLUGIN syntax.


To disable encryption for a file-per-table tablespace: ALTER TABLE t1 ENCRYPTION='N';. The master encryption key should be rotated periodically, and whenever it may have been compromised: ALTER INSTANCE ROTATE INNODB MASTER KEY;. 

CREATE TABLE t2 LIKE t1;              -- copies structure only
CREATE TABLE t2 SELECT * FROM t1;     -- copies structure and data

CREATE TABLE `My Table`(a int);
INSERT INTO `My Table` VALUES (3);

ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(255);
ALTER TABLE A ADD COLUMN c INT DEFAULT 0;

-- turn an existing column into an AUTO_INCREMENT column
ALTER TABLE User ADD KEY (id,email);
ALTER TABLE User CHANGE COLUMN id id INT AUTO_INCREMENT;

-- reset the AUTO_INCREMENT counter
ALTER TABLE tablename AUTO_INCREMENT = 1;

CREATE TABLE Employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    full_name VARCHAR(101) GENERATED ALWAYS AS
        (CONCAT(first_name, ' ', last_name)) STORED
);
INSERT INTO Employees(first_name, last_name) VALUES ("Ali", "Graham");
SELECT * FROM Employees;

idfirst_namelast_namefull_name
1AliGrahamAli Graham

ALTER TABLE orders
ADD COLUMN total_cost DECIMAL(10,2) GENERATED ALWAYS AS
    (quantity * product_price) STORED;
CREATE INDEX idx_total_cost ON orders (total_cost);

CREATE TABLESPACE mytablespace
    ADD DATAFILE '/path/to/mytablespace.ibd'
    ENGINE=InnoDB;

CREATE TABLE mytable (
    id INT PRIMARY KEY,
    name VARCHAR(255)
) TABLESPACE mytablespace;

-- older Barracuda-file-format compression
SET GLOBAL innodb_file_per_table=1;
SET GLOBAL innodb_file_format=Barracuda;
CREATE TABLE t1(c1 INT PRIMARY KEY)
    ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
CREATE TABLESPACE `ts2` ADD DATAFILE 'ts2.ibd' FILE_BLOCK_SIZE = 8192 ENGINE=InnoDB;
CREATE TABLE t4 (c1 INT PRIMARY KEY)
    TABLESPACE ts2 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

-- InnoDB page-level compression, file-per-table tablespaces only
CREATE TABLE t1 (c1 INT) COMPRESSION="zlib";
ALTER TABLE t1 COMPRESSION="None";
OPTIMIZE TABLE t1;