Mysql修改字段与修改表操作

项目github地址：bitcarmanlee easy-algorithm-interview-and-practice
欢迎大家star，留言，一起学习进步

当我们需要针对mysql的表名或者字段名需要修改的时候，这个时候就需要用到mysql的alter命令。
为了方便起见，首先我们新建一张用户表。

create table user(
`id` INT NOT NULL AUTO_INCREMENT,
`court_id` INT NOT NULL DEFAULT 1,
`user_id` INT NOT NULL DEFAULT 0,
PRIMARY KEY(`id`)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

1. 添加删除字段

首先如果我们想删除user_id字段：

mysql> alter table user drop user_id;
Query OK, 0 rows affected (0.26 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user;
+----------+---------+------+-----+---------+----------------+
| Field    | Type    | Null | Key | Default | Extra          |
+----------+---------+------+-----+---------+----------------+
| id       | int(11) | NO   | PRI | NULL    | auto_increment |
| court_id | int(11) | NO   |     | 1       |                |
+----------+---------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

注意：如果数据表中只剩余一个字段则无法使用DROP来删除字段。

我们发现user_id删除错误，想添加回来：

mysql> alter table user add user_id int;
Query OK, 0 rows affected (0.25 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user;
+----------+---------+------+-----+---------+----------------+
| Field    | Type    | Null | Key | Default | Extra          |
+----------+---------+------+-----+---------+----------------+
| id       | int(11) | NO   | PRI | NULL    | auto_increment |
| court_id | int(11) | NO   |     | 1       |                |
| user_id  | int(11) | YES  |     | NULL    |                |
+----------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

可以看出来，此时user_id自动添加到了表的最后。

如果我们想指定新增字段的位置，可以使用MySQL提供的关键字 FIRST (设定位第一列)， AFTER 字段名（设定位于某个字段之后）。

mysql> alter table user drop user_id;
Query OK, 0 rows affected (0.30 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table user add user_id int first;
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user;
+----------+---------+------+-----+---------+----------------+
| Field    | Type    | Null | Key | Default | Extra          |
+----------+---------+------+-----+---------+----------------+
| user_id  | int(11) | YES  |     | NULL    |                |
| id       | int(11) | NO   | PRI | NULL    | auto_increment |
| court_id | int(11) | NO   |     | 1       |                |
+----------+---------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

此时first已经指定user_id为第一个字段。

mysql> alter table user drop user_id;
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table user add user_id int after id;
Query OK, 0 rows affected (0.36 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user;
+----------+---------+------+-----+---------+----------------+
| Field    | Type    | Null | Key | Default | Extra          |
+----------+---------+------+-----+---------+----------------+
| id       | int(11) | NO   | PRI | NULL    | auto_increment |
| user_id  | int(11) | YES  |     | NULL    |                |
| court_id | int(11) | NO   |     | 1       |                |
+----------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

此时可以看到，user_id位于id后面了。

2.修改字段的名称类型

ALTER命令中使用 MODIFY 或 CHANGE 子句能满足以上需求。

mysql> alter table user modify court_id char(16);
Query OK, 0 rows affected (0.25 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user;
+----------+----------+------+-----+---------+----------------+
| Field    | Type     | Null | Key | Default | Extra          |
+----------+----------+------+-----+---------+----------------+
| id       | int(11)  | NO   | PRI | NULL    | auto_increment |
| user_id  | int(11)  | YES  |     | NULL    |                |
| court_id | char(16) | YES  |     | NULL    |                |
+----------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

change的语法比较奇葩。请看下面的例子

mysql> alter table user change court_id court_id int;
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user;
+----------+---------+------+-----+---------+----------------+
| Field    | Type    | Null | Key | Default | Extra          |
+----------+---------+------+-----+---------+----------------+
| id       | int(11) | NO   | PRI | NULL    | auto_increment |
| user_id  | int(11) | YES  |     | NULL    |                |
| court_id | int(11) | YES  |     | NULL    |                |
+----------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，必须要指定新字段名及类型，即使字段名不变！

3.修改字段的默认值

为字段添加默认值

mysql> alter table user alter court_id set default 1;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user;
+----------+---------+------+-----+---------+----------------+
| Field    | Type    | Null | Key | Default | Extra          |
+----------+---------+------+-----+---------+----------------+
| id       | int(11) | NO   | PRI | NULL    | auto_increment |
| user_id  | int(11) | YES  |     | NULL    |                |
| court_id | int(11) | YES  |     | 1       |                |
+----------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

删除某个字段的默认值

mysql> alter table user alter court_id drop default;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user;
+----------+---------+------+-----+---------+----------------+
| Field    | Type    | Null | Key | Default | Extra          |
+----------+---------+------+-----+---------+----------------+
| id       | int(11) | NO   | PRI | NULL    | auto_increment |
| user_id  | int(11) | YES  |     | NULL    |                |
| court_id | int(11) | YES  |     | NULL    |                |
+----------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

4.修改表名

可以在 ALTER TABLE 语句中使用 RENAME 子句来实现修改表名的目的。

mysql> alter table user rename to user_test;
Query OK, 0 rows affected (0.05 sec)

##5.修改表的引擎
可以在ALTER TABLE中设置ENGINE属性，得到改变数据表引擎的目的。

mysql> alter table user_test ENGINE = MYISAM;
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show table status like 'user_test'\G
*************************** 1. row ***************************
           Name: user_test
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 3659174697238527
   Index_length: 1024
      Data_free: 0
 Auto_increment: 1
    Create_time: 2018-06-05 15:36:17
    Update_time: 2018-06-05 15:36:17
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)