Mysql кількість рядків з умовою. Вибірка та підрахунок рядків одним запитом - SQL_CALC_FOUND_ROWS

Від автора:один мій знайомий стверджує, що Дід Мороз існує. Він повірив після того, як його молодшому синові на Новий Рік подарували 10 кг шоколадних цукерок. Тепер наступного року доведеться у Діда Мороза просити пломби для всіх членів родини! На щастя у веб-програмуванні можна перестрахуватися від таких несподіванок і визначити кількість рядків у MySQL заздалегідь.

Навіщо знати скільки?

Будь-яка таблиця складається зі стовпців та рядків. Обсяг збережених у базі даних залежить від того, скільки рядків міститься в кожній таблиці.

Подібно до того, як кількість дірок у зубах людини залежить від з'їденого раніше обсягу цукерок. Точніше, від загальної ваги солодощів, яким «обдарували» вашу дитину на Новий Рік.

Зрозуміло, що від великої кількості рядків у базі MySQL пломб у роті не додасться, але все ж таки знання цього параметра може знадобитися при оптимізації роботи вашого сайту, для запуску деяких скриптів і т.д.

Легко!

Для отримання в MySQL кількості рядків не обов'язково "розуміти" у складанні SQL-запитів. Достатньо просто відкрити в програмній оболонці потрібну таблицю та подивитися id останнього рядка. Саме цей стовпець найчастіше використовують як унікальний ключ, і його значення генерується автоматично (автоінкремент) шляхом збільшення одиниці (+1) до попереднього.

Щоб довести цю «аксіому», відкриємо в phpMyAdmin таблицю будь-якої БД. Наприклад, таблицю animal. Як видно на скріншоті, значення стовпця id йде по порядку, від 1 до… Так, відставити! А куди втік котяра із «ідентифікаційним номером» 4? Напевно, знову «собачок» під номерами 5 та 6 злякався

У цій базі MySQL кількість рядків у таблиці можна просто порахувати "на пальцях". Але що робити, якщо у таблиці не 7 рядків, а кілька сотень? Наприклад, як у цій.

І це лише одна із восьми сторінок вибірки. Насправді число записів може обчислюватися як сотнями, а й тисячами. До того ж тут немає стовпця id, тому що він не є обов'язковим. Його наявність залежить від структури всієї БД, оскільки у ній значення всіх рядків взаємопов'язані між собою. Виходить, що описаний вище спосіб підрахунку рядків MySQL не завжди діє.

Декілька правильних методів

Як кажуть, перший метод «комом», тому розглянемо кілька ефективніших і менш «простих» способів:

Функція COUNT() – повертає кількість рядків. Вбудована функція SQL. Але давайте дізнаємося, кількість записів великої таблиці з БД, скріншот якої наведено вище. Код запиту:

SELECT COUNT(*) FROM `country`

Виявляється у таблиці country цілих 239 записів! Зрозуміло, що для їхнього підрахунку не вистачить пальців ні на руках, ні на ногах (якщо не використовувати кінцівки інших людей чи тварин). Як бачите, цей спосіб MySQL підрахунку кількості рядків працює набагато точніше і найчастіше використовується .

Але ця функція вміє не лише це! Наприклад, з її допомогою можна дізнатися, скільки з представлених у БД країн знаходяться в Європі:

А ще скільки разів кожна країна згадується в таблиці. Приклад запиту:

SELECT `Name`, COUNT(*) FROM `country` GROUP BY `Name`

За допомогою цього запиту ми дізналися кількість рядків MySQL, в яких згадується кожна з країн. З результатів виконання видно, що по одному разу.

Мало не забув! Якщо у вас ще немає бази world, з якою ми сьогодні працюємо, то її можна завантажити за цим посиланням з офіційного сайту розробників СУБД MySQL. Після завантаження архів з нею потрібно розархівувати і потім імпортувати не сервер БД. У phpMyAdmin це можна зробити за допомогою пункту основного меню «Імпорт».

Докладніше про цю процедуру я розповідав в одному з попередніх матеріалів.

