Can MySQL check that file exists?

Question

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

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.

Answer 1

I would go with a query like this:

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

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

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.

EDIT:

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