Cookies op Tweakers

Tweakers is onderdeel van DPG Media en maakt gebruik van cookies, JavaScript en vergelijkbare technologie om je onder andere een optimale gebruikerservaring te bieden. Ook kan Tweakers hierdoor het gedrag van bezoekers vastleggen en analyseren. Door gebruik te maken van deze website, of door op 'Cookies accepteren' te klikken, geef je toestemming voor het gebruik van cookies. Wil je meer informatie over cookies en hoe ze worden gebruikt? Bekijk dan ons cookiebeleid.

Meer informatie
Toon posts:

MariaDB, duplicaat of niet

Pagina: 1
Acties:

Onderwerpen

Vraag


Acties:
  • 0Henk 'm!
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: 21:14

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: 21:14

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: 21:14

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: 18:06

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!
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: 18:06

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: 21:14

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:12
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.

PVoutput

Pagina: 1


Apple iPad Pro (2021) 11" Wi-Fi, 8GB ram Microsoft Xbox Series X LG CX Google Pixel 5a 5G Sony XH90 / XH92 Samsung Galaxy S21 5G Sony PlayStation 5 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 - 2021 Hosting door True