MySQL数据库

最新推荐文章于 2022-09-09 22:48:42 发布

熊小憨

最新推荐文章于 2022-09-09 22:48:42 发布

阅读量403

点赞数

分类专栏： # GolangWeb开发文章标签： go sql mysql

本文链接：https://blog.csdn.net/suma110/article/details/124453849

版权

GolangWeb开发专栏收录该内容

11 篇文章 0 订阅

订阅专栏

安装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")
}