使用golang连接操作mysql的方式主要有以下两种：
【方式1】使用go定义的database/sql接口，基于驱动接口直接在代码里硬编码sql语句；
需要下载包： go get github.com/go-sql-driver/mysql
代码中import引用 github.com/go-sql-driver/mysql ，访问数据库都是直接写 sql，取出结果然后自己拼成对象（备注：Go没有内置的驱动支持任何数据库）。
import (
"database/sql"
_ "github.com/go-sql-driver/mysql"
)【方式2(推荐)】使用gorm（Object Relation Mapping），即对象关系映射的方式在代码里抽象的操作数据库，即对数据库的操作进行封装，屏蔽了数据操作的细节，大大简化了开发工作效率。
需要下载包：go get -u github.com/jinzhu/gorm
go get -u github.com/jinzhu/gorm/dialects/mysql
代码引入：
import (
"github.com/jinzhu/gorm"
_ "github.com/jinzhu/gorm/dialects/mysql" // 需要用到mysql包里的init
)================================================
实例1（方式1）：
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)
}实例2（方式2）
使用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)
}
}