CREATE [TEMPORARY] TABLE [IF NOT EXISTS]tbl_name
(create_definition
,...) [table_options
]
Or:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS]tbl_name
[(create_definition
,...)] [table_options
]select_statement
Or:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS]tbl_name
{ LIKEold_tbl_name
| (LIKEold_tbl_name
) }
create_definition
:col_name
column_definition
| [CONSTRAINT [symbol
]] PRIMARY KEY [index_type
] (index_col_name
,...) | {INDEX|KEY} [index_name
] [index_type
] (index_col_name
,...) | [CONSTRAINT [symbol
]] UNIQUE [INDEX|KEY] [index_name
] [index_type
] (index_col_name
,...) | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name
] (index_col_name
,...) | [CONSTRAINT [symbol
]] FOREIGN KEY [index_name
] (index_col_name
,...)reference_definition
| CHECK (expr
)column_definition
:data_type
[NOT NULL | NULL] [DEFAULTdefault_value
] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string
'] [reference_definition
]data_type
: 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 SETcharset_name
] [COLLATEcollation_name
] | VARCHAR(length
) [CHARACTER SETcharset_name
] [COLLATEcollation_name
] | BINARY[(length
)] | VARBINARY(length
) | TINYBLOB | BLOB | MEDIUMBLOB | LONGBLOB | TINYTEXT [BINARY] [CHARACTER SETcharset_name
] [COLLATEcollation_name
] | TEXT [BINARY] [CHARACTER SETcharset_name
] [COLLATEcollation_name
] | MEDIUMTEXT [BINARY] [CHARACTER SETcharset_name
] [COLLATEcollation_name
] | LONGTEXT [BINARY] [CHARACTER SETcharset_name
] [COLLATEcollation_name
] | ENUM(value1
,value2
,value3
,...) [CHARACTER SETcharset_name
] [COLLATEcollation_name
] | SET(value1
,value2
,value3
,...) [CHARACTER SETcharset_name
] [COLLATEcollation_name
] |spatial_type
index_col_name
:col_name
[(length
)] [ASC | DESC]index_type
: USING {BTREE | HASH | RTREE}reference_definition
: REFERENCEStbl_name
(index_col_name
,...) [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE] [ON DELETEreference_option
] [ON UPDATEreference_option
]reference_option
: RESTRICT | CASCADE | SET NULL | NO ACTIONtable_options
:table_option
[[,]table_option
] ...table_option
: {ENGINE|TYPE} =engine_name
| AUTO_INCREMENT =value
| AVG_ROW_LENGTH =value
| [DEFAULT] CHARACTER SET =charset_name
| CHECKSUM = {0 | 1} | [DEFAULT] COLLATE =collation_name
| COMMENT = 'string
' | DATA DIRECTORY = 'absolute path to directory
' | DELAY_KEY_WRITE = {0 | 1} | INDEX DIRECTORY = 'absolute path to directory
' | INSERT_METHOD = { NO | FIRST | LAST } | MAX_ROWS =value
| MIN_ROWS =value
| PACK_KEYS = {0 | 1 | DEFAULT} | PASSWORD = 'string
' | RAID_TYPE = { 1 | STRIPED | RAID0 } RAID_CHUNKS =value
RAID_CHUNKSIZE =value
| ROW_FORMAT = {DEFAULT|DYNAMIC|FIXED|COMPRESSED} | UNION = (tbl_name
[,tbl_name
]...)select_statement:
[IGNORE | REPLACE] [AS] SELECT ... (Some legal select statement
)
CREATE TABLE
creates a table with
the given name. You must have the
CREATE
privilege for the table.
Rules for allowable table names are given in Section 8.2, “Database, Table, Index, Column, and Alias Names”. By default, the table is created in the default database. An error occurs if the table exists, if there is no default database, or if the database does not exist.
In MySQL 3.22 or later, the table name can be specified as
db_name.tbl_name
to create the table in
a specific database. This works regardless of whether there is a
default database, assuming that the database exists. If you use
quoted identifiers, quote the database and table names separately.
For example, write `mydb`.`mytbl`
, not
`mydb.mytbl`
.
From MySQL 3.23 on, you can use the TEMPORARY
keyword when creating a table. A TEMPORARY
table is visible only to the current connection, and is dropped
automatically when the connection is closed. This means that two
different connections can use the same temporary table name
without conflicting with each other or with an existing
non-TEMPORARY
table of the same name. (The
existing table is hidden until the temporary table is dropped.)
From MySQL 4.0.2 on, to create temporary tables, you must have the
CREATE TEMPORARY TABLES
privilege.
CREATE TABLE
does not
automatically commit the current active transaction if you use
the TEMPORARY
keyword.
In MySQL 3.23 or later, the keywords IF NOT
EXISTS
prevent an error from occurring if the table
exists. However, there is no verification that the existing table
has a structure identical to that indicated by the
CREATE TABLE
statement.
MySQL represents each table by an .frm
table
format (definition) file in the database directory. The storage
engine for the table might create other files as well. In the case
of MyISAM
tables, the storage engine creates
data and index files. Thus, for each MyISAM
table tbl_name
, there are three disk
files.
File | Purpose |
|
Table format (definition) file |
|
Data file |
|
Index file |
Chapter 13, Storage Engines, describes what files each storage engine creates to represent tables.
data_type
represents the data type in a
column definition. spatial_type
represents a spatial data type. The data type syntax shown is
representative only. For a full description of the syntax
available for specifying column data types, as well as information
about the properties of each type, see
Chapter 10, Data Types, and
Chapter 16, Spatial Extensions.
Some attributes do not apply to all data types.
AUTO_INCREMENT
applies only to integer and
floating-point types. DEFAULT
does not apply to
the BLOB
or
TEXT
types.
If neither NULL
nor NOT
NULL
is specified, the column is treated as though
NULL
had been specified.
An integer or floating-point column can have the additional
attribute AUTO_INCREMENT
. When you insert a
value of NULL
(recommended) or
0
into an indexed
AUTO_INCREMENT
column, the column is set to
the next sequence value. Typically this is
, where
value
+1value
is the largest value for the
column currently in the table.
AUTO_INCREMENT
sequences begin with
1
.
To retrieve an AUTO_INCREMENT
value after
inserting a row, use the
LAST_INSERT_ID()
SQL function
or the mysql_insert_id()
C API
function. See Section 11.10.3, “Information Functions”, and
Section 17.6.3.35, “mysql_insert_id()
”.
As of MySQL 4.1.1, if the
NO_AUTO_VALUE_ON_ZERO
SQL
mode is enabled, you can store 0
in
AUTO_INCREMENT
columns as
0
without generating a new sequence value.
See Section 5.1.7, “Server SQL Modes”.
There can be only one AUTO_INCREMENT
column per table, it must be indexed, and it cannot have a
DEFAULT
value. As of MySQL 3.23, an
AUTO_INCREMENT
column works properly only
if it contains only positive values. Inserting a negative
number is regarded as inserting a very large positive
number. This is done to avoid precision problems when
numbers “wrap” over from positive to negative
and also to ensure that you do not accidentally get an
AUTO_INCREMENT
column that contains
0
.
For MyISAM
and BDB
tables, you can specify an AUTO_INCREMENT
secondary column in a multiple-column key. See
Section 3.6.9, “Using AUTO_INCREMENT
”.
To make MySQL compatible with some ODBC applications, you can
find the AUTO_INCREMENT
value for the last
inserted row with the following query:
SELECT * FROMtbl_name
WHEREauto_col
IS NULL
For information about InnoDB
and
AUTO_INCREMENT
, see
Section 13.2.5.3, “AUTO_INCREMENT
Handling in InnoDB
”.
As of MySQL 4.1, character data types
(CHAR
,
VARCHAR
,
TEXT
) can include
CHARACTER SET
and
COLLATE
attributes to specify the character
set and collation for the column. For details, see
Section 9.1, “Character Set Support”. CHARSET
is a
synonym for CHARACTER SET
. Example:
CREATE TABLE t (c CHAR(20) CHARACTER SET utf8 COLLATE utf8_bin);
Also as of 4.1, MySQL interprets length specifications in
character column definitions in characters. (Earlier versions
interpret them in bytes.) Lengths for
BINARY
and
VARBINARY
are in bytes.
NULL
values are handled differently for
TIMESTAMP
columns than for
other column types. Before MySQL 4.1.6, you cannot store a
literal NULL
in a
TIMESTAMP
column; setting the
column to NULL
sets it to the current date
and time. Because TIMESTAMP
columns behave this way, the NULL
and
NOT NULL
attributes do not apply in the
normal way and are ignored if you specify them. On the other
hand, to make it easier for MySQL clients to use
TIMESTAMP
columns, the server
reports that such columns can be assigned
NULL
values (which is true), even though
TIMESTAMP
never actually
contains a NULL
value. You can see this
when you use DESCRIBE
to get a
description of your table.
tbl_name
Note that setting a TIMESTAMP
column to 0
is not the same as setting it
to NULL
, because 0
is a
valid TIMESTAMP
value.
The DEFAULT
clause specifies a default
value for a column. With one exception, the default value must
be a constant; it cannot be a function or an expression. This
means, for example, that you cannot set the default for a date
column to be the value of a function such as
NOW()
or
CURRENT_DATE
. The exception is
that you can specify
CURRENT_TIMESTAMP
as the
default for a TIMESTAMP
column
as of MySQL 4.1.2. See Section 10.3.1.2, “TIMESTAMP
Properties as of MySQL 4.1”.
If a column definition includes no explicit
DEFAULT
value, MySQL determines the default
value as described in Section 10.1.4, “Data Type Default Values”.
A comment for a column can be specified with the
COMMENT
option. The comment is displayed by
the SHOW CREATE TABLE
and
SHOW FULL
COLUMNS
statements. This option is operational as of
MySQL 4.1. (It is allowed but ignored in earlier versions.)
KEY
is normally a synonym for
INDEX
. From MySQL 4.1, the key attribute
PRIMARY KEY
can also be specified as just
KEY
when given in a column definition. This
was implemented for compatibility with other database systems.
A UNIQUE
index creates a constraint such
that all values in the index must be distinct. An error occurs
if you try to add a new row with a key value that matches an
existing row. This constraint does not apply to
NULL
values except for the
BDB
storage engine. For other engines, a
UNIQUE
index allows multiple
NULL
values for columns that can contain
NULL
.
A PRIMARY KEY
is a unique index where all
key columns must be defined as NOT NULL
. If
they are not explicitly declared as NOT
NULL
, MySQL declares them so implicitly (and
silently). A table can have only one PRIMARY
KEY
. If you do not have a PRIMARY
KEY
and an application asks for the PRIMARY
KEY
in your tables, MySQL returns the first
UNIQUE
index that has no
NULL
columns as the PRIMARY
KEY
.
In InnoDB
tables, having a long
PRIMARY KEY
wastes a lot of space. (See
Section 13.2.11, “InnoDB
Table and Index Structures”.)
In the created table, a PRIMARY KEY
is
placed first, followed by all UNIQUE
indexes, and then the nonunique indexes. This helps the MySQL
optimizer to prioritize which index to use and also more
quickly to detect duplicated UNIQUE
keys.
A PRIMARY KEY
can be a multiple-column
index. However, you cannot create a multiple-column index
using the PRIMARY KEY
key attribute in a
column specification. Doing so only marks that single column
as primary. You must use a separate PRIMARY
KEY(
clause.
index_col_name
, ...)
If a PRIMARY KEY
or
UNIQUE
index consists of only one column
that has an integer type, you can also refer to the column as
_rowid
in
SELECT
statements (new in MySQL
3.23.11).
In MySQL, the name of a PRIMARY KEY
is
PRIMARY
. For other indexes, if you do not
assign a name, the index is assigned the same name as the
first indexed column, with an optional suffix
(_2
, _3
,
...
) to make it unique. You can see index
names for a table using SHOW INDEX FROM
. See
Section 12.4.5.13, “tbl_name
SHOW INDEX
Syntax”.
From MySQL 4.1.0 on, some storage engines allow you to specify
an index type when creating an index. The syntax for the
index_type
specifier is
USING
.
type_name
Example:
CREATE TABLE lookup (id INT, INDEX USING BTREE (id)) ENGINE = MEMORY;
For details about USING
, see
Section 12.1.4, “CREATE INDEX
Syntax”.
For more information about indexes, see Section 7.4.4, “How MySQL Uses Indexes”.
Only the MyISAM
, InnoDB
,
BDB
, and (as of MySQL 4.0.2)
MEMORY
storage engines support indexes on
columns that can have NULL
values. In other
cases, you must declare indexed columns as NOT
NULL
or an error results.
For CHAR
,
VARCHAR
,
BINARY
, and
VARBINARY
columns, indexes can
be created that use only the leading part of column values,
using
syntax to specify an index prefix length.
col_name
(length
)BLOB
and
TEXT
columns also can be
indexed, but a prefix length must be
given. Prefix lengths are given in characters for nonbinary
string types and in bytes for binary string types. That is,
index entries consist of the first
length
characters of each column
value for CHAR
,
VARCHAR
, and
TEXT
columns, and the first
length
bytes of each column value
for BINARY
,
VARBINARY
, and
BLOB
columns. Indexing only a
prefix of column values like this can make the index file much
smaller. See Section 7.4.2, “Column Indexes”.
Only the MyISAM
and (as of MySQL 4.0.14)
InnoDB
storage engines support indexing on
BLOB
and
TEXT
columns. For example:
CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));
Prefixes can be up to 1000 bytes long (767 bytes for
InnoDB
tables). (Before MySQL 4.1.2, the
limit is 255 bytes for all tables.) Note that prefix limits
are measured in bytes, whereas the prefix length in
CREATE TABLE
statements is
interpreted as number of characters for nonbinary data types
(CHAR
,
VARCHAR
,
TEXT
). Take this into account
when specifying a prefix length for a column that uses a
multi-byte character set.
An index_col_name
specification can
end with ASC
or DESC
.
These keywords are allowed for future extensions for
specifying ascending or descending index value storage.
Currently, they are parsed but ignored; index values are
always stored in ascending order.
When you use ORDER BY
or GROUP
BY
on a TEXT
or
BLOB
column in a
SELECT
, the server sorts values
using only the initial number of bytes indicated by the
max_sort_length
system
variable. See Section 10.4.3, “The BLOB
and
TEXT
Types”.
In MySQL 3.23.23 or later, you can create special
FULLTEXT
indexes, which are used for
full-text searches. Only the MyISAM
table
type 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. See
Section 11.8, “Full-Text Search Functions”, for details of operation.
In MySQL 4.1 or later, you can create
SPATIAL
indexes on spatial data types.
Spatial types are supported only for MyISAM
tables and indexed columns must be declared as NOT
NULL
. See Chapter 16, Spatial Extensions.
In MySQL 3.23.44 or later, InnoDB
tables
support checking of foreign key constraints. See
Section 13.2, “The InnoDB
Storage Engine”. Note that the FOREIGN
KEY
syntax in InnoDB
is more
restrictive than the syntax presented for the
CREATE TABLE
statement at the
beginning of this section: The columns of the referenced table
must always be explicitly named. InnoDB
supports both ON DELETE
and ON
UPDATE
actions on foreign keys as of MySQL 3.23.50
and 4.0.8, respectively. For the precise syntax, see
Section 13.2.5.4, “FOREIGN KEY
Constraints”.
For other storage engines, MySQL Server parses and ignores the
FOREIGN KEY
and
REFERENCES
syntax in
CREATE TABLE
statements. The
CHECK
clause is parsed but ignored by all
storage engines. See Section 1.9.5.5, “Foreign Keys”.
For users familiar with the ANSI/ISO SQL Standard, please
note that no storage engine, including
InnoDB
, recognizes or enforces the
MATCH
clause used in referential
integrity constraint definitions. Use of an explicit
MATCH
clause will not have the specified
effect, and also causes ON DELETE
and
ON UPDATE
clauses to be ignored. For
these reasons, specifying MATCH
should be
avoided.
The MATCH
clause in the SQL standard
controls how NULL
values in a composite
(multiple-column) foreign key are handled when comparing to
a primary key. Starting from MySQL 3.23.50,
InnoDB
does not check foreign key
constraints on those foreign key or referenced key values
that contain a NULL
column.
InnoDB
essentially implements the
semantics defined by MATCH SIMPLE
, which
allow a foreign key to be all or partially
NULL
. In that case, the (child table) row
containing such a foreign key is allowed to be inserted, and
does not match any row in the referenced (parent) table.
Additionally, MySQL and InnoDB
require
that the referenced columns be indexed for performance.
However, the system does not enforce a requirement that the
referenced columns be UNIQUE
or be
declared NOT NULL
. The handling of
foreign key references to nonunique keys or keys that
contain NULL
values is not well defined
for operations such as UPDATE
or DELETE CASCADE
. You are advised to use
foreign keys that reference only UNIQUE
and NOT NULL
keys.
Furthermore, InnoDB
does not recognize or
support “inline REFERENCES
specifications” (as defined in the SQL standard)
where the references are defined as part of the column
specification. InnoDB
accepts
REFERENCES
clauses only when specified as
part of a separate FOREIGN KEY
specification. For other storage engines, MySQL Server
parses and ignores foreign key specifications.
There is a hard limit of 4096 columns per table, but the effective maximum may be less for a given table and depends on the factors discussed in Section C.3.2, “The Maximum Number of Columns Per Table”.
The table_option
part of the
CREATE TABLE
syntax can be used in
MySQL 3.23 and above. The =
that separates an
option name and its value is optional as of MySQL 4.1.
The ENGINE
and TYPE
options
specify the storage engine for the table.
ENGINE
was added in MySQL 4.0.18 (for 4.0) and
4.1.2 (for 4.1). It is the preferred option name as of those
versions, and TYPE
has become deprecated.
TYPE
is supported throughout the 4.x series,
but likely will be removed in the future.
The ENGINE
and TYPE
table
options take the storage engine names shown in the following
table.
Storage Engine | Description |
ARCHIVE |
The archiving storage engine. See
Section 13.7, “The ARCHIVE Storage Engine”. |
BDB |
Transaction-safe tables with page locking. Also known as
BerkeleyDB . See
Section 13.5, “The BDB (BerkeleyDB ) Storage
Engine”. |
CSV |
Tables that store rows in comma-separated values format. See
Section 13.8, “The CSV Storage Engine”. |
EXAMPLE |
An example engine. See Section 13.6, “The EXAMPLE Storage Engine”. |
HEAP |
The data for this table is stored only in memory. See
Section 13.4, “The MEMORY (HEAP ) Storage Engine”. |
ISAM |
The original MySQL storage engine. See
Section 13.10, “The ISAM Storage Engine”. |
InnoDB |
Transaction-safe tables with row locking and foreign keys. See
Section 13.2, “The InnoDB Storage Engine”. |
MEMORY |
An alias for HEAP . (Actually, as of MySQL 4.1,
MEMORY is the preferred term.) |
MERGE |
A collection of MyISAM tables used as one table. Also
known as MRG_MyISAM . See
Section 13.3, “The MERGE Storage Engine”. |
MyISAM |
The binary portable storage engine that is the improved replacement for
ISAM . See
Section 13.1, “The MyISAM Storage Engine”. |
NDBCLUSTER |
Clustered, fault-tolerant, memory-based tables. Also known as
NDB . See
Chapter 15, MySQL Cluster. |
If a storage engine is specified that is not available, MySQL uses
the default engine instead. Normally, this is
MyISAM
. For example, if a table definition
includes the ENGINE=BDB
option but the MySQL
server does not support BDB
tables, the table
is created as a MyISAM
table. This makes it
possible to have a replication setup where you have transactional
tables on the master but tables created on the slave are
nontransactional (to get more speed). In MySQL 4.1.1, a warning
occurs if the storage engine specification is not honored.
The other table options are used to optimize the behavior of the
table. In most cases, you do not have to specify any of them.
These options apply to all storage engines unless otherwise
indicated. Options that do not apply to a given storage engine may
be accepted and remembered as part of the table definition. Such
options then apply if you later use ALTER
TABLE
to convert the table to use a different storage
engine.
AUTO_INCREMENT
The initial AUTO_INCREMENT
value for the
table. This works for MyISAM
only, for
MEMORY
as of MySQL 4.1.0, and for
InnoDB
as of MySQL 4.1.2. To set the first
auto-increment value for engines that do not support the
AUTO_INCREMENT
table option, insert a
“dummy” row with a value one less than the
desired value after creating the table, and then delete the
dummy row.
For engines that support the AUTO_INCREMENT
table option in CREATE TABLE
statements, you can also use ALTER TABLE
to reset the
tbl_name
AUTO_INCREMENT =
N
AUTO_INCREMENT
value. The value cannot be
set lower than the maximum value currently in the column.
AVG_ROW_LENGTH
An approximation of the average row length for your table. You need to set this only for large tables with variable-size rows.
When you create a MyISAM
table, MySQL uses
the product of the MAX_ROWS
and
AVG_ROW_LENGTH
options to decide how big
the resulting table is. If you do not specify either option,
the maximum size for MyISAM
data and index
files is 4GB. (If your operating system does not support files
that large, table sizes are constrained by the operating
system limit.) If you want to keep down the pointer sizes to
make the index smaller and faster and you do not really need
big files, you can decrease the default pointer size by
setting the
myisam_data_pointer_size
system variable, which was added in MySQL 4.1.2. (See
Section 5.1.3, “Server System Variables”.) If you want all
your tables to be able to grow above the default limit and are
willing to have your tables slightly slower and larger than
necessary, you may increase the default pointer size by
setting this variable. Setting the value to 7 allows table
sizes up to 65,536TB.
[DEFAULT] CHARACTER SET
Specify a default character set for the table.
CHARSET
is a synonym for CHARACTER
SET
. If the character set name is
DEFAULT
, the database character set is
used.
CHECKSUM
Set this to 1 if you want MySQL to maintain a live checksum
for all rows (that is, a checksum that MySQL updates
automatically as the table changes). This makes the table a
little slower to update, but also makes it easier to find
corrupted tables. The CHECKSUM
TABLE
statement reports the checksum.
(MyISAM
only.)
[DEFAULT] COLLATE
Specify a default collation for the table.
COMMENT
A comment for the table, up to 60 characters long.
DATA DIRECTORY
, INDEX
DIRECTORY
By using DATA
DIRECTORY='
or
directory
'INDEX
DIRECTORY='
you
can specify where the directory
'MyISAM
storage engine
should put a table's data file and index file. The directory
must be the full path name to the directory, not a relative
path.
These options work only for MyISAM
tables
from MySQL 4.0 on, when you are not using the
--skip-symbolic-links
option. Your operating system must also have a working,
thread-safe realpath()
call. See
Section 7.6.1.2, “Using Symbolic Links for Tables on Unix”, for more complete
information.
Beginning with MySQL 4.1.24, you cannot use path names that
contain the MySQL data directory with DATA
DIRECTORY
or INDEX DIRECTORY
.
(See Bug#32167.)
DELAY_KEY_WRITE
Set this to 1 if you want to delay key updates for the table
until the table is closed. See the description of the
delay_key_write
system
variable in Section 5.1.3, “Server System Variables”.
(MyISAM
only.)
INSERT_METHOD
If you want to insert data into a MERGE
table, you must specify with INSERT_METHOD
the table into which the row should be inserted.
INSERT_METHOD
is an option useful for
MERGE
tables only. Use a value of
FIRST
or LAST
to have
inserts go to the first or last table, or a value of
NO
to prevent inserts. This option was
introduced in MySQL 4.0.0. See
Section 13.3, “The MERGE
Storage Engine”.
MAX_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.
MIN_ROWS
The minimum number of rows you plan to store in the table. The
MEMORY
storage engine uses this
option as a hint about memory use.
PACK_KEYS
PACK_KEYS
takes effect only with
MyISAM
tables. Set this option to 1 if you
want to have smaller indexes. This usually makes updates
slower and reads faster. Setting the option to 0 disables all
packing of keys. Setting it to DEFAULT
tells the storage engine to pack only long
CHAR
,
VARCHAR
,
BINARY
, or
VARBINARY
columns.
If you do not use PACK_KEYS
, the default is
to pack strings, but not numbers. If you use
PACK_KEYS=1
, numbers are packed as well.
When packing binary number keys, MySQL uses prefix compression:
Every key needs one extra byte to indicate how many bytes of the previous key are the same for the next key.
The pointer to the row is stored in high-byte-first order directly after the key, to improve compression.
This means that if you have many equal keys on two consecutive
rows, all following “same” keys usually only take
two bytes (including the pointer to the row). Compare this to
the ordinary case where the following keys takes
storage_size_for_key + pointer_size
(where
the pointer size is usually 4). Conversely, you get a
significant benefit from prefix compression only if you have
many numbers that are the same. If all keys are totally
different, you use one byte more per key, if the key is not a
key that can have NULL
values. (In this
case, the packed key length is stored in the same byte that is
used to mark if a key is NULL
.)
PASSWORD
This option is unused. If you have a need to scramble your
.frm
files and make them unusable to any
other MySQL server, please contact our sales department.
The RAID_TYPE
option can help you to exceed
the 2GB/4GB limit for the MyISAM
data file
(not the index file) on operating systems that do not support
big files. This option is unnecessary and not recommended for
file systems that support big files.
You can get more speed from the I/O bottleneck by putting
RAID
directories on different physical
disks. The only allowed RAID_TYPE
is
STRIPED
. 1
and
RAID0
are aliases for
STRIPED
.
If you specify the RAID_TYPE
option for a
MyISAM
table, specify the
RAID_CHUNKS
and
RAID_CHUNKSIZE
options as well. The maximum
RAID_CHUNKS
value is 255.
MyISAM
creates
RAID_CHUNKS
subdirectories named
00
, 01
,
02
, ... 09
,
0a
, 0b
, ... in the
database directory. In each of these directories,
MyISAM
creates a file
.
When writing data to the data file, the
tbl_name
.MYDRAID
handler maps the first
RAID_CHUNKSIZE*1024
bytes to the first
file, the next RAID_CHUNKSIZE*1024
bytes to
the next file, and so on.
RAID_TYPE
works on any operating system, as
long as you have built MySQL with the
--with-raid
option to
configure. To determine whether a server
supports RAID
tables, use SHOW
VARIABLES LIKE 'have_raid'
to see whether the
variable value is YES
.
ROW_FORMAT
Defines how the rows should be stored. Currently, this option
works only with MyISAM
tables. The option
value can be FIXED
or
DYNAMIC
for static or variable-length row
format. myisampack sets the type to
COMPRESSED
. See
Section 13.1.3, “MyISAM
Table Storage Formats”.
When executing a CREATE TABLE
statement, if you specify a row format which is not
supported by the storage engine that is used for the table,
the table is created using that storage engine's
default row format. The information reported in this column
in response to SHOW TABLE
STATUS
is the actual row format used. This may
differ from the value in the
Create_options
column because the
original CREATE TABLE
definition is retained during creation.
UNION
is used when you want to
access a collection of identical MyISAM
tables as one. This works only with MERGE
tables. See Section 13.3, “The MERGE
Storage Engine”.
In MySQL 4.1, you must have
SELECT
,
UPDATE
, and
DELETE
privileges for the
tables you map to a MERGE
table.
Originally, all tables used had to be in the same database
as the MERGE
table itself. This
restriction has been lifted as of MySQL 4.1.1.
The original CREATE TABLE
statement, including all specifications and table options are
stored by MySQL when the table is created. The information is
retained so that if you change storage engines, collations or
other settings using an ALTER
TABLE
statement, the original table options specified
are retained. This allows you to change between
InnoDB
and MyISAM
table
types even though the row formats supported by the two engines
are different.
Because the text of the original statement is retained, but due
to the way that certain values and options may be silently
reconfigured (such as the ROW_FORMAT
), the
active table definition (accessible through
DESCRIBE
or with
SHOW TABLE STATUS
) and the table
creation string (accessible through SHOW
CREATE TABLE
) will report different values.
As of MySQL 3.23, you can create one table from another by adding
a SELECT
statement at the end of
the CREATE TABLE
statement:
CREATE TABLEnew_tbl
SELECT * FROMorig_tbl
;
MySQL creates new columns for all elements in the
SELECT
. For example:
mysql>CREATE TABLE test (a INT NOT NULL AUTO_INCREMENT,
->PRIMARY KEY (a), KEY(b))
->TYPE=MyISAM SELECT b,c FROM test2;
This creates a MyISAM
table with three columns,
a
, b
, and
c
. Notice that the columns from the
SELECT
statement are appended to
the right side of the table, not overlapped onto it. Take the
following example:
mysql>SELECT * FROM foo;
+---+ | n | +---+ | 1 | +---+ mysql>CREATE TABLE bar (m INT) SELECT n FROM foo;
Query OK, 1 row affected (0.02 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql>SELECT * FROM bar;
+------+---+ | m | n | +------+---+ | NULL | 1 | +------+---+ 1 row in set (0.00 sec)
For each row in table foo
, a row is inserted in
bar
with the values from foo
and default values for the new columns.
In a table resulting from
CREATE TABLE ...
SELECT
, columns named only in the
CREATE TABLE
part come first.
Columns named in both parts or only in the
SELECT
part come after that. The
data type of SELECT
columns can be
overridden by also specifying the column in the
CREATE TABLE
part.
If any errors occur while copying the data to the table, it is automatically dropped and not created.
CREATE TABLE ...
SELECT
does not automatically create any indexes for
you. This is done intentionally to make the statement as flexible
as possible. If you want to have indexes in the created table, you
should specify these before the
SELECT
statement:
mysql> CREATE TABLE bar (UNIQUE (n)) SELECT n FROM foo;
Some conversion of data types might occur. For example, the
AUTO_INCREMENT
attribute is not preserved, and
VARCHAR
columns can become
CHAR
columns. Retrained attributes
are NULL
(or NOT NULL
) and,
for those columns that have them, CHARACTER
SET
, COLLATION
,
COMMENT
, and the DEFAULT
clause.
When creating a table with CREATE ... SELECT
,
make sure to alias any function calls or expressions in the query.
If you do not, the CREATE
statement might fail
or result in undesirable column names.
CREATE TABLE artists_and_works SELECT artist.name, COUNT(work.artist_id) AS number_of_works FROM artist LEFT JOIN work ON artist.id = work.artist_id GROUP BY artist.id;
As of MySQL 4.1, you can explicitly specify the data type for a generated column:
CREATE TABLE foo (a TINYINT NOT NULL) SELECT b+1 AS a FROM bar;
For CREATE TABLE ...
SELECT
, if IF NOT EXISTS
is given and
the table already exists, MySQL handles the statement as follows:
The table definition given in the CREATE
TABLE
part is ignored. No error occurs, even if the
definition does not match that of the existing table.
If there is a mismatch between the number of columns in the
table and the number of columns produced by the
SELECT
part, the selected
values are assigned to the rightmost columns. For example, if
the table contains n
columns and
the SELECT
produces
m
columns, where
m
<
n
, the selected values are assigned
to the m
rightmost columns in the
table. Each of the initial n
– m
columns is assigned its
default value, either that specified explicitly in the column
definition or the implicit column data type default if the
definition contains no default. If the
SELECT
part produces too many
columns (m
>
n
), an error occurs.
The following example illustrates IF NOT EXISTS
handling:
mysql>CREATE TABLE t1 (i1 INT DEFAULT 0, i2 INT, i3 INT, i4 INT);
Query OK, 0 rows affected (0.05 sec) mysql>CREATE TABLE IF NOT EXISTS t1 (c1 CHAR(10)) SELECT 1, 2;
Query OK, 1 row affected, 1 warning (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql>SELECT * FROM t1;
+------+------+------+------+ | i1 | i2 | i3 | i4 | +------+------+------+------+ | 0 | NULL | 1 | 2 | +------+------+------+------+ 1 row in set (0.00 sec)
In MySQL 4.1, you can also use LIKE
to create
an empty table based on the definition of another table, including
any column attributes and indexes the original table has:
CREATE TABLEnew_tbl
LIKEorig_tbl
;
The copy is created using the same version of the table storage format as the original table.
CREATE TABLE ... LIKE
does not preserve any
DATA DIRECTORY
or INDEX
DIRECTORY
table options that were specified for the
original table, or any foreign key definitions.
If the original table is a TEMPORARY
table,
CREATE TABLE ... LIKE
does not preserve
TEMPORARY
. To create a
TEMPORARY
destination table, use
CREATE TEMPORARY TABLE ... LIKE
.
You can precede the SELECT
by
IGNORE
or
REPLACE
to indicate how to handle
rows that duplicate unique key values. With
IGNORE
, new rows that duplicate an existing row
on a unique key value are discarded. With
REPLACE
, new rows replace rows that
have the same unique key value. If neither
IGNORE
nor
REPLACE
is specified, duplicate
unique key values result in an error.
To ensure that the update log or binary log can be used to
re-create the original tables, MySQL does not allow concurrent
inserts for CREATE
TABLE ... SELECT
statements.
User Comments
For 3.23.58 using InnoDB, I discovered that if you have a unique index key with multiple optional columns, then it does not apply a unique constraint at all if ANY of your data values for the columns are null. Thus, it will duplicate any data that has any nulls in any of the key columns.
If none of your column values are null, then it applies the unique constraint.
This was unexpected, because I remember Oracle applying the unique constraint on the remaining non-null values. It would be nice if MySQL could do this as well so we can guarantee that a unique key will not permit duplicates.
just found a possibility to wotrk around the limitations of not reopening temp tables (works in 4.1.10 , but wouldnt bet on its future);
create temporary table tmp1 (...);
create temporary table tmp2 (...) enginme merge union (tmp1);
# this will only work is the merge table is temporary itself
add as many mrg tables as you need, use the merge tables instead of reopening the tmp (its still the same table :-) )
they are all temporary, so no clean up
If you want to the flexibility to drop or modify a foreign key (and, to change properties, you must drop & re-add the new version), you must create the foreign key with an otherwise optional 'symbol' name. You can verify this at the 'alter table syntax' page.
I just found a work around for the limitation of not reopening temp tables (works in 4.1.10 , but wouldn't bet on it in the future);
create temporary table tmp1 (...);
create temporary table tmp2 (...) engine merge union (tmp1);
this will only work if the merge table is temporary itself
add as many merge tables as you need, use the merge tables instead of reopening the tmp (it's still the same table :-) )
they are all temporary, so no clean up necessary
Create table with constraints. If you happen to run into "can't create table errno 121" with Error Code 1005, most likely your foreign key name is used by another table's foreign key.
CREATE TABLE IF NOT EXISTS `schema`.`Employee` (
`idEmployee` VARCHAR(45) NOT NULL ,
`Name` VARCHAR(255) NULL ,
`idAddresses` VARCHAR(45) NULL ,
PRIMARY KEY (`idEmployee`) ,
CONSTRAINT `fkEmployee_Addresses`
FOREIGN KEY `fkEmployee_Addresses` (`idAddresses`)
REFERENCES `schema`.`Addresses` (`idAddresses`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin
Add your own comment.