6.5 数据定义： CREATE、DROP、ALTER

6.5.1 CREATE DATABASE 句法

CREATE DATABASE [IF NOT EXISTS] db_name

CREATE DATABASE 以给定名字创建一个数据库。允许的数据库名规则在章节 6.1.2 数据库、表、索引、列和别名 中被给出。 如果数据库已经存在，并且你没有指定 IF NOT EXISTS，这时会产生一个错误。

在 MySQL 中，数据库以包含数据库表对应文件的目录实现的。因为数据库在初始创建时没有表，所以 CREATE DATABASE 语句只在 MySQL 数据目录下创建一个目录。

你也可以使用 mysqladmin 创建一个数据库。查看章节 4.8 MySQL 客户端脚本和实用程序。

6.5.2 DROP DATABASE 句法

DROP DATABASE [IF EXISTS] db_name

DROP DATABASE 移除数据库是的所有表并删除数据库。如果你在一个符号链接(symbolic link)数据库上执行一个 DROP DATABASE，链接与原始数据库均会被删除。要非常小心地使用这个命令！

DROP DATABASE 返回从数据库目录下删除的文件数目。通常，它是表的数目的三倍，因为第张表通常对应于一个 “.MYD” 文件、一个 “.MYI” 文件和一个 “.frm” 文件。

DROP DATABASE 命令从给定的数据库目录下移除以下列为扩展名的所有文件：

扩展名	扩展名	扩展名	Ext
.BAK	.DAT	.HSH	.ISD
.ISM	.ISM	.MRG	.MYD
.MYI	.db	.frm

所有包含两个数字的子目录(RAID 目录)也同样被删除。

在 MySQL 3.22 或以后的版本中，你可以使用关键词 IF EXISTS 以防止如果数据库不存在时发生错误。

你也可以使用 mysqladmin 移除数据库。查看章节 4.8 MySQL 客户端脚本和实用程序。

6.5.3 CREATE TABLE 句法

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_table_name;

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 以给定的名字在当前数据库创建一个表。允许的表名规则在章节 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，因而如果表已存在，错误也不会发生。注意，它并不验证表结构是否一致。

在 MySQL 4.1 中你可以使用 LIKE 来基于一个表定义创建另一个表。to create a table based on a table definition in another table. In MySQL 4.1 中，你同样也可以为一个被生成的列指定类型：

CREATE TABLE foo (a tinyint not null) SELECT b+1 AS 'a' FROM bar;

第张表 tbl_name 由数据库目录下的一些文件表示。对于 MyISAM 类型的表，你将得到：

文件	用途
tbl_name.frm	表定义 (form) 文件
tbl_name.MYD	数据文件
tbl_name.MYI	索引文件

对于各种列类型的性质的更多信息，查看章节 6.2 列类型：

• 如果既没有指定 NULL 也没有指定 NOT NULL，列被视为指定了 NULL 。

• 一个整型列可以有附加属性 AUTO_INCREMENT。当你插入一个 NULL 值(推荐)或 0 到一个 AUTO_INCREMENT 列，该列将被设置到 value+1，在这里，value 是表中当前列的最大值。AUTO_INCREMENT 序列以 1 开始。查看章节 8.1.3.130 mysql_insert_id()。 如果你一个 AUTO_INCREMENT 列中包含最大值的行，对于 ISAM 或 BDB 表，该值会被重新使用，但是对于一个 MyISAM 或 InnoDB 表，却不会被重用。如果你以 AUTOCOMMIT 模式执行 DELETE FROM table_name (没有一个 WHERE 子句) 删除表中的所有记录行，对于所有的表序列均重新开始。 注意：每个表只能有一个 AUTO_INCREMENT 列，并且必须被索引。MySQL 3.23 同样也只工作于 AUTO_INCREMENT 列只支持正值。插入一个负值将被当作插入一个很大的正值。这是为了避免数字从正到负“包装”的精度问题，也是为了确保不会意外地得到一个包含 0 的 AUTO_INCREMENT 列。 在 MyISAM 和 BDB 表中，你可以指定 AUTO_INCREMENT 多列索引中的第二个列。查看章节 3.5.9 使用 AUTO_INCREMENT. 为了使 MySQL 兼容某些 ODBC 应用程序，你可以用下列查询找出最后被插入的记录行：

  SELECT * FROM tbl_name WHERE auto_col IS NULL
  

• 如果 MySQL 二进制日志被使用，CREATE TABLE 将自动地提交当前 InnoDB 事务。

