修改mysql列定义

本文介绍如何修改mysql的列定义。同时也可以看出change命令和modify命令的区别。

change: 可以更改列名 和 列类型 (每次都要把新列名和旧列名写上, 即使两个列名没有更改,只是改了类型)
modify: 只能更改列属性 只需要写一次列名, 比change 省事点
所以，建议如下：

• 只修改列名
  使用change
• 只修改列的类型
  使用modify
• 同时修改列名和类型
  使用change

具体演示过程如下：

表描述:

mysql> desc test;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(10)  | YES  |     | NULL    |       |
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)

MySQL 中 change 和 modify 区别

更改列名 change: alter table 表名 change 旧列名 新列名 类型

mysql> alter table test change name name_1 int(10);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc test;
+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| id       | int(10) | YES  |     | NULL    |       |
| name_1 | int(10) | YES  |     | NULL    |       |
+----------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

只更改列属性 change: alter table 表名 列名 列名 类型 相同的列名要写两次


mysql> alter table test change  name_1 name_1 char(32);
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc test;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| id       | int(10)  | YES  |     | NULL    |       |
| name_1 | char(32) | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)

更改列属性 modify: alter table 表名 列名 类型


mysql> desc test;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| id       | int(10)  | YES  |     | NULL    |       |
| name_1 | char(32) | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> alter table test modify name_1 int(10);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc test;
+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| id       | int(10) | YES  |     | NULL    |       |
| name_1 | int(10) | YES  |     | NULL    |       |
+----------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)