之前一直使用mysql作为存储数据库，虽然中间偶尔使用sqlite作为本地数据库存储，hive作为简单查询工具，maxcompute作为大数据查询服务等等，但没有感觉多少差别。事实上，我们往往听说SQL-92标准之类的云云！

　　后来遇上了oracle，且以其作为主要存储，这下就不得不好好了解其东西了。oracle作为商业数据库里的佼佼者，肯定有其过人之处的。oracle与mysql同样是sql类数据库，大体上是差不多的，我是指crud大体流程。

　　但细节总是不一样的，尤其是有些老梗值得一提。本文从使用者的角度来说说感受，希望可以帮助大家快速了解其大致差异以提供一份简单避坑手册，并无其他意思哟。（理解上也并不深入）

 

　　mysql中要使用自增主键非常方便，只需要在建表时增加 auto_increment 关键字即可，样例如下：

  1. 　　create table tb1 (id int(11) unsigned not null auto_increment);

 

　　而在oracle中则不一样了，它需要使用另一个概念：序列号；我们可以简单将其理解为只有一个列的表，这个表提供了 nextval 的方法，辅助我们生成自增id，样例如下：

  1. 　　-- 1. 建普通表
  2. 　　create table tb1(id number not null);
  3. 　　-- 2. 创建序列，参数比较多，自行查阅资料
  4. 　　create sequence seq_tb1 increment by 1 start with 1 minvalue 1 maxvalue 999999999
  5. 　　-- 3. 插入使用
  6. 　　insert into tb1 (id) values (seq_tb1.nextval)

　　可以看到，oracle的操作明显多了许多。当然了，自增这个属性，在许多数据库中确实也是不提供的，尤其是分布式数据库递增更难做。所以，要支持这功能，绕路也就在所难免了。

　　

　　索引的目的自然是为了提高查询效率，mysql中想要添加索引可以在建表时操作，也可以在后期更改；样例如下：

  1.     -- 1. 建表时指定
  2.     create table tb1 (username varchar(50), index username (username));
  3.     -- 2. 后期更改
  4.     alter table tb1 add index username (usrrname);

 

　　而在oracle中则不一样，它只能在建表完成之后操作；样例如下：

  1.     CREATE INDEX tb1_username ON tb1(username);

　　看起来差异不大，但oracle的索引是全局的，即所有表的索引名都不能重复，比如大家都有id索引，但却不能都叫id。另外，oracle建表时，无法做到一步到位。实际上需要n步：建表sql + n个注释sql + 主键sql + n个索引sql + 建序列号sql + 。。。 总之，会让你醉了！

 

