How to design an efficient MySQL table structure to implement audio playback function?

How to design an efficient MySQL table structure to implement the audio playback function?

When developing the audio playback function, an efficient MySQL table structure design is very important. A well-designed table structure can ensure the efficiency of data storage and the speed of data retrieval. This article will introduce how to design an efficient MySQL table structure to implement the audio playback function, and give specific code examples.

1. Design table structure

First, we need to create a table to store audio information, which is used to store basic audio information, such as audio ID, audio name, audio path , duration, etc. The table can be created using the following statement:

CREATE TABLE audio (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `path` VARCHAR(255) NOT NULL,
  `duration` INT NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Next, we need to create a table that stores audio playback records to record the user's audio playback behavior, such as user ID, audio ID, playback duration, etc. The table can be created using the following statement:

CREATE TABLE playback_record (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `audio_id` INT NOT NULL,
  `played_duration` INT NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `user`(`id`),
  FOREIGN KEY (`audio_id`) REFERENCES `audio`(`id`)
);

2. Insert data

In the audio playback function, we need to store the audio information and user's playback records into the database. You can use the following statement to insert sample data into the table:

-- 插入音频信息
INSERT INTO audio (`name`, `path`, `duration`) VALUES ('音频1', '/path/audio1.mp3', 120);
INSERT INTO audio (`name`, `path`, `duration`) VALUES ('音频2', '/path/audio2.mp3', 180);
INSERT INTO audio (`name`, `path`, `duration`) VALUES ('音频3', '/path/audio3.mp3', 240);

-- 插入播放记录
INSERT INTO playback_record (`user_id`, `audio_id`, `played_duration`) VALUES (1, 1, 60);
INSERT INTO playback_record (`user_id`, `audio_id`, `played_duration`) VALUES (1, 2, 90);
INSERT INTO playback_record (`user_id`, `audio_id`, `played_duration`) VALUES (2, 1, 30);

3. Query data

In the audio playback function, we need to query its playback record and playback duration based on the user ID . You can use the following statement to query the playback record of a certain user:

SELECT `audio`.`name`, `played_duration`
FROM `playback_record`
INNER JOIN `audio` ON `playback_record`.`audio_id` = `audio`.`id`
WHERE `user_id` = 1;

The above statement will return the playback record and playback duration of user ID 1.

4. Update data

In the audio playback function, we need to update the user's playback record and playback duration. You can use the following statement to update a user's playback record:

UPDATE `playback_record`
SET `played_duration` = 120
WHERE `user_id` = 1 AND `audio_id` = 1;

The above statement will update the playback duration of the playback record with user ID 1 and audio ID 1 to 120 seconds.

Summary:

Designing an efficient MySQL table structure to implement the audio playback function can improve the efficiency of data storage and retrieval. When designing the table structure, you need to consider the relationship between audio information and playback records, and use foreign keys to establish the association. By inserting and querying data, we can add, delete, modify, and query audio information and playback records. The above is a basic table structure design example. The design can be further optimized according to specific business needs.

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