CHAR или VARCHAR? А может быть BLOB?

Создан в 1997 году, откорректирован – 24.06.2002, 24.01.2003.

 

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

Давайте сначала повторим описание этих типов данных из документации (Data Definition Guide):

• CHAR(n) – n символов, от 1 до 32767, строковый тип фиксированной длины. Если содержимое поля меньше указанного размера, то оно "выравнивается" (добивается) дополнительными пробелами.
• VARCHAR(n) – n символов, от 1 до 32767, строковый тип переменной длины. Пробелы в конце содержимого поля игнорируются.

Максимальная длина строковых типов зависит от используемого набора символов. Наборы символов перечислены в Data Defintion Guide (Appendix A) и в Language Reference (Appendix D). Для каждого набора указано, сколько байт занимает один символ. Если один символ набора занимает больше одного байта, то максимальна длина строкового поля будет 32767/кол-во_байт_на_символ (т. е. для UNICODE_FSS – 10922 символа).

На диске запись всегда упаковывается. То есть, концевые пробелы не имеют никакого значения с точки зрения дискового пространства.

Количество концевых пробелов учитывается только для varchar. Значение char "добивается" пробелами до объявленной длины только тогда, когда с ним производятся операции присвоения или передача данных на сторону клиента.

Поэтому с точки зрения эффективности хранения различия между char и varchar практически нет. И для работы нужно выбирать то, что удобнее. Как правило это varchar.

Клиентские компоненты могут (или не могут) осуществлять обрезание концевых пробелов для столбцов CHAR. В зависимости от склонностей разработчика такого набора обрезание пробелов может быть по умолчанию, а может и потребовать установки в True какого-либо свойства или на уровне DataSet, или на уровне конкретного поля (TStringField). Поэтому, если вас замучили концевые пробелы в строках, посмотрите на свойства компонент.

Нужно отметить, что ни BDE ни dbExpress не могут выполнять обрезание концевых пробелов у строк.
 

Поля типа BLOB

Поля этого типа позволяют хранить безразмерную произвольную двоичную информацию (поэтому поля типа BLOB не имеют свойства "набор символов"). Запись на диск производится сегментами. Дисковый сегмент блоба это вовсе не то, что имеется в виду при объявлении столбца BLOB (SEGMENT SIZE xx). Сервер сам разбирается, как хранить конкретное значение blob на диске. Указание размера сегмента при объявлении столбца BLOB не даст никакого выигрыша или проигрыша в производительности. Оно нужно только для приложений, написанных на C (Embedded SQL) при помощи GPRE. Например, в IBX размер буфера для чтения-записи blob определен жестко в 16К, и именно такими "сегментами" оперирует IBX. Поэтому определять размер сегмента при объявлении blob не имеет смысла.



Существуют предопределенные подтипы (SUB_TYPE) BLOB: 0 – двоичные данные, 1 – текстовые данные. На самом деле разницы между ними нет, и подтип имеет значение только для вашего приложения (или при написании фильтров BLOB). Пользовательские подтипы можно определить, указав SUB_TYPE с отрицательным знаком – -1, -2, -10, -200 и т. д., и опять же это имеет значение только для приложения, работающего с данными или для фильтра.

Сегменты BLOB всегда записываются на свободное пространство, и занимают только действительный объем данных BLOB.
Если размер BLOB превышает размер страницы, то создается массив указателей на страницы BLOB. При очень больших размерах BLOB могут появиться указатели на страницы указателей BLOB.

При изменении записи, если содержимое blob не менялось, его blobID остается тем же самым. Собственно, в новой версии записи пишутся только те поля, которые были изменены. Следовательно, при модификации записи, если не затронуто поле BLOB, данные blob не "дублируются". Если же блоб меняется, то как и версия записи, он находится на диске в двух экземплярах – старом и новом. Учитывайте это для блобов, хранящих большой объем данных.
 

CHAR или BLOB?

Итак, мы рассмотрели все аспекты хранения данных CHAR, VARCHAR и BLOB, и теперь можем перечислить рекомендации по выбору типа:

• Если длина поля < 255 символов, то
  • лучше использовать VARCHAR – по хранению varchar на 2 байта больше char, зато в приложениях не надо писать отрезание концевых пробелов у строк.
  • в старых версиях IB при использовании VARCHAR могут возникнуть проблемы с производительностью при использовании протокола TCP/IP.
  • не имеет смысла использовать BLOB – выборка BLOB осуществляется по его идентификатору, поэтому происходит чуть дольше и требует немного больше затрат на программирование.
