[Go] MySQLに接続する

まずMySQLにデータを作ります。
mysql
create database go;
use go
create table users (
id int auto_increment primary key,
name varchar(255),
age int,
address varchar(255),
update_at datetime
);
mysql> describe users;
+———–+————–+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+———–+————–+——+—–+———+—————-+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(255) | YES | | NULL | |
| age | int | YES | | NULL | |
| address | varchar(255) | YES | | NULL | |
| update_at | datetime | YES | | NULL | |
+———–+————–+——+—–+———+—————-+

package main

import (
	"fmt"

	_ "github.com/go-sql-driver/mysql"
	"github.com/jinzhu/gorm"
)

func main(){
	_, err := sqlConnect()
	if err != nil {
		panic(err.Error())
	} else {
		fmt.Println("DB connect success!")
	}
}

func sqlConnect()(database *gorm.DB, err error){
	DBMS := "mysql"
	USER := "hoge"
	PASS := "fuga"
	PROTOCOL := "tcp(localhost:3306)"
	DBNAME := "go"

	CONNECT := USER + ":" + PASS + "@" + PROTOCOL + "/" + DBNAME + "?charset=utf8&parseTime=true&loc=Asia%2FTokyo"
	return gorm.Open(DBMS, CONNECT)
}

$ /home/vagrant/go/bin/dep ensure
$ go run main.go
DB connect success!

### データ挿入

import (
	"fmt"
	"time"

	_ "github.com/go-sql-driver/mysql"
	"github.com/jinzhu/gorm"
)

func main(){
	db, err := sqlConnect()
	if err != nil {
		panic(err.Error())
	}
	defer db.Close()

	error := db.Create(&Users{
		Name: "山田太郎",
		Age: 20,
		Address: "東京都港区赤坂1丁目1-1",
		UpdateAt: getDate(),
	}).Error
	if error != nil {
		fmt.Println(error)
	} else {
		fmt.Println("data insert success!")
	}

}

func sqlConnect()(database *gorm.DB, err error){
	DBMS := "mysql"
	USER := "hoge"
	PASS := "fuga"
	PROTOCOL := "tcp(localhost:3306)"
	DBNAME := "go"

	CONNECT := USER + ":" + PASS + "@" + PROTOCOL + "/" + DBNAME + "?charset=utf8&parseTime=true&loc=Asia%2FTokyo"
	return gorm.Open(DBMS, CONNECT)
}

type Users struct {
	ID int
	Name string `json:"name"`
	Age int `json:"age"`
	Address string `json:"address"`
	UpdateAt string `json:"updateAt" sql:"not null;type:date"`
}

func getDate() string {
	const layout = "2006-01-01 15:05:30"
	now := time.Now()
	return now.Format(layout)
}

$ go run main.go
data insert success!

mysql> select * from users;
+—-+————–+——+———————————+———————+
| id | name | age | address | update_at |
+—-+————–+——+———————————+———————+
| 1 | 山田太郎 | 20 | 東京都港区赤坂1丁目1-1 | 2021-09-09 14:23:20 |
+—-+————–+——+———————————+———————+
1 row in set (0.00 sec)

### データの取得

func main(){
	db, err := sqlConnect()
	if err != nil {
		panic(err.Error())
	}
	defer db.Close()

	result := []*Users{}
	error := db.Find(&result).Error
	if error != nil || len(result) == 0 {
		return
	}
	for _, user := range result {
		fmt.Println(user.Name)
	}
}

$ go run main.go
山田太郎

### データの更新

func main(){
	db, err := sqlConnect()
	if err != nil {
		panic(err.Error())
	}
	defer db.Close()

	result := []*Users{}
	error := db.Find(&result).Error
	if error != nil || len(result) == 0 {
		return
	}
	for _, user := range result {
		fmt.Println(user.Name)
	}

	fmt.Println("update")

	error = db.Model(Users{}).Where("id = ?", 1).Update(&Users{
		Name: "佐藤はじめ",
		UpdateAt: getDate(),
	}).Error

	if error != nil {
		fmt.Println(error)
	}

	result = []*Users{}
	db.Find(&result)
	for _, user := range result {
		fmt.Println(user.Name)
	}
}

$ go run main.go
山田太郎
update
佐藤はじめ

### DELETE

	fmt.Println("Delete")

	error = db.Where("id = ?", 1).Delete(Users{}).Error
	

	if error != nil {
		fmt.Println(error)
	}

	result = []*Users{}
	db.Find(&result)
	for _, user := range result {
		fmt.Println(user.Name)
	}

$ go run main.go
佐藤はじめ
Delete

mysql> select * from users;
Empty set (0.00 sec)

OK、CRUDは一通りわかった🈴