mysql alter table

                     mysql alter table（转）

授权给所有机器，user:service，password:some_pass

grant all PRIVILEGES on *.* to service@"%" identified by "some_pass";

严重禁止业务高峰期drop/alter table

drop table最好先truncate table，减少hung时间   Introduction

You'll probably find it necessary on occasion to redesign some of your tables. A change in an application's specification may require that you record information not accounted for in the original definition of a table used by that application. Or you may find that anAUTO_INCREMENTcolumn is running out of room to generate new sequence numbers and you need to change the column to use a larger integer type. MySQL offers many possibilities for modifying a table's structure. This chapter describes how to make the following types of changes:

· Dropping, adding, or repositioning a column.

Columns that have become unnecessary or that you discover to be redundant may be removed to simplify a table and to save space. Or you may move columns from one table to another as part of a normalization procedure. Columns may be added when you need to record additional types of information.

· Changing a column definition or name.

If a column as originally created does not serve your purposes, you may be able to correct the problem by redefining it. For example, you can convert a string column that is case sensitive to one that is not, or vice versa. Or you may have an AUTO_INCREMENTcolumn that is a TINYINTand has room only for 127 sequence values. By changing the column to be unsigned or to use a larger integer type, you can extend the range of the sequence. Renaming a column can be useful if after an upgrade to a more recent version of MySQL you find that a column name is now a reserved word. Or maybe you just want to rename a column like numto something more descriptive like test_scoreto make the column's purpose more explicit.

· Changing a table's type.

The various table types in MySQL have differing characteristics. If a table's type is less suitable for your applications than another type, you can convert it.

· Renaming a table.

Like renaming a column, this can be done if you come up with a better name. Or you can rename for other purposes such as rotating the names of a set of tables used for logging.

· Modifying a table's index structure

Dropping an index that is rarely used can improve performance of inserts and updates of table rows, because that index then need not be updated. Adding an index to a column that you reference frequently in queries can be useful for improving SELECTperformance. Indexing can also be used to remove duplicate values from a table.

1, Dropping, Adding, or Repositioning a Column

Use the DROPorADDclauses of ALTER TABLE to remove or add a column. To move a column, drop it and then put it back where you want it.

mysql>SHOW COLUMNS FROM mytbl;
mysql> DESC mytbl;

//上面两句的作用都是一样的，显示表的结构

ALTER TABLE mytbl DROP i;

//删除表的一列
ALTER TABLE mytbl ADD i INT;

//给表增加一列,该列排在最后
ALTER TABLE mytbl DROP i;
ALTER TABLE mytbl ADD i INT FIRST;

//给表增加一列,该列排在最前

ALTER TABLE mytbl DROP i;
ALTER TABLE mytbl ADD i INT AFTER c;

//给表增加一列,该列排在c列后面

问题：当表已经确立后，该如何调整其排列顺序？？

2, Changing a Column Definition or Name

UseMODIFYorCHANGE.MODIFYis simpler, but cannot change the column name. CHANGEis more confusing to use, but can change both the name and the definition.

ALTER TABLE tbl_nameMODIFYcol_name... ;
ALTER TABLE tbl_nameCHANGEcol_name col_name ... ;

It would be nice to have a form of the ALTER TABLE statement that renamed a column without the need to repeat the definition, especially for working with ENUMandSETcolumns that have many member values. Unfortunately, there is no such statement, which makes these column types somewhat difficult to work with when using ALTER TABLE.

One way to avoid retyping the definition is to capture the current definition in a file and edit the file to produce the proper ALTER TABLE statement:

·Runmysqldumpto get the CREATE TABLE statement that contains the column definition:

%mysqldump --no-data cookbook mytbl > test.txt

·Edit the test.txtfile to remove everything but the definition for the ecolumn.
·Modify the definition to produce an ALTER TABLE statement with a semicolon at the end.
·Writetest.txtback out to save it, then get out of the editor and feed test.txtas a batch file to mysql.
%mysql cookbook < test.txt

3, The Effect of ALTER TABLE on Null and Default Value Attributes

You changed a column definition, but MySQL modified the column's NULLvalue and default value attributes when you didn't tell it to.Those attributes are part of the column definition. If you don't specify them explicitly, MySQL chooses their values for you. So just be more specific about how you want the column defined.

mysql>ALTER TABLE mytbl MODIFY j INT NOT NULL DEFAULT 100;
mysql>SHOW COLUMNS FROM mytbl LIKE 'j';

对默认值的处理：

mysql>ALTER TABLE mytbl MODIFY j BIGINT;
mysql>SHOW COLUMNS FROM mytbl LIKE 'j';
mysql>ALTER TABLE mytbl MODIFY j BIGINT NOT NULL DEFAULT 100;
mysql>SHOW COLUMNS FROM mytbl LIKE 'j';

4, Changing a Column's Default Value

UseSET DEFAULT to specify the default value explicitly, or DROP DEFAULT to remove the current default and allow MySQL to assign the "default default."

