MySQL敏感数据加密及解密

这里主要是介绍一些可逆的加密

# 建一张测试表
CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(128) DEFAULT NULL,
  `password` blob DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

1.加密解密：encode()和decode()

都有两个参数，第一个是实际需要保存的值，第二个是盐。

# 插入一条测试语句
INSERT INTO users (`username`, `password`)VALUES('测试',encode('test1234','salt')); 

# 查询
SELECT t.id,t.username,decode(t.`password`,'salt') as `password` FROM `users` as t where decode(t.password,'salt') LIKE '%1234%';

 

 

 

2.加密解密：aes_encrypt()和aes_decrypt()　　

# 插入一条测试语句
INSERT INTO users (`username`, `password`)VALUES('aaabbb',aes_encrypt('aaabbb','salt'));

# 查询
SELECT t.idt.username,,aes_decrypt(t.`password`,'salt') as `password` FROM `users` as t where aes_decrypt(t.password,'salt') LIKE '%aa%';