MySQL bug : sorting by alphabetical order failing

user_19416069 0
Viewed 65

I have a problem with mysql, I need your help please.

Version MySQL

I am sorting alphabetically on a column ('label_mission') the sorting is wrong.

SELECT * FROM `m1_mission` ORDER BY `label_mission` ASC

Sorting works from the letter A -> T

but Mysql inverts the letter U and the letter V

Result sort

does anyone have an idea please?

If you want to test the sort here is the code of my MySQL table:

-- phpMyAdmin SQL Dump
-- version 5.1.1
--
-- Hôte : 127.0.0.1:3306
-- Généré le : sam. 25 juin 2022 à 11:17
-- Version du serveur : 5.7.36
-- Version de PHP : 7.4.26

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Base de données : `uniq3`
--

-- --------------------------------------------------------

--
-- Structure de la table `m1_mission`
--

DROP TABLE IF EXISTS `m1_mission`;
CREATE TABLE IF NOT EXISTS `m1_mission` (
  `pk_mission` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `label_mission` varchar(50) COLLATE utf8_roman_ci DEFAULT NULL,
  `processus` varchar(20) COLLATE utf8_roman_ci DEFAULT NULL,
  PRIMARY KEY (`pk_mission`)
) ENGINE=InnoDB AUTO_INCREMENT=74 DEFAULT CHARSET=utf8 COLLATE=utf8_roman_ci;

--
-- Déchargement des données de la table `m1_mission`
--

INSERT INTO `m1_mission` (`pk_mission`, `label_mission`, `processus`) VALUES
(1, 'PRE-OP', 'PR1'),
(2, 'ESQ', 'PR1'),
(3, 'APS', 'PR1'),
(4, 'APD', 'PR1'),
(5, 'AVP', 'PR1'),
(6, 'PRO', 'PR1'),
(7, 'DCE', 'PR2'),
(8, 'ACT', 'PR2'),
(9, 'DET', 'PR3'),
(10, 'VISA', 'PR3'),
(11, 'EXE', 'PR3'),
(12, 'AOR', 'PR3'),
(13, 'OPC', 'PR3'),
(14, 'PC', 'PR1'),
(15, 'TOPO', 'PR1'),
(20, 'HYDRAU', 'PR1'),
(21, 'G1-PGC', 'PR1'),
(22, 'G1-ES', 'PR1'),
(23, 'G2-AVP', 'PR1'),
(24, 'G2-PRO', 'PR1'),
(25, 'G2-ACT/DCE', 'PR2'),
(26, 'G3', 'PR3'),
(27, 'G4', 'PR3'),
(28, 'G5', 'PR1'),
(29, 'P-R1', 'PR1'),
(30, 'P-R2', 'PR2'),
(31, 'P-R3', 'PR3'),
(32, 'P-M1', 'PM1'),
(33, 'P-M2', 'PM2'),
(34, 'P-M3', 'PM3'),
(35, 'P-S1', 'PS1'),
(36, 'P-S2', 'PS2'),
(37, 'P-S3', 'PS3'),
(38, 'P-S4', 'PS4'),
(39, 'P-S5', 'PS5'),
(40, 'P-S6', 'PS6'),
(41, 'EE ( ÉTUDES ENVIRONNEMENTALES )', 'PR1'),
(42, 'EI ( ÉTUDES D\'IMPACTS )', 'PR1'),
(43, 'FAISABILITÉ', 'PR1'),
(44, 'DIAG', 'PR1'),
(45, 'PERMIS D\'AMÉNAGER', 'PR1'),
(46, 'EP', 'PR1'),
(47, 'SYN', 'PR3'),
(48, 'SUP', 'PR1'),
(49, 'ECO', 'PR1'),
(50, 'DOS', 'PR1'),
(51, 'MC1', 'PR1'),
(52, 'MC2', 'PR1'),
(53, 'MC3', 'PR1'),
(54, 'MC4', 'PR1'),
(55, 'MISSIONS COMPLÉMENTAIRES', 'PR1'),
(56, 'MC5', 'PR1'),
(57, 'MC6', 'PR1'),
(58, 'MC7', 'PR1'),
(59, 'MC8', 'PR1'),
(60, 'MC9', 'PR1'),
(61, 'MC10', 'PR1'),
(62, 'PHASE 1', 'PR1'),
(63, 'PHASE 2', 'PR1'),
(64, 'PHASE 3', 'PR1'),
(65, 'PRO-DCE', 'PR2'),
(66, 'GPA', 'PR3'),
(67, 'OPR', 'PR3'),
(68, 'MOB', 'PR1'),
(69, 'OAD', 'PR1'),
(70, 'DPC', 'PR1'),
(71, 'MISE AU POINT DES MARCHES', 'PR2'),
(72, 'SSI', 'PR1'),
(73, 'USA', 'PR3');
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
alphabetical collation sorting mysql
1 Answers

In ancient Latin ("roman"), "U" and "V" were the same. Unless you are working with such, I would recommend not using any "roman" COLLATION.

The latest available Unicode standard is embodied (in MySQL 5.7) in utf8_unicode_520_ci. I suggest you change (via ALTER) the column definitions to use that. That Collation will probably do a better job on French accents.

Here is a chart for CHARACTER SET utf8: http://mysql.rjweb.org/utf8_collations.html

If you have already changed the table to "ascii", then accents are probably lost. If not, then

ALTER TABLE m1_mission
    CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_520_ci;

utf8mb4 is also good and would allow for Emoji and some obscure Chinese characters.

user_17668310
Related