Witaj Gościu! ( Zaloguj | Rejestruj )

Forum PHP.pl


Konwersja bazy danych z ISO do UTF8 (dziwne znaczki)
szopen
Napisane: 5.10.2008, 18:52:05





Grupa: Zarejestrowani
Postów: 60
Dołączył: 28.08.2008

Ostrzeżenie: (0%)
-----
wstawianie entities jest bardzo złym pomysłem
czy po 'search and replace' zmieniłeś kodowanie bazy na takie, w którym dodawałeś ogonki? Jeśli zostało UTF-8 nic dziwnego, że ucina
Zakłądając że masz cały zrzut w kodowaniu iso (albo cp) możesz użyć iconv'a albo czegoś innego, aby plik zrzutu zapisać w/przekonwertować do UTF8, a potem import do bazy.
  Forum: MySQL · Podgląd postu: #523331 · Odpowiedzi: 2 · Wyświetleń: 2 622

[MySQL] Złożony SELECT
szopen
Napisane: 31.08.2008, 20:19:44





Grupa: Zarejestrowani
Postów: 60
Dołączył: 28.08.2008

Ostrzeżenie: (0%)
-----
Ale dokładnie co? Po edicie już wiem ; )

A to działa tak jak trzeba?
Kod
SELECT r.id AS room_id,
       GROUP_CONCAT( DISTINCT CONCAT(a.name, '*', a.icon) ORDER BY a.name SEPARATOR '|') AS attractions,
       GROUP_CONCAT(DISTINCT CONCAT(rp.id, '*', rp.price) ORDER BY rp.id SEPARATOR '|') AS price_list
  FROM room r LEFT JOIN property p             ON r.property_id=p.id
              LEFT JOIN property_attraction pa ON pa.property_id=p.id
              LEFT JOIN attraction a           ON a.id=pa.attraction_id AND (a.is_active=1 OR a.is_active IS NULL)
              LEFT JOIN room_price rp          ON rp.room_id=r.id
  WHERE pa.attraction_id IN(1,9)
  GROUP BY r.id
  HAVING count(distinct pa.attraction_id)>=2 -- albo = 2 jeśli (a)
  ORDER BY p.is_promoted DESC
  LIMIT 0, 50;


Offtop: czemu przy BBCodach [ sql ] nie zachowuje wcięć? Czy może ja coś źle robię? Byłbym wdzięczny adminom za dodanie w CSSach pre dla bloków SQLowych...
  Forum: MySQL · Podgląd postu: #511757 · Odpowiedzi: 8 · Wyświetleń: 1 807

[MYSQL] Losowanie wyników
szopen
Napisane: 31.08.2008, 15:50:55





Grupa: Zarejestrowani
Postów: 60
Dołączył: 28.08.2008

Ostrzeżenie: (0%)
-----
Cytat(Skie @ 30.08.2008, 11:28:17 ) *
Tylko teraz powstaje pytanie co jest bardziej wydajne i co mniej obciąży MySQL - Jeden z Twoich sposobów czy prosta funkcja while () zastosowana w PHP, losująca po 1 wyniku i dodająca ten wynik do tablicy?

Jak pisałem na początku mojego posta, wylosowanie jednego usera taką "prostą funkcją" trwa na mojej tabelce ok 10 seknud. Razy ilość userów do wylosowania... odpowiedź oczywista co bardziej wydajne ;) Aczkolwiek może to się da jeszcze przyspieszyć stosując w szukaj1/szukaj2 Dynamic SQL...


---- edit

Poniżej wersja losująca unikalnych użytkowników. Stanowi ona wydajniejszą alternatywę dla zapytania w stylu:
[SQL] pobierz, plaintext 
  1. SELECT * FROM xxx ORDER BY rand() LIMIT $mojLimit;
[SQL] pobierz, plaintext

pod warunkiem, że tabela xxx jest duża (rzędu 100 000 rekordów), a wybieramy losowo małą ich ilość np. 500. Dla przykładu zapytanie (a) z limitem 1 dla bazy o 262885 trwało 1.20 sec podczas gdy (b) losuj2uniq(1) trwało 0.01 sec. Dla limitu 100 mamy: (a) 2.46 sec, (b) 0.11; 500: (a) 1.18, (b)0.72 ;ale już dla limit 1000: (a) 1.22 (b) 1.84


