w3resource

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

File Purpose
table_name.frm Table format (definition) file.
table_name.MYD Data file.
table_name.MYI Index file.

Version: MySQL 5.6

Contents:

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:

Name Description
TEMPORARY TEMPORARY keyword can be used to create a table. TEMPORARY table is only visible to the current connection, and dropped automatically with the connection closed. To create temporary tables, it is necessary to have the CREATE TEMPORARY TABLES privilege.
IF NOT EXISTS The keywords IF NOT EXISTS prevent an error from occurring if the table exists.
tbl_name name of the table
LIKE The keyword LIKE is used to create an empty table based on the definition of another table, including any column attributes and indexes defined in the original table.
If the original table is a TEMPORARY table, CREATE TABLE ... LIKE does not preserve TEMPORARY.
LIKE works only for base tables, not for views.

create_ definition:

Name Description
CONSTRAINT CONSTRAINT is used to define rules to allow or restrict what values can be stored in columns.
PRIMARY KEY A PRIMARY KEY is a unique index where all key columns must be defined as NOT NULL. If not declare MySQL declares them so implicitly. A table can have only one PRIMARY KEY.
INDEX | KEY KEY is normally a synonym for INDEX
UNIQUE A UNIQUE index creates a constraint in which all values in the index must be distinct. An error occurs when you try to add a new row with a key value that matches an existing row.
FULLTEXT FULLTEXT indexes, are used for full-text searches. Only the MyISAM storage engine supports FULLTEXT indexes. They can be created only from CHAR, VARCHAR, and TEXT columns. Indexing always happens over the entire column; column prefix indexing is not supported and any prefix length is ignored if specified.
SPATIAL SPATIAL indexes can be created on spatial data types. Spatial types are supported only for MyISAM tables and indexed columns must be declared as NOT NULL.
FOREIGN KEY InnoDB and NDB tables support checking of foreign key constraints. The columns of the referenced table must always be explicitly named. Both ON DELETE and ON UPDATE functions on foreign keys.
CHECK For other storage engines, MySQL Server parses and ignores the FOREIGN KEY and REFERENCES syntax in CREATE TABLE statements other than the InnoDB and NDB engine. The CHECK clause is parsed but ignored by all storage engines.

column_definition: data_type

Name Description
NOT NULL | NULL If neither NULL nor NOT NULL is specified, the column is treated that, NULL had been specified.
DEFAULT DEFAULT does not apply to the BLOB or TEXT types.
AUTO_INCREMENT AUTO_INCREMENT applies only to integer and floating-point types. When you insert a value of NULL or 0 into an indexed AUTO_INCREMENT column, the column is set to the next sequence value and that is is value+1, where value is the largest value for the column currently in the table. AUTO_INCREMENT sequences begin with 1. There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value. An AUTO_INCREMENT column works properly only if it contains only positive values. For MyISAM tables, an AUTO_INCREMENT secondary column in a multiple-column key can be specified.
COMMENT A comment for a column can be specified with the COMMENT option, up to 1024 characters long.
COLUMN_FORMAT In MySQL Cluster, a data storage format can be specified for individual columns of NDB tables using COLUMN_FORMAT. The default value for COLUMN_FORMAT for NDB tables is DEFAULT. The COLUMN_FORMAT keyword is supported only in the build of MySQLd that is supplied with MySQL Cluster; it is not recognized in any other version of MySQL.
STORAGE It is possible for NDB tables, to specify whether the column is stored on disk or in memory by using a STORAGE clause. Causes of STORAGE DISK the column can be stored on disk, and STORAGE MEMORY causes in-memory storage to be used. The STORAGE DEFAULT is equivalent to STORAGE MEMORY for NDB tables. The STORAGE clause has no effect on tables using storage engines other than NDB.

index_col_name:

Name Description
col_name Name of the column
length Length of the column
ASC Sorting order in ascending.
DESC Sorting order in descending.

index_type:

Name Description
USING Some storage engines permits to specify an index type declaration at the time of creating an index and the syntax for the index_type specifier is USING type_name. Before MySQL 5.1.10, USING can be given only before the index column list.

index_option:

Name Description
KEY_BLOCK_SIZE In the case of InnoDB compressed table the size can be specified in kilobytes (which is optional ) to use for pages. The value mention 0 indicates the default compressed page size.
WITH PARSER A WITH PARSER clause can be specified as an index_option value to associate a parser plugin with the index when full-text indexing and searching operations need special handling. The WITH PARSER clause is valid only for FULLTEXT indexes.

reference_definition:

Name Description
REFERENCES The REFERENCES clauses is used only when specified as part of a separate FOREIGN KEY specification.

table_options:

