mysql在线增加字段_mysql各版本在线增加与删除字段

最近测试了mysql各版本在线增加和删除操作，mysql5.1与mysql5.5无论是myisam还是innodb，增加删除字段时，可进行查询操作，修改操作将锁表，而发现mysql5.6的innodb支持查询和更新，在块mysql5.6做的很强大，另外infobright的增加和删除列操作几乎瞬间完成，这也得益于他是列式存储的关系。

以下是我的测试过程：

mysql5.1：增加和删除字段时只能执行查询，修改操作将会锁表。mysql> alter table test1 add id2 int;

Query OK, 33554434 rows affected (36.01 sec)

Records: 33554434 Duplicates: 0 Warnings: 0

mysql> select count(*) from test1;

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

| count(*) |

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

| 33554435 |

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

1 row in set (0.00 sec)

mysql> insert into test1(id) values(1);

Query OK, 1 row affected (32.44 sec)

mysql> show processlist;

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

| Id | User | Host | db | Command | Time | State | Info |

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

| 1 | root | localhost | test | Query | 11 | copy to tmp table | alter table test1 add id2 int |

| 2 | root | localhost | test | Query | 7 | Locked | insert into test1(id) values(1) |

| 3 | root | localhost | NULL | Query | 0 | NULL | show processlist |

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

mysql5.5：增加和删除字段时只能执行查询，修改操作将会锁表。mysql> alter table test add id3 int;

Query OK, 8388608 rows affected (1 min 11.55 sec)

Records: 8388608 Duplicates: 0 Warnings: 0

mysql> select * from test limit 1;

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

| id | id1 |

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

| 1 | bbab3146-87ba-11e2-8238-782bcb0561d1 |

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

1 row in set (0.00 sec)

mysql> insert into test values(1,uuid());

ERROR 1136 (21S01): Column count doesn't match value count at row 1

mysql> show processlist;

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

| Id | User | Host | db | Command | Time | State | Info |

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

| 1 | root | localhost | test | Query | 40 | copy to tmp table | alter table test add id3 int |

| 2 | root | localhost | test | Query | 25 | Waiting for table metadata lock | insert into test values(1,uuid()) |

| 3 | root | localhost | NULL | Query | 0 | NULL | show processlist |

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

mysql5.6INNODB：增加字段时，能在线执行查询与修改操作CREATE TABLE `sbtest` (

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

`k` int(10) unsigned NOT NULL DEFAULT '0',

`c` char(120) NOT NULL DEFAULT '',

`pad` char(60) NOT NULL DEFAULT '',

PRIMARY KEY (`id`),

KEY `k` (`k`)

) ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=latin1 |

mysql> alter table sbtest add column id1 int;

Query OK, 0 rows affected (4 min 45.54 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> select count(*) from sbtest;

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

| count(*) |

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

| 10000000 |

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

1 row in set (3.20 sec)

mysql> insert into sbtest(id) values(1000000000);

Query OK, 1 row affected (0.01 sec)

mysql> delete from sbtest where id=1000000000;

Query OK, 1 row affected (0.77 sec)

mysql> update sbtest set k=1 where k=1000000000;

Query OK, 1 row affected (0.05 sec)

Rows matched: 1 Changed: 1 Warnings: 0

myisam：增加字段时，只能在线执行查询操作，修改操作将锁表mysql> alter table test add id3 int;

Query OK, 33554434 rows affected (12.41 sec)

Records: 33554434 Duplicates: 0 Warnings: 0

mysql> select * from test limit 1;

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

| id | id1 |

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

| 1 | 4f6d81ae-87bb-11e2-8da9-782bcb0561d1 |

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

1 row in set (0.00 sec)

mysql> insert into test(id) values(2);

Query OK, 1 row affected (10.75 sec)

mysql> show processlist;

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

| Id | User | Host | db | Command | Time | State | Info |

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

| 43 | root | localhost | test | Query | 2 | Waiting for table metadata lock | insert into test(id) values(2) |

| 45 | root | localhost | test | Query | 3 | copy to tmp table | alter table test add id3 int |

| 46 | root | localhost | NULL | Query | 0 | init | show processlist |

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

infobright：能快速的增加和删除列mysql> select count(*) from test;

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

| count(*) |

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

| 67108864 |

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

1 row in set (0.00 sec)

mysql> alter table test add id2 int;

Query OK, 0 rows affected (0.93 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table test drop column id2 ;

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0