10
8

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.

Goで一通りのsql操作をやってみる

Last updated at Posted at 2019-12-18

この記事はtomowarkar ひとりAdvent Calendar 2019の18日目の記事です。

はじめに

Goでのmysql操作を備忘録としてまとめてみた

コード全文

コード全文
package main

import (
	"database/sql"
	"fmt"
	"log"

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

const appname = "gopherbot"

func run() (*sql.DB, error) {
	dbDriver := "mysql"
	dbUser := "root"
	dbPass := "password"
	dbName := ""
	db, err := sql.Open(dbDriver, dbUser+":"+dbPass+"@/"+dbName)
	if err != nil {
		return nil, err
	}
	defer db.Close()

	res, _ := db.Query("SHOW DATABASES")
	var database string

	for res.Next() {
		res.Scan(&database)
		fmt.Println(database)
	}
	fmt.Println()

	_, err = db.Exec("CREATE DATABASE IF NOT EXISTS " + appname)
	if err != nil {
		return nil, err
	}

	res, _ = db.Query("SHOW DATABASES")

	for res.Next() {
		res.Scan(&database)
		fmt.Println(database)
	}
	fmt.Println()

	_, err = db.Exec("USE " + appname)
	if err != nil {
		return nil, err
	}

	res, _ = db.Query("SHOW TABLES")
	var table string
	for res.Next() {
		res.Scan(&table)
		fmt.Println(table)
	}
	fmt.Println()

	_, err = db.Exec("CREATE TABLE IF NOT EXISTS example ( id integer, name varchar(32) )")
	if err != nil {
		return nil, err
	}

	ins, err := db.Prepare("INSERT INTO example(id,name) VALUES(?,?)")
	if err != nil {
		return nil, err
	}
	ins.Exec(1, "hoge")
	ins.Exec(3, "huga")

	res, _ = db.Query("SELECT *  FROM example")
	for res.Next() {
		var id int
		var name string
		res.Scan(&id, &name)
		fmt.Println(id, name)
	}
	fmt.Println()

	_, err = db.Exec("DROP DATABASE " + appname)
	if err != nil {
		return nil, err
	}

	return db, nil
}

func main() {
	_, err := run()
	if err != nil {
		log.Fatal(err)
	}
}
output
mysql

gopherbot
mysql

1 hoge
3 huga

データベース接続, 切断

dbDriver := "mysql"
dbUser := "root"
dbPass := "password"
dbName := ""
db, err := sql.Open(dbDriver, dbUser+":"+dbPass+"@/"+dbName)
if err != nil {
	log.Fatal(err)
}
defer db.Close()

データベース表示(SHOW DATABASES)

res, _ := db.Query("SHOW DATABASES")
var database string
for res.Next() {
	res.Scan(&database)
	fmt.Println(database)
}

データベース作成(CREATE DATABASE)

_, err = db.Exec("CREATE DATABASE IF NOT EXISTS " + yourDatabsase)
if err != nil {
	log.Fatal(err)
}

データベース選択(USE)

_, err = db.Exec("USE " + yourDatabsase)
if err != nil {
	log.Fatal(err)
}

テーブル表示(SHOW TABLES)

res, _ = db.Query("SHOW TABLES")
var table string
for res.Next() {
	res.Scan(&table)
	fmt.Println(table)
}

テーブル作成(CREATE TABLE)

_, err = db.Exec("CREATE TABLE IF NOT EXISTS " + yourTable + " ( id integer, name varchar(32) )")
if err != nil {
	log.Fatal(err)
}

データ挿入(INSERT)

ins, err := db.Prepare("INSERT INTO " + yourTable + "(id,name) VALUES(?,?)")
if err != nil {
	log.Fatal(err)
}
ins.Exec(1, "hoge")
ins.Exec(3, "huga")

データ表示(SELECT)

res, _ = db.Query("SELECT *  FROM " + yourTable)
for res.Next() {
	var id int
	var name string
	res.Scan(&id, &name)
	fmt.Println(id, name)
}

データベース削除(DROP DATABASE)

_, err = db.Exec("DROP DATABASE " + yourDatabsase)
if err != nil {
	log.Fatal(err)
}

おわりに

以上明日も頑張ります!!
tomowarkar ひとりAdvent Calendar Advent Calendar 2019

参考

https://qiita.com/merrill/items/967884c02e10bd8f32f5
https://medium.com/@udayakumarvdm/create-mysql-database-using-golang-b28c08e54660
https://flaviocopes.com/golang-sql-database/

10
8
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
10
8

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?