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:
#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;
<?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!