go-database

先要下访问mysql数据库的驱动，下面是一个
https://github.com/go-sql-driver/mysql

// 打开数据库
db, err := sql.Open("mysql", "user:password@/dbname")
if err != nil {
	return  err
}
defer db.Close()

增，改，删操作

一般用Prepared Statements和Exec()完成INSERT, UPDATE, DELETE操作。

stmt, err := db.Prepare("INSERT INTO users(name) VALUES(?)")
if err != nil {
    log.Fatal(err)
}
res, err := stmt.Exec("Dolly")
if err != nil {
    log.Fatal(err)
}
lastId, err := res.LastInsertId()
if err != nil {
    log.Fatal(err)
}
rowCnt, err := res.RowsAffected()
if err != nil {
    log.Fatal(err)
}
log.Printf("ID = %d, affected = %d\n", lastId, rowCnt)

插入数据

//插入数据
stmt, err := db.Prepare("INSERT userinfo SET username=?,departname=?,created=?")
checkErr(err)

res, err := stmt.Exec("astaxie", "研发部门", "2012-12-09")
checkErr(err)

id, err := res.LastInsertId()
checkErr(err)

fmt.Println(id)

更新操作

result, err := db.Exec("INSERT INTO t_diaryModify(f_date,f_contentOld,f_operator,f_contentNew) VALUES (?,?,?,?)",
					time.Now().Format("2006-01-02 15:04:05"), recordOld, 1, recordNew)
if err != nil {
	// error handler
	fmt.Println(err)
}
fmt.Println(result.LastInsertId())
fmt.Println(result.RowsAffected())

//更新数据
stmt, err = db.Prepare("update userinfo set username=? where uid=?")
checkErr(err)

res, err = stmt.Exec("astaxieupdate", id)
checkErr(err)

affect, err := res.RowsAffected()
checkErr(err)

fmt.Println(affect)

删除操作

//删除数据
stmt, err = db.Prepare("delete from userinfo where uid=?")
checkErr(err)

res, err = stmt.Exec(id)
checkErr(err)

affect, err = res.RowsAffected()
checkErr(err)

fmt.Println(affect)

事务

db.Begin()开始事务，Commit() 或 Rollback()关闭事务。Tx从连接池中取出一个连接，在关闭之前都是使用这个连接。Tx不能和DB层的BEGIN, COMMIT混合使用。

tx, err := db.Begin()
if err != nil {
	log.Fatal(err)
}

//...
tx.Commit()

查询

//查询数据
var s sql.NullString
err = db.QueryRow("SELECT * FROM t_devices WHERE f_cellPhone=?", cellphone).Scan(&s)
if err != nil{
	logger.Error(err.Error())
	return false,err
}
if s.Valid {
	// use s.String
} else {
	// NULL value
}

//查询数据
rows, err := db.Query("SELECT * FROM t_devices WHERE f_cellPhone=?", cellphone)
if err != nil {
	logger.Error(err.Error())
	return devInfo, err
}

for rows.Next() {
	err = rows.Scan(&devInfo.id, &devInfo.sn, &devInfo.nickName, &devInfo.registerDate, &devInfo.lastActiveDate,
		&devInfo.comment, &devInfo.cellPhone, &devInfo.manufactureId, &devInfo.plate, &devInfo.plateColor, &devInfo.province,
		&devInfo.terminalId, &devInfo.terminalType, &devInfo.authCode)
	if err != nil {
		logger.Error(err.Error())
		return devInfo, err
	}
	fmt.Println(fmt.Sprintf("%+v", devInfo))
}

存储过程

如果表不存在就创建，同时返回该表是否存在的返回值。0不存在，1存在

DELIMITER $$
DROP PROCEDURE IF EXISTS genGisMonthTable;
CREATE PROCEDURE genGisMonthTable(IN tableName VARCHAR(100))
  BEGIN

    SET @sqlStr = CONCAT(' CREATE TABLE IF NOT EXISTS `',tableName,'`(
    `id` BIGINT(20) AUTO_INCREMENT PRIMARY KEY COMMENT "主键"
    ) ENGINE = INNODB DEFAULT CHARSET = utf8;
    ');
  PREPARE stmt FROM @sqlStr;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;

	SELECT COUNT(*) AS count FROM information_schema.tables WHERE TABLE_NAME=tableName;

  END$$
DELIMITER ;

判断表是否存在

SELECT COUNT(*)
FROM information_schema.tables
WHERE table_schema = '[database name]'
AND table_name = '[table name]';

调用

tableName := "t_position" + time.Now().Format("200601")
isExist := 99
rows, err := db.Query("call genGisMonthTable(?)",tableName)
if err != nil {
	fmt.Println(err.Error())
}

for rows.Next() {
	err = rows.Scan(&isExist)
	if err != nil {
		fmt.Println(err.Error())
	}
	fmt.Println(isExist)
}
//======================