Go to Qiita Advent Calendar 2024 Top
LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@karosuwindam(karosu)

Go言語でMysql操作

Last updated at Posted at 2020-05-07

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

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

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

フォルダ構成

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

個別ソース

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())
}
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
}
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はもう少し、いじればもっと汎用性が高くなるけど、現状は放置だな。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?