• NULL 值对于 TIMESTAMP 列的处理不同于其它的列类型。你不能在一个 TIMESTAMP 列中 存储一个文字 NULL；将列设置为 NULL 将设置它为当前的日期和时间。因为 TIMESTAMP 列的行为就是这样，列的 NULL 和 NOT NULL 属性不以常态方式影响它，如果你指定它们，将被忽略。 另一方面，为了使 MySQL 客户端更容易地使用 TIMESTAMP 列，服务器报告这样的列被赋值为 NULL 值(这是真的)，即使 TIMESTAMP 实际上决不会包含一个 NULL 值。当你使用 DESCRIBE tbl_name 得到有关你的表的描述时，你就会明白这点。 注意，设置一个 TIMESTAMP 列为 0 不等同于设置它为 NULL，因为 0 是一个有效的 TIMESTAMP 值。

• DEFAULT 值必须是一个常量，不可以是一个函数或一个表达式。 如果一个列没有指定 DEFAULT 值，MySQL 将自动地赋于一个，规则如下： 如果列可以接受 NULL 作为一个值，缺省值为 NULL。 如果列被定义为 NOT NULL，缺省值取决于列的类型：
  • 对于没有声明 AUTO_INCREMENT 属性的数字类型，缺省值为 0。对于一个 AUTO_INCREMENT 列，缺省值为序列中的下一个值。

  • 对于非 TIMESTAMP 的日期和时间类型，缺省值是该类型适当的零值。对于表中的第一个 TIMESTAMP 列，缺省值为当前的日期和时间。查看章节 6.2.2 Date 和 Time 类型。

  • 对于非 ENUM 的字符串类型，缺省值是空字符串。对于 ENUM，缺省值为第一个枚举值。

  缺省值必须是常量。这意味着，例如，对于一个日期列，你不能将一个像 NOW() 或 CURRENT_DATE 的函数设置为缺省值。

• KEY 是 INDEX 的同义词。

• 在 MySQL 中，一个 UNIQUE 键只能有不同的值。如果你试图以匹配一个现有行的键添加新行，将产生一个错误。

• PRIMARY KEY 是一个唯一 KEY，它还有一个额外的约束，所有键列必须被定义为 NOT NULL。在 MySQL 中，该被命名为 PRIMARY。一张表只能有一个 PRIMARY KEY。如果在你的表中没有一个 PRIMARY KEY，而某些应用程序要求 PRIMARY KEY，MySQL 将返回第一个没有任何 NULL 列的 UNIQUE 键，做为 PRIMARY KEY。

• 一个 PRIMARY KEY 可以是一个多列索引。然而，你不能在一个列规格说明中使用 PRIMARY KEY 键属性来创建一个多列索引。这样做将仅仅标记单个列做为主键。你必须使用 PRIMARY KEY(index_col_name, ...) 句法。

• 如果 PRIMARY 或 UNIQUE 键只由一个列组成，并且列类型是整型，你可以用 _rowid 引用它。(在版本 3.23.11 中新加入)。

• 如果你不为一个索引指派一个名字，索引名将被指派为与第一个 index_col_name 相同的名字，以一个可选后缀 (_2,_3, ...) 使它唯一。使用 SHOW INDEX FROM tbl_name 可以从一个表中查看索引名。查看章节 4.5.6.1 检索有关数据库、表、列和索引的信息。

• 只有 MyISAM、InnoDB 和 BDB 表类型支持在可以有 NULL 值的列上索引。在其它情况下，你必须声明这个列为 NOT NULL 或者得到一个错误结果。

• 使用 col_name(length) 句法，你可以一个索引只使用一个 CHAR 或 VARCHAR 列的一部分。这可以使索引文件更小一点。查看章节 5.4.4 列索引。

• 只有 MyISAM 表类型运动在 BLOB 和 TEXT 列上索引。当在一个 BLOB 或 TEXT 列上放置一个索引时，你必须总是指定索引的长度：

  CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));
  

• 当你对一个 TEXT 或 BLOB 列使用 ORDER BY 或 GROUP BY 时，只有最前面 的 max_sort_length 字节被使用。查看章节 6.2.3.2 BLOB 和 TEXT 类型。

• 在 MySQL 3.23.23 或更新的版本中，你也可以创建特殊的 FULLTEXT 索引。他们被用于全文搜索。只有 MyISAM 表类型支持 FULLTEXT 索引。他只能从 CHAR、VARCHAR 和 TEXT 列建立。索引总是建立在整个列上；部分索引是不支持的。详细操作请查看章节 6.8 MySQL 全文搜索。

