Как сделать поиск по базе данных

Введение

На часах было почти 3 часа дня, я хотел было сделать себе послеобеденный кофе, но получил новое задание от тимлида. Необходимо было сделать поиск для онлайн платформы просмотра ТВ, сериалов и фильмов.

Старт

Я приступил к выполнению задачи. Запросы в гугле были разного уровня: от “зачем нужен поиск”, до “имплементация обратного индекса в СУБД”. Вот что удалось найти:

• “Серьезные пацаны” используют для поиска всякие Sphinx и ElasticSearch. Минусы: их нужно настраивать, загонять в них данные и мониторить их потребности.
• Haystack обеспечивает модульный поиск Django. У него простой и понятный API для работы над поиском, который позволяет подключать различные поисковые движки. Однако выявилась проблема в настройке минимального количества символов в системе серча: при значении — 1 он переставал корректно работать.
• Любая современная база данных имеет в себе встроенный полнотекстовый поиск.

Делаем приложения с поиском на Go

Делаем полнотекстовый поиск, который написан на Go.

После прочтения “кучи” статей о настройках поиска в хорошем виде в django, я остановился на PostgreSql, так как у “постгреса” часто появляются новые “плюшки”.

Подготовка окружения

Установка и настройка PostgreSql и Django настолько простая в нашем случае, что вы можете ее сделать по гайду. Также прочтите немного теории.

Индексирование в Django проекте

Индексирование бывает прямым и обратным. Например, если с помощью индексирования мы узнаём, что в левой части супермаркета есть баклажаны, картофель, помидоры и мармелад; в правой — мороженое, сухофрукты, мармелад и хлеб, то это прямой индекс.

Но что если нам нужно найти места, где есть мармелад? Придется пробегать циклом по всем местам и выбирать те, в которых имеется неободимый продукт.

Есть и другое решение. Продолжив читать книгу “Поиск. Индексы. И так далее” мы увидим, что нам может помочь обратный индекс. Он работает противоположно прямому, и после применения можно увидеть такую структуру:

Помидоры — левая сторона, при входе

Картофель — конец, левая сторона

Мармелад — левая сторона, правая сторона

Теперь мы точно знаем, где есть наш мармелад!

Возникает новый вопрос, как безболезненно внедрить эту “фичу” в наш Django проект?

Нашел решение. Благодаря разработчикам “постгреса”, сегодня есть простой в использовании GIN, который работает на основе обратного индекса.

Для использования его в “джанге” нужно всего лишь поставить psycorg и импортировать необходимые зависимости.

Тем самым мы проиндексировали поле title. Теперь давайте посмотрим, создался ли индекс внутри базы данных. Проверить можно командой:

Мы видим, что буквально с помощью одной строки кода мы упростили жизнь. И так, у нас есть проиндексированные данные, но как с ними работать? Будет ли работать ‘__icontains’, если просто указать необходимое поле в запросе?

Давайте разберем детально, почему.

Работа с запросами

Выше мы уже определили модель Movies, давайте продолжим работать с ней же.

Посмотрите, как выглядит обычный запрос Django ORM и Sql.

Чтобы выполнить более сложный запрос, мы должны использовать три новых класса: SearchVector, SearchQuery, SearchRank.

SearchVector

Можно использовать SearchVector для создания нашего запроса с использованием нескольких полей. Затем использовать <filter>.

Так мы нашли фильмы “Wilson” и “The Pursuit of Happiness”. В первом случае, слово “Wilson” содержится в названии, во втором, в описании картины (слово “Wilson” в описании ленты добавил лично, потому что было лень искать слово, которое будет удовлетворять запросу).

SearchQuery

Бывает и такое, когда нужно сделать поиск по нескольким словам в базе данных. Представьте, вы ищете фильм в описании которого содержится фраза “good life”. SearchQuery позволит задать порядок этих слов в описании.

Поиск данных и объектов в базе данных MS SQL Server с помощью бесплатной утилиты dbForge Search

Описание общей потребности в поиске данных и объектов в базе данных

Поиск данных, а также хранимых процедур, таблиц и других объектов в базе данных является достаточно актуальным вопросом в том числе и для C#-разработчиков, а также и для .NET-разработки в целом.

Достаточно часто может возникнуть ситуация, при которой нужно найти:

1. объект базы данных (таблицу, представление, хранимую процедуру, функцию и т д)
2. данные (значение и в какой таблице располагается)
3. фрагмент кода в определениях объектов базы данных

Сначала рассмотрим как можно осуществлять поиск данных и объектов в базе данных с помощью встроенных средств самой СУБД, а затем рассмотрим как это сделать с помощью бесплатной утилиты dbForge Search.

Поиск с помощью встроенных средств самой СУБД

Определить есть ли таблица Employee в базе данных можно с помощью следующего скрипта:

Результат может быть примерно такой:

