Menu
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.
351
To test whether a row exists in a MySQL table or not, use exists condition. The exists condition can be used with subquery. It returns true when row exists in the table, otherwise false is returned. True is represented in the form of 1 and false is represented as 0.
The default data directory location is C:\Program Files\MySQL\MySQL Server 8.0\data , or C:\ProgramData\Mysql on Windows 7 and Windows Server 2008. The C:\ProgramData directory is hidden by default.
The EXISTS operator in MySQL is a type of Boolean operator which returns the true or false result. It is used in combination with a subquery and checks the existence of data in a subquery. It means if a subquery returns any record, this operator returns true.
The MySQL EXISTS Operator The EXISTS operator is used to test for the existence of any record in a subquery. The EXISTS operator returns TRUE if the subquery returns one or more records.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