• 在 MySQL 3.23.44 或更新的版本中，InnoDB 表支持外键约束检查。查看章节 7.5 InnoDB 表。注意，InnoDB 中的 FOREIGN KEY 句法比上面介绍的句法有更多的限制。InnoDB 不允许 index_name 被指定，参考表的列总是必须明确命名。从 4.0.8 开始，InnoDB 在外键上支持 ON DELETE 和 ON UPDATE 动作。精确句法查看 InnoDB 手册章节。查看章节 7.5 InnoDB 表。对于其它的表类型，MySQL 服务器对 CREATE TABLE 命令中的 FOREIGN KEY、CHECK 和 REFERENCES 句法作语法分析，但是没有更进一步的行为。查看章节 1.8.4.5 外键。

• 每个 NULL 列占据额外的一个比特，取舍到最接近的字节。

• 最大记录的字节长度可以按下面的计算得出：

  row length = 1
               + (sum of column lengths)
               + (number of NULL columns + 7)/8
               + (number of variable-length columns)
  

• table_options 和 SELECT 选项只在 MySQL 3.23 和以后的版本中被实现。 不同的表类型为：

  表类型	含义
  BDB 或 BerkeleyDB	以页锁定的事务安全型表。查看章节 7.6 BDB 或 BerkeleyDB 表
  HEAP	这个表的数据只存放在内存中。查看章节 7.4 HEAP 表
  ISAM	最初的存储引擎。查看章节 7.3 ISAM 表
  InnoDB	以行锁定的事务安全型表。查看章节 7.5 InnoDB 表
  MERGE	做为一个表使用的 MyISAM 表的收集品。查看章节 7.2 MERGE 表
  MRG_MyISAM	MERGE 表的别名
  MyISAM	用于代替 ISAM 的新的轻便型二进制存储引擎。查看章节 7.1 MyISAM 表

  查看章节 7 MySQL 表类型。 如果一个表类型被指定，而那个特殊类型是不可用的，MySQL 将选择最接近于你所指定类型的表类型。例如，如果 TYPE=BDB 被指定，而当前版本的 MySQL 不支持 BDB 表，该将会做为 MyISAM 表代替被创建。 其它的表选项是用于优化表行为的。在大多数情况下，你不必指定他们中的任何一个。选项对所有表均适用，如果不适用则另外说明：

  选项	含义
  AUTO_INCREMENT	你想要为你的表设定的下一个 AUTO_INCREMENT 值。(MyISAM)
  AVG_ROW_LENGTH	你的表的平均行长度的近似值。你只需为有变长记录的大表设置它
  CHECKSUM	如果你希望 MySQL 对所有的记录行维持一个检验和(这将使表在更新时变得更慢，但是使得更容易地发现损坏的表)，设置它为 1。(MyISAM)
  COMMENT	对于你的表的一个 60 个字符的注释
  MAX_ROWS	你计划在表中存储的最大记录行数目
  MIN_ROWS	你计划在表中存储的最小记录行数目
  PACK_KEYS	如果你希望有更小的索引，设置它为 1。这通常使的更新更加得慢，面读取列快 (MyISAM, ISAM)。设置它为 0，将禁用所有键压缩。设置它为 DEFAULT (MySQL 4.0)，将告诉存储引擎仅仅压缩长的 CHAR/VARCHAR 列
  PASSWORD	以一个密码加密 `.frm' 文件。在 MySQL 标准版中，这个选项不做任何事
  DELAY_KEY_WRITE	如果希望延迟键表更新，直到该表被关闭，设置它为 1。(MyISAM).
  ROW_FORMAT	定义记录行如何被存储。目前，这个选项只能工作于 MyISAM 表，它支持 DYNAMIC 和 FIXED 行格式。查看章节 7.1.2 MyISAM 表格式

  当你使用一个 MyISAM 表时，MySQL 使用 max_rows * avg_row_length 的乘积来最终表将有多大。如果你不指定上面的任何选项，一个表的最大民族教育将是 4G (或 2G ，如果你的操作系统仅支持 2G 的最大文件)。原因是仅仅是为了控制指针范围使索引更小和更快，如果您真的不需要大文件。 如果你不使用 PACK_KEYS，缺省仅仅压缩字符串，不压缩数字。如果你使用 PACK_KEYS=1，数字也将被很好地压缩。 当压缩二进制数字键时，MySQL 将使用 prefix 压缩。这就意味着，如果有许多同样的数字，你才能得到很大的益处。Prefix 压缩意味着每个键都需要一个额外的字节来指示前一个键有多少字节与下一个键相同(注意，行指针被以高元组第一次序(high-byte-first-order)直接地存储在键后，以改善压缩)。这就意味着，如果在一个记录行上有连续两行的相等键，下面所有“相同”的键通常只占用 2 个字节(包括记录行指针)。与通常情况下相比，下面的“相同”键将占用 storage_size_for_key + pointer_size (通常 4) 个字节。另一方面，如果所有的键都是不同的，你将在每个键上损失 1 字节，如果该键不是一个可以有 NULL 值的键。(在这种情况下，压缩后键的长度将存储在用于键是否为 NULL 的位元组中。)

• 如果你在一个 CREATE 语句中指定一个 SELECT，MySQL 为 SELECT 中的所有元素创建新的字段。例如：

  mysql> CREATE TABLE test (a INT NOT NULL AUTO_INCREMENT,
      ->        PRIMARY KEY (a), KEY(b))
      ->        TYPE=MyISAM SELECT b,c FROM test2;
  

  这将创建一个有三个列的 MyISAM 表，a、b 和 c 。请注意，SELECT 语句中的列被添加到表的右边，而不是重叠在上面。看下面的例子：

  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)
  

  对于表 foo 中每个记录行，一个以从表 foo 来的值和新列的缺省值组成的记录行被插入到表 bar 中。 CREATE TABLE ... SELECT 不会为了自动地创建索引。这是故意这样做的，是为了该命令尽可能地灵活。如果你希望在创建表时同时创建索引，你必须在 SELECT 语句之前指定它们：

  mysql> CREATE TABLE bar (UNIQUE (n)) SELECT n FROM foo;
  

  如果在拷贝数据到表中时发生任何错误，数据将被自动地删除。 为了确保更新日志/二进制日志可用于重建最初的表，在 CREATE TABLE ... SELECT 过程中，MySQL 不允许并发的插入。

• 在不支持大文件的操作系统上，RAID_TYPE 选项可以帮助你打破 MyISAM 数据文件(非索引文件)的 2G/4G 限止。注意，这个选项在支持大文件的文件系统上不推荐使用！ 将 RAID 目录放在不同的物理磁盘上，可以通过 I/O 瓶颈得到更高的速度。RAID_TYPE 可以在任何操作系统上工作，只要你配置 MySQL --with-raid。目前 RAID_TYPE 只允许 STRIPED (1 和 RAID0 是它的别名)。 如果你对一个 MyISAM 表指定 RAID_TYPE=STRIPED ，MyISAM 将在数据库目录下创建 RAID_CHUNKS 子目录，并命名为 00, 01, 02 。在每个目录中，MyISAM 将创建一个 table_name.MYD。当将数据写入数据文件中时，RAID 处理器将映射第一个 RAID_CHUNKSIZE *1024 字节到第一个文件中，下一个 RAID_CHUNKSIZE *1024 字节到下一个文件中，等等。

• UNION 被用于当你希望将多个同样的表收集为一个时。它仅仅与 MERGE 表一起配合使用。查看章节 7.2 MERGE 表。 目前，在你将几个表映射为一个 MERGE 表时，你需要有对这些表的 SELECT、UPDATE 和 DELETE 权限。所有被映射的表必须与 MERGE 表在同一个数据库中。

• 如果你希望向一个 MERGE 表中插入数据，你不得不用 INSERT_METHOD 指定记录行插入到哪一个表中。查看章节 7.2 MERGE 表。这个选项在 MySQL 4.0.0 中被引入。

• 在创建表时，PRIMARY 键必须放在第一位，然后是所有 UNIQUE 键，再后是普通键。这可以帮助 MySQL 优化程序区分哪个键优先使用，同时更快地检测出重复的 UNIQUE 键。

• 通过使用 DATA DIRECTORY="directory" 或 INDEX DIRECTORY="directory"，你可以指定存储引擎在什么地方存放它的表和索引文件。注意，目录必须以一个完整路径指定(不是相对路径)。 这仅仅工作于 MySQL 4.0 中的 MyISAM 表，并且你没有使用 --skip-symlink 选项。查看章节 5.6.1.2 对表使用符号链接。

>>> 进入论坛讨论 <<<