Реклама
Спонсорские ссылки
-
Статья
Версия для печати
Обсудить на форуме
MySQL. Поиск строки по всей базе.


Автор: RXL.
Дата написания: 3.12.2009.
Права на статью принадлежат автору и Клубу программистов «Весельчак У».

Содержание.


Вводная.

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

Информация о таблицах и столбцах.

MySQL предоставляет информацию об объектах баз данных через специальную схему «INFORMATION_SCHEMA». Например, так выглядит состав таблиц этой схемы в MySQL 5.0:

mysql> SHOW TABLES FROM information_schema;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| 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                                 |
+---------------------------------------+
17 rows in set (0.00 sec)

Информация о колонках:

mysql> DESC information_schema.columns;
+--------------------------+--------------+------+-----+---------+-------+
| Field                    | Type         | Null | Key | Default | Extra |
+--------------------------+--------------+------+-----+---------+-------+
| TABLE_CATALOG            | varchar(512) | YES  |     | NULL    |       |
| TABLE_SCHEMA             | varchar(64)  | NO   |     |         |       |
| TABLE_NAME               | varchar(64)  | NO   |     |         |       |
| COLUMN_NAME              | varchar(64)  | NO   |     |         |       |
| ORDINAL_POSITION         | bigint(21)   | NO   |     | 0       |       |
| COLUMN_DEFAULT           | longtext     | YES  |     | NULL    |       |
| IS_NULLABLE              | varchar(3)   | NO   |     |         |       |
| DATA_TYPE                | varchar(64)  | NO   |     |         |       |
| CHARACTER_MAXIMUM_LENGTH | bigint(21)   | YES  |     | NULL    |       |
| CHARACTER_OCTET_LENGTH   | bigint(21)   | YES  |     | NULL    |       |
| NUMERIC_PRECISION        | bigint(21)   | YES  |     | NULL    |       |
| NUMERIC_SCALE            | bigint(21)   | YES  |     | NULL    |       |
| CHARACTER_SET_NAME       | varchar(64)  | YES  |     | NULL    |       |
| COLLATION_NAME           | varchar(64)  | YES  |     | NULL    |       |
| COLUMN_TYPE              | longtext     | NO   |     | NULL    |       |
| COLUMN_KEY               | varchar(3)   | NO   |     |         |       |
| EXTRA                    | varchar(20)  | NO   |     |         |       |
| PRIVILEGES               | varchar(80)  | NO   |     |         |       |
| COLUMN_COMMENT           | varchar(255) | NO   |     |         |       |
+--------------------------+--------------+------+-----+---------+-------+
19 rows in set (0.00 sec)

Зачем я это рассказываю? Затем, что если вы будете писать руками SQL-запрос для каждой таблицы в схеме да еще и для каждого подходящего столбца — можно смело менять профессию, т.к. это точно не ваше. Нормальный человек найдет обобщенное решение, без тупого и бессмысленного труда. Это решение — создать необходимые запросы автоматически, и поможет нам в этом информация о таблицах и столбцах из таблицы «COLUMNS».
Нам нужно отобрать таблицы, в которых есть колонки подходящих для нас типов: CHAR, VARCHAR, TEXT и т.п. Всего 12 типов. Фильтровать будем по полю «DATA_TYPE». Такой запрос наглядно показывает типы столбцов в таблицах схемы «test»:

Код: (MySQL)
SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'test'
  AND data_type IN (
    'char', 'varchar', 'binary', 'varbinary',
    'tinytext', 'text', 'mediumtext', 'longtext',
    'tinyblob', 'blob', 'mediumblob', 'longblob'
  );

Если не на чем экспериментировать, воспользуйтесь этой таблицей:

Код: (MySQL)
CREATE TABLE `t2` (
  `f_vch` varchar(10) default NULL,
  `f_bin` varbinary(10) default NULL,
  `f_tblob` tinyblob,
  `f_blob` blob,
  `f_ttext` tinytext,
  `f_text` text,
  `f_ltext` longtext
);

Генерация SQL-кода.

Составим SQL-запрос, который создаст нам набор SQL-запросов для каждой таблицы указанной схемы.

Код: (MySQL)
SELECT CONCAT(
    'SELECT "', c.table_name, '" `$table$`',
    ' FROM `', c.table_schema, '`.`', c.table_name, '`'
    ' WHERE ', GROUP_CONCAT(
      CONCAT('`', c.column_name, '`')
      SEPARATOR ' LIKE "%test%" OR '
    ), ' LIKE "%test%"',
    ' LIMIT 1'
  ) query
