go操作mysql

1，go.mod配置

module github.com/my/repo

go 1.21.3

require (
	github.com/go-sql-driver/mysql v1.6.0
	github.com/redis/go-redis/v9 v9.2.1
)

require (
	github.com/cespare/xxhash/v2 v2.2.0 // indirect
	github.com/dgryski/go-rendezvous v0.0.0-20200823014737-9f7001d12a5f // indirect
)
1
2
3
4
5
6
7
8
9
10
11
12
13

2，mysql.go

package main

import (
	"database/sql"
	"fmt"
	"github.com/go-sql-driver/mysql"
	"log"

	_ "github.com/go-sql-driver/mysql"
)

func main() {
	// 配置MySQL方言
	mysqlConfig := &mysql.Config{
		User:                 "root",
		Passwd:               "pwd",
		Net:                  "tcp",
		Addr:                 "127.0.0.1:3306",
		DBName:               "go_test",
		AllowNativePasswords: true,
		ParseTime:            true,
	}

	// 连接数据库
	db, err := sql.Open("mysql", mysqlConfig.FormatDSN())
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	// 测试连接是否成功
	err = db.Ping()
	if err != nil {
		log.Fatal(err)
	}
	fmt.Println("连接成功！")

	// 查询表是否存在
	query := "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = ? AND table_name = ?"
	var count int
	err = db.QueryRow(query, "go_test", "table_name").Scan(&count)
	if err != nil {
		log.Fatal(err)
	}

	if count > 0 {
		fmt.Println("表存在")
	} else {
		fmt.Println("表不存在")
		// 创建表
		createTableQuery := `
		CREATE TABLE table_name (
			id INT PRIMARY KEY AUTO_INCREMENT,
			name VARCHAR(50)
		);
	`
		_, err = db.Exec(createTableQuery)
		if err != nil {
			log.Fatal(err)
		}

		fmt.Println("表创建成功！")
	}

	// 查询数据
	rows, err := db.Query("SELECT * FROM table_name")
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()

	// 遍历查询结果
	for rows.Next() {
		var id int
		var name string
		err := rows.Scan(&id, &name)
		if err != nil {
			log.Fatal(err)
		}
		fmt.Println("ID:", id, "Name:", name)
	}

	// 插入数据
	result, err := db.Exec("INSERT INTO table_name (name) VALUES (?)", "John")
	if err != nil {
		log.Fatal(err)
	}
	lastInsertID, err := result.LastInsertId()
	if err != nil {
		log.Fatal(err)
	}
	fmt.Println("Last Insert ID:", lastInsertID)

	// 更新数据
	result, err = db.Exec("UPDATE table_name SET name = ? WHERE id = ?", "Jane", 1)
	if err != nil {
		log.Fatal(err)
	}
	rowsAffected, err := result.RowsAffected()
	if err != nil {
		log.Fatal(err)
	}
	fmt.Println("Rows Affected:", rowsAffected)
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105

3,提前建好go_test数据库，然后运行