MySQL两种方式实现增删改查
本文演示两种方式处理MySQL请求与数据示例演示如何连接、查询、删除与更新，SQL在文章底部。
原生预处理方式:
[*]优点：高度自定义，方便处理复杂逻辑与数据
[*]缺点：不如语句构建器方案简单
local cjson = require("cjson")
local conn = require("mysql.conn").new()
local response = require("fastweb.response")
local fw = require("fastweb")
-- 建立连接
local function mysql_connect()
local conn_result = conn:connect("127.0.0.1","root","root","test","utf8mb4",3306)
if conn_result == 0 then
-- 连接成功
return true
elseif conn_result == 1 then
-- 连接超时
return false,"timeout"
elseif conn_result == 2 then
-- 连接错误
return false,conn:last_error()
end
end
-- 清空示例
local function mysql_delete()
-- 清空数据
local ppst = conn:setsql("DELETE FROM users")
ppst:update()
end
-- 插入数据
local function mysql_insert()
local ppst = conn:setsql("INSERT INTO users(username,password,age,data,head)VALUES(?,?,?,?,?)")
-- 文本
ppst:set_str(1,"fastweb")
ppst:set_str(2,"123456")
-- 数字
ppst:set_i32(3,25)
-- 二进制
ppst:set_str(4,"996\x0088")
-- 字节流
local head_file = io.open(fw.website_dir().."/head.png", "rb")
local head_content = head_file:read("all")
head_file:close()
ppst:set_blob(5,head_content)
ppst:update()
end
-- 更新数据
local function mysql_update()
local ppst = conn:setsql("UPDATE users SET age = ? WHERE username = ?")
ppst:set_i32(1,66)
ppst:set_str(2,"fastweb")
ppst:update()
end
-- 查询数据
local function mysql_select()
local ppst = conn:setsql("SELECT * FROM users WHERE age > ?")
ppst:set_i32(1,0)
-- 查询结果
local result = ppst:query();
local users = {}
while result:next() do
local user = {
username = result:get("username"),
password = result:get("password"),
age = result:get("age"),
data_size = #result:get("data"),
head_size = #result:get("head")
}
table.insert(users,user)
end
return users;
end
-- 连接
local is_success,error_msg = mysql_connect()
if is_success == false then
response.send("connect failed,"..error_msg)
return
end
mysql_delete();
mysql_insert();
mysql_select();
local users = mysql_select()
response.send(cjson.encode(users))
SQL构建器方式:
[*] 优点：集成度高、美观、代码简洁
[*] 缺点：不适用较复杂的SQL语句
local cjson = require("cjson")
local conn = require("mysql.conn").new()
local response = require("fastweb.response")
local fw = require("fastweb")
-- 建立连接
local function mysql_connect()
local conn_result = conn:connect("127.0.0.1","root","root","test","utf8mb4",3306)
if conn_result == 0 then
-- 连接成功
return true
elseif conn_result == 1 then
-- 连接超时
return false,"timeout"
elseif conn_result == 2 then
-- 连接错误
return false,conn:last_error()
end
end
-- 清空示例
local function mysql_delete()
-- 清空数据
conn:delete():table("users"):exec()
end
-- 插入数据
local function mysql_insert()
-- 字节流
local head_file = io.open(fw.website_dir().."/head.png", "rb")
local head_content = head_file:read("all")
head_file:close()
local insert = conn:insert():table("users")
:set_str("username","fastweb")
:set_str("password","123456")
:set_i32("age",25)
:set_blob("data","996\x0088")
:set_blob("head",head_content)
:exec()
end
-- 更新数据
local function mysql_update()
conn:update():table("users"):set_i32("age",66):where_str("username","=","fastweb"):exec()
end
-- 查询数据
local function mysql_select()
return conn:select():table("users"):field({"username","password","age"}):where_i32("age",">",0):query():table()
end
-- 连接
local is_success,error_msg = mysql_connect()
if is_success == false then
response.send("connect failed,"..error_msg)
return
end
mysql_delete();
mysql_insert();
mysql_select();
local users = mysql_select()
response.send(cjson.encode(users))
SQL文件：
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users`(
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`age` int(11) NULL DEFAULT NULL,
`data` binary(255) NULL DEFAULT NULL,
`head` blob NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 38 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
页:
[1]