[文章作者:张宴 本文版本:v1.0 最后修改:2010.09.08 转载请注明原文链接:http://blog.zyan.cc/mysql-udf-http/]

  Mysql-udf-http 是一款简单的MySQL用户自定义函数(UDF, User-Defined Functions),具有http_get()、http_post()、http_put()、http_delete()四个函数,可以在MySQL数据库中利用HTTP协议进行REST相关操作。

  项目网址http://code.google.com/p/mysql-udf-http/
  中文说明http://blog.zyan.cc/mysql-udf-http/
  使用环境:Linux操作系统,支持的MySQL版本:5.1.x 和 5.5.x。5.0.x未经测试。
  软件作者:张宴



  一、REST架构风格:

  REST(Representational State Transfer)是一种轻量级的Web Service架构风格,其实现和操作明显比SOAP和XML-RPC更为简洁,可以完全通过HTTP协议实现,还可以利用缓存Cache来提高响应速度,性能、效率和易用性上都优于SOAP协议。REST最早是由 Roy Thomas Fielding 博士2000年在论文《Architectural Styles and the Design of Network-based Software Architectures》中提出的,中文译文全文PDF点此下载。另外,有篇译文对REST做了一个简化说明。

  目前,REST架构风格的常见实现是基于HTTP协议及其四种基本方法(如POST、GET、PUT和DELETE)的。有人将HTTP协议的四种方法与CRUD原则相对应,CRUD原则对于资源只需要四种行为:Create(创建)、Read(读取)、Update(更新)和Delete(删除)就可以完成对其操作和处理。

  点击在新窗口中浏览此图片

  点击在新窗口中浏览此图片

  在Mysql-udf-http中,四个函数http_post()、http_get()、http_put()、http_delete()分别对应HTTP协议的POST、GET、PUT、DELETE四种方法。

  REST是一种架构风格,而不是协议或标准。HTTP协议“POST、GET、PUT、DELET”四种方法与CRUD原则“Create、Read、Update、Delete”四种行为的一一对应关系只是一种架构设计习惯,而不是规范。因此,POST方法也可以用来更新资源,PUT方法也可以用来创建资源,这就要看具体应用程序作者的定义了。例如Tokyo Tyrant除了支持Memcached协议外,还支持REST方式存取,PUT代表创建和更新,GET代表读取,DELETE代表删除(关于Tokyo Tyrant的安装使用请点击这儿)。

  目前国内外流行的Web 2.0应用API接口中,很多都支持REST架构风格。例如:新浪微博开放平台人人网API、Google OpenID、Flickr、Twitter、eBay、Facebook、Last.fm、del.icio.us、Yahoo Search、Amazon S3、Amazon EC2、Digg、Microsoft Bing、FriendFeed、PayPal、Foursquare,更多...

  当记录数成百上千万条时,通常采用 MySQL 分表减低数据库压力。但是,全部数据按点击数、精华、积分排序显示等功能,在MySQL 分表中则无法实现。编写 Mysql-udf-http 的最初目的,是为了在项目开发中,将 MySQL 各分表的数据自动同步到我们的 TCSQL 高速列表数据库,用来做列表查询、显示,内容页则根据ID直接查询各 MySQL 分表的内容。由于HTTP协议的通用性,通过 Mysql-udf-http 可以做更多的事情。

  通过Mysql-udf-http,你可以在MySQL中利用触发器,将MySQL的数据同步到支持REST的应用上。例如你有一个独立博客,你可以在文章表创建MySQL触发器,这样,在发表文章时,就可以将文章标题、URL自动同步到新浪微博、Twitter。你想用 Tokyo Tyrant 做缓存,也可以利用MySQL触发器在发生增、删、改时,将数据自动同步到 Tokyo Tyrant。详细配置方法本文第4节中会有介绍。



  二、Mysql-udf-http的安装与使用:

  1. 在Linux系统上安装Mysql-udf-http

  注意:“/usr/local/webserver/mysql/”是你的MySQL安装路径,如果你的MySQL安装路径不同,请自行修改。

ulimit -SHn 65535
wget http://curl.haxx.se/download/curl-7.21.1.tar.gz
tar zxvf curl-7.21.1.tar.gz
cd curl-7.21.1/
./configure --prefix=/usr
make && make install
cd ../

echo "/usr/local/webserver/mysql/lib/mysql/" > /etc/ld.so.conf.d/mysql.conf
/sbin/ldconfig
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/local/webserver/mysql --with-mysql=/usr/local/webserver/mysql/bin/mysql_config
make && make install
cd ../




  2. 通过命令行登陆进入MySQL

/usr/local/webserver/mysql/bin/mysql -S /tmp/mysql.sock




  3. 创建MySQL自定义函数

  mysql>


  4. 使用方法

  I. 函数描述:

  mysql>
  II. 示例 A:

  mysql>
  III. 示例

  通过MySQL触发器,利用mysql-udf-http和第三方UDF函数lib_mysqludf_json,自动同步数据到 Tokyo Tyrant。

  (1). 下载安装 lib_mysqludf_json 修改版:

  以下安装包适合32位Linux操作系统:
wget http://mysql-udf-http.googlecode.com/files/lib_mysqludf_json-i386.tar.gz
tar zxvf lib_mysqludf_json-i386.tar.gz
cd lib_mysqludf_json-i386/
# 如果你的MySQL安装路径不是/usr/local/webserver/mysql/,请修改以下路径。
cp -f lib_mysqludf_json.so /usr/local/webserver/mysql/lib/mysql/plugin/lib_mysqludf_json.so
cd ../


  以下安装包适合64位Linux操作系统:
wget http://mysql-udf-http.googlecode.com/files/lib_mysqludf_json-x86_64.tar.gz
tar zxvf lib_mysqludf_json-x86_64.tar.gz
cd lib_mysqludf_json-x86_64/
# 如果你的MySQL安装路径不是/usr/local/webserver/mysql/,请修改以下路径。
cp -f lib_mysqludf_json.so /usr/local/webserver/mysql/lib/mysql/plugin/lib_mysqludf_json.so
cd ../


  通过命令行登陆进入MySQL:
/usr/local/webserver/mysql/bin/mysql -S /tmp/mysql.sock


  mysql>  lib_mysqludf_json的详细用法请访问:http://www.mysqludf.org/lib_mysqludf_json/

  (2). 创建测试表

  mysql>
  (3). 为测试表创建触发器:

  mysql>
  (4). 将 MySQL 表和 Tokyo Tyrant 关联进行查询:

  mysql>


  5. 如何删除mysql-udf-http UDF函数:

  mysql>





技术大类 » 数据库技术 | 评论(107) | 引用(0) | 阅读(165508)
细碎机 Email Homepage
2011-1-12 11:17
路过 ,学习了!!
最好的巧克力品牌 Homepage
2011-2-25 17:20
最好的巧克力品牌
<a href=http://www.seo5200.com>最好的巧克力品牌</a>
seo52002011353761593-2011.2.25
carlos
2011-3-2 19:06
ERROR 2013 (HY000): Lost connection to MySQL server during query
太阳里的雪 Email Homepage
2011-3-11 18:23
mysql> select json_object(json_members(password,mobile)) from UserAdminInfo where id=100 limit 1;+--------------------------------------------+| json_object(json_members(password,mobile)) |+--------------------------------------------+| {"555555555":"ttttt"}                      |+--------------------------------------------+1 row in set (0.00 sec)怎么用lib_mysqludf_json查询出来的json数据不包括字段名~
太阳里的雪 Homepage
2011-3-21 13:43
很担心的一个问题就是这种通过mysql触发器来处理http请求,如果当http服务出现问题会不会造成mysql的调用端出问题~
小覃 Homepage
2011-4-5 12:51
envyenvy这个挺好玩的!
小覃 Homepage
2011-4-5 13:03
我认为这样可以缓解数据库服务器的高并发数问题,但是稳定不确定,如果几个用户同时post请求的话或者post间隔时间过短是否会失败呢?我最近做一个程序客户端是一队列的形式post到服务器,下面的速度稍微快一些服务端就瓶颈了 - -!!
Gwen Petitte
2011-6-15 17:04
dresses Email Homepage
2011-7-26 10:06
Here are a variety of the empire <a href="http://www.romancearound.com/">dresses</a>, I want to introduce yourself. The first shoulder chiffon <a href="http://www.romancearound.com/bridesmaid-dresses-c-197.html">bridesmaid dresses</a> empire style wedding will <a href="http://www.romancearound.com/wedding-dresses-trumpet-wedding-dresses-c-180_253.html">trumpet wedding dresses</a>.This Tia cowries, dressed in her wedding day.Chiffon of the empire the best clothes, because they are the size of the drum as complete motion from the wind the site makes it easy to create a plan impression.One romantic flowers floating above shoulder function and help catch the eyes of the cut of the chest and neck problems, so that more comprehensive coverage of physical illusion bust.Clothing do not like big long torso, short legs, or pear-shaped<a href="http://www.romancearound.com/bridesmaid-dresses-ankle-length-bridesmaid-dresses-c-197_198.html">ankle length bridesmaid dresses</a> figure.special is also suitable for pregnant brides since the mobility as a dress can accommodate more Many of the abdomen.
luobo Email Homepage
2011-7-27 15:03
It is my great honor to make friends with you and I’d like to introduce my family to you. There are six people in my family, including my kind father, strong eldest brother, fashionable mother, two lovely eldest sisters and me. Because of such a cozy and harmony home, we share our hobbies with each other and wide our eyes and enrich our mind. My father is interested in watching sports games, especially games played by<a href="http://www.jerseystock.com">NBA</a>, <a href="http://www.jerseystock.com">NFL</a>, <a href="http://www.jerseystock.com">NHL</a>and <a href="http://www.jerseystock.com">MLB</a>. Meanwhile, my eldest brother is especially fond of playing football and basketball, and he likes<a href="http://www.jerseystock.com">cheap MLB Jerseys</a>, <a href="http://www.shopnbajerseys.com">cheap NBA Jerseys</a>, <a href="http://www.shopnfljerseysonline.com">cheap NFL Jerseys</a>and <a href="http://www.jerseystock.com">cheap NHL Jerseys</a> very much. While my lovely two eldest sisters are always buying <a href="http://www.mypursehandbag.com">cheap purses and handbags</a>when they go shopping. Recently, there are many<a href="http://www.purse-cheap.com">cheap purses</a> and <a href="http://www.wholesale-wallets.com">wholesale wallets</a> best selling in the reliable outlet store or on the website. Therefore, both of my sisters run out of their money to buy <a href="http://www.mypursehandbag.com">cheap purses and handbags</a>. They are satisfied with all the<a href="http://www.purse-cheap.com">cheap purses</a>, for they are featured with fresh style, bright colors and excellent quality. Then, what about my mother and me? To be honest, I enjoy going shopping with my mother and buying <a href="http://www.shopnbajerseys.com">cheap NBA Jerseys</a>,<a href="http://www.jerseystock.com">cheap MLB Jerseys</a>, <a href="http://www.jerseystock.com">cheap NHL Jerseys</a> or<a href="http://www.shopnfljerseysonline.com">cheap NFL Jerseys</a> for my brother or getting <a href="http://www.wholesale-wallets.com">wholesale wallets</a>for my sisters. In my heart, my mother is so trendy, elegant and classic that she has a sharp idea towards beauty and fashion. Therefore, I admire her and wish to become a perfect lady like her in the future.
patch quilts Email Homepage
2011-9-20 12:57
这个东西我目前正需要哈
cosplay wig Email Homepage
2011-9-21 13:22
来学习一下
clip in extensions Email Homepage
2011-9-22 13:12
好好学习一下
Leonardo Eisenberg
2011-9-28 16:32
train quilt Email Homepage
2011-10-6 18:54
很好,分享了!
淡定
2011-10-11 17:28
我在写直接操作远程数据库的东东,刚好可以参考.
ghostwwl
2011-11-14 19:42
显示以下错误 centos 64  版本 5.5.2-m2
1126 - Can't open shared library 'mysql-udf-http.so' (errno: 0 feature disabled)

+---------------+-----------------------------+
| Variable_name | Value                       |
+---------------+-----------------------------+
| plugin_dir    | /opt/mysql/lib/mysql/plugin |
+---------------+-----------------------------+
1 row in set

安装也确实到此目录
louis vuitton uk Email Homepage
2011-11-22 17:10
This louis vuitton uk for sale belongs to the sounding just what are termed as Louis Vuitton vintage best sellers, many other products and services for the reason that range appearing companies.You will easily notice the unfold zippers of this coach outlet store online. That is the decoration. There are some inside pockets for you as well. They are easy to match your clothes and to carry.Let us inspire your inner beauty with fine christian louboutin sale. Purse the elegance in bridal wedding. Enjoy the fashion.
armani watches Email Homepage
2011-11-23 10:12
If you are looking for armani Bags, our armani watches Handbags Canada outlet store is your first choice. We promise Original Packing and Best Discount,3-5 Workdays To Your Door!If you buy bags and purses of the latest new designs from the louis vuitton outlet now, you can enjoy special discounts. What else are you waiting for?After you choose the right kind of plants and the size pot for the plant.
guoxliang Email
2011-11-28 12:14
碰到ass和alpha大侠同样的问题,大侠们当时是怎么解决的呀?是mysql设置什么地方不对吗?
分页: 3/6 第一页 上页 1 2 3 4 5 6 下页 最后页
发表评论
表情
emotemotemotemotemot
emotemotemotemotemot
emotemotemotemotemot
emotemotemotemotemot
emotemotemotemotemot
打开HTML
打开UBB
打开表情
隐藏
记住我
昵称   密码   游客无需密码
网址   电邮   [注册]