Oracle和MySQL常用区别 | 兔子山商店街

XML条件查询优化

避免使用`where 1=1`

当遇到多个查询条件，使用where 1=1可以很方便的解决我们的问题，但是这样很可能会造成非常大的性能损失，

因为添加了where 1=1的过滤条件之后，数据库系统就无法使用索引等查询优化策略，数据库系统将会被迫对每行数据进行扫描（即全表扫描）以比较此行是否满足过滤条件，当表中的数据量较大时查询速度会非常慢；

此外，还会存在SQL 注入的风险。

反例如下：

<select id="queryBookInfo" parameterType="com.tjt.platform.entity.BookInfo" resultType="java.lang.Integer">
 select count(*) from t_rule_BookInfo t where 1=1
 <if test="title !=null and title !='' ">
  AND title = #{title} 
 </if> 
 <if test="author !=null and author !='' ">
  AND author = #{author}
 </if> 
</select>

正例：

<select id="queryBookInfo" parameterType="com.tjt.platform.entity.BookInfo" resultType="java.lang.Integer">
 select count(*) from t_rule_BookInfo t
 <where>
 <if test="title !=null and title !='' ">
  title = #{title} 
 </if>
 <if test="author !=null and author !='' "> 
  AND author = #{author}
 </if>
 </where> 
</select>

获取行号

oracle：

1	select A.*,rownum from A;

mysql:

1	select a.* ,@rownum:=@rownum+1 from a,(select @rownum:=0) r

mysql中没有获取行号的函数，因此需要通过一些自定义语句来进行获取。通常做法是，通过定义用户变量@rownum来保存表中的数据。通过赋值语句@rownum：=@rownum+1来累加达到递增行号。
例如：

select a.* ,@rownum:=@rownum+1 from a,(select @rownum:=0) r;

后半部分语句的select @rownum:=0相当于创建了r的新表，其表的列为@rownum，数值为0.

通过利用变量将@rownum的行进行重新赋值，并显示。可以应用于获取行号或名次排列。

另外，在名次排列时，由于会有重复的数据，排列时仅仅按照,@rownum:=@rownum+1。排序是不对的，需要在数据相同时，名次应该相同。因此需要另一变量保存上次的数据，并进行与本行数据对比，相同序号不变，不同序号应该等于行号。此时需要应用到case when进行判断，例如：

Select a.*,
Case
When @rownum0=a.c_id  then  @rownum:=@rownum+1
When @rownum0:=a.c_id  then  @rownum:=1
Else  @rownum
End rank1,
Case
When @rownum1=a.s_score then @rownum2
When @rownum1:=a.s_score then @rownum2:=@rownum
Else @rownum2
End rank2
 from (select * from score  order by c_id ,s_score desc
) as a,(select @rownum:=0, @rownum0:=0,@rownum1:=0,@rownum2:=0) r;

外连接

oracle：

1	select A.,B. from A,B where a.id = b.id(+);

mysql:

1	select A.,B. from A left join B on a.id = b.id;

空值排序

oracle：

1	select * from COUNTRY order by PHONEORDER asc nulls last

mysql:

1	select * from COUNTRY order by PHONEORDER is null,PHONEORDER

应用场景

从小到大升序排列时，null会出现在最前方，现在要将其放在后面

解决办法

方式一：

SELECT * FROM test ORDER BY num IS NULL , num;

方式二：

select * from test order by if(isnull(num),1,0), num desc;

1.将null强制放在最前：

if(isnull(字段名),0,1) asc //asc可以省略

2.将null强制放在最后

if(isnull(字段名),0,1) desc

if(isnull(字段名),1,0) asc //asc可以省略

查询空值的运行速度基本上为IFNULL()>is NULL>ISNULL()。

时间转字符串

oracle：

to_char

mysql:

1	date_format(date,'%Y-%m-%d')

https://www.w3school.com.cn/sql/func_date_format.asp

比较时间

oracle：

