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

Question

I have code which lets the user to download files in the database on certain datatype and the download php code will let user to download the files.

In my sql section, I have put the mime datatype as varchar(20), size as bigint(20) and data as longblob. But when I click the download button, it says the file is corrupt but manually when I check out the detailsin database, the details are stored.

So do you think there is an error on the datatype or do I have to change the download.php or upload.php?

Download.php Looks like this:

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 looks like this:

 <?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

As a general rule, files should be stored in the filesystem or a shared storage system like S3 or NFS, and the file path/URL stored as a string. Storing the actual files presents a variety of issues - much more work on the DB than necessary, unpleasantness in backing up, etc.