mysql表结构

添加列

alter table 表名 add 列名 列类型 列参数 first【把新列加在最前面】

alter table 'RepairLog' add 'RepairID' int not null auto_increment first; 

ALTER TABLE RepairLog ADD RepairID int NOT NULL FIRST 

alter table 表名 add 列名 列类型 列参数 after 某列【把新列加在某列后面】

mysql> ALTER TABLE RepairLog ADD BugTime DATETIME AFTER BugFan;
Query OK, 3 rows affected (0.25 sec)
Records: 3  Duplicates: 0  Warnings: 0

可以看一下，添加列之前的表结构

mysql> desc RepairLog;
+---------------+---------+------+-----+---------+----------------+
| Field         | Type    | Null | Key | Default | Extra          |
+---------------+---------+------+-----+---------+----------------+
| RepairID      | int(11) | NO   | PRI | NULL    | auto_increment |
| BugFan        | int(11) | NO   | UNI | NULL    |                |
| RepairTime    | date    | NO   | UNI | NULL    |                |
| BugName       | text    | NO   |     | NULL    |                |
| BugCode       | int(11) | NO   | UNI | 0       |                |
| BugReason     | text    | NO   |     | NULL    |                |
| RepairContent | text    | NO   |     | NULL    |                |
| spconsumption | int(11) | NO   |     | 0       |                |
+---------------+---------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

添加成功后，看一下表结构

给主键增加一个自增的功能

mysql> alter table user modify id integer auto_increment ; 

alter table RepairLog modify `RepairID` integer primary key  auto_increment ;

去掉主键

如果没有设置为自增长（auto_increment），那么可以直接删除主键（primary key ）;

句式：alter table +表名+drop primary key;

mysql> alter table RepairLog drop primary key;
Query OK, 1 row affected (0.27 sec)
Records: 1  Duplicates: 0  Warnings: 0


mysql> alter table RepairLog modify `RepairID` int primary key  auto_increment ;Query OK, 1 row affected (0.30 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> desc RepairLog;
+---------------+---------+------+-----+---------+----------------+
| Field         | Type    | Null | Key | Default | Extra          |
+---------------+---------+------+-----+---------+----------------+
| RepairID      | int(11) | NO   | PRI | NULL    | auto_increment |
| RepairFan     | int(11) | NO   | UNI | NULL    |                |
| RepairTime    | date    | NO   | UNI | NULL    |                |
| BugName       | text    | NO   |     | NULL    |                |
| BugCode       | int(11) | NO   | UNI | 0       |                |
| BugReason     | text    | YES  |     | NULL    |                |
| RepairContent | text    | NO   |     | NULL    |                |
| spconsumption | int(11) | NO   |     | 0       |                |
+---------------+---------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

删除unique key

alter table 表名 drop index 列名

mysql> alter table RepairLog drop index RepairTime;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

Tip:当该列存在数据时，不能进行删除操作

source

• MySql表结构修改详解

输出

• 故障处理开发流程.docx