Golang 对象关系映射框架 GORM 实现自定义 SQL 提示

GORM 原生支持 3 种提示，分别是：

Index HintsOptimizer HintsComment Hints/* */

WITH(...)WITH

TableHints.go

package tool

import (
	"gorm.io/gorm"
	"gorm.io/gorm/clause"
	"gorm.io/hints"
)

// TableHints 自定义 GORM 表提示
//
// @Author zixizixi.cn
type TableHints struct {
	Type string
	Keys []string
}

// ModifyStatement 实现 gorm.StatementModifier 接口方法
func (tableHint TableHints) ModifyStatement(stmt *gorm.Statement) {
	for _, name := range []string{"FROM", "INTO"} {
		stmtClause := stmt.Clauses[name]

		if stmtClause.AfterExpression == nil {
			stmtClause.AfterExpression = tableHint
		} else {
			stmtClause.AfterExpression = hints.Exprs{stmtClause.AfterExpression, tableHint}
		}

		stmt.Clauses[name] = stmtClause
	}
}

// Build 实现 clause.Expression 接口方法
func (tableHint TableHints) Build(builder clause.Builder) {
	if len(tableHint.Keys) > 0 {
		_, _ = builder.WriteString(tableHint.Type)
		_ = builder.WriteByte('(')
		for idx, key := range tableHint.Keys {
			if idx > 0 {
				_ = builder.WriteByte(',')
			}
			_, _ = builder.WriteString(key)
		}
		_ = builder.WriteByte(')')
	}
}

// With Sql Server 使用 WITH(?) 表提示
func (tableHint TableHints) With(names ...string) TableHints {
	return TableHints{Type: "WITH", Keys: names}
}

// UseWithNolock Sql Server 使用 WITH(NOLOCK) 表提示
func (tableHint TableHints) UseWithNolock() TableHints {
	return tableHint.With("NOLOCK")
}

Usage

• WITH(NOLOCK) 脏读表提示

import (
    "gorm.io/hints"
    "tool"
)

db.Clauses(tool.TableHints{}.UseWithNolock()).Find(&User{})
// SELECT * FROM users WITH(NOLOCK)

• 其他表提示

import (
    "gorm.io/hints"
    "tool"
)

db.Clauses(tool.TableHints{}.With("NOWAIT")).Find(&User{})
// SELECT * FROM users WITH(NOWAIT)

db.Clauses(tool.TableHints{}.With("ROWLOCK")).Find(&User{})
// SELECT * FROM users WITH(ROWLOCK)