openesty嵌入lua脚本连接mysql

  • 下载地址

    nginx.conf文件

    worker_processes  1;
    error_log logs/error.log;
    events {
      worker_connections 1024;
    }
    http {
      # 设置纯 Lua 扩展库的搜寻路径(';;' 是默认路径)
      lua_package_path "/data/www/code/nginx+lua/config/lua_p/?.lua;;";
      # 设置 C 编写的 Lua 扩展模块的搜寻路径(也可以用 ';;')
      lua_package_cpath "/data/www/code/nginx+lua/config/lua_p_c/?.so;;";
    
      server {
         listen 8080;
         location /mysql {
               default_type 'text/html';
               lua_code_cache off;
               content_by_lua_file   ./config/lua/mysql.lua;
               charset utf-8;
         }
      }
    }

    mysql.lua文件

    local mysql = require "resty.mysql";
    local db, err = mysql:new();
    if not db then
      ngx.say("failed to instantiate mysql: ", err);
      return
    end
    
    db:set_timeout(1000); -- 1 sec
    
    -- or connect to a unix domain socket file listened
    -- by a mysql server:
    --     local ok, err, errcode, sqlstate =
    --           db:connect{
    --              path = "/path/to/mysql.sock",
    --              database = "ngx_test",
    --              user = "ngx_test",
    --              password = "ngx_test" }
    
    local ok, err, errcode, sqlstate = db:connect{
      host = "127.0.0.1",
      port = 3306,
      database = "test",
      user = "root",
      password = "123456",
      charset = "utf8",
      max_packet_size = 1024 * 1024,
    };
    
    if not ok then
      ngx.say("failed to connect: ", err, ": ", errcode, " ", sqlstate);
      return
    end
    
    ngx.say("connected to mysql.");
    
    local res, err, errcode, sqlstate =
    db:query("drop table if exists cats");
    if not res then
      ngx.say("bad result: ", err, ": ", errcode, ": ", sqlstate, ".");
      return
    end
    
    res, err, errcode, sqlstate =
    db:query("create table cats "
          .. "(id serial primary key, "
          .. "name varchar(5))");
    if not res then
      ngx.say("bad result: ", err, ": ", errcode, ": ", sqlstate, ".");
      return
    end
    
    ngx.say("table cats created.</br>");
    
    res, err, errcode, sqlstate =
    db:query("insert into cats (name) "
          .. "values (\'Bob\'),(\'老王\'),(\'老张\')");
    if not res then
      ngx.say("bad result: ", err, ": ", errcode, ": ", sqlstate, ".");
      return
    end
    
    ngx.say(res.affected_rows, " rows inserted into table cats </br>",
      "(last insert id: ", res.insert_id, ")</br>");
    
    -- run a select query, expected about 10 rows in
    -- the result set:
    res, err, errcode, sqlstate =
    db:query("select * from cats order by id asc", 10);
    if not res then
      ngx.say("bad result: ", err, ": ", errcode, ": ", sqlstate, ".");
      return
    end
    
    local cjson = require "cjson";
    ngx.say("result: ", cjson.encode(res));
    
    -- put it into the connection pool of size 100,
    -- with 10 seconds max idle timeout
    local ok, err = db:set_keepalive(10000, 100);
    if not ok then
      ngx.say("failed to set keepalive: ", err);
      return
    end
    
    -- or just close the connection right away:
    -- local ok, err = db:close()
    -- if not ok then
    --     ngx.say("failed to close: ", err)
    --     return
    -- end

    访问 http://192.168.56.2:8080/mysql

    连接和创建表以及插入后查询成功

拓展一些常用的封装好的工具包

Last modification:February 7, 2022
如果觉得我的文章对你有用,请随意赞赏