由于项目需要,研究了一下golang操作数据库的基本方式.操作环境在macos上.
1.首先连接数据库 mysql -u root -p
2.选择数据库 use test
3.创建一个我们需要测试的user表
CREATE TABLE IF NOT EXISTS `user` (`uid` SERIAL PRIMARY KEY, `name` VARCHAR(20) NOT NULL, `password` VARCHAR(20) NOT NULL) ENGINE=`innodb`, CHARACTER SET=utf8;
4.插入两条初始数据
INSERT INTO `user`(`name`,`password`) VALUES('nick', 'nick'),('jacky', 'jacky')
随后使用goland创建项目:例子如下
package main
import (
"database/sql"
_ "github.com/go-sql-driver/mysql"
"fmt"
)
type DbWorker struct {
//mysql data source name
Dsn string
}
//显示数据库
/*DB中执行SQL通过Exec和Query方法,查询操作是通过Query完成,它会返回一个sql.Rows的结果集,包含一个游标用来遍历查询结果;
Exec方法返回的是sql.Result对象,用于检测操作结果,及被影响记录数*/
func ShowData(db *sql.DB){
rows, err := db.Query("SELECT * FROM user")
//fmt.Println("rows:",*rows)
if err != nil {
fmt.Println("fetech data failed:", err.Error())
return
}
for rows.Next() {
var uid int
var name, password string
rows.Scan(&uid, &name, &password)
fmt.Println("uid:", uid, "name:", name, "password:", password)
}
}
//插入一条数据
func InserData(db *sql.DB){
result, err := db.Exec("INSERT INTO `user`(`name`,`password`) VALUES('rocky', '112211')")
if err != nil {
fmt.Println("insert data failed:", err.Error())
return
}
id, err := result.LastInsertId()
if err != nil {
fmt.Println("fetch last insert id failed:", err.Error())
return
}
fmt.Println("insert new record", id)
}
//更新数据库字段内容
func UpdataData(db *sql.DB){
result, err := db.Exec("UPDATE `user` SET `password`=? WHERE `name`=?", "1231", "rocky")
if err != nil {
fmt.Println("update data failed:", err.Error())
return
}
num, err := result.RowsAffected()
if err != nil {
fmt.Println("fetch row affected failed:", err.Error())
return
}
fmt.Println("update recors number", num)
}
//删除一条数据
func DeleteData(db *sql.DB){
result, err := db.Exec("DELETE FROM `user` WHERE `name`=? and uid=4", "rocky")
if err != nil {
fmt.Println("delete data failed:", err.Error())
return
}
num, err := result.RowsAffected()
if err != nil {
fmt.Println("fetch row affected failed:", err.Error())
return
}
fmt.Println("delete record number", num)
}
func main() {
dbw := DbWorker{
Dsn: "root:root@tcp(127.0.0.1:3306)/test?charset=utf8",
}
db, err := sql.Open("mysql", //打开数据库并且连接数据库.
dbw.Dsn)
if err != nil {
panic(err)
return
}
ShowData(db)
//UpdataData(db)
DeleteData(db)
ShowData(db)
//InserData(db)
// ShowData(db)
defer db.Close()
}
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)