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)
}