How to use Go language for MySQL data sharding operation

With the rapid development of the Internet, the number of users of Web applications continues to increase, and the amount of data continues to expand. In order to cope with this situation, using data sharding technology to achieve horizontal data segmentation has become a common database expansion solution. This article will introduce how to use Go language to perform MySQL data sharding operations.

1. What is data sharding

Data sharding is a data management technology that breaks a large data set into multiple smaller parts. When the amount of data is huge, horizontal segmentation of data can be achieved by decomposing the data set, thereby reducing the pressure on individual nodes and improving the performance stability of web applications.

2. Go language implements sharded access to MySQL

Go is a modern programming language that supports high concurrency. Because it has very high execution efficiency and excellent concurrency characteristics, it is used in database programs is receiving more and more attention in its development. The following is an example of using Go language to access MySQL for data sharding operations.

1. Establishing a database connection

To connect to the MySQL database in a Go program, you need to use the corresponding driver and connection information. To open a database connection, you need to execute the Open method, and to close the connection, you need to execute the Close method. The following is a sample code to establish a database connection:

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

// 数据库信息
const (
    USERNAME = "root"
    PASSWORD = "password"
    NETWORK  = "tcp"
    SERVER   = "127.0.0.1"
    PORT     = 3306
    DATABASE = "test"
)

func main() {
    // 数据库连接
    db, err := sql.Open("mysql", USERNAME+":"+PASSWORD+"@"+NETWORK+"("+SERVER+":"+strconv.Itoa(PORT)+")/"+DATABASE+"?charset=utf8")
    if err != nil {
        fmt.Println("Connection Failed:", err.Error())
        return
    }

    // 初始化数据库
    defer db.Close()
}

2. Create a sharded table

A sharded table usually includes the sharding key and the table name of each data shard, etc. information. Among them, the shard key can be any field and is mainly used to determine which shard the data is stored on. The following is a sample code to create a sharded table:

// 分片表信息
const TABLE_NAME = "user_info"
const SHARD_KEY = "user_id"

// 根据分片信息生成的各个分片表名称的列表
var shardTableList []string

func main() {
    // 创建分片表
    tableSql := fmt.Sprintf("CREATE TABLE `%s` (`id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `username` varchar(20) DEFAULT '', `password` varchar(50) DEFAULT '', PRIMARY KEY (`id`), KEY `user_id` (`user_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;", TABLE_NAME)
    _, err := db.Exec(tableSql)
    if err != nil {
        fmt.Println("Database initialization failed:", err.Error())
        return
    }

    // 生成分片表
    for i := 0; i < 4; i++ {
        shardTableName := fmt.Sprintf("%s_%d", TABLE_NAME, i)
        shardTableSql := fmt.Sprintf("CREATE TABLE `%s` (`id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `username` varchar(20) DEFAULT '', `password` varchar(50) DEFAULT '', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;", shardTableName)
        shardTableList = append(shardTableList, shardTableName)
        _, err := db.Exec(shardTableSql)
        if err != nil {
            fmt.Println("Database initialization failed:", err.Error())
            return
        }
    }
}

3. Insert data

After implementing data sharding, determine that the data should be inserted by judging the sharding key into which shard table. The following is a sample code for inserting data into a sharded table:

// 插入数据操作
func insertData(userId int, userName string, passWord string) error {
    shardIndex := userId % 4
    sqlStr := fmt.Sprintf("insert into %s (`user_id`,`username`,`password`) values (?,?,?)", shardTableList[shardIndex])
    _, err = db.Exec(sqlStr, userId, userName, passWord)
    if err != nil {
        fmt.Printf("Insert data failed. Error: %v
", err.Error())
        return err
    }

    fmt.Printf("Insert data success. userId=%d, userName=%s, passWord=%s
", userId, userName, passWord)
    return nil
}

func main() {
    // 插入数据
    insertData(1, "user1", "pass1")
    insertData(2, "user2", "pass2")
    insertData(3, "user3", "pass3")
    insertData(4, "user4", "pass4")
}

4. Querying data

When querying data, you need to determine whether the data is corresponding to the sharding key. in the fragmented table. If the data is not in the sharded table, you need to continue searching in other related sharded tables. The following is a sample code for querying sharded table data:

// 查询数据操作
func queryData(userId int) (string, error) {
    shardIndex := userId % 4
    sqlStr := fmt.Sprintf("select `username`,`password` from %s where `user_id`=?", shardTableList[shardIndex])
    rows, err := db.Query(sqlStr, userId)
    if err != nil {
        fmt.Printf("Query data failed. Error: %v
", err.Error())
        return "", err
    }

    var userName, passWord string
    rows.Next()
    err = rows.Scan(&userName, &passWord)
    if err != nil {
        fmt.Printf("Query data failed. Error: %v
", err.Error())
        return "", err
    }

    fmt.Printf("Query data success. userId=%d, userName=%s, passWord=%s
", userId, userName, passWord)
    return userName, nil
}

func main() {
    // 查询数据
    queryData(1)
}

3. Summary

This article introduces how to use Go language to perform MySQL data sharding operations, mainly including establishing database connections, creating Things like sharding tables, inserting data, and querying data. In actual development, the sharding strategies and methods will be different for different application scenarios and data volumes, and need to be adjusted accordingly according to the actual situation.

The above is the detailed content of How to use Go language for MySQL data sharding operation. For more information, please follow other related articles on the PHP Chinese website!