standard/sql/brush/brush.go

package main

import (
	"flag"
	"fmt"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
	"log"
	"math/rand"
	"net/url"
	"os"
	"time"
)


var username,password,host,db string
var port int

type Project struct {
	Id          uint      `gorm:"primarykey"`
	Code        string    `gorm:"column:code;type:varchar(16);not null;default:''"`
	APIKey      string    `gorm:"column:api_key;type:varchar(255);not null;default:''"`
	APISecret   string    `gorm:"column:api_secret;type:varchar(255);not null;default:''"`
	ChainId     uint      `gorm:"column:chain_id;type:bigint(20);not null"`
	UserId      uint      `gorm:"column:user_id;type:bigint(20);not null"`
	Name        string    `gorm:"column:name;type:varchar(255);not null;default:''"`
	Description string    `gorm:"column:description;type:varchar(255);not null;default:''"`
	CreatedAt   time.Time `gorm:"column:created_at;type:datetime;not null;default:CURRENT_TIMESTAMP"`
	UpdatedAt   time.Time `gorm:"column:updated_at;type:datetime ON UPDATE CURRENT_TIMESTAMP;not null;default:CURRENT_TIMESTAMP"`
}
func (Project) TableName() string {
	return "project"
}

func main(){
	flag.StringVar(&username, "u", "console", "")
	flag.StringVar(&password, "pwd", "consolePassword", "")
	flag.StringVar(&host, "h", "192.168.150.40", "")
	flag.StringVar(&db, "d", "console_server", "")
	flag.IntVar(&port, "p", 23306, "")

	flag.Parse()

	newLogger := logger.New(
		log.New(os.Stdout, "\r\n", log.LstdFlags), // io writer
		logger.Config{
			SlowThreshold: time.Second,   // 慢 SQL 阈值
			LogLevel:      logger.Silent, // Log level
			Colorful:      false,         // 禁用彩色打印
		},
	)

	dsn := fmt.Sprintf(
		"%s:%s@tcp(%s:%d)/%s?charset=utf8&parseTime=True&loc=Local&time_zone=%s",
		username,
		password,
		host,
		port,
		db,
		url.QueryEscape("'UTC'"),
	)

	mysqlDb, err := gorm.Open(mysql.Open(dsn),&gorm.Config{Logger:newLogger})
	if err != nil {
		log.Fatal("init mysql db failed, err: ",err.Error())
	}

	codeMap := make(map[string]int)

	tx := mysqlDb.Begin()
	//增加sql
	alterSql := "ALTER TABLE `project` MODIFY COLUMN `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键id' FIRST, ADD COLUMN `code` varchar(16) NOT NULL DEFAULT \"\" COMMENT \"项目id\" AFTER `id`"
	err = tx.Exec(alterSql).Error
	if err != nil {
		tx.Rollback()
		log.Fatal("add code failed, err: ",err.Error())
	}

	//查询code为null的数据
	var projectList []Project
	err = tx.Model(&Project{}).Where("code = ?","").Find(&projectList).Error
	if err != nil {
		tx.Rollback()
		log.Fatal("query code is null failed, err: ",err.Error())
	}
	if len(projectList) > 0 {
		for _,item := range projectList {
			var code string
			for {
				code = GetRandomString(16)
				_,ok := codeMap[code]
				if !ok {
					codeMap[code] = 1
					break
				}
			}

			updateMap := map[string]interface{}{}
			updateMap["code"] = code
			err = tx.Model(&Project{}).Where("id = ?", item.Id).Updates(updateMap).Error
			if err != nil {
				tx.Rollback()
				log.Fatal("update code failed, err: ",err.Error())
			}
		}
	}
	indexSql := "ALTER TABLE `project` ADD UNIQUE INDEX `code`(`code`) USING BTREE;"
	err = mysqlDb.Exec(indexSql).Error
	if err != nil {
		tx.Rollback()
		log.Fatal("add index failed, err: ",err.Error())
	}
	tx.Commit()
	fmt.Println("finish")
}


func GetRandomString(l int) string {
	str := "0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"
	bytes := []byte(str)
	var result []byte
	r := rand.New(rand.NewSource(time.Now().UnixNano() + int64(rand.Intn(100))))
	for i := 0; i < l; i++ {
		result = append(result, bytes[r.Intn(len(bytes))])
	}
	return string(result)
}