golang mysql商业用例_golang操作mysql用例

golang操作mysql的demo，直接上代码

package main

import (

"database/sql"

"fmt"

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

"log"

"strings"

)

const (

userName = "root"

password = "123456"

ip = "127.0.0.1"

port = "3306"

dbName = "oversea"

)

//var db = &sql.DB{}

func main() {

insert("test")

//query()

//update()

//query()

//delete()

}

func createTable(db *sql.DB, table string) {

sql := `

CREATE TABLE ` + table + `(

uid INT(10) NOT NULL AUTO_INCREMENT,

age INT(10) NOT NULL,

username VARCHAR(64) NULL DEFAULT NULL,

created DATE NULL DEFAULT NULL,

PRIMARY KEY(uid)

)ENGINE=InnoDB DEFAULT CHARSET=utf8;`

fmt.Println(sql)

smt, err := db.Prepare(sql)

if err != nil {

fmt.Println(err)

}

smt.Exec()

}

func insert(tableName string) error {

path := strings.Join([]string{userName, ":", password, "@tcp(", ip, ":", port, ")/", dbName, "?charset=utf8"}, "")

db, err := sql.Open("mysql", path)

defer db.Close()

if err != nil {

return err

}

createTable(db, tableName)

sql := fmt.Sprintf("INSERT INTO %s(uid,age,username) values(NULL,%d,%s)",tableName,"mark", 21)

_, err = db.Exec(sql)

if err != nil {

return err

}

return nil

}

func update(tableName string) error {

path := strings.Join([]string{userName, ":", password, "@tcp(", ip, ":", port, ")/", dbName, "?charset=utf8"}, "")

db, err := sql.Open("mysql", path)

defer db.Close()

if err != nil {

return err

}

createTable(db, tableName)

sql := fmt.Sprintf("UPDATE %s set username=%s where uid=%d",tableName,"mark", 21)

_, err = db.Exec(sql)

if err != nil {

return err

}

return nil

}

func delete(tableName string) error {

path := strings.Join([]string{userName, ":", password, "@tcp(", ip, ":", port, ")/", dbName, "?charset=utf8"}, "")

db, err := sql.Open("mysql", path)

defer db.Close()

if err != nil {

return err

}

createTable(db, tableName)

sql := fmt.Sprintf("DELETE FROM %s where uid=%d",tableName,21)

_, err = db.Exec(sql)

if err != nil {

return err

}

return nil

}

func query(tableName string) error {

path := strings.Join([]string{userName, ":", password, "@tcp(", ip, ":", port, ")/", dbName, "?charset=utf8"}, "")

db, err := sql.Open("mysql", path)

defer db.Close()

if err != nil {

return err

}

createTable(db, tableName)

sql := fmt.Sprintf("SELECT uid,username FROM TEST",tableName)

rows, _ := db.Query(sql)

defer rows.Close()

for rows.Next() {

var name string

var id int

if err := rows.Scan(&id, &name); err != nil {

log.Fatal(err)

}

fmt.Printf("name:%s ,id:is %d\n", name, id)

}

return nil

}