Toon posts:

MariaDB, duplicaat of niet

Pagina: 1
Acties:

Onderwerpen

Vraag


Acties:
  • 0Henk 'm!

  • MSteverink
  • Registratie: Juni 2004
  • Laatst online: 21:54
Software:
MariaDB 10.3.22 op Debian testing
storage engine: InnoDB
Collation: utf8mb4
kolomtype: varchar(512)

Ik heb een programmaatje draaien dat dagelijks de bestandsnamen uit een aantal directories, recursief, inleest en wegschrijft naar een SQL-database. Voor het wegschrijven wordt de tabel leeggegooid.
Je zou verwachten dat bestandsnamen (volledig, met padnaam) uniek zijn, maar ik wilde toch een UNIQUE-constraint of een Primary key op de tabel zetten. En dat lukt niet, omdat bepaalde entries twee keer voorkomen.

code:
1
2
3
SELECT filenames
 FROM tbl_filenames
 WHERE UPPER(filenames) LIKE '%STAND BY ME%';

levert
code:
1
2
/mnt/d353bdd4-32af-497a-90de-8971cfc40429/Public/Shared Music/mmedia/F/Flying Pickets/Stand by me.mp3
/mnt/d353bdd4-32af-497a-90de-8971cfc40429/Public/Shared Music/mmedia/F/Flying Pickets/Stand By Me.mp3


De entries op zich zjjn correct, ik bedoel, het gaat echt om twee verschillende bestanden. Dat kan, onder Linux/EXT4.

Maar doe ik dit
code:
1
2
3
SELECT DISTINCT filenames/*, COUNT(*)*/
 FROM tbl_filenames
 WHERE UPPER(filenames) LIKE '%STAND BY ME%';

dan krijg ik nog een record terug, namelijk
code:
1
/mnt/d353bdd4-32af-497a-90de-8971cfc40429/Public/Shared Music/mmedia/F/Flying Pickets/Stand by me.mp3


En, als variant op het vorige voorbeeld:
code:
1
2
3
4
SELECT COUNT(*), filenames
 FROM tbl_filenames
 WHERE UPPER(filenames) LIKE '%STAND BY ME%'
 GROUP BY filenames;

geeft
code:
1
2   /mnt/d353bdd4-32af-497a-90de-8971cfc40429/Public/Shared Music/mmedia/F/Flying Pickets/Stand by me.mp3


Dus, twee verschillende records worden in een GROUP BY of DISTINCT toch als identiek gezien.

Wat kan hier aan de hand zijn? En vooral, hoe los ik het zo op dat ik toch een constraint of een primary key kan zetten?

Zoeken op Google (innodb unique constraint) leverde o.a. https://dba.stackexchange...onstraint-on-large-column op, maar daar wordt gesproken over een limiet, op de index, van 767 bytes. Zo lang is mijn kolom niet.

Beste antwoord (via MSteverink op 01-06-2020 10:58)


  • RobIII
  • Registratie: December 2001
  • Laatst online: 06-10 19:29

RobIII

Admin Devschuur®

^ Romeinse Ⅲ ja!

Je wil een case-sensitive collation op dat veld. Je hebt nu waarschijnlijk een "ci" (Case insensitive) collation en je wil dus een "cs" (Case Sensitive) collation.

[Voor 75% gewijzigd door RobIII op 31-05-2020 16:40]

There are only two hard problems in distributed systems: 2. Exactly-once delivery 1. Guaranteed order of messages 2. Exactly-once delivery.

