发一个自己写的MySQL操作包装

walker.ma · 2014-11-13T12:59:11+00:00

多谢，是mysql 5.1的BUG，在5.5中修正了， quote: https:...

发一个自己写的MySQL操作包装

walker.ma

在项目中用的，举个简单使用的例子：

local dt = require('data')

...这里是一堆初始化mysql连接的代码...
--local db = ...

local ret, err, errno, sqlstate = db:query('START TRANSACTION')
local data = dt(db)
local ok, err = pcall(
  function()
    local sql = 'INSERT INTO foo(name, age) VALUES(%s, %d)'
    local id10 = data.insert(sql, ngx.quote_sql_str('bar10'), 10)
    local id20 = data.insert(sql, ngx.quote_sql_str('bar20'), 20)

    local newage = 11

    sql = 'UPDATE foo SET age = %d WHERE id = %d AND age = %d'
    local rown = data.update(sql, newage, id10, 10)
    if rown ~= 1 then
       error('数据并发', 2)
    end

    local ps = {{12, id10, 11}, {21, id20, 20}}
    sql = 'UPDATE foo SET age = ? WHERE id = ? AND age = ?'
    data.updates(sql, ps, true)

  end
)

if ok then
  db:query('COMMIT')
else
  db:query('ROLLBACK')
end
db:set_keepalive(XXXXXXXXXXXXXXXXXX)





-------------------------data.lua---------------------------------------
local _F = function(db)

local _M = {db = db}

local debug = true

local function throw(code)
  error('{code=' .. code .. '}', 2)
end

--查询一条数据
function _M.queryone(sql, ...)
  local s = string.format(sql, ...)
  if debug then
    ngx.log(ngx.DEBUG, s)
  end

  local ret, err, errno, sqlstate = _M.db:query(s, 1)
  if not ret then
    ngx.log(ngx.ERR, err)
    throw(1002)
  end
  return ret[1]
end

--查询多条
function _M.query(sql, ...)
  local s = string.format(sql, ...)
  if debug then
    ngx.log(ngx.DEBUG, s)
  end

  local ret, err, errno, sqlstate = _M.db:query(s)
  if not ret then
    ngx.log(ngx.ERR, err)
    throw(1002)
  end
  return ret
end

--更新一条
function _M.update(sql, ...)
  local s = string.format(sql, ...)
  if debug then
    ngx.log(ngx.DEBUG, s)
  end

  local ret, err, errno, sqlstate = _M.db:query(s)
  if not ret then
    ngx.log(ngx.ERR, err)
    throw(1002)
  end
  return ret.affected_rows
end

--插入一条，返回生成的id，一般用于自增长主键
function _M.insert(sql, ...)
  local s = string.format(sql, ...)
  if debug then
    ngx.log(ngx.DEBUG, s)
  end

  local ret, err, errno, sqlstate = _M.db:query(s)
  if not ret then
    ngx.log(ngx.ERR, err)
    throw(1002)
  end
  return ret.insert_id
end

--批量更新 ps为参数数组， each为true则检查每条影响的行数>=1 比较初级
function _M.updates(sql, ps, each)
  if debug then
    ngx.log(ngx.DEBUG, sql)
  end

local ret, err, errno, sqlstate = _M.db:query("PREPARE data_updatesFROM '" .. sql .. "'")

  if not ret then
    ngx.log(ngx.ERR, err)
    throw(1002)
  end
  for _, v in ipairs(ps) do
    local us = {}
    for i, vv in ipairs(v) do
      ret, err, errno, sqlstate = _M.db:query('SET @p' .. i .. ' = ' .. vv)
      if not ret then
        ngx.log(ngx.ERR, err)
        throw(1002)
      end
      table.insert(us, '@p' .. i)
    end

ret, err, errno, sqlstate = _M.db:query('EXECUTE data_updates USING' .. table.concat(us, ','))

    if not ret then
      ngx.log(ngx.ERR, err)
      throw(1002)
    end
    if each and ret.affected_rows < 1 then
      ngx.log(ngx.ERR, util.tostring(ret))
      code.throw(code.ERR_LOCK)
    end
  end

ret, err, errno, sqlstate = _M.db:query('DEALLOCATE PREPAREdata_updates')

  if not ret then
    ngx.log(ngx.ERR, err)
    throw(1002)
  end
end

--批量插入 返回一堆生成的id
function _M.inserts(sql, ps)
  if debug then
    ngx.log(ngx.DEBUG, sql)
  end

  local ids = {}

local ret, err, errno, sqlstate = _M.db:query("PREPARE data_insertsFROM '" .. sql .. "'")

  if not ret then
    ngx.log(ngx.ERR, err)
    throw(1002)
  end
  for _, v in ipairs(ps) do
    local us = {}
    for i, vv in ipairs(v) do
      ret, err, errno, sqlstate = _M.db:query('SET @p' .. i .. ' = ' .. vv)
      if not ret then
        ngx.log(ngx.ERR, err)
        throw(1002)
      end
      table.insert(us, '@p' .. i)
    end

ret, err, errno, sqlstate = _M.db:query('EXECUTE data_inserts USING' .. table.concat(us, ','))

    if not ret then
      ngx.log(ngx.ERR, err)
      throw(1002)
    end
    table.insert(ids, ret.insert_id)
  end

ret, err, errno, sqlstate = _M.db:query('DEALLOCATE PREPAREdata_inserts')

  if not ret then
    ngx.log(ngx.ERR, err)
    throw(1002)
  end
  return ids
end

return _M
end

return _F