Go语言与MySQL：高效存储二进制IP地址到BINARY字段

package main

import (
    "database/sql"
    "fmt"
    "log"
    "net"

    _ "github.com/go-sql-driver/mysql" // 导入MySQL驱动
)

// setupDB 连接到数据库并创建表
func setupDB() *sql.DB {
    db, err := sql.Open("mysql", "user:password@tcp(127.0.0.1:3306)/testdb?parseTime=true")
    if err != nil {
        log.Fatalf("无法连接到数据库: %v", err)
    }

    // 确保数据库连接正常
    err = db.Ping()
    if err != nil {
        log.Fatalf("数据库连接失败: %v", err)
    }

    // 创建或清空表
    _, err = db.Exec(`
        CREATE TABLE IF NOT EXISTS AIPRangesBlocks (
            IPGRID INT PRIMARY KEY AUTO_INCREMENT,
            BinRangeStart BINARY(4) NOT NULL,
            BinRangeEnd BINARY(4) NOT NULL
        );
    `)
    if err != nil {
        log.Fatalf("无法创建表: %v", err)
    }

    fmt.Println("数据库连接成功，表已准备就绪。")
    return db
}

func main() {
    db := setupDB()
    defer db.Close()

    // 示例IP地址
    rangeStartStr := "192.168.1.1"
    rangeEndStr := "192.168.1.255"
    id := 1

    // 1. 解析IP地址并转换为IPv4字节切片
    startSlice := net.ParseIP(rangeStartStr).To4()
    if startSlice == nil {
        log.Fatalf("无法解析起始IP地址: %s", rangeStartStr)
    }
    endSlice := net.ParseIP(rangeEndStr).To4()
    if endSlice == nil {
        log.Fatalf("无法解析结束IP地址: %s", rangeEndStr)
    }

    // 2. 插入数据：将 []byte 强制转换为 string 传递给 Exec
    fmt.Printf("尝试插入 IP 范围: %s - %s\n", rangeStartStr, rangeEndStr)
    insertResult, err := db.Exec(
        "INSERT INTO AIPRangesBlocks (IPGRID, BinRangeStart, BinRangeEnd) VALUES (?, ?, ?)",
        id, string(startSlice), string(endSlice),
    )
    if err != nil {
        log.Fatalf("插入数据失败: %v", err)
    }
    rowsAffected, _ := insertResult.RowsAffected()
    fmt.Printf("插入成功，影响行数: %d\n", rowsAffected)

    // 3. 更新数据：同样将 []byte 强制转换为 string
    newRangeStartStr := "10.0.0.1"
    newRangeEndStr := "10.0.0.100"

    newStartSlice := net.ParseIP(newRangeStartStr).To4()
    if newStartSlice == nil {
        log.Fatalf("无法解析新起始IP地址: %s", newRangeStartStr)
    }
    newEndSlice := net.ParseIP(newRangeEndStr).To4()
    if newEndSlice == nil {
        log.Fatalf("无法解析新结束IP地址: %s", newRangeEndStr)
    }

    fmt.Printf("尝试更新 IP 范围为: %s - %s\n", newRangeStartStr, newRangeEndStr)
    updateResult, err := db.Exec(
        "UPDATE AIPRangesBlocks SET BinRangeStart = ?, BinRangeEnd = ? WHERE IPGRID = ?",
        string(newStartSlice), string(newEndSlice), id,
    )
    if err != nil {
        log.Fatalf("更新数据失败: %v", err)
    }
    rowsAffected, _ = updateResult.RowsAffected()
    fmt.Printf("更新成功，影响行数: %d\n", rowsAffected)

    // 4. 检索数据：将 BINARY(4) 字段扫描到 []byte
    var retrievedStartBytes, retrievedEndBytes []byte
    var retrievedID int

    row := db.QueryRow("SELECT IPGRID, BinRangeStart, BinRangeEnd FROM AIPRangesBlocks WHERE IPGRID = ?", id)
    err = row.Scan(&retrievedID, &retrievedStartBytes, &retrievedEndBytes)
    if err != nil {
        log.Fatalf("检索数据失败: %v", err)
    }

    retrievedStartIP := net.IP(retrievedStartBytes)
    retrievedEndIP := net.IP(retrievedEndBytes)

    fmt.Printf("检索到的数据:\n")
    fmt.Printf("  IPGRID: %d\n", retrievedID)
    fmt.Printf("  BinRangeStart: %s (原始: %s)\n", retrievedStartIP.String(), newRangeStartStr)
    fmt.Printf("  BinRangeEnd: %s (原始: %s)\n", retrievedEndIP.String(), newRangeEndStr)

    // 验证数据是否一致
    if retrievedStartIP.String() == newRangeStartStr && retrievedEndIP.String() == newRangeEndStr {
        fmt.Println("数据存储与检索验证成功！")
    } else {
        fmt.Println("数据存储与检索验证失败！")
    }
}