MySQL修改数据表

发表于

总结：

1
ALTER TABLE table_name ADD/DROP ...;

添加/删除列

添加单列

如果指定FIRST，则在整个表的最前方，默认不写为整个表的最后方；如果指定AFTER col_name，则在col_name的后面。

1
2
3
ALTER TABLE table_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name];
ALTER TABLE user1 ADD password varchar(32) not NULL AFTER name;

添加多列

无法指定FIRST/AFTER、只能默认为最后方

1
2
3
ALTER TABLE table_name ADD [COLUMN] (col_name column_definition,...);
ALTER TABLE user1 ADD password varchar(32) not NULL,pid smallint unsigned;

删除单列

1
2
3
ALTER TABLE table_name DROP [COLUMN] col_name;
ALTER TABLE user1 DROP password;

删除多列

1
2
3
ALTER TABLE table_name DROP [COLUMN] col_name, 
DROP [COLUMN] col_name,
DROP [COLUMN] col_name;


添加约束

添加/删除主键约束

constraint 给主键起名

1
2
3
4
5
ALTER TABLE table_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...);
ALTER TABLE table_name DROP PRIMARY KEY;
ALTER TABLE users ADD CONSTRAINT pk_users_id PRIMARY KEY (id);
ALTER TABLE users DROP PRIMARY KEY;

添加/删除唯一约束

1
2
3
4
5
6
ALTER TABLE table_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...)
ALTER TABLE table_name DROP {INDEX|KEY} index_name;
//删除唯一约束只用删掉它的索引就行了
ALTER TABLE users ADD UNIQUE (username);
ALTER TABLE users drop INDEX username;

添加/删除外键约束

1
2
3
4
5
6
7
ALTER TABLE table_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name](index_col_name,...) reference_definition;
ALTER TABLE table_name DROP FOREIGN KEY（fk_symbol);
//fk_symbol 是CONSTRAINT定义的名字，如果没有定义可以通过 show create table table_name来查看
ALTER TABLE users ADD FOREIGN KEY (pid) REFERENCES provinces (id);
SHOW TABLE TABLE users;
ALTER TABLE users DROP FOREIGN KEY (pid);

添加/删除默认约束

1
2
3
4
ALTER TABLE table_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
ALTER TABLE users ALTER age SET DEFAULT 15;
ALTER TABLE users ALTER age DROP DEFAULT;


修改列

修改列定义

1
2
3
4
5
6
ALTER TABLE table_name MODIFY [COLUMN] col_name column_definition [FIRST |AFTER col_name];
ALTER TABLE users2 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST; 
//将id字段的位置提到第一列
ALTER TABLE users2 MODIFY id TINYINT UNSIGNED NOT NULL; 
//修改数据类型，需注意数据丢失的问题（范围大转范围小）

修改列名称

1
2
3
4
ALTER TABLE table_name CHANGE [COLUMN] col_name new_col_name column_definition [FIRST|AFTER col_name];
ALTER TABLE users2 CHANGE pid p_id TINYINT UNSIGNED; 
//修改列名称，类型可以通过show create table table_name复制原来的


数据表更名

方法1

1
2
3
ALTER TABLE table_name RENAME [TO/AS] new_table_name;
ALTER TABLE users2 RENAME (to/as) users3;

方法2

1
2
3
RENAME TABLE table_name TO new_table_name [,table_name2 TO new_table_name2] ...;
RENAME TABLE users5 TO users2;

尽量少使用数据表的列名及表名的更名。