Can MySQL check that file exists?
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
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