package main
import (
"database/sql"
"encoding/json"
"fmt"
"github.com/go-sql-driver/mysql"
"log"
"strconv"
"time"
)
var db *sql.DB
func initDb() {
var err error
config := mysql.Config{
User: "root",
Passwd: "",
Addr: "xxx.xxx.xxx.xxx:3306",
Net: "tcp",
DBName: "xxx",
AllowNativePasswords: true,
ParseTime: false,
}
db, err = sql.Open("mysql", config.FormatDSN())
checkError(err)
// 设置最大连接数
db.SetMaxOpenConns(25)
// 设置最大空闲连接数
db.SetMaxIdleConns(25)
// 设置每个链接的过期时间
db.SetConnMaxLifetime(5 * time.Minute)
// 尝试连接,失败会报错
err = db.Ping()
checkError(err)
}
func checkError(err error) {
if err != nil {
log.Fatal(err)
}
}
func Scan(list *sql.Rows) (rows []map[string]string) {
fields, _ := list.Columns()
//types,_:= list.ColumnTypes()
//for _,b:=range types{
// fmt.Println(b.DatabaseTypeName())
//}
//fmt.Printf("%T",types)
for list.Next() {
scans := make([]interface{}, len(fields))
row := make(map[string]string)
for i := range scans {
scans[i] = &scans[i]
}
list.Scan(scans...)
for i, v := range scans {
var value string
if v != nil {
value = fmt.Sprintf("%s", v)
}
row[fields[i]] = value
}
rows = append(rows, row)
}
return
}
func main() {
// 试图解析、使用和格式化的日期
initDb()
list, _ := db.Query("select * from users")
defer list.Close()
rows := Scan(list)
for _, user := range rows {
var id, _ = strconv.Atoi(user["id"])
var status, _ = strconv.ParseBool(user["status"])
// 将日期字符串解析为Go的time对象第一个参数指定格式,第二个是日期字符串
const timeLayout = "2006-01-02 15:04:05"
const timeLayout1 = "2006-01-02"
const timeLayout2 = "15:04:05"
myDate, _ := time.Parse(timeLayout, user["created_at"])
UserIns := struct {
Id int
Name string
CreatedAt string
RememberToken string
Status bool
}{
id, user["name"], myDate.Format(timeLayout2), user["remember_token"], status,
}
jsonData, _ := json.Marshal(UserIns)
json := string(jsonData)
fmt.Println(UserIns)
fmt.Println(json)
}
}
结果:
{1 admin 01:54:06 zHHWp9NOQ28fiROUlZcHM31WaLfeg1QQnNenLcLUYNLLj5SsHEZOI06GWMcU true}
{"Id":1,"Name":"admin","CreatedAt":"01:54:06","RememberToken":"zHHWp9NOQ28fiROUlZcHM31WaLfeg1QQnNenLcLUYNLLj5SsHEZOI06GWMcU","Status":true}
{15 jack 15:43:04 ovjdd4nJyAFJ1hCHfTHb7bQPnsU3uMwTXNnRgVkHvFluGUpapoGxf0i5Cg5P true}
{"Id":15,"Name":"jack","CreatedAt":"15:43:04","RememberToken":"ovjdd4nJyAFJ1hCHfTHb7bQPnsU3uMwTXNnRgVkHvFluGUpapoGxf0i5Cg5P","Status":true}
{23 测试 19:20:41 NjZBZ47d0dAAnvW7ekhuIvedMbWl42Gq32aSIVjCjYWcz2eFOlndUEOPgY00 true}
{"Id":23,"Name":"测试","CreatedAt":"19:20:41","RememberToken":"NjZBZ47d0dAAnvW7ekhuIvedMbWl42Gq32aSIVjCjYWcz2eFOlndUEOPgY00","Status":true}
{25 测试1 17:25:42 true}
{"Id":25,"Name":"测试1","CreatedAt":"17:25:42","RememberToken":"","Status":true}
{26 demo2 18:16:22 true}
{"Id":26,"Name":"demo2","CreatedAt":"18:16:22","RememberToken":"","Status":true}
{27 demo3 19:08:35 false}
{"Id":27,"Name":"demo3","CreatedAt":"19:08:35","RememberToken":"","Status":false}
{28 user01 22:50:53 false}
{"Id":28,"Name":"user01","CreatedAt":"22:50:53","RememberToken":"","Status":false}
{29 user02 22:52:34 false}
{"Id":29,"Name":"user02","CreatedAt":"22:52:34","RememberToken":"","Status":false}
{30 li 13:57:47 false}
{"Id":30,"Name":"li","CreatedAt":"13:57:47","RememberToken":"","Status":false}