Форум программистов, компьютерный форум, киберфорум
SQLite
Войти
Регистрация
Восстановить пароль
Блоги Сообщество Поиск  
 
 
17 / 5 / 0
Регистрация: 16.04.2016
Сообщений: 371

Когда лучше разбивать бд на отдельные таблицы и побитовые маски в бд

17.10.2026, 01:14. Показов 3908. Ответов 65
Метки нет (Все метки)

Студворк — интернет-сервис помощи студентам
Всех приветствую. У меня есть два вопроса по правильной организации структуры бд. два бесплатных ии (браузерный ии от гугла и gpt) ответили по-разному на мой первый вопрос.

1. Часть моей бд будет содержать Библию. Подскажите пожалуйста,как сделать бд минимального объёма? У меня есть несколько вариантов. Первый вариант для стиха создать таблицу с полями номер_книги (берётся из id с таблицей с книгами),номер_главы,номер_стиха,текст_с тиха и скрытое поле rowid,которое есть в sqlite и не только (все поля,кроме текста стиха и row_id - составной первичный ключ). Второй вариант - создать две таблицы. В первой - номер_книги,номер_главы,rowid (номер книги и номер главы - составной первичный ключ),а во второй - поля номер стиха,id (ссылается на rowid из первой таблицы) (эти поля составной первичный ключ),текст стиха и rowid (id стиха,который,к примеру,будет использоваться для зачал,которые,к примеру,читаются на православной Литургии,вечерней службе и т.д). А может есть ещё более оптимальный вариант. При каком варианте объём бд будет минимальный,а при каком - скорость выполнения sql запросов максимальной. Объясните пожалуйста почему. Какой бы вариант,и почему лучше выбрать,на Ваш взгляд,для android приложения. В первом варианте меня смущает дублирование данных для номера книги и номера главы. Также на будущее объясните пожалуйста,в каких случаях в бд лучше разбивать таблицы с большим количеством столбцов на несколько таблиц,а в каких - объединить в одну таблицу. К примеру что лучше сделать - отдельную таблицу с молитвами,отдельную - с акафистами,а отдельную - с канонами,или объединить молитвы,акафисты и каноны в одну таблицу,добавив числовое поле для молитвы со значением 0,для акафиста со значением 1,а для канона - со значением 2. Почему тот или иной вариант на ваш взгляд лучше? Понимаю,что,к примеру,для Библии уже есть куча готовых бд,но во-первых я не уверен,что найду оптимальную по структуре бд,а во-вторых мне это важно понимать и в будущем,поскольку у меня,скорее всего,будут похожие случаи. В моём случаи дублируется два числовых столбца,а если таких столбцов будет три или более (я понимаю,что текстовые столбцы дублироваться не должны)?

2. С целью оптимизации моей бд (как я ранее писал,Библия - это часть моей бд) я хочу использовать побитовые маски,к примеру чтобы отмечать,в каких православных поместных Церквях отмечается тот или иной праздник. Я спрашивал у гугловского ии в браузере мнение по этому поводу и он ответил мне,что хоть объём таблицы будет меньше,но поиск по бд замедлится,поскольку sqlite вынужден будет вычислять побитовое выражение для каждого значения таблицы. Также ии ответил мне,что при таком подходе я смогу хранить только 64 значения (я имею ввиду id записи). Также я понимаю,что поскольку каскадное удаление/обновление не будет работать,мне нужно будет написать соответствующий триггер,который,кстати,браузерный ии мне тоже подсказал. А вы что думаете по этому поводу? Использовали ли Вы на своём опыте побитовые маски в sqlite или в каких-либо диалектах sql? Интересно,может быть есть какие-то диалекты sql,которые поддерживают побитовый поиск,что ускорит работу с бд? Заранее благодарю всех за помощь.
0
IT_Exp
Эксперт
34794 / 4073 / 2104
Регистрация: 17.06.2006
Сообщений: 32,602
Блог
17.10.2026, 01:14
Ответы с готовыми решениями:

