Doris 内置了如下加密和脱敏函数。详细使用，请参考 SQL 手册。

AES_ENCRYPT

Aes 加密函数。该函数与 MySQL 中的 AES_ENCRYPT 函数行为一致。默认采用 AES_128_ECB 算法，padding 模式为 PKCS7。底层使用 OpenSSL 库进行加密。Reference: <https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html#function_aes-decrypt>

1. select to_base64(aes_encrypt('text','F3229A0B371ED2D9441B830D21A390C3'));

3. +--------------------------------+
4. | to_base64(aes_encrypt('text')) |
5. +--------------------------------+
6. | wr2JEDVXzL9+2XtRhgIloA==       |
7. +--------------------------------+
8. 1 row in set (0.01 sec)

AES_DECRYPT

Aes 解密函数。该函数与 MySQL 中的 AES_DECRYPT 函数行为一致。默认采用 AES_128_ECB 算法，padding 模式为 PKCS7。底层使用 OpenSSL 库进行加密。

1. select aes_decrypt(from_base64('wr2JEDVXzL9+2XtRhgIloA=='),'F3229A0B371ED2D9441B830D21A390C3');
2. +------------------------------------------------------+
3. | aes_decrypt(from_base64('wr2JEDVXzL9+2XtRhgIloA==')) |
4. +------------------------------------------------------+
5. | text                                                 |
6. +------------------------------------------------------+
7. 1 row in set (0.01 sec)

MD5

计算 MD5 128-bit

1. MySQL [(none)]> select md5("abc");
2. +----------------------------------+
3. | md5('abc')                       |
4. +----------------------------------+
5. | 900150983cd24fb0d6963f7d28e17f72 |
6. +----------------------------------+
7. 1 row in set (0.013 sec)

MD5SUM

计算多个字符串 MD5 128-bit

1. MySQL > select md5("abcd");
2. +----------------------------------+
3. | md5('abcd')                      |
4. +----------------------------------+
5. | e2fc714c4727ee9395f324cd2e7f331f |
6. +----------------------------------+
7. 1 row in set (0.011 sec)

9. MySQL > select md5sum("ab","cd");
10. +----------------------------------+
11. | md5sum('ab', 'cd')               |
12. +----------------------------------+
13. | e2fc714c4727ee9395f324cd2e7f331f |
14. +----------------------------------+
15. 1 row in set (0.008 sec)

SM4_ENCRYPT

SM4 加密函数

1. MySQL > select TO_BASE64(SM4_ENCRYPT('text','F3229A0B371ED2D9441B830D21A390C3'));
2. +--------------------------------+
3. | to_base64(sm4_encrypt('text')) |
4. +--------------------------------+
5. | aDjwRflBrDjhBZIOFNw3Tg==       |
6. +--------------------------------+
7. 1 row in set (0.010 sec)

9. MySQL > set block_encryption_mode="SM4_128_CBC";
10. Query OK, 0 rows affected (0.001 sec)

12. MySQL > select to_base64(SM4_ENCRYPT('text','F3229A0B371ED2D9441B830D21A390C3', '0123456789'));
13. +----------------------------------------------------------------------------------+
14. | to_base64(sm4_encrypt('text', 'F3229A0B371ED2D9441B830D21A390C3', '0123456789')) |
15. +----------------------------------------------------------------------------------+
16. | G7yqOKfEyxdagboz6Qf01A==                                                         |
17. +----------------------------------------------------------------------------------+
18. 1 row in set (0.014 sec)

SM3

计算 SM3 256-bit

1. MySQL > select sm3("abcd");
2. +------------------------------------------------------------------+
3. | sm3('abcd')                                                      |
4. +------------------------------------------------------------------+
5. | 82ec580fe6d36ae4f81cae3c73f4a5b3b5a09c943172dc9053c69fd8e18dca1e |
6. +------------------------------------------------------------------+
7. 1 row in set (0.009 sec)

SM3SUM

计算多个字符串 SM3 256-bit

1. MySQL > select sm3("abcd");
2. +------------------------------------------------------------------+
3. | sm3('abcd')                                                      |
4. +------------------------------------------------------------------+
5. | 82ec580fe6d36ae4f81cae3c73f4a5b3b5a09c943172dc9053c69fd8e18dca1e |
6. +------------------------------------------------------------------+
7. 1 row in set (0.009 sec)

9. MySQL > select sm3sum("ab","cd");
10. +------------------------------------------------------------------+
11. | sm3sum('ab', 'cd')                                               |
12. +------------------------------------------------------------------+
13. | 82ec580fe6d36ae4f81cae3c73f4a5b3b5a09c943172dc9053c69fd8e18dca1e |
14. +------------------------------------------------------------------+
15. 1 row in set (0.009 sec)

SHA

使用 SHA1 算法对信息进行摘要处理。

1. mysql> select sha("123");
2. +------------------------------------------+
3. | sha1('123')                              |
4. +------------------------------------------+
5. | 40bd001563085fc35165329ea1ff5c5ecbdbbeef |
6. +------------------------------------------+
7. 1 row in set (0.13 sec)

SHA2

使用 SHA2 对信息进行摘要处理。

1. mysql> select sha2('abc', 224);
2. +----------------------------------------------------------+
3. | sha2('abc', 224)                                         |
4. +----------------------------------------------------------+
5. | 23097d223405d8228642a477bda255b32aadbce4bda0b3f7e36c9da7 |
6. +----------------------------------------------------------+
7. 1 row in set (0.13 sec)

9. mysql> select sha2('abc', 384);
10. +--------------------------------------------------------------------------------------------------+
11. | sha2('abc', 384)                                                                                 |
12. +--------------------------------------------------------------------------------------------------+
13. | cb00753f45a35e8bb5a03d699ac65007272c32ab0eded1631a8b605a43ff5bed8086072ba1e7cc2358baeca134c825a7 |
14. +--------------------------------------------------------------------------------------------------+
15. 1 row in set (0.13 sec)

17. mysql> select sha2(NULL, 512);
18. +-----------------+
19. | sha2(NULL, 512) |
20. +-----------------+
21. | NULL            |
22. +-----------------+
23. 1 row in set (0.09 sec)

DIGITAL_MASKING

别名函数，原始函数为 concat(left(id,3),'****',right(id,4))。

将输入的 digital_number 进行脱敏处理，返回遮盖脱敏后的结果。digital_number 为 BIGINT 数据类型。

将手机号码进行脱敏处理

1. mysql select digital_masking(13812345678);
2. +------------------------------+
3. | digital_masking(13812345678) |
4. +------------------------------+
5. | 138****5678                  |
6. +------------------------------+