MySQL和Oracle的添加字段的处理差别

184次阅读

共计 3804 个字符，预计需要花费 10 分钟才能阅读完成。

昨天在微信群中有个朋友也是无意中问了一下，说数据库中的表字段想保持一种相对规范的顺序，怎么办？要知道 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 和 Oracle 的添加字段的处理差别

在 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