MySQL CREATE TABLE

CREATE TABLE

MySQL CREATE TABLE is used to create a table within a database.

MySQL represents each table by a .frm table format (definition) file in the database directory. The storage engine might create other files as well for the table. The storage engine creates data and index files. The table for this files is as follows

Version: MySQL 5.6

Syntax:

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

Or

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

Or

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }
create_definition:
    col_name column_definition
  | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
      [index_option] ...
  | {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
      [index_name] [index_type] (index_col_name,...)
      [index_option] ...
  | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] FOREIGN KEY
      [index_name] (index_col_name,...) reference_definition
  | CHECK (expr)

column_definition:
    data_type [NOT NULL | NULL] [DEFAULT default_value]
      [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
      [COMMENT 'string']
      [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
      [STORAGE {DISK|MEMORY|DEFAULT}]
      [reference_definition]

data_type:
    BIT[(length)]
  | TINYINT[(length)] [UNSIGNED] [ZEROFILL]
  | SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
  | MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
  | INT[(length)] [UNSIGNED] [ZEROFILL]
  | INTEGER[(length)] [UNSIGNED] [ZEROFILL]
  | BIGINT[(length)] [UNSIGNED] [ZEROFILL]
  | REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | DECIMAL[(length[,decimals])] [UNSIGNED] [ZEROFILL]
  | NUMERIC[(length[,decimals])] [UNSIGNED] [ZEROFILL]
  | DATE
  | TIME
  | TIMESTAMP
  | DATETIME
  | YEAR
  | CHAR[(length)]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | VARCHAR(length)
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | BINARY[(length)]
  | VARBINARY(length)
  | TINYBLOB
  | BLOB
  | MEDIUMBLOB
  | LONGBLOB
  | TINYTEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | TEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | MEDIUMTEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | LONGTEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | ENUM(value1,value2,value3,...)
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | SET(value1,value2,value3,...)
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | spatial_type

index_col_name:
    col_name [(length)] [ASC | DESC]

index_type:
    USING {BTREE | HASH}

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'

reference_definition:
    REFERENCES tbl_name (index_col_name,...)
      [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
      [ON DELETE reference_option]
      [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION

table_options:
    table_option [[,] table_option] ...

table_option:
    ENGINE [=] engine_name
  | AUTO_INCREMENT [=] value
  | AVG_ROW_LENGTH [=] value
  | [DEFAULT] CHARACTER SET [=] charset_name
  | CHECKSUM [=] {0 | 1}
  | [DEFAULT] COLLATE [=] collation_name
  | COMMENT [=] 'string'
  | CONNECTION [=] 'connect_string'
  | DATA DIRECTORY [=] 'absolute path to directory'
  | DELAY_KEY_WRITE [=] {0 | 1}
  | INDEX DIRECTORY [=] 'absolute path to directory'
  | 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}
  | STATS_AUTO_RECALC [=] {DEFAULT|0|1}
  | STATS_PERSISTENT [=] {DEFAULT|0|1}
  | STATS_SAMPLE_PAGES [=] value
  | TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]
  | UNION [=] (tbl_name[,tbl_name]...)

partition_options:
    PARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY [ALGORITHM={1|2}] (column_list)
        | RANGE{(expr) | COLUMNS(column_list)}
        | LIST{(expr) | COLUMNS(column_list)} }
    [PARTITIONS num]
    [SUBPARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY [ALGORITHM={1|2}] (column_list) }
      [SUBPARTITIONS num]
    ]
    [(partition_definition [, partition_definition] ...)]

partition_definition:
    PARTITION partition_name
        [VALUES 
            {LESS THAN {(expr | value_list) | MAXVALUE} 
            | 
            IN (value_list)}]
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'comment_text' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]
        [NODEGROUP [=] node_group_id]
        [(subpartition_definition [, subpartition_definition] ...)]

subpartition_definition:
    SUBPARTITION logical_name
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'comment_text' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]
        [NODEGROUP [=] node_group_id]

select_statement:
    [IGNORE | REPLACE] [AS] SELECT ...   (Some valid select statement)

Arguments:

create_ definition:

column_definition: data_type

index_col_name:

index_type:

index_option:

reference_definition:

table_options:

Limits on MySQL table size

The maximum table size for MySQL databases is determined by operating system constraints on file sizes, not by MySQL internal limits. The following table shows some examples of operating system file-size limits.

