MySQL alter add增加多個字段位置？ modify column語句用法詳解

MySQL的 alter add增加多個字段位置？ modify column語句用法詳解

MySQL的 ALTER指令

當我們需要修改數據表名或者修改數據表字段時，就需要使用到MySQL ALTER命令。

開始本章教程前讓我們先創建一張表，表名為：testalter_tbl。

root@host# mysql -u root -p password;
Enter password:*******
mysql> use chenweiliang;
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)

刪除，添加或修改表字段

如下命令使用了ALTER 命令及DROP 子句來刪除以上創建表的i 字段：

mysql> ALTER TABLE testalter_tbl  DROP i;

如果數據表中只剩餘一個字段則無法使用DROP來刪除字段。

MySQL 中使用ADD 子句來向數據表中添加列，如下實例在表testalter_tbl 中添加i 字段，並定義數據類型:

mysql> ALTER TABLE testalter_tbl ADD i INT;

執行以上命令後，i 字段會自動添加到數據表字段的末尾。

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)

如果你需要指定新增字段的位置，可以使用MySQL提供的關鍵字FIRST (設本土化第一列)， AFTER 字段名（設定位於某個字段之後）。

嘗試以下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 來添加字段並設置位置。

修改字段類型及名稱

如果需要修改字段類型及名稱, 你可以在ALTER命令中使用MODIFY 或CHANGE 子句。

例如，把字段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;

mysql> ALTER TABLE testalter_tbl CHANGE j j INT;

ALTER TABLE 對Null 值和默認值的影響

當你修改字段時，你可以指定是否包含隻或者是否設置默認值。

以下實例，指定字段j 為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)
Changing a Table Type:

修改數據表類型，可以使用ALTER 命令及TYPE 子句來完成。嘗試以下實例，我們將表testalter_tbl 的類型修改為MYISAM ：

注意：查看數據表類型可以使用SHOW TABLE STATUS 語句。

mysql> ALTER TABLE testalter_tbl ENGINE = 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: 2007-06-03 08:04:36
    Update_time: 2007-06-03 08:04: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數據表的索引，該功能我們會在接下來的章節中介紹。

alter其他用途

修改存儲引擎：修改為myisam

alter table tableName engine=myisam;

刪除外鍵約束：keyName是外鍵別名

alter table tableName drop foreign key keyName;

修改字段的相對位置：這裡name1為想要修改的字段，type1為該字段原來類型，first和after二選一，這應該顯而易見，first放在第一位，after放在name2字段後面

alter table tableName modify name1 type1 first|after name2;