Repository: huandu/go-sqlbuilder

Issue:
sqlbuilder.SelectBuilder 需要 Copy 方法
sqlbuilder.SelectBuilder 很需要一个 Copy 方法，用于复制一个独立，完整的 sqlbuilder.SelectBuilder
在构建一个复杂 sql 的时候，最后的 select 可能有所差别，尤其是一些 count(*) 等聚合参数
但是其他条件，where、join 等，都是一致的
导致代码非常难看

Discussion:
@huandu: 关于 `WHERE` 的复用，现在已经提供了 `WhereClause` 封装，可以在不同的 builder 实例共享，[用法可以参考文档](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#readme-share-where-clause-among-builders)。

`JOIN`、`ORDER BY`、`LIMIT` 等复用相对比较麻烦一些，我推荐的方法是实现工厂方法，直接返回一个已经 build 差不多的 builder 实例，然后业务只需要完成部分参数补全的工作。相比而言，工厂方法会比 copy 更加的有效率，特别是现在 args 里面存储的参数可能会非常复杂的情况之下，更加推荐用工厂方法。

[这里是一个例子](https://go.dev/play/p/F3UXhNpsxnT)，假设需要统计带有优惠信息的订单，根据具体需求决定返回什么字段、如何排序。

```go
func NewPromotedProductsBuilder(userId int64, categories []string) *sqlbuilder.SelectBuilder {
	sb := sqlbuilder.NewSelectBuilder()
	sb.From("products").Where(
		sb.In("status", 1, 2),
		sb.In("category_id", sqlbuilder.Raw("c.id")),
	).Join(
		sb.As("categories", "c"),
		sb.In("category", sqlbuilder.Flatten(categories)...),
	)
	return sb
}

func main() {
	latestProducts := NewPromotedProductsBuilder(12345, []string{"sports", "gaming"})
	latestProducts.Select("id", "name", "description").OrderBy("updated_at").Desc()
	sql, args := latestProducts.Build()
	fmt.Println(sql, args)

	totalProducts := NewPromotedProductsBuilder(12345, []string{"sports", "gaming"})
	totalProducts.Select(totalProducts.As("COUNT(*)", "cnt"))
	sql, args = totalProducts.Build()
	fmt.Println(sql, args)
}
```

@cloudzhou: 我知道可以这么写，但是现实中 SelectBuilder 在多个方法中传递，然后根据条件才进行 Where
最后等到 Select(totalProducts.As("COUNT(*)", "cnt")) 这一步，逻辑才开始分叉
更别提中间那些按需条件下的 group by, having
所以一个简单地 Copy 就逻辑清晰，等于产生一个副本，放心安全的使用@zhangyongding: 我也认为这个是有必要的@huandu: 这个功能已经实现并发布了 tag v1.37.0，欢迎大家尝试一下。具体来说，现在所有的 Builder 都加上了一个 Clone 方法，用法很简单，大家可以自行看看文档。@cloudzhou: 感谢，设想是这么实现的

Solution patch:
diff --git a/README.md b/README.md
index 35c863d..6a88b01 100644
--- a/README.md
+++ b/README.md
@@ -17,6 +17,7 @@
   - [Use `sql.Named` in a builder](#use-sqlnamed-in-a-builder)
   - [Argument modifiers](#argument-modifiers)
   - [Freestyle builder](#freestyle-builder)
+  - [Clone builders](#clone-builders)
   - [Using special syntax to build SQL](#using-special-syntax-to-build-sql)
   - [Interpolate `args` in the `sql`](#interpolate-args-in-the-sql)
 - [License](#license)
@@ -381,6 +382,51 @@ fmt.Println(args)
 // [1 2]
 ```
 
+### Clone builders
+
+The `Clone` methods make any builder reusable as a template. You can create a partially initialized builder once (even as a global), then call `Clone()` to get an independent copy to customize per request. This avoids repeated setup while keeping shared templates immutable and safe for concurrent use.
+
+Supported builders with `Clone`:
+
+- [CreateTableBuilder](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#CreateTableBuilder)
+- [CTEBuilder](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#CTEBuilder)
+- [CTEQueryBuilder](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#CTEQueryBuilder)
+- [DeleteBuilder](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#DeleteBuilder)
+- [InsertBuilder](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#InsertBuilder)
+- [SelectBuilder](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#SelectBuilder)
+- [UnionBuilder](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#UnionBuilder)
+- [UpdateBuilder](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#UpdateBuilder)
+
+Example: define a global SELECT template and clone it per call
+
+```go
+package yourpkg
+
+import "github.com/huandu/go-sqlbuilder"
+
+// Global template — safe to reuse by cloning.
+var baseUserSelect = sqlbuilder.NewSelectBuilder().
+    Select("id", "name", "email").
+    From("users").
+    Where("deleted_at IS NULL")
+
+func ListActiveUsers(limit, offset int) (string, []interface{}) {
+    sb := baseUserSelect.Clone() // independent copy
+    sb.OrderBy("id").Asc()
+    sb.Limit(limit).Offset(offset)
+    return sb.Build()
+}
+
+func GetActiveUserByID(id int64) (string, []interface{}) {
+    sb := baseUserSelect.Clone() // start from the same template
+    sb.Where(sb.Equal("id", id))
+    sb.Limit(1)
+    return sb.Build()
+}
+```
+
+The same template pattern applies to other builders. For example, keep a base `UpdateBuilder` with the table and common `SET` clauses, or a base `CTEBuilder` defining reusable CTEs, then `Clone()` and add query-specific `WHERE`/`ORDER BY`/`LIMIT`/`RETURNING` as needed.
+
 ### Using special syntax to build SQL
 
 The `sqlbuilder` package incorporates special syntax for representing uncompiled SQL internally. To leverage this syntax for developing customized tools, the `Build` function can be utilized to compile it with the necessary arguments.
diff --git a/args.go b/args.go
index 3884a44..3467a80 100644
--- a/args.go
+++ b/args.go
@@ -6,9 +6,12 @@ package sqlbuilder
 import (
 	"database/sql"
 	"fmt"
+	"reflect"
 	"sort"
 	"strconv"
 	"strings"
+
+	"github.com/huandu/go-clone"
 )
 
 // Args stores arguments associated with a SQL.
@@ -17,7 +20,7 @@ type Args struct {
 	Flavor Flavor
 
 	indexBase    int
-	argValues    []interface{}
+	argValues    *valueStore
 	namedArgs    map[string]int
 	sqlNamedArgs map[string]int
 	onlyNamed    bool
@@ -48,7 +51,7 @@ func (args *Args) Add(arg interface{}) string {
 }
 
 func (args *Args) add(arg interface{}) int {
-	idx := len(args.argValues) + args.indexBase
+	idx := args.argValues.Len() + args.indexBase
 
 	switch a := arg.(type) {
 	case sql.NamedArg:
@@ -57,7 +60,7 @@ func (args *Args) add(arg interface{}) int {
 		}
 
 		if p, ok := args.sqlNamedArgs[a.Name]; ok {
-			arg = args.argValues[p]
+			arg = args.argValues.Load(p)
 			break
 		}
 
@@ -68,7 +71,7 @@ func (args *Args) add(arg interface{}) int {
 		}
 
 		if p, ok := args.namedArgs[a.name]; ok {
-			arg = args.argValues[p]
+			arg = args.argValues.Load(p)
 			break
 		}
 
@@ -78,10 +81,31 @@ func (args *Args) add(arg interface{}) int {
 		return idx
 	}
 
-	args.argValues = append(args.argValues, arg)
+	if args.argValues == nil {
+		args.argValues = &valueStore{}
+	}
+
+	args.argValues.Add(arg)
 	return idx
 }
 
+// Replace replaces the placeholder with arg.
+//
+// The placeholder must be the value returned by `Add`, e.g. "$1".
+// If the placeholder is not found, this method does nothing.
+func (args *Args) Replace(placeholder string, arg interface{}) {
+	dollar := strings.IndexRune(placeholder, '$')
+
+	if dollar != 0 {
+		return
+	}
+
+	if i, err := strconv.Atoi(placeholder[1:]); err == nil {
+		i -= args.indexBase
+		args.argValues.Set(i, arg)
+	}
+}
+
 // Compile compiles builder's format to standard sql and returns associated args.
 //
 // The format string uses a special syntax to represent arguments.
@@ -201,14 +225,14 @@ func (args *Args) compileDigits(ctx *argsCompileContext, format string, offset i
 }
 
 func (args *Args) compileSuccessive(ctx *argsCompileContext, format string, offset int) (string, int) {
-	if offset < 0 || offset >= len(args.argValues) {
+	if offset < 0 || offset >= args.argValues.Len() {
 		ctx.WriteString("/* INVALID ARG $")
 		ctx.WriteString(strconv.Itoa(offset))
 		ctx.WriteString(" */")
 		return format, offset
 	}
 
-	arg := args.argValues[offset]
+	arg := args.argValues.Load(offset)
 	ctx.WriteValue(arg)
 
 	return format, offset + 1
@@ -245,7 +269,7 @@ func (args *Args) mergeSQLNamedArgs(ctx *argsCompileContext) []interface{} {
 	sort.Ints(ints)
 
 	for _, i := range ints {
-		values = append(values, args.argValues[i])
+		values = append(values, args.argValues.Load(i))
 	}
 
 	return values
@@ -364,3 +388,50 @@ func (ctx *argsCompileContext) WriteValues(values []interface{}, sep string) {
 		ctx.WriteValue(v)
 	}
 }
+
+type valueStore struct {
+	Values []interface{}
+}
+
+func init() {
+	// The values in valueStore should be shadow-copied to avoid unnecessary cost.
+	t := reflect.TypeOf(valueStore{})
+	clone.SetCustomFunc(t, func(allocator *clone.Allocator, old, new reflect.Value) {
+		values := old.FieldByName("Values")
+		newValues := allocator.Clone(values)
+		new.FieldByName("Values").Set(newValues)
+	})
+}
+
+func (as *valueStore) Len() int {
+	if as == nil {
+		return 0
+	}
+
+	return len(as.Values)
+}
+
+// Add adds an arg to argsValues and returns its index.
+func (as *valueStore) Add(arg interface{}) int {
+	as.Values = append(as.Values, arg)
+	return len(as.Values) - 1
+}
+
+// Set sets the arg value by index.
+func (as *valueStore) Set(index int, arg interface{}) {
+	if as == nil || index < 0 || index >= len(as.Values) {
+		return
+	}
+
+	as.Values[index] = arg
+}
+
+// Load returns the arg value by index.
+// Returns nil if index is out of range or as itself is nil.
+func (as *valueStore) Load(index int) interface{} {
+	if as == nil || index < 0 || index >= len(as.Values) {
+		return nil
+	}
+
+	return as.Values[index]
+}
diff --git a/createtable.go b/createtable.go
index b628349..31b163d 100644
--- a/createtable.go
+++ b/createtable.go
@@ -5,6 +5,8 @@ package sqlbuilder
 
 import (
 	"strings"
+
+	"github.com/huandu/go-clone"
 )
 
 const (
@@ -29,6 +31,12 @@ func newCreateTableBuilder() *CreateTableBuilder {
 	}
 }
 
+// Clone returns a deep copy of CreateTableBuilder.
+// It's useful when you want to create a base builder and clone it to build similar queries.
+func (ctb *CreateTableBuilder) Clone() *CreateTableBuilder {
+	return clone.Clone(ctb).(*CreateTableBuilder)
+}
+
 // CreateTableBuilder is a builder to build CREATE TABLE.
 type CreateTableBuilder struct {
 	verb        string
diff --git a/cte.go b/cte.go
index 8b69a1e..b32dcfa 100644
--- a/cte.go
+++ b/cte.go
@@ -3,6 +3,12 @@
 
 package sqlbuilder
 
+import (
+	"reflect"
+
+	"github.com/huandu/go-clone"
+)
+
 const (
 	cteMarkerInit injectionMarker = iota
 	cteMarkerAfterWith
@@ -25,6 +31,25 @@ func newCTEBuilder() *CTEBuilder {
 	}
 }
 
+// Clone returns a deep copy of CTEBuilder.
+// It's useful when you want to create a base builder and clone it to build similar queries.
+func (cteb *CTEBuilder) Clone() *CTEBuilder {
+	return clone.Clone(cteb).(*CTEBuilder)
+}
+
+func init() {
+	t := reflect.TypeOf(CTEBuilder{})
+	clone.SetCustomFunc(t, func(allocator *clone.Allocator, old, new reflect.Value) {
+		cloned := allocator.CloneSlowly(old)
+		new.Set(cloned)
+
+		cteb := cloned.Addr().Interface().(*CTEBuilder)
+		for i, b := range cteb.queries {
+			cteb.args.Replace(cteb.queryBuilderVars[i], b)
+		}
+	})
+}
+
 // CTEBuilder is a CTE (Common Table Expression) builder.
 type CTEBuilder struct {
 	recursive        bool
@@ -47,7 +72,7 @@ func (cteb *CTEBuilder) With(queries ...*CTEQueryBuilder) *CTEBuilder {
 		queryBuilderVars = append(queryBuilderVars, cteb.args.Add(query))
 	}
 
-	cteb.queries = queries
+	cteb.queries = append([]*CTEQueryBuilder(nil), queries...)
 	cteb.queryBuilderVars = queryBuilderVars
 	cteb.marker = cteMarkerAfterWith
 	return cteb
diff --git a/ctequery.go b/ctequery.go
index 0f5b396..1663c0b 100644
--- a/ctequery.go
+++ b/ctequery.go
@@ -3,6 +3,12 @@
 
 package sqlbuilder
 
+import (
+	"reflect"
+
+	"github.com/huandu/go-clone"
+)
+
 const (
 	cteQueryMarkerInit injectionMarker = iota
 	cteQueryMarkerAfterTable
@@ -29,10 +35,28 @@ func newCTEQueryBuilder() *CTEQueryBuilder {
 	}
 }
 
+// Clone returns a deep copy of CTEQueryBuilder.
+// It's useful when you want to create a base builder and clone it to build similar queries.
+func (ctetb *CTEQueryBuilder) Clone() *CTEQueryBuilder {
+	return clone.Clone(ctetb).(*CTEQueryBuilder)
+}
+
+func init() {
+	t := reflect.TypeOf(CTEQueryBuilder{})
+	clone.SetCustomFunc(t, func(allocator *clone.Allocator, old, new reflect.Value) {
+		cloned := allocator.CloneSlowly(old)
+		new.Set(cloned)
+
+		ctetb := cloned.Addr().Interface().(*CTEQueryBuilder)
+		ctetb.args.Replace(ctetb.builderVar, ctetb.builder)
+	})
+}
+
 // CTEQueryBuilder is a builder to build one table in CTE (Common Table Expression).
 type CTEQueryBuilder struct {
 	name       string
 	cols       []string
+	builder    Builder
 	builderVar string
 
 	// if true, this query's table name will be automatically added to the table list
@@ -62,6 +86,7 @@ func (ctetb *CTEQueryBuilder) Table(name string, cols ...string) *CTEQueryBuilde
 
 // As sets the builder to select data.
 func (ctetb *CTEQueryBuilder) As(builder Builder) *CTEQueryBuilder {
+	ctetb.builder = builder
 	ctetb.builderVar = ctetb.args.Add(builder)
 	ctetb.marker = cteQueryMarkerAfterAs
 	return ctetb
diff --git a/delete.go b/delete.go
index ede7ecf..a96295b 100644
--- a/delete.go
+++ b/delete.go
@@ -3,6 +3,12 @@
 
 package sqlbuilder
 
+import (
+	"reflect"
+
+	"github.com/huandu/go-clone"
+)
+
 const (
 	deleteMarkerInit injectionMarker = iota
 	deleteMarkerAfterWith
@@ -33,6 +39,24 @@ func newDeleteBuilder() *DeleteBuilder {
 	}
 }
 
+// Clone returns a deep copy of DeleteBuilder.
+// It's useful when you want to create a base builder and clone it to build similar queries.
+func (db *DeleteBuilder) Clone() *DeleteBuilder {
+	return clone.Clone(db).(*DeleteBuilder)
+}
+
+func init() {
+	t := reflect.TypeOf(DeleteBuilder{})
+	clone.SetCustomFunc(t, func(allocator *clone.Allocator, old, new reflect.Value) {
+		cloned := allocator.CloneSlowly(old)
+		new.Set(cloned)
+
+		db := cloned.Addr().Interface().(*DeleteBuilder)
+		db.args.Replace(db.whereClauseExpr, db.whereClauseProxy)
+		db.args.Replace(db.cteBuilderVar, db.cteBuilder)
+	})
+}
+
 // DeleteBuilder is a builder to build DELETE.
 type DeleteBuilder struct {
 	*WhereClause
diff --git a/go.mod b/go.mod
index 3cc50b2..b5daf8f 100644
--- a/go.mod
+++ b/go.mod
@@ -4,6 +4,7 @@ go 1.18
 
 require (
 	github.com/huandu/go-assert v1.1.6
+	github.com/huandu/go-clone v1.7.3
 	github.com/huandu/xstrings v1.4.0
 )
 
diff --git a/go.sum b/go.sum
index 4763800..7023cd0 100644
--- a/go.sum
+++ b/go.sum
@@ -2,5 +2,7 @@ github.com/davecgh/go-spew v1.1.1 h1:vj9j/u1bqnvCEfJOwUhtlOARqs3+rkHYY13jYWTU97c
 github.com/davecgh/go-spew v1.1.1/go.mod h1:J7Y8YcW2NihsgmVo/mv3lAwl/skON4iLHjSsI+c5H38=
 github.com/huandu/go-assert v1.1.6 h1:oaAfYxq9KNDi9qswn/6aE0EydfxSa+tWZC1KabNitYs=
 github.com/huandu/go-assert v1.1.6/go.mod h1:JuIfbmYG9ykwvuxoJ3V8TB5QP+3+ajIA54Y44TmkMxs=
+github.com/huandu/go-clone v1.7.3 h1:rtQODA+ABThEn6J5LBTppJfKmZy/FwfpMUWa8d01TTQ=
+github.com/huandu/go-clone v1.7.3/go.mod h1:ReGivhG6op3GYr+UY3lS6mxjKp7MIGTknuU5TbTVaXE=
 github.com/huandu/xstrings v1.4.0 h1:D17IlohoQq4UcpqD7fDk80P7l+lwAmlFaBHgOipl2FU=
 github.com/huandu/xstrings v1.4.0/go.mod h1:y5/lhBue+AyNmUVz9RLU9xbLR0o4KIIExikq4ovT0aE=
diff --git a/insert.go b/insert.go
index f7cb737..7698139 100644
--- a/insert.go
+++ b/insert.go
@@ -6,6 +6,8 @@ package sqlbuilder
 import (
 	"fmt"
 	"strings"
+
+	"github.com/huandu/go-clone"
 )
 
 const (
@@ -31,6 +33,12 @@ func newInsertBuilder() *InsertBuilder {
 	}
 }
 
+// Clone returns a deep copy of InsertBuilder.
+// It's useful when you want to create a base builder and clone it to build similar queries.
+func (ib *InsertBuilder) Clone() *InsertBuilder {
+	return clone.Clone(ib).(*InsertBuilder)
+}
+
 // InsertBuilder is a builder to build INSERT.
 type InsertBuilder struct {
 	verb      string
diff --git a/select.go b/select.go
index 4f71481..da7f98e 100644
--- a/select.go
+++ b/select.go
@@ -5,7 +5,10 @@ package sqlbuilder
 
 import (
 	"fmt"
+	"reflect"
 	"strings"
+
+	"github.com/huandu/go-clone"
 )
 
 const (
@@ -55,6 +58,24 @@ func newSelectBuilder() *SelectBuilder {
 	}
 }
 
+// Clone returns a deep copy of SelectBuilder.
+// It's useful when you want to create a base builder and clone it to build similar queries.
+func (sb *SelectBuilder) Clone() *SelectBuilder {
+	return clone.Clone(sb).(*SelectBuilder)
+}
+
+func init() {
+	t := reflect.TypeOf(SelectBuilder{})
+	clone.SetCustomFunc(t, func(allocator *clone.Allocator, old, new reflect.Value) {
+		cloned := allocator.CloneSlowly(old)
+		new.Set(cloned)
+
+		sb := cloned.Addr().Interface().(*SelectBuilder)
+		sb.args.Replace(sb.whereClauseExpr, sb.whereClauseProxy)
+		sb.args.Replace(sb.cteBuilderVar, sb.cteBuilder)
+	})
+}
+
 // SelectBuilder is a builder to build SELECT.
 type SelectBuilder struct {
 	*WhereClause
diff --git a/union.go b/union.go
index 7c751e1..01a946f 100644
--- a/union.go
+++ b/union.go
@@ -5,6 +5,9 @@ package sqlbuilder
 
 import (
 	"fmt"
+	"reflect"
+
+	"github.com/huandu/go-clone"
 )
 
 const (
@@ -31,15 +34,36 @@ func newUnionBuilder() *UnionBuilder {
 	}
 }
 
+// Clone returns a deep copy of UnionBuilder.
+// It's useful when you want to create a base builder and clone it to build similar queries.
+func (ub *UnionBuilder) Clone() *UnionBuilder {
+	return clone.Clone(ub).(*UnionBuilder)
+}
+
+func init() {
+	t := reflect.TypeOf(UnionBuilder{})
+	clone.SetCustomFunc(t, func(allocator *clone.Allocator, old, new reflect.Value) {
+		cloned := allocator.CloneSlowly(old)
+		new.Set(cloned)
+
+		ub := cloned.Addr().Interface().(*UnionBuilder)
+		for i, b := range ub.builders {
+			ub.args.Replace(ub.builderVars[i], b)
+		}
+	})
+}
+
 // UnionBuilder is a builder to build UNION.
 type UnionBuilder struct {
 	opt         string
-	builderVars []string
 	orderByCols []string
 	order       string
 	limitVar    string
 	offsetVar   string
 
+	builders    []Builder
+	builderVars []string
+
 	args *Args
 
 	injection *injection
@@ -76,6 +100,7 @@ func (ub *UnionBuilder) union(opt string, builders ...Builder) *UnionBuilder {
 	}
 
 	ub.opt = opt
+	ub.builders = append([]Builder(nil), builders...)
 	ub.builderVars = builderVars
 	ub.marker = unionMarkerAfterUnion
 	return ub
diff --git a/update.go b/update.go
index 30c376a..56e9ed7 100644
--- a/update.go
+++ b/update.go
@@ -5,6 +5,9 @@ package sqlbuilder
 
 import (
 	"fmt"
+	"reflect"
+
+	"github.com/huandu/go-clone"
 )
 
 const (
@@ -38,6 +41,24 @@ func newUpdateBuilder() *UpdateBuilder {
 	}
 }
 
+// Clone returns a deep copy of UpdateBuilder.
+// It's useful when you want to create a base builder and clone it to build similar queries.
+func (ub *UpdateBuilder) Clone() *UpdateBuilder {
+	return clone.Clone(ub).(*UpdateBuilder)
+}
+
+func init() {
+	t := reflect.TypeOf(UpdateBuilder{})
+	clone.SetCustomFunc(t, func(allocator *clone.Allocator, old, new reflect.Value) {
+		cloned := allocator.CloneSlowly(old)
+		new.Set(cloned)
+
+		ub := cloned.Addr().Interface().(*UpdateBuilder)
+		ub.args.Replace(ub.whereClauseExpr, ub.whereClauseProxy)
+		ub.args.Replace(ub.cteBuilderVar, ub.cteBuilder)
+	})
+}
+
 // UpdateBuilder is a builder to build UPDATE.
 type UpdateBuilder struct {
 	*WhereClause



Test patch:
diff --git a/createtable_test.go b/createtable_test.go
index 7006090..b5a3683 100644
--- a/createtable_test.go
+++ b/createtable_test.go
@@ -102,3 +102,22 @@ func TestCreateTableGetFlavor(t *testing.T) {
 	flavor = ctbClick.Flavor()
 	a.Equal(ClickHouse, flavor)
 }
+
+func TestCreateTableClone(t *testing.T) {
+	a := assert.New(t)
+	ctb := CreateTable("demo.user").IfNotExists().
+		Define("id", "BIGINT(20)", "NOT NULL", "AUTO_INCREMENT", "PRIMARY KEY", `COMMENT "user id"`).
+		Option("DEFAULT CHARACTER SET", "utf8mb4")
+
+	ctb2 := ctb.Clone()
+	ctb2.Define("name", "VARCHAR(255)", "NOT NULL", `COMMENT "user name"`)
+
+	sql1, args1 := ctb.Build()
+	sql2, args2 := ctb2.Build()
+
+	a.Equal("CREATE TABLE IF NOT EXISTS demo.user (id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT \"user id\") DEFAULT CHARACTER SET utf8mb4", sql1)
+	a.Equal(0, len(args1))
+
+	a.Equal("CREATE TABLE IF NOT EXISTS demo.user (id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT \"user id\", name VARCHAR(255) NOT NULL COMMENT \"user name\") DEFAULT CHARACTER SET utf8mb4", sql2)
+	a.Equal(0, len(args2))
+}
diff --git a/cte_test.go b/cte_test.go
index ec7606a..1a9520e 100644
--- a/cte_test.go
+++ b/cte_test.go
@@ -193,3 +193,53 @@ func TestCTEQueryBuilderGetFlavor(t *testing.T) {
 	flavor = ctetbClick.Flavor()
 	a.Equal(ClickHouse, flavor)
 }
+
+func TestCTEBuildClone(t *testing.T) {
+	a := assert.New(t)
+	cteb := With(
+		CTETable("users", "id", "name").As(
+			Select("id", "name").From("users").Where("name IS NOT NULL"),
+		),
+	)
+	ctebClone := cteb.Clone()
+	a.Equal(cteb.String(), ctebClone.String())
+
+	sb := Select("users.id", "users.name", "orders.id").From("users").With(cteb)
+	sbClone := sb.Clone()
+	a.Equal(sb.String(), sbClone.String())
+
+	sql, args := sb.Build()
+	sqlClone, argsClone := sbClone.Build()
+	a.Equal(sql, sqlClone)
+	a.Equal(args, argsClone)
+
+	sb.Limit(20)
+	a.NotEqual(sb.String(), sbClone.String())
+}
+
+func TestCTEQueryBuilderClone(t *testing.T) {
+	a := assert.New(t)
+	ctetb := CTETable("users", "id", "name").As(Select("id", "name").From("users").Where("id > 0"))
+	// Ensure AddToTableList flag is respected in clone as default for CTETable
+	clone := ctetb.Clone()
+	a.Equal(ctetb.String(), clone.String())
+	a.Equal(ctetb.ShouldAddToTableList(), clone.ShouldAddToTableList())
+}
+
+func TestCTEBuilderClone(t *testing.T) {
+	a := assert.New(t)
+	q1 := CTETable("u", "id").As(Select("id").From("users"))
+	q2 := CTEQuery("o", "id").As(Select("id").From("orders"))
+	cte := With(q1, q2)
+	clone := cte.Clone()
+
+	s1, args1 := cte.Build()
+	s2, args2 := clone.Build()
+	a.Equal(s1, s2)
+	a.Equal(args1, args2)
+
+	// mutate clone and verify original unchanged
+	q3 := CTETable("p", "id").As(Select("id").From("profiles"))
+	clone.With(q3)
+	a.NotEqual(cte.String(), clone.String())
+}
diff --git a/delete_test.go b/delete_test.go
index 02e2d3e..400bd8f 100644
--- a/delete_test.go
+++ b/delete_test.go
@@ -193,3 +193,20 @@ func TestDeleteBuilderReturning(t *testing.T) {
 	sql, _ = db7.BuildWithFlavor(PostgreSQL)
 	a.Equal("WITH temp_user AS (SELECT id FROM inactive_users) DELETE FROM user, temp_user WHERE user.id IN (SELECT id FROM temp_user) RETURNING id, deleted_at", sql)
 }
+
+func TestDeleteBuilderClone(t *testing.T) {
+	a := assert.New(t)
+	cte := With(
+		CTETable("temp").As(Select("id").From("to_delete")),
+	)
+	db := cte.DeleteFrom("target").Where("temp.id = target.id").OrderBy("id").Asc().Limit(3).Returning("id")
+
+	clone := db.Clone()
+	s1, args1 := db.BuildWithFlavor(PostgreSQL)
+	s2, args2 := clone.BuildWithFlavor(PostgreSQL)
+	a.Equal(s1, s2)
+	a.Equal(args1, args2)
+
+	clone.Desc().Limit(5)
+	a.NotEqual(db.String(), clone.String())
+}
diff --git a/insert_test.go b/insert_test.go
index 233c14a..6c11c40 100644
--- a/insert_test.go
+++ b/insert_test.go
@@ -317,3 +317,21 @@ func TestIssue200(t *testing.T) {
 	query, _ := ib.Build()
 	a.Equal(query, "INSERT INTO table (data) SELECT id, data FROM table WHERE id = $1 ON CONFLICT DO NOTHING")
 }
+
+func TestInsertBuilderClone(t *testing.T) {
+	a := assert.New(t)
+
+	ib := InsertInto("demo.user").Cols("id", "name")
+	ib.Values(1, "A")
+
+	clone := ib.Clone()
+
+	s1, args1 := ib.Build()
+	s2, args2 := clone.Build()
+	a.Equal(s1, s2)
+	a.Equal(args1, args2)
+
+	// mutate clone and verify original unchanged
+	clone.Values(2, "B")
+	a.NotEqual(ib.String(), clone.String())
+}
diff --git a/select_fuzz_test.go b/select_fuzz_test.go
index aa52bee..03324ab 100644
--- a/select_fuzz_test.go
+++ b/select_fuzz_test.go
@@ -5,6 +5,7 @@ import (
 	"math/rand"
 	"reflect"
 	"slices"
+	"sync"
 	"testing"
 )
 
@@ -382,3 +383,116 @@ func generateArgumentForType(argType reflect.Type, data []byte) reflect.Value {
 		return reflect.Zero(argType)
 	}
 }
+
+// FuzzSelectClone fuzzes SelectBuilder.Clone behavior under concurrent usage
+// and ensures cloned instances are independent and safe to mutate.
+func FuzzSelectClone(f *testing.F) {
+	f.Fuzz(func(t *testing.T, data []byte, seed int64, numberOfChainedFunction uint8) {
+		if len(data) == 0 {
+			return
+		}
+
+		methodList, methodNames := getSelectBuilderMethods()
+
+		r := rand.New(rand.NewSource(seed))
+		r.Shuffle(len(methodNames), func(i, j int) {
+			methodNames[i], methodNames[j] = methodNames[j], methodNames[i]
+		})
+
+		// Build a base template SelectBuilder via fuzzed method chains.
+		base := NewSelectBuilder()
+		baseState := &fuzzState{
+			data:                    data,
+			dataIndex:               0,
+			callchainRepresentation: "NewSelectBuilder()",
+			currentBuilder:          reflect.ValueOf(base),
+			usedMethods:             make(map[string]bool),
+		}
+
+		maxChains := numberOfChainedFunction
+		if maxChains > 10 {
+			maxChains = 10
+		}
+		executeMethodChain(methodList, methodNames, baseState, maxChains, t)
+
+		baseSQLBefore, baseArgsBefore := base.Build()
+
+		// Clone concurrently and mutate clones with fuzzed chains.
+		cloneCount := int(r.Uint32()%4) + 1 // 1..4 clones
+		var wg sync.WaitGroup
+		wg.Add(cloneCount)
+		start := make(chan struct{})
+
+		type result struct {
+			sql  string
+			args []interface{}
+		}
+		results := make(chan result, cloneCount)
+
+		for i := 0; i < cloneCount; i++ {
+			// Use different offsets into the same fuzz data for variety.
+			offset := 0
+			if len(data) > 0 {
+				offset = (i * 17) % len(data)
+			}
+			go func(off int) {
+				defer wg.Done()
+				<-start // start all goroutines roughly at the same time
+
+				c := base.Clone()
+				st := &fuzzState{
+					data:                    data,
+					dataIndex:               off,
+					callchainRepresentation: "Clone()",
+					currentBuilder:          reflect.ValueOf(c),
+					usedMethods:             make(map[string]bool),
+				}
+				executeMethodChain(methodList, methodNames, st, maxChains, t)
+				finalizeBuild(st) // ensure no panic on Build
+				s, a := c.Build()
+				results <- result{sql: s, args: a}
+			}(offset)
+		}
+
+		close(start)
+		wg.Wait()
+		close(results)
+
+		// Ensure base builder stays unchanged after concurrent cloning/mutation of clones.
+		baseSQLAfter, baseArgsAfter := base.Build()
+		if baseSQLBefore != baseSQLAfter || !reflect.DeepEqual(baseArgsBefore, baseArgsAfter) {
+			t.Fatalf("base builder mutated by clones:\n before: %s %v\n after: %s %v", baseSQLBefore, baseArgsBefore, baseSQLAfter, baseArgsAfter)
+		}
+
+		// Independence check: mutating one clone does not affect another clone.
+		cloneA := base.Clone()
+		sA1, aA1 := cloneA.Build()
+
+		done := make(chan struct{})
+		go func() {
+			defer close(done)
+			c2 := base.Clone()
+			// Apply a deterministic small change; should not affect cloneA.
+			c2.OrderBy("id").Desc().Limit(1).Offset(0)
+			_, _ = c2.Build()
+		}()
+
+		sA2, aA2 := cloneA.Build()
+		if sA1 != sA2 || !reflect.DeepEqual(aA1, aA2) {
+			t.Fatalf("cloneA changed after mutating another clone")
+		}
+		<-done
+
+		// Further independence: modifying cloneA should not affect the base.
+		cloneA.Limit(3).Asc()
+		_ = cloneA.String()
+		baseSQLFinal, baseArgsFinal := base.Build()
+		if baseSQLFinal != baseSQLAfter || !reflect.DeepEqual(baseArgsFinal, baseArgsAfter) {
+			t.Fatalf("base changed after modifying a clone")
+		}
+
+		// Drain results to ensure all builds completed; mainly to use the values and avoid lints.
+		for range results {
+		}
+	})
+}
diff --git a/select_test.go b/select_test.go
index 3903cf6..32ff1ea 100644
--- a/select_test.go
+++ b/select_test.go
@@ -422,3 +422,30 @@ func TestNilPointerWhere(t *testing.T) {
 	NewSelectBuilder().SQL("$0").Build()
 	NewSelectBuilder().SQL("$0").BuildWithFlavor(DefaultFlavor)
 }
+
+func TestSelectBuilderClone(t *testing.T) {
+	a := assert.New(t)
+
+	cte := With(
+		CTETable("users").As(
+			Select("id", "name").From("users").Where("name IS NOT NULL"),
+		),
+	)
+
+	sb := cte.Select("users.id", "orders.id").From("orders").Where(
+		"users.id = orders.user_id",
+	).OrderBy("orders.id").Desc().Limit(10).Offset(2)
+
+	// Clone and compare
+	clone := sb.Clone()
+	s1, args1 := sb.Build()
+	s2, args2 := clone.Build()
+	a.Equal(s1, s2)
+	a.Equal(args1, args2)
+
+	// Mutate clone and ensure original unchanged
+	clone.Limit(100).Asc()
+	s1After := sb.String()
+	s2After := clone.String()
+	a.NotEqual(s1After, s2After)
+}
diff --git a/struct_test.go b/struct_test.go
index d350f62..bb419f8 100644
--- a/struct_test.go
+++ b/struct_test.go
@@ -328,13 +328,14 @@ func (rows testRows) Close() error {
 }
 
 func (rows testRows) Scan(dest ...interface{}) error {
-	if rows == 0 {
+	switch rows {
+	case 0:
 		fmt.Sscan("1234 huandu 1", dest...)
-	} else if rows == 1 {
+	case 1:
 		fmt.Sscan("1234 34 huandu 1456725903636000000", dest...)
-	} else if rows == 2 {
+	case 2:
 		fmt.Sscan("1 1456725903636000000", dest...)
-	} else {
+	default:
 		panic("invalid rows")
 	}
 
diff --git a/union_test.go b/union_test.go
index 87915b6..cd655d9 100644
--- a/union_test.go
+++ b/union_test.go
@@ -245,3 +245,21 @@ func ExampleUnionBuilder_limit_offset() {
 	// #4: (SELECT * FROM user1) UNION (SELECT * FROM user2) LIMIT 1
 	// #5: (SELECT * FROM user1) UNION (SELECT * FROM user2) ORDER BY id LIMIT 1 OFFSET 1
 }
+
+func TestUnionBuilderClone(t *testing.T) {
+	a := assert.New(t)
+	sb1 := Select("id", "name").From("users").Where("active = 1")
+	sb2 := Select("id", "nick").From("profiles").Where("status IN (1,2)")
+
+	ub := UnionAll(sb1, sb2).OrderBy("id").Desc().Limit(5).Offset(1)
+	clone := ub.Clone()
+
+	s1, args1 := ub.Build()
+	s2, args2 := clone.Build()
+	a.Equal(s1, s2)
+	a.Equal(args1, args2)
+
+	// Change clone and ensure original unchanged
+	clone.Asc().Limit(10)
+	a.NotEqual(ub.String(), clone.String())
+}
diff --git a/update_test.go b/update_test.go
index a9a17cf..acce757 100644
--- a/update_test.go
+++ b/update_test.go
@@ -266,3 +266,20 @@ func TestUpdateBuilderReturning(t *testing.T) {
 	sql, _ = ub7.BuildWithFlavor(PostgreSQL)
 	a.Equal("WITH temp_user AS (SELECT id FROM active_users) UPDATE user SET status = $1 FROM temp_user WHERE user.id IN (SELECT id FROM temp_user) RETURNING id, status", sql)
 }
+
+func TestUpdateBuilderClone(t *testing.T) {
+	a := assert.New(t)
+	cte := With(
+		CTETable("vip").As(Select("user_id").From("vip_users")),
+	)
+	ub := cte.Update("orders").Set("discount = 1").Where("orders.user_id = vip.user_id").OrderBy("orders.id").Desc().Limit(2).Returning("orders.id")
+
+	clone := ub.Clone()
+	s1, args1 := ub.BuildWithFlavor(PostgreSQL)
+	s2, args2 := clone.BuildWithFlavor(PostgreSQL)
+	a.Equal(s1, s2)
+	a.Equal(args1, args2)
+
+	clone.Asc().Limit(5)
+	a.NotEqual(ub.String(), clone.String())
+}