Stats

1490 Contributors: 40
2017-03-19
Licensed under: CC-BY-SA

Not affiliated with Stack Overflow
Rip Tutorial: info@zzzprojects.com

Download eBook

Table Creation

Download mysql eBook

Syntax

  • CREATE TABLE table_name ( column_name1 data_type(size), column_name2 data_type(size), column_name3 data_type(size), .... ); // Basic table creation

  • CREATE TABLE table_name [IF NOT EXISTS] ( column_name1 data_type(size), column_name2 data_type(size), column_name3 data_type(size), .... ); // Table creation checking existing

  • CREATE [TEMPORARY] TABLE table_name [IF NOT EXISTS] ( column_name1 data_type(size), column_name2 data_type(size), column_name3 data_type(size), .... ); // Temporary table creation

  • CREATE TABLE new_tbl [AS] SELECT * FROM orig_tbl; // Table creation from SELECT

Remarks

The CREATE TABLE statement should end with an ENGINE specification:

CREATE TABLE table_name ( column_definitions ) ENGINE=engine;

Some options are:

  • InnoDB: (Default since version 5.5.5) It's a transation-safe (ACID compliant) engine. It has transaction commit and roll-back, and crash-recovery capabilities and row-level locking.
  • MyISAM: (Default before version 5.5.5) It's a plain-fast engine. It doesn't support transactions, nor foreign keys, but it's useful for data-warehousing.
  • Memory: Stores all data in RAM for extremely fast operations but table date will be lost on database restart.

More engine options here.

Related Examples