MySQL和Oracle的添加字段的处理差别
昨天在微信群中有个朋友也是无意中问了一下，说数据库中的表字段想保持一种相对规范的顺序，怎么办？要知道Oracle中这个操作就比较纠结了，因为是按照追加的方式来处理的。没法在已有的字段1，字段2中间添加一个字段3。但是MySQL却可以，这个方面MySQL看起来要灵活的多，这个是什么原因呢，他们在设计上有什么差别呢。
MySQL中对每个表存在一个定义文件，即frm文件，我们来取出一个表，看看能不能简单解析一下。
比如一个表字段的内容如下：
> desc zd_warshrine_prostate;
+----------+--------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+-------------------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| proName | varchar(100) | NO | MUL | NULL | |
| TYPE | varchar(10) | NO | | NULL | |
| loaderr | int(11) | NO | | 0 | |
| loadTime | timestamp | NO | | CURRENT_TIMESTAMP | |
+----------+--------------+------+-----+-------------------+----------------+
我们可以使用strings来简单解析一下，可以通过上面的内容能够读到一些信息。
# strings zd_warshrine_prostate.frm
PRIMARY
in_ty_zyl_proName
InnoDB
)
proName
TYPE
loaderr
loadTime
proName
TYPE
loaderr
loadTime
大体能够看出，只解析出来了字段名。而查看MySQL中的数据字典columns，却压根看不到column_id这样的字段。
在MySQL要实现添加字段的顺序性，语句可以这样写：
ALTER TABLE test
ADD COLUMN `amount_sum` double(255,0) AFTER `amount_name`;
即在字段amount_name后添加字段amount_sum
难道是MySQL中的这种方式技高一筹,也不是了，对于添加字段，修改数据类型这类的操作，MySQL在早期版本也是饱受诟病，因为会直接锁表，而且实现起来的思路其实就是复制表数据，类似于重建。这个情况在后来的一些版本比如5.6有了一些改善，有了pt-osc的工具，这个改进可以在线修改了。而实现方式其实有点类似于Oracle中的在线重定义，MySQL中会创建一个临时表，然后创建2个触发器，然后同步数据到临时表，然后触发器同步操作。如果表数据不大，倒还不是什么大问题，一旦数据量级上来了，业务关注度上来了，这个地方就值得好好挖掘挖掘。
Oracle中是怎么做的呢。看起来还是有不小的差别。
比如我们查看一个表users的数据。
SQL> select dbms_rowid.ROWID_RELATIVE_FNO(rowid) as file#,dbms_rowid.ROWID_BLOCK_NUMBER(rowid) as block#,dbms_rowid.ROWID_ROW_NUMBER(rowid) as row#,a.* from test.USERS a where rownum<2;
FILE# BLOCK# ROW# USERID USER_NAME
---------- ---------- ---------- ---------- --------------------
24 1569619 0 1278 user1278
通过上面的输出可以看到是在24号文件，数据块1569619中，数据信息也一并输出出来了，这个表含有两个字段，userid,user_name；
那么数据是如何存储的呢。我们做一个dump
alter system dump datafile 24 block 1569619;
为了图省事，可以直接查看select *from v$diag_info;得到trace文件的路径。
/U01/app/oracle/diag/rdbms/mbionline/mbionline/trace/mbionline_ora_15752.trc
我们输出几行trace文件的内容，可以看到字段都是存在一个column_id的字样，即col 0,col 1这样的。
tab 0, row 56, @0x134a
tl: 16 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 3] c2 0e 23
col 1: [ 8] 75 73 65 72 31 33 33 34
tab 0, row 57, @0x135a
tl: 16 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 3] c2 0e 24
col 1: [ 8] 75 73 65 72 31 33 33 35
tab 0, row 58, @0x136a
tl: 16 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 3] c2 0e 25
col 1: [ 8] 75 73 65 72 31 33 33 36
Oracle中是按照这个column_id来进行字段的顺序管理的。关于字段有两个很相似的数据字典dba_tab_cols,tab_tab_columns。
里面很重要的一个属性就是column_id，同时也能够看出还有数据类型为Long的字段 DATA_DEFAULT ，这也算是Oracle为此问题付出的一个代价，为了保持兼容性，这个long类型的字段到了12c依旧是如此。
SQL> desc dba_tab_columns
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
COLUMN_NAME NOT NULL VARCHAR2(30)
DATA_TYPE VARCHAR2(106)
DATA_TYPE_MOD VARCHAR2(3)
DATA_TYPE_OWNER VARCHAR2(60)
DATA_LENGTH NOT NULL NUMBER
DATA_PRECISION NUMBER
DATA_SCALE NUMBER
NULLABLE VARCHAR2(1)
COLUMN_ID NUMBER
DEFAULT_LENGTH NUMBER
DATA_DEFAULT LONG
NUM_DISTINCT NUMBER
而如果对一个大表添加字段，如果涉及默认值，那就工作就很难了，除此之外添加字段方面，Oracle处理起来还是要好很多，至少不会重建表数据。这也算是两者在设计上的一些差别吧。而对于Oracle可以有不少的诊断方式，对于MySQL似乎方式和手段就少了一些，不过也有几种方式，
比如验证MySQL对于添加字段，修改数据类型，可以查看show processlist，找到一个线程会标示copy to tmp table
而同时在数据目录下会创建两个临时文件，类似下面的形式。
-rw-rw---- 1 mysql mysql 8860 Nov 4 19:15 #sql-2721_17a3a9.frm
-rw-rw---- 1 mysql mysql 549453824 Nov 4 19:16 #sql-2721_17a3a9.ibd
而更进一步想看到更多的内容，那就是源代码了，其实还好了，已经看到有些牛人在解析这部分的内容了，不过我得自己读一读，消化一下，才能拿出来。
当前文章：MySQL和Oracle的添加字段的处理差别
本文路径：http://shouzuofang.com/article/psdsdc.html
MySQL中对每个表存在一个定义文件，即frm文件，我们来取出一个表，看看能不能简单解析一下。
比如一个表字段的内容如下：
> desc zd_warshrine_prostate;
+----------+--------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+-------------------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| proName | varchar(100) | NO | MUL | NULL | |
| TYPE | varchar(10) | NO | | NULL | |
| loaderr | int(11) | NO | | 0 | |
| loadTime | timestamp | NO | | CURRENT_TIMESTAMP | |
+----------+--------------+------+-----+-------------------+----------------+
我们可以使用strings来简单解析一下，可以通过上面的内容能够读到一些信息。
# strings zd_warshrine_prostate.frm
PRIMARY
in_ty_zyl_proName
InnoDB
)
proName
TYPE
loaderr
loadTime
proName
TYPE
loaderr
loadTime
大体能够看出，只解析出来了字段名。而查看MySQL中的数据字典columns，却压根看不到column_id这样的字段。
在MySQL要实现添加字段的顺序性，语句可以这样写：
ALTER TABLE test
ADD COLUMN `amount_sum` double(255,0) AFTER `amount_name`;
即在字段amount_name后添加字段amount_sum
难道是MySQL中的这种方式技高一筹,也不是了，对于添加字段，修改数据类型这类的操作，MySQL在早期版本也是饱受诟病，因为会直接锁表，而且实现起来的思路其实就是复制表数据，类似于重建。这个情况在后来的一些版本比如5.6有了一些改善，有了pt-osc的工具，这个改进可以在线修改了。而实现方式其实有点类似于Oracle中的在线重定义，MySQL中会创建一个临时表，然后创建2个触发器，然后同步数据到临时表，然后触发器同步操作。如果表数据不大，倒还不是什么大问题，一旦数据量级上来了，业务关注度上来了，这个地方就值得好好挖掘挖掘。
Oracle中是怎么做的呢。看起来还是有不小的差别。
比如我们查看一个表users的数据。
SQL> select dbms_rowid.ROWID_RELATIVE_FNO(rowid) as file#,dbms_rowid.ROWID_BLOCK_NUMBER(rowid) as block#,dbms_rowid.ROWID_ROW_NUMBER(rowid) as row#,a.* from test.USERS a where rownum<2;
FILE# BLOCK# ROW# USERID USER_NAME
---------- ---------- ---------- ---------- --------------------
24 1569619 0 1278 user1278
通过上面的输出可以看到是在24号文件，数据块1569619中，数据信息也一并输出出来了，这个表含有两个字段，userid,user_name；
那么数据是如何存储的呢。我们做一个dump
alter system dump datafile 24 block 1569619;
为了图省事，可以直接查看select *from v$diag_info;得到trace文件的路径。
/U01/app/oracle/diag/rdbms/mbionline/mbionline/trace/mbionline_ora_15752.trc
我们输出几行trace文件的内容，可以看到字段都是存在一个column_id的字样，即col 0,col 1这样的。
tab 0, row 56, @0x134a
tl: 16 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 3] c2 0e 23
col 1: [ 8] 75 73 65 72 31 33 33 34
tab 0, row 57, @0x135a
tl: 16 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 3] c2 0e 24
col 1: [ 8] 75 73 65 72 31 33 33 35
tab 0, row 58, @0x136a
tl: 16 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 3] c2 0e 25
col 1: [ 8] 75 73 65 72 31 33 33 36
Oracle中是按照这个column_id来进行字段的顺序管理的。关于字段有两个很相似的数据字典dba_tab_cols,tab_tab_columns。
里面很重要的一个属性就是column_id，同时也能够看出还有数据类型为Long的字段 DATA_DEFAULT ，这也算是Oracle为此问题付出的一个代价，为了保持兼容性，这个long类型的字段到了12c依旧是如此。
SQL> desc dba_tab_columns
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
COLUMN_NAME NOT NULL VARCHAR2(30)
DATA_TYPE VARCHAR2(106)
DATA_TYPE_MOD VARCHAR2(3)
DATA_TYPE_OWNER VARCHAR2(60)
DATA_LENGTH NOT NULL NUMBER
DATA_PRECISION NUMBER
DATA_SCALE NUMBER
NULLABLE VARCHAR2(1)
COLUMN_ID NUMBER
DEFAULT_LENGTH NUMBER
DATA_DEFAULT LONG
NUM_DISTINCT NUMBER
而如果对一个大表添加字段，如果涉及默认值，那就工作就很难了，除此之外添加字段方面，Oracle处理起来还是要好很多，至少不会重建表数据。这也算是两者在设计上的一些差别吧。而对于Oracle可以有不少的诊断方式，对于MySQL似乎方式和手段就少了一些，不过也有几种方式，
比如验证MySQL对于添加字段，修改数据类型，可以查看show processlist，找到一个线程会标示copy to tmp table
而同时在数据目录下会创建两个临时文件，类似下面的形式。
-rw-rw---- 1 mysql mysql 8860 Nov 4 19:15 #sql-2721_17a3a9.frm
-rw-rw---- 1 mysql mysql 549453824 Nov 4 19:16 #sql-2721_17a3a9.ibd
而更进一步想看到更多的内容，那就是源代码了，其实还好了，已经看到有些牛人在解析这部分的内容了，不过我得自己读一读，消化一下，才能拿出来。
当前文章：MySQL和Oracle的添加字段的处理差别
本文路径：http://shouzuofang.com/article/psdsdc.html