Функція MAX() – у деяких випадках вона також дозволяє MySQL дізнатися кількість рядків у таблиці. Чому інколи? Зараз поясню. Пам'ятайте перший спосіб підрахунку, який ми розглянули на початку. Він має на увазі, що найбільше значення стовпця id дорівнює числу записів. Застосування цієї функції є свого роду "автоматизацією" того методу. MAX() повертає максимальне значення зазначеного стовпця (id). Для тестування функції ми використовуватимемо таблицю city, яка також входить до складу бази world. Код запиту:

SELECT MAX(`ID`) FROM `city`

В результаті виконання запиту ми отримали значення стовпця останнього рядка. Виходить, що MySQL порахував кількість рядків.

Якщо потрібно, то можна отримати останній запис повністю:

SELECT MAX('ID'), 'Name', 'CountryCode', 'District', 'Population' FROM 'city'

Зверніть увагу, що для вибору перед викликом функції замість «*» ми перераховуємо назву всіх стовпців. Це особливість синтаксису при застосуванні вбудованих функцій MySQL.

Визначення числа записів у PHP

Часто в коді сайтів PHP MySQL кількість рядків потрібно отримати для того, щоб потім передати це значення як вхідний параметр для подальшої обробки. Це можна зробити за допомогою наступного коду:

query("SELECT * FROM `country`")) ( printf("У таблиці %d рядків.\n", $result->num_rows); $result->close(); ) mysqli_close($con); ?>

Щоб визначити кількість записів у таблиці MySQL, необхідно скористатися спеціальною функцією COUNT().

Функція COUNT() повертає кількість записів у таблиці, які відповідають заданому критерію.

Функція COUNT(expr) завжди вважає ті рядки, у яких результатом виразу expr є NOT NULL .

Винятком із цього правила є використання функції COUNT() із зірочкою як аргумент - COUNT(*) . У цьому випадку вважаються всі рядки, незалежно від того, NULL вони або NOT NULL .

Наприклад, функція COUNT(*) повертає загальну кількість записів у таблиці:

SELECT COUNT(*) FROM table_name

Як порахувати кількість записів та вивести на екран

Приклад PHP+MySQL-коду для підрахунку та виведення загальної кількості рядків:

$res = mysql_query("SELECT COUNT(*) FROM table_name") $row = mysql_fetch_row($res); $ total = $ row; // всього записів echo $ total; ?>

Цей приклад ілюструє найпростіший варіант використання функції COUNT(). Але за допомогою цієї функції можна виконувати й інші завдання.

Вказавши певний стовпець таблиці як параметр, функція COUNT(column_name) повертає кількість записів цього стовпця, які містять значення NULL . Записи із значеннями NULL ігноруються.

SELECT COUNT(column_name) FROM table_name

Використовувати функцію mysql_num_rows() не можна, тому що для того, щоб дізнатися загальну кількість записів, потрібно виконати запит SELECT * FROM db, тобто отримати всі записи, а це небажано, тому краще використовувати функцію count.

$result = mysql_query("SELECT COUNT(*) як rec FROM db");

Використання функції COUNT() на прикладі

Ось ще один приклад використання функції COUNT(). Допустимо, є таблиця ice_cream з каталогом морозива, в якій знаходяться ідентифікатори категорій та назви морозива.

Одного разу я зіткнувся з цим завданням, але багато з рішень, що пропонуються в інтернеті, у мене не працювали. І все-таки я знайшов вихід. І навіть не один.

Спосіб 1. phpMyAdmin

Почнемо з найпростішого та очевидного. Припустимо, вам не потрібно використовувати отриманий результат у коді, а просто дізнатися кількість рядків, тільки й усього.

Тоді заходимо до phpMyAdmin (localhost/tools/phpmyadmin — для Денвера), потім вибираємо потрібну базу даних, після цього вибираємо таблицю, кількість рядків у якій потрібно з'ясувати, і бачимо таке повідомлення:

Спосіб 2. COUNT

Ось так виглядає сам SQL-запит:

