ugrás a tartalomhoz

mysql case insensitive like

Ustak · 2009. Szep. 2. (Sze), 07.07
Sziasztok!

Van egy gxt mysql adatbázis alapú adatnyilvántartó alkalmazás, melynek fenntartását, fejlesztését végzem. Az adatbázisra hibernate van ráhúzva (java), mely generálja az sqlt. Feltűnt, hogy pár lekérdezés nagyon lassan fut le, és ahogy logolgattam az sql-t, néztem, hogy bizonyos helyeken a LOWER() függvényt alkalmazza a kód (természetesen java -ból meghíva (SimpleExpression valtozo).ignoreCase();). Mivel ha jól tudom függvényre indexet rakni nem tudunk, gondoltam kiveszem, így viszont nem találja meg kisbetűkre a nagybetűket (m M) pedig a mysql doksiban azt írja, hogy a like case insensitive alapból. case-sensitivity
Nos lefuttattam pár tesztet tisztán az adatbázisban:

mysql> select billing_name from cc_card where billing_name like 'mas%';
Empty set (0,04 sec)

mysql> select billing_name from cc_card where billing_name like 'Mas%';
+------------------+
| billing_name     |
+------------------+
| Masa Akárki      | 
| Masta Borbika    | 
+------------------+
2 rows in set (0,04 sec)

mysql> select billing_name from cc_card where lower(billing_name) like lower('Mas%');
+------------------+
| billing_name     |
+------------------+
| Masa Akárki      | 
| Masta Borbika    | 
+------------------+
2 rows in set (0,04 sec)

mysql> select collation(version());
+----------------------+
| collation(version()) |
+----------------------+
| utf8_general_ci      | 
+----------------------+
1 row in set (0,00 sec)


Az utolsó select azt mutatja, hogy az adott collation igazság szerint case insensitive search -ot kellene hogy adjon, és mégsem. Ám a doksiban az is benne van, hogy ezt rá lehet "erőltetni" oda és viszont, és íme:

mysql> select billing_name from cc_card where billing_name collate utf8_general_ci like 'Mas%';
+------------------+
| billing_name     |
+------------------+
| Masa Akárki      | 
| Masta Borbika    | 
+------------------+
2 rows in set (0,05 sec)

mysql> select billing_name from cc_card where billing_name collate utf8_general_ci like 'mas%';
+------------------+
| billing_name     |
+------------------+
| Masa Akárki      | 
| Masta Borbika    | 
+------------------+
2 rows in set (0,05 sec)
Nagyon örülnék neki ha ezt valaki meg tudná magyarázni, hogy miért van, és tudok -e valamit csinálni az adatbázissal annak érdekében, hogy ne kelljen a collatet belerakni minden ilyen lekérdezésbe, ha mégsem, akkor persze belerakom, mert még mindig gyorsabb (gondolom) mint a lower()
Köszi és béke:
Gábor
 
1

mező/tábla/adatbázis ?

krisy · 2009. Szep. 3. (Cs), 20.58
Szia!

Lehet, hogy nem jó helyen kapizsgálok, de:
http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_collation
A collation megadja az argumentumára vonatkozó információkat, de

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_version
a version mindig utf8-ban adja vissza az értéket!

Nem lehet, hogy a collation-je a mezőknek vagy tábláknak vagy adatbázisnak cs-re van állítva?
2

De jó lenne ha igaz lenne!

Ustak · 2009. Szep. 3. (Cs), 22.58
Pontosan ezt hogy tudom lekérdezni?
Köszi:
Gábor

Ezt próbáltam:

mysql> show variables like '%collation%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   | 
| collation_database   | utf8_hungarian_ci | 
| collation_server     | latin1_swedish_ci | 
+----------------------+-------------------+
3 rows in set (0,00 sec)
3

show

krisy · 2009. Szep. 4. (P), 09.52
Szia!

Táblákét így tudod:
show table status
mezőket pedig:
show full columns from {táblanév}
4

Köszi

Ustak · 2009. Szep. 4. (P), 16.50
A collation a kérdéses táblánál utf8_bin, tehát ha jól emlékszem a doksiból, akkor a binális collation mindig case sensitive keresést eredményez...
Kérdés, hogy hogy tudom ezt megváltoztatni?
Próbáltam a

mysql> alter table cc_card character set 'utf8' collate 'utf8_hungarian_ci';
Query OK, 2748 rows affected (0,49 sec)
Records: 2748  Duplicates: 0  Warnings: 0
Lefut az alter, de a collation marad utf8_bin
Nagyon nagy öröm lenne ha tudnál rá valami gyógymódot:-)
A phpadmin -ra gondoltam, de még nem raktam fel, biztos lehet ezt valahogy sql-ből is...
Köszi:
Gábor.

szerk
Ime ez működik:

alter table cc_card modify billing_name varchar(101) 
character set 'utf8' collate 'utf8_hungarian_ci';
És így már case insensitive a like! Éjen! Ám jó lenne ezt teljes táblára kiterjeszteni, de ezt találtam

alter table cc_card convert to character set 'utf8' collate 'utf8_hungarian_ci';
ERROR 1062 (23000): Duplicate entry 'marcsi' for key 'cons_cc_card_username'
Viszont kapok errort. Sajnos annyira nem értek a mysqlhez (az adatbázist nem én csináltam) hogy mi lehet az error oka, és hol van duplicate entry, a select like marcsi csak egy rekordot hoz...

Köszi, ha van más ötleted (tehát hogy ne mezőnként kelljen változtatni a collationt, hanem legalább táblánként...
Üdv:
Gábor
5

duplicate_key

krisy · 2009. Szep. 4. (P), 18.03
Szia!

Úgy emlékszem, ha a mező nem írja felül a tábla collation-jét, akkor a tábláét kapja.

Viszont a gond az, hogy van egy kulcs a tábla egyik mezőjén, hogy csak egyedi értékek kerülhetnek bele. Viszont ha ci lesz a collation, akkor lesz benne duplikált érték, amit a kulcs nem enged (ez a cons_cc_card_username).

Tehát most van benne "Marcsi" és "marcsi" mondjuk. Ez a kettő jelenleg cs-vel különbözőnek számít, de ha ci lesz a mező, akkor azonos lenne. Ezt nem engedi a kulcs.
6

Renben is van

Ustak · 2009. Szep. 5. (Szo), 12.36
Köszönöm szépen, töröltem a dupla entry-t, és le is futott normálisan az alter, még egyszer nagyon köszönöm a segítséget és az ösztönzést!
Minden jót:
Gábor.
7

nm

krisy · 2009. Szep. 6. (V), 13.30
Nincs mit! :-)
8

Csak hogy tudd

Ustak · 2009. Szep. 6. (V), 20.22
mitől mentettél meg:
A gxt alkalmazásban a hbm.xml -ekben az oszlopnév pl billing_name konvenció szerint a .java osztályban billingName. És ezzel nincs is baj, a hibernate Criteria api nagyon szépen megoldja ezt a konverziót, egészen addig, míg az sqlRestriction függvényt nem kellett használnunk, hogy belerakjuk a like -os lekérdezésekbe collate utf8_hungarian_ci -t. Innentől kezdve a Filter.getFieldName mivel a billingName -t adta vissza, hibás oszlopként jelent meg az sql-ben... A másik megoldás lett volna, hogy a mapping fájloktól kezdve az egész alkalmazást átírhattam volna billing_name - (és még vagy 30 ilyen nevű oszlop) megfeleltetésekre :-)

Szóval NAGYON köszi!
Üdv:
Gábor.