DDL語句--修改表

修改表是指修改數據庫中已經存在的表的定義。修改表比重新定義表簡單，不需要重新加載數據，也不會影響正在進行的服務。

MySQL中通過ALTER TABLE語句來修改表，修改表包括修改表名，修改字段數據類型，修改字段名，增加字段，刪除字段，修改字段的排列位置，更改默認存儲引擎和刪除表的外鍵約束等。

OK，這篇博客會詳細的整理到上面的幾種修改表的方式。現在我們開始。

1，修改表名

表名可以在一個數據庫中唯一的確定一張表。數據庫系統通過表名來區分不同的表。注意的是，數據庫中的表是唯一的，數據庫中不可能存在兩張相同的表。

MySQL中通過sql語句ALTER TABLE來實現表名的修改，語法形式如下：

ALTER TABLE 舊表名 RENAME [TO] 新表名;

其中，‘舊表名’參數表示修改前的表名，‘新表名’參數表示修改後的新表名，TO參數是可選參數，這個屬性是否在語句中出現不會影響語句的執行。rename的意思就是重命名，所以這個挺好記的。

OK，現在我們來實際操作下數據庫。我們現在數據庫中創建一張表，建表語句如下：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(5) COLLATE utf8_bin NOT NULL,
  `card_id` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `age` int(11) DEFAULT '25',
  PRIMARY KEY (`id`),
  UNIQUE KEY `age_UNIQUE` (`card_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

現在我們修改下上面user表的表名：

ALTER TABLE linkinframe.user RENAME linkinframe.usr;

OK，修改成功，我們查看下新的表，表結構和之前的表結構一致，裡面的數據與之前的數據也一致。

2，修改字段的數據類型

字段的數據類型包括整數型，浮點數型，字符串型，二進制類型，日期和時間類型等。數據類型決定了數據的存儲格式，約束條件和有效范圍。表中的每個字段都有數據類型。

MySQL中，ALTER TABLE語句也可以修改字段的數據類型。基本語法如下：

ALTER TABLE 表名 MODIFY 屬性名 數據類型;

其中，‘表名’參數指所要修改的表的名稱，‘屬性名’參數指需要修改的字段的名稱，‘數據類型’參數值修改後的新的數據類型。

OK，現在我們來實際操作下MySQL，首先我們建一張user表，建表語句如下：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(5) COLLATE utf8_bin NOT NULL,
  `card_id` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `age` int(11) DEFAULT '25',
  PRIMARY KEY (`id`),
  UNIQUE KEY `age_UNIQUE` (`card_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

現在我們修改name字段的長度為varhcar(10)，執行如下的sql語句來修改：

ALTER TABLE linkinframe.`user` MODIFY name varchar(10) NOT NULL;

修改後我們查看下數據庫現在表結構，成功修改了name屬性的長度了。

3，修改字段名

字段名是一張表中唯一的一個字段。數據庫系統通過字段名來區分表中的不同字段。MySQL中，ALTER TABLE語句也可以修改表的字段名，基本語法如下：

ALTER TABLE 表名 CHANGE 舊屬性名 新屬性名 新數據類型;

其中，‘舊屬性名’參數指修改前的字段名，‘新屬性名’參數指修改後的字段名，‘新數據類型’參數指修改後的數據類型，如不需要修改，則將新數據類型設置成與原來的一樣。


具體有2種情況：

1），只修改字段名

使用ALTER TABLE 語句直接修改字段名，不改變字段的數據類型。建表語句：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) COLLATE utf8_bin NOT NULL,
  `card_id` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `age` int(11) DEFAULT '25',
  PRIMARY KEY (`id`),
  UNIQUE KEY `age_UNIQUE` (`card_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

現在我們修改name字段為username字段，執行如下語句來修改：

ALTER TABLE linkinframe.`user` CHANGE name username varchar(10) NOT NULL;

修改後的表結構如下：

當然其中的數據也沒有變化：

2），修改字段名和字段數據類型

使用ALTER TABLE語句直接修改字段名和該字段的數據類型。建表語句：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(5) COLLATE utf8_bin NOT NULL,
  `card_id` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `age` int(11) DEFAULT '25',
  PRIMARY KEY (`id`),
  UNIQUE KEY `age_UNIQUE` (`card_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

現在我們修改name字段為username字段，然後我們將name原來的長度修改成為10，執行如下語句來修改：

ALTER TABLE linkinframe.`user` CHANGE name username varchar(10) NOT NULL;

修改後的表結構如下：

3），總結

MODIFY和CHANGE都可以改變字段的數據類型。不同的是：

1，CHANGE可以在改變字段數據類型的同時，改變字段名。MODIFY只能用來改變字段的數據類型，不能修改字段名。

2，CHANGE如果不改變字段名，只修改字段類型，CHAGE後面必須跟兩個同樣的字段名。

3，養成良好的習慣，如果是指修改字段的數據結構就使用MODIFY，如果要修改字段名+數據結構就使用CHANGE。

4，在修改表的字段過程中，如果表中已經有記錄，修改數據類型時應該特別小心。因為，修改數據類型時可能會影響表中的數據。

5，特別注意的是，字符類型的字段最好不要改成整數類型，浮點數類型。

4，增加字段

在創建表時，表中的字段就已經定義完成。如果要增加新的字段，可以通過ALTER TABLE語句進行增加，在MySQL中，ALTER TBALE語句增加字段的基本語法如下：

ALTER TABLE 表名 ADD 屬性名1 數據類型 [完整性約束條件] [FIRST | AFTER 屬性名2];

其中，‘屬性名1’參數指需要增加的字段的名稱，‘數據類型’參數指新增加字段的數據類型，‘完整性約束條件’是可選參數，用來設置新增字段的完整性約束條件；‘FIRST’參數也是可選參數，其作用是將新增字段設置為表的第一個字段，‘AFTER 屬性名

2’參數也是可選參數，其作用是將新增字段添加到‘屬性名2’所指的字段後。如果執行的sql語句中沒有‘FIRST’，‘AFTER 屬性名2’參數指定新增字段的位置，新增的字段默認為表的最後一個字段。

 

具體有如下4種情況：

1），增加無完整性約束條件的字段

一個完整的字段包括字段名，數據類型和完整性約束條件，增加字段一般都是包括上述內容。當然，根據實際情況，一些字段也可以不用完整性約束條件進行約束。

現在我們來實際操作下，數據庫建表語句如下：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(5) COLLATE utf8_bin NOT NULL,
  `card_id` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `age` int(11) DEFAULT '25',
  PRIMARY KEY (`id`),
  UNIQUE KEY `age_UNIQUE` (`card_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

現在我們user表中添加一個phone字段：

ALTER TABLE linkinframe.`user` ADD phone varchar(11);

OK，添加字段成功，這裡沒有設置‘FRIST’和‘AFTER 屬性名2’參數來指定插入位置，所以新增字段默認為表的最後一個字段。

2），增加有完整性約束條件的字段

增加字段時可以設置該字段的完整性約束條件，如設置字段是否為空，是否為主鍵，是否為外鍵，默認值和是否為自增類型等約束條件。

現在我們來實際操作下，數據庫建表語句如下：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(5) COLLATE utf8_bin NOT NULL,
  `card_id` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `age` int(11) DEFAULT '25',
  PRIMARY KEY (`id`),
  UNIQUE KEY `age_UNIQUE` (`card_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

現在我們user表中添加一個phone字段：

ALTER TABLE linkinframe.`user` ADD phone varchar(11) NOT NULL;

OK，添加字段成功，這裡沒有設置‘FRIST’和‘AFTER 屬性名2’參數來指定插入位置，所以新增字段默認為表的最後一個字段。

3），表的第一個位置增加字段

默認情況下，新增字段為表的最後一個字段。如果加上FIRST參數，則可以將新增字段設置為表的第一個字段。
現在我們來實際操作下，數據庫建表語句如下：

CREATE TABLE linkinframe.`user` (
    `name` varchar(5) COLLATE utf8_bin NOT NULL,
  `card_id` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `age` int(11) DEFAULT '25',
	UNIQUE KEY `age_UNIQUE` (`card_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

現在我們user表中添加一個id字段，然後設置id為user表的主鍵，而且放到表中的第一個位置上。

ALTER TABLE linkinframe.`user` ADD `id` int(11) primary KEY AUTO_INCREMENT first;

OK，添加成功，而且成功的放到了表中的第一個字段上面了。

4），表的指定位置之後增加字段

在增加字段時，由於特殊原因需要在表的指定位置增加字段，如果加上‘AFTER 屬性名2’參數，那麼新增的字段插入在‘屬性名2’後面。

現在我們來實際操作下，數據庫建表語句如下：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(5) COLLATE utf8_bin NOT NULL,
  `card_id` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `age` int(11) DEFAULT '25',
  PRIMARY KEY (`id`),
  UNIQUE KEY `age_UNIQUE` (`card_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

現在我們user表中name字段之後添加一個phone字段：

ALTER TABLE linkinframe.`user` ADD phone varchar(11) after name;

OK，添加字段成功，表中phone字段成功的放到了name字段之後。

總結：

1，增加字段時，如果能夠加上完整性約束條件，一定要加上。這樣可以保證此字段的安全性，甚至可以提高整個表的數據的安全性。

2，對於一個數據庫來說，字段的排列順序對表不會有什麼影響。但是對於創建表的人來說，將有某種直接或者間接關系的字段放在一起，會更加好的理解這個表的結構，這其實也是一種習慣，我們以後也要盡量將有關系的字段放在一起，方便我們查看。

5，刪除字段

刪除字段是指刪除已經定義好的表中的某個字段，在表創建完成之後，如果發現某個字段需要刪除，可以采用將整個表都刪除，然後重新創建一張表的做法。這樣做是可以達到目的的，但必然會影響到表中的數據，而且操作比較麻煩。

MySQL中，ALTER TABLE語句也可以刪除表中的字段，語法如下：

ALTER TABLE 表名 DROP 屬性名;

其中，‘屬性名’參數指需要從表中刪除的字段的名稱。

現在我們來實際操作下，數據庫建表語句如下：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(5) COLLATE utf8_bin NOT NULL,
  `card_id` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `age` int(11) DEFAULT '25',
  PRIMARY KEY (`id`),
  UNIQUE KEY `age_UNIQUE` (`card_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

現在我們user表中刪除掉name字段：

ALTER TABLE linkinframe.`user` DROP name;

OK，成功刪除了。

6，修改字段的排列位置

創建表的時候，字段在表中的排列位置都已經確定了，如果要改變字段在表中的排列位置，也可以通過ALTER TABLE來處理。MySQL中，修改字段排列位置的ALTER TABLE語句的基本語法如下：

ALTER TABLE 表名 MODIFY 屬性名1 數據類型 FIRST|AFTER 屬性名2;

其中，‘屬性名1’參數指需要修改位置的字段的名稱，‘數據類型’參數指‘屬性名1’的數據類型，‘FIRST’參數指定位置為表的第一個位置，‘AFTER 屬性名2’參數指定‘屬性名1’插入到‘屬性名2’之後。

具體的分2種情況：

1），字段修改到第一個位置

FIRST參數可以指定字段為表的第一個字段。

現在我們來實際操作下，數據庫建表語句如下：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(5) COLLATE utf8_bin NOT NULL,
  `card_id` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `age` int(11) DEFAULT '25',
  PRIMARY KEY (`id`),
  UNIQUE KEY `age_UNIQUE` (`card_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

現在我們user表中的name字段修改到數據庫的第一個位置上去：

ALTER TABLE linkinframe.`user` modify name varchar(5) first;

OK，顯示name字段已經到了user表的第一個位置上了。

2），字段修改到指定位置

‘AFTER’參數可以將字段排在表中指定的字段之後。

現在我們來實際操作下，數據庫建表語句如下：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(5) COLLATE utf8_bin NOT NULL,
  `card_id` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `age` int(11) DEFAULT '25',
  PRIMARY KEY (`id`),
  UNIQUE KEY `age_UNIQUE` (`card_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

現在我們user表中的name字段修改到age之後去:

ALTER TABLE linkinframe.`user` modify name varchar(5) after age;

OK，顯示name字段已經到了user表中age之後了。

總結：

修改字段的排列位置其實也就是修改表的字段，這裡由於沒有修改字段名，所以我們可以使用MODIFY來操作字段的位置，當然我們也可以使用CHANGE來操作字段的位置，只不過這個時候屬性名要寫2遍而已。

比如說我現在不自己寫sql，然後使用MySQL的客戶端來調整下表中的2字段的位置使用的就是CHANGE。

ALTER TABLE `linkinframe`.`user` 
CHANGE COLUMN `name` `name` VARCHAR(5) CHARACTER SET 'utf8' NULL DEFAULT NULL AFTER `id`;

7，更改表的存儲引擎

MySQL存儲引擎是指MySQL數據庫中表的存儲類型。MySQL存儲引擎包括InnoDB，MyISAM，Memory等。不同的表類型有著不同的優缺點，在前面的博客中我也已經整理到了。

在創建表時，存儲引擎就已經設定好了。如果要改變，可以通過重新創建一張表來實現。這麼做是可以達到目的的，但必然會影響到表中的數據，而且操作比較麻煩。

MySQL中，ALTER TABLE語句也可以更改表的存儲引擎的類型，其基本語法如下：

ALTER TABLE 表名 ENGINE=存儲引擎名

其中，‘存儲引擎名’參數指設置的新的存儲引擎的名稱。

現在我們來實際操作下，數據庫建表語句如下：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(5) COLLATE utf8_bin NOT NULL,
  `card_id` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `age` int(11) DEFAULT '25',
  PRIMARY KEY (`id`),
  UNIQUE KEY `age_UNIQUE` (`card_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

現在我們user表中的存儲引擎設置為MyISAM：

ALTER TABLE linkinframe.`user` ENGINE = MyISAM;

OK，現在我們SHOW CREATE TABLE看下user的表結構，現在user表的存儲引擎成功的變成了MyISAM。
注意：使用Alter語句可以改變表的存儲引擎，這可以避免重新創建表。但是，如果表中已經有很多的數據，改變存儲引擎可能會造成一些意料之外的影響。如果一個表中已經存在了很多數據，最好不要輕易更改其存儲引擎。

8，刪除表的外鍵約束

外鍵是一個特殊字段，其將某一表與其父表建立關聯關系。在創建表的時，外鍵約束就已經設定好了。由於特殊需要，與父表之間的關聯關系需要去除，要求刪除外鍵約束。

MySQL中，ALTER TABLE語句也可以刪除表的外鍵約束。其基本語法如下：

 

ALTER TABLE 表名 DROP FOREIGN KEY 外鍵別名：

 

其中，‘外鍵別名’參數指創建表時設置的外鍵的代號。

現在我們來實際操作下，數據庫建表語句如下：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(5) COLLATE utf8_bin NOT NULL,
  `card_id` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `age` int(11) DEFAULT '25',
  PRIMARY KEY (`id`),
  UNIQUE KEY `age_UNIQUE` (`card_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
CREATE TABLE `address` (
  `id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `name` varchar(45) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id_idx` (`user_id`),
  CONSTRAINT `user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

現在我們刪除掉從表address的外鍵關聯，執行如下sql：

ALTER TABLE linkinframe.address DROP foreign key user_id;

OK，查詢結果顯示從表address的外鍵已經不存在了。