golang实现将数据库表自动转为结构体的小工具（学习笔记）

必备条件

确保数据库开启

代码结构如下

代码详情

config.go

// Package config /**
package config

var SysConfig = &sysConfig{}

type sysConfig struct {
	Port       string `json:"Port"`
	DBUserName string `json:"DBUserName"`
	DBPassword string `json:"DBPassword"`
	DBIp       string `json:"DBIp"`
	DBPort     string `json:"DBPort"`
	DBName     string `json:"DBName"`
	Table      string `json:"table"`
	Path       string `json:"path"`
}

// DbTable 表类型
type DbTable struct {
	Name string `json:"name"`
}

// Column 数据字段类型
type Column struct {
	ColumnName    string `json:"column_name"`
	DataType      string `json:"data_type"`
	ColumnComment string `json:"column_comment"`
	ColumnKey     string `json:"column_key"`
	Extra         string `json:"extra"`
}

init.go

// Package Init /**
package Init

import (
	"database_auto_struct/config"
	"fmt"
	"io/ioutil"

	jsoniter "github.com/json-iterator/go"
)

func Init() {
	//指定对应的json配置文件
	b, err := ioutil.ReadFile("config.json")
	if err != nil {
		fmt.Println("读取文件失败,失败原因是:", err)
		return
	}
	err = jsoniter.Unmarshal(b, config.SysConfig)
	if err != nil {
		fmt.Println("转换失败,失败原因是:", err)
		return
	}
}

tool.go

// Package tool /**
package tool

import "strings"

// InitialToCapital 首字母转大写
func InitialToCapital(str string) string {
	var InitialToCapitalStr string
	strRune := []rune(str)
	for i := 0; i < len(strRune); i++ {
		if i == 0 {
			if strRune[i] >= 97 && strRune[i] <= 122 {
				strRune[i] -= 32
				InitialToCapitalStr += string(strRune[i])
			} else {
				return str
			}
		} else {
			InitialToCapitalStr += string(strRune[i])
		}
	}
	return InitialToCapitalStr
}

// PathProcessing 路径处理
func PathProcessing(path string) bool {
	a := strings.Split(path, "/")
	if len(a) >= 2 && a[1] != "" {
		return false
	} else {
		return true
	}

}

main.go

package main

import (
	"database/sql"
	"database_auto_struct/Init"
	"database_auto_struct/config"
	"database_auto_struct/tool"
	"fmt"
	"io/ioutil"
	"os"

	_ "github.com/go-sql-driver/mysql"
)

/**
 * @Description golang实现将数据库表自动转为结构体的小工具（学习笔记）
 * @Author 逗比的老人
 * @Date 2022/12/10 23:34
 **/

func main() {
	//初始化
	Init.Init()
	// 连接mysql
	dataSourceName := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s", config.SysConfig.DBUserName, config.SysConfig.DBPassword, config.SysConfig.DBIp,
		config.SysConfig.DBPort, config.SysConfig.DBName)
	db, err := sql.Open("mysql", dataSourceName)

	if err != nil {
		fmt.Println("连接数据库失败,失败原因是:", err)
		return
	}
	//获取所有表
	var sql = "select table_name from information_schema.tables where table_schema=?"
	// 指定生成某表
	if config.SysConfig.Table != "all_in" {
		sql = fmt.Sprintf(" select table_name from  information_schema.columns where table_schema =? and table_name = '%s' ", config.SysConfig.Table)
	}
	rows, err2 := db.Query(sql, config.SysConfig.DBName)
	if err2 != nil {
		fmt.Println("查询数据库失败,失败原因是:", err2)
		return
	}

	defer func() {
		if rows != nil {
			//防止过多连接没有释放 导致内存泄露
			rows.Close()
		}
	}()
	tableStrut := config.DbTable{}
	for rows.Next() {
		err := rows.Scan(&tableStrut.Name)
		if err != nil {
			fmt.Printf("生成表失败,失败原因是:err:%v", err)
			return
		}
		//获取单个表所有字段
		sqlStr := fmt.Sprintf("select column_name columnName, data_type dataType, column_comment columnComment, column_key columnKey, extra from information_schema.columns where table_name = '%s' and table_schema =(select database()) order by ordinal_position ", tableStrut.Name)
		fieldConn, err3 := db.Query(sqlStr)
		if err3 != nil {
			fmt.Println("mysql 获取单个表所有字段 err:", err3)
		}
		defer func() {
			if fieldConn != nil {
				fieldConn.Close()
			}
		}()

		// ----- 拼接生成的struct  start--------
		structStr := fmt.Sprintf("type %s struct { \n", tool.InitialToCapital(tableStrut.Name))
		column := config.Column{}
		for fieldConn.Next() {
			err := fieldConn.Scan(&column.ColumnName, &column.DataType, &column.ColumnComment, &column.ColumnKey, &column.Extra)
			if err != nil {
				fmt.Printf("获取失败,失败原因是:err:%v", err)
				return
			}
			structStr += "    " + tool.InitialToCapital(column.ColumnName)
			if column.DataType == "int" || column.DataType == "tinyint" {
				structStr += " int "
			} else if column.DataType == "bigint" {
				structStr += " int64 "
			} else if column.DataType == "timestamp" || column.DataType == "date" || column.DataType == "datetime" {
				structStr += " time.Time "
			} else if column.DataType == "decimal" {
				structStr += " float64 "
			} else {
				structStr += " string "
			}

			if column.Extra != "auto_increment" {
				structStr += fmt.Sprintf("`gorm:\"comment('%s')\" json:\"%s\"` \n",
					column.ColumnComment, column.ColumnName)
			} else {
				structStr += fmt.Sprintf("`gorm:\"not null comment('%s') INT(11)\" json:\"%s\"` \n",
					column.ColumnComment, column.ColumnName)
			}

		}
		structStr += "}"
		modelHead := "package model \n\n"
		// ----- 拼接生成的struct end --------
		if tool.PathProcessing(config.SysConfig.Path) {
			fmt.Println("路径不匹配")
			return
		}
		//导出文件并创建文件夹
		body := modelHead + structStr
		filename := fmt.Sprintf("%s/%s.go", config.SysConfig.Path, tableStrut.Name)
		error2 := os.MkdirAll(config.SysConfig.Path, os.ModePerm)
		if error2 != nil {
			fmt.Println("创建文件夹 err:", error2)
		}
		err4 := ioutil.WriteFile(filename, []byte(body), 0666)
		if err4 != nil {
			fmt.Println("写入文件错误:", err4)
		}
	}
	fmt.Println("Successful")
}

config.json[运行man.go文件的时候需要转哪个表就改Table名]

{
  "Port": "8848",
  "DBUserName": "root",
  "DBPassword": "123456",
  "DBIp": "127.0.0.1",
  "DBPort": "3306",
  "DBName": "bubble",
  "Table": "post",
  "Path": "./model"
}

代码完毕

run一下

生成代码如下

对应数据库

学习参考链接
https://blog.csdn.net/FindHuni/article/details/123552574