FROM information_schema.columns c
WHERE c.table_schema = 'test'
  AND c.data_type IN (
    'char', 'varchar', 'binary', 'varbinary',
    'tinytext', 'text', 'mediumtext', 'longtext',
    'tinyblob', 'blob', 'mediumblob', 'longblob'
  )
GROUP BY c.table_name;

Например, у меня запрос вернул только одну строку для схемы «test»:

Код: (MySQL)
SELECT "t2" `$table$`
 FROM `test`.`t2`
 WHERE `f_vch` LIKE "%test%"
  OR `f_bin` LIKE "%test%"
  OR `f_tblob` LIKE "%test%"
  OR `f_blob` LIKE "%test%"
  OR `f_ttext` LIKE "%test%"
  OR `f_text` LIKE "%test%"
  OR `f_ltext` LIKE "%test%"
  OR `f_vbin` LIKE "%test%"
 LIMIT 1

Полученный запрос будет искать во всех текстовых полях вхождение строки «test» и остановится, найдя первую подходящую строку. Возможно, в него стоит добавить ORDER BY NULL для оптимизации под большие таблицы. Выводить саму строку я не стал — она может быть большой и неудобоваримой для просмотра в консоли. Тут уже дело вкуса — переделайте запрос, как вам захочется. Я вот переделал так:

Код: (MySQL)
SELECT CONCAT(
    'SELECT "', c.table_name, '" `$table$`, ', GROUP_CONCAT(
      CONCAT('SUM(IF(`', c.column_name, '` LIKE "%test%", 1, 0)) `', c.column_name, '`')
      SEPARATOR ', '
    ),
    ' FROM `', c.table_schema, '`.`', c.table_name, '`'
    ' WHERE ', GROUP_CONCAT(
      CONCAT('`', c.column_name, '` LIKE "%test%"')
      SEPARATOR ' OR '
    )
  ) query
FROM information_schema.columns c
WHERE c.table_schema = 'test'
  AND c.data_type IN (
    'char', 'varchar', 'binary', 'varbinary',
    'tinytext', 'text', 'mediumtext', 'longtext',
    'tinyblob', 'blob', 'mediumblob', 'longblob'
  )
GROUP BY c.table_name;

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

mysql> SELECT "t2" `$table$`,
  SUM(IF(`f_vch` LIKE "%test%", 1, 0)) `f_vch`,
  SUM(IF(`f_bin` LIKE "%test%", 1, 0)) `f_bin`,
  SUM(IF(`f_tblob` LIKE "%test%", 1, 0)) `f_tblob`,
  SUM(IF(`f_blob` LIKE "%test%", 1, 0)) `f_blob`,
  SUM(IF(`f_ttext` LIKE "%test%", 1, 0)) `f_ttext`,
  SUM(IF(`f_text` LIKE "%test%", 1, 0)) `f_text`,
  SUM(IF(`f_ltext` LIKE "%test%", 1, 0)) `f_ltext`,
  SUM(IF(`f_vbin` LIKE "%test%", 1, 0)) `f_vbin`
 FROM `test`.`t2`
 WHERE `f_vch` LIKE "%test%"
  OR `f_bin` LIKE "%test%"
  OR `f_tblob` LIKE "%test%"
  OR `f_blob` LIKE "%test%"
  OR `f_ttext` LIKE "%test%"
  OR `f_text` LIKE "%test%"
  OR `f_ltext` LIKE "%test%"
  OR `f_vbin` LIKE "%test%";
+---------+-------+-------+---------+--------+---------+--------+---------+--------+
| $table$ | f_vch | f_bin | f_tblob | f_blob | f_ttext | f_text | f_ltext | f_vbin |
+---------+-------+-------+---------+--------+---------+--------+---------+--------+
| t2      |     1 |     0 |       0 |      0 |       0 |      0 |       0 |      0 |
+---------+-------+-------+---------+--------+---------+--------+---------+--------+
1 row in set (0.00 sec)

Автоматизация выполнения.

