Distinguish accents and non-accents in MySQL and Java

Question

Distinguish accents and non-accents in MySQL and Java

Navigation

#1 by (4 votes)
#2 by (1 votes)

4

I have created a table in a MySQL database that stores a text field:

CREATE TABLE 'Usuarios' (
  'Id' int(11) unsigned NOT NULL AUTO_INCREMENT,
  'Nombre' varchar(50) DEFAULT NULL,
    PRIMARY KEY ('Id')
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

I have entered 2 names: José and jose

And from Java I do a search query that tells me that I have two identical records:

ResultSet resultado = consulta.executeQuery("SELECT COUNT(*) FROM 'Usuarios' WHERE UPPER('Nombre') = ‘JOSÉ’";
resultado.first();
System.out.println("Usuarios llamados JOSÉ: " + resultado.getInt(1));

ANSWER: Users called JOSÉ: 2

How can I avoid treating accented and unstressed vowels equally?

mysql java

asked by Oundroni 23.05.2016 в 21:19

source

2 answers

1

The problem was that the COLLATION of the table was set by default in utf8_general_ci . _ci means case insensitive. Changing it by utf8_bin is solved. Information taken from the MySQL manual

To change just one table we have to do this:

ALTER TABLE tabla CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;

And to change the entire database:

ALTER SCHEMA baseDatos DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_bin;

answered by 24.05.2016 в 09:49

Create buttons on Android programmatically Angular Error assigning "*" to a variable declared in the scope

score 4 · Accepted Answer

Do it with utf8_bin , for this the reserved word COLLATE is used. With this charset we are telling MySQL that the comparison in the search is done at the binary level, so now you can distinguish between words with accents and without accents, uppercase and lowercase, it would be something like this:

SELECT * FROM 'Usuarios' WHERE 'nombre' = 'josé' COLLATE utf8_bin

Retrieved from here:

here