go操作数据库（插入、更新、删除、查找、事务）

package main
 
import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"log"
	"time"
)
 
func init() {
	//sql.Register("mysql", &mysql.MySQLDriver{})
}
func main() {
	db, err := sql.Open("mysql", "root:root@tcp(localhost:3306)/hello?charset=utf8&parseTime=True")
	if err != nil {
		log.Fatal(err)
		return
	}
	defer db.Close()
	//InsterLikeInfo(db)
	//UpdateLikeInfo(db)
	FindLikeInfo(db, 1)
	DeleteLikeInfo(db, 9)
	InsertANDUpdateLikeInfo(db)
}
 
type LikeInfo struct {
	ID        int       `gorm:"primary_key"`
	Ip        string    `gorm:"type:varchar(20);not null;index:ip_idx"`
	Ua        string    `gorm:"type:varchar(256);not null;"`
	Title     string    `gorm:"Title"`
	Hash      int       `gorm:"Hash" column:"Hash"`
	CreatedAt time.Time `gorm:"CreatedAt" column:"CreatedAt"`
}
 
//插入
func InsterLikeInfo(db *sql.DB) {
	var like = &LikeInfo{
		Ip:        "127.0.0.1",
		Ua:        "ua",
		Title:     "like me",
		Hash:      120,
		CreatedAt: time.Now(),
	}
	insertSQL := `	INSERT INTO LikeInfo
	            (Ip, Ua, Title, Hash,CreatedAt)
				VALUES
				(?, ?, ?, ?, ?);`
	stmt, err := db.Prepare(insertSQL)
	if err != nil {
		return
	}
	defer stmt.Close()
	result, err := stmt.Exec(like.Ip, like.Ua, like.Title, like.Hash, like.CreatedAt)
	if err != nil {
		return
	}
	id, err := result.LastInsertId()
	if err != nil {
		return
	}
	fmt.Println("返回id:", id)
}
 
//更新
func UpdateLikeInfo(db *sql.DB) {
	var like = &LikeInfo{
		ID:        1,
		Ip:        "127.0.0.1",
		Ua:        "newUa",
		Title:     "like you",
		Hash:      111,
		CreatedAt: time.Now(),
	}
	updateSQL := `UPDATE LikeInfo
	SET Ip = ?, Ua=?, Title = ?, Hash = ?, CreatedAt = ? 
	WHERE ID = ?;`
	_, err := db.Exec(updateSQL, like.Ip, like.Ua, like.Title, like.Hash, like.CreatedAt, like.ID)
	if err != nil {
		return
	}
	//fmt.Println("更新结果：", result)
}
 
//查找
func FindLikeInfo(db *sql.DB, id int) {
	var like LikeInfo
	querySQL := `SELECT ID,Ua,IP,Hash,Title,CreatedAt FROM LikeInfo where ID = ?;`
	err := db.QueryRow(querySQL, id).Scan(&like.ID, &like.Ua, &like.Ip, &like.Hash, &like.Title, &like.CreatedAt)
	if err != nil {
		return
	}
	fmt.Printf("结果：%#+v\n", like)
}
 
//删除
func DeleteLikeInfo(db *sql.DB, id int) {
	deleteSQl := `delete from LikeInfo where ID= ?;`
	_, err := db.Exec(deleteSQl, id)
	if err != nil {
		fmt.Println(err)
		return
	}
}
 
//事务
func InsertANDUpdateLikeInfo(db *sql.DB) {
	var like = &LikeInfo{
		ID:        1,
		Ip:        "127.0.0.1",
		Ua:        "newUa",
		Title:     "like you",
		Hash:      111,
		CreatedAt: time.Now(),
	}
	tx, err := db.Begin()
	if err != nil {
		if tx == nil {
			tx.Rollback()
			fmt.Printf("开启事务失败，错误：%v\n", err)
			return
		}
	}
	insertSQL := `	INSERT INTO LikeInfo
	            (Ip, Ua, Title, Hash,CreatedAt)
				VALUES
				(?, ?, ?, ?, ?);`
	stmt, err := db.Prepare(insertSQL)
	if err != nil {
		tx.Rollback()
		fmt.Printf("执行insertSQL失败，错误：%v\n", err)
		return
	}
	defer stmt.Close()
	result, err := stmt.Exec(like.Ip, like.Ua, like.Title, like.Hash, like.CreatedAt)
	if err != nil {
		tx.Rollback()
		fmt.Printf("执行insertSQL失败，错误：%v\n", err)
		return
	}
	id, err := result.LastInsertId()
	if err != nil {
		tx.Rollback()
		fmt.Printf("执行insertSQL失败，错误：%v\n", err)
		return
	}
	var like1 = &LikeInfo{
		ID:        int(id),
		Ip:        "127.0.0.1",
		Ua:        "newUa",
		Title:     "like you",
		Hash:      111,
		CreatedAt: time.Now(),
	}
	updateSQL := `UPDATE LikeInfo
	SET Ip = ?, Ua=?, Title = ?, Hash = ?, CreatedAt = ? 
	WHERE ID = ?;`
	_, err = db.Exec(updateSQL, like1.Ip, like1.Ua, like1.Title, like1.Hash, like1.CreatedAt, like1.ID)
	if err != nil {
		tx.Rollback()
		fmt.Printf("执行updateSQL失败，错误：%v\n", err)
		return
	}
	//提交事务
	err = tx.Commit()
	if err != nil {
		tx.Rollback()
		fmt.Printf("提交失败，错误：%v\n", err)
		return
	}
	fmt.Println("事务执行成功")
}

sql:

/*
Navicat MySQL Data Transfer
Source Server         : localhost
Source Server Version : 50733
Source Host           : localhost:3306
Source Database       : hello
Target Server Type    : MYSQL
Target Server Version : 50733
File Encoding         : 65001
Date: 2022-07-29 15:57:29
*/
 
SET FOREIGN_KEY_CHECKS=0;
 
-- ----------------------------
-- Table structure for likeinfo
-- ----------------------------
DROP TABLE IF EXISTS `likeinfo`;
CREATE TABLE `likeinfo` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Ip` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Ua` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Title` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Hash` int(11) DEFAULT NULL,
  `CreatedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

读取配置文件：

package loadconfigs
 
import (
	"encoding/json"
	"io/ioutil"
	"os"
)
 
//Conf 全局配置
var Conf *Config
 
//Redis 缓存配置
type Redis struct {
	Host string `json:"host"`
}
 
//Db 数据库配置
type Db struct {
	Type         string `json:"type"`
	Write        string `json:"write"`
	Read         string `json:"read"`
	MaxOpenConns int    `json:"maxOpenConns"`
	IdleConns    int    `json:"idleConns"`
}
 
//Config 全局配置文件
type Config struct {
	Db    Db    `json:"db"`
	Redis Redis `json:"redis"`
}
 
func fileExists(filename string) bool {
	info, err := os.Stat(filename)
	if os.IsNotExist(err) {
		return false
	}
	return !info.IsDir()
}
 
//LoadConfig 加载配置
func LoadConfig() error {
	var fileData []byte
	var err error
	if fileExists("app.json") { //先找当前目录
		fileData, err = ioutil.ReadFile("app.json")
		if err != nil {
			return err
		}
	} else {
		fileData, err = ioutil.ReadFile("lib/configs/app.json")
		if err != nil {
			return err
		}
	}
	Conf = &Config{}
	err = json.Unmarshal(fileData, Conf)
	return err
}