Теперь напишем процедуру, которая сделает все автоматически: создаст запросы, подставит в них искомый текст и выдаст нам статистику по вхождению искомой строки. Исполнять генерируемые запросы будем посредством prepared statements.
Заметьте, что искомый текст я вставлял непосредственно в запрос. Это ограничение агрегатной функции GROUP_CONCAT — она допускает только константную строку в качестве разделителя — переменную туда подставить не удастся. (Данное ограничение позже решилось выносом подстановки искомой строки из SEPARATOR в группируемое выражение.)
Для решения этой проблемы в процедуре я делаю подстановку функцией REPLACE в уже готовом запросе. Искомую строку стоит предварительно обработать для экранирования недопустимых символов, чтобы не было неприятных сбоев. Здесь я сделал лишь замену одинарной кавычки на обратный слеш и кавычку, но правильнее будет обработать искомую строку функцией QUOTE и удалить из результата первый и последний символ (это тоже одинарные кавычки — их подставляет QUOTE).
Готовый и протестированный код процедуры:

Код: (MySQL)
DROP PROCEDURE IF EXISTS find_overall;
delimiter $$
CREATE PROCEDURE find_overall(
  p_dbname VARCHAR(64),
  p_search VARCHAR(255)
)
BEGIN
  DECLARE query TEXT;
  DECLARE eof BOOL;
  DECLARE curs_tables CURSOR FOR
    SELECT CONCAT(
        'SELECT "', c.table_name, '" `$table$`, ', GROUP_CONCAT(
          CONCAT(
            'SUM(IF(`', c.column_name, '` LIKE "%%", 1, 0))',
            ' `', c.column_name, '`'
          )
          SEPARATOR ', '
        ),
        ' FROM `', c.table_schema, '`.`', c.table_name, '`'
        ' WHERE ', GROUP_CONCAT(
          CONCAT('`', c.column_name, '`')
          SEPARATOR ' LIKE "%%" OR '
        ), ' LIKE "%%"'
      ) query
    FROM information_schema.columns c
    WHERE c.table_schema = p_dbname
      AND c.data_type IN (
        'char', 'varchar', 'binary', 'varbinary',
        'tinytext', 'text', 'mediumtext', 'longtext',
        'tinyblob', 'blob', 'mediumblob', 'longblob'
      )
    GROUP BY c.table_name;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET eof = TRUE;

  OPEN curs_tables;
  SET eof = FALSE;

  L_tables: LOOP
    FETCH curs_tables INTO query;

    IF eof THEN LEAVE L_tables; END IF;

    SET @stm = REPLACE(query, '"%%"',
      CONCAT('"%', REPLACE(p_search, "'", "\\'") , '%"')
    );
    PREPARE find_overall FROM @stm;
    EXECUTE find_overall;
    DROP PREPARE find_overall;
  END LOOP;

  CLOSE curs_tables;
END;$$
delimiter ;

Смотрите, разбирайтесь. Потенциально данный код не защищен от SQL-injection, но и назначение у него чисто административное.
Вопросы можно задать на нашем форуме. Статья писалась быстро и, возможно что-то, стоит переделать или добавить — буду раз замечаниям и предложениям.


Вдогонку.

Вдогонку к уже выпущенной статье: в приведенной процедуре есть неприятное ограничение, накладываемое функцией GROUP_CONCAT на длину производимой ею строки. Переписал процедуру и исправил некоторые недочеты.

Код: (MySQL)
DROP PROCEDURE IF EXISTS find_overall;
delimiter $$
CREATE PROCEDURE find_overall(
  p_dbname VARCHAR(64),
  p_search VARCHAR(255)
)
BEGIN
  DECLARE search_string VARCHAR(512);
  DECLARE table_name VARCHAR(64);
  DECLARE column_name VARCHAR(64);
  DECLARE selections TEXT;
  DECLARE conditions TEXT;
  DECLARE eof BOOL;
  DECLARE curs_tables CURSOR FOR
    SELECT DISTINCT c.table_name
    FROM information_schema.columns c
    WHERE c.table_schema = p_dbname
      AND c.data_type IN (
        'char', 'varchar', 'binary', 'varbinary',
        'tinytext', 'text', 'mediumtext', 'longtext',
        'tinyblob', 'blob', 'mediumblob', 'longblob'
      );
  DECLARE curs_columns CURSOR FOR
    SELECT c.column_name
    FROM information_schema.columns c
    WHERE c.table_schema = p_dbname
      AND c.table_name = table_name
      AND c.data_type IN (
        'char', 'varchar', 'binary', 'varbinary',
        'tinytext', 'text', 'mediumtext', 'longtext',
        'tinyblob', 'blob', 'mediumblob', 'longblob'
      );
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET eof = TRUE;

  SET search_string = QUOTE(p_search);
  SET search_string = SUBSTR(search_string, 2, LENGTH(search_string) - 2);

  OPEN curs_tables;
  SET eof = FALSE;

  L_tables: LOOP
    FETCH curs_tables INTO table_name;

    IF eof THEN LEAVE L_tables; END IF;

    OPEN curs_columns;
    SET selections = '';
    SET conditions = '';

    L_columns: LOOP
      FETCH curs_columns INTO column_name;

      IF eof THEN LEAVE L_columns; END IF;

      SET selections = CONCAT(selections,
        IF(selections = '', '', ', '),
        'SUM(IF(`', column_name, '` LIKE "%', search_string, '%", 1, 0))',
        ' `', column_name, '`'
      );
      SET conditions = CONCAT(conditions,
        IF(conditions = '', '', ' OR '),
        '`', column_name, '` LIKE "%', search_string, '%"'
      );
    END LOOP;

    CLOSE curs_columns;
    SET eof = FALSE;

    SET @stm = CONCAT(
      'SELECT "', table_name, '" `$table$`, ', selections,
      ' FROM `', p_dbname, '`.`', table_name, '`',
      ' WHERE ', conditions
    );
    PREPARE find_overall FROM @stm;
    EXECUTE find_overall;
    DROP PREPARE find_overall;
  END LOOP;

  CLOSE curs_tables;
