MySQL 4.1.0 中文参考手册 --- 6.5 数据定义： CREATE、DROP、ALTER

MySQL Reference Manual for version 4.1.0-alpha.

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_MyISAMMERGE 表的别名
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 对表使用符号链接 。

6.5.3.1 隐式的列定义变化

在某些情况下，MySQL 隐式地改变一个在 CREATE TABLE 给定的列的规约。(这在 ALTER TABLE 中也可能发生。)：

  • 长度不超过四个字节的 VARCHAR 列被改变为 CHAR 。

  • 如果在一个表中有任何一个列是变长的，则结果是整个记录行也是变长的。因此，如果一个表中包含任何变长的列( VARCHAR 、 TEXT 或 BLOB )，所有长于 3 个字符的 CHAR 列将被改变为 VARCHAR 列。这在任何方面都不影响你如何使用该列；在 MySQL 中， VARCHAR 只是存储字符的另一个不同的方法。MySQL 执行这个转换，是因为它节省空间，并且使表操作更快。查看章节 7 MySQL 表类型 。

  • TIMESTAMP 的显示尺寸必须是在 2 到 14 范围之内的偶数。如果指定显示尺寸为 0 或超过 14，尺寸被强制设为 14。从 1 到 13 范围内的奇数值尺寸将被强制为下一个更大的偶数。

  • 你不能在一个 TIMESTAMP 列中存储一个文字 NULL ；将一个 NULL 值赋给它将设置它为当前的日期和时间。因为 TIMESTAMP 列的行为就是这样，列的 NULL 和 NOT NULL 属性不以常态方式影响它，如果你指定它，将被忽略。 DESCRIBE tbl_name 总是报告一个 TIMESTAMP 列被赋于了 NULL 值。

  • MySQL 将其它 SQL 数据库供应商使用的列类型映射到 MySQL 类型。查看章节 6.2.5 使用来自其它的数据库引擎的列类型 。

如果你希望知道在你创建或改变了你的表后， MySQL 是否使用了不同于你所指定的列类型，你可以发出一个 DESCRIBE tbl_name 语句。

如果你使用 myisampack 压缩一个表，其它的某些列类型可能会发生改变。查看章节 7.1.2.3 压缩表的特征 。

6.5.4 ALTER TABLE 句法

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_options

ALTER TABLE 允许你改变一个现有表的结构。例如，你可以添加或删除列，创建或撤销索引，更改现有列的类型或将列或表自身更名。你也可以改变表的注释和表的类型。查看章节 6.5.3 CREATE TABLE 句法 。

如果你使用 ALTER TABLE 来改变一个列规约，但是 DESCRIBE tbl_name 显示你的列并没有被修改，这有可能是因为章节 6.5.3.1 隐式的列定义变化 描述的一个原因，使 MySQL 忽略了你的修改。例如，如果你尝试将一个 VARCHAR 列更改为 CHAR ，而如果在这个表中包含其它的变长列，MySQL 将仍然使用 VARCHAR 。

ALTER TABLE 通过建立原初表的一个临时副本来工作。更改在副本上执行，然后原初表将被删除，临时表被换名。这样做使所有的修改自动地转向到没有任何更新失败的新表。当 ALTER TABLE 执行时，原初表可被其它客户端读取。更新与写入被延迟到新的表准备好。

