golang连接mysql，及基本操作-网页编程网

当前位置：主页 >> Golang >> 正文

golang连接mysql，及基本操作

阅读：1589 输入：2022-10-01 23:34:36

安装驱动

go get github.com/go-sql-driver/mysql

创建user表，并执行CRUD操作。

package main
import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
    "time"
)
//数据库连接信息
const (
	USERNAME = "root"
	PASSWORD = "123456"
	NETWORK = "tcp"
	SERVER = "127.0.0.1"
	PORT = 3306
	DATABASE = "test"
)
//user表结构体定义
type User struct {
	Id int `json:"id" form:"id"`
	Username string `json:"username" form:"username"`
	Password string `json:"password" form:"password"`
	Status int   `json:"status" form:"status"`// 0 正常状态， 1删除
	Createtime int64 `json:"createtime" form:"createtime"`
}
 
func main() {
	conn := fmt.Sprintf("%s:%s@%s(%s:%d)/%s",USERNAME, PASSWORD, NETWORK, SERVER, PORT, DATABASE)
	DB, err := sql.Open("mysql", conn)
	if err != nil {
		fmt.Println("connection to mysql failed:", err)
		return
	}
	
    DB.SetConnMaxLifetime(100*time.Second)  //最大连接周期，超时的连接就close
    DB.SetMaxOpenConns(100)                //设置最大连接数
    CreateTable(DB)
    InsertData(DB)
    QueryOne(DB)
    QueryMulti(DB)
    UpdateData(DB)
    DeleteData(DB)
}
//创建数据表
func CreateTable(DB *sql.DB) {
	sql := `CREATE TABLE IF NOT EXISTS users(
	id INT(4) PRIMARY KEY AUTO_INCREMENT NOT NULL,
	username VARCHAR(64),
	password VARCHAR(64),
	status INT(4),
	createtime INT(10)
	); `
	
	if _, err := DB.Exec(sql); err != nil {
        fmt.Println("create table failed:", err)
        return
    }
	fmt.Println("create table successd")
}
 
//插入数据
func InsertData(DB *sql.DB) {
    result,err := DB.Exec("insert INTO users(username,password) values(?,?)","test","123456")
    if err != nil{
        fmt.Printf("Insert data failed,err:%v", err)
        return
    }
    lastInsertID,err := result.LastInsertId()    //获取插入数据的自增ID
    if err != nil {
        fmt.Printf("Get insert id failed,err:%v", err)
        return
    }
    fmt.Println("Insert data id:", lastInsertID)
   
    rowsaffected,err := result.RowsAffected()  //通过RowsAffected获取受影响的行数
    if err != nil {
        fmt.Printf("Get RowsAffected failed,err:%v",err)
        return
    }
    fmt.Println("Affected rows:", rowsaffected)
}
 
//查询单行
func QueryOne(DB *sql.DB) {
	user := new(User)   //用new()函数初始化一个结构体对象
	row := DB.QueryRow("select id,username,password from users where id=?", 1)
	//row.scan中的字段必须是按照数据库存入字段的顺序，否则报错
	if err := row.Scan(&user.Id,&user.Username,&user.Password); err != nil {
		fmt.Printf("scan failed, err:%v\n", err)
		return
	}
	fmt.Println("Single row data:", *user)
}
 
//查询多行
func QueryMulti(DB *sql.DB) {
    user := new(User)
    rows, err := DB.Query("select id,username,password from users where id = ?", 2)
    
    defer func() {
        if rows != nil {
            rows.Close()   //关闭掉未scan的sql连接
        }
    }()
    if err != nil {
        fmt.Printf("Query failed,err:%v\n", err)
        return
    }
    for rows.Next() {
        err = rows.Scan(&user.Id, &user.Username, &user.Password)  //不scan会导致连接不释放
        if err != nil {
            fmt.Printf("Scan failed,err:%v\n", err)
            return
        }
        fmt.Println("scan successd:", *user)
    }
}
 
