C, PHP, VB, .NET

* MySQL OFFSET

Публикувано на 18 март 2010 в раздел Бази от Данни.

Още в началото, когато се разглеждаха заявки за еднотабличен оператор SELECT, набързо се разгледа оператор LIMIT. Да припомним - той приемаше за параметър целочислено число X, чрез което от резултатната таблица се връщат само първите X реда, а останалите "се отрязват". Това естествено има редица приложения - разглеждане на най-новите записи от статистики, разглеждане на "най-добрите" резултати от състезание, извеждане на последните записи в таблица и т.н. Почти винаги, за такива случаи, оператор LIMIT е предхождан от ORDER BY.

Съвсем логичен въпрос обаче идва по-късно: "а какво да правим ако искаме не първите, а вторите X реда?". Ето ви стандартен пример - искате да направите сайт, в който да направите "страниране" на публикациите. На първо страница ще показвате първите 10 резултата, на втора резултатите от 11 до 20, на трета от 21 до 30, и т.н. Тук определено ще срещнете затруднение в писането на заявки за извеждането на по-задните страници.

Първият подход е да се използва т.нар. OFFSET. Нека например имаме таблица с много продукти. Ако желаем да изведем на екрана всички продукти от 101 до 110, то ще напишем следната заявка:

   SELECT id, name
   FROM products
   ORDER BY id
   LIMIT 10 OFFSET 100;

OFFSET има смисъл на "отместване". Тук все пак трябва да споменем, че колкото по-голямо отместване правим, толкова по-бавно се изпълнява заявката. Затова при доста големи бази данни тези заявки биха породили сериозен проблем откъм производителност.

Алтернативата е да се правили лимитиране в WHERE клаузата. Например ако във въпросната таблица products сме убедени, че в id на продуктите "няма дупки", т.е. липсващи id, то можем да напишем горната заявка като:

   SELECT id, name
   FROM products
   WHERE id BETWEEN 101 AND 110
   ORDER BY id;

Тази заявка ще се изпълни доста по-бързо от предишната, но за съжаление със съответната цена. Ясно е, че при изтриване на един продукт с id в интервала [101, 110] ще се получи въпросната "дупка" и върнатите резултати няма да са 10, а 9. Затова използването на този подход следва да се прави само ако стриктно контролираме id-тата на продуктите (в случая от примера). Това означава, че при изтриване на продукт с id X ние трябва да се погрижим да понижим с 1 id-тата на всички продукти с id по-голямо от X, или още по-добре - да променим id-то на последния въведен продукт в системата на X, като по този начин предотвратим тази "дупка". Както се досещате това евентуално може да доведе до редица други проблеми, както и доста по-голяма загуба на производителност, особено ако често изпълняваме заявки DELETE върху таблицата.

« Предишна статияСледваща статия »

2 коментара

Иван каза:

18 март 2010 в 9:16

По-често се пише
LIMIT X,Y
вместо
LIMIT Y OFFSET X
Филип Петров каза:

18 март 2010 в 11:24
Това, което показваш, е просто съкратен запис. Аз лично предпочитам разширения, защото ясно указва с думи кое число какво е. Просто обичам да си описвам данните с думи :)

Впрочем не съм запознат тази функционалност как е реализирана в ANSI стандарта - по-склонен съм да вярвам, че я няма и се използват трикове с броене на ROWNUM (както в ORACLE) и подобни хитрости.

Ето как се прави алтернатива на LIMIT и OFFSET в няколко различни системи:

DB2:
Доколкото знам няма оператор за OFFSET. За LIMIT се използва:
```
SELECT id,name
FROM products
FETCH FIRST 10 ROWS ONLY;
```
За постигане на OFFSET (подобно на ORACLE) се използва трик с rownum:
```
SELECT * 
FROM (
     SELECT ROW_NUMBER() OVER (ORDER BY id ASC) AS rn, id, name
     FROM products
     ORDER BY id ASC
) AS tblname
WHERE rn > 100 AND rownum <= 110
```
MSSQL:
Долколкото знам няма оператор за OFFSET. За LIMIT се използва:
```
SELECT TOP 10 id, name
FROM products;
```
Ето начин за постигане на OFFSET с вложен SELECT:
```
SELECT TOP 10 id, name 
FROM products 
WHERE name NOT IN (
          SELECT TOP 100 name
          FROM products
);
```
Informix, InterBase, Firebird:
```
SELECT FIRST 10 SKIP 100 id, name
FROM products;
```
Oracle:
Няма нито LIMIT, нито OFFSET. За LIMIT може да се използва ROWNUM:
```
SELECT id, name
FROM products
WHERE ROWNUM<=10;
```
За съжаление ROWNUM не ни помага за постигане на функционалност на OFFSET. Условие като "ROWNUM >100 AND ROWNUM <=110" ще върне false още на първи ред и от там няма да продължи въобще. Затова за OFFSET се използва "трик":
```
SELECT id, name 
FROM (
     SELECT id, name, ROW_NUMBER() OVER (ORDER BY id) AS rn
     FROM products
     ORDER BY id
)
WHERE rn > 100 AND rn <= 110;
```
POSTGRE и SQLLite: Същото както при MySQL. При POSTGRE e валиден и съкратения запис, обаче в него числата са обърнати (т.е. ако в MySQL пишем LIMIT X,Y, то в POSTGRE пишем LIMIT Y,X)!!! Затова просто... хайде без съкратени записи моля :)

Дневникът на Филип Петров

* MySQL OFFSET

2 коментара

Добави коментар