31、MySQL ALTER 命令

MySQL ALTER 命令可以用来修改数据表名或者修改数据表字段

### **测试数据**

先运行下面的 SQL 语句准备测试数据

你可以有选择性的删除某些语句，我这是重新构建数据库和表

```python
DROP DATABASE IF EXISTS souyunku;
CREATE DATABASE souyunku default character set utf8mb4 collate utf8mb4_unicode_ci;
USE souyunku;
DROP TABLE IF EXISTS tbl_language;
CREATE TABLE IF NOT EXISTS tbl_language(
   id INT UNSIGNED AUTO_INCREMENT,
   name VARCHAR(64) NOT NULL,
   url VARCHAR(128) NOT NULL,
   founded_at DATE,
   PRIMARY KEY ( id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO tbl_language VALUES
    (1,'Python','https://baidu.com','1991-2-20'),
    (2,'PHP','http://www.php.net','1994-1-1'),
    (3,'Ruby','https://www.ruby-lang.org/','1996-12-25'),
    (4,'Kotlin','http://kotlinlang.org/','2016-02-17');
```

### **删除表字段**

------------

`ALTER TABLE tablename DROP fieldname` 命令可以用来删除表中的某个字段

如果表中只剩余一个字段，那么无法使用 `DROP` 来删除字段

比如下面的 SQL 语句可以删除 `tbl_language` 表中的 `founded_at` 字段

```python
ALTER TABLE tbl_language  DROP founded_at;
```

我们先用 `DESC tbl_language;` 命令看看 `tbl_language` 有哪些字段