注意，如果你以除 RENAME 之外的其它选项使用 ALTER TABLE ，MySQL 将总是创建一个临时表，即使数据并不确实需要被复制(就像当你改变一个列名时)。我们计划不久来修正它，但是通常人们是不经常执行 ALTER TABLE 的，所以在我们的 TODO 上，这个修正并不是急于处理的。对于 MyISAM 表，你可以将变量 myisam_sort_buffer_size 设置和高一点，以加速索引的重建部分(这是重建进程中最慢的部分)。

  • 为了使用 ALTER TABLE ，你需要在这个表上有 ALTER 、 INSERT 和 CREATE 权限。

  • IGNORE 是 MySQL 对 ANSI SQL92 的扩展。它用于控制当在新表中的唯一键上出现重复值时， ALTER TABLE 如何工作。如果 IGNORE 没有被指定，副本将被放弃并回退。如果 IGNORE 被指定，那么在唯一键上重复的记录行只有第一个记录行被使用；其它的均被删除。

  • 你可以在单个的 ALTER TABLE 语句中发出多个 ADD 、 ALTER 、 DROP 和 CHANGE 子句。这是 MySQL 对 ANSI SQL92 的扩展，ANSI SQL92 只允许在每个 ALTER TABLE 语句中一个子句。

  • CHANGE col_name 、 DROP col_name 和 DROP INDEX 是 MySQL 对 ANSI SQL92 的扩展。

  • MODIFY is an Oracle extension to ALTER TABLE .

  • 可选词 COLUMN 只是一个无用词组，可被忽略。

  • 如果你使用 ALTER TABLE tbl_name RENAME TO new_name ，并没有任何其它的选项，MySQL 将简单地重命名与表 tbl_name 的文件。这不需要创建临时表。查看章节 6.5.5 RENAME TABLE 句法 。

  • create_definition 子句使用与 CREATE TABLE 相同的 ADD 和 CHANGE 句法。注意，这些句法不仅包含列类型，还要包含列名。查看章节 6.5.3 CREATE TABLE 句法 。

  • 你可以使用一个 CHANGE old_col_name create_definition 子句来重命名一个列。为了这样做，你必须指定旧的和新的列名，以及列当前的类型。例如，为了将一个 INTEGER 列 a 重命名为 b ，你必须这样做：

    mysql> ALTER TABLE t1 CHANGE a b INTEGER;

如果你希望改变一个列的类型而不是列名， CHANGE 句法仍然需要有两个列名，即使它们是一样的。例如：

mysql> ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;

然后，到 MySQL 3.22.16a 时，你也可以使用 MODIFY 来改变一个列的类型而不需要重命名它：

mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;

  • 如果你使用 CHANGE 或 MODIFY 缩短一个列，而该列上存在一个取列部分值的索引(举例来说，如果你有一个索引在一个 VARCHAR 列的前 10 个字符上)，那么，你将不能使列短于索引的字符数目。

  • 当你使用 CHANGE 或 MODIFY 改变一个列类型时，MySQL 将尝试尽可能地将数据转换到新的类型。

  • 在 MySQL 3.22 或更新的版本中，你可以使用 FIRST 或 ADD ... AFTER col_name 在一个表中的某个特定位置添加一列。缺省是增加到最后一列。从 MySQL 4.0.1 开始，你也可以在 CHANGE 或 MODIFY 中使用关键词 FIRST 和 AFTER 。

  • ALTER COLUMN 可以为一列指定一个新的缺省值或删除老的缺省值。如果老的缺省值被移除且列可以被设为 NULL ，新的缺省值将是 NULL 。如果该列不允许有 NULL 值，MySQL 以章节 6.5.3 CREATE TABLE 句法 中的描述方式为该列赋于一个缺省值。

  • DROP INDEX 移除一个索引。这是 MySQL 对 ANSI SQL92 的一个扩展。查看章节 6.5.8 DROP INDEX 句法 。

  • 如果列被从一个表中移除，列也将从任何有它为组成部分的索引中被移除。如果组成一个索引的所有列均被移除了，那么，该索引也将被移除。

  • 如果一个表只包含一个列，那么该列不能被移除。如果你本就打算移除该表，请使用 DROP TABLE 代替。

  • DROP PRIMARY KEY 移除主索引。如果这样的索引不存在，它将移除表中的第一个 UNIQUE 索引。(如果没有 PRIMARY KEY 被明确指定，MySQL 将第一个 UNIQUE 键标记为 PRIMARY KEY ) 如果你添加一个 UNIQUE INDEX 或 PRIMARY KEY 到一个表中，它将被存储在任何非 UNIQUE 索引之前，因而，MySQL 可以尽可能地检测出重复键。

  • ORDER BY 允许你以指定的记录行顺序创建一个新表。注意，在插入与删除后，该表将不会保留这个顺序。在某些情况下，如果表在你以后希望排序的列上是有序的，这将使得 MySQL 排序时更加得容易。当你知道你主要查询的行以一个确定的次序时，这将是很有用的。在对表进行过大的改变后，通过使用这个选项，你可能会得到更高的性能。

  • 如果你在一个 MyISAM 表上使用 ALTER TABLE ，所有非唯一的索引将以一个分批方式创建(就像 REPAIR 一样)。当你有很多索引时，这可能使 ALTER TABLE 更快一点。

  • 从 MySQL 4.0 开始，上面的特性可明确地激活。 ALTER TABLE ... DISABLE KEYS 使 MySQL 停止更新 MyISAM 表的非唯一索引。然后 ALTER TABLE ... ENABLE KEYS 可以被用来重建丢失的索引。因为 MySQL 以特殊的算法执行它，这将比一个接一个地插入索引要快得多，禁用键可以很大程序上的加速一个大批量的插入。

  • 使用 C API 函数 mysql_info() ，你可以找出有多少记录被拷贝，以及(当 IGNORE 被使用时)有多少记录因唯一键值重复而被删除。

  • FOREIGN KEY 、 CHECK 和 REFERENCES 子句实际上不做任何事情，除了对于 InnoDB 类型的表，它支持 ADD CONSTRAINT FOREIGN KEY (...) REFERENCES ... (...) 。 注意，InnoDB 不允许一个 index_name 被指定。查看章节 7.5 InnoDB 表 。 对于其它类型的表，这个句法仅仅为了兼容而提供，以更容易地从其它 SQL 服务器移植代码和更容易地运行以引用创建表的应用程序。查看章节 1.8.4 MySQL 与 ANSI SQL92 相比不同的差别 。

