==表结构==
CREATE TABLE `point_info` (
`id` BIGINT(19) NOT NULL COMMENT '自增ID',
`product_key` VARCHAR(50) NOT NULL COMMENT '产品标识' COLLATE 'utf8mb4_general_ci',
`device_name` VARCHAR(50) NOT NULL COMMENT '设备名称' COLLATE 'utf8mb4_general_ci',
`point_id` VARCHAR(50) NOT NULL COMMENT '测点ID' COLLATE 'utf8mb4_general_ci',
PRIMARY KEY (`id`) USING BTREE
)
COMMENT='测点信息'
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
==下载依赖==
go get github.com/go-sql-driver/mysql
==代码样例==
package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
"time"
)
var MysqlDb *sql.DB
var MysqlDbErr error
type PointInfo struct {
Id int64 `db:"id"`
ProductKey string `db:"product_key"`
DeviceName string `db:"device_name"`
PointId string `db:"point_id"`
}
const (
USER_NAME = "root"
PASS_WORD = "root"
HOST = "xx.xx.xx.xx"
PORT = "3306"
DATABASE = "pulse"
CHARSET = "utf8"
)
// 初始化链接
func init() {
dbDSN := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?charset=%s", USER_NAME, PASS_WORD, HOST, PORT, DATABASE, CHARSET)
// 打开连接失败
MysqlDb, MysqlDbErr = sql.Open("mysql", dbDSN)
//defer MysqlDb.Close();
if MysqlDbErr != nil {
fmt.Println("dbDSN: " + dbDSN)
panic("数据源配置不正确: " + MysqlDbErr.Error())
}
// 最大连接数
MysqlDb.SetMaxOpenConns(100)
// 闲置连接数
MysqlDb.SetMaxIdleConns(20)
// 最大连接周期
MysqlDb.SetConnMaxLifetime(100 * time.Second)
if MysqlDbErr = MysqlDb.Ping(); nil != MysqlDbErr {
panic("数据库链接失败: " + MysqlDbErr.Error())
}
}
func main() {
// 插入数据
id := StructInsert()
// 查询指定数据
StructQueryField(id)
// 查询所有数据
StructQueryAllField()
// 更新数据
StructUpdate(id)
// 删除数据
StructDel(id)
}
// StructQueryField 查询数据,指定字段名
func StructQueryField(id int64) {
var sqlStr = "SELECT id, product_key, device_name, point_id FROM point_info WHERE id = ?"
row := MysqlDb.QueryRow(sqlStr, id)
pointInfo := new(PointInfo)
err := row.Scan(&pointInfo.Id, &pointInfo.ProductKey, &pointInfo.DeviceName, &pointInfo.PointId)
if err != nil {
fmt.Printf("scan failed, err:%v", err)
return
}
fmt.Println(pointInfo.Id, pointInfo.ProductKey, pointInfo.DeviceName, pointInfo.PointId)
}
// StructQueryAllField 查询数据,取所有字段
func StructQueryAllField() {
var sqlStr = "SELECT * FROM point_info limit ?"
rows, err := MysqlDb.Query(sqlStr, 10)
if err != nil {
fmt.Println(err.Error())
return
}
// 通过切片存储
pointInfos := make([]PointInfo, 0)
// 遍历
var pointInfo PointInfo
for rows.Next() {
err := rows.Scan(&pointInfo.Id, &pointInfo.ProductKey, &pointInfo.DeviceName, &pointInfo.PointId)
if err != nil {
fmt.Println(err.Error())
return
}
pointInfos = append(pointInfos, pointInfo)
}
fmt.Println(pointInfos)
}
// StructInsert 插入数据
func StructInsert() int64 {
var sqlStr = "insert INTO point_info(product_key, device_name, point_id) values(?, ?, ?)"
ret, err := MysqlDb.Exec(sqlStr, "product1", "device1", "point1")
if err != nil {
fmt.Println(err.Error())
return 0
}
lastInsertID, err := ret.LastInsertId()
if err != nil {
fmt.Println(err.Error())
return 0
}
fmt.Println("插入数据主键 LastInsertID:", lastInsertID)
rowsAffected, err := ret.RowsAffected()
if err != nil {
fmt.Println(err.Error())
return 0
}
fmt.Println("插入影响行数 RowsAffected:", rowsAffected)
return lastInsertID
}
// StructUpdate 更新数据
func StructUpdate(id int64) {
var sqlStr = "UPDATE point_info set product_key = ?, device_name = ?, point_id = ? where id = ?"
ret, err := MysqlDb.Exec(sqlStr, "product2", "device2", "point2", id)
if err != nil {
fmt.Println(err.Error())
return
}
updNums, err := ret.RowsAffected()
if err != nil {
fmt.Println(err.Error())
return
}
fmt.Println("更新影响行数 RowsAffected:", updNums)
}
// StructDel 删除数据
func StructDel(id int64) {
var sqlStr = "delete from point_info where id = ?"
ret, err := MysqlDb.Exec(sqlStr, id)
if err != nil {
fmt.Println(err.Error())
return
}
delNums, err := ret.RowsAffected()
if err != nil {
fmt.Println(err.Error())
return
}
fmt.Println("删除影响行数 RowsAffected:", delNums)
}