I want to do a kind of blacklist so I store in my database
id|value
-----------
1|test.com
2|example@
3|@another.
...
Now I looking for the fastest way to check if a given string (an emailadress for example) matched with a part of the values from the DB.
For exmple my@mail.com -> no result in db -> no spam example@mail.com -> matched with ID2 -> spam ...
Is there a way to do it, insider, the MySQL statement?
For the moment I see only the way to load all values in an array an check this, but this way takes a lot of resources and it's really slow.
Thanks a lot.
Use Query like this:
SELECT * FROM `table_name` WHERE `value` LIKE '%emailadress%';
It is possible to do the complete processing in MySQL only.
Please note this will not scale on larger tables because no indexing can be used so keep that in mind.
Query
SELECT
*
FROM (
SELECT
'test' COLLATE 'utf8mb4_general_ci' AS word
UNION ALL
SELECT
'example' COLLATE 'utf8mb4_general_ci' AS word
# When you need more words
# UNION AL
# SELECT 'another' COLLATE 'utf8mb4_general_ci' AS word
#
) AS check_list
INNER JOIN
blacklist
ON
blacklist.`value` LIKE CONCAT('%', check_list.word ,'%')
# note COLLATE 'utf8mb4_general_ci' might not even be needed to the used server
# because it's depending on server config and used table charsets/collates
Results
| word | id | value |
| ------- | --- | -------- |
| test | 1 | test.com |
| example | 2 | example@ |
Or for a more optimal version which does scale on larger tables you have to use FULLTEXT indexing and MATCH()
Query
SELECT
*
FROM
blacklist
WHERE
MATCH (blacklist.`value`)
AGAINST (
(
'test example'
)
IN BOOLEAN MODE
)
See demo
Isn't it just a check with LOCATE() to see, if the string is inside a given email address provided as string?
SELECT
COUNT(*)
FROM
blacklist
WHERE
LOCATE(value, 'my@mail.com');
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
SELECT
COUNT(*)
FROM
blacklist
WHERE
LOCATE(value, 'example@mail.com');
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
See DB Fiddle demo.