Типы данных CHAR и VARCHAR очень схожи между собой, но различаются по способам их хранения и извлечения.
В столбце типа CHAR длина поля постоянна и задается при создании таблицы. Эта длина может принимать любое значение между 1 и 255 (что же касается версии MySQL 3.23, то в ней длина столбца CHAR может быть от 0 до 255 ). Величины типа CHAR при хранении дополняются справа пробелами до заданной длины. Эти концевые пробелы удаляются при извлечении хранимых величин.
Если задаваемая в столбце CHAR или VARCHAR величина превосходит максимально допустимую длину столбца, то эта величина соответствующим образом усекается.
Различие между этими двумя типами столбцов в представлении результата хранения величин с разной длиной строки в столбцах CHAR(4) и VARCHAR(4) проиллюстрировано следующей таблицей:
Величина
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. Атрибут BINARY не влияет на процессы хранения или извлечения данных из столбца.
MySQL может без предупреждения изменить тип столбца CHAR или VARCHAR во время создания таблицы. See Раздел 6.5.3.1, «Молчаливые изменения определений столбцов».
CHAR и VARCHAR типы подобны, но отличаются по способу, которым они сохранены и получаются. Они также отличаются по максимальной длине и по тому, сохраняются ли конечные пробелы.
CHAR и VARCHAR типы объявляются с длиной, которая указывает на максимальное количество символов, которые Вы хотите сохранить. Например, CHAR(30) может содержать до 30 символов.
Длина a CHAR столбец фиксируется к длине, которую Вы объявляете, когда Вы составляете таблицу. Длина может быть любым значением от 0 до 255. Когда CHAR значения сохранены, они дополняются правом пробелами к указанной длине. Когда CHAR значения получаются, конечные пробелы не удаляются если PAD_CHAR_TO_FULL_LENGTH Режим SQL включается.
Значения в VARCHAR столбцы являются строками переменной длины. Длина может быть определена как значение от 0 до 65 535. Эффективная максимальная длина a VARCHAR подвергается максимальному размеру строки (65 535 байтов, который совместно используется среди всех столбцов), и используемый набор символов. См. Раздел D.10.4, «Пределы на Столбце таблицы граф и Размер Строки».
Если строгий режим SQL не включается, и Вы присваиваете значение a CHAR или VARCHAR столбец, который превышает максимальную длину столбца, значение, является усеченным, чтобы соответствовать, и предупреждение сгенерировано. Для усечения непробелов можно вызвать ошибку произойти (а не предупреждение) и подавить вставку значения при использовании строгого режима SQL. См. Раздел 5.1.7, «Режимы SQL Сервера».
Для VARCHAR столбцы, конечные пробелы сверх длины столбца являются усеченными до вставки, и предупреждение сгенерировано, независимо от режима SQL в использовании. Для CHAR столбцы, усечение избыточных конечных пробелов от вставленных значений выполняется тихо независимо от режима SQL.
VARCHAR значения не дополняются, когда они сохранены. Конечные пробелы сохраняются, когда значения сохранены и получаются в соответствии со стандартным SQL.
Следующая таблица иллюстрирует различия между CHAR и VARCHAR показывая результат хранения различной строки оценивает в CHAR(4) и VARCHAR(4) столбцы (предполагающий, что столбец использует однобайтовый набор символов такой как latin1 ).
Значение
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 столбцы после извлечения. Следующий пример иллюстрирует это различие:
Значения в CHAR и VARCHAR столбцы сортируются и сравниваются согласно сопоставлению набора символов, присвоенному столбцу.
Для получения дополнительной информации о наборах символов MySQL и сопоставлениях, см. Раздел 10.1, «Поддержка Набора символов».
Все значения в столбце должны быть одного типа данных. (Единственным исключением из этого правила являются значения типа данных SQL_VARIANT.) Используемые в Transact-SQL типы данных можно разбить на следующие категории:
временные типы (даты и/или времени);
прочие типы данных.
Все эти категории данных рассматриваются далее в последующих разделах.
Числовые типы данных
Как и следовало ожидать по их названию, числовые типы данных применяются для представления чисел. Эти типы и их краткое описание приводятся в таблице ниже:
Числовые типы данных T-SQL
Тип данных
Описание
INTEGER
Представляет целочисленные значения длиной в 1 байт в диапазоне от 0 до 255
Используется для представления денежных значений. Значения типа MONEY соответствуют 8-байтовым значениям типа DECIMAL, округленным до четырех разрядов после десятичной точки
Представляет такие же значения, что и тип MONEY, но длиной в 4 байта
Символьные типы данных
Существует два общих вида символьных типов данных. Строки могут представляться однобайтовыми символами или же символами в кодировке Unicode. (В кодировке Unicode для представления одного символа применяется несколько байтов.) Кроме этого, строки могут быть разной длины. В таблице ниже перечислены категории символьных типов данных с их кратким описанием.
Символьные типы данных T-SQL
Тип данных
Описание
CHAR[(n)]
Используется для представления строки однобайтовых символов переменной длины (0 NCHAR[(n)]
Используется для хранения строк переменной длины, состоящих из символов в кодировке Unicode. Для хранения каждого символа строки типа NVARCHAR требуется 2 байта, поэтому строка типа данных NVARCHAR может содержать самое большее 4000 символов.
Тип данных VARCHAR идентичен типу данных CHAR, за исключением одного различия: если содержимое строки CHAR(n) короче, чем n символов, остаток строки заполняется пробелами. А количество байтов, занимаемых строкой типа VARCHAR, всегда равно количеству символов в ней.
Типы данных времени
В языке Transact-SQL поддерживаются следующие временные типы данных:
Если нужно сохранить только составляющую даты или времени, использование значений типа DATETIME или SMALLDATETIME несколько неудобно. По этой причине в SQL Server были введены типы данных DATE и TIME, в которых хранятся только составляющие даты и времени значений типа DATETIME, соответственно. Значения типа DATE занимают 3 байта, представляя диапазон дат от 01/01/0001 до 31/12/9999. Значения типа TIME занимают 3-5 байт и представляют время с точностью до 100 нс.
Тип данных DATETIME2 используется для представления значений дат и времени с высокой точностью. В зависимости от требований, значения этого типа можно определять разной длины, и занимают они от 6 до 8 байтов. Составляющая времени представляет время с точностью до 100 нс. Этот тип данных не поддерживает переход на летнее время.
Все рассмотренные на данный момент временные типы данных не поддерживают часовые пояса. Тип данных DATETIMEOFFSET имеет составляющую для хранения смещения часового пояса. По этой причине значения этого типа занимают от 6 до 8 байтов. Все другие свойства этого типа данных аналогичны соответствующим свойствам типа данных DATETIME2.
Двоичные и битовые типы данных
К двоичным типам данным принадлежат два типа: BINARY и VARBINARY. Эти типы данных описывают объекты данных во внутреннем формате системы и используются для хранения битовых строк. По этой причине значения этих типов вводятся, используя шестнадцатеричные числа.
Значения битового типа bit содержат лишь один бит, вследствие чего в одном байте можно сохранить до восьми значений этого типа. Краткое описание свойств двоичных и битовых типов данных приводится в таблице ниже:
Двоичные и битовые типы данных T-SQL
Тип данных
Описание
BINARY[(n)]
Определяет строку битов фиксированной длины, содержащую ровно n байтов(0 VARBINARY[(n)]
Определяет строку битов переменной длины, содержащую до n байтов (0 BIT
Применяется для хранения логических значений, которые могут иметь три возможных состояния: false, true и null
Тип данных больших объектов
используется для хранения объектов данных размером до 2 Гбайт. Такие объекты обычно применяются для хранения больших объемов текстовых данных и для загрузки подключаемых модулей и аудио- и видеофайлов. В языке Transact-SQL поддерживаются следующие типы данных LOB:
Начиная с версии SQL Server 2005, для обращения к значениям стандартных типов данных и к значениям типов данных LOB применяется одна и та же модель программирования. Иными словами, для работы с объектами LOB можно использовать удобные системные функции и строковые операторы.
В компоненте Database Engine параметр MAX применяется с типами данных VARCHAR, NVARCHAR и VARBINARY для определения значений столбцов переменной длины. Когда вместо явного указания длины значения используется значение длины по умолчанию MAX, система анализирует длину конкретной строки и принимает решение, сохранять ли эту строку как обычное значение или как значение LOB. Параметр MAX указывает, что размер значений столбца может достигать максимального размера LOB данной системы.
Хотя решение о способе хранения объектов LOB принимается системой, настройки по умолчанию можно переопределить, используя системную процедуру sp_tableoption с аргументом LARGE_VALUE_TYPES_OUT_OF_ROW. Если значение этого аргумента равно 1, то данные в столбцах, объявленных с использованием параметра MAX, будут сохраняться отдельно от остальных данных. Если же значение аргумента равно 0, то компонент Database Engine сохраняет все значения размером до 8 060 байт в строке таблицы, как обычные данные, а значения большего размера хранятся вне строки в области хранения объектов LOB.
Тип данных UNIQUEIDENTIFIER
Инициализировать столбец или переменную типа UNIQUEIDENTIFIER можно посредством функции NEWID или NEWSEQUENTIALID, а также с помощью строковой константы особого формата, состоящей из шестнадцатеричных цифр и дефисов. Эти функции рассматриваются в следующей статье.
К столбцу со значениями типа данных UNIQUEIDENTIFIER можно обращаться, используя в запросе ключевое слово ROWGUIDCOL, чтобы указать, что столбец содержит значения идентификаторов. (Это ключевое слово не генерирует никаких значений.) Таблица может содержать несколько столбцов типа UNIQUEIDENTIFIER, но только один из них может иметь ключевое слово ROWGUIDCOL.
Тип данных SQL_VARIANT
Тип данных SQL_VARIANT можно использовать для хранения значений разных типов одновременно, таких как числовые значения, строки и даты. (Исключением являются значения типа TIMESTAMP.) Каждое значение столбца типа SQL_VARIANT состоит из двух частей: собственно значения и информации, описывающей это значение. Эта информация содержит все свойства действительного типа данных значения, такие как длина, масштаб и точность.
Для доступа и отображения информации о значениях столбца типа SQL_VARIANT применяется функция SQL_VARIANT_PROPERTY.
Объявлять тип столбца как SQL_VARIANT следует только в том случае, если это действительно необходимо. Например, если столбец предназначается для хранения значений разных типов данных или если при создании таблицы тип данных, которые будут храниться в данном столбце, неизвестен.
Тип данных HIERARCHYID
Тип данных HIERARCHYID используется для хранения полной иерархии. Например, в значении этого типа можно сохранить иерархию всех сотрудников или иерархию папок. Этот тип реализован в виде определяемого пользователем типа CLR, который охватывает несколько системных функций для создания узлов иерархии и работы с ними. Следующие функции, среди прочих, принадлежат к методам этого типа данных: GetLevel(), GetAncestor(), GetDescendant(), Read() и Write().
Тип данных TIMESTAMP
Само по себе значение, сохраняемое в столбце типа TIMESTAMP, не представляет никакой важности. Этот столбец обычно используется для определения, изменилась ли определенная строка таблицы со времени последнего обращения к ней.
Варианты хранения
Начиная с версии SQL Server 2008, существует два разных варианта хранения, каждый из которых позволяет сохранять объекты LOB и экономить дисковое пространство. Это следующие варианты:
хранение данных типа FILESTREAM;
хранение с использованием разреженных столбцов (sparse columns).
Эти варианты хранения рассматриваются в следующих подразделах.
Хранение данных типа FILESTREAM
Как уже упоминалось ранее, SQL Server поддерживает хранение больших объектов (LOB) посредством типа данных VARBINARY(MAX). Свойство этого типа данных таково, что большие двоичные объекты (BLOB) сохраняются в базе данных. Это обстоятельство может вызвать проблемы с производительностью в случае хранения очень больших файлов, таких как аудио- или видеофайлов. В таких случаях эти данные сохраняются вне базы данных во внешних файлах.
Хранение данных типа FILESTREAM поддерживает управление объектами LOB, которые сохраняются в файловой системе NTFS. Основным преимуществом этого типа хранения является то, что хотя данные хранятся вне базы данных, управляются они базой данных. Таким образом, этот тип хранения имеет следующие свойства:
данные типа FILESTREAM можно сохранять с помощью инструкции CREATE TABLE, а для работы с этими данными можно использовать инструкции для модифицирования данных (SELECT, INSERT, UPDATE и DELETE);
система управления базой данных обеспечивает такой же самый уровень безопасности для данных типа FILESTREAM, как и для данных, хранящихся внутри базы данных.
Разреженные столбцы (sparse columns)
Цель варианта хранения, предоставляемого разреженными столбцами, значительно отличается от цели хранения типа FILESTREAM. Тогда как целью хранения типа FILESTREAM является хранение объектов LOB вне базы данных, целью разреженных столбцов является минимизировать дисковое пространство, занимаемое базой данных.
Столбцы этого типа позволяют оптимизировать хранение столбцов, большинство значений которых равны null. При использовании разреженных столбцов для хранения значений null дисковое пространство не требуется, но, с другой стороны, для хранения значений, отличных от null, требуется дополнительно от 2 до 4 байтов, в зависимости от их типа. По этой причине разработчики Microsoft рекомендуют использовать разреженные столбцы только в тех случаях, когда ожидается, по крайней мере, 20% общей экономии дискового пространства.
Разреженные столбцы определяются таким же образом, как и прочие столбцы таблицы; аналогично осуществляется и обращение к ним. Это означает, что для обращения к разреженным столбцам можно использовать инструкции SELECT, INSERT, UPDATE и DELETE таким же образом, как и при обращении к обычным столбцам. Единственная разница касается создания разреженных столбцов: для определения конкретного столбца разреженным применяется аргумент SPARSE после названия столбца, как это показано в данном примере:
Несколько разреженных столбцов таблицы можно сгруппировать в набор столбцов. Такой набор будет альтернативным способом сохранять значения во всех разреженных столбцах таблицы и обращаться к ним.
Значение NULL
Если выражение содержит операцию сравнения и значение одного или обоих операндов этой операции равно null, результат этой операции также будет null.
Значение null должно отличаться от всех других значений. Для числовых типов данных значение 0 и значение null не являются одинаковыми. То же самое относится и к пустой строке и значению null для символьных типов данных.
Значения null можно сохранять в столбце таблицы только в том случае, если это явно разрешено в определении данного столбца. С другой стороны, значения null не разрешаются для столбца, если в его определении явно указано NOT NULL. Если для столбца с типом данных (за исключением типа TIMESTAMP) не указано явно NULL или NOT NULL, то присваиваются следующие значения:
NULL, если значение параметра ANSI_NULL_DFLT_ON инструкции SET равно on.
NOT NULL, если значение параметра ANSI_NULL_DFLT_OFF инструкции SET равно on.
Если инструкцию set не активировать, то столбец по умолчанию будет содержать значение NOT NULL. (Для столбцов типа TIMESTAMP значения null не разрешаются.)
Создан в 1997 году, откорректирован – 24.06.2002, 24.01.2003.
Особенности строковых типов данных
На диске запись всегда упаковывается. То есть, концевые пробелы не имеют никакого значения с точки зрения дискового пространства.
Количество концевых пробелов учитывается только для 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 не имеет смысла.
Сегменты BLOB всегда записываются на свободное пространство, и занимают только действительный объем данных BLOB. Если размер BLOB превышает размер страницы, то создается массив указателей на страницы BLOB. При очень больших размерах BLOB могут появиться указатели на страницы указателей BLOB.
При изменении записи, если содержимое blob не менялось, его blobID остается тем же самым. Собственно, в новой версии записи пишутся только те поля, которые были изменены. Следовательно, при модификации записи, если не затронуто поле BLOB, данные blob не «дублируются». Если же блоб меняется, то как и версия записи, он находится на диске в двух экземплярах – старом и новом. Учитывайте это для блобов, хранящих большой объем данных.
CHAR или BLOB?
Конвертация данных
В Firebird и Yaffil, в 3-м диалекте появилась возможность при insert (update?) содержимое блоба задавать обычной строкой. В остальных серверах при подобных действиях будет выдано стандартное сообщение о невозможности конвертации данных.
Вместе с тем уже давно существуют UDF перевода блоба в строку и обратно (FreeUDFLib и другие).
См. Страницу MySQL на типах CHAR и VARCHAR для подробного объяснения (не забудьте также прочитать комментарии).
VARCHAR
CHAR используется для переменной размера фиксированной длины VARCHAR используется для переменной размера переменной длины.
Вывод: для эффективного использования дискового пространства необходимо использовать VARCHAR вместо CHAR, если переменная длина является переменной
CHAR(x) Колонка может иметь только точно x символы. Столбец может иметь до символов. VARCHAR(x) x
Тем не менее, вы не должны использовать MD5 в первую очередь; у него есть известные слабости. Вместо этого используйте SHA2. Если вы хэшируете пароли, вы должны использовать bcrypt.
К уже приведенным ответам я хотел бы добавить, что в системах OLTP или в системах с частыми обновлениями рекомендуется использовать CHAR даже для столбцов переменного размера из-за возможной VARCHAR фрагментации столбцов во время обновлений.
В большинстве СУБД сегодня они являются синонимами. Однако для тех систем, у которых все еще есть различие, поле CHAR сохраняется как столбец фиксированной ширины. Если вы определите его как CHAR (10), то в таблицу будет записано 10 символов, где «заполнение» (обычно пробелы) используется для заполнения любого пространства, которое не используются данными. Например, сохранение «bob» будет сохранено как («bob» +7 пробелов). Столбец VARCHAR (переменный символ) предназначен для хранения данных, не тратя впустую дополнительное пространство, которое занимает столбец CHAR.
Как всегда, Википедия говорит громче.
Вы можете использовать char, если ожидается, что записи данных в столбце будут одинакового размера. Вы можете использовать varchar, когда ожидается, что размер данных в столбце будет значительно различаться.
VARCHAR хранит символьные строки переменной длины и является наиболее распространенным типом строковых данных. Для него может потребоваться меньше места для хранения, чем для типов фиксированной длины, поскольку он использует столько места, сколько ему нужно (т. Е. Меньше места используется для хранения более коротких значений). Исключением является таблица MyISAM, созданная с ROW_FORMAT = FIXED, которая использует фиксированный объем пространства на диске для каждой строки и, таким образом, может тратить пространство. VARCHAR помогаетпроизводительности, потому что это экономит место.
Char имеет фиксированную длину (поддерживает 2000 символов), это символ для типа данных
Varchar имеет переменную длину (поддерживает 4000 символов)
