Go language and MySQL database: How to divide data role attributes?

With the popularization of large-scale applications and the rapid development of Internet technology, databases have become an essential tool for data storage, management, and retrieval. MySQL is an open source and free relational database management system. At the same time, the Go language is favored by more and more developers due to its high concurrency performance and other advantages. In application scenarios, data roles and data attributes play an important role. In order to better manage data, the attributes of different data roles need to be divided. This article will focus on how to divide data roles and attributes with the MySQL database in the Go language.

1. Division of roles

In the database, data roles are usually divided according to business needs and how the data is used. For example, in an e-commerce platform, we can divide data roles into users, products, orders, etc. Different roles have different data attributes. In order to play the role of the MySQL database, we need to create corresponding tables to store data information for different roles.

1.1 Create a table

First, use MySQL to create the SQL statement of the user table as follows:

CREATE TABLE user(
   id INT PRIMARY KEY AUTO_INCREMENT,
   name VARCHAR(64) NOT NULL,
   age INT NOT NULL,
   gender VARCHAR(4)
);

Among them, user table It contains four attributes: id, name, age and gender. The id attribute is the primary key and is automatically added. Similarly, we can also set the properties of other tables according to business needs.

1.2 Data addition, deletion, modification and query

In the Go language, using MySQL to add, delete, modify and query data requires the use of SQL Statement, mainly involves the following two methods:

1. CREATE/INSERT/UPDATE/DELETE

##CREATE command is used to create a table. The INSERT command is used to add data to the table, the UPDATE command is used to update the table, and the DELETE command is used to delete some data in the table.

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

func main() {
  username := "root"
  password := "password"
  host := "localhost:3306"
  database := "db1"
  db, err := sql.Open("mysql", fmt.Sprintf("%s:%s@tcp(%s)/%s", username, password, host, database))
  if err != nil {
    panic(err)
  }
  defer db.Close()
  // CREATE 命令
  _, err = db.Exec("CREATE TABLE user(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(64) NOT NULL, age INT NOT NULL, gender VARCHAR(4))")
  if err != nil {
    panic(err)
  }
  // INSERT 命令
  _, err = db.Exec("INSERT INTO user(name, age, gender) VALUES (?, ?, ?)", "Tom", 18, "男")
  if err != nil {
    panic(err)
  }
  // UPDATE 命令
  _, err = db.Exec("UPDATE user SET gender = ? WHERE id = ?", "女", 1)
  if err != nil {
    panic(err)
  }
  // DELETE 命令
  _, err = db.Exec("DELETE FROM user WHERE id = ?", 1)
  if err != nil {
    panic(err)
  }
}

2. SELECT

SELECT command is used to query the data in the table. You can use Query or QueryRow method to execute SQL queries.

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

func main() {
  username := "root"
  password := "password"
  host := "localhost:3306"
  database := "db1"
  db, err := sql.Open("mysql", fmt.Sprintf("%s:%s@tcp(%s)/%s", username, password, host, database))
  if err != nil {
    panic(err)
  }
  defer db.Close()
  // 查询 user 表格中所有信息
  rows, err := db.Query("SELECT * FROM user")
  if err != nil {
    panic(err)
  }
  defer rows.Close()
  for rows.Next() {
    var id, age int
    var name, gender string
    err := rows.Scan(&id, &name, &age, &gender)
    if err != nil {
      panic(err)
    }
    fmt.Println(id, name, age, gender)
  }
  // 根据条件查询 user 表格中数据
  row := db.QueryRow("SELECT * FROM user WHERE id = ?", 2)
  var id, age int
  var name, gender string
  err = row.Scan(&id, &name, &age, &gender)
  if err != nil {
    if err == sql.ErrNoRows {
      fmt.Println("没有查询到记录")
      return
    }
    panic(err)
  }
  fmt.Println(id, name, age, gender)
}

Through the above code examples, we can perform common operations such as querying, adding, deleting and updating data.

2. Division of attributes

For data of different roles, the importance of each data attribute is also different. Differences in attribute types and ranges of attribute values ​​also have an impact on how the data is processed.

2.1 Selection of data type

In

MySQL, the data types mainly include INT, VARCHAR, DATE, FLOAT, DOUBLE, etc. Different data types occupy different storage spaces. When selecting attribute types, you need to reduce the storage space as much as possible, otherwise it will have a negative impact on database performance.

For example, when we need to store the user's age, we can choose to use the

INT type for storage, because the age value will not exceed 100, and the INT type range is -2147483648 to 2147483647, the storage space is smaller and the query is faster.

2.2 Range of attribute values ​​

In addition to the data type not being abused, the range of attribute values ​​also needs to be limited, especially when the data involves private information, such as user passwords.

For example, to add a password attribute to the user table, you can use the

VARCHAR(64) type, but the password length cannot be too long, otherwise it will increase storage overhead. At the same time, user passwords need to be protected, and generally need to be encrypted using a hash algorithm, such as MD5, SHA-1, SHA-256, etc.

import (
  "crypto/md5"
  "database/sql"
  "encoding/hex"
  "fmt"
  _ "github.com/go-sql-driver/mysql"
)

func main() {
  password := "123456"
  salt := "random_generated_salt"
  hash := md5.Sum([]byte(password + salt))
  passwordHash := hex.EncodeToString(hash[:])
  fmt.Println(passwordHash)
  // 825b5f05eb9584b068ce830d0983e7c9
  // 使用哈希算法加密密码，将其值存储到数据库中
  username := "root"
  password := "password"
  host := "localhost:3306"
  database := "db1"
  db, err := sql.Open("mysql", fmt.Sprintf("%s:%s@tcp(%s)/%s", username, password, host, database))
  if err != nil {
    panic(err)
  }
  defer db.Close()
  _, err = db.Exec("INSERT INTO user(name, age, gender, password) VALUES (?, ?, ?, ?)", "Tom", 18, "男", passwordHash)
  if err != nil {
    panic(err)
  }
}

By using the MD5 encryption algorithm, the length of the password value will become a relatively fixed value, thereby achieving privacy protection.

3. Summary

In this article, we introduced how to divide data roles and attributes in the

Go language with the MySQL database. In order to better manage data, we need to divide different data roles according to different business needs, and control the range of data attribute types and values. At the same time, when performing data addition, deletion, modification and query operations, corresponding SQL statements need to be used. Through the above introduction, I believe readers have a deeper understanding of how to divide data roles and attributes.

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