1 2	select * from A where to_date(to_char(EVENT_DATE,'yyyy-mm-dd'),'yyyy-mm-dd') > =to_date(to_char(#{fndPortalSystemLogDto.startDate},'yyyy-mm-dd'),'yyyy-mm-dd')

mysql:

1	select * from A where STR_TO_DATE('2017-01-06 10:20:30','%Y-%m-%d %H:%i:%s')

拼音排序

oracle：

1	select * from A order by nlssort(NAME_ZH,'NLS_SORT=SCHINESE_PINYIN_M') ASC

mysql:

1	select * from A order by convert(name using gbk) asc

按照汉字的拼音排序，用的比较多是在人名的排序中，按照姓氏的拼音字母，从A到Z排序；
如果存储姓名的字段采用的是GBK字符集，那就好办了，因为GBK内码编码时本身就采用了拼音排序的方法（常用一级汉字3755个采用拼音排序，二级汉字就不是了，但考虑到人名等都是常用汉字，因此只是针对一级汉字能正确排序也够用了）。

直接在查询语句后面添加 order by name asc; 查询结果按照姓氏的升序排序；

如果存储姓名的字段采用的是 utf8字符集，需要在排序的时候对字段进行转码；order by convert(name using gbk) asc; 同样，查询的结果也是按照姓氏的升序排序；

取当前时间

oracle：

1	select * from T WHERE CREATE_TIME = sysdate

mysql:

1	select * from T WHERE CREATE_TIME = now()

mysql中日期函数还是比较常用的。主要有NOW()和SYSDATE()两种，虽然都表示当前时间，但使用上有一点点区别。
NOW()取的是语句开始执行的时间，SYSDATE()取的是动态的实时时间。

因为NOW()取自mysql的一个变量”TIMESTAMP”，而这个变量在语句开始执行的时候就设定好了，因此在整个语句执行过程中都不会变化。

NOW()还有3个同义词，效果跟NOW()一样，而且都有2种形式写法：

LOCALTIME或LOCALTIME()

LOCALTIMESTAMP或LOCALTIMESTAMP()

CURRENT_TIMESTAMP或CURRENT_TIMESTAMP()

列转行函数listagg()

oracle：

1	listagg(score, ',') within group(ORDER BY )

mysql:

1	select `name`,group_concat(score separator ',') from test group by `name`

模糊查询

oracle：

1	T1.EMAIL LIKE '%' \|\| #{basePortalUser.email} \|\| '%'

mysql:

1	like concat('%',#{param},'%')

推荐使用前者，可以避免sql注入。

1	like '%${param}%'

oracle中的“||”是连接字符串的作用，mysql中需要用concat(a,b,c)方法

查看表注释

oracle：

SELECT
TABLE_NAME,
COMMENTS DESCRIPTION
FROM
all_tab_comments
WHERE
table_name = #{tableName,jdbcType=VARCHAR}

mysql:

SELECT
	TABLE_NAME,
	TABLE_COMMENT  
FROM
	INFORMATION_SCHEMA.TABLES 
WHERE
	table_name = 'base_portal_file' 
	AND table_schema = 'base'

查看表中列结构

oracle：

SELECT
  T1.COLUMN_NAME FIELD_NAME,
  T1.DATA_TYPE TYPE,
  T2.COMMENTS DESCRIPTION,
  T1.COLUMN_ID
FROM
  USER_TAB_COLS T1
  LEFT JOIN USER_COL_COMMENTS T2 ON T1.TABLE_NAME = T2.TABLE_NAME AND T1.COLUMN_NAME = T2.COLUMN_NAME
WHERE
  T1.TABLE_NAME = #{tableName,jdbcType=VARCHAR}
  AND T1.VIRTUAL_COLUMN = 'NO'

mysql:

SELECT
	column_name COLUMN_NAME,
	DATA_TYPE,
CASE
	DATA_TYPE 
	WHEN 'decimal' THEN
	NUMERIC_PRECISION 
	WHEN 'datetime' THEN
	DATETIME_PRECISION ELSE character_maximum_length 
	END AS DATA_LENGTH,
	IS_NULLABLE NULLABLE,
	column_comment DESCRIPTION,
	ORDINAL_POSITION COLUMN_ORDER 
FROM
	information_schema.COLUMNS 
WHERE
	table_name = 'AE_JOURNAL_TYPE' 
  and generation_expression is empty
ORDER BY
	ORDINAL_POSITION ASC

判断为空并格式化

oracle:

1	select nvl(amount,0) from scm_example

mysql:

1	select IFNULL(amount,0) from scm_example

实现差集MINUS

oracle:

1
2
3

SELECT member_id, name FROM a
MINUS
SELECT member_id, name FROM b

mysql:

SELECT DISTINCT member_id, name
FROM a
WHERE (member_id, name) NOT IN
(SELECT member_id, name FROM table2);

SELECT DISTINCT a.member_id, a.name
FROM a LEFT JOIN b USING (member_id, name)
WHERE b.member_id IS NULL
//using() 中 （） 必需

XML条件查询优化

避免使用where 1=1

反例如下：

正例：

获取行号

外连接

空值排序

应用场景

解决办法

时间转字符串

比较时间

拼音排序

取当前时间

列转行函数listagg()

模糊查询

查看表注释

查看表中列结构

判断为空并格式化

实现差集MINUS

避免使用`where 1=1`