Go language to access the mysql database requires the standard library database/ SQL and mysql drivers. The standard library Api is a bit cumbersome to use and here is another library github.com/jmoiron/sqlx.

go get github.com/jmoiron/sqlx
go get github.com/go-sql-driver/mysql
Copy the code

Connecting to a Database

db, err := sqlx.Connect("mysql"."... ? parseTime=true")
iferr ! =nil {
	log.Panicln("db err: ", err.Error())
}
Copy the code

Connect to the database using SQLX’s Connect method. Connect automatically confirms whether the connection is successful. Err returns an error message if an error occurs. You can connect directly in functions that need to connect to the database, or you can use Gin-based Golang Web development: Access the Go language init mechanism described in the mysql database to get the connection.

Increases the deletion

Add, delete, or modify mysql uses the DB Exec function, passing in SQL statements and query parameters. SQLX also provides the MustExec function, which triggers panic instead of returning error in the event of an SQL statement execution error.

db, err := sqlx.Connect("mysql"."... ? parseTime=true")
iferr ! =nil {
	log.Panicln("db err: ", err.Error())
}
defer db.Close()

insertSql := ` INSERT INTO sys_user ( username, PASSWORD ) VALUES (? ,?) ; `
result, err := db.Db.Exec(insertSql, username, password)
iferr ! =nil {
	log.Panicln("add user err: ", err.Error())
}

updateSql := ` update sys_user set username = ? where id = ? ; `
result1, err1 := db.Db.Exec(updateSql, username, id)
iferr1 ! =nil {
	log.Panicln("update user by id err: ", err.Error())
}

deleteSql := `
delete from sys_user
where id = ?
`
result2, err2 := db.Db.Exec(deleteSql, id)
iferr2 ! =nil {
	log.Panicln("delete user by id err: ", err.Error())
}
Copy the code

The result interface of the Exec function contains two functions, LastInsertId, RowsAffected, that verify the result of an SQL statement by judging RowsAffected > 0. Note that RowsAffected is not supported by all databases or drivers, and if you are not using mysql you need to check whether your environment supports RowsAffected.

check

SQLX support makes it easy to convert database query results into GO structures, where Get and Select are used to Get single results and multiple results, respectively.

type SysUser struct {
	Id            int         `json:"id"`
	Username      null.String `json:"username"`
	Password      null.String `json:"password"`                             // Login password
}

func GetAllUser(request GetAllUserRequest) (list []SysUser) {
	strSql := ` select id, username, password from sys_user `
	err = db.Db.Select(&list, strSql)
	iferr ! =nil {
		log.Panicln("select sys_user err: ", err.Error())
	}

	return
}

func GetById(id int) (user SysUser) {
	sysUser := SysUser{}
	dataSql := ` select id, username, password from sys_user where id = ? `
	err := db.Db.Get(&sysUser, dataSql, id)
	iferr ! =nil {
		log.Panicln("get user by id err: ", err.Error())
	}
	return sysUser
}
Copy the code

The Select method retrieves all the results into memory and converts them to the target structure, which can be paged if the results contain a large amount of data, or can be iterated using the Query/StructScan method.

Golang Web development based on Gin: mysql add, delete, change, check