安装mysql驱动包
go get -u github.com/go-sql-driver/mysql
连接mysql数据库
db, err := sql.Open("mysql", "root:123456@(127.0.0.1:3306)/golang?parseTime=true")
if err != nil {
log.Fatal(err)
}
if err := db.Ping(); err != nil {
log.Fatal(err)
}
sql.Open
第一个参数告知使用的数据库驱动,第二个参数分别包含连接数据库所需的用户名、密码、ip、端口号、数据库名称。
创建数据表
func create_table_user(db *sql.DB) {
query := `
CREATE TABLE if not exists users (
id INT AUTO_INCREMENT,
username TEXT NOT NULL,
password TEXT NOT NULL,
created_at DATETIME,
PRIMARY KEY (id)
);`
if _, err := db.Exec(query); err != nil {
log.Fatal(err)
}
println("create table user completed")
}
数据表插入数据
func insert_user(db *sql.DB) int64 {
username := "johndoe"
password := "secret"
createdAt := time.Now()
result, err := db.Exec(`INSERT INTO users (username, password, created_at) VALUES (?, ?, ?)`, username, password, createdAt)
if err != nil {
log.Fatal(err)
}
id, err := result.LastInsertId()
if err != nil {
log.Fatal(err)
}
fmt.Println("insert user completed:", id)
return id
}
查询单条数据
func query_user(db *sql.DB, query_id int64) {
var (
id int
username string
password string
createdAt time.Time
)
query := "SELECT id, username, password, created_at FROM users WHERE id = ?"
if err := db.QueryRow(query, query_id).Scan(&id, &username, &password, &createdAt); err != nil {
log.Fatal(err)
}
fmt.Println("query user completed:", id, username, password, createdAt)
}
查询多条数据
func query_all_users(db *sql.DB) {
type user struct {
id int
username string
password string
createdAt time.Time
}
rows, err := db.Query(`SELECT id, username, password, created_at FROM users`)
if err != nil {
log.Fatal(err)
}
defer rows.Close()
var users []user
for rows.Next() {
var u user
err := rows.Scan(&u.id, &u.username, &u.password, &u.createdAt)
if err != nil {
log.Fatal(err)
}
users = append(users, u)
}
if err := rows.Err(); err != nil {
log.Fatal(err)
}
println("query all users completed:")
fmt.Printf("%#v\n", users)
}
删除数据
func delete_user(db *sql.DB, query_id int64) {
_, err := db.Exec(`DELETE FROM users WHERE id = ?`, query_id)
if err != nil {
log.Fatal(err)
}
println("delete user completed:", query_id)
}
清空数据表
func delete_all_users(db *sql.DB) {
_, err := db.Exec(`truncate table users`)
if err != nil {
log.Fatal(err)
}
println("delete all users completed")
}
完整源码
package main
import (
"database/sql"
"fmt"
"log"
"time"
_ "github.com/go-sql-driver/mysql"
)
func main() {
db, err := sql.Open("mysql", "root:123456@(127.0.0.1:3306)/golang?parseTime=true")
if err != nil {
log.Fatal(err)
}
if err := db.Ping(); err != nil {
log.Fatal(err)
}
create_table_user(db)
id := insert_user(db)
query_user(db, id)
query_all_users(db)
delete_user(db, id)
delete_all_users(db)
}
func create_table_user(db *sql.DB) {
query := `
CREATE TABLE if not exists users (
id INT AUTO_INCREMENT,
username TEXT NOT NULL,
password TEXT NOT NULL,
created_at DATETIME,
PRIMARY KEY (id)
);`
if _, err := db.Exec(query); err != nil {
log.Fatal(err)
}
println("create table user completed")
}
func insert_user(db *sql.DB) int64 {
username := "johndoe"
password := "secret"
createdAt := time.Now()
result, err := db.Exec(`INSERT INTO users (username, password, created_at) VALUES (?, ?, ?)`, username, password, createdAt)
if err != nil {
log.Fatal(err)
}
id, err := result.LastInsertId()
if err != nil {
log.Fatal(err)
}
fmt.Println("insert user completed:", id)
return id
}
func query_user(db *sql.DB, query_id int64) {
var (
id int
username string
password string
createdAt time.Time
)
query := "SELECT id, username, password, created_at FROM users WHERE id = ?"
if err := db.QueryRow(query, query_id).Scan(&id, &username, &password, &createdAt); err != nil {
log.Fatal(err)
}
fmt.Println("query user completed:", id, username, password, createdAt)
}
func query_all_users(db *sql.DB) {
type user struct {
id int
username string
password string
createdAt time.Time
}
rows, err := db.Query(`SELECT id, username, password, created_at FROM users`)
if err != nil {
log.Fatal(err)
}
defer rows.Close()
var users []user
for rows.Next() {
var u user
err := rows.Scan(&u.id, &u.username, &u.password, &u.createdAt)
if err != nil {
log.Fatal(err)
}
users = append(users, u)
}
if err := rows.Err(); err != nil {
log.Fatal(err)
}
println("query all users completed:")
fmt.Printf("%#v\n", users)
}
func delete_user(db *sql.DB, query_id int64) {
_, err := db.Exec(`DELETE FROM users WHERE id = ?`, query_id)
if err != nil {
log.Fatal(err)
}
println("delete user completed:", query_id)
}
func delete_all_users(db *sql.DB) {
_, err := db.Exec(`truncate table users`)
if err != nil {
log.Fatal(err)
}
println("delete all users completed")
}