For up-to-date information, check the documentation of your operating system.

Limits on MySQL table column count and row size

You can create maximum 4096 number of columns per table, but the effective maximum may be less for a given table. The exact number depends on several interacting factors.

• You can create maximum 4096 number of columns per table, but the effective maximum may be less for a given table. The exact number depends on several interacting factors.
• Individual storage engines might impose additional restrictions that limit table column count. For example, InnoDB permits up to 1000 columns.
• Every table has a maximum row size of 65,535 bytes.
• Storage engines may place additional constraints on this limit, reducing the effective maximum row size.

MySQL DESCRIBE statement

MySQL DESCRIBE statement is used to show the structure of the created table.

Syntax:

DESCRIBE [table_name];

The following statement will display the structure of a given table.

-- Describe the structure of the table named 'publisher'
DESCRIBE publisher;

Explanation:

• The SQL command DESCRIBE is used to retrieve information about the structure of a table.


• publisher is the name of the table for which we want to retrieve information.

Sample Output:

MySQL> DESCRIBE  publisher;
+----------------+-------------+------+-----+------------+-------+
| Field          | Type        | Null | Key | Default    | Extra |
+----------------+-------------+------+-----+------------+-------+
| pub_id         | varchar(8)  | NO   | PRI |            |       | 
| pub_name       | varchar(50) | NO   |     |            |       | 
| pub_city       | varchar(25) | NO   |     |            |       | 
| country        | varchar(25) | NO   |     |            |       | 
| country_office | varchar(25) | NO   |     |            |       | 
| no_of_branch   | int(3)      | NO   |     | 0          |       | 
| estd           | date        | NO   |     | 0000-00-00 |       | 
+----------------+-------------+------+-----+------------+-------+
7 rows in set (0.00 sec)

PHP script:

<?php
include('dbopen.php');
$sql = "DESCRIBE publisher";
$result = MySQL_query($sql);
echo "<table>";
echo "<h2>Structure of publisher table : </h2>";
echo "<table border='1' style='border-collapse: collapse;border-color: silver;'>";
echo "<tr style='font-weight: bold;'>";
echo "<td width='100' align='center'>Field</td>";
echo "<td width='100' align='center'>Type</td>";
echo "<td width='100' align='center'>Null</td>";
echo "<td width='100' align='center'>key</td>";
echo "<td width='100' align='center'>Default</td>";
echo "<td width='100' align='center'>Extra</td>";
echo "</tr>";
while($row = MySQL_fetch_array($result))
{
echo "<tr>";
echo "<td align='center'>" . $row['Field'] . "</td>";
echo "<td align='center'>" . $row['Type'] . "</td>";
echo "<td align='center'>" . $row['Null'] . "</td>";
echo "<td align='center'>" . $row['key'] . "</td>";
echo "<td align='center'>" . $row['Default'] . "</td>";
echo "<td align='center'>" . $row['Extra'] . "</td>";
echo "</tr>";
}
echo "</table>";
?>

Examples: MySQL CREATE TABLE

The following statement will create 'NewPublisher' table which have columns like following -

-- Create a new table named 'publisher'
CREATE TABLE publisher(
-- Define a column named 'pub_id' with a data type of VARCHAR(8)
pub_id varchar(8),
-- Define a column named 'pub_name' with a data type of VARCHAR(50)
pub_name varchar(50),
-- Define a column named 'pub_city' with a data type of VARCHAR(25)
pub_city varchar(25),
-- Define a column named 'country' with a data type of VARCHAR(25)
country varchar(25),
-- Define a column named 'country_office' with a data type of VARCHAR(25)
country_office varchar(25),
-- Define a column named 'no_of_branch' with a data type of INT(3)
no_of_branch int(3),
-- Define a column named 'estd' with a data type of DATE
estd date);

Explanation:

• The SQL command CREATE TABLE is used to create a new table in the database.


• publisher is the name of the table being created.


• Inside the parentheses, multiple columns are defined, each with its own name and data type.


• pub_id, pub_name, pub_city, country, and country_office are columns with data types of VARCHAR, which can store variable-length character strings.


• no_of_branch is a column with a data type of INT(3), specifying that it will store integer values with a maximum width of 3 digits.


• estd is a column with a data type of DATE, indicating that it will store date values.

