Home / Questions / 10765
Accepted
Oundroni
Asked: 2020-05-24 11:19:03 +0800 CST 2020-05-24 11:19:03 +0800 CST

Distinguishing accents and non-accents in MySQL and Java

  • 772

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 José

And from Java I do a search query that tells me that I have two equal 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 named JOSÉ: 2

How can I prevent it from treating stressed and unstressed vowels the same?

java

2 Answers

  1. Best Answer

    Do it with utf8_bin, for this the reserved word is used COLLATE. With this charsetwe are indicating to MySQL that the comparison in the search be done at the binary level, with which now it will be able to 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

    Obtained from here:

    here

    • 5

  2. The problem was that the COLLATIONof the table was set by default to utf8_general_ci. _cistands for case insensitive. Changing utf8_binit to fixes it. Information taken from the MySQL manual

    To change only 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;
    • 1