Как из таблицы со списком, когда аудитории заняты, собрать таблицу со списком когда они свободны
Есть таблица: ID НомерАудитории Дата ВремяНачалааЗанятия ВремяКонца занятия 1 123 ...

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

Проектирование таблицы Клиенты(и юрид. и физ. лица)Нужно ли разбивать на подтаблицы?
Вопрос в следуещем: есть таблица Клиенты, в которой находится некая общая информация про клиента....

65
923 / 296 / 58
Регистрация: 01.06.2023
Сообщений: 847
30.10.2026, 23:12
Студворк — интернет-сервис помощи студентам
Цитата Сообщение от КАВ Посмотреть сообщение
Ну если текст сжатый,значит sqlite не будет искать текст в тех записях. А вот в несжатых записях текст искаться будет.
Плохо себе представляю запрос в котором не будет искать. Давайте не много порассуждаем.

Есть одна таблица с текстами. Вроде других предложений не было. Сжатые и не сжатые тексты помещаются в одно единственное поле text_value (если я правильно понял ход Ваших мыслей).

SQL
1
2
3
4
5
CREATE TABLE texts_content (
  id_text INTEGER,
  id_lang INTEGER,
  text_value text
)
Единственный вариант (если только не использовать FTS, но он противопоказан так как база не влезает уже при текущей структуре) это использовать оператор like

SQL
1
SELECT * FROM texts_content WHERE text_value LIKE '%житие мое%'
при таком раскладе движок субд будет сравнивать поле из каждой записи с шаблоном и не важно в каком формате данные там лежат. то что он не будет находить, вовсе не значит что не будет пытаться находить. Попутно еще можно ловить ошибку декодировки utf-8.

Поэтому все таки рекомендую отделять мух от котлет хранить раздельно данные разных типов, голые тексты в одном поле, сжатые в другом. Физически это добавит лишь 1 байт на запись. Средствами только субд поиск будет доступен только по необработанным текстам.
0
17 / 5 / 0
Регистрация: 16.04.2016
Сообщений: 371
30.10.2026, 23:49  [ТС]
при таком раскладе движок субд будет сравнивать поле из каждой записи с шаблоном и не важно в каком формате данные там лежат.
Вот я это и хочу сделать,используя fts. Просто,вроде,строки индексируются,т.е fts ускоряет поиск.
так как база не влезает уже при текущей структуре
Ну почему же? В целом влезает. Да,конечно,как я ранее писал,есть отдельные случаи,но это,в основном,исключения.
Попутно еще можно ловить ошибку декодировки utf-8.
А вот это как раз аргумент,чтобы сделать два поля,или эту ошибку как-то можно обойти? Тогда,кстати,если перекодировать в cp 1251,то эта ошибка,наверное,тоже будет появляться?
0
923 / 296 / 58
Регистрация: 01.06.2023
Сообщений: 847
31.10.2026, 00:25
Цитата Сообщение от КАВ Посмотреть сообщение
Тогда,кстати,если перекодировать в cp 1251,то эта ошибка,наверное,тоже будет появляться?
Цитата Сообщение от КАВ Посмотреть сообщение
Вот я это и хочу сделать,используя fts. Просто,вроде,строки индексируются,т.е fts ускоряет поиск.
Если будете использовать FTS, и поиск текста только по его индексам, то в целом не так важно как будут храниться оригиналы текстов. После разворота БД выполните разово индексацию текстов. При этом текст сначала нужно декодировать в обычный юникод затем нормализованный/лемматизированный текст можно передавать в индекс.
0
Невнимательный
 Аватар для ft4l
3148 / 1386 / 362
Регистрация: 08.02.2013
Сообщений: 8,199
Записей в блоге: 2
31.10.2026, 00:32
Цитата Сообщение от КАВ Посмотреть сообщение
хочу сделать,используя fts
Надкюсь модераторы не удалят за ответ от ИИ.
Но это не решение, а просто для справки спросил у гугла
где-то ксть такие примеры,
чтобы сами данные хранились либо в .zip архиве, либо с sqlar,
а а для индексации поиска по ней использовался fts5 ?

