web01-配置Mysql

安装Mysql 创建userInfo表

CREATE TABLE `userinfo` (
  `uid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(16) DEFAULT '',
  `departname` varchar(20) DEFAULT '',
  `password` varchar(32) DEFAULT '',
  `sex` tinyint(1) unsigned zerofill DEFAULT '0',
  `userid` varchar(32) DEFAULT '',
  `phone` varchar(13) DEFAULT '',
  `phoneprefix` varchar(6) DEFAULT '',
  `createtime` int(10) unsigned NOT NULL,
  `updatetime` int(10) unsigned NOT NULL,
  `state` tinyint(1) DEFAULT '-1',
  `authtoken` char(32) DEFAULT '',
  `mail` varchar(40) DEFAULT '',
  `oldpassword` varchar(100) DEFAULT '',
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=1000039 DEFAULT CHARSET=utf8;

db 控制器

//所有的db操作全部使用同一个db实例
var db *sql.DB
var err error

func DBConf()*sql.DB  {
    if db == nil {
        InitMysql()
    }
    return db
}

func InitMysql()  {
    db, err = sql.Open(config.TomlConf().Mysql().DBtype, config.TomlConf().Mysql().Url)
    db.SetMaxIdleConns(200)
    db.SetMaxOpenConns(100)
    checkErr(err)
}


func checkErr(err error) {
    if err != nil {
        panic(err)
    }
}

userModel

type UserInfoModel struct {
    Uid         int64       `json:"uid,omitempty" form:"uid,omitempty"`
    UserName    string      `json:"username,omitempty" form:"username,omitempty"`
    Password    string      `json:"password,omitempty" form:"password,omitempty"`
    CreateTime  int64       `json:"createtime,omitempty" form:"createtime,omitempty"`
    UpdateTime  int64       `json:"updatetime,omitempty" form:"updatetime,omitempty"`
    Sex         int         `json:"sex,omitempty" form:"sex,omitempty"`     //0默认未设置 1男,2女
    UserId      string      `json:"userid,omitempty" form:"userid,omitempty"`
    DepartName  string      `json:"departname,omitempty" form:"departname,omitempty"`
    Phone       string      `json:"phone,omitempty" form:"phone,omitempty"`
    PhonePrefix     string      `json:"phoneprefix,omitempty" form:"phoneprefix,omitempty"`
    Mail        string      `json:"mail,omitempty" form:"mail,omitempty"`
    OldPassword     string      `json:"oldpassword,omitempty" form:"oldpassword,omitempty"`
    Authtoken   string      `json:"authtoken,omitempty" form:"authtoken,omitempty"`
    State       int         `json:"state,omitempty" form:"state,omitempty"`
}

//初始化一个uer
func NewUser() UserInfoModel {
    return UserInfoModel{}
}

func (user *UserInfoModel)ToString()(desc string)  {
    desc = "name:"+user.UserName
    return desc
}

//插入到数据库
func (user *UserInfoModel)InsertUser()(error){
    if CheckUserNameValid(user.UserName) == false{
        err := errors.New("username already exists")
        return err
    }

    if CheckPhoneValid(user.Phone) == false{
        err := errors.New("phone number already exists")
        return err
    }

    db := db.DBConf()
    stmt, err := db.Prepare("INSERT userinfo SET username=?,departname=?,createtime=?,updatetime=?,password=?,sex=?,mail=?,phone=?,phoneprefix=?")
    checkErr(err)
    _, err = stmt.Exec(user.UserName, user.DepartName,user.CreateTime,user.UpdateTime,user.Password,user.Sex,user.Mail,user.Phone,user.PhonePrefix)
    checkErr(err)
    return err
}

//更新用户信息到数据库
func (user *UserInfoModel)UpdateIntoDB()(error)  {
    if user.Uid == 0 {
        log.Error("update faild,Pri key Uid not found")
        return errors.New("update faild,Pri key Uid not found")
    }
    db := db.DBConf()
    stmt, err := db.Prepare("UPDATE userinfo set username=?,departname=?,createtime=?,password=?,sex=? where uid=?")
    checkErr(err)
    _, err = stmt.Exec(user.UserName, user.DepartName,user.CreateTime,user.Password,user.Sex, user.Uid)
    checkErr(err)
    return err
}

//判断次用户名有没有被注册
func CheckUserNameValid(name string)(bool)  {
    db := db.DBConf()
    err := db.QueryRow("SELECT  username FROM userinfo WHERE username=?", name).Scan(&name)
    if err != nil {
        return true
    }
    log.Warn("username already exist",name)
    return false
}

//判断手机号是否被注册
func CheckPhoneValid(phone string)(bool)  {
    db := db.DBConf()
    err := db.QueryRow("SELECT  username FROM userinfo WHERE phone=?", phone).Scan(&phone)
    if err != nil {
        return true
    }
    log.Warn("phone number already exists",phone)
    return false
}
//判断email是否被注册
func CheckEmailValid(mail string)(bool)  {
    db := db.DBConf()
    err := db.QueryRow("SELECT  username FROM userinfo WHERE mail=?", mail).Scan(&mail)
    if err != nil {
        return true
    }
    log.Warn("mail already exists",mail)
    return false
}

//判断userid是否已存在
func CheckUserIdValid(userId string)(bool)  {
    db := db.DBConf()
    err := db.QueryRow("SELECT  username FROM userinfo WHERE userid=?", userId).Scan(&userId)
    if err != nil {
        return true
    }
    log.Warn("CheckUserIdValid:userid already exists",userId)
    return false
}
//通过uid查找用户
func FindUserFromDB(uid int64)(UserInfoModel,error)  {
    var user UserInfoModel
    db := db.DBConf()
    err := db.QueryRow("SELECT uid, username, departname, password, sex, userid, phone, phoneprefix, createtime, updatetime, state, authtoken, mail, oldpassword FROM userinfo WHERE uid=?", uid).
        Scan(&user.Uid,
         &user.UserName, &user.DepartName, &user.Password, &user.Sex, &user.UserId, &user.Phone, &user.PhonePrefix,
        &user.CreateTime, &user.UpdateTime,&user.State,&user.Authtoken,&user.Mail,&user.OldPassword)
    checkErr(err)
    return user,err
}

//通过用户名查找用户
func FindUserFromDBByName(name string)(UserInfoModel,error)  {
    var user UserInfoModel
    db := db.DBConf()
    err := db.QueryRow("SELECT uid, username, departname, password, sex, userid, phone, phoneprefix, createtime, updatetime, state, authtoken, mail, oldpassword FROM userinfo WHERE username=?", name).
        Scan(&user.Uid,
        &user.UserName, &user.DepartName, &user.Password, &user.Sex, &user.UserId, &user.Phone, &user.PhonePrefix,
        &user.CreateTime, &user.UpdateTime,&user.State,&user.Authtoken,&user.Mail,&user.OldPassword)
    user.Password = ""
    user.OldPassword = ""
    checkErr(err)
    return user,err
}

//通过用户名和密码查找用户
func CheckUserNameAndPass(name string,pass string)(UserInfoModel,error)  {
    var user UserInfoModel
    db := db.DBConf()
    err := db.QueryRow("SELECT uid, username, departname, password, sex, userid, phone, phoneprefix, createtime, updatetime, state, authtoken, mail, oldpassword FROM userinfo WHERE username=?", name).
        Scan(&user.Uid,
        &user.UserName, &user.DepartName, &user.Password, &user.Sex, &user.UserId, &user.Phone, &user.PhonePrefix,
        &user.CreateTime, &user.UpdateTime,&user.State,&user.Authtoken,&user.Mail,&user.OldPassword)

    if user.Password != pass {
        err = errors.New("password not right")
        user = NewUser()
    }
    user.Password = ""
    user.OldPassword = ""
    checkErr(err)
    return user,err
}

//通过uid删除用户
func DeleteUserFromDB(uid int64)(error)  {
    db := db.DBConf()
    stmt, err := db.Prepare("delete from userinfo where uid=?")
    if err != nil{
        return err
    }
    fmt.Println(uid)
    _,err = stmt.Exec(uid)
    return err
}

//错误日志
func checkErr(err error) {
    if err != nil {
        log.Warn(err.Error())
    }
}