MySQL敏感數(shù)據(jù)加密的實(shí)現(xiàn)方案
一、準(zhǔn)備工作（環(huán)境要求）
1、版本要求
? mysql必須是mysql5.6以上版本,通過以下命令查看是否版本支持
mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.24 | +-----------+
2、字段類型要求
? 數(shù)據(jù)表字段類型必須是以下類型中的其中一個(gè)
varbinary、binary、blob
二、數(shù)據(jù)庫演示
1、創(chuàng)建演示表
mysql> CREATE TABLE `user` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `username` varchar(20) DEFAULT NULL, `mobile` varbinary(50) NOT NULL, `id_card` varbinary(155) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
注意： mobile（手機(jī)號） 和 id_card（身份證號） 的字段類型為varbinary，我們將對這兩個(gè)字段進(jìn)行加密存儲。
2、插入加密數(shù)據(jù)
mysql> insert into user(username, mobile, id_card)
values('Tom', aes_encrypt('15101123111', 'test_key'), aes_encrypt('372522195710100019','test_key'));
mysql> insert into user(username, mobile, id_card) values('Mary', aes_encrypt('15101123111', 'key'), aes_encrypt('372522195710100019','key'));
注意：這里在使用**insert** 插入時(shí)，將需要加密的字段使用aes_encrypt函數(shù)并指定秘鑰key。
3、查看插入數(shù)據(jù)
mysql> select * from user; -----------++----+----------+------------------------------------+--------------------------------------------------------- | id | username | mobile | id_card | +----+----------+------------------------------------+--------------------------------------------------------------------+ | 1 | Tom | 0x28E415A075D38ECFEE0AFB86027231BD | 0x45397D31A49C50EEFE669D2145110F134A90D6E834084FBA38E4B5098F2EDAED | | 2 | Mary | 0xC932282B9EBC4FC82A225A56FB12BB63 | 0xF748DFDB3C0994DB9C06F4FB863AAAE5DC1685EA49D8AAA5C28952D5BFDD8A35 | +----+----------+------------------------------------+--------------------------------------------------------------------+
**提示：**此處是直接查詢，返回的是加密后的值，神仙也看不出是啥，如果猜測的話首先要猜出加密方式，其次是秘鑰。
4、解密數(shù)據(jù)
mysql> select id, username, cast(aes_decrypt(mobile, 'test_key') as char charset utf8 ) as mobile from user where id = 1; +----+----------+-------------+ | id | username | mobile | +----+----------+-------------+ | 1 | Tom | 15101123111 | +----+----------+-------------+ mysql> select id, username, cast(aes_decrypt(id_card, 'key') as char charset utf8 ) as id_card from user where id = 2; +----+----------+--------------------+ | id | username | id_card | +----+----------+--------------------+ | 2 | Mary | 372522195710100019 | +----+----------+--------------------+
注意：
? 1）、id為1的那條數(shù)據(jù)的加密密鑰為key 為 test_key,id 為2的加密密鑰為key 為 key 。 解密時(shí)如果密鑰不對將查詢結(jié)果為null。
? 2）、解密函數(shù)aes_decrypt 外層需要使用cast 進(jìn)行處理，否則將返回二進(jìn)制值而不是解密后的值。
三、Java + mybatis 實(shí)現(xiàn)加解密
1、Java代碼中什么都不需要?jiǎng)?/h3>
提示： 此處java使用的是mybatis持久化數(shù)據(jù)，如果使用hibernate或mybatis-plus等框架，請自行研究，理論上通用但需要變通一下
2、mybatis中插入和更新用法
<!-- 插入 -->
<insert id="saveCertificate" parameterType="com.test.candidate.bean.Certificate">
insert into certificate (
oa_serial,
begin_date,
end_date,
certificate_type,
certificate_code,
certificate_code_again,
address,
country,
created_at,
updated_at
) values (
#{oaSerial},
#{beginDate},
#{endDate},
#{certificateType},
AES_ENCRYPT(#{certificateCode,jdbcType=VARCHAR},'perinfo'),
AES_ENCRYPT(#{certificateCodeAgain,jdbcType=VARCHAR},'perinfo'),
#{address},
#{country},
#{createdAt},
#{updatedAt}
);
</insert>
<!-- 更新 -->
<update id="updateCertificate" parameterType="com.test.candidate.bean.Certificate">
update certificate set
begin_date = #{beginDate},
end_date = #{endDate},
certificate_type = #{certificateType},
certificate_code = AES_ENCRYPT(#{certificateCode,jdbcType=VARCHAR},'perinfo'),
certificate_code_again = AES_ENCRYPT(#{certificateCodeAgain,jdbcType=VARCHAR},'perinfo'),
address = #{address},
country = #{country},
updated_at = #{updatedAt}
where oa_serial = #{oaSerial} and id = #{id}
</update>
注意：插入和更新是加密：AES_ENCRYPT(#{certificateCode,jdbcType=VARCHAR},'perinfo')
3、mybatis中查詢用法
<!-- 查詢 -->
<select id="findList" resultMap="PartCertificateMap">
SELECT
begin_date,
end_date,
certificate_type,
cast(AES_DECRYPT(certificate_code,'perinfo') as char charset utf8) certificate_code,
cast(AES_DECRYPT(certificate_code_again,'perinfo') as char charset utf8) certificate_code_again,
address,
country
FROM certificate
where
oa_serial = #{oaSerial}
</select>
**注意：**查詢時(shí)解密：cast(AES_DECRYPT(certificate_code,'perinfo') as char charset utf8) certificate_code,
四、其他說明
網(wǎng)上有人說無需更改數(shù)據(jù)類型，直接將加密數(shù)據(jù)存儲到carchar類型的字段中，然后將字符集改成latin1, 雖然此種方式確實(shí)也能實(shí)現(xiàn)加解密，但是mysql中insert 時(shí)，會報(bào)warning， 并且可能會帶來隱患，使用以下命令查看警告信息
mysql> show warnings();
并不推薦此種方式：
mysql> CREATE TABLE t_passwd_3(pass varchar(32)) CHARSET latin1;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t_passwd_3 SELECT AES_ENCRYPT('text', 'key3');
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> SELECT AES_DECRYPT(pass, 'key3') FROM t_passwd_3;
+---------------------------+
| AES_DECRYPT(pass, 'key3') |
+---------------------------+
| text |
+---------------------------+
1 row in set (0.00 sec)
MySQL官方給出的描述信息如下：
Many encryption and compression functions return strings for which the result might contain arbitrary byte values. If you want to store these results, use a column with a VARBINARY or BLOB binary string data type. This will avoid potential problems with trailing space removal or character set conversion that would change data values, such as may occur if you use a nonbinary string data type (CHAR, VARCHAR, TEXT).
大意是，如果用此方法，直接將加密后的串存入char/varchar/text類型中，在做字符轉(zhuǎn)換的時(shí)或空格被刪除時(shí)，可能會帶來潛在的影響。
以上就是MySQL敏感數(shù)據(jù)加密的實(shí)現(xiàn)方案的詳細(xì)內(nèi)容，更多關(guān)于MySQL敏感數(shù)據(jù)加密的資料請關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
Mysql數(shù)據(jù)庫值的添加、修改、刪除及清空操作實(shí)例
這篇文章主要給大家介紹了關(guān)于Mysql數(shù)據(jù)庫值的添加、修改、刪除及清空操作的相關(guān)資料,文中通過示例代碼以及圖文介紹的非常詳細(xì),需要的朋友可以參考下2021-06-06
這篇文章主要給大家介紹了關(guān)于MySQL InnoDB中鎖機(jī)制的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對大家學(xué)習(xí)或者使用MySQL具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧2019-05-05
一般情況下,我們排序都是直接利用 order by 字段 asc/desc;但是如果要排序的字段數(shù)據(jù)格式并不能直接實(shí)現(xiàn),或者說我們需要指定的順序且沒有什么規(guī)律,簡單的order by字段就實(shí)現(xiàn)不了,所以本文給大家介紹了MySQL實(shí)現(xiàn)字段的自定義排序的方法,需要的朋友可以參考下2024-04-04
一文了解MySQL Optimizer Trace的神奇功能
這篇文章主要為大家介紹了一文了解MySQL Optimizer Trace的神奇功能詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪2023-06-06
MySQL5.7 如何通過邏輯備份遷移到GreatSQL及注意事項(xiàng)
在將數(shù)據(jù)庫從MySQL 5.7遷移到GreatSQL8.0.32時(shí),由于數(shù)據(jù)量較小且關(guān)注安全性,決定使用mysqldump執(zhí)行邏輯備份,并將數(shù)據(jù)導(dǎo)入GreatSQL,這篇文章主要介紹了MySQL5.7 通過邏輯備份遷移到GreatSQL注意事項(xiàng),需要的朋友可以參考下2024-06-06
作為一名Java開發(fā)工程師,在處理高并發(fā)業(yè)務(wù)時(shí),MySQL索引失效是導(dǎo)致系統(tǒng)性能下降的"隱形殺手",本文將結(jié)合實(shí)際案例,深度剖析索引失效的8大常見場景,并提供Java代碼層面的優(yōu)化建議,幫助開發(fā)者避開性能陷阱,需要的朋友可以參考下2025-05-05
Mysql使用insert插入多條記錄 批量新增數(shù)據(jù)
這篇文章主要介紹了Mysql使用insert插入多條記錄批量新增數(shù)據(jù),需要的朋友可以參考下2017-08-08
MySQL的分區(qū)表功能在某些場景下可以顯著提高查詢效率,本文主要介紹了MySQL的分區(qū)表使用場景及示例小結(jié),具有一定的參考價(jià)值,感興趣的可以了解一下2024-06-06
MAC系統(tǒng)自帶apache和php，但是沒有mysql，那么我們只好自力更生了，經(jīng)過一番研究，借鑒網(wǎng)上一位大神的文章，終于成功安裝上了mysql,這里推薦給有需要的朋友2014-10-10
MySQL中將逗號分隔的字段轉(zhuǎn)換為多行數(shù)據(jù)的方法
在我們的實(shí)際開發(fā)中,經(jīng)常需要存儲一些字段,它們使用像,?-?等連接符進(jìn)行連接,在查詢過程中,有時(shí)需要將這些字段使用連接符分割,然后查詢多條數(shù)據(jù),今天,我們將使用一個(gè)實(shí)際的生產(chǎn)場景來詳細(xì)解釋這個(gè)解決方案,需要的朋友可以參考下2024-04-04