[MySQL] Case-insensitive search, maar áccéntén niet matchen?

[pim]

Ik heb een tabel met utf8_unicode_ci encoding met 4 woorden erin:
1. go
2. GO
3. gó
4. GÓ

Met een simpele zoek query op 'go','GO','gó' of 'GÓ' komen alle vier de woorden terug:

SELECT * FROM `test` WHERE woord = 'gó' # 4 results

Dat is niet de bedoeling. Letters met andere accenten mogen niet matchen.
Zoek ik met COLLATE utf8_bin, dan word ie te strict, en word er niet meer case-INsensitive gezocht:

SELECT * FROM `test` where woord = 'go' COLLATE utf8_bin // 1 result

Is er een query mogelijk waarmee ik case-INsensitive kan zoeken, maar waar bij wel onderscheid word gemaakt tussen karakters met andere accenten?

Ik wil dat op de volgende query:

SELECT * FROM `test` WHERE `woord` LIKE 'go'

De woorden 'go' en 'GO' terug komen, maar niet gó en GÓ.

(Het is voor een vietnamese website, waarbij door een klein accent verschil een woord een compleet andere betekenis krijgt)

[Zoijar]

Staat het web hier niet echt vol mee? Bv http://stackoverflow.com/...cent-insensitive-in-utf-8

(is het nou echt makkelijker om hierheen te gaan, een topic te starten, je probleem uit te leggen met voorbeelden, dan in google in te typen 'mysql case insensitive accent sensitive'? )

[ Voor 38% gewijzigd door Zoijar op 20-07-2013 13:28 ]

[OnTracK]

Op zich een bekend probleem, maar de oplossing is denk ik niet zo simpel als Zoijar stelt. (die link die je geeft Zoijar biedt ook geen oplossing).

Ik denk dat je beste oplossing is een vietnamese collation te gebruiken. Helaas heeft MySQL deze zelf niet. Maar ik zie wel dat meer mensen dat hebben gedaan:
- http://dev.mysql.com/doc/...adding-character-set.html
- http://vietunicode.source...o/vietcollationmysql.html
- https://www.google.com/search?q=mysql+vietnamese+collation

[ Voor 7% gewijzigd door OnTracK op 20-07-2013 14:01 ]

[CaVeFiSh]

Normaal gesproken maak je dan gebruik van een AS (Accent Sensitive Collation), in MSSQL wordt dit simpelweg aangeduid met AS in de collation name. Maar weet niet of je ook zoiets heb in MySQL

Mocht dit niet het geval zijn zou je het kunnen oplossen met bijvoorbeeld het maken van een checksum van de kolom + juiste collation.

Je query op te testen zal dan zoiets worden (in mssql het het gewoon CHECKSUM maar in MySQL heet het geloof ik CRC32 maar dat weet ik niet zeker):

code:

SELECT *
FROM 'test'
WHERE CRC32(woord) = CRC32('gó')

[pim]

Ik kan geen vietnamese collation gebruiken, want in de tabel komen ook woorden voor uit andere talen..

En volgens mij is een checksum oplossing niet case-insensitive..

MySQL:

mysql> SELECT CRC32('MySQL');
        -> 3259397556
mysql> SELECT CRC32('mysql');
        -> 2501908538

De oplossing heb ik nog niet, wel een plakband oplossing die voor het moment goed werkt.
Ik zoek naar uppercase/lowercase varianten:

MySQL:

SELECT * FROM `test` WHERE 
woord = "gó" COLLATE utf8_bin OR # Originele zoek query
woord = LOWER("gó") COLLATE utf8_bin OR # lowercase
woord = UPPER("gó") COLLATE utf8_bin OR  # uppercase
woord = CONCAT(UPPER(LEFT("gó", 1)), SUBSTRING(LOWER("gó"), 2)) COLLATE utf8_bin #  first character capitalized

Geeft terug gó en GÓ.

[ Voor 3% gewijzigd door pim op 20-07-2013 14:21 ]

[Voutloos]

Ipv al die OR clauses kan je ook gewoon beide kanten vd vergelijking door UPPER/LOWER halen.

En als je nou bijv. een kolom toevoegt met als definitie utf8_bin en inhoud UPPER, kan je nog een hele snelle index hebben ook.

(Eea mbt je huidige aanpak, als een collation beschikbaar is, is dat deel vd betere oplossing)

[CaVeFiSh]

Je zou ook kunnen kijken naar REGEXP. Misschien dat zoiets wel werkt:

code:

SELECT * 
FROM `test` 
WHERE woord REGEXP 'gó' ;

[mrwiggs]

pim schreef op zaterdag 20 juli 2013 @ 14:19:
Ik kan geen vietnamese collation gebruiken, want in de tabel komen ook woorden voor uit andere talen..

En volgens mij is een checksum oplossing niet case-insensitive..

MySQL:

mysql> SELECT CRC32('MySQL');
        -> 3259397556
mysql> SELECT CRC32('mysql');
        -> 2501908538

Waarom dan niet

MySQL:

CRC32(LOWER('MySQL'))

Ofwel

MySQL:

SELECT * FROM table WHERE CRC32(LOWER(word)) = CRC32('go')

?

[Zoijar]

