准备工作：创建数据库

Go语言连接mysql

安装包

go get "gethub.com/go-sql-driver/mysql"
go get "gethub.com/jmoiron/sqlx"

Exec增删改

//连接数据库信息
var (
	userName  string = "root"
	passWord  string = "123456"
	idAddress string = "127.0.0.1"
	port      string = "3306"
	dbName    string = "go_text"
	charset   string = "utf8"
)

// 连接数据库这里用*来保证连接的是同一个对象
//root:123456@tcp(127.0.0.1:3306)/go_text?charset=utf8
func connectMySQL() *sqlx.DB {
          
   
	dbstr := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?charset=%s", userName, passWord, idAddress, port, dbName, charset)
	DB, err := sqlx.Open("mysql", dbstr)
	fmt.Println(err)
	ping(DB)
	return DB

}
func ping(DB *sqlx.DB) {
          
   
	err := DB.Ping()
	if err != nil {
          
   
		fmt.Println("ping failed")
	} else {
          
   
		fmt.Println("ping success")
	}
}

func textExec(mysqlDB *sqlx.DB) {
          
   
	//Exec 指行sql语句
	insertSQL := "insert into  user(userid,username,password,avatar,create_time,update_time)values(?,?,?,?,?,?)"
	result, err := mysqlDB.Exec(insertSQL, 10000, "周南", "123456", "ks.png", "2022-11-11 11:11:11", "2022-11-11 11:11:11")
	if err != nil {
          
   
		fmt.Println("数据插入失败", err)
		return
	}
	id, _ := result.LastInsertId()
	fmt.Println(id)
	//修改
	updateSQL := "update user set username=打阿伟where id =1"
	result2, err := mysqlDB.Exec(updateSQL)
	rowNum, _ := result2.RowsAffected() //受到印象的行数
	fmt.Println("更新成功，affected rows", rowNum)

	//删除
	deleteSQL := "delete from user where id =2"
	result3, err := mysqlDB.Exec(deleteSQL)
	rowNum1, _ := result3.RowsAffected() //受到印象的行数
	fmt.Println("删除成功，affected rows", rowNum1)
}

查询

Query查询

unc textQuery(mysqlDB *sqlx.DB) {
          
   
	querySql := "select *from user"
	rows, err := mysqlDB.Query(querySql)
	if err != nil {
          
   
		fmt.Println("数据查询失败", err)
		return
	}
	fmt.Println(rows)
	//返回一个结果集rows
	//next来获取结果
	//要关闭结果集
	for rows.Next() {
          
   
		var id, userid int
		var username, password, avatar, create_time, update_time string
		rows.Scan(&id, &userid, &username, &password, &avatar, &create_time, &update_time)
		fmt.Println(id, userid, username, password, avatar, create_time, update_time)
	}
	rows.Close()
}

Get和Select查询

mysqlDB := connectMySQL()
	defer mysqlDB.Close()
	type user struct {
          
   
		Id         int    `db:"id"`
		UserId     int    `db:"userid"`
		UserName   string `db:"username"`
		Password   string `db:"password"`
		Avatar     string `db:"avatar"`
		CreateTime string `db:"create_time"`
		UpdateTime string `db:"update_time"`
	}
	userData := new(user)
	mysqlDB.Get(userData, "select * from user where id =1")
	fmt.Println(*userData)
	var userSlice []user
	err := mysqlDB.Select(&userSlice, "select*from user")
	if err != nil {
          
   
		fmt.Println(err)
	}
	for i := range userSlice {
          
   
		fmt.Println(userSlice[i])
	}

}