mysql表转clickhouse mysql表转oracle表

转载

mob64ca13f7ab19 2023-08-22 21:37:20

文章标签 mysql表转clickhouse oracle mysql sql 字段 文章分类 MySQL 数据库

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）的数

select 'abc' || 'd' from dual

两个数据执行的结果不同（语法都能通过），MySQL要写成 select concat('abc' , 'd') 的形式

autocommit 注意 mysql 这个

创建数据库的时候需要自行配置，修改my.cnf文件 autocommit = 0|OFF|1|ON

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 这种形式的连接方式

区分大小写

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