MySQL：数据库变动时使用mysql-http-udf实时更新前台

布满荆棘的人生 2022-04-23 04:02 70阅读 0赞

### 文章目录 ###

*  一、安装Mysql
 *  二、安装libcurl
 *  三、安装mysql-http-udf
 *  四、使用mysql-http-udf
 *   *  实验1:mysql-http-udf与web服务器
     *   *  准备服务器
         *   *  1、创建项目
             *  2、编写代码
             *  3、打开服务器
         *  Mysql测试准备
         *   *  1、登陆MySQL
             *  2、创建测试表
             *  3、创建 触发器
             *  4、测试
             *  5、备注
     *  实验2：验证“MySQL返回json”【不要看】
     *  实验3：触发器中使用count(1)【不要看】
     *   *  
     *  实验4：同时发送多个请求\[允许\]
     *  实验2：mysql结合mysql-http-udf，使用redis测试

> 背景：项目中遇到一个问题，如果数据库中的数据变动之后及时通知前台  
> 通过查找资料，决定采用websocket技术让服务器主动通过客户端。  
> 但是问题来了，服务器如何得知数据库中有数据变化呢？我想了以下几种方法
> 
> > (1)、服务器轮询。这个不好，只不过从客户端轮询–>服务器—>数据库 变成了服务器(轮询)—>数据库。有一点点改进  
> > (2)、服务器监听数据库。这个相当于长轮询，pass  
> > (3)、数据库中使用触发器和udf扩展服务，当数据库中有变化时，查询出结果通过http返回给服务器，服务器在通过websocket转交给客户端，即数据库–>服务器—>客户端。但是在做的过程中，发现MYSQL的触发器辣鸡的一笔，决定更改架构数据库–>服务器–>数据库—>服务器–>客户端。  
> > (4)、将来的优化方向：redis缓存，消息队列，消息中间件

### 文章目录 ###

# 一、安装Mysql #

> $ uname -a  
> Linux ubuntu 4.18.0-13-generic \#14-Ubuntu SMP Wed Dec 5 09:04:24 UTC 2018 x86\_64 x86\_64 x86\_64 GNU/Linux

1.查看是否安装

$ sudo netstat -tap | grep mysql

2.安装

$ sudo apt-get update
    $ sudo apt-get install mysql-server
    $ sudo apt-get install mysql-client
    $ sudo apt-get install libmysqlclient-dev

备注：安装了libmysqlclient-dev以后，在mysql安装目录下/usr/bin才会有mysql\_config配置文件。

3.测试是否安装成功

sudo netstat -tap | grep mysql

备注：  
此时mysql的安装目录为：/usr/bin  
mysql插件的目录（可以登陆mysql以后使用命令show variables like ‘%plugin%’;查看） 我的为:/usr/lib/mysql/plugin

# 二、安装libcurl #

$ sudo apt-get install libcurl4-openssl-dev

# 三、安装mysql-http-udf #

1、下载：mysql-udf-http-1.0.tar.gz  
网址：https://code.google.com/archive/p/mysql-udf-http/downloads  
![在这里插入图片描述][20190116111536451.png]  
或者：https://pan.baidu.com/s/1nuYZqR3  
2、解压：

$ tar zvxf mysql-udf-http-1.0.tar.gz
    $ cd mysql-udf-http-1.0/

3、配置

$ ./configure --prefix=/usr/local/mysql-udf-http --with-mysql=/usr/bin/mysql_config
    *******
    configure: creating ./config.status
    config.status: creating Makefile
    config.status: creating src/Makefile
    config.status: creating src/curl_config.h
    config.status: executing depfiles commands

备注：  
其中–prefix是你安装mysql-udf-http指定的安装目录，–with-mysql是你安装的mysql目录下的mysql\_config文件所在位置。  
遇到的错误：configure: error: no acceptable C compiler found in $PATH  
解决：sudo apt-get install gcc  
4、安装

$ sudo make 
    $ sudo make install

5.添加软链  
sudo ln -s /usr/local/mysql-udf-http/lib/mysql/plugin/mysql-udf-http.so.0.0.0 /usr/lib/mysql/plugin/mysql-udf-http.so

5.将安装好的插件拷贝到mysql的插件目录下

$ sudo cp /usr/local/mysql-udf-http/lib/mysql-udf-http.so.0.0.0 /usr/lib/mysql/plugin/mysql-udf-http.so

# 四、使用mysql-http-udf #

登陆Mysql  
1.创建自定义函数

