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 named videos to 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 the TEXT type 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 for comments is 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 the TEXT type
• created_at: The timestamp of comment creation, defaults to the current time

In the above code, we also define foreign key constraints to ensure The video_id and user_id fields in the comments table are consistent with the corresponding fields in the videos table and users table.

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

<?php

// 连接到数据库
$conn = new mysqli("localhost", "username", "password", "database");

// 检查连接是否成功
if ($conn->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 the videos table 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 the comments table.

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:

<?php

// 连接到数据库
$conn = new mysqli("localhost", "username", "password", "database");

// 检查连接是否成功
if ($conn->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"] . "<br>";
    }
} 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!