Говорит есть и много.
ИИ
Да, такое решение возможно и активно используется для создания легковесных поисковых систем по архивам документов.
Комбинация встроенного в SQLite расширения SQLar (SQLite Archive) или работы с .zip и модуля полнотекстового поиска FTS5 — отличный паттерн. Для этого используется концепция external content tables (внешнего контента) в FTS5. Индекс FTS5 хранит только поисковые токены, а сами сжатые данные (файлы) остаются в архиве/BLOB и не дублируются в поисковой таблице. [1] (https://sqlite.org/sqlar/), [2] (https://www.rldp.ru/sqlite/sqlite344/sqlar.html), [3] (https://labex.io/ru/tutorials/... ing-552551), [4] (https://coddy.tech/docs/sqlite/full-text-search)
Ниже приведены готовые практические примеры для обоих вариантов.

Вариант 1: Использование SQLar + FTS5 (Самый нативный путь)

Таблица sqlar создается стандартным образом (или автоматически при использовании утилиты sqlar). Поле data содержит сжатые с помощью zlib бинарные данные.
Поскольку FTS5 не умеет индексировать бинарный сжатый BLOB напрямую, нам нужно использовать встроенную в SQLar функцию sqlar_uncompress(), чтобы распаковывать текст «на лету» во время индексации. [1] (https://www.sqlite.org/draft/sqlar.html), [2] (https://www.rldp.ru/sqlite/sqlite344/sqlar.html), [3] (https://sqlite.org/sqlar/)

Пошаговый SQL-пример:

sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-- 1. Включаем расширение SQLar (если оно не вкомпилировано по умолчанию)
-- Создаем стандартную таблицу архива SQLar
CREATE TABLE sqlar(
  name TEXT PRIMARY KEY,  -- Имя файла / путь
  mode INT,               -- Права доступа (Unix-style)
  mtime INT,              -- Время модификации
  sz INT,                 -- Оригинальный размер
  DATA BLOB               -- Сжатый контент (zlib)
);
 
-- 2. Создаем виртуальную таблицу FTS5 с внешним контентом (content='sqlar')
-- Мы будем индексировать имя файла и его текстовое содержимое
CREATE VIRTUAL TABLE sqlar_search USING fts5(
  name, 
  content,
  content='sqlar',
  content_rowid='rowid'  -- привязываем к rowid оригинальной таблицы
);
 
-- 3. Создаем триггер для автоматической индексации при добавлении файлов в архив
-- Функция sqlar_uncompress распакует данные, чтобы FTS5 смог прочитать текст
CREATE TRIGGER sqlar_ai AFTER INSERT ON sqlar BEGIN
  INSERT INTO sqlar_search(rowid, name, content) 
  VALUES (
    NEW.rowid, 
    NEW.name, 
    CAST(sqlar_uncompress(NEW.data, NEW.sz) AS TEXT)
  );
END;
 
-- Триггер на удаление из архива (чтобы чистить поисковый индекс)
CREATE TRIGGER sqlar_ad AFTER DELETE ON sqlar BEGIN
  INSERT INTO sqlar_search(sqlar_search, rowid, name, content) 
  VALUES('delete', OLD.rowid, OLD.name, CAST(sqlar_uncompress(OLD.data, OLD.sz) AS TEXT));
END;
Используйте код с осторожностью.

Как искать:

sql
SQL
1
2
3
4
5
6
7
8
-- Поиск слова "протокол" внутри всех сжатых файлов архива
SELECT name, sqlar_uncompress(DATA, sz) AS file_text
FROM sqlar
WHERE rowid IN (
    SELECT rowid 
    FROM sqlar_search 
    WHERE content MATCH 'протокол'
);
Используйте код с осторожностью.

Вариант 2: Использование стандартного .zip архива (Виртуальные таблицы)

Если данные должны физически лежать в стандартном .zip файле, в экосистеме SQLite существует официальное расширение zipfile. Оно представляет любой .zip архив как виртуальную таблицу SQLite, с которой можно работать обычными SQL-запросами.

Пошаговый SQL-пример:

sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 1. Подгружаем zip-архив как виртуальную таблицу (расширение должно быть активно)
CREATE VIRTUAL TABLE my_zip USING zipfile('documents.zip');
 
-- Посмотрим структуру, которую дает zipfile автоматически:
-- (name TEXT, mode INT, mtime INT, sz INT, rawdata BLOB, data BLOB)
-- Поле `data` здесь автоматически возвращает РАСПАКОВАННЫЙ текст/данные.
 
-- 2. Создаем FTS5 таблицу для индексации содержимого архива
CREATE VIRTUAL TABLE zip_search USING fts5(
  name, 
  body, 
  content='' -- Используем "бесконтентовую" или внешнюю структуру
);
 
-- 3. Первоначальная индексация всего ZIP-архива одной командой:
INSERT INTO zip_search(rowid, name, body)
SELECT rowid, name, CAST(DATA AS TEXT) 
FROM my_zip 
WHERE DATA IS NOT NULL;
Используйте код с осторожностью.

Полнотекстовый поиск по ZIP-файлу:

sql
SQL
1
2
3
4
5
6
7
8
-- Ищем файлы, содержащие "инвойс" и "2026"
SELECT name, DATA 
FROM my_zip 
WHERE rowid IN (
  SELECT rowid 
  FROM zip_search 
  WHERE zip_search MATCH 'инвойс AND 2026'
);
Используйте код с осторожностью.

Важные нюансы реализации:

1. Поддержка расширений: Полнотекстовый движок fts5 встроен в большинство дистрибутивов SQLite (Python, Node.js, CLI). Модули sqlar и zipfile присутствуют в официальном CLI (начиная с версии 3.22/3.23). При использовании в языках программирования (например, в Python через sqlite3) может потребоваться скомпилировать SQLite с флагами -DSQLITE_ENABLE_SQLAR или загрузить соответствующую .so/.dll библиотеку (ext/misc/sqlar.c). [1] (https://thecode.media/sqlite/), [2] (https://www.sqlite.org/draft/sqlar.html)
2. Память и скорость: Использование content='table_name' в FTS5 гарантирует, что база данных не раздуется в 2 раза. Однако при обновлении или удалении данных триггерам FTS5 требуется доступ к несжатому контенту, поэтому функция sqlar_uncompress вызывается прямо в теле триггера. [1] (https://coddy.tech/docs/sqlite/full-text-search)

Однако, насколько я знаю по mysql, бывает расширенный поиск, типа как со звёздочками, и поиск например точных словосочетаний, которые выполнялись частично по индексам, а частично по данным... так-что трудно сказать как такое должно работать...
Да и записи для такого вроде сподручнее немелкими файлами, а не по пару строк....

... немного уменьшать вес индексов можно добавляя стоп-слова... которые не будут индексироваться
SQL
1
2
3
4
5
CREATE VIRTUAL TABLE articles_search USING fts5(
  title, 
  body,
  tokenize = "unicode61 remove_diacritics 1 stopwords 'и в на что как с по'"
);
0
17 / 5 / 0
Регистрация: 16.04.2016
Сообщений: 371
31.10.2026, 01:10  [ТС]
После разворота БД выполните разово индексацию текстов
Так,как показал в примере человек после Вашего сообщения? Как я понял,мне не нужно будет что-то мудрить,раз уже sqlite через готовые расширения может сжимать бд. Кстати Вы писали что,мол,то или иное поле будет занимать в незаполненном состоянии только один байт. Как я понимаю размер бд увеличится в минимальном случаи на 1 байт умноженный на количество записей в той или иной таблице.
0
923 / 296 / 58
Регистрация: 01.06.2023
Сообщений: 847
31.10.2026, 02:58
Цитата Сообщение от КАВ Посмотреть сообщение
Кстати Вы писали что,мол,то или иное поле будет занимать в незаполненном состоянии только один байт. Как я понимаю размер бд увеличится в минимальном случаи на 1 байт умноженный на количество записей в той или иной таблице.
да верно, поля типа null занимает 1 байт на одну запись

Цитата Сообщение от КАВ Посмотреть сообщение
Как я понял,мне не нужно будет что-то мудрить,раз уже sqlite через готовые расширения может сжимать бд.
штатно не может, нужно подключать расширения, как это сделать и можно ли вообще смотрите в той среде где разрабатываете приложение
0
Надоела реклама? Зарегистрируйтесь и она исчезнет полностью.
BasicMan
Эксперт
29316 / 5623 / 2384
Регистрация: 17.02.2009
Сообщений: 30,364
Блог
31.10.2026, 02:58

Большая таблица, надо ли её разбивать?
Делаю БД для учета заказов Издательства и дальнейшему распределению их на производстве. ...

Как вести в базе данных логи. Кто вошел? Когда вошел? Когда вышел?
Привет, народ! Пожалуйста подскажите как вести таблицу в БД с логами о пользователях вошедших в...

для каждой записи с таблицы 1,поле ФИО, выделит отдельно фамилию,имя,отчестово и запишет их в соответсвтующие поля таблицы 2.
Написать программу,которая: для каждой записи с таблицы 1,поле ФИО, выделит отдельно...

Какой запрос лучше и почему лучше?
Не знаю какой запрос лучше. Вопросы: 1. Рационально ли использовать 1 вариант? Если второй...

Создать маску ввода для поля таблицы
Здравствуйте, уважаемые форумчане! Вопрос в том, как через SQL добавить полю таблицы типа VARCHAR2...


Искать еще темы с ответами

Или воспользуйтесь поиском по форуму:
66
Ответ Создать тему
Новые блоги и статьи
Просмотровщик HLP и конвертер в CHM
Argus19 16.11.2026
Просмотровщик HLP и конвертер в CHM Давно хотел переделать справку Microsoft® Win32® Programmer's Reference, чтобы иметь её под рукой. Но использовать несколько программ не удобно. Искал другие. . .
BitTorrent (версия 7.11 English) и Меню Настройки
russiannick 15.11.2026
Пункты, отвечающие за отключение обновлений в BitTorrent версии 7. 11 (английской) Options Preferences General Language
Транслитерация текста с кириллицы на латиницу (вариант №4)
Maks 15.11.2026
Что-то я перемудрил с предыдущими версиями, в этот раз сделал всё проще, обратившись к функции "СтрокаЛатиницей" из типового общего модуля "СтроковыеФункцииКлиент". В данной функции нет апострофов,. . .
BitTorrent (версия 4.3.9) и Меню Настройки
russiannick 15.11.2026
В меню Настройки можно отключить автоматическое обновление программы. Карта этого меню приложена ниже. Нужный пункт выделен красным. Сервис Настройки Поведение
Транслитерация текста с кириллицы на латиницу (вариант №3)
Maks 15.11.2026
Обновленная версия предыдущей обработки. Изменения коснулись букв: Ъ, Ы, Ь, Э, отображения которых зависят от положения реквизита "Транслитерация имён". Изменено отображение буквы Й. Добавлена. . .
Транслитерация текста с кириллицы на латиницу (вариант №2)
Maks 13.11.2026
Обновленная версия ранее созданной обработки. Добавлено: - Проверка исходного текста на кириллицу, через обращение к стандартной функции "ТолькоКириллицаВСтроке" из общего модуля. . .
Часы с термометром и праздничным календарем
yariko 12.11.2026
Сегодня мы рассмотрим как можно сделать отличные многофункциональные часы. Итак, поставим конкретную задачу: разработать часы с календарем, двумя термодатчиками, выводом поздравительных сообщений. . .
Транслитерация текста с кириллицы на латиницу (вариант №1)
Maks 11.11.2026
Решил облегчить жизнь себе и коллегам админам при создании электронных почтовых ящиков для пользователей, в результате написал обработку "Транслитерация текста" с кириллицы на латиницу, а в ней. . .
КиберФорум - форум программистов, компьютерный форум, программирование
Powered by vBulletin
Copyright ©2000 - 2026, CyberForum.ru