go语言mysql查询
1. sql依赖包的安装
go get -v -u github.com/go-sql-driver/mysql
2. 初始化
import (
"database/sql"
_ "github.com/go-sql-driver/mysql"
)
func main() {
db, err := sql.Open("mysql", "user:password@/dbname?charset=utf8")
//操作你的操作...
defer db.Close()
}
3. 简单使用
3.1 表结构
CREATE TABLE `users` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`phone` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '手机号',
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `users_email_unique` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
3.2 声明返回结构体
type ResUser struct {
Id uint `json:"id"`
Name string `json:"name"`
Phone string `json:"phone"`
CreatedAt string `json:"created_at"`
}
3.2 连接数据库
db, err := sql.Open("mysql", "root:@/test?charset=utf8")
if err != nil {
log.Printf("connect failed error info is %v \n", err)
return
}
defer db.Close()
3.3 查询单条记录
var u = ResUser{}
nsql := `select id ,name,phone,created_at from users where id = ? `
err = db.QueryRow(nsql, 4).Scan(&u.Id,&u.Name,&u.Phone,&u.CreatedAt)
if err != nil {
log.Printf("query failed error info is %v \n", err)
}
fmt.Printf("%+v\n",u) //{Id:5 Name:123 Phone:13912345678 CreatedAt:2018-11-08 09:43:11}
注意:这里的Scan
函数是根据查询字段的顺序返回的,需要做到一一对相应,否则可能会错乱
3.4 更改json输出
js,_ := json.Marshal(u)
fmt.Printf("%s\n",js) //{"id":5,"name":"123","phone":"13912345678","created_at":"2018-11-08 09:43:11"}
4. 多条记录查询
type ResUser struct {
Id uint `json:"id"`
Name string `json:"name"`
Phone sql.NullString `json:"phone"`
CreatedAt string `json:"created_at"`
}
db, err := sql.Open("mysql", "root:@/test?charset=utf8")
if err != nil {
log.Printf("connect failed error info is %v \n", err)
return
}
defer db.Close()
var us []ResUser
var tu = ResUser{}
nsql := `select id ,name,phone,created_at from users where id > ? `
rows,err := db.Query(nsql,3)
if err != nil {
log.Printf("query failed error info is %v \n", err)
return
}
for rows.Next() {
err = rows.Scan(&tu.Id,&tu.Name,&tu.Phone,&tu.CreatedAt)
if err != nil {
log.Printf(err.Error())
continue
}
if !tu.Phone.Valid {
tu.Phone.String = ""
}
us = append(us,tu)
}
fmt.Printf("%+v\n",us)
fmt.Println()
js,_ := json.Marshal(us)
fmt.Printf("%s\n",js)
打印结果:
[{Id:4 Name:abc@126.com Phone:{String:13112345678 Valid:true} CreatedAt:2018-11-08 09:32:09} {Id:5 Name:123 Phone:{String:13912345678 Valid:true} CreatedAt:2018-11-08 09:43:11}]
[{“id”:4,“name”:“abc@126.com”,“phone”:{“String”:“13112345678”,“Valid”:true},“created_at”:“2018-11-08 09:32:09”},{“id”:5,“name”:“123”,“phone”:{“String”:“13912345678”,“Valid”:true},“created_at”:“2018-11-08 09:43:11”}]