Roses are red Violets are blue, Unexpected ‘{‘ on line 32.

Over mij

Alle reacties


Acties:
  • Beste antwoord
  • +4Henk 'm!

  • RobIII
  • Registratie: December 2001
  • Laatst online: 06-10 19:29

RobIII

Admin Devschuur®

^ Romeinse Ⅲ ja!

Je wil een case-sensitive collation op dat veld. Je hebt nu waarschijnlijk een "ci" (Case insensitive) collation en je wil dus een "cs" (Case Sensitive) collation.

[Voor 75% gewijzigd door RobIII op 31-05-2020 16:40]

There are only two hard problems in distributed systems: 2. Exactly-once delivery 1. Guaranteed order of messages 2. Exactly-once delivery.

Roses are red Violets are blue, Unexpected ‘{‘ on line 32.

Over mij


Acties:
  • 0Henk 'm!

  • 0xDEADBEEF
  • Registratie: December 2003
  • Niet online
MSteverink schreef op zondag 31 mei 2020 @ 16:09:
code:
1
2
3
SELECT DISTINCT filenames/*, COUNT(*)*/
 FROM tbl_filenames
 WHERE UPPER(filenames) LIKE '%STAND BY ME%';
Vermoedelijk voert de engine/query optimizer eerst UPPER() uit, en DISTINCT op een upper case filename. Dan klopt het dat er éen regel wordt geretourneerd.

"Religion is an insult to human dignity. With or without it you would have good people doing good things and evil people doing evil things. But for good people to do evil things, that takes religion." - Steven Weinberg


Acties:
  • +1Henk 'm!

  • RobIII
  • Registratie: December 2001
  • Laatst online: 06-10 19:29

RobIII

Admin Devschuur®

^ Romeinse Ⅲ ja!

0xDEADBEEF schreef op zondag 31 mei 2020 @ 23:12:
[...]

Vermoedelijk voert de engine/query optimizer eerst UPPER() uit, en DISTINCT op een upper case filename. Dan klopt het dat er éen regel wordt geretourneerd.
Nee, omdat er een case-insensitive collation wordt gebruikt maakt 't voor een DISTINCT niets uit dat er verschil in hoofdletters zit; "a" is immers gelijk aan "A" (en dus niet verschillend, niet distinct). Sowieso maakt de UPPER() je sargability nul (tenzij de query optimizer slim genoeg is om te zien dat 't totaal niets toevoegt - iets wat bij MySQL/MariaDB altijd maar zeer de vraag is). Heel die "Upper()" heeft in beide queries geen toegevoegde waarde whatsoever.

Wijzig je de collation naar een case-sensitive variant dan zal 't voor DISTINCT (en GROUP BY) etc. wél uitmaken wat de case is en dan is "a" niet meer gelijk aan "A" en dus (ook) UNIQUE.

Kijk hier maar.
Overigens: de collation utf8mb4 bestaat niet; wel utf8mb4_bin, utf8mb4_unicode_ci of utf8mb4_general_ci bijvoorbeeld.

En als je dan nog niet overtuigd bent dan probeer dit maar eens:

SQL:
1
2
3
4
5
6
CREATE TABLE t(
  f CHAR(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  UNIQUE KEY UQ (f)
);

insert into `t` VALUES ('a'), ('A');

Dat geeft een "Duplicate entry 'A' for key 'UQ'". Wijzig je de collation van utf8mb4_unicode_ci naar utf8mb4_bin dan werkt 't precies zoals je wil.

Overigens betekent 't wél weer dat je LIKE of = operator etc. nu ook "opeens" case-sensitive zijn; daar moet je dan wel rekening mee houden uiteraard als je case-insenstive wil zoeken (en dan zul je dus wél met UPPER()/LOWER() en consorten aan de gang moeten).

[Voor 68% gewijzigd door RobIII op 01-06-2020 01:03]

There are only two hard problems in distributed systems: 2. Exactly-once delivery 1. Guaranteed order of messages 2. Exactly-once delivery.

Roses are red Violets are blue, Unexpected ‘{‘ on line 32.

Over mij


Acties:
  • 0Henk 'm!

  • MueR
  • Registratie: Januari 2004
  • Laatst online: 06-10 17:55

MueR

Moderator Devschuur®

is niet lief

Anyone who gets in between me and my morning coffee should be insecure.
Breng nu uw applicatie naar de kloot. Dat is veel beter! Nu samen met klootopslag. Voor maar €9,95. Doei doei!


Acties:
  • 0Henk 'm!

  • MSteverink
  • Registratie: Juni 2004
  • Laatst online: 21:54
RobIII schreef op maandag 1 juni 2020 @ 00:22:
[...]
Heel die "Upper()" heeft in beide queries geen toegevoegde waarde whatsoever.
Klopt. aanvankelijk stond die er ook niet. Die heb ik toegevoegd, en de like-string in hoofdletters, om duidelijk te maken dat het niet aan de LIKE ligt.
RobIII schreef op maandag 1 juni 2020 @ 00:22:
[...]


(tenzij de query optimizer slim genoeg is om te zien dat 't totaal niets toevoegt - iets wat bij MySQL/MariaDB altijd maar zeer de vraag is).
En dit, ook.
RobIII schreef op maandag 1 juni 2020 @ 00:22:
Overigens: de collation utf8mb4 bestaat niet; wel utf8mb4_bin, utf8mb4_unicode_ci of utf8mb4_general_ci bijvoorbeeld.
Dan heb ik nog een extra uitdaging erbij. Mijn tooling (MySQL workbench) kent deze namelijk wel. Maar heeft ook moeite om een gekozen collation vast te houden. Vermoedelijk hierdoor, dus.

Ik heb je eerste antwoord als beste antwoord gemarkeerd. Ik ben er nog niet uit, nog lang niet, maar weet nu in elk geval in welke richting ik moet zoeken.

Acties:
  • +1Henk 'm!

  • MueR
  • Registratie: Januari 2004
  • Laatst online: 06-10 17:55

MueR

Moderator Devschuur®

is niet lief

MSteverink schreef op maandag 1 juni 2020 @ 11:07:
Dan heb ik nog een extra uitdaging erbij. Mijn tooling (MySQL workbench) kent deze namelijk wel. Maar heeft ook moeite om een gekozen collation vast te houden. Vermoedelijk hierdoor, dus.

Ik heb je eerste antwoord als beste antwoord gemarkeerd. Ik ben er nog niet uit, nog lang niet, maar weet nu in elk geval in welke richting ik moet zoeken.
Controleer dan ook even of de betreffende velden ook die collation hebben. MySQL/MariaDB doen daar wel eens wat funky over.

Anyone who gets in between me and my morning coffee should be insecure.
Breng nu uw applicatie naar de kloot. Dat is veel beter! Nu samen met klootopslag. Voor maar €9,95. Doei doei!


Acties:
  • 0Henk 'm!

  • RobIII
  • Registratie: December 2001
  • Laatst online: 06-10 19:29

RobIII

Admin Devschuur®

^ Romeinse Ⅲ ja!

MSteverink schreef op maandag 1 juni 2020 @ 11:07:
Ik heb je eerste antwoord als beste antwoord gemarkeerd. Ik ben er nog niet uit, nog lang niet, maar weet nu in elk geval in welke richting ik moet zoeken.
Je hebt een database collation, een table collation en een column collation. Ik adviseer je ze, voor zover mogelijk, alle 3 gelijk te trekken, maar het gaat hier natuurlijk om de column collation van het "filenames" veld (wat overigens enkelvoud zou moeten zijn).
MSteverink schreef op maandag 1 juni 2020 @ 11:07:
Dan heb ik nog een extra uitdaging erbij. Mijn tooling (MySQL workbench) kent deze namelijk wel. Maar heeft ook moeite om een gekozen collation vast te houden. Vermoedelijk hierdoor, dus.
Jij zit bij Charset te kijken, niet bij Collation. Zorg dat je 't verschil leert ;)

[Voor 66% gewijzigd door RobIII op 01-06-2020 14:24]

There are only two hard problems in distributed systems: 2. Exactly-once delivery 1. Guaranteed order of messages 2. Exactly-once delivery.

Roses are red Violets are blue, Unexpected ‘{‘ on line 32.

Over mij


Acties:
  • 0Henk 'm!

  • Kalentum
  • Registratie: Juni 2004
  • Laatst online: 22:45
MSteverink schreef op maandag 1 juni 2020 @ 11:07:
Dan heb ik nog een extra uitdaging erbij. Mijn tooling (MySQL workbench) kent deze namelijk wel. Maar heeft ook moeite om een gekozen collation vast te houden. Vermoedelijk hierdoor, dus.

Ik heb je eerste antwoord als beste antwoord gemarkeerd. Ik ben er nog niet uit, nog lang niet, maar weet nu in elk geval in welke richting ik moet zoeken.
Er zijn twee verschillende dingen:

- Character sets: die bepalen op welke manier tekst wordt opgeslagen. utf8mb4 is een character set
- Collations: die bepalen hoe het vergelijken van tekens in een characterset gaat

Dus gegeven deze strings: '🎉A' en '🎉a':
- met een collation die case insensitive is zullen deze twee door Maria DB als identiek worden gezien
- met een collation die case sensitive is zullen deze twee als verschillend worden gezien.

PV Output

Pagina: 1



Google Pixel 7 Sony WH-1000XM5 Apple iPhone 14 Samsung Galaxy Watch5, 44mm Sonic Frontiers Samsung Galaxy Z Fold4 Insta360 X3 Nintendo Switch Lite

Tweakers vormt samen met Hardware Info, AutoTrack, Gaspedaal.nl, Nationale Vacaturebank, Intermediair en Independer DPG Online Services B.V.
Alle rechten voorbehouden © 1998 - 2022 Hosting door True

Tweakers maakt gebruik van cookies

Tweakers plaatst functionele en analytische cookies voor het functioneren van de website en het verbeteren van de website-ervaring. Deze cookies zijn noodzakelijk. Om op Tweakers relevantere advertenties te tonen en om ingesloten content van derden te tonen (bijvoorbeeld video's), vragen we je toestemming. Via ingesloten content kunnen derde partijen diensten leveren en verbeteren, bezoekersstatistieken bijhouden, gepersonaliseerde content tonen, gerichte advertenties tonen en gebruikersprofielen opbouwen. Hiervoor worden apparaatgegevens, IP-adres, geolocatie en surfgedrag vastgelegd.

Meer informatie vind je in ons cookiebeleid.

Sluiten

Toestemming beheren

Hieronder kun je per doeleinde of partij toestemming geven of intrekken. Meer informatie vind je in ons cookiebeleid.

Functioneel en analytisch

Deze cookies zijn noodzakelijk voor het functioneren van de website en het verbeteren van de website-ervaring. Klik op het informatie-icoon voor meer informatie. Meer details

janee

    Relevantere advertenties

    Dit beperkt het aantal keer dat dezelfde advertentie getoond wordt (frequency capping) en maakt het mogelijk om binnen Tweakers contextuele advertenties te tonen op basis van pagina's die je hebt bezocht. Meer details

    Tweakers genereert een willekeurige unieke code als identifier. Deze data wordt niet gedeeld met adverteerders of andere derde partijen en je kunt niet buiten Tweakers gevolgd worden. Indien je bent ingelogd, wordt deze identifier gekoppeld aan je account. Indien je niet bent ingelogd, wordt deze identifier gekoppeld aan je sessie die maximaal 4 maanden actief blijft. Je kunt deze toestemming te allen tijde intrekken.

    Ingesloten content van derden

    Deze cookies kunnen door derde partijen geplaatst worden via ingesloten content. Klik op het informatie-icoon voor meer informatie over de verwerkingsdoeleinden. Meer details

    janee