GO学习记录四——读取excel完成数据库建表

一、研究了下读取本地excel完成数据库建表的操作，以下为excel格式。
半路出家学的后端，对数据库完全是小白一枚。
后续这个表可以根据实际项目进行完善，此处只记录下思路和主要逻辑代码。

二、代码部分
同时添加了指定打印日志颜色功能，之前参与前端开发，看习惯了颜色分明的日志形式。

package main//引用的包
import ("database/sql""fmt""log""strconv""strings"_ "github.com/lib/pq"         //pgsql数据库组件"github.com/xuri/excelize/v2" //解析excel文件包
)// 定义数据库相关配置
const (host     = "localhost"        //数据库ipport     = 5432               //数据库端口user     = "postgres"         //数据库用户名password = "postgres"         //数据库密码dbname   = "postgresLearning" //数据库名
)// 启动函数
func main() {//初始化数据库连接db := initDB()defer db.Close() //defer db.Close() 是 Go 语言中一种确保资源被正确释放的惯用写法，它的作用是：//在函数返回前，自动调用 db.Close() 来关闭数据库连接，无论函数是正常返回还是发生 panicexcelTable := []Table{}path := "D:\\GoProject\\表结构.xlsx"excelTable = ReadExcel(path, false)for i := 0; i < len(excelTable); i++ {createDBTable(db, excelTable[i])}//测试代码// createUserTable(db)  //创建users表// createTest1Table(db) //创建test1表// createTest2Table(db) //创建test2表
}// 读取Excel文件
func ReadExcel(path string, showLog bool) []Table {createTable := []Table{}// 打开Excel文件f, err := excelize.OpenFile(path)if err != nil {fmt.Println(err)return createTable}// 获取工作表名称列表sheetNames := f.GetSheetList()//遍历sheet列表for _, sheetName := range sheetNames {if showLog {LogColor(White, "开始处理%s工作表", sheetName)}itemTable := Table{Name: sheetName,}// 读取指定工作表的所有行rows, err := f.GetRows(sheetName)if err != nil {LogColor(Red, "读取%s工作表失败,原因: %v", sheetName, err)continue}for _, row := range rows[1:] {itemColumns := Column{Name:    row[0],Type:    parseColumnType(row[1]),Length:  row[2],NotNull: parseBool(row[3], showLog),Unique:  parseBool(row[4], showLog),Primary: parseBool(row[5], showLog),}if len(row) > 6 {itemColumns.Default = row[6]}itemTable.Columns = append(itemTable.Columns, itemColumns)if showLog {// 遍历行中的单元格for _, colCell := range row {LogColor(White, "%s", colCell)}}}createTable = append(createTable, itemTable)}return createTable
}// 创建数据库表
func createDBTable(db *sql.DB, table Table) {success, createTableSQL := CreateTable(table)if success {LogColor(White, "sql=%s", createTableSQL)_, err := db.Exec(createTableSQL)if err != nil {LogColor(Red, "创建%s数据表失败,原因: %v", table.Name, err)} else {LogColor(Green, "创建%s数据表成功", table.Name)}} else {LogColor(Red, "创建%s数据表失败,原因: %s", table.Name, createTableSQL)}
}// 初始化数据库连接
func initDB() *sql.DB {// 构建连接字符串psqlInfo := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable",host, port, user, password, dbname)// 连接数据库db, err := sql.Open("postgres", psqlInfo)if err != nil {log.Fatal(err)}//defer db.Close()  这里有一个注意点，这块代码回直接关闭数据库连接// 检查连接err = db.Ping()if err != nil {log.Fatal(err)}LogColor(White, "Successfully connected to PostgreSQL database!")return db
}// ColumnItemType 定义列类型的自定义类型
type ColumnItemType string// 支持的列类型常量
const (VARCHAR   ColumnItemType = "VARCHAR"TIMESTAMP ColumnItemType = "TIMESTAMP"SERIAL    ColumnItemType = "SERIAL"TEXT      ColumnItemType = "TEXT"DECIMAL   ColumnItemType = "DECIMAL"INT       ColumnItemType = "INT"
)func parseColumnType(typeStr string) ColumnItemType {switch strings.ToUpper(typeStr) {case "VARCHAR":return VARCHARcase "TIMESTAMP":return TIMESTAMPcase "SERIAL":return SERIALcase "TEXT":return TEXTcase "DECIMAL":return DECIMALcase "INT":return INT// 处理其他可能的类型default:return ColumnItemType(typeStr) // 如果类型不在预定义的范围内，可以返回原字符串或默认值}
}// 辅助函数，将字符串转换为整数，如果转换失败则打印错误信息并返回0
func mustAtoi(s string, showLog bool) int {i, err := strconv.Atoi(s)if err != nil {if showLog {LogColor(Yellow, "无法解析int值,原因: %v", err)}return 0 // 或者你可以选择返回一个默认值，或者根据错误处理逻辑来决定}return i
}// 添加一个函数来将字符串转换为布尔值
func parseBool(str string, showLog bool) bool {switch str {case "true", "1", "TRUE", "T", "Y", "YES":return truecase "false", "0", "FALSE", "F", "N", "NO":return falsedefault:// 你可以根据需要处理默认情况，比如记录日志或者返回一个默认值if showLog {LogColor(Yellow, "无法解析布尔值，设置默认值=false, 原始值=%s", str)}return false}
}// Column 定义字段结构
type Column struct {Name    stringType    ColumnItemTypeLength  string // 长度，仅对 VARCHAR、DECIMAL 等有效NotNull boolUnique  boolPrimary boolDefault string
}// Table 定义表结构
type Table struct {Name    stringColumns []Column
}// CreateTable 生成 CREATE TABLE SQL 语句
func CreateTable(table Table) (bool, string) {if table.Name == "" {LogColor(Red, "表名不能为空")return false, ""}if len(table.Columns) == 0 {LogColor(Red, "字段列表不能为空")return false, ""}var fieldDefs []stringfor _, col := range table.Columns {if col.Name == "" {LogColor(Red, "字段名不能为空")return false, ""}def := col.Name + " " + string(col.Type) // 注意：col.Type 是 ColumnItemType，需转为 string// 处理长度（仅对支持长度的类型）if len(col.Length) > 0 && (col.Type == VARCHAR || col.Type == DECIMAL) {// 可以根据 Type 判断是否支持 Length，例如只对 VARCHAR 和 DECIMAL 生效def += "(" + col.Length + ")"}// 添加约束if col.NotNull {def += " NOT NULL"}if col.Unique {def += " UNIQUE"}if col.Primary {def += " PRIMARY KEY"}if col.Default != "" {// 判断是否需要为 DEFAULT 值加引号if col.Type == TEXT || col.Type == VARCHAR {def += fmt.Sprintf(" DEFAULT '%s'", EscapeString(col.Default))} else {def += " DEFAULT " + col.Default}}fieldDefs = append(fieldDefs, def)}// 拼接完整 SQLsql := fmt.Sprintf("CREATE TABLE IF NOT EXISTS %s (%s);",table.Name,strings.Join(fieldDefs, ", "),)return true, sql
}// EscapeString 是一个假设的函数，用于转义SQL字符串中的特殊字符
func EscapeString(s string) string {// 实现对字符串s中单引号等特殊字符的转义return strings.ReplaceAll(s, "'", "''") // 示例：转义单引号
}// 辅助函数：判断 DEFAULT 是否需要加引号
func isStringDefault(defaultValue string) bool {// 尝试将 defaultValue 转换为数字或 NULL_, err1 := strconv.ParseFloat(defaultValue, 64)_, err2 := strconv.ParseBool(defaultValue)// 如果 defaultValue 可以转换为数字或布尔值，或者它是 "NULL"，则不需要加引号return !(err1 == nil || err2 == nil || strings.ToUpper(defaultValue) == "NULL")
}// ==================================封装打印log========================================
const (Red    = "31"Green  = "32"Yellow = "33"Blue   = "34"Purple = "35"Cyan   = "36"White  = "37"
)// PrintColor 打印指定颜色的文本
// colorCode: ANSI 颜色码
// format: 格式化字符串，如 "创建%s表成功"
// args: 格式化参数
func LogColor(colorCode string, format string, args ...interface{}) {// \033[颜色码m + 文本 + \033[0m（重置）colored := fmt.Sprintf("\033[%sm%s\033[0m", colorCode, fmt.Sprintf(format, args...))fmt.Println(colored)
}//==================================封装打印log END========================================// ===============================================测试代码============================================
// 创建users表
func createUserTable(db *sql.DB) {createTable := Table{Name: "users",Columns: []Column{{Name: "id", Type: SERIAL, Primary: true},{Name: "username", Type: VARCHAR, Length: "50", Unique: true, NotNull: true},{Name: "password", Type: VARCHAR, Length: "100", NotNull: true},{Name: "email", Type: VARCHAR, Length: "100", Unique: true, NotNull: true},{Name: "created_at", Type: TIMESTAMP, Default: "CURRENT_TIMESTAMP"},{Name: "updated_at", Type: TIMESTAMP, Default: "CURRENT_TIMESTAMP"},},}success, createTableSQL := CreateTable(createTable)if success {db.Exec(createTableSQL)LogColor(Green, "创建%s数据表成功", createTable.Name)}
}// 创建test1表
func createTest1Table(db *sql.DB) {createTable := Table{Name: "test1",Columns: []Column{{Name: "id", Type: SERIAL, Primary: true},{Name: "name", Type: VARCHAR, Length: "50", Unique: true, NotNull: true},{Name: "age", Type: INT, NotNull: true},{Name: "created_at", Type: TIMESTAMP, Default: "CURRENT_TIMESTAMP"},{Name: "updated_at", Type: TIMESTAMP, Default: "CURRENT_TIMESTAMP"},},}success, createTableSQL := CreateTable(createTable)if success {db.Exec(createTableSQL)LogColor(Green, "创建%s数据表成功", createTable.Name)}
}// 创建test2表
func createTest2Table(db *sql.DB) {createTable := Table{Name: "test2",Columns: []Column{{Name: "id", Type: SERIAL, Primary: true},{Name: "name", Type: VARCHAR, Length: "50", Unique: true, NotNull: true},{Name: "age", Type: INT, NotNull: true},{Name: "created_at", Type: TIMESTAMP, Default: "CURRENT_TIMESTAMP"},{Name: "updated_at", Type: TIMESTAMP, Default: "CURRENT_TIMESTAMP"},},}success, createTableSQL := CreateTable(createTable)if success {db.Exec(createTableSQL)LogColor(Green, "创建%s数据表成功", createTable.Name)}
}

三、结果展示