oracle和mysql中的一些区别总结

笔者在实际开发中遇到过得一些关于mysql和oracle适配的问题，经过查阅很多资料和博客，在此记录下来，以备后续查阅。

 

1.自增主键

mysql支持自增主键，oracle不支持，在表结构转化的时候，需要尤为注意。

mysql 使用increment 关键字即可；

oracle 需要使用序列，利用序列自增，每次获取序列的下一个值达到同样效果，建立序列如下：

create sequence TABLE_NAME_SEQ
minvalue 1
maxvalue 999999999999999
start with 1
increment by 1
cache 20
order;
-- 序列使用的时候如下方式：
select TABLE_NAME_SEQ.NEXTVAL from dual;

自增主键的区别，导致在修改 mybatis中的 <insert> 标签里面的语句的时候尤为注意，

举个例子：

mysql中的sql如下：

<!-- 这里么有显示写明自增主键id -->
<insert id="insert" keyColumn="id" keyProperty="id" parameterType="com.xxx.xxx.pojo.A" useGeneratedKeys="true">
    insert into TABLE_A (name, order)
    values (#{name,jdbcType=VARCHAR}, #{order,jdbcType=INTEGER})
  </insert>

对于插入语句，利用序列有两个使用方式。

• 单独序列+sql显式调用；

• 序列+触发器，隐式调用；

1.1 单独序列+sql显式调用

如果需求修改成oracle的版本的话，如下：

    <insert id="insert" keyColumn="id" keyProperty="id" parameterType="com.xxx.xxx.pojo.A" useGeneratedKeys="true">
        <selectKey keyProperty="id" resultType="java.lang.Integer" order="BEFORE">
            SELECT TABLE_A_SEQ.NEXTVAL from dual
        </selectKey>
    insert into white_group (id,name,order)
    values (#{id,jdbcType=INTEGER},#{name,jdbcType=VARCHAR}, #{order,jdbcType=INTEGER})
  </insert>

需要线获取序列的下一个值，然后标记为id,最后在sql中显示写明id，即可。

还有一种写法，就是直接把TABLE_A_SEQ.NEXTVAL放到sql中，如下：

 <insert id="insert" keyColumn="id" keyProperty="id" parameterType="com.xxx.xxx.pojo.A" useGeneratedKeys="true">
    insert into table_A (id,name,order)
    values (TABLE_A_SEQ.NEXTVAl,#{name,jdbcType=VARCHAR}, #{order,jdbcType=INTEGER})
  </insert>

如果id是varchar类型，那么就不需要用到序列了。

1.2 序列+触发器，隐式调用

继续上面的例子，如果这种方式的话， 那么首先需要建立触发器，如下：

create or replace trigger trg_a
before insert on TABLE_A each row
begin
select TABLE_A_SEQ.nextval into :new.author_id from dual;
end;

参考这篇博客：https://www.cnblogs.com/yanghuahui/p/3416277.html

，建立好触发器以后，sql就需要修改了，不用手动增加id了，因为触发器在每次插入的时候，已经自动获取了序列的下一个值。

 

2.函数区别

在做mysql到oracle的适配的时候，在mysql可以运行的sql，放到oracle里面执行的时候，需要注意做转化。

可以参考这些博客：

https://www.cnblogs.com/molao-doing/articles/6048038.html

https://blog.youkuaiyun.com/lanmuhhh2015/article/details/97763615

这里记录几个经常使用到的函数的区别

CONCAT区别

mysql和oracle都存在这个函数，但是存在区别，oracle只支持两个参数，mysql有多个，

举个例子，mysql:

concat('a','b','c')

如果连接多个字符串，那么对于oracle来说有两个方法：

• concat嵌套

• 用“||”连接符

对于上述例子，修改如下：

concat(concat('a','b'),'c')

或者：

'a' || 'b' || 'c'

都可以达到连接多个字符串的效果。

当前时间 表示

mysql 是 now()或者SYSDATE()，获取当前时间

SELECT SYSDATE() ;
--结果： 2021-04-25 14:41:06

oracle 是 sysdate/sysdate()

 

FIND_IN_SET

网上：

MySQL手册中find_in_set函数的语法： FIND_IN_SET(str,strlist)

str 要查询的字符串 strlist 字段名 参数以”,”分隔 如 (1,2,6,8) 查询字段(strlist)中包含(str)的结果，返回结果为null或记录

假如字符串str在由N个子链组成的字符串列表strlist 中，则返回值的范围在 1 到 N 之间。 一个字符串列表就是一个由一些被 ‘,’ 符号分开的子链组成的字符串。如果第一个参数是一个常数字符串，而第二个是type SET列，则FIND_IN_SET() 函数被优化，使用比特计算。 如果str不在strlist 或strlist 为空字符串，则返回值为 0 。如任意一个参数为NULL，则返回值为 NULL。这个函数在第一个参数包含一个逗号(‘,’)时将无法正常运行。

oracle 没有这个函数，因为为了达到同样效果，需要做某些调整：

举个例子：type 这个列，是个字符串，包括的值是："1,2,3,4,5,6"。

-- mysql
select * from artile where find_in_set('3',type);

oracle: instr()函数的格式 （俗称：字符查找函数）

格式一：instr( string1, string2 ) // instr(源字符串, 目标字符串)

格式二：instr( string1, string2 [, start_position [, nth_appearance ] ] ) // instr(源字符串, 目标字符串, 起始位置, 匹配序号)

解析：string2 的值要在string1中查找，是从start_position给出的数值（即：位置）开始在string1检索，检索第nth_appearance（几）次出现string2。

注：在Oracle/PLSQL中，instr函数返回要截取的字符串在源字符串中的位置。只检索一次，也就是说从字符的开始到字符的结尾就结束。

-- oracle
 select * from artile da where instr(','||type||',',',3,')<>0;
 

原理：

将1,3,6,8转为 ,1,3,6,8,然后找出 ,3,的位置，则<>0的即为存在，返回记录，或者 >0 也是存在）。

注意：可以找到字段，但是具体位置信息无法锁定。只能判定这个值是否存在于该列表中，具体位置计算不出。

参考：https://www.cnblogs.com/qinyios/p/11207981.html

https://www.cnblogs.com/dshore123/p/7813230.html

 

日期格式

mysql DATE_FORMAT(),设置日期格式，如下：

select date_format(now(),'%Y-%m-%d');
select time_format(now(),'%H-%i-%S');
select time_format(now(),'%Y-%m-%d %H-%i-%S');

对应于oracle就是：to_date

to_date("要转换的字符串","转换的格式") 两个参数的格式必须匹配，否则会报错。

即按照第二个参数的格式解释第一个参数。

select to_char(sysdate,'yyyy-mm-dd') from dual;
select to_char(sysdate,'hh24-mi-ss') from dual;
select to_char(sysdate,'yyyy-mm-dd hh24-mi-ss') from dual;

功能一致，名称不同。

 

字符串 to_char

如果转字符串的话，用这个

将时间日期按照指定的格式输出，得到的是字符串，而非date类型。

 select sysdate,to_char(sysdate,'yyyy-mm-dd')from dual;
 select sysdate,to_char(sysdate,'yyyy/mm/dd')from dual;
 select sysdate,to_char(sysdate,'yyyymmdd')from dual;
 select sysdate,to_char(sysdate,'yyyymmdd hh24:mi:ss')from dual;

 

mysql group_concat

MySQL GROUP_CONCAT()函数将组中的字符串连接成为具有各种选项的单个字符串。语法：

GROUP_CONCAT(DISTINCT expression
    ORDER BY expression
    SEPARATOR sep);

 

适配oracle的有两个方法：

wm_concat

实现行转列功能，即将查询出的某一列值使用逗号进行隔开拼接，成为一条数据

举个例子：

mysql:

select
    GROUP_CONCAT(c.call_phone) as call_phones,
    <include refid="ALIAS_Column_List" />
    from table_B as u left join table_C as c on FIND_IN_SET(c.id,u.call_phone_ids)
    where 1=1
    <if test="loginName != null and loginName!=''">
      and u.login_name like CONCAT('%',#{loginName},'%')
    </if>
    <if test="name != null and name!=''">
      and u.`name` like CONCAT('%',#{name},'%')
    </if>
    group by u.id
    order by u.frist_login_time desc
    limit #{pageNum},#{pageSize}

oracle 适配：

select  * from (
      select rownum rn, aa.* from (
    select
    wm_concat(c.call_phone) as call_phones,
    <include refid="ALIAS_Column_List" />
    from table_B as u left join table_C as c on  instr(',' || u.call_phone_ids || ',' , ',' || c.id || ',') >0
    where 1=1
    <if test="loginName != null and loginName!=''">
      and login_name like concat(concat('%' ,#{loginName}) , '%')
    </if>
    <if test="name != null and name!=''">
      and name like concat(concat('%' ,#{name}) , '%')
    </if>
    group by <include refid="ALIAS_Column_List" />
    order by frist_login_time desc
    ) aa
    ) aaa
    where aaa.rn between #{pageNum}*#{pageSize}+1 and (#{pageNum}+1)*#{pageSize}

listagg() within group( )

所有版本的oracle都可以使用select wm_concat(name) as name from user; 但如果是oracle11g，使用select listagg(name, ',') within group( order by name) as name from user; 效率更高，官方也更推荐这种写法。

注意：wm_concat 运行后的返回结果根据oracle的版本不同而会字段类型不同，在oracle11g中返回clob型，在oracle10g中返回varchar型

通用版本语句：

select col_a,to_char(wm_concat(col_b)) as colB,to_char(wm_concat(col_c)) as colC from table_name t group by col_a;

举例：

 select  wm_concat(call_phone) as call_phones from call_phone
    
     select  listagg(call_phone, ',') within group( order by call_phone) as call_phones1111 from call_phone;

 

 

参考博客：https://www.cnblogs.com/qinyios/p/11208079.html

3.oracle 中的 rownum介绍

ROWNUM是对结果集加的一个伪列，即先查到结果集之后再加上去的一个列 (强调：先要有结果集)。简单的说 rownum 是对符合条件结果的序列号。它总是从1开始排起的。

例如，我们现在只想看到emp表中的第一条记录：

select * from A where rownum=1;

查看emp中的前2条记录：

select * from A where rownum<=2;

将rownum的限制为2条，这样就可以查询出前2条记录。

假如我们现在只想查看emp中的第二条记录，又该如何写语句呢？

如果我们先这样写：

select * from A where rownum=2;

where条件为：rownum=2，来看看查询结果：

没有查出任何数据。因为rownum是伪列，从1开始，所以需要使用子查询来实现：

select * 
from 
       ( select rownum as rn, a.* from A a where rownum<=2 )
where rn=2 ;

首先通过子查询，取出emp表的前2条记录，并将子查询中的rownum定义为别名rn，然后在外层查询中，使用where条件使rn=2即可，查询出emp表的第二条记录。

参考博客：

https://www.cnblogs.com/wyb628/p/7675691.html

https://blog.youkuaiyun.com/qq_39196949/article/details/84379874

4. limit

mysql有limit关键字， oracle里面没有。

LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)： 为了与 PostgreSQL 兼容，MySQL 也支持句法： LIMIT # OFFSET #。

所以需要做些转化实现原本的mysql语法。

举个例子：

对于单个参数

SELECT * FROM table LIMIT 5;

转化oracle:

SELECT * FROM table rownum = 5;

对于分页

利用上面提到的rownum实现，举个例子：

mysql:

select id,name,remark from table_a limit 0 10;

select id,name,remark from table_a limit #{pageIndex} #{pageSize};

oracle: 先用子查询获取rownum,在外层使用 between and 进行分页：

select a.* from (
 select id,name,remark, rownum as rn from table_a
) a
where a.rn between 0+1 and 0+10;

select a.* from (
 select id,name,remark, rownum as rn from table_a
) a
where a.rn between (#{pageIndex}+1) and (#{pageIndex} + #{pageSize});

 

5. Oracle 的 AS

在Oracle中as关键字不能用于指定表的别名，在Oracle中指定表的别名时只需在原有表名和表的别名之间用空格分隔即可，

但可以用于指定列的别名，但在存储过程中如果列的别名与原有列名相同，在运行时会报错（编译时不会出错），其他情况下

列的别名可以与列名本身相同。

参考博客： https://blog.youkuaiyun.com/qq_42931883/article/details/89472810

Oracle 的单双引号

单引号

• 引用一个字符串常量，用于标识字符与数字的区别

• 转义符，对紧随其后出现的字符(单引号)进行转义

举个例子：

select '''' from dual;
select '''|' from dual;

结果：

'
‘|

 

双引号

关键字，对象名、字段名、别名加双引号，则指示 Oracle将严格区分大小写，否则Oracl都默认大写。

字段名是关键字的就可以用到这个！ 如果可以，最好建表的时候避免关键字和列名重合。

. insert

insert ignore 1

mysql: insert ignore into 表名(唯一约束字段名,表字段1,表字段2) values(‘唯一约束字段值’,‘值1’,‘值2’); 当表字段1为唯一约束主键时这一条插入数据将不进行插入.

mysql 使用ignore关键字：

INSERT IGNORE INTO table_A (id,phone,import_time) VALUES
(3,'111111',NOW());
​
#id 自增主键
INSERT IGNORE INTO table_A (id,phone,import_time) VALUES
(11,'111111',NOW());

 

 

oracle 类似的功能：

oracle:insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(表名(唯一约束字段名1)) */ into 表名 (唯一约束字段名1,表字段1,表字段2) values(‘唯一约束字段值’,‘值1’,‘值2’); 这里的唯一约束字段名1将是你要忽略的唯一约束字段名,若你的表唯一约束字段名1建有序列那么这条数据会重新赋值给唯一约束字段名并将这条记录插入进去.若为没有序列的表时无限执行insert也只会插入一条记录.

oracle实现：

insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(black_phone(id)) */ INTO table_A (id,phone,import_time,group_id,is_del) VALUES
(7,'111111',sysdate,222,0);
​
insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(black_phone(id)) */ INTO table_A (id,phone,import_time,group_id,is_del) VALUES
(99,'111111',sysdate,222,0);

 

 

参考博客：https://blog.youkuaiyun.com/qq_25787555/article/details/89375577

 

insert ignore 2

可以用merge into实现，MYSQL:

   <insert id="batchInsertPhone" parameterType="java.lang.String">
        insert ignore into black_phone (phone,import_time)
        values
        <foreach item="item" index="index" collection="phoneList" separator=",">
            (#{item},now())
        </foreach>
    </insert>

ORACLE:

    <insert id="batchInsertPhone" parameterType="java.lang.String" useGeneratedKeys="false">
        merge into table_A T1 using
        (
        <foreach collection="phoneList" index="index" item="item" separator="union">
            select
            #{item} phone,
            sysdate import_time
            from dual
        </foreach>
        ) T2
        on (T1.phone=T2.phone)
        when not matched then
        insert (id,phone,import_time) values (BLACK_PHONE_SEQ.NEXTVAL,T2.phone,T2.import_time)
    </insert>

 

insert 自增主键

参考上述：1.自增主键

 

 

. update

update table_name left join

oracle不支持连接更新的写法，举个例子

mysql:

update table_A t right join
    (
      SELECT a.id from (
      <!-- 子查询 -->
      ) A  LEFT JOIN (
      <!-- 子查询 -->
      )
      B on A.Id=B.Id WHERE 1=1 AND B.Id IS NULL
    ) tmp on tmp.id =t.id set t.state = 4,t.result=10

 

oracle:

UPDATE duty_resource
        set state = 4, result = 10
        where id in (SELECT a.id
        from (<!-- 子查询 -->) A
        LEFT JOIN (<!-- 子查询 -->) B on A.Id = B.Id
        WHERE B.Id IS NULL)

 

 

补充

oracle不支持sql中的分号

如果有多条需要执行需要用存储过程。

        begin
        update duty_resource set state=#{newState},result = 8 where duty_id=#{dutyId} and state in (0,2,6);
        update duty_resource set state=#{newState},result = 7 where duty_id=#{dutyId} and state = 1;
        end;

另外不只会 truncat和drop一起使用

 

group by,在下oracle下用group by的话，group by后面的字段必须在select后面出现，不然会报错的，而mysql却不会；

 

mysql 有 substring() 和substr oracle 只有substr()

 

后续待补充。

如有侵权，请联系我删除。