PHP script:

<?php
include('dbopen.php');
$result = MySQL_query("CREATE TABLE NewPublisher(
pub_id varchar(8),
pub_name varchar(50),
pub_city varchar(25),
country varchar(25),
country_office	varchar(25),
no_of_branch int(3),
estd date)");
$sql1 = "SHOW TABLES FROM bookinfo";
$result1 = MySQL_query($sql1);
while($row1=MySQL_fetch_array($result1))
{
echo "<table>";
echo "<tr>";
echo "<td align='center'>" . $row1['Tables_in_bookinfo'] . "</td>";
echo "</tr>";
echo "</table>";
}
?>

View the example in browser

MySQL CREATE TABLE when not EXISTS

The keywords IF NOT EXISTS is used to prevent an error from occurring if the table exists. The keywords IF NOT EXISTS will not verify whether the existing table is of the same structure indicated by the CREATE TABLE statement. The following statement creates a table newauthor if the table 'newauthor' does not exist with the following column name, type, length and default value -

-- Create a new table named 'newauthor' if it does not already exist
CREATE TABLE IF NOT EXISTS newauthor(
-- Define a column named 'aut_id' with a data type of VARCHAR(8)
aut_id varchar(8),
-- Define a column named 'aut_name' with a data type of VARCHAR(50)
aut_name varchar(50),
-- Define a column named 'country' with a data type of VARCHAR(25)
country varchar(25),
-- Define a column named 'home_city' with a data type of VARCHAR(25) and allow NULL values
home_city varchar(25) NULL); 

Explanation:

• The SQL command CREATE TABLE is used to create a new table in the database.


• The IF NOT EXISTS clause ensures that the table is only created if it does not already exist in the database.


• newauthor is the name of the table being created.


• Inside the parentheses, multiple columns are defined, each with its own name and data type.


• aut_id, aut_name, country are columns with data types of VARCHAR, which can store variable-length character strings.


• home_city is a column with a data type of VARCHAR(25) and is allowed to contain NULL values, indicated by the NULL keyword.

MySQL CREATE with LIKE or copy table structure

MySQL allows you to create a table identical to another by using LIKE. The following MySQL statement will create a table 'author_copy' whose structure is identical to the table 'author'.

Sample table: author

-- Create a new table named 'author_copy' with the same structure as the 'author' table
CREATE TABLE author_copy LIKE author;

Explanation:

• The SQL command CREATE TABLE is used to create a new table in the database.


• author_copy is the name of the new table being created.


• LIKE author specifies that the new table's structure should be identical to the existing table named 'author'.

MySQL CREATE TABLE with SELECT or copy a table

MySQL AS SELECT allows you to copy the data (either selective or total) or table structure to a new table. The following MySQL statement will create a new table 'author_copy' with the same structure and data of 'author' table .

-- Create a new table named 'author_copy' and populate it with data from the 'author' table
CREATE TABLE author_copy 
-- Select all columns and their data from the 'author' table
AS SELECT *
-- Specify the source table from which data will be retrieved
FROM author;

Explanation:

• The SQL command CREATE TABLE is used to create a new table in the database.


• author_copy is the name of the new table being created.


• AS SELECT * specifies that the new table should include all columns and their data.


• FROM author specifies the source table from which data will be retrieved to populate the new table.

MySQL CHARACTER SET in CREATE TABLE

MySQL uses tables CHARACTER SET and COLLATION as default values for column definitions if the character set specified while defining a column. The following MySQL statement will create a table 'mytale1' using the CHARACTER SET and COLLATION tables.

-- Create a new table named 'mytable1'
CREATE TABLE mytable1 (
-- Define a column named 'col1' with a data type of VARCHAR(20) and character set utf8
col1 VARCHAR(20) CHARACTER SET utf8,
-- Define a column named 'col2' with a data type of TEXT, character set latin1, and collation latin1_general_cs
col2 TEXT CHARACTER SET latin1 COLLATE latin1_general_cs
);

Explanation:

• The SQL command CREATE TABLE is used to create a new table in the database.


• mytable1 is the name of the new table being created.


• Inside the parentheses, two columns are defined, each with its own name and data type.


• col1 is a column with a data type of VARCHAR(20) and is set to use the utf8 character set.


• col2 is a column with a data type of TEXT and is set to use the latin1 character set and collation latin1_general_cs.