To change a default value, use ALTERcol_nameSET DEFAULT:
ALTER TABLE mytbl ALTER j SET DEFAULT 1000;

To drop a default value, use ALTERcol_nameDROP DEFAULT:
ALTER TABLE mytbl ALTER j DROP DEFAULT;

5, Changing a Table Type

UseALTER TABLE to change its type with a TYPEclause.

To find out the current type of a table, use the SHOW TABLE STATUS statement (introduced in MySQL 3.23.0) or SHOW CREATE TABLE (introduced in MySQL 3.23.20):

mysql>SHOW TABLE STATUS LIKE 'mytbl'\G
mysql>SHOW CREATE TABLE mytbl\G

Changing a table type is easy; use ALTER TABLE with a TYPEspecifier. For example, to convert a table to the MyISAM type, use this statement:

ALTER TABLE tbl_nameTYPE = MYISAM;

6, Renaming a Table

To rename a table, use the RENAMEoption of the ALTER TABLE statement:
ALTER TABLE old_nameRENAME TO new_name;

As of Version 3.23.23, MySQL includes an explicit RENAME TABLE statement:
RENAME TABLE old_nameTOnew_name;

RENAME TABLE allows you to rename multiple tables, which allows you to do things such as swap the names of two tables in a single statement:

RENAME TABLE name1TOtemp_name,name2TOname1,tmp_nametoname2;

You can achieve the same result with ALTER TABLE, except that you need three separate statements. Because of that, the tables become available to other clients in the brief intervals between statements, which may be undesirable. Using a single RENAME TABLE statement avoids this problem.

7, Adding or Dropping Indexes

ALTER TABLE can not only drop or add columns, it can drop or add indexes on those columns. These operations often are useful for improving the performance of a database. Typically, indexing a column that you query frequently helps SELECTstatements run faster because the index allows MySQL to avoid full table scans. Dropping indexes can sometimes be useful as well. Whenever a row is modified, MySQL must update any indexes that include the modified columns. If you don't actually use a particular index very much, it's possible that your table is overindexed and that dropping the index will speed up performance of table updates.

mysql>SHOW INDEX FROM mytbl;

There are four types of statements for adding indexes to a table:

ALTER TABLE tbl_nameADD PRIMARY KEY (column_list);
ALTER TABLE tbl_nameADD UNIQUE index_name(column_list);
ALTER TABLE tbl_nameADD INDEX index_name(column_list);
ALTER TABLE tbl_nameADD FULLTEXT index_name(column_list);

The first statement adds a PRIMARY KEY, which means that indexed values must be unique and cannot be NULL. The second creates an index for which values must be unique (with the exception of NULLvalues, which may appear multiple times). The third adds an ordinary index in which any value may appear more than once. The fourth creates a special FULLTEXTindex that is used for text-searching purposes.

Dropping a PRIMARY KEY is easiest, because you need not know the index name:

ALTER TABLE mytbl DROP PRIMARY KEY;

To drop an index that is not a PRIMARY KEY, you must specify the index name. If you don't know the name, use SHOW INDEX. Vertical-format output (specified by \G) often is useful with this statement, to avoid long line wraparound:

mysql>SHOW INDEX FROM mytbl\G

TheKey_nameandSeq_in_indexvalues show the index names and the positions of columns within an index.

8, Eliminating Duplicates by Adding an Index

One way to do this is to create a unique index on the column or columns containing duplicates.

If MySQL discovers duplicate key values when you try to create a PRIMARY KEY or a UNIQUEindex, it aborts the ALTER TABLE operation. To ignore the duplicates and proceed anyway, use ALTER IGNORE TABLE rather than ALTER TABLE. The IGNOREkeyword tells MySQL to retain the first row containing a duplicated key value and discard the others. This is, in fact, a useful way to eliminate duplicates in a column or set of columns: just create a unique-valued index and let MySQL throw away the duplicates.

INSERT INTO client_info (id,name,address)
SELECT id,name,address FROM client_billing;
INSERT INTO bill_item (id,date,minutes,description)
SELECT id,date,minutes,description FROM client_billing;

当对于一个大表进行ALTER TABLE的时候，性能问题就产生了。MySQL大部分改动的步骤如下：根据新的表结构创建一个空表，从旧表中把数据取出来插入到新表中，在删除旧表。这 是个非常漫长的过程。许多人ALTER TABLE之后，都有等待1小时或者1天的痛苦经历。

　　MySQL AB已经开始提升这方面的性能了。一些即将到来的特性是支持"在线"的操作，而不会去锁定表。InnoDB的开发者也在积极努力开发以排序来创建索引。MyISAM已经支持这一特性了，结果就是索引更快并且压缩了索引布局。

　　并不是所有的ALTER TABLE都会使表重建。举个例子，你可以用两个方法更改或者删除列默认值(一个快，一个慢)。如果你想更改一个film的租赁期限为原来的3天到现在的5天。方法如下：

　　mysql> ALTER TABLE sakila.film
　　-> MODIFY COLUMN rental_duration TINYINT(3) NOT NULL DEFAULT 5;

