教你使用MySQL觸發器自動更新memcache

mysql 5.1支持觸發器以及自定義函數接口(UDF)的特性,如果配合libmemcache以及Memcached Functions for MySQL,就能夠實現memcache的自動更新。簡單記錄一下安裝測試步驟。



安裝步驟


安裝memcached,這個步驟很簡單,隨處可見


安裝mysql server 5.1RC,安裝辦法也很大眾,不廢話瞭


編譯libmemcached,解壓後安裝即可./configure; make; make install


編譯Memcached Functions for MySQL,在找一個最新的版本下載就是,./configure –with-mysql=/usr/local/mysql/bin/mysql_config –libdir=/usr/local/mysql/lib/mysql/


make


make install


接下來有兩個辦法讓Memcached Functions for MySQL在mysql中生效


在mysql的shell中執行memcached_functions_mysql源碼目錄下的sql/install_functions.sql,這會把memcache function作為UDF加入mysql


運行memcached_functions_mysql源碼目錄下的utils/install.pl,這是一個perl腳本,作用同上一條


測試memcache function


以下測試腳本摘自memcached_functions_mysql的源碼目錄,有興趣可以試試



PLAIN TEXTCODE: drop table if exists urls;


create table urls (


id int(3) not null,


url varchar(64) not null default ,


primary key (id)


);



select memc_servers_set(localhost:11211);


select memc_set(urls:sequence, 0);



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 |



DELIMITER ;



insert into urls (url) values (https://google.com);


insert into urls (url) values (https://www.ooso.net/index.php);


insert into urls (url) values (https://www.devdao.net/);


insert into urls (url) values (https://slashdot.org);


insert into urls (url) values (https://mysql.com);


select * from urls;



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);

You May Also Like