introduce
There are many databases that provide storage capacity for the system, including relational and non-relational
The mainstream relational database has Oracle, DB2, MySQL, Microsoft SQL Server, Microsoft Access…
Non-relational mongodb, Redis, CouchDB…
The Database/SQL package, the interface to operate the database, is provided in the Go Lang, and no specific database driver is provided
A database driver must be injected when using the Database/SQL package (there must be a driver)
Operational data
Use the Go-SQL-driver driver
Installing the Database Driver
go get -u github.com/go-sql-driver/mysql
Copy the codeInitialize the database connection
Establishing connections is provided using database/ SQL
func Open(driverName, dataSourceName string) (*DB, error) {}
Copy the code- DriverName specifies the driverName
- DataSourceName Database connection address
- Returns the operation database DB
Initialization, get DB
func InitDivider(a) (db *sql.DB) {
url := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s? charset=utf8"."root"."123456"."127.0.0.1"."3306"."moose")
db, err := sql.Open("mysql", url)
iferr ! =nil {
fmt.Println(err)
return
}
return db
}
Copy the code- Url contains the account, password, address, port, and database name of the data
Set up table
Create a T_user_INFO user information table using the previous database script
DROP TABLE IF EXISTS `t_user_info`;
CREATE TABLE `t_user_info` (
`user_id` bigint(20) NOT NULL COMMENT 'user Id'.`username` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT ' ' COMMENT 'Username'.`account_id` bigint(20) NOT NULL.`account_name` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT ' ' COMMENT 'Username'.`phone` varchar(11) COLLATE utf8_bin NOT NULL DEFAULT ' ' COMMENT 'Mobile phone Number'.`gender` char(1) COLLATE utf8_bin NOT NULL COMMENT 'Male 1; Female 2; un_known or hide 0'.`avatar` varchar(255) COLLATE utf8_bin NOT NULL COMMENT 'avatar'.`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time'.`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update Time',
PRIMARY KEY (`user_id`),
UNIQUE KEY `uniq_phone` (`phone`),
UNIQUE KEY `uniq_user_id` (`user_id`),
UNIQUE KEY `uniq_username` (`username`),
UNIQUE KEY `uniq_account_id` (`account_id`),
UNIQUE KEY `uniq_account_name` (`account_name`),
KEY `idx_create_time` (`create_time`),
KEY `idx_update_time` (`update_time`))ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='User Information Table';
Copy the codeMore database script addresses
Gitee.com/shizidada/m…
All the following operations are performed
Prerequisite: Create a test query method and pass in the operation database handle (DB)
increase
func TestInsert(db *sql.DB) {
sql := `INSERT INTO t_user_info(user_id, username, account_id, account_name, gender, phone, avatar) values(? ,? ,? ,? ,? ,? ,?) `
_, err := db.Exec(sql, "1"."Test user"."10"."Test Account".1."18732132132"."https://moose-plus.oss-cn-shenzhen.aliyuncs.com/avatar.png")
iferr ! =nil {
fmt.Println(err)
return
}
fmt.Println("Added successfully")}func main(a) {
db := InitDivider()
defer db.Close()
TestInsert(db)
}
Copy the codeλ go run main. Go "added successfully"Copy the codeIf the ID is self-growing, the first parameter of db.exec gets the return result
func (db *DB) Exec(query string, args ...interface{}) (Result, error) {}
// A Result summarizes an executed SQL command.
type Result interface {
// LastInsertId returns the integer generated by the database
// in response to a command. Typically this will be from an
// "auto increment" column when inserting a new row. Not all
// databases support this feature, and the syntax of such
// statements varies.
LastInsertId() (int64, error)
// RowsAffected returns the number of rows affected by an
// update, insert, or delete. Not every database or database
// driver may support this.
RowsAffected() (int64, error)
}
Copy the codeThere is also a newly added data in the database
Modify the
func TestUpdate(db *sql.DB) {
sql := `UPDATE t_user_info SET username = ? WHERE user_id = ? `
result, err := db.Exec(sql, "Change the name"."1")
iferr ! =nil {
fmt.Println(err)
return
}
fmt.Println(result)
}
Copy the code
delete
func TestDelete(db *sql.DB) {
sql := `DELETE FROM t_user_info WHERE user_id = ? `
result, err := db.Exec(sql, "1")
iferr ! =nil {
fmt.Println(err)
return
}
fmt.Println(result)
}
Copy the codeCheck the database. The data has been deleted
The query
The UserInfo structure
type UserInfo struct {
UserId string
UserName string
Avatar string. }Copy the codefunc TestQueryRow(db *sql.DB) {
/ / query SQL
sql := "select user_id, avatar, username from t_user_info"
var userInfo UserInfo
err := db.QueryRow(sql).Scan(&userInfo.UserId, &userInfo.UserName, &userInfo.Avatar)
iferr ! =nil {
fmt.Println("sacn error :: ", err)
return
}
fmt.Println(userInfo)
}
func main(a) {
db := InitDivider()
// Delay closure, after execution in main method
defer db.Close()
TestQueryRow(db)
}
Copy the codeLambda go run main. Go {785919644501544960} river https://moose-plus.oss-cn-shenzhen.aliyuncs.com/2020-12-23/avatar.pngCopy the codeSelect field1, field2, field3 and Scan(field1, field2, field3)
Query returns multiple pieces of data
func TestQuery(db *sql.DB) {
sql := "select user_id, avatar, username from t_user_info where user_id IN(? ,?) "
rows, err := db.Query(sql, "785919644501544960"."790883082524954600")
iferr ! =nil {
fmt.Println(err)
return
}
for rows.Next() {
var userInfo UserInfo
err = rows.Scan(&userInfo.UserId, &userInfo.UserName, &userInfo.Avatar)
iferr ! =nil {
fmt.Println("Sending error")
return
}
fmt.Println(userInfo)
}
}
Copy the codeLambda go run main. Go {785919644501544960} river https://moose-plus.oss-cn-shenzhen.aliyuncs.com/2020-12-23/avatar.png {790883082524954600 https://default.png Li Bai}Copy the code-
Use Query to Query multiple entries
-
Query The Query result is returned
func (*sql.DB).Query(query string, args ...interface{}) (*sql.Rows, error)
Copy the code- Iterate over Rows and use Next to determine if there is another entry
func (rs *Rows) Next(a) bool
Copy the code- Data encapsulation and single data consistency Scan
Sql Injection Problems
Execute SQL statements by using some combination of SQL syntax to return the desired result
Prepare(SQL) is used to precompile the SQL and return Stmt. Stmt is used to perform database operations
func (db *DB) Prepare(query string) (*Stmt, error). stmt, err := db.Prepare(sql) stmt.Exec() ...Copy the code
Database transaction
Execute an SQL, guarantee atomicity, all success or all failure
func (db *DB) Begin(a) (*Tx, error)
tx, _ := db.Begin()
// db.BeginTx()
tx.Commit()
tx.Rollback()
Copy the codeStart transaction db.begin () before SQL execution. If tx.rollback () is required, tx.mit () is not required.
TODO
Go is still a bit troublesome in the process of operating databases. There are also third-party packages on the market that encapsulate operational data, as well as some ORM frameworks that simplify data operations.