MySQL和Oracle的添加字段的处理差别-阿里云开发者社区

开发者社区> jeanron100> 正文

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

简介: 昨天在微信群中有个朋友也是无意中问了一下,说数据库中的表字段想保持一种相对规范的顺序,怎么办?要知道Oracle中这个操作就比较纠结了,因为是按照追加的方式来处理的。没法在已有的字段1,字段2中间添加一个字段3。
+关注继续查看
昨天在微信群中有个朋友也是无意中问了一下,说数据库中的表字段想保持一种相对规范的顺序,怎么办?要知道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
而更进一步想看到更多的内容,那就是源代码了,其实还好了,已经看到有些牛人在解析这部分的内容了,不过我得自己读一读,消化一下,才能拿出来。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
discuz论坛发帖添加字段
1 后台--论坛-- 分类信息   字段管理:添加字段。 分类管理:添加一个分类,将该分类添加几个字段; 2论坛---》 选择一个模块--》分类信息---》开启----》启用分类勾选上即可。
614 0
[Struts]处理表单中值为空的日期类型字段
在示范中心项目中,我们把ActionForm中日期类型的字段指定为String类型,而在对应的JavaBean中指定为java.sql.Date类型。当用户提交表单的时候,在Action里使用BeanUtils.copyProperties()方法从ActionForm构造JavaBean对象(详见利用BeanUtils在对象间复制属性)。
1031 0
关于分组后字段拼接的问题 (Oracle)
-----关于分组后字段拼接的问题来自:www.itpub.net  最近在论坛上,经常会看到关于分组后字段拼接的问题,大概是类似下列的情形:SQL> select no,q from test2 /NO Q---------- ------------------------------001 ...
830 0
使用OpenApi弹性释放和设置云服务器ECS释放
云服务器ECS的一个重要特性就是按需创建资源。您可以在业务高峰期按需弹性的自定义规则进行资源创建,在完成业务计算的时候释放资源。本篇将提供几个Tips帮助您更加容易和自动化的完成云服务器的释放和弹性设置。
12061 0
SAP LSMW 导入OPEN PO 单据时候’税码’字段的处理
SAP LSMW Standard Batch (Direct) Input 方式制作的LSMW工具导入OPEN PO 单据时候’税码’字段的处理 如下的Open PO 批量导入LSMW工具,   ...
1087 0
oracle修改表字段
增加字段     alter   table   docdsp     add   dspcode   char(200)     删除字段     ALTER   TABLE   table_NAME   DROP   COLUMN   column_NAME     修改字段类型     ALTER   TABLE   table_name     ALTER   COLUMN   column_name   new_data_type     改名     sp_rename     更改当前数据库中用户创建对象(如表、列或用户定义数据类型)的名称。
588 0
使用ElementUi的table组件自定义添加升序、降序按钮和点击事件及排序
使用ElementUi的table组件自定义添加升序、降序按钮和点击事件及排序
7927 0
解决Druid设置Oracle的Clob字段时的小坑
众所周知,Oracle有很多坑, 所以才有了去IOE。 在使用Druid做数据库连接池后,其实偶尔也会碰到小坑,这就是使用开源项目所必须去填平的。【如果使用不开源的产品,那就不是坑,而是陷阱了,你都不知道怎么去填坑】 用Druid连接池,通过JDBC往Oracle数据库的Clob字段插入数据,或者更新数据时,一个问题出现了。
2021 0
+关注
jeanron100
Oracle ACE,《Oracle DBA工作笔记》作者 现就职于国内某互联网公司,擅长数据管理,数据迁移,性能优化,目前专注于开源技术,运维自动化和性能优化。
1180
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载