Using Go language to operate MySQL database

Keywords: Go MySQL Database SQL Navicat

Recently, I learned to use Go language to operate MySQL database to add, delete, modify and query user data when I was doing the registration and login service. Now I summarize my personal learning experience as follows, and attach Code warehouse address Welcome to fork.

Software environment: Goland, Navicat for MySQL.

1, Realization ideas

1. My overall design idea is to write out the logic of connecting and closing the database first, then establish four branches, and enter the operation of adding, deleting, modifying and querying respectively according to the user's choice;

func DBstart() {
	db,_= sql.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/dbtest?charset=utf8") //dbtest is my new database name

	fmt.Println("mysql start succcessed !")
}

 

func DBclose(){
	//close database
	db.Close()
	fmt.Println("database closed")
}

  

2. Added operation

Input corresponding data

	fmt.Println("Please enter user ID: ")
	fmt.Scan(&Userid)
	fmt.Println("Please enter a name:")
	fmt.Scan(&Username)
	fmt.Println("Please enter Department:")
	fmt.Scan(&Departname)
	fmt.Println("Please enter the time of entering the Department")
	fmt.Scan(&Created)


	//insert data
	stmt,err := db.Prepare("INSERT userif SET userid=?,username=?,departname=?,created=?")
	CheckErr(err)

	res,err := stmt.Exec(Userid,Username,Departname,Created)
	CheckErr(err)

	id,err := res.LastInsertId()

	fmt.Println(id)

  

3. Query operation

	rows,err := db.Query("SELECT * FROM userif")
	CheckErr(err)

	for rows.Next(){
		err = rows.Scan(&Id,&Userid,&Created,&Departname,&Username)
		CheckErr(err)
		fmt.Println(Userid)
		fmt.Println(Username)
		fmt.Println(Departname)
		fmt.Println(Created)
}

  

4. Update operation

Enter the content to be updated according to the prompt, and then go to the corresponding update statement

	fmt.Println("Please enter the user to query ID: ")
	fmt.Scan(&Userid)
	fmt.Println("Please enter what to update: a-Name; b-Department; c-Time of entering the Department:")
	var s string
	fmt.Scan(&s)
	switch s {
	case "a":
		fmt.Println("Please enter the user to change ID by%d Name",Userid)
		fmt.Scan(&Username)
		fmt.Println(Username)
		fmt.Println(Userid)
		stmt,err := db.Prepare("UPDATE userif SET username=? where userid=?")
		CheckErr(err)
                ......//And so on
}

  

5. Delete

	fmt.Println("Please enter the user to delete ID: ")
	fmt.Scan(&Userid)
	stmt,err := db.Prepare("DELETE from userif where userid=?")
	CheckErr(err)

	res,err := stmt.Exec(Userid)
	CheckErr(err)

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

	fmt.Println(affect)

 

2, Attention points

1. In the search operation, pay attention to whether the column names corresponding to the input and output are the same as those in the MySQL data. Otherwise, the following errors may be caused

 

 

3, References

go-sql-driver

Posted by Potatis on Sat, 04 Jan 2020 03:05:34 -0800