END;$$
delimiter ;

Быстродействие проверено на схеме с 62-я таблицам MYISAM общим объемом 148 МБ (только файлы MYD), на сервере с двумя одноядерными Xeon 2.8 ГГц и 3 ГБ DDR1 ECC.

Query OK, 0 rows affected (3.07 sec)

Повторный вызов отрабатывает за 2.58 секунды.
Следующую модификацию процедуры find_overall не рекомендую запускать в консоли. Он вернет множество рекордсетов — по одному на каждую проверяемую таблицу.

Код: (MySQL)
DROP PROCEDURE IF EXISTS find_overall;
delimiter $$
CREATE PROCEDURE find_overall(
  p_dbname VARCHAR(64),
  p_search VARCHAR(255)
)
BEGIN
  DECLARE search_string VARCHAR(512);
  DECLARE table_name VARCHAR(64);
  DECLARE column_name VARCHAR(64);
  DECLARE selections TEXT;
  DECLARE conditions TEXT;
  DECLARE eof BOOL;
  DECLARE curs_tables CURSOR FOR
    SELECT DISTINCT c.table_name
    FROM information_schema.columns c
    WHERE c.table_schema = p_dbname
      AND c.data_type IN (
        'char', 'varchar', 'binary', 'varbinary',
        'tinytext', 'text', 'mediumtext', 'longtext',
        'tinyblob', 'blob', 'mediumblob', 'longblob'
      );
  DECLARE curs_columns CURSOR FOR
    SELECT c.column_name
    FROM information_schema.columns c
    WHERE c.table_schema = p_dbname
      AND c.table_name = table_name
      AND c.data_type IN (
        'char', 'varchar', 'binary', 'varbinary',
        'tinytext', 'text', 'mediumtext', 'longtext',
        'tinyblob', 'blob', 'mediumblob', 'longblob'
      );
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET eof = TRUE;

  SET search_string = QUOTE(p_search);
  SET search_string = SUBSTR(search_string, 2, LENGTH(search_string) - 2);

  OPEN curs_tables;
  SET eof = FALSE;

  L_tables: LOOP
    FETCH curs_tables INTO table_name;

    IF eof THEN LEAVE L_tables; END IF;

    OPEN curs_columns;
    SET selections = '';
    SET conditions = '';

    L_columns: LOOP
      FETCH curs_columns INTO column_name;

      IF eof THEN LEAVE L_columns; END IF;

      SET selections = CONCAT(selections,
        IF(selections = '', '', ', '),
        'IF(`', column_name, '` LIKE "%', search_string, '%", `', column_name, '`, NULL)',
        ' `', column_name, '`'
      );
      SET conditions = CONCAT(conditions,
        IF(conditions = '', '', ' OR '),
        '`', column_name, '` LIKE "%', search_string, '%"'
      );
    END LOOP;

    CLOSE curs_columns;
    SET eof = FALSE;

    SET @stm = CONCAT(
      'SELECT "', table_name, '" `$table$`, ', selections,
      ' FROM `', p_dbname, '`.`', table_name, '`',
      ' WHERE ', conditions
    );
    PREPARE find_overall FROM @stm;
    EXECUTE find_overall;
    DROP PREPARE find_overall;
  END LOOP;

  CLOSE curs_tables;
END;$$
delimiter ;

Версия для печати
Обсудить на форуме