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



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/mysql/creating-using-databases-tables/create-table.php