![]() |
|
[ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] | [Top] | [Contents] | [Index] | [ ? ] |
CREATE
, DROP
, ALTER
CREATE DATABASE
Syntax CREATE DATABASE [IF NOT EXISTS] db_name |
CREATE DATABASE
creates a database with the given name.
Rules for
allowable database names are given in Database, Table, Index, Column, and Alias Names. An error occurs if
the database already exists and you didn't specify IF NOT EXISTS
.
Databases in MySQL are implemented as directories containing files
that correspond to tables in the database. Because there are no tables in a
database when it is initially created, the CREATE DATABASE
statement
only creates a directory under the MySQL data directory.
You can also create databases with mysqladmin
.
See section MySQL Client-Side Scripts and Utilities.
DROP DATABASE
Syntax DROP DATABASE [IF EXISTS] db_name |
DROP DATABASE
drops all tables in the database and deletes the
database. If you do a DROP DATABASE
on a symbolic linked
database, both the link and the original database is deleted. Be
VERY careful with this command!
DROP DATABASE
returns the number of files that were removed from
the database directory. Normally, this is three times the number of
tables, because normally each table corresponds to a `.MYD' file, a
`.MYI' file, and a `.frm' file.
The DROP DATABASE
command removes from the given database
directory all files with the following extensions:
Ext | Ext | Ext | Ext |
.BAK | .DAT | .HSH | .ISD |
.ISM | .ISM | .MRG | .MYD |
.MYI | .db | .frm |
All subdirectories that consists of 2 digits (RAID
directories)
are also removed.
In MySQL Version 3.22 or later, you can use the keywords
IF EXISTS
to prevent an error from occurring if the database doesn't
exist.
You can also drop databases with mysqladmin
. See section MySQL Client-Side Scripts and Utilities.
CREATE TABLE
Syntax 6.5.3.1 Silent Column Specification Changes |
CREATE [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: col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [[PRIMARY] KEY] [COMMENT 'string'] [reference_definition] | PRIMARY KEY (index_col_name,...) | KEY [index_name] (index_col_name,...) | INDEX [index_name] (index_col_name,...) | UNIQUE [INDEX] [index_name] (index_col_name,...) | FULLTEXT [INDEX] [index_name] (index_col_name,...) | [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name,...) [reference_definition] | CHECK (expr) 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] | CHAR(length) [BINARY] | VARCHAR(length) [BINARY] | DATE | TIME | TIMESTAMP | DATETIME | TINYBLOB | BLOB | MEDIUMBLOB | LONGBLOB | TINYTEXT | TEXT | MEDIUMTEXT | LONGTEXT | ENUM(value1,value2,value3,...) | SET(value1,value2,value3,...) index_col_name: col_name [(length)] reference_definition: REFERENCES tbl_name [(index_col_name,...)] [MATCH FULL | MATCH PARTIAL] [ON DELETE reference_option] [ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT table_options: table_option [table_option] ... table_option: TYPE = {BDB | HEAP | ISAM | InnoDB | MERGE | MRG_MYISAM | MYISAM } | AUTO_INCREMENT = # | AVG_ROW_LENGTH = # | CHECKSUM = {0 | 1} | COMMENT = 'string' | MAX_ROWS = # | MIN_ROWS = # | PACK_KEYS = {0 | 1 | DEFAULT} | PASSWORD = 'string' | DELAY_KEY_WRITE = {0 | 1} | ROW_FORMAT = { default | dynamic | fixed | compressed } | RAID_TYPE = { 1 | STRIPED | RAID0 } RAID_CHUNKS=# RAID_CHUNKSIZE=# | UNION = (table_name,[table_name...]) | INSERT_METHOD = { NO | FIRST | LAST } | DATA DIRECTORY = 'absolute path to directory' | INDEX DIRECTORY = 'absolute path to directory' select_statement: [IGNORE | REPLACE] SELECT ... (Some legal select statement) |
CREATE TABLE
creates a table with the given name.
Rules for allowable table names are given in Database, Table, Index, Column, and Alias Names.
By default, the table is created in the current database.
An error occurs if the table already exists, if there is no current database,
or if the database does not exist.
In MySQL Version 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 current database.
From MySQL Version 3.23, you can use the TEMPORARY
keyword when
you create a table. The temporary table is visible only to the
current connection, and will be deleted automatically when the
connection is closed. This means that two different
connections can both use the same temporary table name without conflicting
with each other or with an existing table of the same name. (The existing table
is hidden until the temporary table is deleted.) From MySQL 4.0.2 on, you must
have the CREATE TEMPORARY TABLES
privilege to be able to create
temporary tables.
In MySQL Version 3.23 or later, you can use the keywords
IF NOT EXISTS
so that an error does not occur if the table already
exists. Note that there is no verification that the existing table has a
structure identical to that indicated by the CREATE TABLE
statement.
From version 4.1.0, the attribute SERIAL
can be used as an alias for
BIGINT NOT NULL AUTO_INCREMENT UNIQUE
. This is compatibility feature.
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 TABLE new_tbl SELECT * FROM orig_tbl; |
Indexes are not carried over to the new table, and some conversion of column
types may occur. For example, the AUTO_INCREMENT
attribute is not
preserved, and VARCHAR
columns may become CHAR
columns.
As of MySQL 4.1, you can explicitly specify the type for a generated column:
CREATE TABLE foo (a tinyint not null) SELECT b+1 AS 'a' FROM bar; |
In MySQL 4.1, you can also use LIKE
to create a table based on the
definition of another table, including any column attributes and
indexes the original table has:
CREATE TABLE new_tbl LIKE orig_tbl; |
Each table tbl_name
is represented by some files in the database
directory. In the case of MyISAM
-type tables you will get:
File | Purpose |
| Table format (definition) file |
| Datafile |
| Index file |
For more information on the properties of the various column types, see Column Types:
NULL
nor NOT NULL
is specified, the column
is treated as though NULL
had been specified.
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+1
, where
value
is the largest value for the column currently in the table.
AUTO_INCREMENT
sequences begin with 1
.
See section mysql_insert_id()
.
If you delete the row containing the maximum value for an
AUTO_INCREMENT
column, the value will be reused for an
ISAM
or BDB
table, but not for a
MyISAM
or InnoDB
table. If you delete all rows in the table
with DELETE FROM table_name
(without a WHERE
) in
AUTOCOMMIT
mode, the sequence starts over for all table types except
InnoDB
. See section How an AUTO_INCREMENT
Column Works in InnoDB.
Note: there can be only one AUTO_INCREMENT
column per
table, it must be indexed and it can't have a DEFAULT
value.
In MySQL Version 3.23, an AUTO_INCREMENT
column will work 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 one doesn't accidentally
get an AUTO_INCREMENT
column that contains 0.
In MyISAM
and BDB
tables you can specify AUTO_INCREMENT
secondary column in a multiple-column key. See section 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 |
CREATE TABLE
implicitly commits the current InnoDB
transaction if MySQL binary logging is used.
NULL
values are handled differently for TIMESTAMP
columns than
for other column types. 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 may be
assigned NULL
values (which is true), even though TIMESTAMP
never actually will contain a NULL
value. You can see this when you
use DESCRIBE tbl_name
to get a description of your table.
Note that setting a TIMESTAMP
column to 0
is not the same
as setting it to NULL
, because 0
is a valid TIMESTAMP
value.
DEFAULT
value has to be a constant, it cannot be a function or
an expression.
If no DEFAULT
value is specified for a column, MySQL
automatically assigns one, as follows.
If the column may take NULL
as a value, the default value is
NULL
.
If the column is declared as NOT NULL
, the default value depends on
the column type:
AUTO_INCREMENT
attribute, the default is 0
. For an AUTO_INCREMENT
column, the
default value is the next value in the sequence.
TIMESTAMP
, the default is the
appropriate zero value for the type. For the first TIMESTAMP
column in a table, the default value is the current date and time.
See section Date and Time Types.
ENUM
, the default value is the empty
string. For ENUM
, the default is the first enumeration value.
Default values must be constants. 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
.
COMMENT
option.
The comment is displayed by the
SHOW CREATE TABLE
statement, and by SHOW FULL COLUMNS
.
This option is available as of MySQL 4.1.
(It is allowed but ignored in earlier versions.)
KEY
is normally a synonym for INDEX
.
From version 4.1, the key attribute PRIMARY KEY
may also be
specified as just KEY
. This was implemented for compatibility
with other databases.
UNIQUE
key can have only distinct values. An
error occurs if you try to add a new row with a key that matches an existing
row.
PRIMARY KEY
is a unique KEY
where all key columns must be
defined as NOT NULL
. If they are not explicitly declared as
NOT NULL
, it will be done implicitly (and quietly). In MySQL
the key is named PRIMARY
. A table can have only one PRIMARY KEY
.
If you don't have a PRIMARY KEY
and some applications ask for the
PRIMARY KEY
in your tables, MySQL will return the first
UNIQUE
key, which doesn't have any NULL
columns, as the
PRIMARY KEY
.
PRIMARY KEY
can be a multiple-column index. However, you cannot
create a multiple-column index using the PRIMARY KEY
key attibute in a
column specification. Doing so will mark only that single column as primary.
You must use a separate PRIMARY KEY(index_col_name, ...)
clause.
UNIQUE
index is one in which all values in the index must be
distinct. The exception to this is that if a column in the index is allowed
to contain NULL
values, it may contain multiple NULL
values.
This exception does not apply to BDB
tables, which allow only a single
NULL
.
PRIMARY
or UNIQUE
key consists of only one column and this
is of type integer, you can also refer to it as _rowid
(new in Version 3.23.11).
PRIMARY KEY
,
the index will be assigned the same
name as the first index_col_name
, 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 Retrieving information about Database, Tables, Columns, and Indexes.
MyISAM
, InnoDB
, and BDB
table types support indexes on columns that can have
NULL
values. In other cases you must declare such columns
NOT NULL
or an error results.
col_name(length)
syntax in an index specification, you can create
an index that uses only the first length
bytes of a CHAR
or VARCHAR
column. This can make the index file much smaller.
See section Column Indexes.
MyISAM
table type supports indexing on BLOB
and
TEXT
columns. When putting an index on a BLOB
or TEXT
column you MUST always specify the length of the index, up to 255 bytes. For
example:
CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10))); |
ORDER BY
or GROUP BY
with a TEXT
or
BLOB
column, the server sorts values using only the initial number of
bytes indicated by the max_sort_length
server variable.
See section BLOB
.
FULLTEXT
indexes. They are used for full-text search. 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; partial indexing is not
supported. See MySQL Full-text Search for details of operation.
InnoDB
tables support checking of
foreign key constraints. See section InnoDB
Tables. Note that the
FOREIGN KEY
syntax in InnoDB
is more restrictive than
the syntax presented above: 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.
See the InnoDB
manual section for the precise syntax.
See section FOREIGN KEY
Constraints.
For other table types, MySQL Server does parse the FOREIGN KEY
,
CHECK
, and REFERENCES
syntax in CREATE TABLE
commands,
but without further action being taken. See section Foreign Keys.
MyISAM
and ISAM
tables,
each NULL
column takes one bit extra, rounded up to the nearest byte.
The maximum record 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 record 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 not different for NULL
columns compared to NOT
NULL
columns.
table_options
and SELECT
options are only
implemented in MySQL Version 3.23 and above.
The TYPE
option for specifying the table type takes the following
values:
Table type | Description |
| Transaction-safe tables with page locking. See section |
| The data for this table is only stored in memory. See section |
| The original storage engine. See section |
| Transaction-safe tables with row locking. See section |
| A collection of |
| An alias for |
| The new binary portable storage engine that is the
replacement for |
See section MySQL Table Types.
If a table type is specified, and that particular type is not available,
MySQL will use MyISAM
instead.
For example, if a table definition includes the TYPE=BDB
option but the
MySQL server does not support BDB
tables, the table will be 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 4.1.1 you
get a warning if the specified table type is not honored.
The other table options are used to optimise the behaviour of the table. In most cases, you don't have to specify any of them. The options work for all table types, unless otherwise indicated:
Option | Description |
| The next |
| An approximation of the average row length for your table. You only need to set this for large tables with variable size records. |
| Set this to 1 if you want MySQL to maintain a checksum for all rows (makes the table a little slower to update but makes it easier to find corrupted tables) ( |
| A 60-character comment for your table. |
| Maximum number of rows you plan to store in the table. |
| Minimum number of rows you plan to store in the table. |
| Set this to 1 if you want to have a smaller index. This usually makes updates slower and reads faster ( |
| Encrypt the `.frm' file with a password. This option doesn't do anything in the standard MySQL version. |
| Set this to 1 if want to delay key table updates until the table is closed ( |
| Defines how the rows should be stored. Currently this option only works with |
When you use a MyISAM
table, MySQL uses the product of
MAX_ROWS * AVG_ROW_LENGTH
to decide how big the resulting table
will be. If you don't specify any of the above options, the maximum size
for a table will be 4G (or 2G if your operating systems only supports 2G
tables). The reason for this is just to keep down the pointer sizes
to make the index smaller and faster if you don't really need big files.
If you don't use PACK_KEYS
, the default is to only pack strings,
not numbers. If you use PACK_KEYS=1
, numbers will be packed as well.
When packing binary number keys, MySQL will use prefix compression.
This means that you will only get a big benefit from this if you have
many numbers that are the same. Prefix compression means that every
key needs one extra byte to indicate how many bytes of the previous key are
the same for the next key (note that 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 will usually only take 2 bytes
(including the pointer to the row). Compare this to the ordinary case
where the following keys will take storage_size_for_key +
pointer_size (usually 4). On the other hand, if all keys are
totally different, you will use 1 byte more per key, if the key isn't a
key that can have NULL
values. (In this case the packed key length will
be stored in the same byte that is used to mark if a key is NULL
.)
SELECT
after the CREATE
statement,
MySQL will create new fields 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 will create 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.
CREATE TABLE ... SELECT
will not automatically create any indexes
for you. This is done intentionally to make the command 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; |
If any errors occur while copying the data to the table, it will automatically be deleted.
You can precede the SELECT
by IGNORE
or REPLACE
to indicate how to handle records that duplicate unique key values.
With IGNORE
, new records that duplicate an existing record on a
unique key value are discarded. With REPLACE
, new records replace
records that have the same unique key value. If neither IGNORE
nor REPLACE
are specified, duplicate unique key values result in
an error.
To ensure that the update log/binary log can be used to re-create the
original tables, MySQL will not allow concurrent inserts during
CREATE TABLE ... SELECT
.
RAID_TYPE
option will help you to exceed the 2G/4G limit for
the MyISAM datafile (not the index file) on operating systems that
don't support big files. Note that this option is not recommended for
filesystem that supports big files!
You can get more speed from the I/O bottleneck by putting RAID
directories on different physical disks. RAID_TYPE
will work on
any OS, as long as you have configured MySQL with --with-raid
.
For now the only allowed RAID_TYPE
is STRIPED
(1
and RAID0
are aliases for this).
If you specify RAID_TYPE=STRIPED
for a MyISAM
table,
MyISAM
will create RAID_CHUNKS
subdirectories named 00,
01, 02 in the database directory. In each of these directories
MyISAM
will create a table_name.MYD
. When writing data
to the datafile, the RAID
handler will map the first
RAID_CHUNKSIZE
*1024 bytes to the first file, the next
RAID_CHUNKSIZE
*1024 bytes to the next file and so on.
UNION
is used when you want to use a collection of identical
tables as one. This only works with MERGE
tables.
See section MERGE
.
For the moment you need to have SELECT
, UPDATE
, and
DELETE
privileges on the tables you map to a MERGE
table.
All mapped tables must be in the same database as the MERGE
table.
MERGE
table, you have to specify with
INSERT_METHOD
into with table the row should be inserted.
INSERT_METHOD
is an option useful for MERGE
tables only.
See section MERGE
Tables. This option was introduced in MySQL 4.0.0.
PRIMARY
key will be placed first, followed
by all UNIQUE
keys and then the normal keys. This helps the
MySQL optimiser to prioritise which key to use and also more quickly
detect duplicated UNIQUE
keys.
DATA DIRECTORY="directory"
or INDEX
DIRECTORY="directory"
you can specify where the storage engine should
put it's table and index files. Note that the directory should be a full
path to the directory (not relative path).
This only works for MyISAM
tables in MySQL
4.0, when you
are not using the --skip-symlink
option. See section Using Symbolic Links for Tables.
In some cases, MySQL silently changes a column specification from
that given in a CREATE TABLE
statement. (This may also occur with
ALTER TABLE
.):
VARCHAR
columns with a length less than four are changed to
CHAR
.
VARCHAR
, TEXT
, or BLOB
),
all CHAR
columns longer than three characters are changed to
VARCHAR
columns. This doesn't affect how you use the columns in
any way; in MySQL, VARCHAR
is just a different way to
store characters. MySQL performs this conversion because it
saves space and makes table operations faster. See section MySQL Table Types.
CHAR
or VARCHAR
field with a
length specification greater than 255 is converted to TEXT
.
This is a compatibility feature.
TIMESTAMP
display sizes must be even and in the range from 2 to 14.
If you specify a display size of 0 or greater than 14, the size is coerced
to 14. Odd-valued sizes in the range from 1 to 13 are coerced
to the next higher even number.
NULL
in a TIMESTAMP
column; setting
it 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. DESCRIBE tbl_name
always reports that a TIMESTAMP
column may be assigned NULL
values.
If you want to see whether MySQL used a column type other
than the one you specified, issue a DESCRIBE tbl_name
statement after
creating or altering your table.
Certain other column type changes may occur if you compress a table
using myisampack
. See section Compressed Table Characteristics.
ALTER TABLE
Syntax ALTER [IGNORE] TABLE tbl_name alter_specification [, alter_specification ...] alter_specification: ADD [COLUMN] create_definition [FIRST | AFTER column_name ] | ADD [COLUMN] (create_definition, create_definition,...) | ADD INDEX [index_name] (index_col_name,...) | ADD PRIMARY KEY (index_col_name,...) | ADD UNIQUE [index_name] (index_col_name,...) | ADD FULLTEXT [index_name] (index_col_name,...) | ADD [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name,...) [reference_definition] | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} | CHANGE [COLUMN] old_col_name create_definition [FIRST | AFTER column_name] | MODIFY [COLUMN] create_definition [FIRST | AFTER column_name] | DROP [COLUMN] col_name | DROP PRIMARY KEY | DROP INDEX index_name | DISABLE KEYS | ENABLE KEYS | RENAME [TO] new_tbl_name | ORDER BY col | table_options |
ALTER TABLE
allows you to change the structure of an existing table.
For example, you can add or delete columns, create or destroy indexes, change
the type of existing columns, or rename columns or the table itself. You can
also change the comment for the table and type of the table.
See section CREATE TABLE
.
If you use ALTER TABLE
to change a column specification but
DESCRIBE tbl_name
indicates that your column was not changed, it is
possible that MySQL ignored your modification for one of the reasons
described in Silent Column Specification Changes. For example, if you try to change
a VARCHAR
column to CHAR
, MySQL will still use
VARCHAR
if the table contains other variable-length columns.
ALTER TABLE
works by making a temporary copy of the original table.
The alteration is performed on the copy, then the original table is
deleted and the new one is renamed. This is done in such a way that
all updates are automatically redirected to the new table without
any failed updates. While ALTER TABLE
is executing, the original
table is readable by other clients. Updates and writes to the table
are stalled until the new table is ready.
Note that if you use any other option to ALTER TABLE
than
RENAME
, MySQL will always create a temporary table, even
if the data wouldn't strictly need to be copied (like when you change the
name of a column). We plan to fix this in the future, but as one doesn't
normally do ALTER TABLE
that often this isn't that high on our TODO.
For MyISAM tables, you can speed up the index recreation part (which is the
slowest part of the recreation process) by setting the
myisam_sort_buffer_size
variable to a high value.
ALTER TABLE
, you need ALTER
, INSERT
,
and CREATE
privileges on the table.
IGNORE
is a MySQL extension to SQL-92.
It controls how ALTER TABLE
works if there are duplicates on
unique keys in the new table.
If IGNORE
isn't specified, the copy is aborted and rolled back.
If IGNORE
is specified, then for rows with duplicates on a unique
key, only the first row is used; the others are deleted.
ADD
, ALTER
, DROP
, and
CHANGE
clauses in a single ALTER TABLE
statement. This is a
MySQL extension to SQL-92, which allows only one of each clause
per ALTER TABLE
statement.
CHANGE col_name
, DROP col_name
, and DROP
INDEX
are MySQL extensions to SQL-92.
MODIFY
is an Oracle extension to ALTER TABLE
.
COLUMN
is a pure noise word and can be omitted.
ALTER TABLE tbl_name RENAME TO new_name
without any other
options, MySQL simply renames the files that correspond to the table
tbl_name
. There is no need to create the temporary table.
See section RENAME TABLE
.
create_definition
clauses use the same syntax for ADD
and
CHANGE
as for CREATE TABLE
. Note that this syntax includes
the column name, not just the column type.
See section CREATE TABLE
.
CHANGE old_col_name create_definition
clause. To do so, specify the old and new column names and the type that
the column currently has. For example, to rename an INTEGER
column
from a
to b
, you can do this:
mysql> ALTER TABLE t1 CHANGE a b INTEGER; |
If you want to change a column's type but not the name, CHANGE
syntax still requires an old and new column name, even if they are the same.
For example:
mysql> ALTER TABLE t1 CHANGE b b BIGINT NOT NULL; |
However, as of MySQL Version 3.22.16a, you can also use MODIFY
to change a column's type without renaming it:
mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL; |
CHANGE
or MODIFY
to shorten a column for which
an index exists on part of the column (for instance, if you have an index
on the first 10 characters of a VARCHAR
column), you cannot make
the column shorter than the number of characters that are indexed.
CHANGE
or MODIFY
,
MySQL tries to convert data to the new type as well as possible.
FIRST
or
ADD ... AFTER col_name
to add a column at a specific position
within a table row. The default is to add the column last.
From MySQL Version 4.0.1, you can also use the FIRST
and
AFTER
keywords in CHANGE
or MODIFY
.
ALTER COLUMN
specifies a new default value for a column
or removes the old default value.
If the old default is removed and the column can be NULL
, the new
default is NULL
. If the column cannot be NULL
, MySQL
assigns a default value, as described in
CREATE TABLE
.
DROP INDEX
removes an index. This is a MySQL extension to
SQL-92. See section DROP INDEX
Syntax.
DROP TABLE
instead.
DROP PRIMARY KEY
drops the primary index. If no such
index exists, it drops the first UNIQUE
index in the table.
(MySQL marks the first UNIQUE
key as the PRIMARY KEY
if no PRIMARY KEY
was specified explicitly.)
If you add a UNIQUE INDEX
or PRIMARY KEY
to a table, this
is stored before any not UNIQUE
index so that MySQL can detect
duplicate keys as early as possible.
ORDER BY
allows you to create the new table with the rows in a
specific order. Note that the table will not remain in this order after
inserts and deletes. In some cases, it may make sorting easier for
MySQL if the table is in order by the column that you wish to
order it by later. This option is mainly useful when you know that you
are mostly going to query the rows in a certain order; by using this
option after big changes to the table, you may be able to get higher
performance.
ALTER TABLE
on a MyISAM
table, all non-unique
indexes are created in a separate batch (like in REPAIR
).
This should make ALTER TABLE
much faster when you have many indexes.
ALTER TABLE ... DISABLE KEYS
makes MySQL to stop updating
non-unique indexes for MyISAM
table.
ALTER TABLE ... ENABLE KEYS
then should be used to recreate missing
indexes. As MySQL does it with a special algorithm which is much
faster then inserting keys one by one, disabling keys could give a
considerable speedup on bulk inserts.
mysql_info()
, you can find out how many
records were copied, and (when IGNORE
is used) how many records were
deleted due to duplication of unique key values.
FOREIGN KEY
, CHECK
, and REFERENCES
clauses don't
actually do anything, except for InnoDB type tables which support
... ADD [CONSTRAINT symbol] FOREIGN KEY (...) REFERENCES ... (...)
and ... DROP FOREIGN KEY ...
.
See section FOREIGN KEY
Constraints.
The syntax for other table types is provided only for compatibility,
to make it easier to port code from other SQL servers and to run applications
that create tables with references.
See section MySQL Differences Compared To SQL-92.
Here is an example that shows some of the uses of ALTER TABLE
. We
begin with a table t1
that is created as shown here:
mysql> CREATE TABLE t1 (a INTEGER,b CHAR(10)); |
To rename the table from t1
to t2
:
mysql> ALTER TABLE t1 RENAME t2; |
To change column a
from INTEGER
to TINYINT NOT NULL
(leaving the name the same), and to change column b
from
CHAR(10)
to CHAR(20)
as well as renaming it from b
to
c
:
mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20); |
To add a new TIMESTAMP
column named d
:
mysql> ALTER TABLE t2 ADD d TIMESTAMP; |
To add an index on column d
, and make column a
the primary key:
mysql> ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a); |
To remove column c
:
mysql> ALTER TABLE t2 DROP COLUMN c; |
To add a new AUTO_INCREMENT
integer column named c
:
mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD INDEX (c); |
Note that we indexed c
, because AUTO_INCREMENT
columns must be
indexed, and also that we declare c
as NOT NULL
, because
indexed columns cannot be NULL
.
When you add an AUTO_INCREMENT
column, column values are filled in
with sequence numbers for you automatically. You can set the first
sequence number by executing SET INSERT_ID=#
before
ALTER TABLE
or using the AUTO_INCREMENT = #
table option.
See section SET
Syntax.
With MyISAM tables, if you don't change the AUTO_INCREMENT
column, the sequence number will not be affected. If you drop an
AUTO_INCREMENT
column and then add another AUTO_INCREMENT
column, the numbers will start from 1 again.
See section Problems with ALTER TABLE
..
RENAME TABLE
Syntax RENAME TABLE tbl_name TO new_tbl_name[, tbl_name2 TO new_tbl_name2,...] |
The rename is done atomically, which means that no other thread can access any of the tables while the rename is running. This makes it possible to replace a table with an empty one :
CREATE TABLE new_table (...); RENAME TABLE old_table TO backup_table, new_table TO old_table; |
The rename is done from left to right, which means that if you want to swap two table names, you have to:
RENAME TABLE old_table TO backup_table, new_table TO old_table, backup_table TO new_table; |
As long as two databases are on the same disk you can also rename from one database to another:
RENAME TABLE current_db.tbl_name TO other_db.tbl_name; |
When you execute RENAME
, you can't have any locked tables or
active transactions. You must also have the ALTER
and DROP
privileges on the original table, and the CREATE
and INSERT
privileges on the new table.
If MySQL encounters any errors in a multiple-table rename, it will do a reverse rename for all renamed tables to get everything back to the original state.
RENAME TABLE
was added in MySQL 3.23.23.
DROP TABLE
Syntax DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name,...] [RESTRICT | CASCADE] |
DROP TABLE
removes one or more tables. All table data and the table
definition are removed, so be careful with this command!
In MySQL Version 3.22 or later, you can use the keywords
IF EXISTS
to prevent an error from occurring for tables that don't
exist. In 4.1 one gets a NOTE
for all not existing tables when using
IF EXISTS
. See section SHOW WARNINGS | ERRORS
.
RESTRICT
and CASCADE
are allowed to make porting easier.
For the moment they don't do anything.
Note: DROP TABLE
will automatically commit current
active transaction (except if you are using 4.1 and the TEMPORARY
key word.
Option TEMPORARY
is ignored in 4.0. In 4.1 this option works as
follows:
Using TEMPORARY
is a good way to ensure that you don't accidently
drop a real table.
CREATE INDEX
Syntax CREATE [UNIQUE|FULLTEXT] INDEX index_name ON tbl_name (col_name[(length)],... ) |
The CREATE INDEX
statement doesn't do anything in MySQL prior
to Version 3.22. In Version 3.22 or later, CREATE INDEX
is mapped to an
ALTER TABLE
statement to create indexes.
See section ALTER TABLE
.
Normally, you create all indexes on a table at the time the table itself
is created with CREATE TABLE
.
See section CREATE TABLE
.
CREATE INDEX
allows you to add indexes to existing tables.
A column list of the form (col1,col2,...)
creates a multiple-column
index. Index values are formed by concatenating the values of the given
columns.
For CHAR
and VARCHAR
columns, indexes can be created that
use only part of a column, using col_name(length)
syntax to index the
first length
bytes of each column value. (For
BLOB
and TEXT
columns, a prefix length is required;
length
may be a value up to 255.) The
statement shown here creates an index using the first 10 characters of
the name
column:
mysql> CREATE INDEX part_of_name ON customer (name(10)); |
Because most names usually differ in the first 10 characters, this index should
not be much slower than an index created from the entire name
column.
Also, using partial columns for indexes can make the index file much smaller,
which could save a lot of disk space and might also speed up INSERT
operations!
Note that you can only add an index on a column that can have NULL
values or on a BLOB
/TEXT
column if you are using
MySQL Version 3.23.2 or newer and are using the MyISAM
table type.
For more information about how MySQL uses indexes, see MySQL indexes.
FULLTEXT
indexes can index only VARCHAR
and
TEXT
columns, and only in MyISAM
tables. FULLTEXT
indexes
are available in MySQL Version 3.23.23 and later.
MySQL Full-text Search.
DROP INDEX
Syntax DROP INDEX index_name ON tbl_name |
DROP INDEX
drops the index named index_name
from the table
tbl_name
. DROP INDEX
doesn't do anything in MySQL
prior to Version 3.22. In Version 3.22 or later, DROP INDEX
is mapped to an
ALTER TABLE
statement to drop the index.
See section ALTER TABLE
.
[ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] |
Hosting by: Hurra Communications Ltd.
Generated: 2007-01-26 17:58:45