Oracle SQL 转 Mysql注意事项
oracle转mysql修改:
-
substr()
substr( string , 0, 10) 这里测试 必须从 第一位获取 既是 substr(string , 1 , 10) -
to_char()
只能用做oracle的函数,兼容oracle和mysql故 改为concat( … , ‘’);
这里 使用了两个, 一个 是将类似 int 转为 string -
nvl函数
被改为
case when length(str)>0 then '处理1' else '处理2' end
或者
IFNULL(EXP1,EXP2)
例如:
-- Scheduling_G_GetSchProductSpecList select PRODUCTSPECNAME as "value" , 'priority-' || avg(ifnull(PRIORITYLEVEL , 0)) as "text" from SCHEDULINGRULE s where s.FACTORYNAME = :factoryName group by PRODUCTSPECNAME order by avg(ifnull(PRIORITYLEVEL , 0)) desc
-
oracle rownum
select @rownum:=@rownum+1 rownum from (select @rownum:=0 from table)
例如:
-- ReceiveVendorLot_G_GetVendorLotList
select
distinct
v.materialtype as "materialType",
v.vendorlotname as "vendorLotName",
v.productquantity as "productQuantity",
v.createtime as "createTime"
from
vendorlotdef v
where
1 = 1
and (:vendorLotName is null
or v.vendorlotname = :vendorLotName)
and (:materialType is null
or v.materialtype = :materialType)
and v.productquantity > 0
and v.vendorLotState = 'Created'
and rownum < 1000
order by
v.PRODUCTQUANTITY desc
修改为
-- ReceiveVendorLot_G_GetVendorLotList
SELECT
t.materialtype as "materialType",
t.vendorlotname as "vendorLotName",
t.productquantity as "productQuantity",
date_format(t.createtime,'%Y-%m-%d %H:%i:%s') as "createTime"
FROM
(SELECT tb.*, @rownum := @rownum + 1 AS rownum FROM
(select distinct
v.materialtype as "materialType",
v.vendorlotname as "vendorLotName",
v.productquantity as "productQuantity",
v.createtime as "createTime"
from vendorlotdef v
where 1 = 1
and (:vendorLotName is null or v.vendorlotname = :vendorLotName)
and (:materialType is null or v.materialtype = :materialType)
and v.productquantity > 0
and v.vendorLotState = 'Created'
-- and rownum < 1000
order by v.PRODUCTQUANTITY desc
) tb, (SELECT @rownum := 0) r
) AS t
WHERE rownum <= 1000 ;
-
时间格式转换
-
to_char(to_date(t1.create_date,'yyyy-MM-dd hh24:mi:ss'),'dateFormat’)
修改为了STR_TO_DATE(t1.create_date, '%Y-%m-%d %h:%i:%s’) DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s)'
例如 mysql
``
date_format(s.planReleasedTime,'%Y-%m-%d') as "planReleasedTime", date_format(s.planfinishedtime,'%Y-%m-%d') AS "planFinishedTime",
-
-
日期直接加减的含义不同了
- 比如Oracle中
sysdate + 1
变成了sysdate() + interval 1 day
(注意如果写成sysdate() + 1
语法还是正确的,但含义是错误的) - 查询
select sysdate() + 1 from dual
在MySQL得到比如 20080223153234(= 20080223153233 + 1)的数
- 比如Oracle中
-
select 'abc' || 'd' from dual
- 两个数据执行的结果不同(语法都能通过),MySQL要写成
select concat('abc' , 'd')
的形式
- 两个数据执行的结果不同(语法都能通过),MySQL要写成
-
autocommit 注意 mysql 这个
- 创建数据库的时候需要自行配置,修改my.cnf文件
autocommit = 0|OFF|1|ON
- 创建数据库的时候需要自行配置,修改my.cnf文件
-
decode()函数
-
decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,默认值)
含义::
当条件的值等于值1的时候,返回值1,当条件的值等于值2的时候,返回值2,当条件的值等于值n的时候,返回值n,如果条件与值1-值n都不想等,则返回默认值。相当于java中的if-else。
-
mysql中的”decode()”函数:
语法:
ifnull( etl( filed(col,…) ) )
-
-
如果字段类型是 varchar 但是 此字段值是数字,想以此字段排序,mysql 需要 查出来后 +0 就会自动转为 int 类型了
-
表连接
- Oracle表连接可以使用
m.factoryName = s.factoryName(+)
这种形式来进行连接,但是Mysql不支持,需要修改成通用的Left Join ON
这种形式的连接方式
- Oracle表连接可以使用
-
区分大小写
- Mysql8.0之后,创建数据库需要配置忽略区分表明大小写,且必须在数据库初始化之前配置完成,配置方式自行百度
- 同时Mysql默认的字符规则,字段内容不区分大小写,所以A 和a是相同的,需要配置修改数据库字符集为
utf8mb4_0900_as_cs
即可区分字段内容大小写,解决主键冲突的问题
-
Oracle CONCAT() 函数与mysql CONCAT()区别
-
Oracle 基本用法,对于两种不同数据类型,Oracle
CONCAT()
返回导致无损转换的数据类型。CONCAT(char1, char2)
等效于串联运算符 (||
)char1 || char2
。SELECT CONCAT('Hello', 'World') FROM dual; 输出: CONCAT('HELLO','WORLD') __________________________ HelloWorld
当其中一个参数为NULL的时候,返回另一个不为NULL的参数,如果所有都为NULL则返回NULL
SELECT CONCAT(NULL, 'A'), CONCAT('B', NULL) FROM dual; 输出: CONCAT(NULL,'A') CONCAT('B',NULL) ___________________ ___________________ A B
-
Mysql中的Concat()函数,可以拼接多个字符串,但只要有一个为NULL,则返回NULL值,
- NULL != 空字符串(‘’)
SELECT CONCAT('1,',NULL,'2'); 结果为 NULL SELECT CONCAT('1,','','2'); 结果为 1,2 事实证明CONCAT函数拼接时如果拼接中的参数只要有NULL时,结果为NULL
-
解决方法一:使用IFNULL函数如果是NULL将其置为’'空字符串。
SELECT CONCAT('1,',IFNULL(NULL,''),'2'); 执行结果:1,2
-
解决方法二:使用CONCAT_WS函数。指定有分隔符的字符串连接
SELECT CONCAT('1,',IFNULL(NULL,''),'2'); 执行结果:1,2
CONCAT_WS(分隔符,参数1,参数2,…参数n)与CONCAT(参数1,参数2,…参数n)
两个函数的区别:CONCAT_WS():表示concat with separator,即有分隔符的字符串连接
CONCAT():表示将字符串进行连接
两个最重要的区别就是,CONCAT()函数,拼接参数中存在NULL直接返回NULL而CONCAT_WS()函数在执行的时候,不会因为NULL值而返回NULL
-
-
MYSQL 查询使用Group By
- 不能使用
SELECT [DISTINCT]
去重查询。否则会报错如下信息
Expression #1 of ORDER BY clause is not in SELECT list, references column 'database.table.column' which is not in SELECT list; this is incompatible with DISTINCT
-
我们使用GROUP BY查询时,出现在SELECT字段后面的只能是GROUP BY后面的分组字段,或使用聚合函数包裹着的字段,否则会报错如下信息
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'database.table. column' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
-
原因:
- mysql5.7.34及以上版本将sql_mode的ONLY_FULL_GROUP_BY模式默认设置为打开状态,会导致一些错误
-
解决:
-
去除ONLY_FULL_GROUP_BY ,即关闭此模式
-
命令关闭
set global sql_mode=‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’; 但该方法在重启Mysql服务后会失效,重启服务后会失效
-
通过修改mysql的配置文件关闭ONLY_FULL_GROUP_BY SQL模式
sudo vim /etc/mysql/conf.d/mysql.cnf [mysqld] sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION #重启 sudo service mysql restart
-
-
- 不能使用
参考资料
Oracle转Mysql需要注意的地方_一只仰望天空的菜鸟的博客-CSDN博客_oracle转mysql约束问题
Expression #1 of ORDER BY clause is not in SELECT list,references column ‘xxxxxxx’_涵山韵海的博客-CSDN博客