mysql.lua 读改增删封装类，符合习惯使用

为什么80%的码农都做不了架构师？>>>   

local mysql = require "resty.mysql"

local _M = {
    host = "127.0.0.1",
    port = 3306,
    database = "",
    user = "root",
    password = "",
    charset = "utf8",
    tablePrefix = '',
    timeout = 1000,
    max_packet_size = 1024 * 1024,
}

function _M:new(config)
    config = config or {}
    setmetatable(config, self)
    self.__index = self
    return config
end

function _M:connect()
    local db, err = mysql:new()
    if not db then
        ngx.log(ngx.ERR, "failed to instantiate mysql: ", err)
        return nil
    end
    db:set_timeout(self.timeout)

    local ok, err, errcode, sqlstate = db:connect {
        host = self.host,
        port = self.port,
        database = self.database,
        user = self.user,
        password = self.password,
        max_packet_size = self.max_packet_size,
    }
    if not ok then
        ngx.log(ngx.ERR, "failed to connect: ", err, ": ", errcode, " ", sqlstate)
        return nil
    end

    local ok, err = db:get_reused_times()
    if (not ok or ok == 0) and self.charset then
        db:query('SET NAMES ' .. self.charset)
    end

    self.db = db
    return true
end

function _M:set_keepalive(max_idle_timeout, pool_size)
    max_idle_timeout = max_idle_timeout or 10000
    pool_size = pool_size or 10
    local ok, err = self.db:set_keepalive(max_idle_timeout, pool_size)
    if not ok then
        ngx.log(ngx.ERR, "failed to set_keepalive: ", err)
        return nil
    end
    return true
end

function _M:tbname(tableName)
    return self.tablePrefix .. tableName
end

function _M:query(sql, nrows)
    --ngx.log(ngx.ERR, "query sql: ", sql)
    local res, err, errno, sqlstate = self.db:query(sql, nrows)
    if not res then
        ngx.log(ngx.ERR, "bad count result: ", err, ": ", errno, ": ", sqlstate, " sql:", sql)
        return nil
    end
    return res
end

function _M:count(tbname, where, sql)
    local count = 0
    if not sql then
        where = where or '1=1'
    end
    local sql = string.format("SELECT COUNT(1) AS NUM FROM %s WHERE %s", self:tbname(tbname), where)
    local res = self:query(sql, 1)
    if res then
        count = tonumber(res[1]['NUM']) or 0
    end
    return count
end

function _M:find(tbname, where, field, order, sql)
    if not sql then
        where = where or '1=1'
        field = field or '*'
        order = order and string.format('order by %s', order) or ''
        if type(field) == 'table' then
            field = string.format('`%s`', table.concat(field, '`,`'))
        end
        sql = string.format("SELECT %s FROM %s WHERE %s %s limit 1",
            field, self:tbname(tbname), where, order)
    end

    local res = self:query(sql, 1)
    if not res then
        return false
    end
    return res[1]
end

function _M:findOne(tbname, where, field, order, sql)
    if not sql then
        where = where or '1=1'
        field = field or 'id'
        order = order and string.format('order by %s', order) or ''
        sql = string.format("SELECT `%s` FROM %s WHERE %s %s limit 1",
            field, self:tbname(tbname), where, order)
    end
    local res = self:query(sql, 1)
    if not res then
        return false
    end
    return res[1][field]
end

function _M:findAll(tbname, where, field, order, limit, sql)
    if not sql then
        where = where or '1=1'
        field = field or '*'
        order = order and string.format('order by %s', order) or ''
        limit = limit and string.format('limit %s', limit) or ''
        if type(field) == 'table' then
            field = string.format('`%s`', table.concat(field, '`,`'))
        end
        sql = string.format("SELECT %s FROM %s WHERE %s %s %s",
            field, self:tbname(tbname), where, order, limit)
    end

    local res = self:query(sql, 1)
    return res
end

function _M:findCol(tbname, where, field, order, limit, sql)
    if not sql then
        where = where or '1=1'
        field = field or 'id'
        order = order and string.format('order by %s', order) or ''
        limit = limit and string.format('limit %s', limit) or ''
        sql = string.format("SELECT %s FROM %s WHERE %s %s %s",
            field, self:tbname(tbname), where, order, limit)
    end

    local res = self:query(sql)
    if not res then
        return false
    end
    local t = {}
    for k, r in pairs(res) do
        t[k] = r[field]
    end
    return t
end

function _M:insert(tbname, params)
    if type(params) ~= 'table' then
        ngx.log(ngx.ERR, 'mysql insert params required table', type(params))
        return false
    end
    local field, valus = {}, {}
    local index = 1;
    for k, v in pairs(params) do
        field[index] = k
        valus[index] = ngx.quote_sql_str(v)
        index = index + 1
    end
    field = table.concat(field, '`,`')
    valus = table.concat(valus, ",")
    local sql = string.format("INSERT INTO %s (`%s`) VALUES (%s)", self:tbname(tbname), field, valus)
    local res = self:query(sql)
    if not res then
        return false
    end
    return res.insert_id
end

function _M:update(tbname, where, params)
    if type(params) ~= 'table' then
        ngx.log(ngx.ERR, 'mysql update params required table')
        return false
    end
    local field = {}
    local index = 1;
    for k, v in pairs(params) do
        field[index] = string.format("`%s`=%s", k, ngx.quote_sql_str(v))
        index = index + 1
    end
    field = table.concat(field, ',')
    local sql = string.format("UPDATE %s SET %s WHERE %s", self:tbname(tbname), field, where)
    local res = self:query(sql)
    if not res then
        return false
    end
    return res.affected_rows
end

function _M:delete(tbname, where)
    local sql = string.format("DELETE FROM %s WHERE %s", self:tbname(tbname), where)
    local res = self:query(sql)
    if not res then
        return false
    end
    return res.affected_rows
end

function _M:close()
    local ok, err = self.db:close()
    if not ok then
        ngx.say("failed to close: ", err)
        return false
    end
    return true
end

return _M

 

转载于:https://my.oschina.net/itlong/blog/1036610