GoLang之MySQL基本操作

「爱情、让人受尽委屈。」 2021-12-12 15:43 253阅读 0赞

### 1.数据库字段结构 ###

mysql表字段如下图所示：

![20190712135841658.png][]

### 2.GoLang代码 ###

package db
    
    import (
    	"database/sql"
    	"fmt"
    	_ "github.com/go-sql-driver/mysql"
    )
    
    type DBConnect struct {
    	DBUrl string
    	DB *sql.DB
    	student student
    }
    
    type student struct {
    	id int
    	name string
    	gender string
    	age int
    	position string
    }
    
    func Start()  {
    	db := DBConnect{
    		DBUrl:"root:TI1mo1j2qq6.@(127.0.0.1:3306)/student?charset=utf8mb4",
    	}
    	var err error
    	db.DB, err = sql.Open("mysql", db.DBUrl)
    	if err != nil {
    		fmt.Printf("sql connect error : %s", err)
    		return
    	}
    	fmt.Println(db.DBUrl)
    	fmt.Println(db.DB.Stats())
    	db.queryStudent()
    	newStudent := &student{
    		id:10003,
    		name:"赵小海",
    		gender:"男",
    		age:22,
    		position:"无",
    	}
    	db.insertStudent(newStudent)
    	db.deleteStudent(10001)
    	updateStudent := &student{
    		id:10002,
    		name:"刘小红",
    		gender:"女",
    		age:18,
    		position:"团支书",
    	}
    	db.updateStudent(updateStudent)
    }
    
    func (db *DBConnect) queryStudent() {
    	// pre用法
    	//sqlPre, _ := db.DB.Prepare("select * from student where age > ? and age < ?")
    	//students, err := sqlPre.Query(18, 22)
    	students, err := db.DB.Query("select * from class1")
    	if err != nil{
    		fmt.Printf("sql query error : %s", err)
    	}
    	defer students.Close()
    	for i:= 0; students.Next(); i ++ {
    		students.Scan(&db.student.id, &db.student.name, &db.student.gender, &db.student.age, &db.student.position,)
    		fmt.Println("id:",db.student.id, "name:", db.student.name, "gender:", db.student.gender, "age", db.student.age, "position:", db.student.position)
    	}
    }
    
    func (db *DBConnect) insertStudent(student *student)  {
    	insertStu, _ := db.DB.Prepare("insert into class1 values(?,?,?,?,?)")
    	defer insertStu.Close()
    	result, err := insertStu.Exec(&student.id, &student.name, &student.gender, &student.age, &student.position)
    	if err != nil {
    		fmt.Printf("sql insert error : %s", err)
    		return
    	}
    	rowNum, _ := result.RowsAffected()
    	fmt.Printf("影响行数：%d\n", rowNum)
    }
    
    func (db *DBConnect) deleteStudent(id int){
    	deleteStu, _ := db.DB.Prepare("delete from class1 where id = ?")
    	defer deleteStu.Close()
    	result, err := deleteStu.Exec(id)
    	if err != nil {
    		fmt.Printf("sql delete error : %s", err)
    	}
    	rowNum, _ := result.RowsAffected()
    	fmt.Printf("影响行数：%d\n", rowNum)
    }
    
    func (db *DBConnect) updateStudent(newstudent *student)  {
    	updateStu, _ := db.DB.Prepare("update class1 set name = ?, gender = ?, age = ?, position = ? where id = ?")
    	defer updateStu.Close()
    	result, err := updateStu.Exec(&newstudent.name, &newstudent.gender, &newstudent.age, &newstudent.position, &newstudent.id)
    	if err != nil {
    		fmt.Printf("sql insert error : %s", err)
    		return
    	}
    	rowNum, _ := result.RowsAffected()
    	fmt.Printf("影响行数：%d\n", rowNum)
    }

### 3.运行效果 ###

运行效果图如下所示：

![20190712140056204.png][]

[20190712135841658.png]: /images/20211210/b66cf5f0bd62463895e9dc11fbf2bcdb.png
[20190712140056204.png]: /images/20211210/295687bd2bfb4607a73417a7d2953bcf.png