Типы данных столбцов MySQL

Previous / Next / Up / Table of Contents

6.2.3.1 Типы данных CHAR и VARCHAR

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

В столбце типа длина поля постоянна и задается при создании таблицы. Эта длина может принимать любое значение между и (что же касается версии MySQL 3.23, то в ней длина столбца может быть от до ).

Величины типа при хранении дополняются справа пробелами до заданной длины. Эти концевые пробелы удаляются при извлечении хранимых величин.

Величины в столбцах представляют собой строки переменной длины. Так же как и для столбцов , можно задать столбец любой длины между и . Однако, в противоположность , при хранении величин типа используется только то количество символов, которое необходимо, плюс один байт для записи длины. Хранимые величины пробелами не дополняются, наоборот, концевые пробелы при хранении удаляются (описанный процесс удаления пробелов отличается от предусмотренного спецификацией ANSI SQL).

Если задаваемая в столбце или величина превосходит максимально допустимую длину столбца, то эта величина соответствующим образом усекается.

Различие между этими двумя типами столбцов в представлении результата хранения величин с разной длиной строки в столбцах и проиллюстрировано следующей таблицей:

Величина Требуемая память Требуемая память
4 байта 1 байт
4 байта 3 байта
4 байта 5 байтов
4 байта 5 байтов

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

Если при создании таблицы не был задан атрибут для столбцов, то величины в столбцах типа и сортируются и сравниваются без учета регистра. При задании атрибута величины в столбце сортируются и сравниваются с учетом регистра в соответствии с порядком таблицы ASCII на том компьютере, где работает сервер MySQL. Атрибут не влияет на процессы хранения или извлечения данных из столбца.

Атрибут является «прилипчивым». Это значит, что, если в каком-либо выражении использовать столбец, помеченный как , то сравнение всего выражения будет выполняться как сравнение величины типа .

MySQL может без предупреждения изменить тип столбца или во время создания таблицы. See section 6.5.3.1 Молчаливые изменения определений столбцов.

Top / Previous / Next / Up / Table of Contents

Создан в 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 не "дублируются". Если же блоб меняется, то как и версия записи, он находится на диске в двух экземплярах – старом и новом. Учитывайте это для блобов, хранящих большой объем данных.
 

Примечание. Индексировать по полям 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 либо в объявлении типа поля, либо в выражении поиска или сортировки.

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

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

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

select last_name||first_name||middle_name from clients

то результат будет приблизительно такой: "Иванов           Иван           Иванович". А если это будут VARCHAR-поля, то такой же запрос выдаст результат в виде "ИвановИванИванович".

Для решения этой проблемы можно для CHAR использовать UDF (типа RTrim), а для 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-серверами.

к оглавлению

Типы данных CHAR и VARCHAR

Типы данных CHAR и VARCHAR очень схожи между собой, но различаются по способам их хранения и извлечения.

В столбце типа CHAR длина поля постоянна и задается при создании таблицы. Эта длина может принимать любое значение между 1 и 255. Величины типа CHAR при хранении дополняются справа пробелами до заданной длины.

Эти концевые пробелы удаляются при извлечении хранимых величин.

Величины в столбцах VARCHAR представляют собой строки переменной длины. Так же как и для столбцов CHAR, можно задать столбец VARCHAR любой длины между 1 и 255. Однако, в противоположность CHAR, при хранении величин типа VARCHAR используется только то количество символов, которое необходимо, плюс один байт для записи длины. Хранимые величины пробелами не дополняются, наоборот, концевые пробелы при хранении удаляются.

Если задаваемая в столбце CHAR или VARCHAR величина превосходит максимально допустимую длину столбца, то эта величина соответствующим образом усекается.

Различие между этими двумя типами столбцов в представлении результата хранения величин с разной длиной строки в столбцах CHAR(4) и VARCHAR(4) проиллюстрировано следующей таблицей 4.6.

Таблица 4.6. Результат хранения величин с разной длиной строки типов Char и Varchar

Величина

CHAR(4)

Требуемая память

VARCHAR(4)

Требуемая память

»

‘ ‘

4 байта

»

1 байт

‘ab’

‘ab ‘

4 байта

‘ab’

3 байта

‘abcd’

‘abcd’

4 байта

‘abcd’

5 байтов

‘abcdefgh’

‘abcd’

4 байта

‘abcd’

5 байтов

Извлеченные из столбцов CHAR(4) и VARCHAR(4) величины в каждом случае будут одними и теми же, поскольку при извлечении концевые пробелы из столбца CHAR удаляются.

Если при создании таблицы не был задан атрибут BINARY для столбцов, то величины в столбцах типа CHAR и VARCHAR сортируются и сравниваются без учета регистра. При задании атрибута BINARY величины в столбце сортируются и сравниваются с учетом регистра в соответствии с порядком таблицы ASCII на том компьютере, где работает сервер MySQL.

к оглавлению

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

Добавить комментарий

Закрыть меню