Mysql SELECT unicode characters

Question

I have two tables:

words table:

+----+-------+------+
| id | word  | lang |
+----+-------+------+
|  1 | uña   | es   |
|  2 | two   | en   |
|  3 | three | en   |
|  4 | four  | en   |
+----+-------+------+

stop_words table:

+----+------+------+
| id | word | lang |
+----+------+------+
|  1 | una  | es   |
|  2 | one  | en   |
+----+------+------+

I need to select a word from words table where the word are not in stop_words table

• If I try the below query the result is empty because uña from words match with una from stop_words

  SELECT  *
  FROM words a WHERE word LIKE  'uñ%' AND lang = 'es'
  AND NOT EXISTS( Select *
      FROM stop_words as b WHERE a.word = b.word AND lang = 'es'
  

  )

• If I try to use BINARY the result seem to be OK but fail if not use same case (ex: Uñ instead uñ )

  SELECT  *
  FROM words a WHERE word LIKE BINARY 'Uñ%'  AND lang = 'es'
  AND NOT EXISTS( Select *
      FROM stop_words as b WHERE BINARY a.word = BINARY b.word AND lang = 'es'
  )
  

I use utf8mb4_unicode_ci for database, tables and columns.

Any idea how to solve this?

Answer 1

SELECT id, word, lang 
FROM words 
WHERE BINARY word NOT IN (Select BINARY word FROM stop_words);

Result :

+----+-------+------+
| id | word  | lang |
+----+-------+------+
|  1 | uña   | es   |
|  2 | two   | en   |
|  3 | three | en   |
|  4 | four  | en   |
+----+-------+------+

using utf8_unicode_ci for tables

EDIT : For the same case

SELECT  id, word, lang 
FROM words 
WHERE BINARY LOWER(word) NOT IN (Select BINARY LOWER(word) FROM stop_words);