create function http_get returns string soname 'mysql-udf-http.so'; 
    create function http_post returns string soname 'mysql-udf-http.so'; 
    create function http_put returns string soname 'mysql-udf-http.so'; 
    create function http_delete returns string soname 'mysql-udf-http.so';

2、删除

DROP FUNCTION IF EXISTS http_get;
    DROP FUNCTION IF EXISTS http_post;
    DROP FUNCTION IF EXISTS http_put;
    DROP FUNCTION IF EXISTS http_delete;

## 实验1:mysql-http-udf与web服务器 ##

框架  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg_size_16_color_FFFFFF_t_70]  
将后台数据实时更新到前台  
1，ajax短连接：客户端每隔一秒钟发一次请求，服务器收到请求后会立刻返回结果，不管有没有新数据。  
2，ajax长连接：客户端发送一次请求，服务器端收到请求后查询有没有新数据，如果没有新数据就阻塞这个请求，直到有新数据或者超时为止。客户端每次收到请求返回结果后立刻再发一次请求。comet貌似就是这个原理。  
3，WebSocket：这就不是一个HTTP协议了，而是一个tcp协议，而且Socket这个玩意顾名思义就是一个流了

下面我们只讲MYSQL数据库如果数据有变动就实时讲数据采用http协议实时发送到业务系统

### 准备服务器 ###

> 准备环境：go语言，beego

#### 1、创建项目 ####

> beego api http

#### 2、编写代码 ####

项目结构：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg_size_16_color_FFFFFF_t_70 1]  
main.go不变

package main
    
    import (
    	_ "http/routers"
    
    	"github.com/astaxie/beego"
    )
    
    func main() {
        
    	if beego.BConfig.RunMode == "dev" {
        
    		beego.BConfig.WebConfig.DirectoryIndex = true
    		beego.BConfig.WebConfig.StaticDir["/swagger"] = "swagger"
    	}
    	beego.Run()
    }

route.go

package routers
    
    import (
    	"http/controllers"
    
    	"github.com/astaxie/beego"
    )
    
    func init() {
        
    	ns := beego.NewNamespace("/v1",
    		beego.NSNamespace("/user",
    			beego.NSInclude(
    				&controllers.UserController{
        },
    			),
    		),
    	)
    	beego.AddNamespace(ns)
    }

user.go

package controllers
    
    import (
    	"github.com/astaxie/beego"
    )
    
    // Operations about Users
    type UserController struct {
        
    	beego.Controller
    }
    
    
    // @Title Login
    // @Description     测试mysql-udf-http
    // @Param	newip		query 	string	true		"The username for login"
    // @Param	newid		query 	string	true		"The username for login"
    // @Success 200 {string} login success
    // @Failure 403 user not exist
    // @router /GetHttp [get]
    func (u *UserController) GetHttp() {
        
    	newip := u.GetString("newip")
    	newid := u.GetString("newid")
    	beego.Info(newip, newid)
    	u.Data["json"] = newip
    
    	u.ServeJSON()
    }
    
    // @Title Login
    // @Description     测试mysql-udf-http
    // @Param	newip		query 	string	true		"The username for login"
    // @Success 200 {string} login success
    // @Failure 403 user not exist
    // @router /UpdateHttp [post]
    func (u *UserController) UpdateHttp() {
        
    	newip := u.GetString("newip")
    	beego.Info(newip)
    	u.Data["json"] = newip
    
    	u.ServeJSON()
    }
    
    // @Title Login
    // @Description     测试mysql-udf-http
    // @Param	oldip		query 	string	true		"The username for login"
    // @Param	oldid		query 	string	true		"The username for login"
    // @Success 200 {string} login success
    // @Failure 403 user not exist
    // @router /DeleteHttp [DELETE]
    func (u *UserController) DeleteHttp() {
        
    	oldip := u.GetString("oldip")
    	oldid := u.GetString("oldid")
    	beego.Info(oldip, oldid)
    	u.Data["json"] = oldid
    
    	u.ServeJSON()
    }

#### 3、打开服务器 ####

> bee run  
> ![在这里插入图片描述][20190116140312801.png]

### Mysql测试准备 ###

#### 1、登陆MySQL ####

#### 2、创建测试表 ####

