Go 连接MySQL数据库的使用
在进行开发之前首先要使用git下载mysql的驱动,github上面的mysql驱动有好几个,我在这里选择的https://github.com/go-sql-driver/mysql,
执行命令:
go get -u github.com/go-sql-driver/mysql
我的数据表信息如下:
+----------+------+------+
| name | age | sex |
+----------+------+------+
| xiaoming | 28 | 1 |
+----------+------+------+
1 row in set (0.00 sec)
代码如下(插入,查询,更新,删除):
package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
"log"
)
var DB *sql.DB
func init() {
//建立链接db, err := sql.Open("mysql", "user:password@/dbname")
conn, err := sql.Open("mysql","root:123456@tcp(localhost:3306)/irocn")
if err != nil {
log.Fatal("Open Connection failed:", err.Error())
}
//defer conn.Close()
err = conn.Ping()
if err != nil {
log.Fatal("Ping mysql failed, err:%s", err.Error()) // proper error handling instead of panic in your app
}
DB = conn
}
func main(){
//插入
InsertUser()
//查询
QueryUser()
fmt.Println("====================")
//更新
UpdateUser()
//查询
QueryUser()
fmt.Println("====================")
//删除
DeleteUser()
//查询
QueryUser()
fmt.Println("====================")
}
//QueryDB:查询users表中的数据
func QueryUser() {
rows, err := DB.Query("select * from users;")
if err != nil{
log.Fatal("query failed, err:%s", err.Error())
}
for rows.Next() {
var name string
var age int32
var sex int32
err := rows.Scan(&name, &age, &sex)
if err != nil{
log.Fatal(err.Error())
}
fmt.Println(name, age, sex)
}
}
//InsertUser:向users表中插入一条数据
func InsertUser() {
stmt, err := DB.Prepare("insert users set name=?,age=?,sex=?")
res, err := stmt.Exec("张燕", 20, 0)
id, err := res.LastInsertId()
if err != nil {
fmt.Println("插入数据失败")
} else {
fmt.Println("插入数据成功:", id)
}
}
//UpdataUser:更新users表中的数据
func UpdateUser() {
stmt, err := DB.Prepare("update users set name=? where age=?")
if err != nil{
log.Fatal(err)
}
res, err := stmt.Exec("王五", 20)
affect, err := res.RowsAffected()
fmt.Println("更新数据:", affect)
}
//DeleteUser:删除users表中的数据
func DeleteUser() {
stmt, err := DB.Prepare("delete from users where name=?")
if err != nil{
log.Fatal("Delete users",err)
}
res, err := stmt.Exec("王五")
affect, err := res.RowsAffected()
fmt.Println("删除数据:", affect)
}