[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

6.5 Data Definition: CREATE, DROP, ALTER


6.5.1 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.


6.5.2 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.


6.5.3 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 [)];

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

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 Column Types:


6.5.3.1 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.):

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.


6.5.4 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.

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..


6.5.5 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.


6.5.6 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.


6.5.7 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.


6.5.8 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