alter column和modify column

原创

mb61273498c4ce2 2021-08-26 14:50:47 博主文章分类：mysql3 ©著作权

文章标签 mysql 干货 文章分类 代码人生

©著作权归作者所有：来自51CTO博客作者mb61273498c4ce2的原创作品，请联系作者获取转载授权，否则将追究法律责任

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

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

mysql> 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)