Автор:
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»:
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'
);
Если не на чем экспериментировать, воспользуйтесь этой таблицей:
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-запросов для каждой таблицы указанной схемы.
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»:
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 для оптимизации под большие таблицы. Выводить саму строку я не стал — она может быть большой и неудобоваримой для просмотра в консоли. Тут уже дело вкуса — переделайте запрос, как вам захочется. Я вот переделал так:
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).
Готовый и протестированный код процедуры:
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 на длину производимой ею строки. Переписал процедуру и исправил некоторые недочеты.
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 не рекомендую запускать в консоли. Он вернет множество рекордсетов — по одному на каждую проверяемую таблицу.
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 ;