然后运行 `ALTER TABLE tbl_language DROP founded_at;` 语句删除 `founded_at` 字段
```python
MariaDB [souyunku]> ALTER TABLE tbl_language  DROP founded_at;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

### **添加表字段**

------------

`ALTER TABLE tablename ADD field` 命令可以给一张表添加字段

比如下面的 SQL 命令给表 `tbl_language` 添加一个字段 `url` 并定义了数据类型

```python
ALTER TABLE tbl_language ADD founded_at DATE;
```

```python
MariaDB [souyunku]> ALTER TABLE tbl_language ADD founded_at DATE;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
```

字段又给加回来了

### **指定位置添加字段**

------------

默认情况下字段会添加到数据表字段的末尾

如果需要指定新增字段的位置，可以使用 MySQL 提供的关键字 `FIRST` (设定位第一列)， `AFTER` 字段名 (设定位于某个字段之后)

比如运行以下 `ALTER TABLE` 语句，会发现字段都插在我们想要的位置

```python
ALTER TABLE tbl_language ADD lid int FIRST;
ALTER TABLE tbl_language ADD llid int AFTER url;
ALTER TABLE tbl_language ADD lllid int AFTER lid;
```

执行结果如下

```python
MariaDB [souyunku]> ALTER TABLE tbl_language ADD lid int FIRST;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
MariaDB [souyunku]> ALTER TABLE tbl_language ADD llid int AFTER url;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
MariaDB [souyunku]> ALTER TABLE tbl_language ADD lllid int AFTER lid;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
```

然后使用 `desc tbl_language` 查看表结构，显示如下
```python
MariaDB [souyunku]> desc tbl_language;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| lid        | int(11)          | YES  |     | NULL    |                |
| lllid      | int(11)          | YES  |     | NULL    |                |
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name       | varchar(64)      | NO   |     | NULL    |                |
| url        | varchar(128)     | NO   |     | NULL    |                |
| llid       | int(11)          | YES  |     | NULL    |                |
| founded_at | date             | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
```

`FIRST` 和 `AFTER` 关键字只适用于 `ADD` 子句

如果想重置数据表字段的位置就需要先使用 DROP 删除字段然后使用 ADD 来添加字段并设置位置

### **修改字段类型和其它字段元数据**

------------

`ALTER TABLE tablename MODIFY field` 命令可以用来修改字段类型

例如下面的语句将 `tbl_language` 表中的 `url` 字段的 `VARCHAR(128)` 改成 `VARCHAR(255)`

```python
ALTER TABLE tbl_language MODIFY url VARCHAR(255);
```

因为上面的操作已经把 `tbl_language` 弄乱了，我们执行以下语句重置 `tbl_language` 表
```python
DROP TABLE IF EXISTS tbl_language;
CREATE TABLE IF NOT EXISTS tbl_language(
   id INT UNSIGNED AUTO_INCREMENT,
   name VARCHAR(64) NOT NULL,
   url VARCHAR(128) NOT NULL,
   founded_at DATE,
   PRIMARY KEY ( id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```

然后运行` ALTER TABLE tbl_language MODIFY url VARCHAR(255);` 修改 url 字段

```python
MariaDB [souyunku]> ALTER TABLE tbl_language MODIFY url VARCHAR(255);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
```

发现已经把 `url` 改过来了

### **重命名字段**

------------

`ALTER TABLE tablename MODIFY old_field new_field` 命令可以用来重命名字段

和 `MODIFY` 不一样，`CHANGE` 后面紧跟着的是要修改的字段名，然后指定新字段名及类型

比如下面的语句用于将 `tbl_language` 表中的 `url` 字段改名为 `home`
```python
ALTER TABLE tbl_language CHANGE url home VARCHAR(128);
```
对的，如果只想重命名，也要添加后面哪些数据类型等数据，不然会报错

因为上面的操作已经把 `tbl_language` 弄乱了，我们执行以下语句重置 `tbl_language` 表

```python
DROP TABLE IF EXISTS tbl_language;
CREATE TABLE IF NOT EXISTS tbl_language(
   id INT UNSIGNED AUTO_INCREMENT,
   name VARCHAR(64) NOT NULL,
   url VARCHAR(128) NOT NULL,
   founded_at DATE,
   PRIMARY KEY ( id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```

然后使用 `DESC tbl_language` 查看修改后的表结构

然后运行 `ALTER TABLE tbl_language CHANGE url home VARCHAR(128);` 重命名 `url` 字段为 `home`

```python
ALTER TABLE tbl_language CHANGE url home VARCHAR(128);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
```

### **ALTER TABLE 对 Null 值和默认值的影响**

不管是MODIFY 还是 CHANGE，大家注意 Null 那栏，发现变成了 YES，我们创建的时候还是 NOT NULL 的，这是因为我们修改或者重命名字段的时候没有传递 NOT NULL 所以 MySQL 使用了默认的

也就是说，大家使用 CHANGE 修改的时候，要带上全部的参数，一个没带，就会使用默认的

### **修改字段默认值**

`ALTER TABLE tablename ALTER field SET DEFAULT default_value` 可以修改字段的默认值

例如下面的 SQL 语句 `url` 的默认值由 `NULL` 改成了空字符串(`''`)

```python
ALTER TABLE tbl_language ALTER url SET DEFAULT '';
```

因为上面的操作已经把 `tbl_language` 弄乱了，我们执行以下语句重置 `tbl_language` 表
```python
DROP TABLE IF EXISTS tbl_language;
CREATE TABLE IF NOT EXISTS tbl_language(
   id INT UNSIGNED AUTO_INCREMENT,
   name VARCHAR(64) NOT NULL,
   url VARCHAR(128) NOT NULL,
   founded_at DATE,
   PRIMARY KEY ( id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```

然后运行 `ALTER TABLE tbl_language ALTER url SET DEFAULT '';` 修改 url 的默认值为 `''`
```python
MariaDB [souyunku]> ALTER TABLE tbl_language ALTER url SET DEFAULT '';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
```
然后使用 `DESC tbl_language` 查看修改后的表结构

### **删除字段默认值**

------------

`ALTER TABLE tablename ALTER field DROP DEFAULT` 可以删除字段的默认值

例如下面的 SQL 语句 `url` 的默认值刚刚设置的 `''` 改成了 `NULL`

```python
ALTER TABLE tbl_language ALTER url DROP DEFAULT;
```

然后运行 `ALTER TABLE tbl_language ALTER url DROP DEFAULT;` 删除 url 的默认值
```python
MariaDB [souyunku]> ALTER TABLE tbl_language ALTER url DROP DEFAULT;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
```

### **修改数据表引擎**

`ALTER TABLE tablename ENGINE = engine` 可以修改表的类型(数据库引擎)

例如下面的语句将 tbl_language 的数据表引擎由 InnoDB 改成 MYISAM
```python
ALTER TABLE tbl_language ENGINE = MYISAM;
```
我们先用 `SHOW TABLE STATUS LIKE 'tbl_language'\G;` 查看当前表的元数据

```python
MariaDB [souyunku]> SHOW TABLE STATUS LIKE 'tbl_language'\G;
*************************** 1. row ***************************
           Name: tbl_language
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 1
    Create_time: 2018-04-08 19:07:23
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)
ERROR: No query specified
```
然后运行 `ALTER TABLE tbl_language ENGINE = MYISAM;` 把表引擎改成 `MYISAM`
```python
MariaDB [souyunku]> ALTER TABLE tbl_language ENGINE = MYISAM;
Query OK, 0 rows affected (0.05 sec)               
Records: 0  Duplicates: 0  Warnings: 0
```

最后使用 `SHOW TABLE STATUS LIKE 'tbl_language'\G;` 查看 `tbl_language` 的元数据

```python
MariaDB [souyunku]> SHOW TABLE STATUS LIKE 'tbl_language'\G;
*************************** 1. row ***************************
           Name: tbl_language
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 281474976710655
   Index_length: 1024
      Data_free: 0
 Auto_increment: 1
    Create_time: 2018-04-08 19:13:34
    Update_time: 2018-04-08 19:13:34
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)
ERROR: No query specified
```
### **修改表名**

------------

如果需要修改数据表的名称，可以在 `ALTER TABLE` 语句中使用 `RENAME` 子句来实现

先在mysql> 终端中运行以下语句查看当前的所有表

```python
SHOW TABLES;

然后运行下面的 SQL 语句将 `tbl_language` 修改成 `tbl_lang`

```python
ALTER TABLE tbl_language RENAME TO tbl_lang;
```

执行结果如下

```python
MariaDB [souyunku]> ALTER TABLE tbl_language RENAME TO tbl_lang;
Query OK, 0 rows affected (0.02 sec)
```
然后再使用 `SHOW TABLES` 命令查看有哪些表
```python
MariaDB [souyunku]> SHOW TABLES;
+----------------+
| Tables_in_souyunku |
+----------------+
| tbl_lang       |
| tbl_rank       |
+----------------+
2 rows in set (0.00 sec)
```
发现表已经被重命名了

## **ALTER 修改索引**

------------

ALTER 命令还可以用来创建及删除MySQL数据表的索引，请看下一篇