go 操作clickhouse

package main

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

	_ "github.com/ClickHouse/clickhouse-go"
)

type ClickHouseTool struct {
	db *sql.DB
}

// NewClickHouseTool 创建一个新的 ClickHouse 工具类
func NewClickHouseTool(dsn string) (*ClickHouseTool, error) {
	db, err := sql.Open("clickhouse", dsn)
	if err != nil {
		return nil, err
	}

	return &ClickHouseTool{db: db}, nil
}

// Insert 插入数据
func (c *ClickHouseTool) Insert(ctx context.Context, query string, args ...interface{}) error {
	tx, err := c.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer tx.Rollback() // 确保在出错时回滚

	_, err = tx.ExecContext(ctx, query, args...)
	if err != nil {
		return err
	}

	return tx.Commit()
}

// Update 更新数据
func (c *ClickHouseTool) Update(ctx context.Context, query string, args ...interface{}) error {
	_, err := c.db.ExecContext(ctx, query, args...)
	return err
}

// Delete 删除数据
func (c *ClickHouseTool) Delete(ctx context.Context, query string, args ...interface{}) error {
	_, err := c.db.ExecContext(ctx, query, args...)
	return err
}

// Query 查询数据
func (c *ClickHouseTool) Query(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error) {
	return c.db.QueryContext(ctx, query, args...)
}

// Close 关闭数据库连接
func (c *ClickHouseTool) Close() error {
	return c.db.Close()
}

// createTable 创建用户表
func createTable(db *sql.DB) error {
	query := `
        CREATE TABLE IF NOT EXISTS users (
            id UUID,
            username String,
            email String,
            age Int32
        ) ENGINE = MergeTree()
        ORDER BY id
    `
	_, err := db.Exec(query)
	return err
}

func main() {
	dsn := "tcp://127.0.0.1:9000?username=default&password=&database=default"
	tool, err := NewClickHouseTool(dsn)
	if err != nil {
		log.Fatal(err)
	}
	defer tool.Close()

	// 创建表
	err = createTable(tool.db)
	if err != nil {
		log.Fatal(err)
	}

	ctx := context.Background()

	// 示例：插入数据
	insertQuery := "INSERT INTO users (id, username, email, age) VALUES (?, ?, ?, ?)"
	err = tool.Insert(ctx, insertQuery, "550e8400-e29b-41d4-a716-446655440000", "user1", "user1@example.com", 30)
	if err != nil {
		log.Fatal(err)
	}

	// 示例：更新数据
	updateQuery := "ALTER TABLE users UPDATE username = ? WHERE id = ?"
	err = tool.Update(ctx, updateQuery, "new_user", "550e8400-e29b-41d4-a716-446655440000")
	if err != nil {
		log.Fatal(err)
	}

	// 示例：查询数据
	selectQuery := "SELECT id, username, email, age FROM users"
	rows, err := tool.Query(ctx, selectQuery)
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()

	for rows.Next() {
		var id string
		var username string
		var email string
		var age int32
		if err := rows.Scan(&id, &username, &email, &age); err != nil {
			log.Fatal(err)
		}
		fmt.Printf("ID: %s, Username: %s, Email: %s, Age: %d\n", id, username, email, age)
	}

	// 示例：删除数据
	deleteQuery := "ALTER TABLE users DELETE WHERE id = ?"
	err = tool.Delete(ctx, deleteQuery, "550e8400-e29b-41d4-a716-446655440000")
	if err != nil {
		log.Fatal(err)
	}
}

*错误：如果是插入，不做回滚，或者commit。会提示 insert statement supported only in the batch mode (use begin/commit) 。

原来参考的文章是好的。拿来练习，估计配置哪里有什么不一样的。本着动代码，不动库原则。进行修改

参考大佬程序练习：一文教你Go语言如何轻松使用ClickHouse_golang clickhouse-CSDN博客