How to design an efficient MySQL table structure to implement the video comment function?

How to design an efficient MySQL table structure to implement the video comment function?

On a video website, users can watch videos and leave comments. Sometimes, we need to store a large amount of review data and be able to query and display these reviews efficiently. Designing an efficient MySQL table structure is an important step in ensuring proper functionality. This article will introduce how to design an efficient MySQL table structure to implement the video comment function and provide detailed code examples.

First, we need to create two tables, one to store video information and the other to store comment information. We will use the following SQL statements to create these two tables:

CREATE TABLE videos ( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255) NOT NULL, description TEXT, url VARCHAR(255) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE comments ( id INT AUTO_INCREMENT PRIMARY KEY, video_id INT NOT NULL, user_id INT NOT NULL, comment TEXT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (video_id) REFERENCES videos(id), FOREIGN KEY (user_id) REFERENCES users(id) );

In the above code, we create a table namedvideosto store video information. The table contains the following fields:

• id: The unique identifier of the video, auto-incrementing
• title: The title of the video, max. The length is 255 characters
• description: The description information of the video, using theTEXTtype to store
• url: The video’s URL address, the maximum length is 255 characters
• created_at: The timestamp of the video creation, the default is the current time

At the same time, we also created a name The table forcommentsis used to store comment information. The table contains the following fields:

• id: The unique identifier of the comment, auto-incremented
• video_id: The unique identifier of the video to which the comment belongs ID
• user_id: The ID of the user who posted the comment
• comment: The content of the comment, stored using theTEXTtype
• created_at: The timestamp of comment creation, defaults to the current time

In the above code, we also define foreign key constraints to ensureThevideo_idanduser_idfields in the commentstable are consistent with the corresponding fields in thevideostable anduserstable.

Next, we can use the following code example to show how to insert data into these two tables:

connect_error) { die("连接失败：" . $conn->connect_error); } // 插入视频记录 $sql = "INSERT INTO videos (title, description, url) VALUES ('视频标题', '视频描述', 'http://example.com/video')"; $conn->query($sql); // 获取刚插入的视频的ID $videoId = $conn->insert_id; // 插入评论记录 $sql = "INSERT INTO comments (video_id, user_id, comment) VALUES ($videoId, 1, '这是一个好视频')"; $conn->query($sql); // 断开数据库连接 $conn->close(); ?>

The above code example shows how to insert data into thevideostable Insert a video record and get the ID of the video just inserted. Then, we can use the ID of the video as a parameter to insert a comment record into thecommentstable.

After designing the table structure and learning how to insert data, we also need to understand how to query and display comments efficiently. The following code example shows how to query the comment list of a certain video:

connect_error) { die("连接失败：" . $conn->connect_error); } // 获取指定视频的评论列表 $videoId = 1; $sql = "SELECT * FROM comments WHERE video_id = $videoId"; $result = $conn->query($sql); // 显示评论列表 if ($result->num_rows > 0) { while ($row = $result->fetch_assoc()) { echo "用户ID: " . $row["user_id"] . ", 评论内容: " . $row["comment"] . "
"; } } else { echo "暂无评论"; } // 断开数据库连接 $conn->close(); ?>

In the above code example, we query the comment list of the specified video ID and display the users of each comment through loop traversal. ID and comment content.

In summary, designing an efficient MySQL table structure to implement the video comment function requires taking into account factors such as table relationships, field types, and foreign key constraints. Through reasonable design and optimization, we can efficiently store and query large amounts of review data.

The above is the detailed content of How to design an efficient MySQL table structure to implement the video comment function?. For more information, please follow other related articles on the PHP Chinese website!