1.mysql -uroot -p
2.输入密码 root
3.创建一个数据库 :create database go_test
4.使用改数据库:use go_test
5.创建一个表并插入一些数据:
CREATE TABLE user (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
username VARCHAR (20),
PASSWORD VARCHAR (20)
)
INSERT INTO user_tbl (username, PASSWORD) VALUES ("tom", "123456")
INSERT INTO user_tbl (username, PASSWORD) VALUES ("Bob", "999999")
6.查看是否成功:select * from user;
成功
package main
import (
"database/sql"
"fmt"
"time"
_ "github.com/go-sql-driver/mysql"
)
var db *sql.DB
func initDB() (err error) {
dsn := "root:root@tcp(127.0.0.1:3306)/go_test?charset=utf8mb4&parseTime=True"
db, err = sql.Open("mysql", dsn)
fmt.Printf("db: %v\n", db)
if err != nil {
fmt.Printf("err: %v\n", err)
return err
}
// 尝试去链接
err2 := db.Ping()
if err2 != nil {
fmt.Printf("err2: %v\n", err2)
return err2
}
return nil
}
func Test() {
d, err := sql.Open("mysql", "root:root@/go_test")
if err != nil {
fmt.Printf("err: %v\n", err)
}
d.SetConnMaxLifetime(time.Minute * 3)
d.SetMaxOpenConns(10)
d.SetMaxIdleConns(10)
fmt.Printf("d: %v\n", d)
}
// 尝试连接
func getCon() {
err := initDB()
if err != nil {
fmt.Printf("err: %v\n", err)
} else {
fmt.Printf("\"连接成功\": %v\n", "连接成功")
}
}
// 插入数据
func insertData() {
// SQL语句
sqlStr := `insert into user(username,password) vaues ("Bob","999999")`
ret, err := db.Exec(sqlStr)
if err != nil {
fmt.Printf("insert failed, err:%v\n", err)
return
}
// 新插入数据的ids
theID, err := ret.LastInsertId()
if err != nil {
fmt.Printf("get lastinsert ID failed, err:%v\n", err)
return
}
fmt.Printf("insert success, the id is %d.\n", theID)
}
// 查询一条用户数据
func queryRowDemo() {
// 查询的SQL 语句
sqlStr := "select username, password from user where id=?"
// 确保QueryRow之后调用Scan方法,否则持有的数据库链接不会被释放
var username, password string
err := db.QueryRow(sqlStr, 1).Scan(&username,&password)
if err != nil {
fmt.Printf("scan failed, err:%v\n", err)
return
}
fmt.Printf("username:%s password:%s\n",username, password)
}
// 删除语句
func delData() {
sql := "delete from user where username =?"
ret, err := db.Exec(sql, "tom")
if err != nil {
fmt.Printf("删除失败, err:%v\n", err)
return
}
rows, err := ret.RowsAffected()
if err != nil {
fmt.Printf("删除行失败, err:%v\n", err)
return
}
fmt.Printf("删除成功, 删除的行数: %d.\n", rows)
}
// 更新语句
func updateData() {
sql := "update user set username=?, password=? where username=?"
ret, err := db.Exec(sql, "tom", "123456", "Alice")
if err != nil {
fmt.Printf("更新失败, err:%v\n", err)
return
}
rows, err := ret.RowsAffected()
if err != nil {
fmt.Printf("更新行失败, err:%v\n", err)
return
}
fmt.Printf("更新成功, 更新的行数: %d.\n", rows)
}
func main() {
// Test()
getCon()
// insertData()
// queryRowDemo()
// delData()
updateData()
}