# 添加MySQL配置(drizzle)
upstream backend {
    drizzle_server 127.0.0.1:3306 dbname=test user=root password=123456 protocol=mysql;
    drizzle_keepalive max=200 overflow=ignore mode=single;
}

server {
    listen       80;
    server_name  localhost;

    #charset koi8-r;
    #access_log  logs/host.access.log  main;

    location / {
        root   html;
        index  index.html index.htm;
    }

    location /lua {
        default_type text/plain;
        content_by_lua ‘ngx.say("hello, lua")‘;
    }

    location /lua_redis {
        default_type text/plain;
        content_by_lua_file /usr/local/lua_test/redis_test.lua;
    } 

    location /lua_mysql {
            default_type text/plain;
            content_by_lua_file /usr/local/lua_test/mysql_test.lua;
    }

    location @cats-by-name {
        set_unescape_uri $name $arg_name;
        set_quote_sql_str $name;
        drizzle_query ‘select * from cats where name=$name‘;
        drizzle_pass backend;
        rds_json on;
    }

    location @cats-by-id {
        set_quote_sql_str $id $arg_id;
        drizzle_query ‘select * from cats where id=$id‘;
        drizzle_pass backend;
        rds_json on;
    }

    location = /cats {
        access_by_lua ‘
            if ngx.var.arg_name then
                return ngx.exec("@cats-by-name")
            end

            if ngx.var.arg_id then
                return ngx.exec("@cats-by-id")
            end
        ‘;

        rds_json_ret 400 "expecting \"name\" or \"id\" query arguments";
    }

    # 通过url匹配出name，并编码防止注入，最后以json格式输出结果
    location ~ ‘^/mysql/(.*)‘ {
        set $name $1;
        set_quote_sql_str $quote_name $name;
        set $sql "SELECT * FROM cats WHERE name=$quote_name";
        drizzle_query $sql;
        drizzle_pass backend;
        rds_json on;
    }

    # 查看MySQL服务状态
    location /mysql-status {
        drizzle_status;
    }
}

$ curl ‘http://127.0.0.1/lua_test‘
hello, lua

$ redis-cli set foo ‘hello,lua-redis‘
OK
$ curl ‘http://127.0.0.1/lua_redis‘
hello,lua-redis

$ curl ‘http://127.0.0.1/lua_mysql‘
connected to mysql.
table cats created.
3 rows inserted into table cats (last insert id: 1)
result: [{"name":"Bob","id":"1"},{"name":"","id":"2"},{"name":null,"id":"3"}]

$ curl ‘http://127.0.0.1/cats‘
{"errcode":400,"errstr":"expecting \"name\" or \"id\" query arguments"}

$ curl ‘http://127.0.0.1/cats?name=bob‘
[{"id":1,"name":"Bob"}]

$ curl ‘http://127.0.0.1/cats?id=2‘
[{"id":2,"name":""}]

$ curl ‘http://127.0.0.1/mysql/bob‘
[{"id":1,"name":"Bob"}]

$ curl ‘http://127.0.0.1/mysql-status‘
worker process: 32261

upstream backend
  active connections: 0
  connection pool capacity: 0
  servers: 1
  peers: 1