MySQL Table Creation

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Insert
> Step 2: And Like the video. BONUS: You can also share it!

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.



Got any MySQL Question?