golang实现对mysql数据库的操作

一、创建表格

marksql := "create table mark (id int(20) primary key auto_increment,markname char(50)," +
		"mlongitude float,mlatitude float,mdescribe char(255),mtype int,creattime char(50),mvalue float );"
smt, err := db.Db.Prepare(marksql)
checkErr(err)
smt.Exec()

二、创建外键

//定义好友fk1外键
	idx_userid := "alter table user add index idx_userid(uid);"
	smt, err = db.Db.Prepare(idx_userid)
	checkErr(err)
	smt.Exec()

	idx_userid = "alter table friend add index idx_friuid(uid);"
	smt, err = db.Db.Prepare(idx_userid)
	checkErr(err)
	smt.Exec()

//定义好友fk2外键
	frifk2sql := "alter table friend add constraint fk2_fri_user foreign key(uid) references user(uid);"
	smt, err = db.Db.Prepare(frifk2sql)
	checkErr(err)
	smt.Exec()

三、插入数据

//插入到数据库表中
func (db *DbOperation) Insert2Table(str string) bool {

	stmt, err := db.Db.Prepare(str)
	checkErr(err)
	res, err := stmt.Exec()
	defer stmt.Close()

	checkErr(err)
	i, err := res.RowsAffected()
	if i > 0 && err == nil {
		return true
	} else {
		return false
	}

}

var sql = "insert into user(uid,ucode,unickname,password,ufavicon,utel,utype,udescribe,ucreattime) values (" + strconv.Itoa(user.Id) + ",'" + user.Code + "','" + user.NickName + "','" + user.Password + "','" + user.Img + "','" + user.Mobile + "'," + strconv.FormatFloat(user.Category, 'e', 10, 64) + ",'" + user.Description + "','" + user.CreateTime + "');"

		if !db.G_db.Insert2Table(sql) {
			fmt.Println("user表插入不成功！")
		} else {
			fmt.Println("entering weite2db OK ! ! !")
		}

四、更新数据库

var sql = "update user set ufavicon='" + user.Img +
		"',udescribe='" + user.Description +
		"',utype=" + strconv.FormatFloat(user.Category, 'e', 10, 64) + " where uid=" + strconv.Itoa(user.Id) + ";"
	if !db.G_db.Insert2Table(sql) {
		fmt.Println("user表更新不成功！")
		return errors.New("update error!!!")
	}

五、查找数据库

func (db *DbOperation) Find(str string) *sql.Rows {
	fmt.Println(str)
	rows, err := db.Db.Query(str)
	fmt.Println("--------------------err", err)
	if err != nil {
		panic("error in: selecting in table")
	}
	//rows.Close()

	return rows
}

func (pUsersSet *UserSet) LoadData(pDb *db.DbOperation) {
	var user User
	fmt.Println("begining:load userset........")
	selectstr := "select uid,ucode,unickname,password,ufavicon,utel,utype,udescribe,ucreattime from user"
	rows := pDb.Find(selectstr)

	for rows.Next() {
		err := rows.Scan(&user.Id, &user.Code, &user.NickName, &user.Password, &user.Img, &user.Mobile, &user.Category, &user.Description, &user.CreateTime)

		if err != nil {
			panic("error in:scanning the table user")
		}

		fmt.Println("-------------------Load data to cache--------------------")
		fmt.Println(user)
		pUsersSet.WriteUserFromDb2Cache(user)
	}
}

有疑问加站长微信联系（非本文作者）