Kod
DROP PROCEDURE IF EXISTS losuj2uniq;

delimiter //

CREATE PROCEDURE losuj2uniq( IN ileLosowac INT UNSIGNED)
BEGIN
   -- użytkownik o najwyższym ID
   DECLARE maxId INT UNSIGNED;
   DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' BEGIN SET ileLosowac = ileLosowac+1; END;

   -- tabelka przechowująca wylosowane identyfikatory
   DROP TEMPORARY TABLE IF EXISTS numbers;
   CREATE TEMPORARY TABLE numbers (`user_id` int unsigned not null primary key) ENGINE=MEMORY;

   -- znajdź największy ID użytkownika
   SELECT MAX(`user_id`) FROM xxx INTO maxId;

   -- powtarzaj tyle razy, ile chcemy wyników
   losowanieUsera: WHILE ileLosowac > 0 DO
   BEGIN
      DECLARE randomElement INT UNSIGNED;
      SELECT FLOOR( 1+ RAND()*maxId ) INTO randomElement;

      -- Dodaj do tabelki pierwszego usera o ID równym wylosowanemu (może takiego nie być!)
      INSERT INTO numbers SELECT `user_id` FROM xxx WHERE `user_id`=randomElement LIMIT 1;

      -- Jeśli nie ma usera o takim ID, losujemy jeszcze raz
      IF ROW_COUNT()=0 THEN ITERATE losowanieUsera; END IF;

      -- Wylosowaliśmy kolejnego usera :) Zmniejsz ilość pzostałych do wylosowania
      SET ileLosowac = ileLosowac-1;
   END;
   END WHILE;

   -- Zwróć wylosowanych userów (zachowaj kolejność losowania)
   select xxx.user_id from numbers left join xxx on xxx.user_id=numbers.user_id;

   -- Jeśli kolejność nie ma znaczenia (w kolejności w jakiej są zapisani userzy) można użyć
   -- select * from numbers natural join xxx;

   -- Posprzątaj
   DROP TEMPORARY TABLE IF EXISTS numbers;
END;
//

delimiter;
  Forum: Przedszkole · Podgląd postu: #511264 · Odpowiedzi: 5 · Wyświetleń: 2 014

[MYSQL] Losowanie wyników
szopen
Napisane: 30.08.2008, 03:39:43





Grupa: Zarejestrowani
Postów: 60
Dołączył: 28.08.2008

Ostrzeżenie: (0%)
-----
Uprasza się o niezajeżdżanie serwera SQL zapytaniami w stylu podanego przez grzemacha smile.gif Wszak dla każdego losowania biedna baza musi posortować wszytkie rekordy. I to za każdym razem! Przeszło mi przez myśl, że może to jakoś optymalizować. Sprawdziłem na bazie o 262885 rekordach -- wylosowanie jednej pozycji trwało średnio 10 sekund. Więc niezbyt ciekawie.

Problem sam w sobie jest ciekawy, więc postanowiłem się nieco pobawić. Zakładając że mamy MySQLa w wersji 5 możemy napisać sobie pomocne procedury składowane. W pierwszej wersji zakładamy, że istnieje jakiś unikalny `id` i nie ma "dziur", a jeśli nawet są, to nie mają one dużego znaczenia. Rozkład prawdopodobieństwa w przypadku z diurami nie jest jednostajny, czyli elementy znajdujące się na "krawędzi" dziury będą częściej losowane. Dlaczego? Bo odpowiada im więcej niż jedna wylosowana wartość.

Kod
DROP PROCEDURE IF EXISTS losuj1;

delimiter //

