1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161
| package main
import ( "database/sql" "errors" "fmt" _ "github.com/go-sql-driver/mysql" "log" "reflect" "strings" )
var DB *sql.DB
var ( dbAddress = "mysql.hongfs.cn" dbName = "hongfs" dbUsername = "root" dbPassword = "12345678" )
var ( oldDomain = "cdn-old.hongfs.cn" newDomain = "cdn-new.hongfs.cn" )
func init() { db, err := sql.Open("mysql", fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?&collation=utf8mb4_unicode_ci", dbUsername, dbPassword, dbAddress, 3306, dbName))
if err != nil { panic(err) }
DB = db }
func main() { tables, err := getTableNames()
if err != nil { log.Printf("获取全部表名失败:%s", err.Error())
return }
for _, name := range tables { err := updateTable(name)
if err != nil { log.Printf("更新 %s 表异常:%s", name, err.Error()) } }
log.Println("完成") }
func updateTable(name string) error { rows, err := DB.Query(fmt.Sprintf("SELECT * FROM %s", name))
if err != nil { return err }
column, err := rows.Columns()
if err != nil { return err }
if len(column) < 1 { return errors.New("表的列过少") }
if strings.ToLower(column[0]) != "id" { return errors.New("表的第一列需要为 ID") }
vals := make([]interface{}, len(column))
for i, _ := range column { vals[i] = new(sql.RawBytes) }
defer rows.Close()
for rows.Next() { err = rows.Scan(vals...)
if err != nil { return err }
var id string
for i, val := range vals { val := reflect.ValueOf(val).Interface().(*sql.RawBytes)
value := string(*val)
if i == 0 { id = value
continue }
if strings.Index(value, oldDomain) == -1 { continue }
exec := fmt.Sprintf("UPDATE %s SET `%s` = ? WHERE `id` = ? AND `%s` = ?", name, column[i], column[i])
_, err = DB.Exec(exec, strings.ReplaceAll(value, oldDomain, newDomain), id, value)
if err != nil { log.Printf("更新异常:[%s 表][id=%s] %s\n", name, id, err.Error()) } } }
return nil }
func getTableNames() ([]string, error) { rows, err := DB.Query("SELECT `TABLE_NAME` AS `name` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=?;", dbName)
if err != nil { return nil, err }
defer rows.Close()
var list []string
for rows.Next() { var name string
err = rows.Scan(&name)
if err != nil { return nil, err }
list = append(list, name) }
err = rows.Err()
if err != nil { return nil, err }
return list, nil }
|