Go言語でMysql操作

# 必要ライブラリのインストール

https://github.com/go-sql-driver/mysql
のライブラリーをインストールする。

```
go get github.com/go-sql-driver/mysql
```


# フォルダ構成

```
/
├── main.go
├── mysql
│   └── mysql.go
└── sqlscan.go
```

# 個別ソース

``` golang:main.go
package main

import (
	"fmt"
	"math/rand"
	"strconv"
	"time"

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

const dbuser = "root"
const dbpassword = "root"
const conecttype = "tcp"
const ipaddr = "127.0.0.1"
const port = "33060"
const databs = "my_database"

func main() {
	var err error
	inputdata := []string{"あいうえお", "かきくけこ", "さしすせそ", "たちつてと"}
	rand.Seed(time.Now().UnixNano())
	err = sqltmp.SqlSetup(dbuser, dbpassword, conecttype, ipaddr, port, databs)
	if err != nil {

	}
	defer sqltmp.CloseDB()
	sqlsetup(&sqltmp)
	sqltmp.CreateTableDB()

	for i := len(scansql()); i < 10; i++ {
		sqltmp.AddDB(inputdata[rand.Intn(len(inputdata))]+strconv.Itoa(i), inputdata[rand.Intn(len(inputdata))])
	}
	sqltmp.EditDB("2", "bb", "cc")
	sqltmp.DeleteDB(5)
	fmt.Printf("7番目表示:")
	fmt.Println(scansqlId("7"))
	fmt.Printf("あいうえお検索:")
	fmt.Println(serch_sql("あいうえお"))
	fmt.Printf("today検索:")
	fmt.Println(serch_sql("today"))
	fmt.Printf("すべて表示:")
	fmt.Println(scansql())
}

``` 
``` golang:sqlscan.go
package main

import (
	"database/sql"
	"fmt"
	"time"

	"./mysql"
)

//
type SqlData struct {
	Id         int       `json:id`
	Name       string    `json:name`
	Text       string    `json:text`
	Created_at time.Time `json:created_at`
	Updated_at time.Time `json:updated_at`
}

var sqltmp mysql.SqlConfig

//テーブルセットアップ
func sqlsetup(sql *mysql.SqlConfig) {
	tableName := "blogs"
	column_name := []string{"name", "text"}
	column_type := []string{"varchar(256)", "text"}
	sql.TableSetupDB(tableName, column_name, column_type)
}

//すべて表示
func scansql() []SqlData {
	output := []SqlData{}
	row, _ := sqltmp.ReadAllDB()
	defer row.Close()
	for row.Next() {
		var tmp SqlData
		err := row.Scan(&tmp.Id, &tmp.Name, &tmp.Text, &tmp.Created_at, &tmp.Updated_at)
		if err != nil {
			fmt.Println(err.Error())
			break
		}
		output = append(output, tmp)
	}
	return output
}

//キーワード検索
func serch_sql(keyword string) []SqlData {
	var row *sql.Rows
	timekeyword := []string{"today", "toweek", "tomonth"}
	keyflag := -1
	output := []SqlData{}
	for i := 0; i < len(timekeyword); i++ {
		if keyword == timekeyword[i] {
			keyflag = i
			break
		}
	}
	if keyflag >= 0 {
		row, _ = sqltmp.SerchTimeDB(keyflag)
	} else {
		row, _ = sqltmp.SeachReadDB(keyword, []string{"name", "text"})
	}
	defer row.Close()
	for row.Next() {
		var tmp SqlData
		err := row.Scan(&tmp.Id, &tmp.Name, &tmp.Text, &tmp.Created_at, &tmp.Updated_at)
		if err != nil {
			fmt.Println(err.Error())
			break
		}
		output = append(output, tmp)
	}
	return output
}

//ID検索
func scansqlId(Id string) SqlData {
	tmp := SqlData{}
	row, _ := sqltmp.ReadIdDB(Id)
	row.Next()
	err := row.Scan(&tmp.Id, &tmp.Name, &tmp.Text, &tmp.Created_at, &tmp.Updated_at)
	if err != nil {
		fmt.Println(err.Error())
	}
	return tmp
}

```

