Use MySQL in Go language to manage distributed system data

With the development of the Internet, distributed systems have attracted more and more attention. The benefits of a distributed system are easy expansion and good fault tolerance, but it also brings data management problems. In a distributed system, multiple nodes read and write the same data at the same time, and data inconsistency is a common problem. As a result, data management becomes more complex. This article will introduce how to use MySQL in Go language to manage distributed system data.

The Go language provides good support for distributed systems. It also provides a database/SQL package that can be used to conveniently operate the database. MySQL is a very commonly used relational database that provides many advanced features, such as transactions, replication, topology, clustering, etc. Below we will introduce how to use MySQL to manage distributed system data.

Step one: Install MySQL

First you need to install MySQL, which can be downloaded and installed on the official website. In addition, you can also use some open source MySQL distributions, such as MariaDB. During the installation process, you need to pay attention to configuring related parameters such as password and port. Generally, the default user is root and the password is blank.

Step 2: Go language to connect to MySQL

In Go language, use the database/sql package and mysql driver to connect to the MySQL database. In the import statement, use database/sql and mysql driver packages:

import (
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
)

In the main function, we can use the following code to connect to MySQL:

db, err := sql.Open("mysql", "username:password@tcp(127.0.0.1:3306)/testdb")
if err != nil {
    log.Fatal(err)
}
defer db.Close()

err = db.Ping()
if err != nil {
    log.Fatal(err)
}

Among them, username and password are set by ourselves The specified MySQL user name and password, tcp (127.0.0.1:3306) indicates the MySQL service connected to the local host, and testbd is the name of the database to be connected.

Step 3: Create a data table

In MySQL, you can use the following SQL statement to create a table:

CREATE TABLE `books` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `author` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` varchar(1024) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

In this example, we created a table named books The table has four fields. id is the primary key, title, author and description are the title, author and description of the book.

Step 4: Add, delete, modify and query data

In Go language, you can use SQL statements to operate the MySQL database. The following is an example of adding, deleting, modifying and querying data using the Go language:

// insert
stmt, err := db.Prepare("INSERT INTO books(title, author, description) VALUES (?, ?, ?)")
if err != nil {
    log.Fatal(err)
}
res, err := stmt.Exec("Go语言编程", "张三", "Go语言的基础知识")
if err != nil {
    log.Fatal(err)
}

// retrieve
rows, err := db.Query("SELECT id, title, author, description FROM books")
if err != nil {
    log.Fatal(err)
}
defer rows.Close()
for rows.Next() {
    var id int
    var title string
    var author string
    var description string
    err = rows.Scan(&id, &title, &author, &description)
    if err != nil {
        log.Fatal(err)
    }
    fmt.Println(id, title, author, description)
}
err = rows.Err()
if err != nil {
    log.Fatal(err)
}

// update
stmt, err = db.Prepare("UPDATE books SET title=? WHERE id=?")
if err != nil {
    log.Fatal(err)
}
res, err = stmt.Exec("Go语言从入门到精通", 1)
if err != nil {
    log.Fatal(err)
}

// delete
stmt, err = db.Prepare("DELETE FROM books WHERE id=?")
if err != nil {
    log.Fatal(err)
}
res, err = stmt.Exec(1)
if err != nil {
    log.Fatal(err)
}

Here, we use the Prepare and Exec functions to perform insert, query, update, and delete operations. In fact, these operations can be combined using transactions to ensure data consistency. In the Go language, transactions can be used in the following ways:

tx, err := db.Begin()
if err != nil {
    log.Fatal(err)
}

stmt, err := tx.Prepare("INSERT INTO books(title, author, description) VALUES (?, ?, ?)")
if err != nil {
    tx.Rollback()
    log.Fatal(err)
}
defer stmt.Close()

res, err := stmt.Exec("Go语言编程", "张三", "Go语言的基础知识")
if err != nil {
    tx.Rollback()
    log.Fatal(err)
}

stmt, err = tx.Prepare("UPDATE books SET title=? WHERE id=?")
if err != nil {
    tx.Rollback()
    log.Fatal(err)
}
res, err = stmt.Exec("Go语言从入门到精通", 1)
if err != nil {
    tx.Rollback()
    log.Fatal(err)
}

err = tx.Commit()
if err != nil {
    tx.Rollback()
    log.Fatal(err)
}

In the above example, we first open a transaction, then perform insert and update operations in the transaction, and finally commit the transaction. If an error occurs, the transaction can be rolled back.

Summary

In this article, we introduced how to use MySQL in the Go language to manage distributed system data. Through the operation of Go language and MySQL, we can easily add, query, update and delete data, and at the same time, we can also use transactions to ensure data consistency. When using MySQL, you need to pay attention to configuration parameters, such as user name, password, port, etc. In addition, you also need to pay attention to problems caused by concurrent operations, such as locks.

The above is the detailed content of Use MySQL in Go language to manage distributed system data. For more information, please follow other related articles on the PHP Chinese website!