How to design a reliable MySQL table structure to implement file storage function?

How to design a reliable MySQL table structure to implement file storage function?

Currently, file storage has become an integral part of many applications. When designing a reliable MySQL table structure, we need to consider the following key factors:

1. File storage method
   File storage can be done in two ways: store the file directly in the database, or Store the file on disk and store the path to the file in the database. Storing files directly in the database simplifies management, but may affect database performance for large files. Therefore, when designing a reliable MySQL table structure, we recommend storing the file on disk and storing the path to the file in the database.
2. Table structure design
   Create a file table in the database to manage file-related information. The structure of the file table can include the following fields:

• #id: file ID, as the primary key;
• name: file Name;
• path: file path;
• size: file size;
• type: File type;
• created_at: file creation time;
• updated_at: file update time.

For example, you can use the following SQL statement to create a file table:

CREATE TABLE `file` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `path` varchar(255) NOT NULL,
  `size` int(11) NOT NULL,
  `type` varchar(255) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

3. File operation interface design
   In order to implement operations such as uploading, downloading, and deleting files, we Corresponding operation interfaces can be designed for file tables. The following is a simple PHP sample code:

<?php

function uploadFile($file)
{
    $name = $file['name'];
    $path = 'uploads/' . $name;
    $size = $file['size'];
    $type = $file['type'];

    move_uploaded_file($file['tmp_name'], $path);

    $sql = "INSERT INTO `file` (`name`, `path`, `size`, `type`) VALUES ('$name', '$path', $size, '$type')";
    // 执行SQL语句插入文件信息

    return true;
}

function downloadFile($id)
{
    $sql = "SELECT `path` FROM `file` WHERE `id` = $id";
    // 执行SQL语句查询文件路径

    $path = $result['path'];

    header('Content-Type: application/octet-stream');
    header("Content-Disposition: attachment; filename="" . basename($path) . """);
    readfile($path);

    return true;
}

function deleteFile($id)
{
    $sql = "SELECT `path` FROM `file` WHERE `id` = $id";
    // 执行SQL语句查询文件路径

    $path = $result['path'];

    unlink($path);

    $sql = "DELETE FROM `file` WHERE `id` = $id";
    // 执行SQL语句删除文件信息

    return true;
}

?>

In actual applications, we can perform appropriate expansion and optimization according to needs, such as adding file permission management, file preview and other functions.

In summary, designing a reliable MySQL table structure to implement file storage function requires considering the file storage method, table structure design and file operation interface design. Through reasonable design and management, we can achieve efficient and stable file storage functions.

The above is the detailed content of How to design a reliable MySQL table structure to implement file storage function?. For more information, please follow other related articles on the PHP Chinese website!