``` golang:mysql.go
package mysql

import (
	"database/sql"
	"errors"
	"fmt"
	"time"

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

//SqlConfigはデータベースに関する配列
type SqlConfig struct {
	dataSourceName string
	dbname         string
	setupflag      bool
	dbpath         *sql.DB
	tableName      string
}

var column_name []string
var column_type []string

//sqlSetup はデータベースの基本設定
func (t *SqlConfig) SqlSetup(dbUser, dbPass, conectType, ipAddr, portN, dbName string) error {
	t.dataSourceName = dbUser + ":" + dbPass + "@" + conectType + "(" + ipAddr + ":" + portN + ")/"
	t.dbname = dbName
	t.setupflag = true
	db, err := t.openDB()
	if err != nil {
		err = t.createDB()
		if err != nil {
			fmt.Println(err.Error())
			return err
		}
		db, err = t.openDB()
	}
	t.dbpath = db
	return err
}

//TableSetupDB()
func (t *SqlConfig) TableSetupDB(table string, columnName, columnType []string) {
	t.tableName = table
	column_name = columnName
	column_type = columnType

}

//createDB はデータベースを作成する
func (t *SqlConfig) createDB() error {
	if !t.setupflag {
		return errors.New("not run setup")
	}
	db, err := sql.Open("mysql", t.dataSourceName)
	if err != nil {
		fmt.Println(err.Error())
	}
	str := "CREATE DATABASE " + t.dbname + ";"
	_, err = db.Exec(str)
	if err != nil {
		fmt.Println(err.Error())
	} else {
		fmt.Println("Successfully created database..")
	}
	db.Close()
	return err
}

//openDB()
//データベースを開く
func (t *SqlConfig) openDB() (*sql.DB, error) {
	if !t.setupflag {
		return nil, errors.New("not run setup")
	}
	db, err := sql.Open("mysql", t.dataSourceName+t.dbname+"?parseTime=true")
	if err != nil {
		fmt.Println(err.Error())
	}
	err = db.Ping()
	if err != nil {
		fmt.Println("データベースの接続失敗")
		db.Close()
	} else {
		fmt.Println("データベースの接続成功")
	}
	return db, err
}

//CloseDB()
//
func (t *SqlConfig) CloseDB() error {
	t.setupflag = false
	err := t.dbpath.Close()
	return err
}

//CreateTableDB()
func (t *SqlConfig) CreateTableDB() {
	if !t.setupflag {
		return
	}
	cmd := "create table "
	cmd += t.tableName + " "
	cmd += "("
	cmd += "id" + " " + "int " + "NOT NULL AUTO_INCREMENT" + ","
	for i := 0; i < len(column_type); i++ {
		cmd += column_name[i] + " " + column_type[i] + ","
	}
	cmd += "created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,"
	cmd += "updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,"
	cmd += "PRIMARY KEY (id)"
	cmd += ")"
	fmt.Println(cmd)
	stmt, err := t.dbpath.Prepare(cmd)
	if err != nil {
		fmt.Println(err.Error())
	}
	_, err = stmt.Exec()
	if err != nil {
		fmt.Println(err.Error())
	} else {
		fmt.Println("データベース作成")
	}

}

//AddDB()
//
func (t *SqlConfig) AddDB(v ...interface{}) {
	if !t.setupflag {
		return
	}
	cmd := "insert into " + t.tableName + "("
	for i := 0; i < len(column_name); i++ {
		if i == 0 {
			cmd += column_name[i]
		} else {
			cmd += "," + column_name[i]
		}
	}
	cmd += ") values("
	for i := 0; i < len(column_name); i++ {
		if i == 0 {
			cmd += "?"
		} else {
			cmd += ",?"
		}
	}
	cmd += ")"
	fmt.Println(cmd)
	stmt, err := t.dbpath.Prepare(cmd)
	if err != nil {
		fmt.Println(err.Error())
	}
	_, err = stmt.Exec(v...)
	if err != nil {
		fmt.Println(err.Error())
	} else {
		fmt.Println("追加成功")
	}
}

//ReadAllDB()
func (t *SqlConfig) ReadAllDB() (*sql.Rows, error) {
	cmd := "select * from " + t.tableName
	row, err := t.dbpath.Query(cmd)
	if err != nil {
		fmt.Println(err.Error())
	}
	return row, err
}

//SerchTimeDB()
func (t *SqlConfig) SerchTimeDB(num int) (*sql.Rows, error) {
	nowtime := time.Now()
	cmd := "select * from " + t.tableName
	switch num {
	case 1: //week
		cmd += " " + "where " + "updated_at "
		cmd += "between '" + nowtime.Add(-24*time.Hour*7).Format("2006-01-02") + "' and '"
		cmd += nowtime.Add(24*time.Hour).Format("2006-01-02") + "'"
	case 2: //month
		cmd += " " + "where " + "updated_at "
		cmd += "between '" + nowtime.Add(-24*time.Hour*30).Format("2006-01-02") + "' and '"
		cmd += nowtime.Add(24*time.Hour).Format("2006-01-02") + "'"
	default: //today
		cmd += " " + "where " + "updated_at "
		cmd += "between '" + nowtime.Format("2006-01-02") + "' and '"
		cmd += nowtime.Add(24*time.Hour).Format("2006-01-02") + "'"
	}
	row, err := t.dbpath.Query(cmd)
	if err != nil {
		fmt.Println(err.Error())
	}
	return row, err
}

//ReadIdDB()
func (t *SqlConfig) ReadIdDB(Id string) (*sql.Rows, error) {
	cmd := "select * from " + t.tableName
	cmd += " " + "where id=" + Id
	row, err := t.dbpath.Query(cmd)
	if err != nil {
		fmt.Println(err.Error())
	}
	return row, err
}

func (t *SqlConfig) SeachReadDB(word string, serchKey []string) (*sql.Rows, error) {
	cmd := "select * from " + t.tableName
	cmd += " " + "where "
	for i := 0; i < len(serchKey); i++ {
		if i == 0 {
			cmd += serchKey[i] + " " + "like '%" + word + "%'"
		} else {
			cmd += " or " + serchKey[i] + " " + "like '%" + word + "%'"
		}
	}
	row, err := t.dbpath.Query(cmd)
	if err != nil {
		fmt.Println(err.Error())
	}
	return row, err
}

//EditDB()
//
func (t *SqlConfig) EditDB(No string, v ...interface{}) {
	if !t.setupflag {
		return
	}
	cmd := "update " + t.tableName + " set "
	for i := 0; i < len(column_name); i++ {
		if i == 0 {
			cmd += column_name[i] + "=?"
		} else {
			cmd += "," + column_name[i] + "=?"
		}
	}
	cmd += ",updated_at=now()"
	cmd += " where id=" + No
	fmt.Println(cmd)
	stmt, err := t.dbpath.Prepare(cmd)
	if err != nil {
		fmt.Println(err.Error())
	}
	_, err = stmt.Exec(v...)
	if err != nil {
		fmt.Println(err.Error())
	} else {
		fmt.Println("編集成功")
	}
}

//DeleteDB()
//
func (t *SqlConfig) DeleteDB(No int) {
	if !t.setupflag {
		return
	}
	cmd := "delete from " + t.tableName + " where id=?"
	stmtDelete, err := t.dbpath.Prepare(cmd)
	if err != nil {
		panic(err.Error())
	}
	defer stmtDelete.Close()

	result, err := stmtDelete.Exec(No)
	if err != nil {
		panic(err.Error())
	}

	_, err = result.RowsAffected()
	if err != nil {
		panic(err.Error())
	}

}
```

main.goはテスト用のプログラムやMysqlのデータベースアクセスについて記載しています。
sqlscan.goはテーブルを作成または、操作するための型を作るように作成しました。
mysql.goは汎用的操作が可能のように、作成しました。基本はRailsで作成したら出来るようなテーブルを作るようにしています。
写経をして、Mysqlの動作を確認してみてください。

sqlscan.goはもう少し、いじればもっと汎用性が高くなるけど、現状は放置だな。