记录一下使用mysql来处理db相关操作。
连接db
package main
import (
"database/sql"
"github.com/go-sql-driver/mysql"
"github.com/sirupsen/logrus"
)
func connMysql() bool {
cfg := &mysql.Config{}
cfg.Net = "tcp"
cfg.Addr = "127.0.0.1"
cfg.DBName = "test"
cfg.User = "root"
cfg.Passwd = "root"
cfg.AllowNativePasswords = true
dns := cfg.FormatDSN()
logrus.Debugf("connMysql %v", dns)
_, err := sql.Open("mysql", dns)
if err != nil {
logrus.Fatalf("connMysql open fail")
return false
}
logrus.Debugf("connMysql open success")
return true
}
func main() {
connMysql()
}
添加测试表
CREATE TABLE IF NOT EXISTS `tbl_test` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '唯一ID',
`user` VARCHAR(50) NOT NULL COMMENT '用户ID' UNIQUE KEY,
`ts` INT(10) UNSIGNED NOT NULL COMMENT '时间戳',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
增查改
增
insForm, perErr := db.Prepare("INSERT INTO tbl_test(user, ts) VALUES(?,?)")
if perErr != nil {
panic(err.Error())
}
t := time.Now()
secs := t.Unix()
insResult, insErr := insForm.Exec("abel", secs)
if insErr != nil {
panic(insErr.Error())
}
insRow, insErr := insResult.RowsAffected()
if insErr != nil {
panic(insErr.Error())
}
logrus.Infof("RowsAffected %v", insRow)
查
selDB, selErr := db.Query("SELECT * FROM tbl_test WHERE user='abel'")
if selErr != nil {
panic(selErr.Error())
}
for selDB.Next() {
var name string
var id, ts int
err = selDB.Scan(&id, &name, &ts)
if err != nil {
panic(err.Error())
}
logrus.Infof("row info name: %v, ts: %v", name, ts)
}
改
updateForm, upErr := db.Prepare("update tbl_test set ts=? where user=?")
if upErr != nil {
panic(upErr.Error())
}
t := time.Now()
secs := t.Unix()
upResult, rErr := updateForm.Exec(secs, "abel")
if rErr != nil {
panic(rErr)
}
num, _ := upResult.RowsAffected()
logrus.Infof("update RowsAffected %v", num)
