MySQL

MySQL is currently the most popular open-source relational database. This article will not elaborate on specific SQL knowledge; if you don't know SQL, please learn it first. This article only briefly explains how to use Go to perform SQL operations. In projects, you generally don't use the driver directly for database operations but instead use an ORM framework. Here we use the sqlx library, which enhances the standard sql library without the rich ORM features but wins in simplicity. If you want to use an ORM, you can look into libraries like Gorm, Xorm, or Ent.

Dependencies

Download the sqlx library:

$ go get github.com/jmoiron/sqlx

sqlx and the standard library database/sql support not only MySQL but any database that implements the driver.Driver interface, such as:

• PostgreSQL
• Oracle
• MariaDB
• SQLite
• And other relational databases

To use a specific database, you need to implement a database driver. The driver can be one you wrote yourself or a third-party library. Before using it, you must register the driver with sql.Register before you can use it. However, most downloaded driver libraries will automatically register the driver, so you don't need to register it manually.

func Register(name string, driver driver.Driver)

Since MySQL is popular and simple, this article uses MySQL for explanation. Other relational databases operate similarly. Download the MySQL driver library:

$ go get github.com/go-sql-driver/mysql

Connecting to the Database

Using the sqlx.Open function, you can open a database connection. It takes two parameters: the first is the driver name, and the second is the data source (commonly abbreviated as DSN).

func Open(driverName, dataSourceName string) (*DB, error)

The driver name is the name used when registering the driver and must be consistent. The DSN is the database connection address, which may vary for each database. For MySQL, it looks like this:

db,err := sqlx.Open("mysql","root:123456@tcp(127.0.0.1:3306)/test")

Preparing Data

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  `age` tinyint(0) NULL DEFAULT NULL,
  `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('12132', '张三', 35, '北京市');
INSERT INTO `user` VALUES ('16162', '王五', 22, '上海市');

SET FOREIGN_KEY_CHECKS = 1;

Query

Query and map results to a struct:

var db *sqlx.DB

type Person struct {
   UserId   string `db:"id"`
   Username string `db:"name"`
   Age      int    `db:"age"`
   Address  string `db:"address"`
}

func init() {
    conn, err := sqlx.Open("mysql", "root:wyh246859@tcp(127.0.0.1:3306)/test")
   if err != nil {
      fmt.Println("Open mysql failed", err)
      return
   }

   db = conn
}

func main() {
   query()
   defer db.Close()
}

func query() {
   var person Person
   // Get for single, Select for multiple
   err := db.Get(&person, "select * from user where id = ?", "12132")
   if err != nil {
      fmt.Println("query failed:", err)
      return
   }
   fmt.Printf("query succ:%+v", person)
}

func list() {
  var perons []Person
  err := db.Select(&perons, "select * from user")
  if err != nil {
    fmt.Println("list err", err)
    return
  }
  fmt.Printf("list succ,%+v", perons)
}

Insert

Insert data:

func insert() {
   result, err := db.Exec("insert into user value (?,?,?,?)", "120230", "李四", 12, "广州市")
   if err != nil {
      fmt.Println("insert err:", err)
      return
   }
   id, err := result.LastInsertId()
   if err != nil {
      fmt.Println("insert err:", err)
      return
   }
   fmt.Println("insert succ:", id)
}

Update

Update data:

func update() {
   res, err := db.Exec("update user set name = ? where id = ?", "赵六", "120230")
   if err != nil {
      fmt.Println("update err:", err)
      return
   }
   eff, err := res.RowsAffected()
   if err != nil || eff == 0 {
      fmt.Println("update err:", err)
      return
   }
   fmt.Println("Update succ")
}

Delete

Delete data:

func delete() {
   res, err := db.Exec("delete from user where id = ?", "120230")
   if err != nil {
      fmt.Println("delete err:", err)
      return
   }
   eff, err := res.RowsAffected()
   if err != nil || eff == 0 {
      fmt.Println("delete err:", err)
      return
   }
   fmt.Println("delete succ")
}

Transactions

func (db *DB) Begin() (*Tx, error) // Start a transaction
func (tx *Tx) Commit() error // Commit a transaction
func (tx *Tx) Rollback() error // Rollback a transaction

When starting a transaction, for safety, it's common to add defer tx.Rollback(). If an error occurs during the process, it will rollback. If the transaction is successfully committed, this rollback becomes ineffective.

func main() {

  transation, err := db.Begin()
  if err != nil {
    fmt.Println("transation err")
  }
    defer transation.Rollback()

  insert()
  query()
  update()
  query()
  delete()
   transation.Commit()
}