skynet demo plain text 分支（二）Mysql业务处理

skynet demo 项目地址(plain text分支 )

debugtool 代码

---

-- mysqldb.lua
local skynet = require  "skynet"
local mysql = require "skynet.db.mysql"
require "skynet.manager"
local debugtool = require "debugtool"
local db
local CMD = {}

-- 注册 
function CMD.sinup(username, password, nickname)

    local stmt = db:prepare("select id, username, nickname from user where username=?")
    local res = db:execute(stmt, username)
    db:stmt_close(stmt)

    if not next(res) then
        if not nickname or nickname == "" then
            nickname = username
        end
        stmt = db:prepare("insert into user(username, password,  nickname) values (?,?,?)")
        res = db:execute(stmt,username, password, nickname)
        return {result="注册成功", ok=true}
    end
    return {result="用户已经存在", ok=false}
end

function CMD.auth(username, password)
    local stmt = db:prepare("select password from user where username=?")
    local res = db:execute(stmt, username)
    db:stmt_close(stmt)
    debugtool.dump(res)
    if #res ~=1 then
        return {result="用户不存在", ok=false}
    end
    if password ~= res[1].password then
        debugtool.log("recv password: %s, password  in db: %s", password,  res[1].password)
        return {result="用户名或密码错误", ok=false}
    end
    return {result="认证成功", ok=true}
end


skynet.start(function()
    db = mysql.connect({
        host="192.168.46.128",
        port=3306,
        database="skynetdb",
        user="root",
        password="123456",
        max_packet_size=1024*1024,
        charset="utf8mb4",
        on_connect=function()
            skynet.error("mysql connect !")
        end
    })
    if not db then
        skynet.error("failed to connect mysql!")
    end

    skynet.dispatch("lua", function(session,  address, cmd, ...)
        skynet.error("mysqldb get cmd "..cmd)
        local f = assert(CMD[cmd])
        skynet.ret(skynet.pack(f(...)))
    end)

    skynet.register("mysqldb")
end)

创建数据库

-- user.sql
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `nickname` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  `password` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `username` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

在mysql中执行上述sql脚本，创建用户表。

业务逻辑

local mysql = require "skynet.db.mysql"
...
db = mysql.connect({
    host="192.168.46.128",
    port=3306,
    database="skynetdb",
    user="root",
    password="123456",
    max_packet_size=1024*1024,
    charset="utf8mb4",
    on_connect=function()
        skynet.error("mysql connect !")
    end
})

引用skynet框架自带的mysql驱动，填写好相应参数，连接到mysql数据库，on_connect函数会在驱动连接到数据库时被触发。

    skynet.dispatch("lua", function(session,  address, cmd, ...)
        skynet.error("mysqldb get cmd "..cmd)
        local f = assert(CMD[cmd])
        skynet.ret(skynet.pack(f(...)))
    end)

dispatch函数为mysqldb服务注册了其消息处理函数，将调用者传过来的命令正确的分发到相应的处理函数上，并在业务处理完后将结果回传到调用者处。

function CMD.sinup(username, password, nickname)

    local stmt = db:prepare("select id, username, nickname from user where username=?")
    local res = db:execute(stmt, username)
    db:stmt_close(stmt)

    if not next(res) then
        if not nickname or nickname == "" then
            nickname = username
        end
        stmt = db:prepare("insert into user(username, password,  nickname) values (?,?,?)")
        res = db:execute(stmt,username, password, nickname)
        return {result="注册成功", ok=true}
    end
    return {result="用户已经存在", ok=false}
end

这个函数的主要功能是用户注册，将客户端发过来的信息保存到数据库中，其return的这个table的结构与我们定义的sigup协议中的response一致。

function CMD.auth(username, password)
    local stmt = db:prepare("select password from user where username=?")
    local res = db:execute(stmt, username)
    db:stmt_close(stmt)
    debugtool.dump(res)
    if #res ~=1 then
        return {result="用户不存在", ok=false}
    end
    if password ~= res[1].password then
        debugtool.log("recv password: %s, password  in db: %s", password,  res[1].password)
        return {result="用户名或密码错误", ok=false}
    end
    return {result="认证成功", ok=true}
end

用户登录认证，其中debugtool.dump是自定义的一个库，其内容详见 github

if #res ~=1 then中的#res表示取res的长度，~=表示不等于，这个是lua的基础知识。