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 code

Initialize 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 code

More 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 code

If 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 code

There 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 code

Check the database. The data has been deleted

The query

The UserInfo structure

type UserInfo struct {
	UserId   string
	UserName string
	Avatar   string. }Copy the code
func 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 code
Lambda go run main. Go {785919644501544960} river https://moose-plus.oss-cn-shenzhen.aliyuncs.com/2020-12-23/avatar.pngCopy the code

Select 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 code
Lambda 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 code

Start 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.