Name Description
ENGINE . In MySQL, the storage engines are such components that handle the SQL operations for different table types. One of the most general-purpose storage engine is InnoDB.
AVG_ROW_LENGTH An approximate value of the average row length for a table used in MySQL. It is only required to set this only for large tables with variable-size rows. When a MyISAM table is created, MySQL uses the product of the MAX_ROWS and AVG_ROW_LENGTH options to decide how big the resulting table may be. If don't specify the either option, the maximum size for MyISAM data and index files is 256TB by default.
CHARACTER SET CHARSET is a synonym for CHARACTER SET. If the character set name is DEFAULT, the database character set is used.
CHECKSUM If the value of CHECKSUM is set to 1, MySQL maintains a live checksum for all rows, i.e. MySQL updates the table automatically as it changes. Although it makes the table a little slower to update, but also makes it easier to find corrupted tables.
COLLATE Specify a default collation for the table.
CONNECTION The connection string for a FEDERATED table.
DATA DIRECTORY By using DATA DIRECTORY clause it is to understand that where the InnoDB storage engine puts the .ibd tablespace file for a new table.
DELAY_KEY_WRITE Set this to 1 when it is needed to delay key updates for the table until the table is closed.
INDEX DIRECTORY At the time of creating MyISAM tables, INDEX DIRECTORY clause can be used to know where to put a MyISAM table's index file.
INSERT_METHOD When it is needed to insert data into a MERGE table, you have to specify with INSERT_METHOD, the table into which the row should be inserted. The INSERT_METHOD is an option which is useful for MERGE tables only.
MAX_ROWS, MIN_ROWS The maximum number of rows you plan to store in the table. This is not a hard limit, but rather a hint to the storage engine that the table must be able to store at least this many rows.
MAX_ROWS and MIN_ROWS may be used to specify, respectively, the maximum and a minimum number of rows to be stored in the partition. The values for maximum number of rows and a minimum number of rows must be positive integers.
PACK_KEYS The effect of PACK_KEYS only found with MyISAM tables. When we want to have smaller indexes set the PACK_KEYS option to 1, and this option generally makes updates slower and reads faster. If we set the option to 0 it disables all packing of keys. The DEFAULT setting tells the storage engine to pack only long CHAR, VARCHAR, BINARY, or VARBINARY columns.
By default, it packs strings, not numbers if we do not use PACK_KEYS. If we use the value of PACK_KEYS is 1, numbers are packed as well.
ROW_FORMAT Defines the physical format in which the rows are stored. The choices differ depending on the storage engine used for the table.
For InnoDB table, by default. the rows are stored in compact format i.e. ROW_FORMAT=COMPACT.
In the case of MyISAM tables, the option value can be FIXED or DYNAMIC for static or variable-length row format.
STATS_AUTO_RECALC Specifies whether to automatically recalculate persistent statistics for an InnoDB table. The value DEFAULT is responsible for the persistent statistics setting for the table to be determined by the innodb_stats_auto_recalc configuration option. The value set to 1 determined the statistics to be recalculated when 10% of the data in the table has changed. The value 0 prevents automatic recalculation for this table; with this setting.
STATS_PERSISTENT Specifies whether to enable persistent statistics for an InnoDB table. The value DEFAULT is responsible for the persistent statistics setting for the table to be determined by the innodb_stats_persistent configuration option. The value set to 1 enables the persistent statistics for the table, while the value 0 turns off.
STATS_SAMPLE_PAGES The number of index pages to sample when estimating cardinality and other statistics for an indexed column, such as those calculated by ANALYZE TABLE.
UNION The UNION is used when we want to access a collection of identical MyISAM tables as one. This works only with MERGE tables.

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.

Operating System File-size Limit
Win32 w/ FAT/FAT32 2GB/4GB
Win32 w/ NTFS 2TB (possibly larger)
Linux 2.2-Intel 32-bit 2GB (LFS: 4GB)
Linux 2.4+ (using ext3 file system) 4TB
Solaris 9/10 16TB
Mac OS X w/ HFS+ 2TB

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  publisher;

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 TABLE  publisher(
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);

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 TABLE IF NOT EXISTS newauthor
(aut_id        	varchar(8),
aut_name      	varchar(50),  
country       	varchar(25),  
home_city     	varchar(25) NULL); 

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 TABLE author_copy LIKE 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 TABLE author_copy 
AS SELECT *
FROM  author;

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 TABLE mytable1 (col1  VARCHAR(20) CHARACTER SET utf8, 
col2 TEXT CHARACTER SET latin1 COLLATE latin1_general_cs );

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 TABLE mytable2 (   col1 VARCHAR(10) CHARACTER SET binary, 
col2 TEXT CHARACTER SET binary, 
col3 ENUM('a','b','c') CHARACTER SET binary ); 

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 TABLE mytable3
(col1 VARBINARY(10),   col2 BLOB,   
col3 ENUM('a','b','c') CHARACTER SET binary );

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 TABLE testtable(book_lang ENUM('english', 'german','french') );

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 TABLE testtable  ( string1  VARCHAR(4),  string2  CHAR(4));

See also: MySQL DROP table

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



Follow us on Facebook and Twitter for latest update.