我有两个表:
words
表:
+----+-------+------+
| id | word | lang |
+----+-------+------+
| 1 | uña | es |
| 2 | two | en |
| 3 | three | en |
| 4 | four | en |
+----+-------+------+
stop_words
表:
+----+------+------+
| id | word | lang |
+----+------+------+
| 1 | una | es |
| 2 | one | en |
+----+------+------+
我需要从表中选择一个单词words
该单词不在表中stop_words
如果我尝试以下查询,结果为空,因为
uña
words
与stop_words
una
匹配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'
)
如果我尝试使用
BINARY
结果似乎没问题,但如果不使用相同的情况,则失败(例如:Uñ
而不是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' )
我将utf8mb4_unicode_ci
用于数据库、表和列。
知道如何解决这个问题吗?
SELECT id, word, lang
FROM words
WHERE BINARY word NOT IN (Select BINARY word FROM stop_words);
结果:
+----+-------+------+
| id | word | lang |
+----+-------+------+
| 1 | uña | es |
| 2 | two | en |
| 3 | three | en |
| 4 | four | en |
+----+-------+------+
对表使用utf8_unicode_ci
编辑:对于同一情况
SELECT id, word, lang
FROM words
WHERE BINARY LOWER(word) NOT IN (Select BINARY LOWER(word) FROM stop_words);