SET NAMES UTF8;  
    USE test;  
    CREATE TABLE IF NOT EXISTS `udf_test` (  
      `id` int NOT NULL AUTO_INCREMENT,  
      `ip` varchar(255) CHARACTER SET utf8 NOT NULL,  
      PRIMARY KEY (`id`)  
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

#### 3、创建 触发器 ####

DELIMITER |  
    /* INSERT插入操作的触发器:当插入数据时，mysql会发送get请求至业务系统。 */  
    DROP TRIGGER IF EXISTS udftest_insert;  
    CREATE TRIGGER udftest_insert  
    	AFTER INSERT ON udf_test
    	FOR EACH ROW BEGIN  
       	 	SET @tt_resu = (SELECT http_get(CONCAT('http://127.0.0.1:8080/v1/user/GetHttp?newip=',  NEW.ip, '&newid=', NEW.id)));  
    END |      
    
    /* Update更新操作的触发器:当数据有更新时，mysql会发送post请求至业务系统。 */  
    DROP TRIGGER IF EXISTS udftest_update;  
    CREATE TRIGGER udftest_update
    	AFTER UPDATE ON udf_test
    	FOR EACH ROW BEGIN  
       	 	SET @tt_resu = (SELECT http_post('http://127.0.0.1:8080/v1/user/UpdateHttp',  CONCAT('oldip=', OLD.ip, '&newip=',NEW.ip)));  
    END |        
    
    /* Delete删除操作的触发器:当删除数据时，mysql会发送delete请求至业务系统。*/  
    DROP TRIGGER IF EXISTS udftest_delete;  
    CREATE TRIGGER udftest_delete
    	AFTER DELETE ON udf_test
    	FOR EACH ROW BEGIN  
       	 	SET @tt_resu = (SELECT http_delete(CONCAT('http://127.0.0.1:8080/v1/user/DeleteHttp?oldip=',  OLD.ip, '&oldid=', OLD.id)));   
    END |        
    DELIMITER ;

备注：  
CONCAT讲多个字符连接成一个字符串：concat(str1, str2,…)

#### 4、测试 ####

1、在udf\_test表中插入并更新数据：

mysql>  insert into udf_test (ip) VALUES ('192.168.1.1');
    Query OK, 1 row affected (0.01 sec)
    
    mysql> select * from udf_test;
    +----+-------------+
    | id | ip          |
    +----+-------------+
    |  1 | 192.168.1.1 |
    +----+-------------+
    1 row in set (0.00 sec)
    
    mysql> update udf_test set ip = '127.0.0.1' where id = 1;
    
    mysql> select * from udf_test;
    +----+-----------+
    | id | ip        |
    +----+-----------+
    |  1 | 127.0.0.1 |
    +----+-----------+
    1 row in set (0.00 sec)
    
    mysql> delete from udf_test where id = 1;

结果：  
![在这里插入图片描述][20190116160912864.png]

#### 5、备注 ####

[触发器的用法][Link 1]

## 实验2：验证“MySQL返回json”【不要看】 ##

1、修改user.go  
备注：【这个跟下面的没有什么关系，这个实验没有用到web服务端】

package controllers
    
    import (
    	"encoding/json"
    	"github.com/astaxie/beego"
    )
    
    // Operations about Users
    type UserController struct {
        
    	beego.Controller
    }
    
    // @Title Login
    // @Description     测试mysql-udf-http
    // @Param	body   body 	string	true		"body for put"
    // @Success 200
    // @Failure 403 user not exist
    // @router /puthttp [put]
    func (u *UserController) PutHttp() {
        
    	var user map[string]interface{
        }
    	data := u.Ctx.Input.RequestBody
    	err := json.Unmarshal(data, &user)
    	if err != nil{
        
    		beego.Info("body error",err)
    	}
    	beego.Info(user)
    
    	u.Data["json"] = data
    
    	u.ServeJSON()
    }

2、修改触发器

DELIMITER |  
    DROP TRIGGER IF EXISTS mytable_insert;  
    CREATE TRIGGER mytable_insert  
    AFTER INSERT ON udf_test  
    FOR EACH ROW BEGIN  
        SET @tt_json = (select json_object(id, ip) from udf_test order by id limit 0,3);  
        SET @tt_resu = (SELECT http_put('http://127.0.0.1:8080/v1/user/puthttp', @tt_json));  
    END |  
    show warnings |
    DELIMITER ;

3、测试

mysql>  insert into udf_test (ip) VALUES ('192.168.1.1');
    mysql>  insert into udf_test (ip) VALUES ('192.168.1.1');
    ERROR 1242 (21000): Subquery returns more than 1 row
    mysql>   select * from udf_test;

触发器只能返回1行吗？？？？？？

其余同实验1

## 实验3：触发器中使用count(1)【不要看】 ##

1、修改user.go

package controllers
    
    import (
    	"github.com/astaxie/beego"
    )
    
    // Operations about Users
    type UserController struct {
        
    	beego.Controller
    }
    
    // @Title Login
    // @Description     测试mysql-udf-http
    // @Param	count		query 	string		"The username for login"
    // @Success 200 {string} login success
    // @Failure 403 user not exist
    // @router /PostHttp [post]
    func (u *UserController) PostHttp() {
        
    	newip := u.GetString("count")
    	beego.Info(newip)
    	u.Data["json"] = newip
    
    	u.ServeJSON()
    }

2、修改触发器

DELIMITER |  
     DROP TRIGGER IF EXISTS udftest_insert; 
    CREATE TRIGGER udftest_insert 
    AFTER INSERT ON udf_test 
    FOR EACH ROW BEGIN 
    SET @tt_count = (select count(1) from udf_test);  
    SET @tt_resu = (SELECT http_post('http://127.0.0.1:8080/v1/user/PostHttp?count=',  @tt_count));
    END |
    DELIMITER ;

2、测试

mysql>  insert into udf_test (ip) VALUES ('192.168.1.1');

实验结果：没有获取到post参数  
![在这里插入图片描述][20190117114838742.png]  
因此，post方法不能写成\{SET @tt\_resu = (SELECT http\_post(‘http://127.0.0.1:8080/v1/user/PostHttp?count=’, @tt\_count));\}

###  ###

DELIMITER |  
     DROP TRIGGER IF EXISTS udftest_insert; 
    CREATE TRIGGER udftest_insert 
    AFTER INSERT ON udf_test 
    FOR EACH ROW BEGIN 
    SET @tt_count = (select ip from udf_test where id = NEW.id);  
    SET @tt_resu = (SELECT http_post('http://127.0.0.1:8080/v1/user/PostHttp', CONCAT('count=', @tt_count)));
    END |
    DELIMITER ;

## 实验4：同时发送多个请求\[允许\] ##

修改user.go

package controllers
    
    import (
    	"github.com/astaxie/beego"
    )
    
    // Operations about Users
    type UserController struct {
        
    	beego.Controller
    }
    
    
    // @Title Login
    // @Description     测试mysql-udf-http
    // @Success 200 {string} login success
    // @Failure 403 user not exist
    // @router /GetHttp [get]
    func (u *UserController) GetHttp() {
        
    	beego.Info("GetHttp----------------------------")
    	u.Data["json"] = "nnnn"
    
    	u.ServeJSON()
    }
    
    // @Title Login
    // @Description     测试mysql-udf-http
    // @Success 200 {string} login success
    // @Failure 403 user not exist
    // @router /UpdateHttp [post]
    func (u *UserController) UpdateHttp() {
        
    
    	beego.Info("UpdateHttp--------------------")
    	u.Data["json"] = "nnnn"
    
    	u.ServeJSON()
    }
    
    // @Title Login
    // @Description     测试mysql-udf-http
    // @Success 200 {string} login success
    // @Failure 403 user not exist
    // @router /DeleteHttp [DELETE]
    func (u *UserController) DeleteHttp() {
        
    
    	beego.Info("DeleteHttp----------------------")
    
    	u.ServeJSON()
    }

修改触发器

DELIMITER |  
    /* INSERT插入操作的触发器:当插入数据时，mysql会发送get请求至业务系统。 */  
    DROP TRIGGER IF EXISTS udftest_insert;  
    CREATE TRIGGER udftest_insert  
    	AFTER INSERT ON udf_test
    	FOR EACH ROW BEGIN  
       	 	SET @tt_resu = (SELECT http_get('http://127.0.0.1:8080/v1/user/GetHttp'));  
       	 	SET @tt_resu = (SELECT http_get('http://127.0.0.1:8080/v1/user/UpdateHttp'));  
       	 	  SET @tt_resu = (SELECT http_get('http://127.0.0.1:8080/v1/user/DeleteHttp'));  
    END |      
           
    DELIMITER ;

测试

insert into udf_test (ip) VALUES ('192.168.1.1');

实验结果：  
![在这里插入图片描述][20190117162128954.png]  
重新设计框架：当数据更新时websocket后台刷新前台

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg_size_16_color_FFFFFF_t_70 2]

备注：此框架有一个十分致命的缺点：如果相应路由树返回结果太慢的话，会导致操作十分慢，如果时插入触发器，那么插入一条数据可能就需要几十秒  
解决方法：并发。数据库通知web服务器之后，开一个线程来执行相应的SQL语句

## 实验2：mysql结合mysql-http-udf，使用redis测试 ##

1、下载启动redis  
https://blog.csdn.net/zhizhengguan/article/details/85047649  
![在这里插入图片描述][20190116124347781.png]  
2、登陆Mysql，并创建测试表

SET NAMES UTF8;  
    USE test;  
    CREATE TABLE IF NOT EXISTS `mytable` (  
      `id` int(10) NOT NULL AUTO_INCREMENT,  
      `addtime` int(10) NOT NULL,  
      `title` varchar(255) CHARACTER SET utf8 NOT NULL,  
      PRIMARY KEY (`id`)  
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

3、为测试表创建触发器

/* INSERT插入操作的触发器 */  
    DELIMITER |  
    DROP TRIGGER IF EXISTS mytable_insert;  
    CREATE TRIGGER mytable_insert  
    AFTER INSERT ON mytable  
    FOR EACH ROW BEGIN  
        SET @tt_json = (SELECT json_object(id,addtime,title) FROM mytable WHERE id = NEW.id LIMIT 1);  
        SET @tt_resu = (SELECT http_put(CONCAT('http://127.0.0.1:6347/', NEW.id), @tt_json));  
    END |  
    DELIMITER ;  
      
    /* UPDATE更新操作的触发器 */  
    DELIMITER |  
    DROP TRIGGER IF EXISTS mytable_update;  
    CREATE TRIGGER mytable_update  
    AFTER UPDATE ON mytable  
    FOR EACH ROW BEGIN  
        SET @tt_json = (SELECT json_object(id,addtime,title) FROM mytable WHERE id = OLD.id LIMIT 1);  
        SET @tt_resu = (SELECT http_put(CONCAT('http://127.0.0.1:6347/', OLD.id), @tt_json));  
    END |  
    DELIMITER ;  
      
    /* DELETE删除操作的触发器 */  
    DELIMITER |  
    DROP TRIGGER IF EXISTS mytable_delete;  
    CREATE TRIGGER mytable_delete  
    AFTER DELETE ON mytable  
    FOR EACH ROW BEGIN  
        SET @tt_resu = (SELECT http_delete(CONCAT('http://127.0.0.1:6347/', OLD.id)));  
    END |  
    DELIMITER ;

参考：  
[ubuntu下mysql-http-udf的安装和配置][ubuntu_mysql-http-udf]  
[给MySQL增加mysql-udf-http和mysql-udf-json自定义函数，让MySQL有调用http接口和查询直接回JSON的能力][MySQL_mysql-udf-http_mysql-udf-json_MySQL_http_JSON]  
[MySQL中触发器的基础学习教程][MySQL]  
[为 MySQL 增加 HTTP/REST 客户端：MySQL UDF 函数 mysql-udf-http 1.0 发布][MySQL _ HTTP_REST _MySQL UDF _ mysql-udf-http 1.0]  
[com/database/201801/713571.html][com_database_201801_713571.html]

[20190116111536451.png]: /images/20220323/60c2fdd72af540e3b391533ac1fb3606.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg_size_16_color_FFFFFF_t_70]: /images/20220323/86b2e1863a6e4e75bdddb3bfbc6cebef.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg_size_16_color_FFFFFF_t_70 1]: /images/20220323/7a94ad5d7753415ab9dd8ae2fa933166.png
[20190116140312801.png]: /images/20220323/6cb04fa113694ce58f19334220cd79c7.png
[20190116160912864.png]: /images/20220323/9ed0e0011c3f46b29bc09beb0ad29782.png
[Link 1]: https://mp.csdn.net/mdeditor/86512124#1SHOW_TRIGGERS_160
[20190117114838742.png]: /images/20220323/140d4f4b863141b2891c2317a3de9a6f.png
[20190117162128954.png]: /images/20220323/43bde7c2551a472cb83a2e2bd7356acd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg_size_16_color_FFFFFF_t_70 2]: /images/20220323/95b76b76e5a44b4b90b7738e7d4254fa.png
[20190116124347781.png]: /images/20220323/03b03a0df65a413cb006ccd1fd556937.png
[ubuntu_mysql-http-udf]: https://blog.csdn.net/bcfdsagbfcisbg/article/details/78017223
[MySQL_mysql-udf-http_mysql-udf-json_MySQL_http_JSON]: https://www.cnblogs.com/kgdxpr/p/5961310.html
[MySQL]: https://www.jb51.net/article/76106.htm
[MySQL _ HTTP_REST _MySQL UDF _ mysql-udf-http 1.0]: http://zyan.cc/mysql-udf-http/2/1/
[com_database_201801_713571.html]: https://www.2cto.com/database/201801/713571.html