Memcache mysql触发器操作memcache

清疚 2022-08-07 10:49 201阅读 0赞

一般对memcache的操作一般都是放程序里面去操作的，如：新增，更新，删除等。mysql 5.1支持触发器以及自定义函数接口(UDF)的特性，如果配合libmemcache以及Memcached Functions for MySQL，就能够实现 memcache 的自动更新。这样代码端就会简单一点。但是利用mysql来操作memcache，比较适合实现简单的方式，不建议生产使用。  
**一、安装mysql（５.1版本以上）和memcache略  
二、libmemcached的安装**  
libmemcached下载：http://download.tangent.org/  
tar zxvf libmemcached-0.37.tar.gz    
cd libmemcached-0.37    
./configure --prefix=/usr/local/libmemcached37 --with-memcached    
make && make install    
安装遇到的问题：当时我下载的是libmemcached-0.42.tar.gz，安装memcached\_functions\_mysql过程中遇到这样一个问题  
servers.c:263:28: error: 'memcached\_st' has no member named 'hosts'  
servers.c:264:28: error: 'memcached\_st' has no member named 'hosts'  
后来我在网上查一下，libmemcached-0.37没有这个问题。  
**三、安装memcached\_functions\_mysql**  
tar xzf memcached\_functions\_mysql-0.9.tar.gz    
cd memcached\_functions\_mysql-0.9    
./configure --prefix=/usr/local/memcache\_mysql --with-mysql-config=/usr/local/mysql/bin/mysql\_config --with-libmemcached=/usr/local/libmemcached37    
make && make install    
cp /usr/local/memcache\_mysql/lib/libmemcached\_functions\*   /usr/local/mysql/lib/mysql/plugin    
别忘了加上--with-libmemcached=/usr/local/libmemcached37不然会报以下错误  
checking for mysql\_config... /usr/bin/mysql\_config  
checking for libmemcached >= 0.17... configure: error: libmemcached not found  
创建mysql的memcache操作函数  
mysql <memcached\_functions\_mysql-0.9/sql/install\_functions.sql  
注意路径不要错了。install\_functions.sql定义了一些memcache的操作函数：如下  
\#cat memcached\_functions\_mysql-0.9/sql/install\_functions.sql |awk '\{if($0 !~ /^$/ )\{print $3;\} \}

memc_add  
    memc_add_by_key  
    memc_servers_set  
    memc_servers_version  
    memc_server_count  
    memc_set  
    memc_set_by_key  
    memc_cas  
    memc_cas_by_key  
    memc_get  
    memc_get_by_key  
    memc_delete  
    memc_delete_by_key  
    memc_append  
    memc_append_by_key  
    memc_prepend  
    memc_prepend_by_key  
    memc_increment  
    memc_decrement  
    memc_replace  
    memc_replace_by_key  
    memc_servers_behavior_set  
    memc_servers_behavior_get  
    memc_behavior_set  
    memc_behavior_get  
    memc_list_behaviors  
    memc_list_hash_types  
    memc_list_distribution_types  
    memc_udf_version  
    memc_libmemcached_version  
    memc_stats  
    memc_stat_get_keys  
    memc_stat_get_value

到这儿安装基本上结束，下面我们来测试一下。  
**四、测试**  
1.启动memcached。  
2.创建数据库表  
//创建一个测试有    
drop table if exists urls;    
create table urls (    
 id int(3) not null,    
 url varchar(64) not null default '',    
 primary key (id)    
 );     
//连接memcched，根启动memcahed的端口要一样    
select memc\_servers\_set('127.0.0.1:12000,127.0.0.1:13000');    
//设置一个开始序列    
select memc\_set('urls:sequence', 0);    
    
//创建插入memcached触发器    
DELIMITER |    
    
DROP TRIGGER IF EXISTS url\_mem\_insert |    
CREATE TRIGGER url\_mem\_insert    
BEFORE INSERT ON urls    
FOR EACH ROW BEGIN    
 SET NEW.id= memc\_increment('urls:sequence');    
 SET @mm= memc\_set(concat('urls:',NEW.id), NEW.url);    
END |    
    
//创建更新memcached触发器    
DROP TRIGGER IF EXISTS url\_mem\_update |    
CREATE TRIGGER url\_mem\_update    
BEFORE UPDATE ON urls    
FOR EACH ROW BEGIN    
 SET @mm= memc\_replace(concat('urls:',OLD.id), NEW.url);    
END |    
    
//创建删除memcached触发器    
DROP TRIGGER IF EXISTS url\_mem\_delete |    
CREATE TRIGGER url\_mem\_delete    
BEFORE DELETE ON urls    
FOR EACH ROW BEGIN    
 SET @mm= memc\_delete(concat('urls:',OLD.id));    
END |    
    
DELIMITER ;  //写触发器的时候，我们会用；mysql执行分割符也是；所以我们在写触发器或者是存储过程的时候都会改变一下，例如：DELIMITER |    
    
//插入一些测试数据    
insert into urls (url) values ('http://google.com');    
insert into urls (url) values ('http://baidu.com/');    
insert into urls (url) values ('http://www.51yip.com/');    
insert into urls (url) values ('http://blog.51yip.com/');    
insert into urls (url) values ('http://51yip.com');    
insert into urls (url) values ('http://mysql.com');    
select \* from urls;    
    
//将插入的6条数据显示出来,下面的显示和删除也是一样的不多说了。    
select memc\_get('urls:1');    
select memc\_get('urls:2');    
select memc\_get('urls:3');    
select memc\_get('urls:4');    
select memc\_get('urls:5');    
select memc\_get('urls:6');    
    
update urls set url= 'http://mysql.com/sun' where url = 'http://51yip.com';    
select url from urls where url = 'http://51yip.com/manual';    
select memc\_get('urls:6');    
    
delete from urls where url = 'http://blog.51yip.com/';    
select \* from urls where url='http://blog.51yip.com/';    
select memc\_get('urls:4');    
  
  
转至：http://blog.51yip.com/mysql/999.html  
http://www.tuicool.com/articles/A7ZZVri