SQL-запит з умовою:

Реалізація на PHP:

Так сюди можна додати умову. Тоді код виведе число рядків у таблиці, які відповідають умові.

Спосіб 3. mysql_num_rows

Наприклад, у WordPress пости зберігаються в таблиці wp_posts, таким чином ми можемо дізнатися скільки записів (постів) зберігається в базі даних (у таблиці MySQL). Цей код дано тільки для прикладу (або для випадків, коли середовище WordPress не підвантажено), оскільки WordPress підключення до бази даних здійснюється через клас .

У результат будуть включені всі записи. А як дізнатися кількість лише тих, що опубліковані? Для цього нам потрібно трохи змінити SQL-запит.

Починаючи від версії 4.0 в СУБД MySQL з'явилася досить зручна можливість підрахунку кількості всіх записів, що підходять під запит, коли кількість записів обмежується LIMIT'ом. При роботі з пошуком у БД, а також при вибірках з таблиць з великою кількістю записів такий функціонал просто необхідний.

Синтаксису. У запиті SELECT перед списком шпальт необхідно вказати опцію SQL_CALC_FOUND_ROWS. Ось початок опису синтаксису конструкції SELECT.

SELECT




select_expr, … …

Таким чином, виконуючи запит SELECT SQL_CALC_FOUND_ROWS СУБД підрахує повну кількість рядків, що підходять під умову запиту, та зберегти це число у пам'яті. Природно, має сенс запит SELECT SQL_CALC_FOUND_ROWS лише за умови використання обмеження (LIMIT). Відразу після виконання запиту на вибірку для отримання кількості записів потрібно виконати ще один SELECT-запит: SELECT FOUND_ROWS();. В результаті MySQL поверне один рядок з одним полем, в якому зберігатиметься число рядків.

Приклад самих запитів:

> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE number > 100 LIMIT 10;
> SELECT FOUND_ROWS();

Перший запит поверне (виведе) 10 рядків таблиці tbl_name, для яких виконується умова number > 100. Другий виклик команди SELECT поверне кількість рядків, які повернула б перша команда SELECT, якби вона була написана без виразу LIMIT. Хоча при використанні команди SELECT SQL_CALC_FOUND_ROWS, MySQL повинен перерахувати всі рядки в наборі результатів, цей спосіб все одно швидше, ніж без LIMIT, тому що не потрібно надсилати результат клієнту.

Приклад запитів з PHP:

$result = mysql_query("SELECT SQL_CALC_FOUND_ROWS * FROM table1 LIMIT 0, 10", $link);
while ($row = mysql_fetch_assoc($result))
{
var_dump($row);
}

$result = mysql_query("SELECT FOUND_ROWS()", $link);
$num_rows = mysql_result($result, 0);
echo "$num_rows Rows\n";

В результаті виконання коду за умови, що $link вказує на відкрите з'єднання з СУБД, PHP виведе 10 рядків з таблиці table1 , а потім ціле число рядків, відповідних запиту (без урахування LIMIT).

У запитах з UNION SQL_CALC_FOUND_ROWS може поводитися подвійно через те, що LIMIT може з'являтися у кількох місцях. Рахунок рядків може вестися для індивідуальних SELECT-запитів, або для всього запиту після об'єднання.

Ціль SQL_CALC_FOUND_ROWS для UNION полягає в тому, що він повинен повернути кількість рядків, які будуть повернуті без глобального LIMIT. Умови застосування SQL_CALC_FOUND_ROWS з UNION наведені нижче:

• Ключове слово SQL_CALC_FOUND_ROWS має вказуватися в першому операторі SELECT.
• Значення FOUND_ROWS() буде точним лише за умови застосування UNION ALL. Якщо вказано UNION без ALL, відбувається виключення дублікатів і значення FOUND_ROWS() буде лише приблизним.
• Якщо в UNION немає LIMIT, то SQL_CALC_FOUND_ROWS ігнорується і повертається кількість рядків у часовій таблиці, яка створюється для виконання UNION.