Sorting with special characters in MySQL

Question

I have an issue with sorting a select from MySQL. I have some names with starts with "A", "Á", "Z" and "Ž" to show a few. The strange thing is, that "A" and "Á" are sorted like the same letter instead of the separate letters. Which means that it may come out like this:

Aak
Adv
Ága
Agg
Ahm

I know Á is a subletter to A, but I want it to be grouped, so all A's come first and then the Á. The collation on the field is utf8mb4_unicode_ci.

What am I doing wrong?

Update
If Á is taken for the same letter as A, then the sort is okay, as it looks at the next letter and so on. But I want Á to be separated from A, so it will sort like this instead:

Aak
Adv
Agg
Ahm
Ága

user_8626118 · Answer

The following 3 collations for UTF8MB4 character set look promising, that is, utf8mb4_icelandic_ci, utf8mb4_is_0900_ai_ci, and utf8mb4_is_0900_as_cs. The result using @Paul Spiegel's example is:

word
====
Ab
Ac
Ae
Ád
Áf
Ba
Ca
Št
Zo
Žv

db-fiddle code

I identified them by checking through the available collations using PROCEDURE.

CREATE PROCEDURE `Try_Collation`()
BEGIN

DECLARE v_finished INTEGER DEFAULT 0;
DECLARE c_collation varchar(100) DEFAULT "";

-- read available collations for utf8mb4
DECLARE table_cursor CURSOR FOR 
    SELECT COLLATION_NAME 
    FROM INFORMATION_SCHEMA.COLLATIONS
    WHERE CHARACTER_SET_NAME='utf8mb4';

DECLARE CONTINUE HANDLER 
    FOR NOT FOUND SET v_finished = 1;

OPEN table_cursor;

    Loop_label: LOOP -- test individual collations by loop

    -- read one collation to table_cursor
    FETCH table_cursor INTO c_collation;

    IF v_finished = 1 THEN 
        LEAVE Loop_label;
    END IF;
    
    -- save the ordered table to a temp table
    DROP TEMPORARY TABLE IF EXISTS temp_table;
    CREATE TEMPORARY TABLE temp_table (
        `word` varchar(50)
        );

    SET @s=CONCAT(
        "INSERT INTO temp_table ",
        "(select word from test ",
        "order by convert(word using utf8mb4) ",
        "collate ",c_collation,");");
    
    PREPARE stmt FROM @s;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    
    -- set some filtering rules for checking orders
    IF (select word from temp_table limit 2,1)='Ád' THEN
        ITERATE Loop_label; -- skip to next loop iteration
    END IF;
    IF (select word from temp_table limit 5,1)='Zo' THEN
        ITERATE Loop_label; -- skip to next loop iteration
    END IF;
    
    SELECT c_collation; -- print out current collation
    SELECT * FROM temp_table; -- print out ordered table

    END LOOP Loop_label;

CLOSE table_cursor;

END;

CALL Try_Collation();

Code link

Sorting with special characters in MySQL

1 Answers