You need to use the correct collation in your database.
For example, if the column of your table that is going to be searched is in UTF-8, you need to use the collation utf8_unicode_ci
("ci" at the end of the MySQL collations is case insensitive, lowercase has the same weight).
To know the collations available, just do this:
SHOW CHARACTER SET;
Example output:
+----------+-----------------------------+---------------------+
| Charset | Description | Default collation |
+----------+-----------------------------+---------------------+
| latin1 | cp1252 West European | latin1_swedish_ci |
| latin2 | ISO 8859-2 Central European | latin2_general_ci |
| ascii | US ASCII | ascii_general_ci |
| cp1250 | Windows Central European | cp1250_general_ci |
... truncado para melhor leitura. deixei os mais comuns pra nós ...
| utf8 | UTF-8 Unicode | utf8_general_ci |
| ucs2 | UCS-2 Unicode | ucs2_general_ci |
| cp852 | DOS Central European | cp852_general_ci |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci |
| utf16 | UTF-16 Unicode | utf16_general_ci |
+----------+-----------------------------+---------------------+
For testing purposes, Victor's solution is good, but for definite use, you may want to modify the table or column so that you do not need COLLATE
in query .
p>
The Handbook describes well all the collations , but is in English:
COLLATE
Converting table charset
To change the collation of a table:
ALTER TABLE <nome> COLLATE utf8_unicode_ci; -- ponha o collation correto
To convert the charset of a table:
VERY WARNING! NEVER use the command below if the table is not behaving correctly. Once that's all right, then you may need to convert the data, and only then can you use that option as it moves the data in the table. ALWAYS MAKE BACKUP. Ready, now you can mouse over the yellow to see the command:)
ALTER TABLE CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;