• Если длина поля > 255, но < ~10000 символов
  • Можно использовать как CHAR или VARCHAR, так и BLOB. Индексирование по полями такой длины невозможно, к тому же есть шанс что однажды записываемые данные превысят 10000 символов, и может быть BLOB подойдет больше. Ориентируйтесь только на удобство работы с такими данными в приложении.
• Если длина поля > ~5000 символов, или информация может быть произвольной
  • лучше использовать BLOB. Подтип может быть любой, информацию в таком поле можно хранить произвольную и не беспокоиться о размере данных. Стоимость доступа к данным такого размера полностью компенсирует разницу в способах хранения и извлечения полей типа CHAR и BLOB.
• Дополнительным фактором выбора может быть размер страницы. При размере страницы 8К можно для хранения строк выбирать CHAR или VARCHAR, если их длина также не превысит 8К (записи могут пересекать страницы, поэтому даже при размере страницы 1К можно объявлять строки длиной 32К). Неплохо в таких случаях создать тестовую таблицу, и попробовать скорость или удобство считывания разных вариантов типов полей, наполнив char, varchar и blob одними и теми же данными.

 

Конвертация данных

В Firebird и Yaffil, в 3-м диалекте появилась возможность при insert (update?) содержимое блоба задавать обычной строкой. В остальных серверах при подобных действиях будет выдано стандартное сообщение о невозможности конвертации данных.

Вместе с тем уже давно существуют UDF перевода блоба в строку и обратно (FreeUDFLib и другие).
 

Возможные проблемы

Индексирование

• Строковые независимо от типа поля имеют ограничение на длину индекса – 84 байта при указании COLLATE и 252 байта – без COLLATE.
• BLOB-поля не могут быть проиндексированы.

Поиск

• Для поиска по полям типа CHAR, VARCHAR и BLOB можно использовать операторы STARTING WITH (начинается с), LIKE (начинается, содержит, или заканчивается на) и CONTAINING (содержит). В BLOB этими операторами можно искать произвольную информацию (необязательно текстовую), однако необходимо учитывать что поиск в BLOB может осуществляться только перебором записей.
• Если поиск производится по окончанию, например, LIKE '%ов', то такой запрос по полю CHAR выдаст 0 записей, если длина значения поля хотя бы на один символ меньше объявленной длины поля. Это происходит потому, что CHAR при сравнении добивается до длины поля пробелами, и получается, что 'Иванов ' не подходит под условие поиска '%ов'. Для решения этой проблемы нужно пользоваться VARCHAR
• Поиск или упорядочивание (ORDER BY) с использованием функции UPPER возможен только для полей типа CHAR или VARCHAR, т. к. только они имеют свойство CHARACTER SET (BLOB содержит только произвольную двоичную информацию, т. е. необязательно текстовую). Кроме того, для UPPER поля CHAR и VARCHAR должны иметь соответствующий COLLATE либо в объявлении типа поля, либо в выражении поиска или сортировки.

Выборка данных

• При конкатенации строковых полей в запросе нужно учитывать, что CHAR-поля будут "расширены" до указанной длины пробелами, а VARCHAR – нет. Например, если в запросе производится "сборка" фамилии, имени и отчества

• Для многоязыковых баз данных BLOB не могут быть перекодированы из одной кодировки в другую. Например, если сервер поддерживает кодировки WIN1251 и KOI8R, и база создана в WIN1251, возможно подключиться (через компоненты прямого доступа) указывая lc_ctype=KIO8R в параметрах коннекта. При этом информация будет перекодироваться из win1251 в koi8r и наоборот для всех строковых типов данных, кроме BLOB. Для конвертации данных blob хотя бы при выборке придется написать собственную UDF.

Вставка и модификация данных

• Поля BLOB невозможно передавать как параметр запроса или хранимой процедуры в BDE 2.5x и 3.x (такая возможность появилась только в BDE 4.0 и у компонент Delphi 3.0). Это приводит к необходимости использования TQuery и передачи данных в BLOB-поля через TBlobStream. Сам сервер не имеет проблем с получением или передачей blob в виде параметров запроса или параметров процедур.

Создание переносимой базы данных

• Стандарт ANSI SQL в частности определяет типы полей, но безусловно реализация этих типов, способ хранения и обработки определяет изготовитель конкретного SQL-сервера. Для обеспечения хоть какой-то возможной переносимости следует пользоваться совместимыми типами, игнорируя преимущества использовани типов данных (например CHAR в InterBase). Вам необходимо обратиться к документации или справочным файлам BDE (BDE32.HLP), для того чтобы определить совместимость различных типов между выбранными вами SQL-серверами.