　　用SHOW 　STATUS来监控这个语句，它做了1000次读和1000次插入。换句话说就是复制一个表到新表中。即使这个列的类型，大小，是否为NULL都没有改变。

　　原理上来说，MySQL可以跳过创建新表。这个默认的值实际存储在.frm文件中。因此你可以更改它而没有必要接触表。MySQL也不会做优化，然而任何的MODIFY COLUMN都会导致表的重建。

　　你可以使用ALTER COLUMN来修改：

　　mysql> ALTER TABLE sakila.film
　　-> ALTER COLUMN rental_duration SET DEFAULT 5;

　　这个语句修改了.frm文件而没有去操作表。结果就是速度非常之快。

　　仅仅修改.frm文件

　　我们发现修改标的.frm文件速度非常快并且当它不能那么做的时候，MySQL有的时候会重建表。如果你愿意承担一部分风险，你可以告诉MySQL做一些类型的修改而不去重建表。

　　你可以不用重建表来做如下类型的操作：

　　移除列的AUTO_INCREMENT属性。

　　添加，移除，更改ENUM和SET。如果你移除了一个常量并且一些行包含这个值，查询语句返回这个值将会是空字符串

　　基本的技术就是创建一个.frm文件并且拷贝它到以存在表的.frm文件的位置。步骤如下：

　　创建一个空表，当然表布局必须是准确的。除了一些更改的项。

　　执行FLUSH TABLES WITH READ LOCK.这一步将关闭使用的所有的表并且防止了这些表被打开。

　　交换.frm文件。

　　执行UNLOCK TABLES释放读锁。

　　为了做例子，我们给skila.film表的rating列添加一个常量。当前的列如下：

　　mysql> SHOW COLUMNS FROM sakila.film LIKE 'rating';
　　+--------+------------------------------------+------+-----+---------+-------+
　　| Field 　| Type 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 | Null | Key | Default | Extra |
　　+--------+------------------------------------+------+-----+---------+-------+
　　| rating | enum('G','PG','PG-13','R','NC-17') | YES 　| 　 　 | G 　 　 　 | 　 　 　 |
　　+--------+------------------------------------+------+-----+---------+-------+

　　我们添加一个PG-14到这列中。

　　mysql> CREATE TABLE sakila.film_new LIKE sakila.film;
　　mysql> ALTER TABLE sakila.film_new
　　-> MODIFY COLUMN rating ENUM('G','PG','PG-13','R','NC-17', 'PG-14')
　　-> DEFAULT 'G';
　　mysql> FLUSH TABLES WITH READ LOCK;

　　注意一下我们实在最后添加这个PG-14的，而不是在中间，那样做的话就会修改现有的值了，R值变为PG-14，NC-17变为R依此类推。

　　现在连交换.frm文件，操作系统命令如下

　　root:/var/lib/mysql/sakila# mv film.frm film_tmp.frm
　　root:/var/lib/mysql/sakila# mv film_new.frm film.frm
　　root:/var/lib/mysql/sakila# mv film_tmp.frm film_new.frm

　　在回到MySQL提示符，我们要解锁，再来看看更改的结果。

　　mysql> UNLOCK TABLES;
　　mysql> SHOW COLUMNS FROM sakila.film LIKE 'rating'G
　　*************************** 1. row ***************************
　　Field: rating
　　Type: enum('G','PG','PG-13','R','NC-17','PG-14')

　　最后一步要做的就是删除我们已创建的表。

　　mysql> DROP TABLE sakila.film_new;

　　快速的创建MyISAM索引

　　高效的读取MyISAM表一般的技巧是，关闭键，读取数据，重新启用键。

　　mysql> ALTER TABLE test.load_data DISABLE KEYS;
　　-- load the data
　　mysql> ALTER TABLE test.load_data ENABLE KEYS;

　　这样可以工作的原因是MyISAM延迟了创建键值直到数据读取之后，重要的是，它可以有序的创建索引。结果就非床快并且无碎片和压缩的索引树。

　　不过，对于唯一索引这种方法就不行了。因为DISABLE KEYS仅仅应用于非唯一的索引。MyISAM在内存中创建唯一索引并且读取每一行来校验唯一性。一旦索引大小超出了内存大小，读取会极度缓慢。

　　前一部分所说的ALTER TABLE的技巧，可以加速这个过程，前提是你需要多做一点工作和承担一部分风险。这对于备份来说很有用。比如，当你发现所有的数据是无效的并且不需要做唯一性检查。

　　你需要做的步骤如下：

　　创建一个期望的表。但是不要有任何的索引。

　　读取数据来创建MYD文件。

　　创建另一个空表，这次要包含索引。这会创建.frm和.MYI文件。

　　用个读锁来刷新表。

　　对第二个表的.frm和.MYI进行重命名。因此MySQL会把它们当作第一个表使用。

　　释放读锁。

　　使用REPAIR TABLE来创建表的索引。会以排序的方式创建索引，包括了唯一索引。

　　这个方法对于大表来说，速度格外的快。