//更新数据
func UpdateData(DB *sql.DB){
    result,err := DB.Exec("UPDATE users set password=? where id=?","111111",1)
    if err != nil{
        fmt.Printf("Insert failed,err:%v\n", err)
        return
    }
    fmt.Println("update data successd:", result)
    
    rowsaffected,err := result.RowsAffected()
    if err != nil {
        fmt.Printf("Get RowsAffected failed,err:%v\n",err)
        return
    }
    fmt.Println("Affected rows:", rowsaffected)
}
 
//删除数据
func DeleteData(DB *sql.DB){
    result,err := DB.Exec("delete from users where id=?",1)
    if err != nil{
        fmt.Printf("Insert failed,err:%v\n",err)
        return
    }
    fmt.Println("delete data successd:", result)
    
    rowsaffected,err := result.RowsAffected()
    if err != nil {
        fmt.Printf("Get RowsAffected failed,err:%v\n",err)
        return
    }
    fmt.Println("Affected rows:", rowsaffected)
}

GORM

下载包

go get github.com/jinzhu/gorm

Go的Gin框架和Gorm开发简单的CRUD API

package main
 
import (
	"fmt"
	"github.com/gin-gonic/gin"
	"github.com/jinzhu/gorm"
	_ "github.com/jinzhu/gorm/dialects/mysql"
)
 
var MysqlDB *gorm.DB
 
type User struct {
	Id   int    `gorm:"size:11;primary_key;AUTO_INCREMENT;not null" json:"id"`
	Age  int    `gorm:"size:11;DEFAULT NULL" json:"age"`
	Name string `gorm:"size:255;DEFAULT NULL" json:"name"`
	//gorm后添加约束，json后为对应mysql里的字段
}
 
func main() {
	MysqlDB, err := gorm.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/test?charset=utf8")
	if err != nil {
		fmt.Println("failed to connect database:", err)
		return
	}
	fmt.Println("connect database success")
	MysqlDB.SingularTable(true)
	MysqlDB.AutoMigrate(&User{}) //自动建表
	fmt.Println("create table success")
	defer MysqlDB.Close()
 
	Router()
}
 
func Router() {
	router := gin.Default()
	router.GET("/user/init", InitPage)
	router.POST("/user/create", CreateUser)
	router.GET("/user/list", ListUser)
	router.POST("/user/update", UpdateUser)
	router.GET("/user/find", GetUser)
	router.Run(":8080")
        //API接口
}
 
func InitPage(c *gin.Context) {
	c.JSON(200, gin.H{
		"message": "pong",
	})
}
 
func CreateUser(c *gin.Context) {
	var user User
	c.BindJSON(&user)     //使用bindJSON填充对象
	MysqlDB.Create(&user) //创建对象
	c.JSON(200, &user)    //返回页面
}
 
func UpdateUser(c *gin.Context) {
	var user User
	id := c.PostForm("id")                //post方法取相应字段
	err := MysqlDB.First(&user, id).Error //数据库查找主键=ID的第一行
	if err != nil {
		c.AbortWithStatus(404)
		fmt.Println(err.Error())
	} else {
		c.BindJSON(&user)
		MysqlDB.Save(&user) //提交更改
		c.JSON(200, &user)
	}
}
func ListUser(c *gin.Context) {
	var user []User
	line := c.Query("line")
	MysqlDB.Limit(line).Find(&user) //限制查找前line行
	c.JSON(200, &user)
}
func GetUser(c *gin.Context) {
	id := c.Query("id")
	var user User
	err := MysqlDB.First(&user, id).Error
	if err != nil {
		c.AbortWithStatus(404)
		fmt.Println(err.Error())
	} else {
		c.JSON(200, &user)
	}
}

定义了以下API接口：

http://127.0.0.1:8080/user/init //用户初始化
http://127.0.0.1:8080/user/create //用户创建
http://127.0.0.1:8080/user/list //用户列出
http://127.0.0.1:8080/user/update //用户更新
http://127.0.0.1:8080/user/find //用户查找

相关阅读