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

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

Background
In modern social media platforms, likes are a common way for users to express their approval and support for content they like. For a video platform, it is very important to implement the video like function. This article will introduce how to design an efficient MySQL table structure to implement the video like function and provide some code examples.

Design Ideas
When designing the MySQL table structure, you need to consider the following aspects: storage method of user, video and like relationship, querying the number of likes, querying whether the user likes, querying the user's points Liked videos, etc.

1. User table
   First, you need to create a user table to store the user's basic information, such as user ID, user name, etc.

CREATE TABLE user (
id int(11) NOT NULL AUTO_INCREMENT,
username varchar(255 ) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. Video table
   Secondly, you need to create a video table , used to store basic information of the video, such as video ID, video title, etc.

CREATE TABLE video (
id int(11) NOT NULL AUTO_INCREMENT,
title varchar(255 ) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

3. Like relationship table
   Next create a The like relationship table is used to store the user's like relationship for videos. Each record represents a user's like operation on a video. This table needs to contain two foreign keys: user ID and video ID.

CREATE TABLE like_relation (
id int(11) NOT NULL AUTO_INCREMENT,
user_id int(11 ) NOT NULL,
video_id int(11) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY user_video_unique (user_id,video_id),
CONSTRAINT like_user_fk FOREIGN KEY (user_id) REFERENCES user ( id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT like_video_fk FOREIGN KEY (video_id) REFERENCES video (id ) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Code examples
The following are some commonly used SQL statements used to implement the video like function.

1. Count the number of likes on a video
   SELECT COUNT(*) FROM like_relation WHERE video_id = 1;
2. Query whether the user likes a video
   SELECT COUNT( *) FROM like_relation WHERE user_id = 1 AND video_id = 1;
3. Query videos liked by users
   SELECT video_id FROM like_relation WHERE user_id = 1;
4. Videos liked by users
   INSERT INTO like_relation (user_id, video_id) VALUES (1, 1);
5. User cancels liking video
   DELETE FROM like_relation WHERE user_id = 1 AND video_id = 1;

Summary
To design an efficient MySQL table structure to implement the video like function, you need to consider the design of the user table, video table and like relationship table, as well as some common SQL statements to perform like-related operations. Through reasonable table structure design and optimized query statements, the performance and efficiency of the like function can be improved.

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