CREATE PROCEDURE losuj1( IN ileLosowac INT UNSIGNED)
BEGIN
   -- użytkownik o najwyższym ID
   DECLARE maxId INT UNSIGNED;

   -- tabelka przechowująca wylosowane identyfikatory
   DROP TEMPORARY TABLE IF EXISTS numbers;
   CREATE TEMPORARY TABLE numbers (`user_id` int unsigned not null) ENGINE=MEMORY;

   -- znajdź największy ID użytkownika
   SELECT MAX(`user_id`) FROM xxx INTO maxId;

   -- powtarzaj tyle razy, ile chcemy wyników
   WHILE ileLosowac > 0 DO
   BEGIN
      -- Wylosuj ID. UWAGA! Może on nie istnieć, dlatego dalej mamy >=
      DECLARE randomElement INT UNSIGNED;
      SELECT FLOOR( 1+ RAND()*maxId ) INTO randomElement;

      -- Dodaj do tabelki pierwszego usera o ID równym lub większym od wylosowanego
      INSERT INTO numbers SELECT `user_id` FROM xxx WHERE `user_id`>=randomElement LIMIT 1;

      -- Wylosowaliśmy kolejnego usera :) Zmniejsz ilość pzostałych do wylosowania
      SET ileLosowac = ileLosowac-1;
   END;
   END WHILE;

   -- Zwróć wylosowanych userów (zachowaj kolejność losowania)
   select xxx.* from numbers left join xxx on xxx.user_id=numbers.user_id;

   -- Jeśli kolejność nie ma znaczenia (w kolejności w jakiej są zapisani userzy) można użyć
   -- select * from numbers natural join xxx;

   -- Posprzątaj
   DROP TEMPORARY TABLE IF EXISTS numbers;
END;
//

delimiter;


Problem "dziur" możemy załatać ponownym losowaniem użytkownika, w przypadku gdy jego ID nie istnieje. Dalej nie jest to dobre rozwiązanie, gdyż teoretycznie może zdarzyć się sytuacja, w której ten sam nieistniejący `user_id` będzie losowany w nieskończoność. Bardziej realna wada -- przy "razdkich" danych (tj. duże dziury), będzie trzeba wykonać wiele losowań aby trafić w istniejący ID. Zmiany pojawiły się w okolicy pętli WHILE jednak dla czytelności wstawiam jeszcze raz pełny kod.

Kod
DROP PROCEDURE IF EXISTS losuj2;

delimiter //

CREATE PROCEDURE losuj2( IN ileLosowac INT UNSIGNED)
BEGIN
   -- użytkownik o najwyższym ID
   DECLARE maxId INT UNSIGNED;

   -- tabelka przechowująca wylosowane identyfikatory
   DROP TEMPORARY TABLE IF EXISTS numbers;
   CREATE TEMPORARY TABLE numbers (`user_id` int unsigned not null) ENGINE=MEMORY;

   -- znajdź największy ID użytkownika
   SELECT MAX(`user_id`) FROM xxx INTO maxId;

   -- powtarzaj tyle razy, ile chcemy wyników
   losowanieUsera: WHILE ileLosowac > 0 DO
   BEGIN
      DECLARE randomElement INT UNSIGNED;
      SELECT FLOOR( 1+ RAND()*maxId ) INTO randomElement;

      -- Dodaj do tabelki pierwszego usera o ID równym wylosowanemu (może takiego nie być!)
      INSERT INTO numbers SELECT `user_id` FROM xxx WHERE `user_id`=randomElement LIMIT 1;

      -- Jeśli nie ma usera o takim ID, losujemy jeszcze raz
      IF ROW_COUNT()=0 THEN ITERATE losowanieUsera; END IF;

      -- Wylosowaliśmy kolejnego usera :) Zmniejsz ilość pzostałych do wylosowania
      SET ileLosowac = ileLosowac-1;
   END;
   END WHILE;

   -- Zwróć wylosowanych userów (zachowaj kolejność losowania)
   select xxx.* from numbers left join xxx on xxx.user_id=numbers.user_id;

   -- Jeśli kolejność nie ma znaczenia (w kolejności w jakiej są zapisani userzy) można użyć
   -- select * from numbers natural join xxx;

   -- Posprzątaj
   DROP TEMPORARY TABLE IF EXISTS numbers;
END;
//

delimiter;


