基于SQL的数据AES加解密

对于原始是明文的情况，可以使用下面的方法 ：
-- 原始内容如下：
[test]> select * from t1;
+----+--------+---------------------+
| id | remark | create_time         |
+----+--------+---------------------+
|  7 | aaabbb | 2024-09-26 11:08:23 |
|  8 | aaa    | 2024-09-26 11:07:22 |
|  9 | aaabbb | 2024-09-26 11:07:30 |
+----+--------+---------------------+
3 rows in set (0.00 sec)

如果直接使用AES_ENCRYPT会发现数据是乱码的
[test]> select id,AES_ENCRYPT(remark, 'encryption key'),create_time from t1;
+----+---------------------------------------+---------------------+
| id | AES_ENCRYPT(remark, 'encryption key') | create_time         |
+----+---------------------------------------+---------------------+
|  7 | u�?�x�U��݄u3�                             | 2024-09-26 11:08:23 |
|  8 | ���P����/Y����_                                 | 2024-09-26 11:07:22 |
|  9 | u�?�x�U��݄u3�                             | 2024-09-26 11:07:30 |
+----+---------------------------------------+---------------------+
3 rows in set (0.00 sec)


[test]> select id,remark,TO_BASE64(AES_ENCRYPT(remark, 'encryption key')) as remark_encr,create_time from t1 ;
+----+--------+--------------------------+---------------------+
| id | remark | remark_encr              | create_time         |
+----+--------+--------------------------+---------------------+
|  7 | aaabbb | daYVP8d44FUY+JTdhHUzyg== | 2024-09-26 11:08:23 |
|  8 | aaa    | hIzbFlCSpcfJL1m58dvnXw== | 2024-09-26 11:07:22 |
|  9 | aaabbb | daYVP8d44FUY+JTdhHUzyg== | 2024-09-26 11:07:30 |
+----+--------+--------------------------+---------------------+
3 rows in set (0.00 sec)

解密
create table t1_encr select id,remark,TO_BASE64(AES_ENCRYPT(remark, 'encryption key')) as remark_encr,create_time from t1 ;
[test]> SELECT id,CAST(AES_DECRYPT(FROM_BASE64(remark_encr), 'encryption key') AS CHAR) AS remark,create_time FROM t1_encr;
+----+-----------+---------------------+
| id | remark    | create_time         |
+----+-----------+---------------------+
|  7 | aaa       | 2024-01-01 00:02:01 |
|  8 | aaabbb    | 2024-01-01 00:02:01 |
|  9 | aaabbbccc | 2024-01-01 00:02:01 |
+----+-----------+---------------------+
3 rows in set (0.00 sec)

-- 安装扩展
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- 加密
SELECT pgp_sym_encrypt('Hello, World!', 'my_secret_key', 'cipher-algo=aes256');

-- 解密
SELECT pgp_sym_decrypt('U2FsdGVkX1...', 'my_secret_key', 'cipher-algo=aes256');