[MySQL]Niepoprawne sortowanie tabeli Opcje

[Demoneos]

Sortuję tabelę wg. pola zawierającego liczby (sumy pieniędzy) w takim formacie jak ta przykładowa liczba: 4 381 532.50
Sortowanie nie wykonuje się tak jakbym tego chciał:

4 061 206.20
4 029 104.20
4 016 132.10
33 787 496.10
3 972 404.10
3 970 208.20

czyli mniejsze kwoty są przed większymi.

Co należałoby zrobić, żeby sortowanie było poprawne? Może zmienić jakoś format tych liczb?

Ten post edytował Demoneos 16.05.2012, 15:12:53

[bpskiba]

Przechowujesz liczby w polu typu tekstowego więc sortowanie nie wychodzi tak, jak oczekujesz. Problemem są spacje

może

[SQL] pobierz, plaintext 
SELECT REPLACE(kolumna," ","")*1,kolumna  FROM tabela ORDER BY 1
[SQL] pobierz, plaintext 

Ten post edytował bpskiba 16.05.2012, 15:38:20

[Demoneos]

Trop jest chyba dobry, ale nie pomogło. Stworzyłem takie zapytanie:

[SQL] pobierz, plaintext 
SELECT REPLACE(REPLACE(kolumna,' ',''),'.','') AS aaaa FROM $this->tabela ORDER BY aaaa DESC
[SQL] pobierz, plaintext 

czyli pozbyłem się i spacji i nawet kropki, a sortuje wciąż niepoprawnie:

337956400

3378749610

337471590

kolumna na format varchar(20), więc może tu jest problem, że to pole tekstowe i może w takiej sytuacji trzeba jakoś inaczej sortować?

Ten post edytował Demoneos 16.05.2012, 17:18:45

[lobopol]

Nie powinno się trzymać liczb w polach tekstowych, skoro to są float to trzymaj je jako floaty a nie varchary. Czyli float/double/etc.

[miniol]

Sortowanie tekstu zawsze jest alfabetycznie, a nie numerycznie. Tak więc sortując alfabetycznie zawsze '152454' będzie przed '4', bo sortowanie odbywa się znak po znaku. Zamień sobie te dane na integer i będziesz mógł sortować je numerycznie.

[bostaf]

To co koledzy napisali wcześniej o przechowywaniu liczb w odpowiednim formacie jest najlepszym rozwiązaniem.
Ale jeśli z jakiegoś powodu nie możesz zmienić typu danych w kolumnie, możesz skorzystać z funkcji CAST() i spreparować swoje zapytanie w taki sposób:

[SQL] pobierz, plaintext 
SELECT CAST(REPLACE(kolumna,' ','') AS DECIMAL(65,2)) AS aaaa
FROM $this->tabela ORDER BY aaaa DESC
[SQL] pobierz, plaintext 

Typ DECIMAL jest dostępny od wersji MySQL 5.0.8. Jeśli masz wcześniejszą wersję, to musisz "castować" jako SIGNED albo UNSIGNED, tracąc to co jest po przecinku.

[Demoneos]

bostaf, Twoje zapytanie działa bardzo dobrze, ale jaki ma wpływ na szybkość wykonania zapytania? Obecnie wykonuje to zapytanie na tabeli liczącej około 800 wierszy i wykonuje się bardzo szybko, ale jak będzie przy dużo większych tabelach?

[lobopol]

Oczywiście wolniej, nawet sporo w końcu musisz przerabiać naprawdę dużo danych, użyj odpowiednich typów pól i je odpowiednio poindeksuj, a zobaczysz dużą różnicę i nie będziesz się musiał martwić poprawnością typów danych w bazie.

[bostaf]

jak będzie przy dużo większych tabelach?

CAST() jest funkcją, która przetwarza dane - każde przetworzenie to dodatkowy czas. Czas wykonania zapytania będzie rósł w postępie arytmetycznym, proporcjonalnie do ilości przetwarzanych danych.

Przetestowałem to zapytanie na tabeli z dwoma milionami rekordów (w drugim i trzecim przypadku bez CASTa oczywiście), z kolumną "kolumna" w trzech wariantach typu danych: VARCHAR(40), DECIMAL(40,2) i BIGINT(40). Takie były średnie czasy wykonania (10 prób dla każdego przypadku):

1. kolumna jako VARCHAR(40): min. 23,275 sek., max. 25,615 sek.
2. kolumna jako DECIMAL(40,2): min. 3,370 sek., max. 3,557 sek.
3. kolumna jako BIGINT(40): min. 2,730 sek., max 2,824 sek.

Wnioski są oczywiste No i testuj sam. Zrób na przykład test jak rodzaj silnika bazy wpływa na szybkość wykonania zapytania - w niektórych przypadkach MyISAM jest szybsze od InnoDB, w innych odwrotnie.