(mysql.info)CREATE TABLE

     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 | 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}
     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      DATA DIRECTORY="directory"
     or      INDEX DIRECTORY="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 Note: Legal
names.  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 or not 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' Data file
`tbl_name.MYI' Index file

For more information on the properties of the various column types, see

   * 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'.
     Note: `mysql_insert_id()'.

     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.

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

   * `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
     attibute 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'.  *Note `SHOW':
     SHOW.

   * Only the `MyISAM' table type supports 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.  Note: Indexes.

   * 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.  Note:
     `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 `VARCHAR' and `TEXT' columns.  Indexing always
     happens over the entire column, partial indexing is not supported.
     See Note: Fulltext Search 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.
     Note: Missing functions.

   * 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 Version 3.23 and above.

     The different table types are:

     BDB or        Transaction-safe tables with page locking. Note:
     Berkeley_db   BDB.
     HEAP          The data for this table is only stored in memory.
                   Note: HEAP.
     ISAM          The original table handler. Note: ISAM.
     InnoDB        Transaction-safe tables with row locking. Note:
                   InnoDB.
     MERGE         A collection of MyISAM tables used as one table.
                   Note: MERGE.
     MyISAM        The new binary portable table handler that is
                   replacing ISAM. Note: MyISAM.
     Note: Table types.

     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 optimize 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:

     `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'    Max 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).
     `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).
     `ROW_FORMAT'  Defines how the rows should be stored. Currently you
                   can only use the DYNAMIC and STATIC options for
                   MyISAM tables.

     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 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. You can get also 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. Note: 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.

   * 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 optimizer to prioritize 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 table handler
     should put it's table and index files.  This only works for
     `MyISAM' tables in `MySQL' 4.0, when you are not using the
     `--skip-symlink' option. Note: Symbolic links to tables.