Strings vergelijken in MariaDB

Inleiding

De afgelopen dagen liep ik in één van mijn webapplicaties tegen een probleem aan: het updaten van een tekstveld wilde maar niet lukken - tenzij ik extra tekens toevoegde, de aanpassing deed en na opslaan de extra tekens weer verwijderde. Concreet wilde ik de bedrijfsnaam TUV Nederland corrigeren naar TÜV Nederland. Maar na elke klik op Opslaan was mijn correctie verdwenen. Wat was hier aan de hand?

SQL

Dus ging ik op zoek in de betreffende SQL-query; de stored procedure genaamd SP_Bedrijf. Daar stond het UPDATE-statement dat alleen werd uitgevoerd als één van de velden daadwerkelijk is veranderd.

UPDATE `Bedrijf` SET
    `bedrijf_naam` = BedrijfNaam,
    `bedrijf_adres_straat` = BedrijfAdresStraat,
    `bedrijf_adres_huisnummer` = BedrijfAdresHuisnummer,
    ...
WHERE
    (`Bedrijf_id` = ID)
    AND
    (
        StrEq(`bedrijf_naam`, BedrijfNaam) = 0 OR
        StrEq(`bedrijf_adres_straat`, BedrijfAdresStraat) = 0 OR
        StrEq(`bedrijf_adres_huisnummer`, BedrijfAdresHuisnummer) = 0 OR
        ...
    );

Voor deze controle maakte ik gebruik van STRCMP(). Ik wilde graag dat NULL-waarden gelijk gesteld zouden worden aan ''. Dus schreef ik er deze wrapper omheen:

DROP FUNCTION IF EXISTS `StrEq`;
CREATE FUNCTION `StrEq`(
    `a` LONGTEXT,
    `b` LONGTEXT
) RETURNS BOOL
BEGIN
    DECLARE `O` BOOL DEFAULT 0;

    IF (NULLIF(a, '') IS NULL) AND (NULLIF(b, '') IS NULL) THEN
        SET `O` = 1;
    ELSEIF (STRCMP(a, b) = 0) THEN
        SET `O` = 1;
    END IF;

RETURN `O`;
END;

Diagnose

In een MySQL-console probeerde ik de procedure uit te voeren, maar mijn functie zei bij alle velden dat er niets was veranderd; en dus werd het UPDATE-statement niet uitgevoerd. Dan maar de functie rechtstreeks aanspreken:

mysql> SELECT StrEq('TÜV Nederland', 'TUV Nederland') as `Equal`;
+-------+
| Equal |
+-------+
|     1 |
+-------+
1 row in set (0.000 sec)

Blijkbaar was STRCMP() er van overtuigd dat een Ü hetzelfde is als een U.

Oplossing

Met behulp van een hoop documentatie kwam ik de uiteindelijke oplossing op het spoor. Elke tekenreeks (string) heeft een karakterset (characterset), de tabel waarmee de tekens naar binaire data worden vertaald. Dit is tegenwoordig (gelukkig!) bijna altijd UTF-8. Daarbij komt ook nog de collation, een eigenschap waar ik zo 1-2-3 geen betere vertaling dan 'verzameling' of 'rangorde' voor kon vinden. Deze speelt een grote rol bij sorteren en – jawel! – vergelijken.

De aanroep van STRCMP() kon ik op de volgende manier uitbreiden:

...
    ELSEIF (STRCMP(
        CONVERT(a USING utf8) COLLATE utf8_bin,
        CONVERT(b USING utf8) COLLATE utf8_bin
    ) = 0) THEN
        SET `O` = 1;
    END IF;
...

Op deze manier worden beide parameters gedwongen zich te gedragen als keurige UTF-8-burgers in een binaire rangorde die zich perfect laat sorteren en vergelijken. Eindelijk!

mysql> SELECT StrEq('TÜV Nederland', 'TUV Nederland') as `Equal`;
+-------+
| Equal |
+-------+
|     0 |
+-------+
1 row in set (0.000 sec)