5.6中，发现其实alter column 和更改modify column 步骤是一样的
mysql> create table xs(name varchar(12),age int default 5);
Query OK, 0 rows affected (0.34 sec)mysql> insert into xs values('a',4);
Query OK, 1 row affected (0.05 sec)mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.01 sec)mysql> alter table xs modify age int not null default 3;
Query OK, 0 rows affected (0.38 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> show profiles;
+----------+------------+--------------------------------------------------+| Query_ID | Duration | Query |+----------+------------+--------------------------------------------------+| 1 | 0.38208725 | alter table xs modify age int not null default 3 |+----------+------------+--------------------------------------------------+4 rows in set, 1 warning (0.00 sec)mysql> show profile for query 1;
+--------------------------------+----------+| Status | Duration |+--------------------------------+----------+| starting | 0.000114 || checking permissions | 0.000007 || checking permissions | 0.000026 || init | 0.000006 || Opening tables | 0.000056 || setup | 0.000033 || creating table | 0.088857 || After create | 0.009908 || System lock | 0.000017 || preparing for alter table | 0.022311 || altering table | 0.083790 || committing alter table to stor | 0.173121 || end | 0.000042 || query end | 0.000072 || closing tables | 0.000013 || freeing items | 0.003677 || cleaning up | 0.000038 |+--------------------------------+----------+17 rows in set, 1 warning (0.03 sec)mysql> drop table xs;
Query OK, 0 rows affected (0.04 sec)
mysql> create table xs(name varchar(12),age int default 5);
Query OK, 0 rows affected (0.20 sec)mysql> insert into xs values('a',4);
Query OK, 1 row affected (0.00 sec)mysql> alter table xs alter column age set default 3;
Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show profiles;
+----------+------------+-----------------------------------------------------+| Query_ID | Duration | Query |+----------+------------+-----------------------------------------------------+| 2 | 0.00445825 | alter table xs alter column age set default 3 |+----------+------------+-----------------------------------------------------+8 rows in set, 1 warning (0.00 sec)mysql> show profile for query 2;
+--------------------------------+----------+| Status | Duration |+--------------------------------+----------+| starting | 0.000058 || checking permissions | 0.000006 || checking permissions | 0.000006 || init | 0.000003 || Opening tables | 0.000040 || setup | 0.000024 || creating table | 0.003538 || After create | 0.000160 || System lock | 0.000019 || preparing for alter table | 0.000004 || altering table | 0.000016 || committing alter table to stor | 0.000364 || end | 0.000021 || query end | 0.000048 || closing tables | 0.000008 || freeing items | 0.000128 || cleaning up | 0.000020 |+--------------------------------+----------+17 rows in set, 1 warning (0.00 sec)********************************************************************************************************
5.1版本中
mysql> create table xs(name varchar(12),age int default 5);
Query OK, 0 rows affected (0.02 sec)mysql> insert into xs values('a',4);
Query OK, 1 row affected (0.00 sec)mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)mysql> alter table xs modify age int not null default 3;
Query OK, 1 row affected (0.00 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> show profiles;
+----------+------------+--------------------------------------------------+| Query_ID | Duration | Query |+----------+------------+--------------------------------------------------+| 1 | 0.00469700 | alter table xs modify age int not null default 3 |+----------+------------+--------------------------------------------------+1 row in set (0.00 sec)mysql> show profile for query 1;
+----------------------+----------+| Status | Duration |+----------------------+----------+| starting | 0.000063 || checking permissions | 0.000002 || checking permissions | 0.000005 || init | 0.000036 || Opening tables | 0.000008 || System lock | 0.000003 || Table lock | 0.000008 || setup | 0.000023 || creating table | 0.004031 || After create | 0.000076 || copy to tmp table | 0.000152 || rename result table | 0.000238 || end | 0.000026 || query end | 0.000003 || freeing items | 0.000021 || cleaning up | 0.000002 |+----------------------+----------+16 rows in set (0.00 sec)mysql> drop table xs;
Query OK, 0 rows affected (0.00 sec)mysql> create table xs(name varchar(12),age int default 5);
Query OK, 0 rows affected (0.00 sec)mysql> insert into xs values('a',4);
Query OK, 1 row affected (0.00 sec)mysql> alter table xs alter column age set default 3;
Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show profiles;
+----------+------------+-----------------------------------------------------+| Query_ID | Duration | Query |+----------+------------+-----------------------------------------------------+| 1 | 0.00469700 | alter table xs modify age int not null default 3 || 2 | 0.00023300 | drop table xs || 3 | 0.00527100 | create table xs(name varchar(12),age int default 5) || 4 | 0.00030300 | insert into xs values('a',4) || 5 | 0.00403400 | alter table xs alter column age set default 3 |+----------+------------+-----------------------------------------------------+5 rows in set (0.00 sec)mysql> show profile for query 5;
+----------------------+----------+| Status | Duration |+----------------------+----------+| starting | 0.000041 || checking permissions | 0.000003 || checking permissions | 0.000004 || init | 0.000034 || Opening tables | 0.000009 || System lock | 0.000004 || Table lock | 0.000015 || setup | 0.000028 || creating table | 0.003599 || After create | 0.000011 || manage keys | 0.000003 || rename result table | 0.000241 || end | 0.000021 || query end | 0.000003 || freeing items | 0.000017 || cleaning up | 0.000001 |+----------------------+----------+16 rows in set (0.00 sec)