Waarom zou je crc's vergelijken? Dan kan je ook nog false positives hebben. Enige wanneer ik ooit crc's heb vergeleken was toen ik lange strings moest zoeken in een tabel met 100 miljoen strings oid; dan kan je optimaliseren door van elke string zijn crcs op te slaan en daar een index search op te doen, vervolgens met die kleine set alsnog je echte strings vergelijken. Maar voor dit probleem zie ik niet wat het toevoegd.

[CaVeFiSh]

Zoijar schreef op zondag 21 juli 2013 @ 14:15:
Waarom zou je crc's vergelijken? Dan kan je ook nog false positives hebben. Enige wanneer ik ooit crc's heb vergeleken was toen ik lange strings moest zoeken in een tabel met 100 miljoen strings oid; dan kan je optimaliseren door van elke string zijn crcs op te slaan en daar een index search op te doen, vervolgens met die kleine set alsnog je echte strings vergelijken. Maar voor dit probleem zie ik niet wat het toevoegd.

Oke heb jij dan wel een oplossing voor het probleem van de TS?

[MLM]

Je zou dit in de application layer kunnen verwerken misschien?
Je doet de query die ook foute accenten matcht, en daarna doe je in je app nog een keer een test op je originele waarde. Niet ideaal, maar beter dan niets.

Andere mogelijkheid is om je "WHERE woord = x" om te zetten naar "WHERE LOWER(woord) = LOWER(x)" en dan met binary collation, maar dat is wellicht kostbaar als je tabel een redelijke omvang heeft. En mogelijk heeft dat andere side-effects, als je woorden niet allemaal in dezelfde normalization form zijn,

[ Voor 11% gewijzigd door MLM op 21-07-2013 15:46 ]

[pedorus]

pim schreef op zaterdag 20 juli 2013 @ 14:19:
Ik kan geen vietnamese collation gebruiken, want in de tabel komen ook woorden voor uit andere talen..

Dat maakt natuurlijk niets uit. Je wilt op een bepaalde manier karakters vergelijken of ordenen, een (custom) collation is daarvoor de oplossing. Dat deze toevallig utf8_vietnamese2_ci heet maakt weinig uit, het maakt alleen uit voor de manier waarop dingen gesorteerd en vergeleken worden. Je kunt nog steeds alle utf8 karakters in zo'n kolom opslaan.

Het voorbeeld in de TS werkt bijvoorbeeld zoals je wil met utf8_icelandic_ci of utf8_polish_ci, dat heeft in zoverre iets met taal te maken dat in die talen "o"<"ó" waar is.

mysql> select "o"<"ó" COLLATE utf8_polish_ci;
+---------------------------------+
| "o"<"ó" COLLATE utf8_polish_ci  |
+---------------------------------+
|                               1 |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select "o"<"ó" COLLATE utf8_unicode_ci;
+----------------------------------+
| "o"<"ó" COLLATE utf8_unicode_ci  |
+----------------------------------+
|                                0 |
+----------------------------------+
1 row in set (0.00 sec)

[Zoijar]

CaVeFiSh schreef op zondag 21 juli 2013 @ 15:07:
Oke heb jij dan wel een oplossing voor het probleem van de TS?

Maakt dat uit? Als er ergens onzin staat dan staat er onzin. Wat heeft een CRC te maken met equivalentie klasses van karakters? Niets.

Verder heb ik mijn oplossing al gegeven in die link: utf8_general_ci. Dat is een redelijke default voor equivalentie klasses. Ik kan geen oplossing geven als het probleem niet eenduidig gedefinieerd is: de TS zegt zelfs nog dat als er een vietnamese specificatie bestaat dat niet genoeg is omdat hij alle talen goed moet doen, inclusief het swalikaboelees waar een lowercase 'eu' gelijk is aan 'k'.

[Voutloos]

CaVeFiSh schreef op zondag 21 juli 2013 @ 15:07:
[...]
Oke heb jij dan wel een oplossing voor het probleem van de TS?

gelukkig had ik die al gepost (voortbordurend op pim en zoijar).

Het probleem zit hem in collations, en anders in dat je zelf beide kanten wil normaliseren. Tevens een hint voor leesbare query en bruikbare index gegeven. Crc en regex hebben hier niets mee van doen.

[Gomez12]

Eigenlijk vind ik het wel een interessante vraag als ik hem iets verder doortrek.

Hoe moet je in een internationale database werken met collations? Want tot nu toe zet ik eigenlijk alle labels in 1 tabel en ga ik ervanuit dat het wel goed gaat.
Ik kan dit doen en dan met collate gaan zitten spelen, maar dan verlies ik alle index-mogelijkheden.
ALternatief is dat ik voor elke taal een andere tabel pak met daarin de goede collation aangegeven (dan behoud ik mijn indexen)

[MSalters]

Het fundametele probleem is dat de wens blijk geeft van een beperkt begrip van Unicode.

Neem het meest bekende voorbeeld: i = I = İ =ı. Welke van die letters zijn gelijk? is het puntje op de "i" een accent of niet? De vraag veronderstelt dat er een objectief antwoord is, wat voor alle talen gelijk is (de tabel is nl. gemixt). In werkelijkheid vinden de Turken dat i = İ <> I = ı