09-golang连接mysql数据库-01.基本使用（mysql的连接和增删改查）

1. 连接数据库

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
)
var DBName,_ =sql.Open("mysql","root:40010355@(127.0.0.1:3306)/liubeidb")

2. 创建表

之后补上go创建表

CREATE TABLE `user` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(20) DEFAULT '',
    `age` INT(11) DEFAULT '0',
    PRIMARY KEY(`id`)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

3. 插入数据

func InsertUser(name string,age int) {
	sqlStr := "insert into user(name, age) values (?,?)"
	ret, err := DBName.Exec(sqlStr, name,age)
	if err != nil {
		fmt.Printf("insert failed, err:%v\n", err)
		return
	}
	theID, err := ret.LastInsertId() // 新插入数据的id
	if err != nil {
		fmt.Printf("get lastinsert ID failed, err:%v\n", err)
		return
	}
	fmt.Printf("insert success, the id is %d.\n", theID)
}

注意sql注入：我们这里虽然要往函数里传入变量，但是这个变量不要从代码外部传入，避免被传入拼接字串，造成sql注入。

• ID是自增长的，因此可以写：

func InsertUser(name string,age int) {
	sqlStr := "insert into user(name, age) values (?,?)"
	_, err := DBName.Exec(sqlStr, name,age)
	if err != nil {
		fmt.Printf("insert failed, err:%v\n", err)
		return
	}
	//theID, err2 := ret.LastInsertId() // 新插入数据的id
	//if err2 != nil {
	//	fmt.Printf("get lastinsert ID failed, err:%v\n", err)
	//	return
	//}
	fmt.Printf("insert success, the name is %s.\n", name)
}

4. 查询数据

4.1 单行查询

type userTab struct {
	Id int `json:"id"`
	Name string `json:"name"`
	Age int `json:"age"`
}

func QueryUser(selectId int) userTab{
	sqlStr := "select id, name, age from user where id=?"
	var userInfor userTab
	// 非常重要：确保QueryRow之后调用Scan方法，否则持有的数据库链接不会被释放
	row := DBName.QueryRow(sqlStr, selectId)
	err := row.Scan(&userInfor.Id, &userInfor.Name, &userInfor.Age)
	if err != nil {
		fmt.Printf("scan failed, err:%v\n", err)
	}
	return userInfor
}

4.2 多行查询

func QueryUsers() []userTab{
	sqlStr := "select id, name, age from user where id > ?"
	rows, err := DBName.Query(sqlStr, 0)
	if err != nil {
		fmt.Printf("query failed, err:%v\n", err)
	}
	// 非常重要：关闭rows释放持有的数据库链接
	defer rows.Close()
	var usersInfo []userTab
	// 循环读取结果集中的数据
	for rows.Next() {
		var userInfo userTab
		err := rows.Scan(&userInfo.Id, &userInfo.Name, &userInfo.Age)
		if err != nil {
			fmt.Printf("scan failed, err:%v\n", err)
		}
		usersInfo = append(usersInfo,userInfo)
		//fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)
	}
	return usersInfo
}

用gin框架写了一个接口查询结果如下：

5. 修改数据

func ModifyUserTab() {
	sqlStr := "update user set age=? where id = ?"
	ret, err := DBName.Exec(sqlStr, 20, 3)
	if err != nil {
		fmt.Printf("update failed, err:%v\n", err)
		return
	}
	n, err2 := ret.RowsAffected() //number of rows
	if err2 != nil {
		fmt.Printf("get RowsAffected failed, err:%v\n", err)
		return
	}
	fmt.Printf("update success, affected rows:%d\n", n)
}

6. 删除数据

func DeleteUserTab() {
    sqlStr := "delete from user where id = ?"
    ret, err := db.Exec(sqlStr, 3)
    if err != nil {
        fmt.Printf("delete failed, err:%v\n", err)
        return
    }
    n, err := ret.RowsAffected() // 操作影响的行数
    if err != nil {
        fmt.Printf("get RowsAffected failed, err:%v\n", err)
        return
    }
    fmt.Printf("delete success, affected rows:%d\n", n)
}