• Character set determines the encoding used to store character data in the column.


• Collation defines the rules used for comparing and sorting character data.

MySQL CREATE TABLE with BINARY CHARACTER SET

While creating a table in MySQL, using CHARACTER SET binary, you can define that the character set for a column will be binary. The following MySQL statement will create a table 'mytale2' using the CHARACTER SET as binary.

-- Create a new table named 'mytable2'
CREATE TABLE mytable2 (
-- Define a column named 'col1' with a data type of VARCHAR(10) and character set binary
col1 VARCHAR(10) CHARACTER SET binary,
-- Define a column named 'col2' with a data type of TEXT and character set binary
col2 TEXT CHARACTER SET binary,
-- Define a column named 'col3' with a data type of ENUM and character set binary
col3 ENUM('a','b','c') CHARACTER SET binary
);

Explanation:

• The SQL command CREATE TABLE is used to create a new table in the database.


• mytable2 is the name of the new table being created.


• Inside the parentheses, three columns are defined, each with its own name, data type, and character set.


• col1 is a column with a data type of VARCHAR(10) and is set to use the binary character set.


• col2 is a column with a data type of TEXT and is set to use the binary character set.


• col3 is a column with a data type of ENUM('a','b','c') and is set to use the binary character set.


• The binary character set stores data using the binary representation of the characters, which is useful for binary data or when character encoding is not relevant.

MySQL CREATE TABLE with BLOB CHARACTER SET

While creating a table in MySQL, using BLOB following a column name, you can define that the character set for a column will be BLOB. The following MySQL statement will create a table 'mytale3' using the CHARACTER SET binary and BLOB.

-- Create a new table named 'mytable3'
CREATE TABLE mytable3 (
-- Define a column named 'col1' with a data type of VARBINARY(10)
col1 VARBINARY(10),
-- Define a column named 'col2' with a data type of BLOB
col2 BLOB,
-- Define a column named 'col3' with a data type of ENUM and character set binary
col3 ENUM('a','b','c') CHARACTER SET binary
);

Explanation:

• The SQL command CREATE TABLE is used to create a new table in the database.


• mytable3 is the name of the new table being created.


• Inside the parentheses, three columns are defined, each with its own name, data type, and optionally, character set.


• col1 is a column with a data type of VARBINARY(10), which can store binary data up to 10 bytes in length.


• col2 is a column with a data type of BLOB, which can store large binary data such as images, audio, or video files.


• col3 is a column with a data type of ENUM('a','b','c') and is set to use the binary character set.


• The binary character set stores data using the binary representation of the characters, which is useful for binary data or when character encoding is not relevant.

MySQL CREATE TABLE with ENUM data type

While creating a table in MySQL, using ENUM following a column name, you can define that the character set for a column will be enumerated. The following MySQL statement will create a table 'testtable' using the data type ENUM.

-- Create a new table named testtable
CREATE TABLE testtable(
-- Define a column named book_lang
book_lang ENUM('english', 'german', 'french')
);

Explanation:

• The SQL command CREATE TABLE is used to create a new table in the database.


• testtable is the name of the table being created.


• Inside the parentheses, the column book_lang is defined.


• ENUM('english', 'german', 'french') specifies that the column book_lang will only accept values that are within the enumerated list: 'english', 'german', or 'french'.


• ENUM is a data type in MySQL that represents a set of predefined values.

MySQL CREATE TABLE with specific data type

While creating a table in MySQL, using the specific data type name, following a column name, you can define the character set for a column. The following MySQL statement will create a table 'testtable' using the data type as specified.

-- Create a new table named 'testtable'
CREATE TABLE testtable (
-- Define a column named 'string1' with a data type of VARCHAR(4)
string1 VARCHAR(4),
-- Define a column named 'string2' with a data type of CHAR(4)
string2 CHAR(4)
);

Explanation:

• The SQL command CREATE TABLE is used to create a new table in the database.


• testtable is the name of the new table being created.


• Inside the parentheses, two columns are defined, each with its own name and data type.


• string1 is a column with a data type of VARCHAR(4), which can store variable-length character strings with a maximum length of 4 characters.


• string2 is a column with a data type of CHAR(4), which can store fixed-length character strings with a length of 4 characters.

See also: MySQL DROP table

Previous: MySQL DATABASE
Next: Loading data into a table and usage of line terminator