MySQL przesunął nieskończone wiersze


114

Chciałbym skonstruować zapytanie, które wyświetla wszystkie wyniki w tabeli, ale jest przesunięte o 5 od początku tabeli. O ile wiem, MySQL LIMITwymaga zarówno ograniczenia, jak i przesunięcia. Czy jest na to sposób?


1
To jest całkowicie uzasadnione pytanie, ale zastanawiam się, czy lepiej byłoby zebrać wszystko i programowo zignorować kilka pierwszych rekordów. Biorąc pod uwagę horror tego, co wydaje się być najlepszą odpowiedzią (limit 5, 18446744073709551615), zdecydowanie wolałbym obejść ograniczenia MySQL LIMIT.
cesoid

3
@cesoid co jeśli chcesz limit 5000, 18446744073709551615. Nie będziesz pobierać dodatkowych 5000 wierszy tylko po to, aby kod wyglądał ładnie.
elipoultorak

@ user3576887 Myślę, że masz rację, właśnie rozważałem powyższe pytanie z założeniem, że 5 jest jedynym wymaganiem, a nie jakąś zmienną kwotą, która może być znacznie większa (i zamiast rozwiązywać czyjś problem).
cesoid

Sugeruję, że jest to tak rzadkie zadanie, że można zaakceptować brzydotę rozwiązania.
Rick James

Odpowiedzi:


151

Z podręcznika MySQL na LIMIT :

Aby pobrać wszystkie wiersze od określonego przesunięcia do końca zestawu wyników, możesz użyć dużej liczby dla drugiego parametru. Ta instrukcja pobiera wszystkie wiersze od 96. do ostatniego wiersza:

SELECT * FROM tbl LIMIT 95, 18446744073709551615;

105
Straszny! Przyszedłem tutaj z nadzieją, że MySQL uczynił klauzulę Limit opcjonalną, tak jak jest, ale także z zapewnionym przesunięciem ... ale nie! Widziałem 18446744073709551615 rozproszone po całym kodzie i winiłem leniwych programistów, ale to cecha projektowa!
Petruza

8
okropna odpowiedź, ale to jest oficjalne z MySQL Doc. Co mogę powiedzieć @ _ @
GusDeCooL

21
18446744073709551615 to 2 ^ 64-1 dla tych, którzy się zastanawiali. Możesz uważać, ponieważ nie będziesz w stanie zapisać tej wartości w 32-bitowej liczbie całkowitej. Musisz upewnić się, że przechowujesz to jako ciąg, aby zapewnić kompatybilność.
AlicanC

13
Straszny! muszą być bardziej eleganckie ... Limit -1lub Limit Nullwyglądać całkiem rozsądnie! lub przynajmniej Limit powinien akceptować podzapytanie, takie jakselect * from table limit (select count(*) from table)
vulcan raven

19
użyj php 'PHP_INT_MAX', aby uniknąć efektów przepełnienia.
Karl Adler

24

Jak wspomniałeś LIMIT jest wymagany, więc musisz użyć największego możliwego limitu, który wynosi 18446744073709551615 (maksymalnie bez znaku BIGINT)

SELECT * FROM somewhere LIMIT 18446744073709551610 OFFSET 5

33
Wow, czy to oficjalne rozwiązanie zespołu MySQL?
Antony

12

Jak zauważono w innych odpowiedziach, MySQL sugeruje użycie 18446744073709551615 jako liczby rekordów w limicie, ale weź pod uwagę to: Co byś zrobił, gdybyś odzyskał 18 446 744 073 709 551 615 rekordów? Właściwie co byś zrobił, gdybyś miał 1 000 000 000 rekordów?

Może chcesz więcej niż miliard płyt, ale chodzi mi o to, że istnieje pewien limit liczby, którą chcesz , i jest to mniej niż 18 kwintylionów. Ze względu na stabilność, optymalizację i być może użyteczność sugerowałbym wprowadzenie znaczącego ograniczenia zapytania. Zmniejszyłoby to również zamieszanie u każdego, kto nigdy nie widział tej magicznie wyglądającej liczby, i miałoby dodatkową zaletę w postaci podania przynajmniej liczby rekordów, które chcesz obsłużyć na raz.

Jeśli naprawdę musisz pobrać wszystkie 18 kwintylionów rekordów ze swojej bazy danych, być może naprawdę chcesz pobrać je w przyrostach po 100 milionów i zapętlić 184 miliardy razy.


Masz rację, ale pozostawienie tej decyzji deweloperowi nie jest dobrym wyborem
amd

@amd Czy mógłbyś to trochę wyjaśnić? Nie wiem, co próbujesz powiedzieć.
cesoid

1
@cesoid Myślę, że mówi, że to deweloperzy nie powinni arbitralnie wybierać logiki biznesowej, z którą się zgadzam, ale tylko do pewnego stopnia. Załóżmy, że zwracasz klientowi listę zamówień. Całkowicie rozsądne jest, aby nigdy nie zwracać więcej niż, powiedzmy, miliona na raz, ale ograniczenie do 100 może spowodować zamieszanie.
Autumn Leonard,