　　我们建一张表时，肯定都需要注释的，否则过两天连我们自己都不认识其含义了。mysql中在建表或增加字段时直接指定，样例如下：

  1.     -- 1. 建表时指定
  2.     create table tb1(username varchar(50) comment \'用户名标识\') comment \'测试建表\';
  3.     -- 2. 修改表结构时指定
  4.     alter table tb1 add column nickname varchar (100) \'昵称\';

 

　　而oracle中则不太一样，它只能在建表之后和创建字段之后才能进行注释；样例如下：

  1.     -- 表注释
  2.     comment on table tb1 is \'测试建表\';
  3.     -- 字段注释
  4.     comment on tb1.username is \'用户名称标识\';
  5.     -- 删除表注释，置空
  6.     -- 删除列注释，置空

　　我不是说它这设计不好，但是就感觉太烦了。

 

　　分页一般用于列表数据多页显示，或取总数中的几条数据使用。mysql中的分页，使用limit，这也是大多数数据库的选择，样例如下：

  1.     select username from tb1 limit 50, 100;

 

　　而在oracle中则不太一样，它使用行号去定位记录，一般需要使用嵌套子查询；样例如下：

  1.     select * from (select t.*,rownum num from tb1 t where rownum<=100 ) where num>50

　　性能比limit怎么样我不清楚，反正是写得挺烦的。

 

　　查询执行计划，可以看出哪些语句是需要优化的，这个工作实际上还是比较专业的。但如果想简单看看情况，mysql中可以这样做：

  1.     explain select * from tb1 where username=\'xx\' order by id limit 10;

　　

　　而oracle中要查看执行计划，则需要借助工具或者自己写，样例如下：

  1.     -- 1. 执行查询执行计划语句
  2.     explain plan for select * from tb1 where username=\'xx\';
  3.     -- 2. 查看执行计划结果
  4.     select * from table(dbms_xplan.display());

　　oracle还有其他许多种查看执行计划的方式，就不列举了。也没啥好坏之分，能查看就行。

 

　　这个简单说说，mysql有很多工具，sqlyog，navicat，mysqlworkbench。。。

　　oracle也有很多，plsql，navicat。。。

　　而具体操作上的差异则根据客户端工具的差异来，无可厚非。

 

　　mysql中对超长文本使用text和longtext类型进行处理，和其他字段并没有太多差别（不能建有效索引除外）

　　而oracle中则使用CLOB类型进行存储超长字符，但它有许多限制，普通查询无法显示clob，分号限制等等。但它可以容纳上G的数据。

       可能更多需要考虑存储结构异样等问题吧！

 

　　都支持date，timestamp等日期数据类型。

　　mysql支持直接使用字符串日期进行条件过滤，默认格式为：yyyy-MM-dd HH:ii:ss 比如：

  1.     select * from tb1 where dt>\'2020-09-13 12:15:01\';

 

　　而oracle则要求严格些，要求必须都是日期老式string格式才能比较；

  1.     select * from tb1 where dt>to_date(\'2020-09-13 12:15:01\', \'yyyy-MM-dd hi24:mi:ss\');

　　虽然加这么个格式东西也不复杂，但总感觉不爽。毕竟，90%以上情况，我只要这种格式能查就行，多让写这么多字符串，简直是罪过啊。。。

 

　　有时候表字段用着用着就跑偏了，需要换一种存储类型，所以改数据类型。mysql中修改字段类型，直接改就好，但有可能失败。

  1.     alter table tb1 change column f1_old f1_new int(11) comment \'xxx\';

　　

　　而oracle中则分情况处理，空字段直接改，不允许修改有值字段类型，如果硬要改那就相当麻烦，如下：

  1.     -- 空字段类型修改，可任意修改
  2.     alter table tb1 modify (f1_old number);
  3.     -- 非空字段类型修改，分类型匹配与不匹配情况
  4.     -- 如果类型匹配，可直接改，如nchar(20) -> nvarchar(20)
  5.     -- 否则不允许修改，只能主动新建字段替换回来，关键是不一定能成功
  6.     alter table tb1 rename column name to name_tmp;
  7.     /*增加一个和原字段名同名的字段name*/
  8.     alter table tb1 add name varchar2(40);
  9.     /*将原字段name_tmp数据更新到增加的字段name，可算可能失败*/
  10.     update tb1 set name=trim(name_tmp);
  11.     /*更新完，删除原字段name_tmp*/
  12.     alter table tb1 drop column name_tmp;

　　很显然，oracle的做法更严谨，不允许更改字段名称，改类型必须保证正确；哎，但总感觉不爽；

 

　　group by可以按照某字段去重一些数据，并按需要聚合数据，mysql与oracle都差不多，差别点在于oracle不允许返回group by外的其他字段（或者说不能准确描述的字段），而mysql则会随机返回一个group by的字段值。mysql如下：

  1.     select username, avg(score), grade from tb1 group by grade;

　　

　　oracle中则要求必须确定某值：

  1.     select max(username), avg(score), grade from tb1 group by grade;

　　看起来oracle是更严谨一些的。

 

　　分区表的目的，在于提高查询速度和方便隔离管理。

　　mysql 创建分区表，Mysql不能自动创建分区，且要求分区字段必须是主键的一部分，如果想自动创建分区，需要使用mysql event事件的方式自动创建分区. 样例如下：

  1.     create table tb1 (
  2.         id int(11),
  3.         day datetime not null
  4.         prmary key (id, day)
  5.     )
  6.     PARTITION BY RANGE (TO_SECONDS(day))
  7.     (PARTITION p20200912 VALUES LESS THAN (TO_SECONDS(\'20200912\')) ENGINE = InnoDB,
  8.     PARTITION p20200913 VALUES LESS THAN (TO_SECONDS(\'20200913\')) ENGINE = InnoDB);

 

　　oracle 中创建分区表

  1.     create table tb1 (
  2.         id NUMBER(20) not null,
  3.         create_time DATE
  4.     )
  5.     PARTITION BY RANGE (CREATE_TIME) INTERVAL (NUMTODSINTERVAL(1, \'day\'))
  6.     (partition part_t01 values less than(to_date(\'2020-09-12\', \'yyyy-mm-dd\')));

　　明显 oracle 支持得更好些呢。

 

　　在做一些大型数据数据分析sql时，with as sql 非常有用，在mysql低版本中是不支持的，只能自己写临时表进行处理。

　　而oracle则支持该通用语法：

  1.     with a as (
  2.       select * from tb1 where dt = \'20200912\'
  3.     ),
  4.     b as (
  5.       select * from tb2 where dt = \'20200912\'
  6.     )
  7.     select a.id aid, b.id bid from a join b on a.pid = b.id;

 

　　在rdb中，通常事务是指对一批操作的原子性，一致性，隔离性，持久性的体现（ACID）。大体上mysql与oracle表现是一致的。

　　mysql是分存储引擎，如innodb，myisam，每个引擎的事务支持能力不同，原则不同，锁实现不同，如innodb锁行，而myisam 锁表等。

　　oracle 中在建表时就可以指定事务槽数

  1.     -- 建表时指定事务槽数
  2.     create table t3 (id int, num int ) INITRANS 6;
  3.     -- 创建索引时指定事务槽数
  4.     create unique index tb1_username_idx on tb1 (username) initrans 6;

 

　　查询正在运行的任务情况，可用于查询慢查询的利器。

　　mysql 中 直接使用 show full processlist 即可；但带条件的查询需要查表：

  1.     show full processlist;
  2.     select * from information_schema.`PROCESSLIST` where duration > 5;

 

　　oracle 进程信息：

  1. SELECT b.sid oracleID,  
  2.        b.username Oracle用户,  
  3.        b.serial#,  
  4.        spid 操作系统ID,  
  5.        paddr,  
  6.        sql_text 正在执行的SQL,  
  7.        b.machine 计算机名  
  8. FROM v$process a, v$session b, v$sqlarea c  
  9. WHERE a.addr = b.paddr  
  10.    AND b.sql_hash_value = c.hash_value;

　　反正我是记不住这么长的sql的。

 

　　mysql 中使用 binlog 可以方便的将数据同步到其他地方;

　　oracle, 好像很复杂的样子, 待研究。

 

     实际上还有其他许多差别，比如查看表结构：mysql中只需show create table tb1，面oracle中则是select  dbms_metadata.get_ddl(table, \’tb1\’) from dual;  Oracle 某些场合大小写敏感，单双引号含义不一致等等。

      张三总是不等于李四的，我们允许不一样的声音，哈哈 。。。

　　

　　最后，给几个出乎意料的oracle 空判定样例，供君娱乐：

  1. -- 以下都不会返回true
  2. select 1 null_judge from dual where \'\' is not null and \'\' != \'\'
  3. select 1 null_judge from dual where \'abc\' is not null and \'abc\' != \'\'
  4. select 1 null_judge from dual where \'\' is not null
  5. -- 以下都会返回true
  6. select 1 null_judge from dual where \'\' is null
  7. select 1 null_judge from dual where null is null

 

版权声明：本文为yougewe原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://www.cnblogs.com/yougewe/p/13662695.html