这里是一个例子，显示了 ALTER TABLE 的一些用法。我们以一个按如下方式创建一个表 t1 开始：

mysql> CREATE TABLE t1 (a INTEGER,b CHAR(10));

为了将表 t1 重命名为 t2 ：

mysql> ALTER TABLE t1 RENAME t2;

为了将列 a 从 INTEGER 改变为 TINYINT NOT NULL (列名不变)，并将列 b 从 CHAR(10) 改变为 CHAR(20) ，同时也将 b 重命名为 c ：

mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);

添加一个名为 d 的 TIMESTAMP c列：

mysql> ALTER TABLE t2 ADD d TIMESTAMP;

在列 d 上增加一个索引，将列 a 设为主键：

mysql> ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);

移除列 c :

mysql> ALTER TABLE t2 DROP COLUMN c;

添加一个名为 c 的 AUTO_INCREMENT 整型列：

mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,
           ADD INDEX (c);

注意，我们索引了 c ，因为 AUTO_INCREMENT 列必须被索引，同样我们声明列 c 为 NOT NULL ，因为被索引的列不能有 NULL 。

当你添加一个 AUTO_INCREMENT 列时，列值会自动地以序列值填充。通过在 ALTER TABLE 或使用 AUTO_INCREMENT = # 表选项之前执行 SET INSERT_ID=# ，你可以设置第一个序列数字。查看章节 5.5.6 SET 句法 。

对于 MyISAM 表，如果你不改变 AUTO_INCREMENT 列，序列值将不会被影响。如果你移除一个 AUTO_INCREMENT 列，并添加另一个 AUTO_INCREMENT 列，值将再次从 1 开始。

查看章节 A.6.1 ALTER TABLE 的问题 。

6.5.5 RENAME TABLE 句法

RENAME TABLE tbl_name TO new_tbl_name[, tbl_name2 TO new_tbl_name2,...]

更名是以原子方式(atomically)执行，这就意味着，当更名正在运行时，其它的任何线程均不能该表。这使得以一个空表替换一个表成为可能。

CREATE TABLE new_table (...);
RENAME TABLE old_table TO backup_table, new_table TO old_table;

改名是从左到右执行的，这就意味着，如果你希望交换两个表名，你不得不这样做：

RENAME TABLE old_table    TO backup_table,
             new_table    TO old_table,
             backup_table TO new_table;

