create extension pgcrypto; --增加,加密解密模块; drop extension pgcrypto; --删除,加密解密模块;
定义--encrypt(bytea, bytea, text)-- update bill_his_yun set yun_ip=encode(encrypt('202.99','p08','des') ,'hex') where yun_id=299904910298845184; select decrypt(yun_ip,'p08','des') from bill_his_yun where yun_id=299904910298845184; select convert_from(decrypt(decode(yun_ip,'hex'),'p08','des'),'SQL_ASCII') as yun_ip from bill_his_yun where yun_id=299904910298845184; text加密方式 AES DES/3DES/CAST5 Blowfish 查询表时一定符合类型 select nm, encrypt(cast(tb1.nm as bytea) ,'aa','aes') from tb1 select nm,test, encrypt(cast(test as bytea) ,'aa','aes') as 加密, convert_from(decrypt(encrypt(cast(test as bytea),'aa','aes'),'aa','aes'),'SQL_ASCII') as 解密 from tb1 --------------------------------------------------------- select encrypt('123456年a','aa','aes'); --\000\003AXv\327\370\351\363\006\242\267A\245yX select convert_from(decrypt('\000\003AXv\327\370\351\363\006\242\267A\245yX','aa','aes'),'SQL_ASCII'); --123456年a select convert_from(decrypt(encrypt('123456年a','aa','aes'),'aa','aes'),'SQL_ASCII'); --123456年a —————