Ostatnia wersja, teoretycznie najlepsza, stosuje inne podejście. Wykorzystujemy tu frazę "LIMIT offset,1" w celu pobrania elementu o danym offsecie. Niestety MySQL nie umożliwia używania zmiennych w części LIMIT zapytania, więc musimy skorzystać z prepared statements, co nawet może nam wyjść na dobre winksmiley.jpg. Zwróćcie uwagę na zmianę zakresu losowanych liczb -- teraz potrzebne są nam liczby z przedziału [0, count(*)) (czyli bez prawego końca), wcześniej potrzebowaliśmy [1, max(`user_id`)].

Kod
DROP PROCEDURE IF EXISTS losuj3;

delimiter //

CREATE PROCEDURE losuj3( IN ileLosowac INT UNSIGNED)
BEGIN
  -- użytkownik o najwyższym ID
  DECLARE itemCount INT UNSIGNED;

  -- tabelka przechowująca wylosowane identyfikatory
  DROP TEMPORARY TABLE IF EXISTS numbers;
  CREATE TEMPORARY TABLE numbers (`user_id` int unsigned not null) ENGINE=MEMORY;

  -- znajdź największy ID użytkownika
  SELECT count(*) FROM xxx INTO itemCount;

  -- przygotuj zapytanie do wywoływanie w pętli
  PREPARE stmt FROM "INSERT INTO numbers SELECT `user_id` FROM xxx LIMIT ?, 1";

  -- powtarzaj tyle razy, ile chcemy wyników
  WHILE ileLosowac > 0 DO
  BEGIN
     DECLARE randomOffset INT UNSIGNED;
     SELECT FLOOR( RAND()*itemCount ) INTO randomOffset;

     -- Dodaj użytkownika spod wylosowanego offsetu
     SET @a_hde9fhcdgva = randomOffset;
     EXECUTE stmt USING @a_hde9fhcdgva;

     -- Wylosowaliśmy kolejnego usera :) Zmniejsz ilość pzostałych do wylosowania
     SET ileLosowac = ileLosowac-1;
  END;
  END WHILE;
  DEALLOCATE PREPARE stmt;

  -- Zwróć wylosowanych userów (zachowaj kolejność losowania)
  select xxx.* from numbers left join xxx on xxx.user_id=numbers.user_id;

  -- Jeśli kolejność nie ma znaczenia (w kolejności w jakiej są zapisani userzy) można użyć
  -- select * from numbers natural join xxx;

  -- Posprzątaj
  DROP TEMPORARY TABLE IF EXISTS numbers;
END;
//

delimiter;


A teraz chwila prawdy -- testy smile.gif Na sam początek wykonałem powyższe trzy punkty dla wcześniej wspomnienej tabeli z argumentem 100. Pierwsze dwie zwróciły wynik poniżej sekundy, natomiast trzecia dopiero po minucie. Dlaczego? Bo select dla dużej wartości OFFSETu w LIMIT działa wolno (przynajmniej na InnoDB).

Jeśli ktoś ma inne pomysły albo uwagi to śmiało smile.gif
  Forum: Przedszkole · Podgląd postu: #511229 · Odpowiedzi: 5 · Wyświetleń: 2 014

Blokowanie takich samych wpisów w tabeli
szopen
Napisane: 31.08.2008, 22:14:48





Grupa: Zarejestrowani
Postów: 60
Dołączył: 28.08.2008

Ostrzeżenie: (0%)
-----
Jeśli chodzi o filtr TRIMowy to na szybko coś takiego:
TRIM(TRIM( BOTH "\r\n" FROM nazwapola))
A jak chcesz wywalić coś co kończy się na \r\n to... delete from ... nazwa like '%\r\n\' ?
  Forum: MySQL · Podgląd postu: #511817 · Odpowiedzi: 12 · Wyświetleń: 2 404


New Posts  Nowe odpowiedzi
No New Posts  Brak nowych odpowiedzi
Hot topic  Popularny temat (Nowe)
No new  Popularny temat (Brak nowych)		 Poll  Sonda (Nowe)
No new votes  Sonda (Brak nowych)
Closed  Zamknięty temat
Moved  Przeniesiony temat
 

RSS Wersja Lo-Fi Aktualny czas: 19.04.2024 - 04:07
Powered By IP.Board © 2024  IPS, Inc.
All changes by PHP.pl Administrators
Hosting zapewnia NQ.pl hosting, trac, svn