只要两个数据库在同一个磁盘上，你也可以从一个数据库更名到另一个数据库：

RENAME TABLE current_db.tbl_name TO other_db.tbl_name;

当你执行 RENAME 时，你不能有任何锁定的表或活动的事务。你同样也必须有对原初表的 ALTER 和 DROP 权限，以及对新表的 CREATE 和 INSERT 权限。

如果在多表更名中，MySQL 遭遇到任何错误，它将对所有被更名的表进行倒退更名，将每件事物退回到最初状态。

RENAME TABLE 在 MySQL 3.23.23 中被加入。

6.5.6 DROP TABLE 句法

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name,...] [RESTRICT | CASCADE]

DROP TABLE 移除一个或多个表。所有的数据和表定义均被 移除 ，所以， 一定要小心地 使用这个命令！

在 MySQL 3.22 或更新的版本中，你可以使用关键词 IF EXISTS 防止表不存在时发生错误。在 4.1 中，当使用 IF EXISTS 时，对于所有不存在的表，你将得到一个 NOTE 。查看章节 4.5.6.9 SHOW WARNINGS | ERRORS 。

RESTRICT and CASCADE 被允许是为了更容易的移植。目前，他们不起任何作用。

注意： DROP TABLE 将自动地提交当前活动的事务(除非你使用的是MySQL 4.1 ，并且使用了 TEMPORARY 关键词)。

选项 TEMPORARY 在 4.0 中被忽略。在 4.1 中，这人选项按如下所示工作：

  • 只移除临时表。
  • 不结束一个运行着的事务。
  • 不会被检查访问权限。

使用 TEMPORARY 是一个很好的安全方式，它可以防止你意外地移除一个真实的表。

6.5.7 CREATE INDEX 句法

CREATE [UNIQUE|FULLTEXT] INDEX index_name
       ON tbl_name (col_name[(length)],... )

CREATE INDEX 句法在 MySQL 3.22 以前的版本中不做任何事情。在 3.22 或以后的版本中， CREATE INDEX 被映射到一个 ALTER TABLE 语句来创建索引。查看章节 6.5.4 ALTER TABLE 句法 。

通常，在用 CREATE TABLE 创建表本身时你就创建表的所有索引。查看章节 6.5.3 CREATE TABLE 句法 。 CREATE INDEX 允许你在一个现有表上添加索引。

(col1,col2,...) 格式的列列表创建一个多列索引。索引值由给定的列值连接而成。

对于 CHAR 和 VARCHAR 列，使用 col_name(length) 句法，可以只用一个列的部分来创建索引。(对于 BLOB 和 TEXT 列，长度是必须的。)这里的语句显示使用 name 列的前 10 个字符创建一个索引：

mysql> CREATE INDEX part_of_name ON customer (name(10));

因为，大多数名字通常在前 10 个字符是不一样的，这个索引不应该比以整个 name 创建的索引慢。同样，使用部分列值创建的索引文件会更小一点，这将节省很多磁盘空间，也可以加速 INSERT 操作！

注意，如果你存在使用的是 MySQL 3.23.2 或更新的版本并且是 MyISAM 表类型，这时你才能在一个可以有 NULL 值的列上创建索引，以及在一个 BLOB / TEXT 列上创建索引。

关于 MySQL 如何使用索引的更多信息，查看章节 5.4.3 MySQL 如何使用索引 。

FULLTEXT 索引只能索引 VARCHAR 和 TEXT 列，而且只能应用于 MyISAM 表。 FULLTEXT 索引在 MySQL 3.23.23 和更新的版本中可以使用。查看章节 6.8 MySQL 全文搜索 。

6.5.8 DROP INDEX 句法

DROP INDEX index_name ON tbl_name

DROP INDEX 从表 tbl_name 移除一个名为 index_name 的索引。在 MySQL 3.22 先前的版本中不做任何事情。在 3.22 或以后的版本中， DROP INDEX 被映射到一个 ALTER TABLE 语句来移除索引。查看章节 6.5.4 ALTER TABLE 句法 。

Published At
Categories with 数据库类
comments powered by Disqus