How to create a collection table in MySQL to implement the user collection function

MySQL creates a collection table to implement the user collection function

The user collection function is one of the common functions in modern websites or applications. It allows users to save content they like or are interested in and view it at any time. . In this article, we will introduce how to use a MySQL database to create a collection table to implement the user collection function.

First, we need to create a table to save collection information. We can use the following SQL statement to create a favorites table named "favorites" in MySQL:

CREATE TABLE favorites (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    content_id INT NOT NULL,
    content_type ENUM('article', 'video', 'image') NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

In the above code, we define five fields:

• id: The unique identifier of the collection record, using an auto-increasing integer as the primary key.
• user_id: The user ID to which the collection record belongs.
• content_id: ID of the collected content.
• content_type: The type of content to be collected. The ENUM type used here is limited to only three values: 'article', 'video' or 'image'.
• created_at: The creation time of the collection record, using the TIMESTAMP type, and setting the default value to the current time.

Next, we can use the following SQL statement to add some test data to the collection table:

INSERT INTO favorites (user_id, content_id, content_type) VALUES
(1, 1, 'article'),
(1, 2, 'video'),
(2, 3, 'article'),
(2, 4, 'image');

According to the above code, we created four collection records, belonging to two different users. Each collection record contains the user ID, ID and type of the collected content.

Next, we will introduce how to use SQL statements to implement some common user collection functions.

1. View the user's collection records:

SELECT * FROM favorites WHERE user_id = 1;

The above code will retrieve all collection records belonging to user ID 1 from the collection table.

2. View collection records of a specific type:

SELECT * FROM favorites WHERE user_id = 1 AND content_type = 'article';

The above code will retrieve collections belonging to user ID 1 and type 'article' (article) from the collection table Record.

3. Delete collection record:

DELETE FROM favorites WHERE user_id = 1 AND content_id = 1;

The above code will delete the collection record with user ID 1 and content ID 1 from the collection table.

The above are just examples of some functions. You can extend these SQL statements to implement more functions and logic according to actual needs and application scenarios.

Summary:

By using the MySQL database to create a collection table, we can easily implement the user collection function. We can retrieve, add and delete collection records through different SQL statements to meet different user needs. In actual development, you can further design and optimize the collection table according to specific business needs, such as adding indexes to improve query efficiency, or using foreign key constraints to ensure data consistency. I hope this article will help you understand and implement the user collection function!

The above is the detailed content of How to create a collection table in MySQL to implement the user collection function. For more information, please follow other related articles on the PHP Chinese website!