mysql alter命令和mysql索引介绍

上一章mysql教程中我们讨论了：事务操作介绍和临时表的使用，本章我们会详细讨论mysql alter命令的使用以及mysql索引。

一、mysql alter命令

当你需要更改表的名称、任何表字段或想要添加或删除表中的现有列时，MySQL alter命令非常有用。

mysql> create table sample
   -> (
   -> i INT,
   -> c CHAR(1)
   -> );
Query OK, 0 rows affected (0.05 sec)
mysql> show columns from sample;
+-------+---------+------+-----+---------+-------+
| Field |  Type   | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
|   i   | int(11) | YES  |     |   NULL  |       |
|   c   | char(1) | YES  |     |   NULL  |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

1、删除、添加或重新定位列

删除一个列如下所示：

mysql> alter table sample drop i;

如果表中只剩下一列drop子句不会起作用，若要添加列请使用add并指定列定义，下面的语句将i列恢复到sample：

mysql> alter table sample add i int;

这样与首次相同的两列结构不同，这是因为默认情况下有新列被添加到表的末尾，所以即使最初是sample的第一列现在它变成是最后一列。

mysql> show columns from sample;
+-------+---------+------+-----+---------+-------+
| Field |  Type   | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
|   c   | char(1) | YES  |     |   NULL  |       |
|   i   | int(11) | YES  |     |   NULL  |       |
+-------+---------+------+-----+---------+-------+

如果希望在表中的特定位置放置列，可以使用first使其成为第一列，也可以使用col_name后的值指示应该将新列放置在col_name之后。

尝试以下alter table语句，在每一个语句之后使用show列来查看每一个语句的效果：

alter table sample drop i;
alter table sample add i int first;
alter table sample drop i;
alter table sample add i int after c;

第一个和后面的说明符只与add子句一起工作，这意味着如果要重新定位表中现有的列，首先必须删除它然后将其添加到新位置。

2、更改列定义或名称

要更改列的定义请使用modify或change子句和alter命令，如要将列c从CHAR(1)更改为CHAR(10)，可以使用以下命令：

mysql> alter table sample modify c CHAR(10);

使用change的语法略有不同，在change关键字之后命名要更改的列，然后指定包含新名称的新定义。

mysql> alter table sample change i j bingint;

如果现在使用change将j从bigint转换回int而不更改列名，则语句将如下所示：

mysql> alter table sample change j j int;

3、更改列的默认值

可以使用alter命令更改任何列的默认值，尝试以下示例。

mysql> alter table sample alter i set default 1000;
mysql> show columns from table sample;
+-------+---------+------+-----+---------+-------+
| Field |  Type   | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
|   c   | char(1) | YES  |     |   NULL  |       |
|   i   | int(11) | YES  |     |   1000  |       |
+-------+---------+------+-----+---------+-------+

也可以通过使用DROP子句和ALTER命令从任何列中删除默认约束。

4、更改表类型

使用type子句和alter命令更改表类型，尝试以下示例将sample更改为MYISAM表类型。

mysql> alter table sample TYPE = MYISAM;

5、重命名一个表

要重命名表请使用alter table语句的rename选项，尝试以下示例将sample重命名为exp。

mysql> alter table sample rename to exp;

二、mysql索引介绍

数据库索引是一种数据结构，它可以提高表的操作速度。可以使用一个或多个列创建索引，这为快速随机查找和对记录的有效访问排序提供了基础。索引也是表的一种类型，它保留主键或索引字段以及指向实际表中每条记录的指针。

在创建索引时，应该考虑哪些列将用于进行SQL查询并在这些列上创建一个或多个索引。用户看不到索引，它们只是用来加速查询，数据库搜索引擎将使用它们来非常快速地定位记录。

INSERT和UPDATE语句在有索引的表上花费的时间更多，而SELECT语句在这些表上的执行速度更快。原因是在执行插入或更新时，数据库还需要插入或更新索引值。

1、简单惟一索引

下面是在表上创建简单唯一索引的语法。

create unique index index_name ON table_name ( column1, column2,...);

你可以使用一个或多个列来创建索引，例如下创建一个索引。

create unique index author_index on sample (author)

2、alter命令添加和删除索引

向表添加索引的语句有四种类型：

• ALTER TABLE tbl_name ADD PRIMARY KEY(column_list)——这个语句添加了一个主键，这意味着索引的值必须是唯一的而且不能为空。
• ALTER TABLE tbl_name ADD UNIQUE index_name (column_list)——该语句创建的索引的值必须是惟一的(NULL值除外，NULL值可能出现多次)。
• ALTER TABLE tbl_name ADD INDEX index_name (column_list)——其中任何值都可以出现多次，简单索引。
• ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)，这会创建一个特殊的全文索引用于文本搜索。

3、alter命令添加和删除主键

下面的代码块是一个在现有表中添加主键的示例，首先使列不为空，然后将其作为主键添加。

mysql> alter table sample MODIFY i INT NOT NULL;
mysql> alter table sample ADD PRIMARY KEY (i);

可以使用SHOW INDEX命令列出与表关联的所有索引，使用垂直格式输出(由\G指定)在此语句中通常很有用，可以避免长行环绕

mysql> show index from table_name\G