MySQL Alter命令

发布于 2015-07-31 16:33:54 | 279 次阅读 | 评论: 0 | 来源: 网络整理

当想改变表名，MySQL的ALTER命令非常有用, 在添加或删除任何表字段到现有列在表中。

让我们开始创建一个表为 testalter_tbl。

root@host# mysql -u root -p password;
Enter password:
mysql> use test;
Database changed
mysql> create table testalter_tbl
    -> (
    -> i INT,
    -> c CHAR(1)
    -> );
Query OK, 0 rows affected (0.05 sec)
mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i     | int(11) | YES  |     | NULL    |       |
| c     | char(1) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

删除，添加或重新定义列

假设想要从上面MySQL的表中删除一个现有列，那么使用ALTER命令以及DROP子句如下：

mysql> ALTER TABLE testalter_tbl  DROP i;

如果在表中列是唯一的一个，那么DROP将无法正常工作。

要添加列，使用ADD并指定列定义。下面的语句将存储到 i 列：testalter_tbl 

mysql> ALTER TABLE testalter_tbl ADD i INT;

发出这条语句后，testalter将包含当第一次创建表时的两列, 但不会有完全一样的结构。这是因为新的列被添加到表中时，默认排在最后一列。因此，即使 i 列原来是 mytbl 的第一列，但现在它是最后一列。

mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

若要指示列在表中的特定位置，要么使用FIRST把它放在第一列或AFTER col_name 来指定新的列应放置col_name之后。 试试下面的ALTER TABLE语句，使用 SHOW COLUMNS 看看有什么影响：

ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT FIRST;
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT AFTER c;

FIRST 和 AFTER 只能在ADD子句中使用。这意味着，如果你想在表中重新排位现有列，首先必须删除(DROP )它，然后在新的位置添加(ADD)它。

更改列定义或名称

要改变列的定义，使用MODIFY 或CHANGE 子句以及ALTER命令。 例如, 要改变字段 c 从 CHAR(1) 修改为 CHAR(10), 那么可以这样做：

mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10);

CHANGE语法可能有点不同。CHANGE关键字后的名称是要修改的列，然后指定新的定义，其中包括新的名称。试试下面的例子：

mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;

如果现在使用CHANGE转换i列从BIGINT为INT，但不改变列名，该语句执行预期：

mysql> ALTER TABLE testalter_tbl CHANGE j j INT;

ALTER TABLE影响Null和缺省值属性

当使用 MODIFY 或 CHANGE修改列，还可以指定该列是否能为NULL值以及它的默认值。事实上，如果你不这样做，MySQL也会自动分配这些属性值。

下面是一个例子，这里 NOT NULL列将使用100作为默认值。

mysql> ALTER TABLE testalter_tbl 
    -> MODIFY j BIGINT NOT NULL DEFAULT 100;

如果不使用上面的命令，那么MySQL将填补使用NULL 来填充所有列值。

更改列的默认值

可以使用ALTER命令更改任何列的默认值。尝试下面的例子。

mysql> ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | YES  |     | 1000    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

从任何一列删除默认的约束，可以使用ALTER命令以及DROP子句。

mysql> ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

更改表类型

可以通过使用ALTER命令以及TYPE子句修改表的类型。试试下面的例子，将 testalter_tbl 的类型更改为MyISAM 表类型。

若想要知道一个表的当前类型，那么可使用 SHOW TABLE STATUS 语句。

mysql> ALTER TABLE testalter_tbl TYPE = MYISAM;
mysql>  SHOW TABLE STATUS LIKE 'testalter_tbl'G
*************************** 1. row ****************
           Name: testalter_tbl
           Type: MyISAM
     Row_format: Fixed
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 25769803775
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2015-06-03 18:35:36
    Update_time: 2015-06-03 18:35:36
     Check_time: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

重命名表

要重命名表，使用ALTER TABLE语句的RENAME选项。试试下面的例子是用来重命名testalter_tbl为alter_tbl。

mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;

可以使用ALTER命令来创建并在MySQL的文件删除索引。我们将在下一章看到此功能。