Alter column and modify column

5.6, we found that the alter column step is the same as the modify column step.

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: 0

Mysql>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 |
+ -------------------------------- + ---------- +
| Startling | 0.000114 |
| Check permissions | 0.000007 |
| Check permissions | 0.000026 |
| Init | 1, 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 |
| Cleaningup | 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: 0

Mysql>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 |
+ -------------------------------- + ---------- +
| Startling | 0.000058 |
| Check permissions | 0.000006 |
| Check permissions | 0.000006 |
| Init | 1, 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 |
| Cleaningup | 0.000020 |
+ -------------------------------- + ---------- +
17 rows in set, 1 warning (0.00 Sec)

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

In version 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: 0

Mysql> 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 |
+ ---------------------- + ---------- +
| Startling | 0.000063 |
| Check permissions | 0.000002 |
| Check permissions | 0.000005 |
| Init | 1, 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 |
| Cleaningup | 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: 0

Mysql> show profiles;
+ ---------- + ------------ + ----------------------------------------------------- +
| Query_id | duration | query |
+ ---------- + ------------ + ----------------------------------------------------- +
| 1 | 0.00469700 | alter table Xs modify age int not null default 3 |
| 1 | 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 |
+ ---------------------- + ---------- +
| Startling | 0.000041 |
| Check permissions | 0.000003 |
| Check permissions | 0.000004 |
| Init | 1, 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 |
| Cleaningup | 0.000001 |
+ ---------------------- + ---------- +
16 rows in SET (0.00 Sec)

Alter column and modify column