Search the MySQL manual:
MySQL Manual

Buy this Reference Manual in softcover from Barnes & Noble!

/ / Up / Table of Contents

6.5.3 Синтаксис оператора 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]
  или   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:
        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="абсолютный путь к каталогу"
или     INDEX DIRECTORY="абсолютный путь к каталогу"

select_statement:
        [IGNORE | REPLACE] SELECT ...  (любое корректное выражение SELECT)

Оператор CREATE TABLE создает таблицу с заданным именем в текущей базе данных. Правила для допустимых имен таблицы приведены в разделе section 6.1.2 Имена баз данных, таблиц, столбцов, индексы псевдонимы. Если нет активной текущей базы данных или указанная таблица уже существует, то возникает ошибка выполнения команды.

В версии MySQL 3.22 и более поздних имя таблицы может быть указано как db_name.tbl_name. Эта форма записи работает независимо от того, является ли указанная база данных текущей.

В версии MySQL 3.23 при создании таблицы можно использовать ключевое слово TEMPORARY. Временная таблица автоматически удаляется по завершении соединения, а ее имя действительно только в течение данного соединения. Это означает, что в двух разных соединениях могут использоваться временные таблицы с одинаковыми именами без конфликта друг с другом или с существующей таблицей с тем же именем (существующая таблица скрыта, пока не удалена временная таблица). В версии MySQL 4.0.2 для создания временных таблиц необходимо иметь привилегии CREATE TEMPORARY TABLES.

В версии MySQL 3.23 и более поздних можно использовать ключевые слова IF NOT EXISTS для того, чтобы не возникала ошибка, если указанная таблица уже существует. Следует учитывать, что при этом не проверяется идентичность структур этих таблиц.

Каждая таблица tbl_name представлена определенными файлами в директории базы данных. В случае таблиц типа MyISAM это следующие файлы:

Файл Назначение
tbl_name.frm Файл определения таблицы
tbl_name.MYD Файл данных
tbl_name.MYI Файл индексов

Чтобы получить более полную информацию о свойствах различных типов столбцов, section 6.2 Типы данных столбцов:

Главы

User Comments

Posted by on Thursday July 25 2002, @7:47am [Delete] [Edit]

A friend and I thought the following info would be
useful, since I don't think it is explicitly
stated in the documentation, at least not on this
page.

Anyway, for columns with DEFAULT values you can
insert the empty string ('' or "") in order to
force that column to use its default.

This makes sense but took us a while to figure
out. We figured we could just put NULL and the
DEFAULT would be set. This caused an error since
our columns were flagged as NOT NULL.

I have not tested to see if the NULL keyword sets
a columns DEFAULT if it is allowed to be null.

Hope this info is timely and useful for someone.

Posted by Alan on Saturday July 20 2002, @2:19pm [Delete] [Edit]

Even though I didn't find it in the documentation,
I've encountered problems in tables with
AUTO_INCREMENT fields where the field isn't the
first field in the table. SELECT statements don't
seem to work on these tables. If I reformat the
table with the AUTO_INCREMENTed field as the first
field in the table it works fine.

Posted by on Wednesday December 18 2002, @5:27pm [Delete] [Edit]

Reserved Keyword Problems
If you want to create or perform an operation on
any table whose name is a reserved keyword,
simply place backtick characters around the word -
e.g:
CREATE TABLE `group`
(ID CHAR(13) NOT NULL);

Ordinary single quotes don't seem to work.
This also works for multiple drops or anything else.

Posted by on Thursday October 17 2002, @1:47am [Delete] [Edit]

To copy table "alpha" to table "beta" in your
database, just do

create table beta select * from alpha

This is not mentioned under "copy" anywhere in the
manual and it took me about a half an hour of
flipping pages to figure it out. Please add a note for
all the other doofusses (doofi?) out there

Posted by Mike Nerone on Wednesday December 18 2002, @5:27pm [Delete] [Edit]

On copying table structures:
Continuing Mr. Reynolds' example: if you want
to copy only the table structure (and
not the row
data) from table "alpha" to table "beta", you can
simply use:

mysql> CREATE TABLE beta
SELECT *
FROM alpha LIMIT
0;
Unfortunately,
while this does preserve the field structure, it does
not preserve indexes and such (which
is also a
failing of using this technique even if you
are copying the data as well), and I've
found no
way to do this at the SQL level. The easiest way I've
found requires a quick visit to bash (or whatever):
#
cd /var/lib/mysql/somedatabase
# cp -a alpha.frm
beta.frm═══#Note: -a
preserves
file permissions so mysqld is able to access the new
file


...then back in mysql:

mysql> TRUNCATE TABLE
beta;


A SQL-level method for this would be nice
(maybe something like "CREATE TABLE beta
DEFINITION [FROM] alpha"), but this is the
closest
thing I've found.
Posted by Melvyn Sopacua on Thursday January 23 2003, @3:12pm [Delete] [Edit]

Now that InnoDB tables support this, it would be a welcome addition, to mention which CONSTRAINT 'symbol'(s) is/are supported (or refer to whatever standard applies)

Posted by Melvyn Sopacua on Thursday January 23 2003, @3:43pm [Delete] [Edit]

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

This is not true anymore for Mysql 4.x.

Posted by Sergiy Kuzmenko on Monday February 10 2003, @11:41am [Delete] [Edit]

Regarding feedback about default values for columns of type char/varchar.
MySQL doesn't seem to be able to assign a default value other then NULL or an empty sring, regardless of wether a column is allowed or not to contain null values.
Consider this example:

mysql> create table mytest(id int not null primary key auto_increment, description varchar(40) default 'not available');
Query OK, 0 rows affected (0.00 sec)

mysql> describe mytest;
+-------------+-------------+------+-----+---------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------------+----------------+
| id | int(11) | | PRI | 0 | auto_increment |
| description | varchar(40) | YES | | not available | |
+-------------+-------------+------+-----+---------------+----------------+
2 rows in set (0.00 sec)

mysql> insert into mytest values(null, null);
Query OK, 1 row affected (0.00 sec)

mysql> insert into mytest values(null, '');
Query OK, 1 row affected (0.00 sec)

mysql> select * from mytest;
+----+-------------+
| id | description |
+----+-------------+
| 1 | NULL |
| 2 | |
+----+-------------+
2 rows in set (0.00 sec)

mysql> alter table mytest modify description varchar(40) not null default 'not available';
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 1

mysql> insert into mytest values(null, '');
Query OK, 1 row affected (0.00 sec)

mysql> insert into mytest values(null, null);
ERROR 1048: Column 'description' cannot be null
mysql> select * from mytest;
+----+-------------+
| id | description |
+----+-------------+
| 1 | |
| 2 | |
| 3 | |
+----+-------------+
3 rows in set (0.00 sec)

The default value of varchar has no impact on updating/inserting data.
This was tested under MySQL 3.22.32 on Linux.

Add your own comment.

Top / / / Up / Table of Contents