Data Definition: `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 *Note Legal names::.
An error occurs if the database already exists and you didn't specify
`IF NOT EXISTS'.
statement
only creates a directory under the MySQL data directory.
You can also create databases with `mysqladmin'. *Note Client-Side
Scripts::.
`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!*
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'. *Note Client-Side
Scripts::.
`CREATE TABLE' Syntax
---------------------
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 [)];
| 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)] [ASC | DESC]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
table_options: table_option [table_option] ...
| 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] [AS] SELECT ... (Some legal select statement)
`CREATE TABLE' creates a table with the given name. Rules for
allowable table names are given in *Note Legal 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.
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;
`CREATE TABLE ... LIKE' does not copy any `DATA DIRECTORY' or `INDEX
DIRECTORY' table options that were specified for the original table.
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*
`tbl_name.frm' Table format
(definition) file
`tbl_name.MYD' Datafile
`tbl_name.MYI' Index file
For more information on the properties of the various column types, see
*Note Column types:::
* If neither `NULL' nor `NOT NULL' is specified, the column is
treated as though `NULL' had been specified.
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'. *Note
`mysql_insert_id()': 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'. *Note InnoDB auto-increment
column::.
*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. *Note
example-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
* `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.
* A `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:
in the sequence.
- For date and time types other than `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. *Note Date and time types::.
- For string types other than `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'.
* A comment for a column may be specified with the `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.
* In MySQL, a `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.
* A `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'.
* A `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.
* A `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'.
* If the `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).
* If you don't assign a name to an index that is not a `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'. *Note Show database info::.
* Only the `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.
* With `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.
*Note Indexes::.
* Only the `MyISAM' and (as of MySQL 4.0.14) `InnoDB' table types
support 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)));
* An `index_col_name' specification may 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' 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. *Note
`BLOB': BLOB.
* In MySQL Version 3.23.23 or later, you can also create special
`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 *Note Fulltext Search:: for details of operation.
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. *Note InnoDB 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. *Note ANSI diff Foreign Keys::.
* For `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.
* The `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:
memory. *Note HEAP::.
`ISAM' The original storage engine. *Note ISAM::.
`InnoDB' Transaction-safe tables with row locking.
*Note InnoDB::.
`MERGE' A collection of `MyISAM' tables used as one
table. *Note MERGE::.
`MRG_MyISAM' An alias for `MERGE'.
`MyISAM' The new binary portable storage engine that is
the replacement for `ISAM'. *Note `MyISAM':
MyISAM.
*Note 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*
`AUTO_INCREMENT' The next `AUTO_INCREMENT' value you want to
set for your table (`MyISAM' only; to set the
first auto-increment value for an `InnoDB'
table, insert a dummy row with a value one
less, and delete the dummy row).
`AVG_ROW_LENGTH' An approximation of the average row length for
your table. You only need to set this for
large tables with variable size records.
`CHECKSUM' 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) (`MyISAM' only).
`COMMENT' A 60-character comment for your table.
`MAX_ROWS' Maximum number of rows you plan to store in
the table.
`MIN_ROWS' Minimum number of rows you plan to store in
the table.
`PACK_KEYS' Set this to 1 if you want to have a smaller
index. This usually makes updates slower and
reads faster (`MyISAM' and `ISAM' only).
Setting this to 0 will disable all packing of
keys. Setting this to `DEFAULT' (MySQL 4.0)
will tell the storage engine to only pack long
`CHAR'/`VARCHAR' columns.
`PASSWORD' Encrypt the `.frm' file with a password. This
option doesn't do anything in the standard
MySQL version.
`DELAY_KEY_WRITE' Set this to 1 if want to delay key table
updates until the table is closed (`MyISAM'
only).
`ROW_FORMAT' Defines how the rows should be stored.
Currently this option only works with `MyISAM'
tables, which supports the `DYNAMIC' and
`FIXED' row formats. *Note MyISAM table
formats::.
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'.)
* As of MySQL 3.23, if you specify a `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;
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.
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.
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'.
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. *Note
`MERGE': MERGE.
* If you want to insert data in a `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.
*Note MERGE::. This option was introduced in MySQL 4.0.0.
* In the created table the `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.
* By using `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. *Note Symbolic links to
tables::.
Silent Column Specification Changes
...................................
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'.
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. *Note Table types::.
* From version 4.1.0, if a `CHAR' or `VARCHAR' field with a length
specification greater than 255 is converted to `TEXT'. This is a
compatibility feature.
1 to 13
are coerced to the next higher even number.
* You cannot store a literal `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.
* MySQL maps certain column types used by other SQL database vendors
to MySQL types. *Note Other-vendor column types::.
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'. *Note Compressed format::.
`ALTER TABLE' Syntax
--------------------
ALTER [IGNORE] TABLE tbl_name alter_specification [, alter_specification ...]
| 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. *Note `CREATE TABLE': 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 *Note Silent column 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.
* To use `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.
* You can issue multiple `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'.
* The optional word `COLUMN' is a pure noise word and can be omitted.
* If you use `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. *Note `RENAME TABLE': 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. *Note `CREATE TABLE':
CREATE TABLE.
* You can rename a column using a `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;
* If you use `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.
* When you change a column type using `CHANGE' or `MODIFY', MySQL
tries to convert data to the new type as well as possible.
* In MySQL Version 3.22 or later, you can use `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
*Note `CREATE TABLE': CREATE TABLE.
* `DROP INDEX' removes an index. This is a MySQL extension to
SQL-92. *Note DROP INDEX::.
* If columns are dropped from a table, the columns are also removed
from any index of which they are a part. If all columns that make
up an index are dropped, the index is dropped as well.
* If a table contains only one column, the column cannot be dropped.
If what you intend is to remove the table, use `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.
* If you use `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.
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.
* With the C API function `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.
* The `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 ...'. *Note InnoDB 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.
*Note Differences from ANSI::.
* `ALTER TABLE' ignores the `DATA DIRECTORY' and `INDEX DIRECTORY'
table options.
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. *Note SET OPTION::.
numbers will start from 1 again.
*Note ALTER TABLE problems::.
`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;
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!
EXISTS'.
*Note SHOW WARNINGS::.
`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:
* Only drops temporary tables.
* It doesn't end a running transactions.
* No access rights is checked.
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 (index_col_name,...)
index_col_name:
col_name [(length)] [ASC | DESC]
ALTER
TABLE.
Normally, you create all indexes on a table at the time the table itself
is created with `CREATE TABLE'. *Note `CREATE TABLE': 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));
the
index file much smaller, which could save a lot of disk space and might
also speed up `INSERT' operations!
Note that you can add an index on a column that can have `NULL' values
only if you are using MySQL Version 3.23.2 or newer and are using the
`MyISAM', `InnoDB', or `BDB' table type. You can only add an index on
a `BLOB'/`TEXT' column if you are using MySQL Version 3.23.2 or newer
and are using the `MyISAM' or `BDB' table type, or MySQL Version 4.0.14
or newer and the `InnoDB' table type. For an index on a`BLOB'/`TEXT'
column, a prefix length must always be specified.
An `index_col_name' specification may 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.
For more information about how MySQL uses indexes, see *Note MySQL
indexes: MySQL indexes.
`FULLTEXT' indexes can index only `CHAR', `VARCHAR', and `TEXT'
columns, and only in `MyISAM' tables. `FULLTEXT' indexes are available
in MySQL Version 3.23.23 and later. *Note Fulltext 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. *Note `ALTER TABLE': ALTER TABLE.
[Назад] [Содержание] [Вперед]
| Главная |