lua操作数据库

项目中C对lua只开放了C_updateColumn用于写，C_selectDb用于读。想要把直接一个lua table映射更新到一张数据库表比较烦。

于是写了以下这些函数方便操作。

---一些操作数据库用的工具函数--@author lqkDatabase = {}---生成update需要的a=11,b=22function Database.colValueSet(colValue)local str = ""local substr = ""local bf = falsefor k,v in pairs(colValue) doif type(v) ~= "table" thenk = "`"..k.."`"if type(v) == "string" thenv = "'"..v.."'"endsubstr = k.."="..vif bf == false thenstr = str..substrbf = trueelsestr = str..","..substrendendendreturn strend---返回插入数据库所需要的一些参数--@return col 列名--@return value 值--@return tcol blob列function Database.colValueInsert(colValue)local col = ""local value = ""local tcol = {}local bf = falselocal kblobfor k,v in pairs(colValue) dokblob = kk = "`"..k.."`"if type(v) == "string" thenv = "'"..v.."'"endif type(v) ~= "table" thensubstr = k.."="..vif bf == false thencol = tostring(k)value = tostring(v)bf = trueelsecol = col..","..kvalue = value..","..vendelsetable.insert(tcol, kblob)endendreturn col, value, tcolend---直接插入数据库--@param t 表名--@param data 要存到数据库的内容，是lua 中的table，key是 数据库中的列名。--@return aid，如果失败返回falsefunction Database.insert(t, data)local sqllocal enSuc, enData--普通字段local col, value, tcol = Database.colValueInsert(data)sql = "insert into " .. t .. " (".. col ..") value(".. value ..")"enSuc, enData = C_updateDb(sql)if enSuc == false thenreturn falseend--aidsql = "SELECT LAST_INSERT_ID() as aid"enSuc, enData = C_selectDb(sql)if enSuc == false then--TODO ERRORreturn falseendlocal aid = enData[1].aid--blobif table.getn(tcol) > 0 thenenSuc, enData = C_updateColumn(t, tcol, data, "aid=" .. aid)if enSuc == false thenC_errorLog(space("Database.insert", t, tcol[1], aid))return falseendendreturn aidend---获取数据库的多列--@param t 表名--@param aid 记录aid--@param columns 列名字符串组成的数组--@return key是列名的tablefunction Database.getColumns(t, aid, columns)if t == nil or aid == nil or columns == nil thenreturn falseendif table.maxn(columns) == 0 thenreturn falseendlocal data = {}local sql = "select "for k, v in pairs(columns) dosql = sql..v..","endsql = string.sub(sql, 1, -2)sql = sql.." from "..tsql = sql.." where aid="..aidlocal enSuc, enData = C_selectDb(sql)if enSuc == false thenreturn falseendreturn enData[1]endfunction Database.getColumn(t, aid, column)local columns = {}table.insert(columns, column)local data = Database.getColumns(t, aid, columns)if data == false thenreturn falseendreturn data[column]end---直接插入数据库--@param t 表名--@param data 要存到数据库的内容，是lua 中的table，key是 数据库中的列名。--@return aid，如果失败返回falsefunction Database.setColumns(t, aid, data)if t == nil or aid == nil or data == nil thenreturn falseendlocal sqllocal enSuc, enData--普通字段local col, value, tcol = Database.colValueInsert(data)local cvStr = Database.colValueSet(data)sql = "update "..t.." set "..cvStr.." where aid="..aidenSuc, enData = C_updateDb(sql)if enSuc == false thenC_errorLog(space("Database.setColumns1"))return falseend--blobif table.getn(tcol) > 0 thenenSuc, enData = C_updateColumn(t, tcol, data, "aid=" .. aid)if enSuc == false thenC_errorLog(space("Database.setColumns", t, tcol[1], aid))return falseendendreturn trueend---生成where子句--@param key 字符串，表明哪个字段--@param value 值--@author lqkfunction Database.genWhereSub(key, value)local t = type(value)if t == "number" or t == "string" or t == "boolean" thenreturn " and "..key.."=".."'"..value.."'"elsereturn ""endend