@amd Nie mówię, że programista powinien zmienić zachowanie aplikacji, aby uniknąć używania 18446744073709551615. Mówię, że powinni rozważyć, czy użycie tego numeru ma sens w ramach implementacji niezależnie od klienta lub projektanta interfejsu o co prosił, i jest bardzo mało prawdopodobne, aby była to właściwa implementacja do czegokolwiek. Decyzja o użyciu MySQL została prawdopodobnie podjęta przez programistę bez pytania, czy będzie czegoś więcej niż 18 trylionów.
cesoid,

5

Innym podejściem byłoby wybranie kolumny z autoimkrementacją, a następnie przefiltrowanie jej za pomocą HAVING.

SET @a := 0; 
select @a:=@a + 1 AS counter, table.* FROM table 
HAVING counter > 4

Ale prawdopodobnie trzymałbym się podejścia z wysokimi limitami.


dziękuję i zastanawiam się, jak mogę umieścić takie zapytanie w instrukcji PHP! mam na myśli w ten sposób$sql = 'SET @a :=0 SELECT .....';
Reham Fahmy

2

Jak wspominali inni, z podręcznika MySQL. Aby to osiągnąć, możesz użyć maksymalnej wartości unsigned big int, czyli tej okropnej liczby (18446744073709551615). Ale żeby trochę mniej bałaganu, możesz użyć operatora bitowego tyldy "~".

  LIMIT 95, ~0

działa jako negacja bitowa. Wynik „~ 0” to 18446744073709551615.


1
Nie działa w MariaDB 10.3 :( Próbowałem obu LIMIT 5, ~0i LIMIT ~0 OFFSET 5. Czy to jest funkcja MySQL 8.0?
jurchiks

1
To nie jest rzecz w MySQL 5.7 - nieprawidłowa składnia.
Jonny Nott

0

Właśnie dzisiaj czytałem o najlepszym sposobie uzyskania ogromnych ilości danych (ponad milion wierszy) z tabeli mysql. Jednym ze sposobów jest, jak sugerowano, użycie LIMIT x,ygdzie xjest przesunięciem i yostatnim wierszem, który chcesz zwrócić. Jednak, jak się dowiedziałem, nie jest to najbardziej efektywny sposób. Jeśli masz kolumnę z autoinkrementacją, możesz równie łatwo użyć SELECTinstrukcji z WHEREklauzulą ​​informującą, od którego rekordu chcesz zacząć.

Na przykład, SELECT * FROM table_name WHERE id > x;

Wygląda na to, że mysql uzyskuje wszystkie wyniki, gdy używasz, LIMITa następnie wyświetla tylko rekordy, które mieszczą się w przesunięciu: nie są najlepsze pod względem wydajności.

Źródło: odpowiedz na to pytanie Forum MySQL . Zwróć uwagę, pytanie dotyczy około 6 lat.


13
Daje to nieprawidłowe wyniki, jeśli kiedykolwiek usunąłeś rekord. Ta metoda jest szczególnie niebezpieczna, ponieważ działa przez większość czasu i zawiedzie cicho, gdy nie.
octern

0

Możesz użyć instrukcji MySQL z LIMIT:

START TRANSACTION;
SET @my_offset = 5;
SET @rows = (SELECT COUNT(*) FROM my_table);
PREPARE statement FROM 'SELECT * FROM my_table LIMIT ? OFFSET ?';
EXECUTE statement USING @rows, @my_offset;
COMMIT;

Przetestowano w MySQL 5.5.44. W ten sposób możemy uniknąć wstawiania numeru 18446744073709551615.

uwaga: transakcja zapewnia, że ​​zmienna @rows jest zgodna z tabelą braną pod uwagę przy wykonywaniu wyciągu.


jak stwierdził @amd: „select count (*) na stole z rekordami 7M zajmuje około 17 s”
Rodrirokr

-1

Wiem, że to jest stare, ale nie widziałem podobnej odpowiedzi, więc jest to rozwiązanie, którego bym użył.

Najpierw wykonałbym zapytanie zliczające w tabeli, aby zobaczyć, ile rekordów istnieje. To zapytanie jest szybkie i zwykle czas wykonania jest znikomy. Coś jak:

SELECT COUNT(*) FROM table_name;

Następnie zbudowałbym zapytanie, używając wyniku z count jako mojego limitu (ponieważ jest to maksymalna liczba wierszy, które tabela może ewentualnie zwrócić). Coś jak:

SELECT * FROM table_name LIMIT count_result OFFSET desired_offset;

Lub może coś takiego:

SELECT * FROM table_name LIMIT desired_offset, count_result;

Oczywiście, jeśli to konieczne, możesz odjąć pożądane_offset od count_result, aby uzyskać rzeczywistą, dokładną wartość do podania jako limit. Przekazywanie wartości „18446744073709551610” po prostu nie ma sensu, jeśli mogę określić odpowiedni limit do podania.


2
select count (*) na stole z rekordami 7M trwa około 17s
amd

-7
WHERE .... AND id > <YOUROFFSET>

id może być dowolną automatycznie zwiększaną lub unikalną kolumną liczbową, którą masz ...


7
Kiepski pomysł. Poda niepoprawne przesunięcie, jeśli kiedykolwiek usunąłeś wiersz.
octern
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.