数据库的测试表结构如下:
DROP TABLE IF EXISTS `documents`;
CREATE TABLE `documents` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date_added` datetime NOT NULL,
`title` varchar(255) NOT NULL,
`content` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
二、mysql-udf-http 安装配置
wget http://mysql-udf-http.googlecode.com/files/mysql-udf-http-1.0.tar.gz
tar zxvf mysql-udf-http-1.0.tar.gz
cd mysql-udf-http-1.0/
./configure –prefix=/usr –with-mysql=/usr/bin/mysql_config
make && make install
echo "/usr/lib/mysql/plugin" >> /etc/ld.so.conf.d/mysql-i386.conf
/sbin/ldconfig
/sbin/ldconfig -v|grep mysql
如果没有类似如下的输出 /usr/lib/mysql/plugin:
mysql-udf-http.so.0 -> mysql-udf-http.so 则建立mysql-udf-http.so 的软连接(当然也可以通过修改编译Makefile脚本)
ln -s /usr/lib/mysql/plugin/mysql-udf-http.so /usr/lib/mysql/plugin/libmysql-udf-http.so
建立软连接后再执ldconfig 命令,使用/sbin/ldconfig -v|grep mysql是否有如下内容: /usr/lib/mysql/plugin:
mysql-udf-http.so.0 -> libmysql-udf-http.so 原因是mysql-udf-http的Makefile中指定了动态链接库的SONAME为mysql-udf-http.so.0 ,而ldconfig缺省只处理以lib开头的动态链接库(例如libmysqlclient.so.15),因此执行ldconfig命令时候忽略了mysql-udf-http.so 。
创建mysql的用户自定义函数:
mysql >
create function http_get returns string soname ‘libmysql-udf-http.so’;
create function http_post returns string soname ‘libmysql-udf-http.so’;
create function http_put returns string soname ‘libmysql-udf-http.so’;
create function http_delete returns string soname ‘libmysql-udf-http.so’; 三、创建调用mysql-udf-http的触发器
/* INSERT插入操作的触发器 */
DELIMITER |
DROP TRIGGER IF EXISTS documents_insert;
CREATE TRIGGER documents_insert
AFTER INSERT ON documents
FOR EACH ROW BEGIN
SET @result = (SELECT http_get(‘http://www.yeeach.com/solr/dataimport?command=delta-import&commit=yes’));
END |
DELIMITER ;
/* UPDATE更新操作的触发器 */
DELIMITER |
DROP TRIGGER IF EXISTS documents_update;
CREATE TRIGGER documents_update
AFTER UPDATE ON documents
FOR EACH ROW BEGIN
60; SET @result = (SELECT http_get("http://www.yeeach.com/solr/dataimport?command=delta-import&commit=yes"));
END |
DELIMITER ;
/* DELETE删除操作的触发器 */
DELIMITER |
DROP TRIGGER IF EXISTS documents_delete;
CREATE TRIGGER documents_delete
AFTER DELETE ON documents
FOR EACH ROW BEGIN
SET @result = (SELECT http_get(CONCAT(‘http://www.yeeach.com/solr/update/?stream.body=’,OLD.id,’&stream.contentType=text/xml;charset=utf-8&commit=true’)));
END |
DELIMITER ; 四、测试
在数据库执行增删改操作,然后在Solr的管理界面执行查询操作,测试触发器调用mysql-udf-http是否正常。 五、参考资料
DataImportHandler文档:http://wiki.apache.org/solr/DataImportHandler
Mysql UDF的大本营:http://www.mysqludf.org/
mysql-udf-http:http://code.google.com/p/mysql-udf-http/