MySQL中的alter table敕令的根本應用辦法及提速優化。本站提示廣大學習愛好者：（MySQL中的alter table敕令的根本應用辦法及提速優化）文章只能為提供參考，不一定能成為您想要的結果。以下是MySQL中的alter table敕令的根本應用辦法及提速優化正文
1、根本用法
1. 增長列
alter table tbl_name add col_name type
例如, 給pet的表增長一列 weight,
mysql>alter table pet add weight int;
2. 刪除列
alter table tbl_name drop col_name
例如, 刪除pet表中的weight這一列
mysql>alter table pet drop weight;
3. 轉變列
分為轉變列的屬性和轉變列的名字
轉變列的屬性——辦法1：
alter table tbl_name modify col_name type
例如，轉變weight的類型
mysql>alter table pet modify weight varchar(30);
轉變列的屬性——辦法2：
alter table tbl_name change old_col_name col_name type
例如，轉變weight的類型
alter table pet change weight weight varchar(30);
轉變列的名字：
alter table tbl_name change old_col_name col_name
例如轉變pet表中weight的名字：
mysql>alter table pet change weight wei;
4. 轉變表的名字
alter table tbl_name rename new_tbl
例如, 把pet表改名為animal
mysql>alter table pet rename animal;
2、對ALTER TABLE的優化
在體系的平常保護中，常常須要對表構造停止更新，例如添加/刪除一個字段，轉變一個VARCHAR的字段長度等等。MySQL針對這類修正表構造的處置方法是先創立一張新的構造的表，接著會經由過程履行Insert語句將舊表的內容拔出到新表中，最初刪除整張舊表。這類處置方法在數據量比擬小的時刻，不會有甚麼成績，可是當數據量很年夜的時刻能夠須要許多時光來處置該進程。
履行一個更新表構造的操作消費了幾個小時的時光，這是沒法忍耐的。假如你用的是5.1之前的版本的話，還會在履行表構造更新是數據庫常常是停滯辦事的，幸虧在最新的版本中這個成績獲得了改良
假如在停止表構造更新的時刻你采取了適當的辦法，也其實不是一切的更新操作會占用你良久的時光。
例如 你想更新用戶表的默許暗碼為“666666”，平日采取的做法是
mysql> ALTER TABLE user
-> MODIFY COLUMN pwd VARCHAR NOT NULL DEFAULT ‘666666';
經由過程SHOW STATUS你可以發明在履行這個操作的進程中停止了年夜量的Insert操作，當用戶的數目很年夜時 例如百萬,萬萬條的數據時，必定會消費許多的時光。
可是假如你采取下邊的方法來更新的話，時光會年夜年夜的延長
mysql> ALTER TABLE user
-> ALTER COLUMN pwd varchar not null SETDEFAULT 5;
履行SHOW STATUS操作發明年夜量的拔出操作不存在了,且時光也年夜年夜的延長了（須要先輩行FLUSH STATUS）
之所以能夠延長時光是由於
（1）表字段的默許值是放在表的frm(.frm:表構造文件 .MYD:表數據文件 .MYI:表索引)文件中
（2）ALTER COLUMN會更新frm文件，而不會觸及到表的內容
（3）MODIFY COLUMN會觸及到表數據的內容
早年面的列子可以看出假如操作的進程中只觸及到frm文件的修改的話，表構造的更新效力會年夜年夜的進步，然則許多時刻在沒有需要的時刻mysql也會停止表的重建。假如你情願承當風險，可以用修正frm文件的方法以到達提速修正表結的目標
其實不是一切的表構造修改都可以經由過程修正frm文件的方法來進步修正的效力，上面的一些修改可以經由過程修正frm文件的方法到達更新的目標：
（1） 更改字段的默許值
（2） 增長/刪除字段的AUTO_INCREMENT屬性
（3） 增長/刪除/修正 ENUM的常量值。關於刪除操作，假如有字段援用了這個常量值，則在刪除後查詢的構造為空字符串
上面以更新字段的默許值屬性為例,分離經由過程應用ALTER COLUMN和修正frm文件的方法來進步修正表構造的效力
1 履行ALTER COLUMN
1.1 起首預備一張字典表
CREATETABLE IF NOT EXISTS dictionary ( id int(10) unsigned NOT NULLAUTO_INCREMENT, word varchar(100) NOT NULL, mean varchar(300) NOT NULL, PRIMARY KEY (`id`) );
1.2 拔出一些測試數據
mysql>DELIMITER $$ mysql>DROP PROCEDURE IF EXISTS SampleProc$$ Query OK, 0rows affected, 1 warning (0.01 sec)
CREATEPROCEDURE SampleProc()
BEGIN
DECLARE xINT;
SET x = 1;
WHILEx <= 110000 DO
insert intodictionary (word, mean) values(concat('a',x),concat('a means',x));
SET x = x + 1;
END WHILE;
END
mysql> DELIMITER ;
mysql>call SampleProc();
1.3 SHOW STATUS 不雅察成果Modify Column 和Alter Column的差別
起首應用MODIFY COLUMN
mysql> flush status;
Query OK, 0 rows affected (0.00 sec)
mysql> alter table dictionary
->modify column mean varchar(20) NOT null default 'DEFAULT1';
Query OK, 110002 rows affected (3.07 sec)
Records: 110002 Duplicates: 0 Warnings: 0
mysql> SHOW STATUS WHERE Variable_name LIKE'Handler%'
->OR Variable_name LIKE 'Created%';
+----------------------------+--------+
| Variable_name | Value |
+----------------------------+--------+
| Handler_read_rnd_next | 110003 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 110002 |
+----------------------------+--------+
在應用ALTER COLUMN
mysql> flush status;
mysql> alter table dictionary
-> alter column mean set default'DEFAULT2';
Query OK, 0 rowsaffected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW STATUSWHERE Variable_name LIKE 'Handler%'
-> OR Variable_name LIKE 'Created%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
|Handler_read_rnd_next | 0 |
|Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
2 修正frm文件
經由過程修正frm文件的方法來進步修正表構造效力的步調年夜概以下
1. 備份相干的數據庫文件
2. 創立一張和舊表完整雷同的表構造
mysql>create table dictionary_new like dictionary;
3. 履行FLUSH TABLES WITH READ LOCK. 一切的表都被封閉
mysql> alter table dictionary_new
-> modify column mean varchar(30)default 'DEFAULR#';
mysql> flush table with read lock;
5. 把dictionary_new.frm文件重名為dictionary.frm
6. 履行UNLOCK TABLES
mysql> unlock tables;
mysql> insert into dictionary(word) values('Random');
mysql> select * from dictionarywhere word='Random'；
從上面的成果可以看出，默許值曾經被改失落，且不觸及到內容的轉變
+--------+--------+----------+ | id | word | mean | +--------+--------+----------+ | 110004 |Random | DEFAULR# | +--------+--------+----------+
7. Drop dictionary_new
