数据库:
CREATE DATABASE Customer;#创建数据库
USE customer;#使用数据库
CREATE TABLE Customer(id INT PRIMARY KEY AUTO_INCREMENT ,NAME VARCHAR(11),sex VARCHAR(4),age INT ,phone VARCHAR(20), email VARCHAR(20));#非空,唯一标识,自增,添加字段
INSERT INTO Customer (NAME,sex,age,phone , email) VALUES('tom','男',18,'123123','123123@qq.com');#添加信息
SELECT * FROM customer;#查询信息
DESC customer;#查看表结构
package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
"log"
"time"
)
// 结构体
type customer struct {
id int
name string
sex string
age int
phone string
email string
}
type customers struct {
customer
customers []customer
}
const (
drivername = "mysql"
driver = "root:root@tcp(localhost:3306)/customer?charset=utf8"
)
var DB = &sql.DB{}
func init() {
DB, _ = sql.Open(drivername, driver)
if err := DB.Ping(); err != nil {
fmt.Println(err)
return
}
DB.SetConnMaxLifetime(100 * time.Second) // 最大连接周期,超过时间的连接就close
DB.SetMaxOpenConns(100) // 设置最大连接数
DB.SetMaxIdleConns(16) // 设置闲置连接数
}
// 错误信息
func CheckErr(err error) {
if err != nil {
log.Fatalln(err)
}
}
// 回滚
func clearTransaction(tx *sql.Tx) {
err := tx.Rollback()
if err != sql.ErrTxDone && err != nil {
log.Fatalln(err)
}
}
// 查询customer
func (this *customers) Query(DB *sql.DB) []customer {
// 查询语句
Rows, err := DB.Query("select * from customer")
CheckErr(err)
defer Rows.Close()
// 清空切片内容 多次使用切片,查询结果会累加
this.customers = append(this.customers[:0], this.customers[len(this.customers):]...)
for Rows.Next() {
err = Rows.Scan(&this.id, &this.name, &this.sex, &this.age, &this.phone, &this.email)
CheckErr(err)
this.customers = append(this.customers, this.customer)
}
return this.customers
}
// 添加 返回受影响行数
func (this *customers) Increase(customer customer, tx *sql.Tx) int64 {
/*因为stmt.Close使用defer语句,即函数退出的时候再清理stmt,可是实际执行过程的时候,tx.Commit就已经释放了连接。
当函数退出的时候,再执行stmt.Close的时候,连接可能有被使用了。*/
// stmt, err := tx.Prepare("insert customer set name = ?,sex = ?,age = ?,phone = ?,email = ?")
// CheckErr(err)
// defer stmt.Close()
//
// result, err := stmt.Exec(customer.name, customer.sex, customer.age, customer.phone, customer.email)
// CheckErr(err)
// err = tx.Commit()
// CheckErr(err)
// LastInsertId返回自增的序号
// RowsAffected返回受影响行数
// num, _ := result.RowsAffected()
result, err := tx.Exec("insert customer set name = ?,sex = ?,age = ?,phone = ?,email = ?",
customer.name, customer.sex, customer.age, customer.phone, customer.email)
CheckErr(err)
num, _ := result.RowsAffected() // 返回受影响行数
if err := tx.Commit(); err != nil {
// tx.Rollback()
log.Fatalln(err)
}
return num
}
func main() {
// 实例化
this := customers{}
customer := customer{
name: "jack~~",
sex: "女",
age: 18,
phone: "1231231232123",
email: "1231232123123",
}
// 开启事务
Tx, err := DB.Begin()
CheckErr(err)
defer clearTransaction(Tx)
// 查询
fmt.Println("*******************查询*******************")
fmt.Println("序号\t姓名\t性别\t年龄\t电话\tEmail")
for _, v := range this.Query(DB) {
fmt.Println(v.id, "\t", v.name, "\t", v.sex, "\t", v.age, "\t", v.phone, "\t", v.email)
}
fmt.Println("******************查询完成******************")
fmt.Println()
// 添加
fmt.Println("*******************添加*******************")
fmt.Println(this.Increase(customer, Tx))
if this.Increase(customer, Tx) != 1 {
fmt.Println("******************添加失败******************")
}
fmt.Println("******************添加完成******************")
fmt.Println()
// 修改同等
// 删除同等
}