mysql 同步redis_Redis与Mysql实时同步？-问答-阿里云开发者社区-阿里云

你可以通过MySql自定义函数 (user-defined function UDF)，添加一个请求RedisClient的方法，然后在你需要MySql实时同步的地方，进行触发器掉用自定义函数，获取触发器内操作的数据，然后通过UDF直接同步到Redis中。 之前做的类似触发器，你可以参考一下：

-- 选择执行数据库 use taxdischosting; -- 删除UDF自定义Redis客户端方法 drop function if exists rediscli; -- 注册添加UDF自定义Redis客户端方法 create function rediscli returns integer soname "RedisDll.dll";

-- 删除操作触发器 drop trigger if exists insert_redis; drop trigger if exists update_redis; drop trigger if exists delete_redis;

-- 创建添加触发器 create trigger insert_redis after insert on tax_disc for each row begin -- 设置REDIS中KEY set @k=CONCAT(new.plateno,'_',new.platesign); -- 设置REDIS中VAL set @v=CONCAT( "{", """""""plateno"""""":""""""",IFNULL(new.plateno,-1),""""""",", """""""platenumber"""""":""""""",IFNULL(new.platenumber,-1),""""""",", """""""platename"""""":""""""",HEX(IFNULL(new.platename,-1)),""""""",", """""""platesign"""""":""""""",IFNULL(new.platesign,-1),""""""",", """""""stime"""""":""""""",IFNULL(new.stime,-1),""""""",", """""""etime"""""":""""""",IFNULL(new.etime,-1),""""""",", """""""ctime"""""":""""""",IFNULL(new.ctime,-1),""""""",", """""""cuser"""""":""""""",IFNULL(new.cuser,-1),"""""""", "}" );

-- 掉用UDF客户端函数同步REDIS

select rediscli('D:\\RedisApp\\Redis\\redis-cli.exe',CONCAT('-h 127.0.0.1 -p 6379 -n 2 set ',@k,' ',@v)) into @ret;

end;

-- 创建更新触发器 create trigger update_redis after update on tax_disc for each row begin -- 设置REDIS中KEY set @k=CONCAT(new.plateno,'_',new.platesign); -- 设置REDIS中VAL set @v=CONCAT( "{", """""""plateno"""""":""""""",IFNULL(new.plateno,-1),""""""",", """""""platenumber"""""":""""""",IFNULL(new.platenumber,-1),""""""",", """""""platename"""""":""""""",HEX(IFNULL(new.platename,-1)),""""""",", """""""platesign"""""":""""""",IFNULL(new.platesign,-1),""""""",", """""""stime"""""":""""""",IFNULL(new.stime,-1),""""""",", """""""etime"""""":""""""",IFNULL(new.etime,-1),""""""",", """""""ctime"""""":""""""",IFNULL(new.ctime,-1),""""""",", """""""cuser"""""":""""""",IFNULL(new.cuser,-1),"""""""", "}" );

-- 掉用UDF客户端函数同步REDIS

select rediscli('D:\\RedisApp\\Redis\\redis-cli.exe',CONCAT('-h 127.0.0.1 -p 6379 -n 2 set ',@k,' ',@v)) into @ret;

end;

-- 创建删除触发器 create trigger delete_redis after delete on tax_disc for each row begin -- 设置REDIS中KEY set @k=CONCAT('D:\RedisApp\Redis\redis-cli.exe','-h 127.0.0.1 -p 6379 -n 2 del ',old.plateno,'_',old.platesign); -- 掉用UDF客户端函数同步REDIS select rediscli(@k) into @ret; end;