CREATE DATABASE [IF NOT EXISTS] db_name
CREATE DATABASE creates a database with the given name. Rules for allowable database names are given in Section 6.1.2. 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 4.8.
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 are 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 4.8.
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]
[table_options] [select_statement]
create_definition:
col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]
[PRIMARY KEY] [reference_definition]
or PRIMARY KEY (index_col_name,...)
or KEY [index_name] (index_col_name,...)
or INDEX [index_name] (index_col_name,...)
or UNIQUE [INDEX] [index_name] (index_col_name,...)
or FULLTEXT [INDEX] [index_name] (index_col_name,...)
or [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name,...)
[reference_definition]
or CHECK (expr)
type:
TINYINT[(length)] [UNSIGNED] [ZEROFILL]
or SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
or MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
or INT[(length)] [UNSIGNED] [ZEROFILL]
or INTEGER[(length)] [UNSIGNED] [ZEROFILL]
or BIGINT[(length)] [UNSIGNED] [ZEROFILL]
or REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
or DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
or FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
or DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL]
or NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL]
or CHAR(length) [BINARY]
or VARCHAR(length) [BINARY]
or DATE
or TIME
or TIMESTAMP
or DATETIME
or TINYBLOB
or BLOB
or MEDIUMBLOB
or LONGBLOB
or TINYTEXT
or TEXT
or MEDIUMTEXT
or LONGTEXT
or ENUM(value1,value2,value3,...)
or 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:
TYPE = {BDB | HEAP | ISAM | InnoDB | MERGE | MRG_MYISAM | MYISAM }
or AUTO_INCREMENT = #
or AVG_ROW_LENGTH = #
or CHECKSUM = {0 | 1}
or COMMENT = "string"
or MAX_ROWS = #
or MIN_ROWS = #
or PACK_KEYS = {0 | 1 | DEFAULT}
or PASSWORD = "string"
or DELAY_KEY_WRITE = {0 | 1}
or ROW_FORMAT= { default | dynamic | fixed | compressed }
or RAID_TYPE= {1 | STRIPED | RAID0 } RAID_CHUNKS=# RAID_CHUNKSIZE=#
or UNION = (table_name,[table_name...])
or INSERT_METHOD= {NO | FIRST | LAST }
or DATA DIRECTORY="absolute path to directory"
or INDEX DIRECTORY="absolute path to directory"
select_statement:
[IGNORE | REPLACE] SELECT ... (Some legal select statement)CREATE TABLE creates a table with the given name in the current database. Rules for allowable table names are given in Section 6.1.2. An error occurs if there is no current database or if the table already exists.
In MySQL Version 3.22 or later, the table name can be specified as db_name.tbl_name. This works whether there is a current database.
In MySQL Version 3.23, you can use the TEMPORARY keyword when you create a table. A temporary table will automatically be deleted if a connection dies and the name is per connection. 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.)
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 table structures are identical.
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 definition (form) file |
|
tbl_name.MYD |
Datafile |
|
tbl_name.MYI |
Index file |
Here is some more information on the properties of the various column types (see also Section 6.2):
If neither NULL nor NOT NULL is specified, the column is treated as though NULL had been specified.
An integer column may have the additional attribute AUTO_INCREMENT. When you insert a value of NULL (recommended) or 0 into an AUTO_INCREMENT column, the column is set to value+1, where value is the largest value for the column currently in the table. AUTO_INCREMENT sequences begin with 1. See Section 8.4.3.126.
If you delete the row containing the maximum value for an AUTO_INCREMENT column, the value will be reused with an ISAM or BDB table but not with 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.
Note: There can be only one AUTO_INCREMENT column per table, and it must be indexed. MySQL Version 3.23 will also only work properly if the AUTO_INCREMENT column only has 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 columns in a multi-column key. See Section 3.5.9.
To make MySQL compatible with some ODBC applications, you can find 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.
If no DEFAULT value is specified for a column, MySQL automatically assigns one.
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:
For numeric types other than those declared with the AUTO_INCREMENT attribute, the default is 0. For an AUTO_INCREMENT column, the default value is the next value 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. See Section 6.2.2.
For string types other than ENUM, the default value is the empty string. For ENUM, the default is the first enumeration value (if you haven’t explicitly specified another default value with the DEFAULT directive).
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.
KEY is a synonym for INDEX.
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 with the extra constraint that all key columns must be defined as NOT NULL. 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 attribute in a column specification. Doing so will mark only that single column as primary. You must use the PRIMARY KEY(index_col_name, ...) syntax.
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, 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 4.5.6.
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, you can specify an index that uses only a part of a CHAR or VARCHAR column. This can make the index file much smaller. See Section 5.4.4.
Only the 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:
CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));
When you use ORDER BY or GROUP BY with a TEXT or BLOB column, only the first max_sort_length bytes are used. See Section 6.2.3.2.
In MySQL Version 3.23.23 or later, you can also create special FULLTEXT indexes. They are used for full-text searches. Only the MyISAM table type supports FULLTEXT indexes. They can be created only from VARCHAR and TEXT columns. Indexing always happens over the entire column; partial indexing is not supported. See Section 6.8, for details of operation.
The FOREIGN KEY, CHECK, and REFERENCES clauses don’t actually do anything. The syntax for them 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 1.7.4.
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 + 7)/8 + (number of variable-length columns)The table_options and SELECT options are only implemented in MySQL Versions 3.23 and higher.
The different table types are:
Table type
Description
BDB or Berkeley_db
Transaction-safe tables with page locking. See Section 7.6.
HEAP
The data for this table is only stored in memory. See Section 7.4.
ISAM
The original table handler. See Section 7.3.
InnoDB
Transaction-safe tables with row locking. See Section 7.5.
MERGE
A collection of MyISAM tables used as one table. See Section 7.2.
MRG_MyISAM
An alias for MERGE tables.
MyISAM
The new binary portable table handler that is replacing ISAM. See Section 7.1.
See Chapter 7.
If a table type is specified and that particular type is not available, MySQL will choose the closest table type to the one that you have specified. For example, if TYPE=BDB is specified and that distribution of MySQL does not support BDB tables, the table will be created as MyISAM instead.
The other table options are used to optimise the behavior of the table. In most cases, you don’t have to specify any of them. The options work for all table types, if not otherwise indicated:
Option
Description
AUTO_INCREMENT
The next AUTO_INCREMENT value you want to set for your table (MyISAM).
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).
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, ISAM). Setting this to 0 will disable all packing of keys. Setting this to DEFAULT (MySQL 4.0) will tell the table handler to only pack long CHAR/VARCHAR columns.
PASSWORD
Encrypt the
.frmfile with a password. This option doesn’t do anything in the standard MySQL version.DELAY_KEY_WRITE
Set this to 1 if you want to delay key table updates until the table is closed (MyISAM).
ROW_FORMAT
Defines how the rows should be stored. Currently this option only works with MyISAM tables, which support the DYNAMIC and FIXED row formats. See Section 7.1.2.
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 preceding options, the maximum size for a table will be 4G (or 2G if your operating system 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 of 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 rows in a row, 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 lose 1 byte 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.)
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;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.
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.
The RAID_TYPE option will help you to break the 2G/4G limit for the MyISAM data file (not the index file) on operating systems that don’t support big files. Note that this option is not recommended for filesystem that support 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 data file, 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 7.2.
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.
If you want to insert data in a MERGE table, you have to specify with INSERT_METHOD into table the row should be inserted. See Section 7.2. 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 to more quickly detect duplicated UNIQUE keys.
By using DATA DIRECTORY="directory" or INDEX DIRECTORY="directory" you can specify where the table handler should put its table and index files. Note that the directory should be a full path to the directory (not a relative path).
This only works for MyISAM tables in MySQL 4.0, when you are not using the --skip-symlink option. See Section 5.6.1.2.
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.
If any column in a table has a variable length, the entire row is variable-length as a result. Therefore, if a table contains any variable-length columns (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 Chapter 7.
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.
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. See Section 6.2.5.
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 7.1.2.3.
ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...]
alter_specification:
ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
or ADD [COLUMN] (create_definition, create_definition,...)
or ADD INDEX [index_name] (index_col_name,...)
or ADD PRIMARY KEY (index_col_name,...)
or ADD UNIQUE [index_name] (index_col_name,...)
or ADD FULLTEXT [index_name] (index_col_name,...)
or ADD [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...)
[reference_definition]
or ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
or CHANGE [COLUMN] old_col_name create_definition
[FIRST | AFTER column_name]
or MODIFY [COLUMN] create_definition [FIRST | AFTER column_name]
or DROP [COLUMN] col_name
or DROP PRIMARY KEY
or DROP INDEX index_name
or DISABLE KEYS
or ENABLE KEYS
or RENAME [TO] new_tbl_name
or ORDER BY col
or table_optionsALTER 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 6.5.3.
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 Section 6.5.3.1. 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 option to ALTER TABLE other 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 ANSI SQL92. 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 ANSI SQL92, which allows only one of each clause per ALTER TABLE statement.
CHANGE col_name, DROP col_name, and DROP INDEX are MySQL extensions to ANSI SQL92.
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. See Section 6.5.5.
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 6.5.3.
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 two column names 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 accurately 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.
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 Section 6.5.3.
DROP INDEX removes an index. This is a MySQL extension to ANSI SQL92. See Section 6.5.8.
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 you intend 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.
Since MySQL 4.0 the preceding feature can be activated explicitly. ALTER TABLE ... DISABLE KEYS makes MySQL stop updating non-unique indexes for MyISAM table. ALTER TABLE ... ENABLE KEYS then should be used to re-create missing indexes. As MySQL does it with a special algorithm that is much faster than 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. The syntax for them 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 1.7.4.
Here is an example that shows some of the uses of ALTER TABLE. We begin with a table t1 that is created as follows:
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 5.5.6.
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 A.6.1
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 [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.
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.
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 6.5.4.
Normally, you create all indexes on a table at the time the table itself is created with CREATE TABLE. See Section 6.5.3. 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. (On BLOB and TEXT columns the length is required.) The following statement 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 Versions 3.23.2 or newer and are using the MyISAM table type.
For more information about how MySQL uses indexes, see Section 5.4.3.
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. See Section 6.8.
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 6.5.4.
Get MySQL Reference Manual now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
