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