1. идентификаторы объекта и схемы, где располагается объект
2. название этой схемы и название этого объекта
3. тип объекта и описание этого типа объекта
4. даты и время создания и последней модификации объекта

Результат может быть примерно такой:

Как видно из результата, здесь подстроку “Project” содержат не только две таблицы Project и ProjectSkill, но и также некоторые первичные и внешние ключи.

Чтобы понять кому именно принадлежат данные ключи, добавим в вывод поле parent_object_id и его имя и схему, в которой он располагается следующим образом:

Результатом будет вывод таблицы с детальной информацией о родительских объектах, т е где определены первичные и внешние ключи:

В запросах используются следующие системные объекты:

• таблица sys.all_objects
• скалярная функция schema_name

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

Результат выполнения может быть таким:

Здесь выводятся имена таблиц и в каких столбцах хранится значение, содержащие подстроку “Ramiro”. А также количество найденных входов данной подстроки для найденной пары таблица-колонка.

Чтобы найти объекты, в определениях которых есть заданный фрагмент кода, можно воспользоваться следующими системными представлениями:

Здесь будет выведен идентификатор, название, описание и полное определение объекта.

Поиск с помощью бесплатной утилиты dbForge Search

Однако, более удобно поиск производить с помощью готовых хороших инструментов. Одним из таких инструментов является dbForge Search.

Для вызова этой утилиты в окне SSMS нажмите на кнопку .

Появится следующее окно поиска:

Обратите внимание на верхнюю панель (слева направо):

1. можно переключать режим поиска (ищем DDL (объекты) или данные)
2. непосредственно что ищем (какую подстроку)
3. учитывать ли регистр, искать точное соответствие слову, искать вхождения:

В режиме поиска данных изменится только выбор типов объектов:

А именно будут доступны для выбора только таблицы, где и хранятся собственно сами данные:

Теперь как и раньше найдем все вхождения подстроки “Project” в названиях объектов:

Как видно, был выбран режим поиска по DDL-объектам, заполнено что ищем-строка “Project”, остальное все было по умолчанию.

При выделении найденного объекта внизу отображается код определения данного объекта или всего его родительского объекта.

Также можно переместить навигацию на найденный объект, щелкнув на кнопку :

Можно также сгруппировать найденные объекты по их типу:

Обратите внимание, что выводятся даже те таблицы, в которых есть поля, в именах которых содержится подстрока “Project”. Однако, напомним, что режим поиска можно менять: искать полное соответствие/частичное/учитывать регистр или нет.

Теперь найдем значение “Ramiro” по всем таблицам:

Обратите внимание, что внизу отображаются все строки, в которых содержится подстрока “Ramiro” выбранной таблицы Employee.

Также можно переместить навигацию к найденному объекту, нажав как и ранее на кнопку :

Таким образом мы можем искать нужные объекты и данные в базе данных.

Заключение

Были рассмотрены способы поиска как самих данных, так и объектов в базе данных как с помощью встроенных средств самой СУБД MS SQL Server, так и с помощью бесплатной утилиты dbForge Search.

Также от компании Devart есть и ряд других бесплатных готовых решений, полный список которых можно посмотреть здесь.

Search text in fields in every table of a MySQL database

I want to search in all fields from all tables of a MySQL database a given string, possibly using syntax as:

Is it possible to do something like this?

27 Answers 27

You could do an SQLDump of the database (and its data) then search that file.

If you have phpMyAdmin installed use its ‘Search’ feature.

• Select your DB
• Be sure you do have a DB selected (i.e. not a table, otherwise you’ll get a completely different search dialog)
• Click ‘Search’ tab
• Choose the search term you want
• Choose the tables to search

I have used this on up to 250 table/10GB databases (on a fast server) and the response time is nothing short of amazing.

You can peek into the information_schema schema. It has a list of all tables and all fields that are in a table. You can then run queries using the information that you have gotten from this table.

The tables involved are SCHEMATA, TABLES and COLUMNS. There are foreign keys such that you can build up exactly how the tables are created in a schema.

This is the simple way that’s i know. Select your DB in PHPMyAdmin, and go to the «Search» tab and write what you want to find and where you will searching for. Select all tables if you will search the words from all tables. Then «GO» and look the result.

If you are avoiding stored procedures like the plague, or are unable to do a mysql_dump due to permissions, or running into other various reasons.

I would suggest a three-step approach like this:

1) Where this query builds a bunch of queries as a result set.

Results should look like this:

2) You can then just Right Click and use the Copy Row (tab-separated)

3) Paste results in a new query window and run to your heart’s content.

Detail: I exclude system schema’s that you may not usually see in your workbench unless you have the option Show Metadata and Internal Schemas checked.

I did this to provide a quick way to ANALYZE an entire HOST or DB if needed or to run OPTIMIZE statements to support performance improvements.

I’m sure there are different ways you may go about doing this but here’s what works for me:

Как сделать поиск по базе данных

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

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 ;

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 ;

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 ;