mysql运维改表_修改表 - 凌乱的运维 - 博客园

修改表是指修改数据库中已存在的表的定义。比重新定义表简单，不需要重新加载数据，也不会影响正在进行的服务。MySQL 中通过ALTER  TABLE语句来修改表。修改表包括修改表名、修改字段数据类型、修改字段名、增加字段、删除字段、修改字段的排列位置、更改默认存储引擎和删除表的外键约束等。

先查看example0表，之后的操作都针对这张表，我们可以观察其中变化。

mysql> DESC example0;

也可以使用语句： mysql> SHOW CREATE TABLE example0 \G

1、修改表名

表名可以在一个数据库中唯一的确定一张表。数据库系统通过表名来区分不同的表。

语法形式：  ALTER  TABLE  旧表名RENAME  [TO]新表名;

mysql> ALTER TABLE example0 RENAME user;  将example0表改名为user。

2、修改字段的数据类型

字段的数据类型包括整数型、浮点数型、字符串型、二进制类型、日期和时间类型等。数据类型决定了数据的存储格式、约束条件和有效范围。

基本语法： ALTER TABLE  表名MODIFY属性名  数据类型;

其中，表名 参数指所要修改的表的名称；属性名  参数指需要修改的字段的名称； 数据类型 参数指修改后的新数据类型。

mysql> ALTER TABLE user MODIFY name VARCHAR(30); 将user表中name字段的数据类型变为VARCHAR(30)。

3、修改字段名

字段名可以在一张表中唯一的确定一个字段。数据库系统通过字段名来区分表中的不同字段。

基本语法： ALTER  TABLE  表名CHANGE旧属性名  新属性名  新数据类型；

其中，旧属性名 参数指修改前的字段名；新属性名 参数指修改后的字段名；新数据类型 参数修改后的数据类型，如不需要修改，则将新数据类型设置与原来一样。

mysql> ALTER TABLE user CHANGE id stu_id INT(2); 将user表中id字段名改为stu_id，数据类型改为INT(2)。

如果只修改字段的数据类型，可以把旧属性名和新属性名都写成旧属性名。

4、增加字段

在创建表时，表中的字段就已经定义完成。如果增加新的字段，可以通过 ALTER TABLE 语句进行增加。

基本语法： ALTER  TABLE  表名ADD属性名1数据类型[完整性约束条件]  [FIRST  |  AFTER属性名2];

其中， 属性名1  参数是指需要增加的字段的名称； 数据类型  参数指新增加字段的数据类型； 完整性约束条件  是可选参数，用来设置新增字段的完整性约束条件； FIRST  是可选参数，作用是将新增字段设置为表的第一个字段； ALTER 属性名2参数也是可选参数，作用是将新增字段添加到  属性名2  所指的字段后。如果没有FIRST和AFTER属性名2参数指定新增字段的位置，新增字段默认为表的最后一个字段。

mysql> ALTER TABLE user ADD num INT(8) PRIMARY KEY FIRST; 在user表的第一个位置增加字段

mysql> ALTER TABLE user ADD address VARCHAR(30) NOT NULL AFTER num;   在user表的num字段后增加address字段

对于一个表而言，其中字段排列顺序对表不会有什么影响。但对于创建人来说，将某种直接或间接关系的字段放一块，更好理解。

5、删除字段

删除字段是指删除已经定义好的表中的某个字段。

基本语法：  ALTER  TABLE  表名DROP属性名;其中，属性名  参数指需要从表中删除的字段的名称。

mysql> ALTER TABLE user ADD phone VARCHAR(20) NOT NULL; 在user表中增加一个字段，默认为表的最后一个字段。

mysql> ALTER TABLE user DROP phone; 在user表中删除字段phone。

6、修改字段的排列位置

创建表的时候，字段在表中的排列位置就已经确定，如果要改变字段在表的排列位置，使用 ALTER  TABLE  语句来处理。

基本语法： ALTER  TABLE  表名MODIFY属性名1数据类型FIRST | AFTER属性名2;

其中，属性名1  参数指需要修改位置的字段的名称； 数据类型  参数指  属性名1的数据类型；FIRST参数指定位置为表的第一个位置；AFTER属性名2参数指定  属性名1插入在  属性名2之后。

mysql> ALTER TABLE user MODIFY name VARCHAR(30) FIRST; 在user表中将字段user修改到第一个位置

mysql> ALTER TABLE user MODIFY sex TINYINT(1) AFTER name; 在user表中将字段sex修改到name字段后面

7、更改表的存储引擎

MySQL存储引擎是指MySQL数据库中表的存储类型。MySQL存储引擎包括InnoDB、MyISAM、MEMORY等。不同的表类型有着不同的优缺点。在创建表时，存储引擎就已经设定好了，使用ALTER  TABLE语句可以更改表的存储引擎的类型。

基本语法：  ALTER  TABLE  表名ENGINE=存储引擎名; 其中，存储引擎名  参数指设置新的存储引擎的名称。

mysql> ALTER TABLE user ENGINE=MyISAM; 将user表存储引擎改为MyISAM

注意：使用ALTER语句可以改变表的存储引擎，避免重新创建表，但如果表中数据很多，改变存储引擎可能会造成一些意料之外的影响，如果一个表中已经存在很多数据，最好不要轻易更改其存储引擎。

8、删除表的外键约束

外键是一个特殊的字段，其将某一表与其父表建立关联关系。在创建表的时候，外键约束就已经设定好了。由于特殊需要，与父表之间的关联关系需要去除时，要求删除外键约束，使用 ALTER TABLE 语句删除表的外键约束。

基本语法： ALTER  TABLE  表名DROP  FOREIGN  KEY外键别名; 其中，外键别名  参数指创建表时设置的外键的代号

mysql> SHOW CREATE TABLE example3 \G 查看example3表详细结构

*************************** 1. row ***************************

Table: example3

Create Table: CREATE TABLE `example3` (

`id` int(11) NOT NULL,

`stu_id` int(11) DEFAULT NULL,

`course_id` int(11) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `c_fk` (`stu_id`,`course_id`),

CONSTRAINT `c_fk` FOREIGN KEY (`stu_id`, `course_id`) REFERENCES `example2` (`stu_id`, `course_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

从查询结果中可以看出，example3表的外键别名为c_fk。

mysql> ALTER TABLE example3 DROP FOREIGN KEY c_fk; 删除example3表的外键(FOREIGN KEY)约束