MySQL 如何修改已建立表的结构

1、为什么要讨论关于ALTER TABLE的问题呢？

MySQL执行大部分修改表结构的原理：

用一个新的结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表。

导致的问题：可能需要花费很长的时间（特别是当内存不大而表又很大，且又有很多索引的情况）

2、举个栗子：更改上面的name字段的校对规则，使其大小写敏感

 ALTER TABLE tbl_alter_table MODIFY COLUMN name VARCHAR(20) NOT NULL;

注意：ALTER 后面跟着的name的属性是完整的。

转换成功后，通过SHOW CREATE TABLE tbl_alter_table;可以查看到：

CREATE TABLE `tbl_alter_table` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `name` varchar(20) COLLATE utf8_bin NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin 

此时输入：SELECT * FROM tbl_alter_table WHERE name=‘tim’；输出：

+----+------+

| id | name |

+----+------+

|  1 | tim  |

+----+------+

3、对于小表的更改的一些方法

如果直接ALTER TABLE则会将之前旧的表的结构更改，如果更改之后出现问题，回返回之前旧的表会比较麻烦。

推荐的做法是：复制一份之前的表的结构和数据作为备份。

MySQL语句如下：

CREATE TABLE tbl_alter_table_new LIKE tbl_alter_table; //复制表的结果

INSERT INTO tbl_alter_table_new

SELECT * FROM tbl_alter_table;                       //将原表的数据也复制过来

...                                          //针对tbl_alter_table_new表进行操作

ALTER TABLE tbl_alter_table RENAME tbl_alter_table_old; //将原本备份

ALTER TABLE tbl_alter_table_new RENAME tbl_alter_table;//新建的表替换成原表名

******************************************************************************************************

往已经存在一个表中添加一个新的column “id”

ALTER TABLE testdb.testtable
Add id bigint(20)

• 1
• 2

取消表的原有主键

ALTER TABLE testdb.testtable
DROP primary key

• 1
• 2

设置”id”为主键

ALTER TABLE testdb.testtable
ADD PRIMARY KEY (id)

• 1
• 2

取消表中的某一列”id”

ALTER TABLE testdb.testtable 
DROP COLUMN id

• 1
• 2

现在有这样一个题目： 
往已经存在数据的表中添加一个新的column “id”，并让其值依次递增，取消表的原有主键，设置”id”为主键

下面给出题目要求的做法： 
改动前的表：

name	sex	title
Lili	femal	Dev
Lilei	male	QA

往已经存在一个表中添加一个新的column “id”，设置其为主键，并让其值依次递增

ALTER TABLE testdb.testtable ADD `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST; 

• 1

执行上面一条SQL，可得改动后的表：

name	sex	title	id
Lili	female	Dev	1
Lilei	male	QA	2

有时候我们会发现，改动后的id，并不是按照加1来增加的。递增步长控制着增加的数值：

设置递增步长

SET @@auto_increment_increment=1;
SHOW VARIABLES LIKE 'auto_inc%'; 

• 1
• 2