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>";
}
?>
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
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics