php - 使用php在mysql数据库中存储大文件的数据类型是什么？

Question

我有代码允许用户以某种数据类型下载数据库中的文件，下载 php 代码将允许用户下载文件。

在我的 sql 部分，我将 mime 数据类型设置为 varchar(20)，大小设置为 bigint(20)，数据设置为 longblob。但是，当我单击下载按钮时，它说文件已损坏，但是当我手动检查数据库中的详细信息时，详细信息会被存储。

那么您认为数据类型有错误还是我必须更改 download.php 或 upload.php ？

Download.php 看起来像这样:

if(isset($_GET['id'])) {
// Get the ID
$id = ($_GET['id']);

// Make sure the ID is in fact a valid ID
if($id <= 0) {
    die('The ID is invalid!');
}
else {
    // Connect to the database
    $dbLink = new mysqli('server', 'databaseuser', 'password', 'databasename');
    if(mysqli_connect_errno()) {
        die("MySQL connection failed: ". mysqli_connect_error());
    }

    // Fetch the file information
    $query = "
        SELECT `mime`, `name`, `size`, `data`
        FROM `files`
        WHERE `id` = {$id}";
    $result = $dbLink->query($query);

    if($result) {
        // Make sure the result is valid
        if($result->num_rows == 1) {
        // Get the row
            $row = mysqli_fetch_assoc($result);

            // Print headers
            header("Content-Type: ". $row['mime']);
            header("Content-Length: ". $row['size']);
            header("Content-Disposition: attachment; filename=". $row['name']);

            // Print data
            echo $row['data'];
        }
        else {
            echo 'Error! No files exists with that ID.';
        }


    }
    else {
        echo "Error! Query failed: <pre>{$dbLink->error}</pre>";
    }
    @mysqli_close($dbLink);
}
}
  else {
   echo 'Error! No ID was passed.';
  }
 ?>

Upload.php 如下所示:

 <?php
 // Check if a file has been uploaded
  if(isset($_FILES['uploaded_file'])) {
// Make sure the file was sent without errors
if($_FILES['uploaded_file']['error'] == 0) {
    // Connect to the database
    $dbLink = new mysqli('server', 'databaseuser', 'password', 'databasename');
    if(mysqli_connect_errno()) {
        die("MySQL connection failed: ". mysqli_connect_error());
    }

    // Gather all required data
    $name = $dbLink->real_escape_string($_FILES['uploaded_file']['name']);
    $mime = $dbLink->real_escape_string($_FILES['uploaded_file']['type']);
    $data = $dbLink->real_escape_string(file_get_contents($_FILES  ['uploaded_file']      ['tmp_name']));
    $size = intval($_FILES['uploaded_file']['size']);

    // Create the SQL query
    $query = "
        INSERT INTO `files` (
            `name`, `mime`, `size`, `data`, `created`
        )
        VALUES (
            '{$name}', '{$mime}', {$size}, '{$data}', NOW()
        )";

    // Execute the query
    $result = $dbLink->query($query);

    // Check if it was successfull
    if($result) {
        echo 'Success! Your file was successfully added!';
    }
    else {
        echo 'Error! Failed to insert the file'
           . "<pre>{$dbLink->error}</pre>";
    }
}
else {
    echo 'An error accured while the file was being uploaded. '
       . 'Error code: '. intval($_FILES['uploaded_file']['error']);
}

// Close the mysql connection
$dbLink->close();
 }
  else {
echo 'Error! A file was not sent!';
  }

   // Echo a link back to the main page
   echo '<p>Click <a href="index.html">here</a> to go back</p>';
   ?>

Answer 1

作为一般规则，文件应存储在文件系统或共享存储系统(如 S3 或 NFS)中，并且文件路径/URL 存储为字符串。存储实际文件会带来各种问题 - 数据库上的工作量超出必要范围、备份过程中的不愉快等。