通过MySQL自动同步刷新Redis

link之家

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

相关文章推荐

文雅的跑步鞋 · sql 当前日期减一天前一天的数-掘金· 1 年前 ·

微醺的墨镜 · 从出生日期计算年龄 [重复]· 1 年前 ·

干练的打火机 · C# WPF ...· 1 年前 ·

千杯不醉的松鼠 · MySQL中的类型：BigInt（20）与I ...· 1 年前 ·

乐观的桔子 · c++ - ...· 1 年前 ·

SQL Type	C/C++ Type
STRING	char *
INTEGER	long long
REAL	double

[root@localhost zhxilin]# yum install mysql-devel -y

 1 /*simple.cpp*/ 2 #include <mysql.h> 3  4 extern "C" long long simple_add(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error) 5 { 6     int a = *((long long *)args->args[0]); 7     int b = *((long long *)args->args[1]); 8     return a + b; 9 }10 11 extern "C" my_bool simple_add_init(UDF_INIT *initid, UDF_ARGS *args, char *message)12 {13     return 0;14 }

extern "C" { ... }

[zhxilin@localhost mysql-redis-test]$ g++ -shared -fPIC -I /usr/include/mysql -o simple_add.so simple.cpp

[root@localhost mysql-redis-test]# cp simple_add.so /usr/lib64/mysql/plugin/

mysql> CREATE FUNCTION simple_add RETURNS INTEGER SONAME 'simple_add.so';
Query OK, 0 rows affected (0.04 sec)

mysql> select simple_add(10, 5);+-------------------+| simple_add(10, 5) |+-------------------+|                15 |+-------------------+1 row in set (0.00 sec)

mysql> DROP FUNCTION simple_add;
Query OK, 0 rows affected (0.03 sec)

[root@localhost dev]# yum install boost boost-devel

[root@localhost dev]# git clone https://github.com/mrpi/redis-cplusplus-client

 1 /* test.cpp */ 2 #include <stdio.h> 3 #include <mysql.h> 4 #include "redisclient.h" 5 using namespace boost; 6 using namespace std; 7  8 static redis::client *m_client = NULL; 9 10 extern "C" char *redis_hset(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length, char *is_null, char *error) {11     try {12         // 连接Redis13         if(NULL == m_client) {14             const char* c_host = getenv("REDIS_HOST");15             string host = "127.0.0.1";16             if(c_host) {17                 host = c_host;18             }19             m_client = new redis::client(host);20         }        21 22         if(!(args->args && args->args[0] && args->args[1] && args->args[2])) {23             *is_null = 1;24             return result;25         }26 27         // 调用hset插入一个哈希表28         if(m_client->hset(args->args[0], args->args[1], args->args[2])) {29             return result;30         } else {31             *error = 1;32             return result;33         }34     } catch (const redis::redis_error& e) {35         return result;36     }37 }38 39 extern "C" my_bool redis_hset_init(UDF_INIT *initid, UDF_ARGS *args, char *message) {40     if (3 != args->arg_count) {41         // hset(key, field, value) 需要三个参数42         strncpy(message, "Please input 3 args for: hset('key', 'field', 'value');", MYSQL_ERRMSG_SIZE);43         return -1;44     }45     if (args->arg_type[0] != STRING_RESULT  || 
46         args->arg_type[1] != STRING_RESULT  || 
47         args->arg_type[2] != STRING_RESULT) { 
48         // 检查参数类型49         strncpy(message, "Args type error: hset('key', 'field', 'value');", MYSQL_ERRMSG_SIZE);50         return -1;51     }52 53     args->arg_type[0] = STRING_RESULT;54     args->arg_type[1] = STRING_RESULT;55     args->arg_type[2] = STRING_RESULT;56 57     initid->ptr = NULL;58     return 0;59 }

[zhxilin@localhost mysql-redis-test]$ g++ -shared -fPIC -I /usr/include/mysql -lboost_serialization -lboost_system -lboost_thread -o libmyredis.so anet.c test.cpp

[root@localhost mysql-redis-test]# cp libmyredis.so /usr/lib64/mysql/plugin/ & chmod 777 /usr/lib64/mysql/plugin/libmyredis.so

mysql> DROP FUNCTION IF EXISTS `redis_hset`;
Query OK, 0 rows affected (0.16 sec)
mysql> CREATE FUNCTION redis_hset RETURNS STRING SONAME 'libmyredis.so';
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT redis_hset('zhxilin', 'id', '09388334');+-----------------------------------------+| redis_hset('zhxilin', 'id', '09388334') |+-----------------------------------------+| 0                                                     |+-----------------------------------------+1 row in set (0.00 sec)

127.0.0.1:6379> HGETALL zhxilin1) "id"2) "09388334"

CREATE TRIGGER trigger_name
trigger_time
trigger_event ON table_nameFOR EACH ROW
trigger_statement

BEGIN[statement_list]END

mysql> DELIMITER $

mysql > DELIMITER $      > CREATE TRIGGER tg_student 
      > AFTER INSERT on Student 
      > FOR EACH ROW 
      > BEGIN
      > SET @id = (SELECT redis_hset(CONCAT('stu_', new.Sid), 'id', CAST(new.Sid AS CHAR(8))));      > SET @name = (SELECT redis_hset(CONCAT('stu_', new.Sid), 'name', CAST(new.Sname AS CHAR(20))));      > Set @age = (SELECT redis_hset(CONCAT('stu_', new.Sid), 'age', CAST(new.Sage AS CHAR))); 
      > Set @gender = (SELECT redis_hset(CONCAT('stu_', new.Sid), 'gender', CAST(new.Sgen AS CHAR))); 
      > Set @dept = (SELECT redis_hset(CONCAT('stu_', new.Sid), 'department', CAST(new.Sdept AS CHAR(10))));    
      > END $

mysql> SHOW TRIGGERS;

mysql> DROP TRIGGER tg_student;

mysql> INSERT INTO Student VALUES('09388165', 'Rose', 19, 'F', 'SS3-205');
Query OK, 1 row affected (0.27 sec)

mysql> SELECT * FROM Student;+----------+---------+------+------+---------+| Sid      | Sname   | Sage | Sgen | Sdept   |+----------+---------+------+------+---------+| 09388123 | Lucy    |   18 | F    | AS2-123 || 09388165 | Rose    |   19 | F    | SS3-205 || 09388308 | zhsuiy  |   19 | F    | MD8-208 || 09388318 | daemon  |   18 | M    | ZS4-630 || 09388321 | David   |   20 | M    | ZS4-731 || 09388334 | zhxilin |   20 | M    | ZS4-722 |+----------+---------+------+------+---------+6 rows in set (0.00 sec)

127.0.0.1:6379> HGETALL stu_09388165 1) "id" 2) "09388165" 3) "name" 4) "Rose" 5) "age" 6) "19" 7) "gender" 8) "F" 9) "department"10) "SS3-205"

云+社区

在这篇文章中：

一. 什么是UDF

1. xxx()

2. xxx_init()

3. xxx_deinit()

4. xxx_add()

5. xxx_clear()

二. UDF函数的基本使用

三. 在UDF中访问Redis

四. 通过MySQL触发器刷新Redis

总结