|
 |
|
| |
13.1.5. CREATE TABLE SyntaxCREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options] [select_statement]
Or:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(] LIKE old_tbl_name [)];
create_definition:
column_definition
| [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
| KEY [index_name] [index_type] (index_col_name,...)
| INDEX [index_name] [index_type] (index_col_name,...)
| [CONSTRAINT [symbol]] UNIQUE [INDEX]
[index_name] [index_type] (index_col_name,...)
| FULLTEXT [INDEX] [index_name] (index_col_name,...)
[WITH PARSER parser_name]
| SPATIAL [INDEX] [index_name] (index_col_name,...)
| [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name,...) [reference_definition]
| CHECK (expr)
column_definition:
col_name type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT 'string'] [reference_definition]
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) [BINARY | ASCII | UNICODE]
| VARCHAR(length) [BINARY]
| BINARY(length)
| VARBINARY(length)
| TINYBLOB
| BLOB
| MEDIUMBLOB
| LONGBLOB
| TINYTEXT [BINARY]
| TEXT [BINARY]
| MEDIUMTEXT [BINARY]
| LONGTEXT [BINARY]
| ENUM(value1,value2,value3,...)
| SET(value1,value2,value3,...)
| spatial_type
index_col_name:
col_name [(length)] [ASC | DESC]
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 [COLLATE collation_name]
| CHECKSUM [=] {0 | 1}
| COMMENT [=] 'string'
| CONNECTION [=] 'connect_string'
| MAX_ROWS [=] value
| MIN_ROWS [=] value
| PACK_KEYS [=] {0 | 1 | DEFAULT}
| PASSWORD [=] 'string'
| DELAY_KEY_WRITE [=] {0 | 1}
| ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
| UNION [=] (tbl_name[,tbl_name]...)
| INSERT_METHOD [=] { NO | FIRST | LAST }
| DATA DIRECTORY [=] 'absolute path to directory'
| INDEX DIRECTORY [=] 'absolute path to directory'
partition_options:
PARTITION BY
[LINEAR] HASH(expr)
| [LINEAR] KEY(column_list)
| RANGE(expr)
| LIST(expr)
[PARTITIONS num]
[ SUBPARTITION BY
[LINEAR] HASH(expr)
| [LINEAR] KEY(column_list)
[SUBPARTITIONS num]
]
[(partition_definition) [, (partition_definition)] ...]
partition_definition:
PARTITION partition_name
[VALUES {LESS THAN (expr) | MAXVALUE | IN (value_list)}]
[[STORAGE] ENGINE [=] engine-name]
[COMMENT [=] 'comment_text' ]
[DATA DIRECTORY [=] 'data_dirindex_dirmax_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_dirindex_dirmax_number_of_rows]
[MIN_ROWS [=] min_number_of_rows]
[TABLESPACE [=] (tablespace_name)]
[NODEGROUP [=] node_group_id]
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 9.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.
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, `mydb`.`mytbl` is
legal, but `mydb.mytbl` is not.
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.)
To create temporary tables, you must have the CREATE
TEMPORARY TABLES privilege.
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. Note: If you use IF NOT
EXISTS in a CREATE TABLE ... SELECT
statement, any rows selected by the SELECT
part are inserted regardless of whether the table already
exists.
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:
Chapter 14, Storage Engines and Table Types, describes what files each
storage engine creates to represent tables.
type represents the data type is a
column definition. spatial_type represents a
spatial data type. For general information on the properties of
data types other than the spatial types, see
Chapter 11, Data Types. For information about spatial data
types, see Chapter 18, Spatial Extensions.
If neither NULL nor NOT
NULL is specified, the column is treated as though
NULL had been specified.
-
An integer 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
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 12.10.3, “Information Functions”,
and Section 25.2.3.36, “mysql_insert_id()”.
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.2.5, “The Server SQL Mode”.
Note: 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. 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 * FROM tbl_name WHERE auto_col IS NULL
The attribute SERIAL can be used as an
alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
UNIQUE.
-
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
Chapter 10, Character Set Support. CHARSET is a
synonym for CHARACTER SET. Example:
CREATE TABLE t (c CHAR(20) CHARACTER SET utf8 COLLATE utf8_bin);
MySQL 5.1 interprets length specifications in
character column definitions in characters. (Versions before
MySQL 4.1 interpreted them in bytes.)
-
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. See
Section 11.3.1.1, “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 11.1.4, “Data Type Default Values”.
BLOB and TEXT columns
cannot be assigned a default value.
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.
KEY is normally a synonym for
INDEX. 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 that matches
an existing row. The exception to this is that if a column
in the index is allowed to contain NULL
values, it can contain multiple NULL
values. This exception does not apply to
BDB tables, for which a column with a
UNIQUE index allows only a single
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 the created table, a PRIMARY KEY is
placed first, followed by all UNIQUE
indexes, and then the non-unique 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(index_col_name, ...) clause.
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.
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
tbl_name. See
Section 13.5.4.15, “SHOW INDEX Syntax”.
-
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 13.1.4, “CREATE INDEX Syntax”.
For more information about how MySQL uses indexes, see
Section 7.4.5, “How MySQL Uses Indexes”.
In MySQL 5.1, only the
MyISAM, InnoDB,
BDB, and 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.
-
With
col_name(length)length characters of
each column value for CHAR and
VARCHAR columns, and the first
length bytes of each column value
for BINARY and
VARBINARY columns. Indexing only a prefix
of column values like this can make the index file much
smaller. See Section 7.4.3, “Column Indexes”.
The MyISAM, BDB, and
InnoDB storage engines support indexing
on BLOB and TEXT
columns. When indexing a BLOB or
TEXT column, you
must specify a prefix length for the
index. 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). Note that prefix limits
are measured in bytes, whereas the prefix length in
CREATE TABLE statements is interpreted as
number of characters for non-binary 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 11.4.3, “The BLOB and TEXT Types”.
You can create special FULLTEXT indexes,
which 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;
partial indexing is not supported and any prefix length is
ignored if specified. See Section 12.7, “Full-Text Search Functions”,
for details of operation. A WITH PARSER
clause can be specified to associate a parser plugin with
the index if full-text indexing and searching operations
need special handling. This clause is legal only for
FULLTEXT indexes. See
Section 27.2, “The MySQL Plugin Interface”, for details on creating
plugins.
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 18, Spatial Extensions.
-
InnoDB tables support checking of foreign
key constraints. See Section 14.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. For the precise syntax, see
Section 14.2.6.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 MyISAM tables, each
NULL column takes one bit extra, rounded
up to the nearest byte. The maximum row length in bytes can
be calculated as follows:
row length = 1
+ (sum of column lengths)
+ (number of NULL columns + delete_flag + 7)/8
+ (number of variable-length columns)
delete_flag is 1 for tables with
static row format. Static tables use a bit in the row record
for a flag that indicates whether the row has been deleted.
delete_flag is 0 for dynamic
tables because the flag is stored in the dynamic row header.
These calculations do not apply for
InnoDB tables, for which storage size is
no different for NULL columns than for
NOT NULL columns.
The ENGINE table option specifies the storage
engine for the table.
The ENGINE table option takes the storage
engine names shown in the following table.
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 non-transactional (to get more speed). In MySQL
5.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:
-
AUTO_INCREMENT
The initial AUTO_INCREMENT value for the
table. In MySQL 5.1, this works for
MyISAM, MEMORY, and
InnoDB tables. 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 tbl_name
AUTO_INCREMENT = N to
reset the AUTO_INCREMENT value.
-
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 don't specify either option,
the maximum size for a table is 65,536TB of data. (If your
operating system does not support files that large, table
sizes are constrained by the file size limit.) If you want
to keep down the pointer sizes to make the index smaller and
faster and you don't really need big files, you can decrease
the default pointer size by setting the
myisam_data_pointer_size system variable.
(See Section 5.2.2, “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 can increase the default pointer
size by setting this variable.
-
[DEFAULT] CHARACTER SET
Specify a default character set for the table.
CHARSET is a synonym for
CHARACTER SET.
-
COLLATE
Specify a default collation for the table.
-
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.)
-
COMMENT
A comment for the table, up to 60 characters long.
-
CONNECTION
The connection string for a FEDERATED
table. (Note: Older
versions of MySQL used a COMMENT option
for the connection string.)
-
MAX_ROWS
The maximum number of rows you plan to store in the table.
This is not a hard limit, but rather an indicator 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.
-
PACK_KEYS
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 or
VARCHAR columns.
(MyISAM only.)
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
Encrypt the .frm file with a password.
This option does nothing in the standard MySQL version.
-
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.2.2, “Server System Variables”.
(MyISAM only.)
-
ROW_FORMAT
Defines how the rows should be stored. For
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 14.1.3, “MyISAM Table Storage Formats”.
For InnoDB tables, rows are stored in
compact format (ROW_FORMAT=COMPACT) by
default. The non-compact format used in older versions of
MySQL can still be requested by specifying
ROW_FORMAT=REDUNDANT.
-
RAID_TYPE
RAID support has been removed as of MySQL
5.0. For information on RAID, see
https://dev.mysql.com/doc/refman/4.1/en/create-table.html.
-
UNION
UNION is used when you want to access a
collection of identical MyISAM tables as
one. This works only with MERGE tables.
See Section 14.3, “The MERGE Storage Engine”.
You must have SELECT,
UPDATE, and DELETE
privileges for the tables you map to a
MERGE table. (Note:
Formerly, all tables used had to be in the same database as
the MERGE table itself. This restriction
no longer applies.)
-
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. See Section 14.3, “The MERGE Storage Engine”.
-
DATA DIRECTORY, INDEX
DIRECTORY
By using DATA
DIRECTORY='directory'
or INDEX
DIRECTORY='directory'
you can specify where the MyISAM storage
engine should put a table's data file and index file. The
directory must be the full pathname to the directory, not a
relative path.
These options work only 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.
-
partition_options can be used to
control partitioning of the table created with
CREATE TABLE, and if used, must contain
at a minimum a PARTITION BY clause. This
clause contains the function that is used to determine the
partition; the function returns an integer value ranging
from 1 to num, where
num is the number of partitions.
The choices that are available for this function in MySQL
5.1 are shown in the following list.
Important: Not all options
shown in the syntax for
partition_options at the
beginning of this section are available for all partitioning
types. Please see the listings for the following individual
types for information specific to each type, and see
Chapter 17, Partitioning, for more complete
information about the workings of and uses for partitioning
in MySQL, as well as additional examples of table creation
and other statements relating to MySQL partitioning.
-
HASH(expr):
Hashes one or more columns to create a key for placing
and locating rows. expr is an
expression using one or more table columns. This can be
any legal MySQL expression (including MySQL functions)
that yields a single integer value. For example, these
are all valid CREATE TABLE statements
using PARTITION BY HASH:
CREATE TABLE t1 (col1 INT, col2 CHAR(5))
PARTITION BY HASH(col1);
CREATE TABLE t1 (col1 INT, col2 CHAR(5))
PARTITION BY HASH( ORD(col2) );
CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATETIME)
PARTITION BY HASH ( YEAR(col3) );
You may not use either VALUES LESS
THAN or VALUES IN clauses
with PARTITION BY HASH.
PARTITION BY HASH uses the remainder
of expr divided by the number
of partitions (that is, the modulus). For examples and
additional information, see
Section 17.2.3, “HASH Partitioning”.
The LINEAR keyword entails a somewhat
different algorithm. In this case, the number of the
partition in which a row is stored is calculated as the
result of one or more logical AND
operations. For discussion and examples of linear
hashing, see Section 17.2.3.1, “LINEAR HASH Partitioning”.
-
KEY(column_list):
This is similar to HASH, except that
MySQL supplies the hashing function so as to guarantee
an even data distribution. The
column_list argument is
simply a list of table columns. This example shows a
simple table partitioned by key, with 4 partitions:
CREATE TABLE tk (col1 INT, col2 CHAR(5), col3 DATE)
PARTITION BY KEY(col3)
PARTITIONS 4;
For tables that are partitioned by key, you can employ
linear partitioning by using the
LINEAR keyword. This has the same
effect as with tables that are partitioned by
HASH. That is, the partition number
is found using the & operator
rather than the modulus (see
Section 17.2.3.1, “LINEAR HASH Partitioning”, and
Section 17.2.4, “KEY Partitioning”, for details). This
example uses linear partitioning by key to distribute
data between 5 partitions:
CREATE TABLE tk (col1 INT, col2 CHAR(5), col3 DATE)
PARTITION BY LINEAR KEY(col3)
PARTITIONS 5;
You may not use either VALUES LESS
THAN or VALUES IN clauses
with PARTITION BY KEY.
-
RANGE: In this case,
expr shows a range of values
using a set of VALUES LESS THAN
operators. When using range partitioning, you must
define at least one partition using VALUES LESS
THAN. You cannot use VALUES
IN with range partitioning.
VALUES LESS THAN can be used with
either a literal value or an expression that evaluates
to a single value.
Suppose that you have a table that you wish to partition
on a column containing year values, according to the
following scheme:
A table implementing such a partitioning scheme can be
realized by the CREATE TABLE
statement shown here:
CREATE TABLE t1 (
year_col INT,
some_data INT
)
PARTITION BY RANGE (year_col) (
PARTITION p0 VALUES LESS THAN (1991),
PARTITION p1 VALUES LESS THAN (1995),
PARTITION p2 VALUES LESS THAN (1999),
PARTITION p3 VALUES LESS THAN (2002),
PARTITION p4 VALUES LESS THAN (2006),
PARTITION p5 VALUES LESS THAN MAXVALUE
);
PARTITION ... VALUES LESS THAN ...
statements work in a consecutive fashion.
VALUES LESS THAN MAXVALUE works to
specify “leftover” values that are greater
than the maximum value otherwise specified.
Note that VALUES LESS THAN clauses
work sequentially in a manner similar to that of the
case portions of a switch
... case block (as found in many programming
languages such as C, Java, and PHP). That is, the
clauses must be arranged in such a way that the upper
limit specified in each successive VALUES LESS
THAN is greater than that of the previous one,
with the one referencing MAXVALUE
coming last of all in the list.
-
LIST(expr):
This is useful when assigning partitions based on a
table column with a restricted set of possible values,
such as a state or country code. In such a case, all
rows pertaining to a certain state or country can be
assigned to a single partition, or a partition can be
reserved for a certain set of states or countries. It is
similar to RANGE, except that only
VALUES IN may be used to specify
allowable values for each partition.
VALUES IN is used with a list of
values to be matched. For instance, you could create a
partitioning scheme such as the following:
CREATE TABLE client_firms (
id INT,
name VARCHAR(35)
)
PARTITION BY LIST (id) (
PARTITION r0 VALUES IN (1, 5, 9, 13, 17, 21),
PARTITION r1 VALUES IN (2, 6, 10, 14, 18, 22),
PARTITION r2 VALUES IN (3, 7, 11, 15, 19, 23),
PARTITION r3 VALUES IN (4, 8, 12, 16, 20, 24)
);
When using list partitioning, you must define at least
one partition using VALUES IN. You
cannot use VALUES LESS THAN with
PARTITION BY LIST.
Note: Currently, the
value list used with VALUES IN must
consist of integer values only.
-
The number of partitions may optionally be specified
with a PARTITIONS
num clause, where
num is the number of
partitions. If both this clause and
any PARTITION clauses are used,
num must be equal to the
total number of any partitions that are declared using
PARTITION clauses.
Note: Whether or not
you use a PARTITIONS clause in
creating a table that is partitioned by
RANGE or LIST, you
must still include at least one PARTITION
VALUES clause in the table definition (see
below).
-
A partition may optionally be divided into a number of
subpartitions. This can be indicated by using the
optional SUBPARTITION BY clause.
Subpartitioning may be done by HASH
or KEY. Either of these may be
LINEAR. These work in the same way as
previously described for the equivalent partitioning
types. (It is not possible to subpartition by
LIST or RANGE.)
The number of subpartitions can be indicated using the
SUBPARTITIONS keyword followed by an
integer value.
Each partition may be individually defined using a
partition_definition clause. The
individual parts making up this clause are as follows:
PARTITION
partition_name:
This specifies a logical name for the partition.
A VALUES clause: For range
partitioning, each partition must include a
VALUES LESS THAN clause; for list
partitioning, you must specify a VALUES
IN clause for each partition. This is used to
determine which rows are to be stored in this partition.
See the discussions of partitioning types in
Chapter 17, Partitioning, for syntax examples.
-
An optional COMMENT clause may be
used to describe the partition. The comment must be set
off in single quotes. Example:
COMMENT = 'Data for the years previous to 1999'
-
DATA DIRECTORY and INDEX
DIRECTORY may be used to indicate the
directory where, respectively, the data and indexes for
this partition are to be stored. Both the
data_dirindex_dir
CREATE TABLE th (id INT, name VARCHAR(30), adate DATE)
PARTITION BY LIST(YEAR(adate))
(
PARTITION p1999 VALUES IN (1995, 1999, 2003)
DATA DIRECTORY = '/var/appdata/95/data'
INDEX DIRECTORY = '/var/appdata/95/idx',
PARTITION p2000 VALUES IN (1996, 2000, 2004)
DATA DIRECTORY = '/var/appdata/96/data'
INDEX DIRECTORY = '/var/appdata/96/idx',
PARTITION p2001 VALUES IN (1997, 2001, 2005)
DATA DIRECTORY = '/var/appdata/97/data'
INDEX DIRECTORY = '/var/appdata/97/idx',
PARTITION p2000 VALUES IN (1998, 2002, 2006)
DATA DIRECTORY = '/var/appdata/98/data'
INDEX DIRECTORY = '/var/appdata/98/idx'
);
DATA DIRECTORY and INDEX
DIRECTORY behave in the same way as in the
CREATE TABLE statement's
table_option clause as used
for MyISAM tables.
One data directory and one index directory may be
specified per partition. If left unspecified, the data
and indexes are stored by default in the MySQL data
directory.
MAX_ROWS and
MIN_ROWS may be used to specify,
respectively, the maximum and minimum number of rows to
be stored in the partition. The values for
max_number_of_rows and
min_number_of_rows must be
positive integers. As with the table-level options with
the same names, these act only as
“suggestions” to the server and are not
hard limits.
The optional TABLESPACE clause may be
used to designate a tablespace for the partition. Used
for MySQL Cluster only.
-
The optional [STORAGE] ENGINE clause
causes the tables in this partition to use the storage
engine specified, which may be any engine supported by
this MySQL server. Both the STORAGE
keyword and the equals sign (=) are
optional. If no partition-specific storage engine is set
using this option, the engine applying to the table as a
whole is used for this partition.
Note: The partitioning
handler accepts a [STORAGE] ENGINE
option for both PARTITION and
SUBPARTITION. Currently, the only way
in which this can be used is to set all partitions or
all subpartitions to the same sorage engine, and an
attempt to set different storage engines for partitions
or ubpartitions in the same table will give rise to the
error ERROR 1469 (HY000): The mix of handlers
in the partitions is not allowed in this version of
MySQL. We expect to lift this restriction on
partitioning in a future MySQL 5.1 release.
The NODEGROUP option can be used to
make this partition act as part of the node group
identified by node_group_id.
This option is applicable only to MySQL Cluster.
-
The partition definition may optionally contain one or
more subpartition_definition
clauses. Each of these consists at a minimum of the
SUBPARTITION
name, where
name is an identifier for the
subpartition. Except for the replacement of the
PARTITION keyword with
SUBPARTITION, the syntax for a
subpartition definition is identical to that for a
partition definition.
Subpartitioning must be done by HASH
or KEY, and can be done only on
RANGE or LIST
partitions. See
Section 17.2.5, “Subpartitioning”.
Partitions can be modified, merged, added to tables, and
dropped from tables. For basic information about the MySQL
statements to accomplish these tasks, see
Section 13.1.2, “ALTER TABLE Syntax”. For more detailed
descriptions and examples, see
Section 17.3, “Partition Management”.
You can create one table from another by adding a
SELECT statement at the end of the
CREATE TABLE statement:
CREATE TABLE new_tbl SELECT * FROM orig_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))
-> ENGINE=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.
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;
You can also explicitly specify the type for a generated column:
CREATE TABLE foo (a TINYINT NOT NULL) SELECT b+1 AS a FROM bar;
Use LIKE to create an empty table based on
the definition of another table, including any column attributes
and indexes defined in the original table:
CREATE TABLE new_tbl LIKE orig_tbl;
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.
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 binary log can be used to re-create the
original tables, MySQL does not allow concurrent inserts during
CREATE TABLE ... SELECT.
|
|
|
