18 数据库操作

GO操作MySQL

1.下载驱动

go get -u github.com/go-sql-driver/mysql

2.数据库操作

package main

import (
	"database/sql"
	"fmt"
	"os"

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

//表sys_config
type table struct {
	variable string
	value    string
	set_time string
	set_by   string
}

func checkErr(err error) {
	if err != nil {
		fmt.Println("ERROR:", err)
		os.Exit(1)
	}
}

//go get -u github.com/go-sql-driver/mysql
func main() {
	dsn := "root:123456@tcp(127.0.0.1:3306)/sys"
	db, err := sql.Open("mysql", dsn) //不校验用户密码，只校验dsn格式
	checkErr(err)
	defer db.Close()

	db.SetMaxOpenConns(10)

	err = db.Ping() //尝试连接数据库
	checkErr(err)

	fmt.Println("数据库连接成功！")

	//1.增加记录
	fmt.Println("新增记录：")
	sqlStr := `INSERT INTO sys_config(variable, value, set_time, set_by) VALUES("TEST_ROW", "OFF", "2022-03-22 21:44:23", "");`
	_, err = db.Exec(sqlStr)
	checkErr(err)
	fmt.Println("新增记录成功！")

	//2.查询记录
	fmt.Println("查询记录：")

	sqlStr = `SELECT * FROM sys_config WHERE value=?;`
	rows, err := db.Query(sqlStr, "OFF")
	checkErr(err)

	var tmpVar table
	for rows.Next() {
		rows.Scan(&tmpVar.variable, &tmpVar.value, &tmpVar.set_time, &tmpVar.set_by)
		fmt.Printf("%#v\n", tmpVar)
	}

	err = rows.Err() // get any error encountered during iteration
	if err != nil {
		fmt.Println("ERROR:", err)
	}

	//3.删除记录
	fmt.Println("删除记录：")
	sqlStr = `DELETE FROM sys_config WHERE variable=?;`
	_, err = db.Exec(sqlStr, "TEST_ROW")
	checkErr(err)
	fmt.Println("删除记录成功！")
}

输出结果：

数据库连接成功！
新增记录：
新增记录成功！
查询记录：
main.table{variable:"diagnostics.allow_i_s_tables", value:"OFF", set_time:"2022-03-22 21:44:23", set_by:""}
main.table{variable:"diagnostics.include_raw", value:"OFF", set_time:"2022-03-22 21:44:23", set_by:""}
main.table{variable:"TEST_ROW", value:"OFF", set_time:"2022-03-22 21:44:23", set_by:""}
删除记录：
删除记录成功！

3.MySQL预处理

package main

import (
	"database/sql"
	"fmt"
	"os"

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

//表sys_config
type table struct {
	variable string
	value    string
	set_time string
	set_by   string
}

func checkErr(err error) {
	if err != nil {
		fmt.Println("ERROR:", err)
		os.Exit(1)
	}
}

//go get -u github.com/go-sql-driver/mysql
func main() {
	dsn := "root:123456@tcp(127.0.0.1:3306)/sys"
	db, err := sql.Open("mysql", dsn) //不校验用户密码，只校验dsn格式
	checkErr(err)
	defer db.Close()

	db.SetMaxOpenConns(10)

	err = db.Ping() //尝试连接数据库
	checkErr(err)

	fmt.Println("数据库连接成功！")

	//查询记录
	sqlStr := `SELECT * FROM sys_config WHERE value=?;`
	stmt, err := db.Prepare(sqlStr)
	checkErr(err)
	defer stmt.Close()

	rows, err := stmt.Query("OFF")
	checkErr(err)
	var tmpVar table
	for rows.Next() {
		rows.Scan(&tmpVar.variable, &tmpVar.value, &tmpVar.set_time, &tmpVar.set_by)
		fmt.Printf("%#v\n", tmpVar)
	}

	err = rows.Err() // get any error encountered during iteration
	if err != nil {
		fmt.Println("ERROR:", err)
	}
}

输出结果：

数据库连接成功！
main.table{variable:"statement_performance_analyzer.limit", value:"100", set_time:"2022-03-22 21:44:23", set_by:""}

4.事务

package main

import (
	"database/sql"
	"fmt"
	"os"

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

//表sys_config
type table struct {
	variable string
	value    string
	set_time string
	set_by   string
}

func checkErr(err error) {
	if err != nil {
		fmt.Println("ERROR:", err)
		os.Exit(1)
	}
}

//go get -u github.com/go-sql-driver/mysql
func main() {

	dsn := "root:123456@tcp(127.0.0.1:3306)/sys"
	db, err := sql.Open("mysql", dsn) //不校验用户密码，只校验dsn格式
	checkErr(err)
	defer db.Close()

	db.SetMaxOpenConns(10)

	err = db.Ping() //尝试连接数据库
	checkErr(err)

	fmt.Println("数据库连接成功！")

	tx, err := db.Begin() //开启事务
	checkErr(err)
	//1.增加记录
	sqlStr := `INSERT INTO sys_config(variable, value, set_time, set_by) VALUES("TEST_ROW", "OFF", "2022-03-22 21:44:23", "");`
	_, err = tx.Exec(sqlStr)
	if err != nil {
		fmt.Println("ERROR:", err)
		tx.Rollback() //回滚
	}

	//2.删除记录
	sqlStr = `DELETE FROM sys_config WHERE variable=?;`
	_, err = tx.Exec(sqlStr, "TEST_ROW")
	if err != nil {
		fmt.Println("ERROR:", err)
		tx.Rollback() //回滚
	}

	err = tx.Commit() //提交事务
	if err != nil {
		fmt.Println("ERROR:", err)
		tx.Rollback() //回滚
	} else {
		fmt.Println("提交事务成功！")
	}

}

GO操作Redis

1.下载驱动

go get -u github.com/go-redis/redis

2.Redis操作

package main

import (
	"context"
	"fmt"
	"os"

	"github.com/go-redis/redis"
)

var ctx = context.Background()
var redisdb *redis.Client

func checkErr(err error) {
	if err != nil {
		fmt.Println("ERROR:", err)
		os.Exit(1)
	}
}

//go get -u github.com/go-redis/redis
func main() {
	redisdb = redis.NewClient(&redis.Options{
		Addr:     "127.0.0.1:6379",
		Password: "",
		DB:       0,
	})

	_, err := redisdb.Ping(ctx).Result()
	checkErr(err)
	fmt.Println("Redis 数据库连接成功！")

	err = redisdb.Set(ctx, "score", 99, 0).Err()
	checkErr(err)

	val, err := redisdb.Get(ctx, "score").Result()
	checkErr(err)
	fmt.Println("score:", val) //score: 99
}

实时分布式内存消息队列NSQ

1.下载驱动

go get -u github.com/nsqio/go-nsq

2.生产者

package main

import (
	"bufio"
	"fmt"
	"os"
	"strings"

	"github.com/nsqio/go-nsq"
)

var producer *nsq.Producer

func checkErr(err error) {
	if err != nil {
		fmt.Println("ERROR:", err)
		os.Exit(1)
	}
}

// go get -u github.com/nsqio/go-nsq
func main() {
	config := nsq.NewConfig()
	nsqAddress := "127.0.0.1:4150"
	producer, err := nsq.NewProducer(nsqAddress, config)
	checkErr(err)

	reader := bufio.NewReader(os.Stdin)
	for {
		fmt.Printf("请输入：")
		data, err := reader.ReadString('\n')
		checkErr(err)

		data = strings.TrimSpace(data)
		if strings.ToUpper(data) == "Q" {
			break
		}

		err = producer.Publish("topic_demo", []byte(data))
		checkErr(err)
	}
}

3.消费者

package main

import (
	"fmt"
	"os"
	"os/signal"
	"syscall"
	"time"

	"github.com/nsqio/go-nsq"
)

//消费者类型
type MyHandler struct {
	Title string
}

//HandleMessage 处理消息方法
func (m *MyHandler) HandleMessage(msg *nsq.Message) (err error) {
	fmt.Printf("消费者%s从%v接收消息%v\n", m.Title, msg.NSQDAddress, string(msg.Body))
	return
}

func checkErr(err error) {
	if err != nil {
		fmt.Println("ERROR:", err)
		os.Exit(1)
	}
}

// go get -u github.com/nsqio/go-nsq
func main() {
	config := nsq.NewConfig()
	config.LookupdPollInterval = 15 * time.Second

	consumer, err := nsq.NewConsumer("topic_demo", "first", config)
	checkErr(err)

	testConsumer := &MyHandler{
		Title: "消费者",
	}

	consumer.AddHandler(testConsumer)

	address := "127.0.0.1:4161"
	err = consumer.ConnectToNSQLookupd(address)
	checkErr(err)

	ch := make(chan os.Signal)
	signal.Notify(ch, syscall.SIGINT)
	<-ch
}

输出结果：

2022/03/24 22:17:28 INF    1 [topic_demo/first] querying nsqlookupd http://127.0.0.1:4161/lookup?topic=topic_demo
2022/03/24 22:17:28 INF    1 [topic_demo/first] (127.0.0.1:4150) connecting to nsqd
消费者消费者从127.0.0.1:4150接收消息123
消费者消费者从127.0.0.1:4150接收消息456
2022/03/24 22:17:47 INF    1 [topic_demo/first] querying nsqlookupd http://127.0.0.1:4161/lookup?topic=topic_demo
消费者消费者从127.0.0.1:4150接收消息789