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 коментара


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

    Впрочем не съм запознат тази функционалност как е реализирана в 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)!!! Затова просто... хайде без съкратени записи моля :)

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

Адресът на електронната поща няма да се публикува


*