MySQL可以检查该文件是否存在吗? [英] Can MySQL check that file exists?

问题描述

我有一个表，其中包含HDD上实际文件的相对路径.例如:

I have a table that holds relative paths to real files on HDD. for example:

SELECT * FROM images -->
id | path
1  | /files/1.jpg
2  | /files/2.jpg

我可以创建查询来选择所有指向不存在文件的记录吗?我需要完全由MySql服务器检查它，而无需在PHP客户端中使用迭代.

Can I create a query to select all records pointing to non-existent files? I need to check it by MySql server exactly, without using an iteration in PHP-client.

推荐答案

我会使用这样的查询:

SELECT id, path, ISNULL(LOAD_FILE(path)) as not_exists
FROM images
HAVING not_exists = 1

函数LOAD_FILE尝试将文件加载为字符串，并在失败时返回NULL.

The function LOAD_FILE tries to load the file as a string, and returns NULL when it fails.

请注意，这种情况下的失败可能是由于mysql根本无法读取该特定位置，即使该文件实际存在也是如此.

Please notice that a failure in this case might be due to the fact that mysql simply cannot read that specific location, even if the file actually exists.

正如@ostrokach在评论中指出的那样，即使MySQL允许，这也不是标准的SQL，而是遵循以下标准:

As @ostrokach pointed out in comments, this isn't standard SQL, even though MySQL allows it, to follow the standard it could be:

SELECT *
FROM images
WHERE LOAD_FILE(PATH) IS NULL

这篇关于MySQL可以检查该文件是否存在吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！