go语言mysql入门之一（查询）

阳光穿透心脏的1/2处 2022-04-23 04:08 534阅读 0赞

> go语言mysql查询

### 文章目录 ###

*   *   *  1. sql依赖包的安装
         *  2. 初始化
         *  3. 简单使用
         *   *  3.1 表结构
             *  3.2 声明返回结构体
             *  3.2 连接数据库
             *  3.3 查询单条记录
             *  3.4 更改json输出
         *  4. 多条记录查询

### 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][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”\}\]

[Name_abc_126.com]: mailto:Name:abc@126.com