teen_patti/src/server/db/mysql/mysqlmgr.go

package mysqlmgr

import (
	"database/sql"
	"time"

	"github.com/name5566/leaf/log"

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

var (
	db *sql.DB
)

func init() {
	Connect()
}

// Connect 初始化数据库连接
func Connect() {
	var err error
	db, err = sql.Open("mysql", "root:password@tcp(localhost:3306)/teen_patti?charset=utf8mb4&parseTime=True")
	if err != nil {
		panic(err)
	}

	// 设置连接池
	db.SetMaxIdleConns(10)           // 最大空闲连接数
	db.SetMaxOpenConns(100)          // 最大打开连接数
	db.SetConnMaxLifetime(time.Hour) // 连接最大生命周期
}

// Insert 插入数据
func Insert(query string, args ...interface{}) (int64, error) {
	result, err := db.Exec(query, args...)
	if err != nil {
		return 0, err
	}
	return result.LastInsertId()
}

// Update 更新数据
func Update(query string, args ...interface{}) (int64, error) {
	result, err := db.Exec(query, args...)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected()
}

// Delete 删除数据
func Delete(query string, args ...interface{}) (int64, error) {
	result, err := db.Exec(query, args...)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected()
}

// QueryRow 查询单行
func QueryRow(query string, args ...interface{}) *sql.Row {
	return db.QueryRow(query, args...)
}

// Query 查询多行
func Query(query string, args ...interface{}) (*sql.Rows, error) {
	return db.Query(query, args...)
}

// 测试函数
func test() {
	// 创建用户表
	createTable := `
	CREATE TABLE IF NOT EXISTS users (
		id INT AUTO_INCREMENT,
		name VARCHAR(255) NOT NULL,
		balance DECIMAL(10,2) DEFAULT 0,
		created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		PRIMARY KEY (id)
	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
	`
	_, err := db.Exec(createTable)
	if err != nil {
		log.Debug("Create table error:", err)
		return
	}

	// 插入数据
	id, err := Insert("INSERT INTO users (name, balance) VALUES (?, ?)", "player1", 1000.00)
	if err != nil {
		log.Debug("Insert error:", err)
		return
	}
	log.Debug("Inserted user ID: %d\n", id)

	// 更新数据
	affected, err := Update("UPDATE users SET balance = balance + ? WHERE id = ?", 100.00, id)
	if err != nil {
		log.Debug("Update error:", err)
		return
	}
	log.Debug("Updated %d rows\n", affected)

	// 查询单行
	var name string
	var balance float64
	err = QueryRow("SELECT name, balance FROM users WHERE id = ?", id).Scan(&name, &balance)
	if err != nil {
		log.Debug("Query error:", err)
		return
	}
	log.Debug("User: %s, Balance: %.2f\n", name, balance)

	// 查询多行
	rows, err := Query("SELECT id, name, balance FROM users")
	if err != nil {
		log.Debug("Query error:", err)
		return
	}
	defer rows.Close()

	for rows.Next() {
		var id int
		var name string
		var balance float64
		err := rows.Scan(&id, &name, &balance)
		if err != nil {
			log.Debug("Scan error:", err)
			continue
		}
		log.Debug("ID: %d, Name: %s, Balance: %.2f\n", id, name, balance)
	}
}