MySql的md5加密

MySql的md5加密  

一、任务：

写一个crc的function，

写三个触发器，当users表的email字段有变更(insert, delete, update)时，将connect_email_hash 中的email_hash同步更新

CREATE TABLE `connect_user` (

`user_id` int(11) NOT NULL,

`app_id` int(11) NOT NULL,

`state` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,

PRIMARY KEY (`user_id`, `app_id`)

)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

CREATE TABLE `connect_email_hash` (

`user_id` int(11) NOT NULL,

`email_hash` VARCHAR(255) NOT NULL,

PRIMARY KEY (`user_id`)

)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

二、实现：

################## CRC ######################

create function getEmailHash(email varchar(255))

   returns varchar(255)

    begin

    declare emailHash varchar(255);

   declare emailCRC varchar(255);

    declare emailMD5 varchar(255);

   select crc32(email) into emailCRC;

    select md5(email) into emailMD5;

    set emailHash=concat(emailCRC,'_',emailMD5);

    return emailHash;

   end; //

Query OK, 0 rows affected (0.02 sec)

#### ############ 触发器 ######################

create trigger users_test_email_insert

AFTER insert on users_test for EACH ROW

    BEGIN

   declare email_hash VARCHAR(255);

   set email_hash=getEmailHash(new.email);

   insert into connect_email_hash(user_id,email_hash) values (new.id,email_hash);

END

create trigger user_test_email_update

AFTER update on users_test For EACH ROW

BEGIN

   declare email_hash VARCHAR(255);

   UPDATE connect_email_hash

    SET email_hash=getEmailHash(new.email)

   WHERE user_id=new.id;

END

create trigger users_test_email_delete

AFTER delete on users_test For EACH ROW

BEGIN

delete from connect_email_hash

WHERE user_id=old.id;

END

三、测试：

################## sql #################

set global log_bin_trust_routine_creators=1;

help 'show' ;

show create function getEmailHash ;//

create table users_test like users;

mysql> insert into users_test select * from users limit 10;

mysql> insert into connect_email_hash(user_id) select id from users limit 10;

insert into connect_user(user_id,app_id) values(10000,00001);

update users_test set email='rongjin.zheng@opi-corp.com' where id=277406 ;  

mysql> select * from connect_user;//

mysql> select * from connect_email_hash;//

select id,name,email from users_test;//

mysql> desc users_test;

mysql> delete from users_test where id=27740660 ;  

insert into users_test(id,password,email,name) values(100000,'taohx','taohx@yahoo.com.cn','taohuixiang');

mysql> update users_test set email='rongjin.zheng@opi-corp.com' where id=27740660 ;