Migrate oracle to openGauss/oceanbase/达梦/kingbase： md5 function

by

在十年前简单测试过oracle 9i 的加密解密用法之dbms_obfuscation_toolkit(二)，其中有md5单向加密， 最近在oracle迁移到opengauss项目中用到了md5， 这里简单记录替换方案，在pg或og中直接就有md5 function. 在mysql及Mysql系的产品和ocenabse, 达梦一样存在该函数md5。

Oracle dbms_obfuscation_toolkit.MD5

sys@ORA19C 11:40:24> create or replace function encrypt_md5(p_in varchar2) return varchar2
   is
    begin
     return RawToHex(UTL_RAW.CAST_TO_RAW(dbms_obfuscation_toolkit.MD5(input_string=>p_in)));
    end;
   /  2    3    4    5    6

Function created.

sys@ORA19C 11:41:09> select encrypt_md5('abc') from dual;

ENCRYPT_MD5('ABC')
---------------------------------------------------------------------------------    
900150983CD24FB0D6963F7D28E17F72

sys@ORA19C 11:52:50> create or replace function encrypt_md5_raw(p_in varchar2) return raw
   is
   begin
    return UTL_RAW.CAST_TO_RAW(dbms_obfuscation_toolkit.MD5(input_string=>p_in));
   end;
   /  

Function created.

Elapsed: 00:00:00.00
sys@ORA19C 11:53:06> select encrypt_md5_raw('abc') from dual;

ENCRYPT_MD51('ABC')
-------------------------------------------------------------------------------------  
900150983CD24FB0D6963F7D28E17F72

Linux md5sum

[oracle@ora19c:/home/oracle]$ echo -n "abc"|md5sum
900150983cd24fb0d6963f7d28e17f72  -

openGauss/ PostgreSQL md5

[omm@localhost ~]$ gsql -d postgres
gsql ((openGauss 6.0.0-RC1 build ed7f8e37) compiled at 2024-03-31 11:59:31 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

openGauss=# select md5('abc');
               md5
----------------------------------
 900150983cd24fb0d6963f7d28e17f72
(1 row)

MySQL MD5

select md5('abc');

md5('abc')
------------------------------------
900150983cd24fb0d6963f7d28e17f72

KingBASE md5

kingbase=# select md5('abc');
               md5
----------------------------------
 900150983cd24fb0d6963f7d28e17f72
(1 row)

达梦 md5

select to_char(md5('abc'));
900150983CD24FB0D6963F7D28E17F72

Oceanbase md5

obclient [oceanbase]> select md5('abc') from dual;
+----------------------------------+
| md5('abc')                       |
+----------------------------------+
| 900150983cd24fb0d6963f7d28e17f72 |
+----------------------------------+
1 row in set (0.078 sec)

— enjoy —