Go language and MySQL database: How to convert data into rows and columns?

As the amount of data continues to grow, data processing and analysis have become indispensable and important businesses for modern enterprises. The database is the core of data processing and management. As a relational database with excellent performance, powerful functions and ease of use, MySQL is chosen by more and more enterprises. The Go language has become a programming language favored by more and more enterprises and developers because of its advantages such as high efficiency, ease of development, and high degree of concurrency. How to convert data rows and columns in MySQL database in Go language? This article will explain it to you.

1. Data types in the MySQL database

Before performing data row and column conversion processing, we first need to understand the data types in the MySQL database. MySQL supports a variety of data types, including numeric types, string types, time and date types, etc. The specific MySQL data types and their corresponding Go language types are shown in the following table:

MySQL data type	Go language type
INT	int64
FLOAT, REAL	float64
CHAR, VARCHAR, TEXT	string
DATE, TIME, DATETIME, TIMESTAMP	time.Time
BLOB	[]byte

2. Store data in the MySQL database

In Go language , we can use a third-party MySQL driver to connect to the MySQL database. Here we use the github.com/go-sql-driver/mysql package to connect to the MySQL database.

First, we need to define the connection string, including the database username and password, server IP address and port, database name and other information.

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

const (
    USERNAME = "root"
    PASSWORD = "12345678"
    NETWORK  = "tcp"
    SERVER   = "localhost"
    PORT     = 3306
    DATABASE = "test"
)

func Connect() (*sql.DB, error) {
    connStr := fmt.Sprintf("%s:%s@%s(%s:%d)/%s", USERNAME, PASSWORD, NETWORK, SERVER, PORT, DATABASE)
    db, err := sql.Open("mysql", connStr)
    if err != nil {
        return nil, err
    }
    return db, nil
}

We defined the Connect function to connect to the MySQL database and return an object of type sql.DB. Next, we can define a structure in the Go language to represent the data we want to store. Taking student information as an example, the structure is defined as follows:

type student struct {
    id   int
    name string
    age  int
}

Next, we can insert a piece of student information into the MySQL database through the following code:

func InsertStudent(db *sql.DB, stu *student) error {
    sql := "INSERT INTO student (name, age) VALUES (?, ?)"
    stmt, err := db.Prepare(sql)
    if err != nil {
        return err
    }
    defer stmt.Close()
    _, err = stmt.Exec(stu.name, stu.age)
    if err != nil {
        return err
    }
    return nil
}

The above code defines an InsertStudent function, Insert a student object into the student table in the MySQL database. In advance, we define the student table in the database as (name VARCHAR(50), age INT).

3. Query the data in the MySQL database

In the Go language, we can use query to execute SQL statements. The following is how to query student information in the student table:

func QueryStudent(db *sql.DB) ([]*student, error) {
    students := make([]*student, 0)
    rows, err := db.Query("SELECT * FROM student")
    if err != nil {
        return nil, err
    }
    defer rows.Close()
    for rows.Next() {
        stu := new(student)
        err := rows.Scan(&stu.id, &stu.name, &stu.age)
        if err != nil {
            return nil, err
        }
        students = append(students, stu)
    }
    if err = rows.Err(); err != nil {
        return nil, err
    }
    return students, nil
}

The above code defines a QueryStudent function, which is used to query all student information in the student table in the MySQL database. The function returns an array of student information.

4. Convert data rows into data columns

Sometimes we need to convert data rows into data columns. For example, we need to display 4 student information in each row in the student information table. The following is how to convert an array of student information into a two-dimensional array of student information:

func ToColumnArray(students []*student, n int) [][]*student {
    result := make([][]*student, (len(students)+n-1)/n)
    for i := range result {
        result[i] = make([]*student, n)
    }
    for i, s := range students {
        row, col := i/n, i%n
        result[row][col] = s
    }
    return result
}

The above code defines a ToColumnArray function to convert an array of student information into a two-dimensional array of student information, with each row containing n student information.

5. Convert data columns into data rows

On the other hand, we also need to convert data columns into data rows. For example, we need to split a large table into multiple small tables. . The following is how to convert a two-dimensional array of student information into an array of student information:

func ToRowArray(students [][]*student) []*student {
    result := make([]*student, 0)
    for _, row := range students {
        for _, s := range row {
            if s != nil {
                result = append(result, s)
            }
        }
    }
    return result
}

The above code defines a ToRowArray function to convert a two-dimensional array of student information into an array of student information.

Summary

This article introduces how to connect to the MySQL database in Go language and perform data row and column conversion processing. In actual business, data processing and management are very critical. I hope this article can inspire you and help you better process and manage large amounts of data.

The above is the detailed content of Go language and MySQL database: How to convert data into rows and columns?. For more information, please follow other related articles on the PHP Chinese website!