I’m trying to search my database table and get all records except those containing a specific word. When I use just the minus operator by itself, it doesn’t work at all. The MySQL documentation explains that the minus operator only excludes rows that would otherwise match other search terms. If you only use minus operators without any positive search terms, you get no results back instead of getting all rows except the excluded ones.
Right now I’m using a hack where I search for “a* b* c* d* e* f* g* h* i* j* k* l* m* n* o* p* q* r* s* t* u* v* w* x* y* z* -unwanted_term” to make it work. This forces positive matches on words starting with any letter, then excludes my unwanted term.
Is there a better or more elegant solution for this type of exclusion search in MySQL fulltext?
Had this exact problem last year building a content filter. Your alphabet trick works, but there’s a cleaner way - use natural language mode with a WHERE clause. Run your regular fulltext search for the main terms, then add WHERE column NOT LIKE ‘%unwanted_term%’. You get better control without the hacky alphabet search. Performance hit’s minimal on indexed columns and results are way more predictable. You could also try MATCH AGAINST with BOOLEAN MODE - search for common words in your content, then exclude what you don’t want. Both beat the alphabet method for maintenance.
Here’s what worked for me - I used UNION with subqueries. Build a base query that catches everything with wildcards or common stop words, then filter out the junk with NOT EXISTS. Try something like SELECT * FROM table WHERE MATCH(content) AGAINST(‘+the +a +in’ IN BOOLEAN MODE) AND NOT EXISTS (SELECT 1 WHERE MATCH(content) AGAINST(‘unwanted_term’)). Way more flexible than the alphabet trick and crushes LIKE operations on big datasets. Just find terms that hit most of your records as positive matches, then cut out what you don’t want.