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
}