dm-mysql

DM - mysql 通用语法/对比

由于达梦数据库语法更偏向oracle, 同时兼容部分mysql, 因此整理一些两者都通用语法. 同时对不通用的语法做些许对比

字符/数字

-- 33 补到 8位, 用0补充
select LPAD(33, 8, 0)
select RPAD(33, 8, 0)

-- instr/locate/position
select INSTR('abcd', 'abc') -- INSTR(str, substr) 获取子串第一次出现的索引
select LOCATE('abc', 'abcd') -- LOCATE(substr,str) 返回substr在字符串str 的第一个出现的位置,没有则返回0 
select POSITION('abc' in 'abcd')  -- POSITION(substr in str) 返回substr在字符串str 的第一个出现的位置,没有则返回0

select ifnull('1234', '123') -- eExpression1,eExpression2 不为 NULL的值, 都为NULL则返回NULL
select isnull() -- null返回1, 不为null返回0

-- 大小写转换
select upper('wangdi');
select lower('WANGDI');

select abs(-2.4) -- 绝对值
select CEIL(1.09) -- 向上
select FLOOR(1.08)  -- 向下
select truncate(1.23456, 1) -- 截断
select mod(-10, 3) -- 取模

日期时间

select timestampadd(second, floor(DBMS_RANDOM.VALUE(45, 120), '2023-01-01'))  -- 随机获取2023-01-01 00:00:00 之上45s到120s之间的时间

-- ====获取具体时间
select year(now()), month(now()), DAY(NOW()), CURDATE(), curtime() -- 通用

-- ====格式化日期
select date_format('2022-02-02','%Y%m%d'), date_format('2022-02-02','%Y"年"%m"月"%d')   -- 20220202 | 2022年02月02 (DM)
SELECT TO_CHAR(NOW(),'yyyy"年"MM"月"dd"日"'), TO_CHAR(NOW(),'yyyy-MM-dd') -- 2022年02月02 | 2022-02-02 (DM)
select date_format('2022-02-02','%Y%m%d'), date_format('2022-02-02','%Y年%m月%d %H小时%i分钟%s秒')  -- 20220202 | 2022年02月02 (mysql)

-- ====时间差
select datediff(HH,now(),'2022-02-12'); -- param1 => param2 经过的时间(DM)
select datediff('2020-06-06','2022-06-06') -- param1 => param2 经过的天数(mysql)
select TIMESTAMPDIFF(minute,'2022-02-12 00:00:00',NOW()); -- param1 => param2 经过的时间(mysql)

-- ====时间函数
select date_sub(curdate(),interval '1' month) -- 距离param1 1个月前的日期 (DM)
select date_add(now(), interval -30 minute) -- 距离param1 30分钟前的时间(mysql)

多行转为一列

-- ====DM
select id, wmsys.wm_concat(name) as name, wmsys.wm_concat(age) as age from test group id;
-- ====mysql
SELECT GROUP_CONCAT(name SEPARATOR ',') as name from test;

动态新增(更新)数据

-- ====DM
MERGE INTO <表名> T1 USING(
	<查询语句>
)T2 ON (T2.<字段> = T1.<字段>) 
WHEN MATCHED THEN UPDATE SET 
T1.<更新字段>=T2.<更新字段>
WHEN NOT MATCHED THEN 
INSERT (<字段1>,<字段2>)
VALUES (<字段1>,<字段2>);

-- ====mysql(ON DUPLICATE KEY UPDATE 必须要有主键冲突才可以触发更新)
INSERT INTO <表名1> (<字段1>, <字段2>)
SELECT <字段1>, <字段2>
FROM <表名2> 
ON DUPLICATE KEY UPDATE 
    <更新字段1> = values(<更新字段1>),
    <更新字段2> = values(<更新字段2>);

删除重复数据

-- 查询表数据量
select count(1) from <表名>
-- 查询去重后的数据量
select count(1) from(
select distinct <字段1>,<字段2> from <表名>
)
-- 查询要去重的数据量
select count(1) from(
select <字段1>,<字段2> from <表名>
group by <字段1>,<字段2> having count(*) >1
)
-- ====DM
delete from <表名> T1
where 
(T1.idcard,T1.certnum) 
in (select <字段1>,<字段2> from <表名> group by  <字段1>,<字段2> having count(*) >1)
and 
rowid 
not in (select min(rowid) from <表名> group by  <字段1>,<字段2> having count(*) >1);

-- ====mysql(由于myql没有默认rowid, 因此需要有主键id作为控制)
delete from <表名> T1
where 
(T1.<字段1>,T1.<字段2>) 
in (select <字段1>,<字段2> from <表名> group by  <字段1>,<字段2> having count(*) >1)
and 
rowid 
not in (select min(rowid) from <表名> group by  <字段1>,<字段2> having count(*) >1);

存储过程&事件

-- ====DM(达梦建议通过DM管理工具代理-计划任务实现)
-- ====mysql
-- ※※※ 存储过程
drop procedure if exists <pro_存储名称>;
delimiter $$
CREATE PROCEDURE <pro_存储名称>()
BEGIN
	-- 逻辑
END $$
delimiter ;

-- 查看
show procedure status;
show create procedure <pro_存储名称>;

-- ※※※ 事件
-- 创建事件
drop event if exists `<event_事件名称>`;
create EVENT
    `<event_事件名称>`  -- 创建一个事件
    ON SCHEDULE EVERY
        1 DAY -- 每间隔一天执行一次
        STARTS
            '2023-04-25 00:00:00' -- 从2023-04-25 00:00:00后开始
    ON COMPLETION
        PRESERVE ENABLE  -- 执行完成之后不删除定时器
    DO
    call <pro_存储名称>(); -- 每次触发定时器时执行的语句

-- 查询事件功能
SELECT @@event_scheduler;
-- 开启事件功能
SET GLOBAL event_scheduler = 1;
-- 关闭事件功能
SET GLOBAL event_scheduler = 0;
-- 关闭事件
ALTER EVENT event_sync_cxpt_reg_threetype ON COMPLETION PRESERVE DISABLE;
-- 开启事件
ALTER EVENT event_sync_cxpt_reg_threetype ON COMPLETION PRESERVE ENABLE;
-- 查看当前事件
SHOW EVENTS;

触发器

-- ====DM
-- 知识点: DM抛异常处理
-- 功能: 确保表中某字段不可重复
create  or replace trigger "模式名"."触发器名"
before INSERT 
on "模式名"."表名"
referencing OLD ROW AS "OLD" NEW ROW AS "NEW"
for each row
BEGIN
IF EXISTS(SELECT 1 FROM 表名 WHERE 字段名 = :NEW.字段名)
THEN  RAISE_APPLICATION_ERROR(-20001, '字段已存在，不允许插入数据');
END IF;
END;

函数

-- ====DM
-- 知识点: 异常抓取
/***Manager***/CREATE OR REPLACE FUNCTION "DMSTAND"."fun_to_date"("date_str" IN VARCHAR(50))
RETURN DATETIME(6)
AS
BEGIN
	/*执行体*/
	BEGIN
	-- 定义异常处理
    return(to_date(date_str));
    EXCEPTION
    	WHEN OTHERS THEN
        return(null);
    END;
END;