Oracle чем varchar отличается от varchar2
Так случилось, что продукт, который мы разрабатываем работает с несколькими реляционными базами данных. Сейчас это MS SQL, Postgres и Oracle. Были запуски под много чем от MySQL до покойного, наверное, Firebird и экзотических Sybase с DB2, но сказ не об этом.
Если с MS SQL и Postgres все более мене понятное-привычное, то с Oracle каждый раз нас ждут какие-то сюрпризы. Проницательный читатель сразу заметит, что "руки у нас кривые" и мы "попросту не умеем его готовить", но если, уважаемому читателю захочется узнать чем varchar (а точнее varchar2 ) в Богоподобном Oracle отличается от его собратьев, то прошу под кат.
Как все современные системы, мы храним данные в Unicode формате (в данный момент это UTF-8). Почему это может быть важно для реляционных баз данных?
Ну, например, если у вас в базе данных mix unicode и non-unicode типов данных, то некоторые драйвера в такое не могут. Например, JTDS - JDBC драйвер для MS SQL сервера может работать либо в Unicode режиме, либо в Ansi. Соответственно, если Вы решите "сэкономить" и создать не unicode колонку (varchar/char), то получите преобразование unicode->ansi на уровне вставки данных в таблицу и, скорее всего, достигните обратного эффекта (как минимум замедления на вставке данных, а то и на поиске).
Итак, история. Наш сервер приложений проверяет максимальную допустимую длину полей до их вставки (здесь нужно оговориться, что проверка выполняется не по данным БД, а по нашим внутренним метаданным), но несмотря на это иногда под Oracle мы "ловим" ошибку вида ORA-12899: value too large for column.
Что за напасть? Причем, скрипты генерируются примерно одним и тем же способом под все базы данных, но проблема возникает только иногда и только под Oracle.
Не буду томить. Оказалось, что мы невнимательно прочитали спецификацию типа varchar2 в котором хранятся данные :)
Давайте изменим размер колонки, например, на следующий
Как Вы думаете 150 - это длина в символах (как в других базах в общем-то)? Подсказка - нет :) Скорее всего в байтах.
А в символах это
Т.е. не указывая спецификацию char - byte мы оказываемся в серой зоне настроек базы данных по умолчанию. Причем во всех базах до которых мы смогли дотянуться (включая продакшн и не только наши) настройка по умолчанию - это байты.
А теперь давайте вспомним, что в UTF-8, например, один символ может занимать от одного до 4 байт (обычно 1 байт ANSI, 2 русские символы и некоторые которым больше повезло и до 4 для иероглифов).
И что это за дикая настройка по умолчанию для Unicode баз!? Но ведь, именно она, зараза такая, включена "из коробки". Ну т.е. да, я все понимаю: legacy, обратная совместимость для тех времен, когда Unicode'а еще и "в проекте не было", гордость за то, что backup 86 года можно восстановить последней редакцией imp - вот это вот все.
А почему ошибка возникала только иногда и только для некоторых колонок? Так как тот tool, которым мы генерируем базу изначально был настолько умным, что сразу в create table для всех колонок явно прописывал суффикс char :)
Выводы:
Неплохо бы иногда проверять, не прокрался ли враг или, если Вы достаточно смелый, изменить эту настройку по умолчанию.
VARCHAR зарезервирован Oracle для поддержки различия между NULL и пустой строкой в будущем, как предписывает стандарт ANSI .
VARCHAR2 не делает различий между NULL и пустой строкой и никогда не будет.
Если вы полагаетесь на то, что пустая строка и NULL одно и то же, вам следует использовать VARCHAR2 .
В настоящее время VARCHAR ведет себя точно так же, как VARCHAR2. Однако тип VARCHAR не следует использовать, поскольку он зарезервирован для использования в будущем.
Взято из последней стабильной производственной версии Oracle 12.2: Типы данных
Основное отличие состоит в том, что VARCHAR2 - это внутренний тип данных , а VARCHAR - это внешний тип данных . Итак, нам нужно понять разницу между внутренним и внешним типом данных .
Внутри базы данных значения хранятся в столбцах таблиц. Внутренне Oracle представляет данные в определенных форматах, известных как внутренние типы данных .
Как правило, приложения OCI (Oracle Call Interface) работают не с представлениями данных внутреннего типа, а с типами данных основного языка, которые предопределены языком, на котором они написаны. Когда данные передаются между клиентским приложением OCI и таблицей базы данных, библиотеки OCI преобразуют данные между внутренними типами данных и внешними типами данных.
Внешние типы обеспечивают удобство для программиста, позволяя работать с типами основного языка вместо проприетарных форматов данных. OCI может выполнять широкий спектр преобразований типов данных при передаче данных между базой данных Oracle и приложением OCI. Внешних типов данных OCI больше, чем внутренних типов данных Oracle.
Тип данных VARCHAR2 представляет собой строку символов переменной длины с максимальной длиной 4000 байтов. Если параметр init.ora max_string_size установлен по умолчанию, максимальная длина VARCHAR2 может составлять 4000 байтов. Если параметр init.ora max_string_size = extended, максимальная длина VARCHAR2 может составлять 32767 байт.
Тип данных VARCHAR хранит символьные строки различной длины. Первые 2 байта содержат длину символьной строки, а остальные байты содержат строку. Указанная длина строки в вызове связывания или определения должна включать два байта длины, поэтому самая большая строка VARCHAR , которая может быть получена или отправлена, имеет длину 65533 байта, а не 65535.
Быстрый тест в базе данных 12.2 показывает, что Oracle как внутренний тип данных по-прежнему рассматривает VARCHAR как псевдотип для VARCHAR2 . Это НЕ SYNONYM , который является фактическим типом объекта в Oracle.
Есть также некоторые последствия использования VARCHAR для параметров прекомпилятора ProC / C ++. Для заинтересованных программистов ссылка находится по адресу: Pro * C / Руководство программиста на C ++
После некоторых экспериментов (см. Ниже) я могу подтвердить, что по состоянию на сентябрь 2017 года ничего не изменилось в отношении функций, описанных в принятом ответе: -
-
Пустые строки вставляются как NULL для обоих VARCHAR и VARCHAR2 . То же Результаты.
Историческая причина появления этих двух ключевых слов хорошо объясняется в ответе на другой вопрос.
VARCHAR может хранить до 2000 байтов символов, а VARCHAR2 может хранить до 4000 байтов символов.
Если мы объявим тип данных как VARCHAR, тогда он будет занимать место для значений NULL. В случае типа данных VARCHAR2 он не будет занимать места для значений NULL. например.,
Зарезервирует 6 байтов памяти, даже если имя - Ravi__, тогда как
Зарезервирует место в соответствии с длиной входной строки. например, 4 байта памяти для Ravi__.
Здесь _ представляет NULL.
ПРИМЕЧАНИЕ: varchar резервирует место для нулевых значений, а varchar2 не резервирует пространство для нулевых значений.
В настоящее время они такие же. но раньше
VARCHAR зарезервирован Oracle для поддержки различия между NULL и пустой строкой в будущем, как предписывает стандарт ANSI.
VARCHAR2 не делает различий между NULL и пустой строкой и никогда не будет.
Emp_name varchar(10) - если вы вводите значение меньше 10 цифр, то оставшееся место нельзя удалить. в нем использовалось всего 10 мест.
Emp_name varchar2(10) - если вы вводите значение меньше 10 цифр, оставшееся место автоматически удаляется
VARCHAR зарезервирован Oracle для поддержки различия между NULL и пустая строка в будущем, как ANSI стандарт предписывает.
VARCHAR2 не различает NULL и пустая строка, и никогда не будет.
если вы полагаетесь на пустую строку и NULL будучи тем же самым, вы должны использовать VARCHAR2 .
В настоящее время VARCHAR ведет себя точно так же, как VARCHAR2. Однако этот тип не следует использовать, поскольку он зарезервирован для будущего использования.
взято из последней стабильной версии Oracle 12.2: Типы Данных
главное отличие в том, что VARCHAR2 это внутренний тип данных и VARCHAR это внешний тип данных. Поэтому нам нужно понять разницу между внутренним и внешним типом данных.
внутри базы данных, значения хранятся в Столбцах в таблицах. Внутри Oracle представляет данные в определенных форматах, известных как внутренние типы данных.
В общем случае приложения OCI (Oracle Call Interface) работают не с внутренними представлениями типа данных, а с типами данных языка хоста, которые предопределены языком, на котором они написаны. При передаче данных между клиентским приложением OCI и таблицей базы данных библиотеки OCI преобразуют данные между внутренними и внешними типами данных.
внешние типы обеспечивают удобство для программист путем делать его возможным работать с типами языка хозяина вместо собственнических форматов данных. OCI может выполнять широкий спектр преобразований типов данных при передаче данных между базой данных Oracle и приложением OCI. Внешних типов данных OCI больше, чем внутренних типов данных Oracle.
на VARCHAR2 тип данных представляет собой строку символов переменной длины с максимальной длиной 4000 байт. Если init.параметр Ora max_string_size по умолчанию, максимальный длина VARCHAR2 может быть 4000 байт. Если init.параметр Ora max_string_size = extended, максимальная длина a VARCHAR2 может быть 32767 байт
быстрый тест в базе данных 12.2 предполагает, что в качестве внутренний тип данных, Oracle по-прежнему лечит VARCHAR как псевдотип на VARCHAR2 . Это не SYNONYM который является фактическим типом объекта в Oracle.
есть также некоторые последствия VARCHAR для параметров Precompiler ProC / C++. Для интересующихся программистов ссылка находится по адресу:Pro*C/C++ руководство программиста
после некоторых экспериментов (см. Ниже) я могу подтвердить, что по состоянию на сентябрь 2017 года ничего не изменилось в отношении функциональности, описанной в принято отвечать:-
- rextester демо для Oracle 11g: Пустые строки вставляются как NULL s для обоих VARCHAR и VARCHAR2 .
- LiveSQL демо для Oracle 12c: те же результаты.
в историческая причина этих двух ключевых слов хорошо объясняется в ответ на другой вопрос.
в настоящее время они одинаковы. но раньше
VARCHAR зарезервирован Oracle для поддержки различия между NULL и пустая строка в будущем, как предписывает стандарт ANSI.
VARCHAR2 не различает NULL и пустая строка, и никогда не будет.
Emp_name varchar(10) - если вы вводите значение менее 10 цифр, то остальные пространство нельзя удалить. он использовал всего 10 мест.
Emp_name varchar2(10) - если вы вводите значение менее 10 цифр, то оставшееся пространство автоматически удаляется
VARCHAR2
используется для хранения строк символов переменной длины. Длина строкового значения будет храниться на диске вместе с самим значением.
тип varchar
ведет себя точно так же, как VARCHAR2. Однако этот тип не следует использовать, поскольку он зарезервирован для будущего использования
VARCHAR может хранить до 2000 байт символов, в то время как VARCHAR2 может хранить до 4000 байт символов.
если мы объявим тип данных как VARCHAR, он будет занимать место для значений NULL. В случае типа данных VARCHAR2 он не будет занимать места для значений NULL. например,
зарезервирует 6 байт памяти, даже если имя " Ravi__", тогда как
оставляем пробел в соответствии с длиной входной строки. например, 4 байта памяти для ' Ravi__'.
здесь _ представляет NULL.
Примечание: varchar зарезервирует место для нулевых значений, а varchar2 не зарезервирует место для нулевых значений.
Переменные символьных типов предназначены для хранения текста, а для работы с ними используются символьные функции. Работа с символьными данными значительно различается по сложности от простой до весьма нетривиальной. В этой статье базовые средства PL/SQL по работе со строками рассматриваются в контексте однобайтовых наборов символов — например, тех, которые традиционно используются в Западной Европе и США. Если вы работаете в Юникоде или в другой многобайтовой кодировке или ваше приложение должно поддерживать несколько языков.
Хотя типы CLOB (Character Large OBject) и LONG теоретически тоже можно отнести к символьным типам, по принципам использования они отличаются от символьных типов, рассматриваемых в этой статье.
Строковые типы данных
Oracle поддерживает четыре строковых типа данных (табл. 1). Выбор типа зависит от двух факторов:
Типы данных фиксированной длины — CHAR и NCHAR — в приложениях Oracle используются очень редко. Их вообще не рекомендуется применять, если нет особых причин работать именно со строкой фиксированной длины. Далее, в разделе «Смешение значений CHAR и VARCHAR2 » рассказывается о проблемах, которые могут возникнуть при совместном использовании строковых переменных фиксированной и переменной длины.
Тип данных VARCHAR2
В переменных типа VARCHAR2 хранятся символьные строки переменной длины. При объявлении такой строки для нее определяется максимальная длина в диапазоне от 1 до 32 767 байт. Максимальная длина может задаваться в байтах или символах, но в любом случае компилятор определяет ее в байтах. Общий синтаксис объявления VARCHAR2 :
Здесь имя_переменной — имя объявляемой переменной, макс_длина — ее максимальная длина, а CHAR и BYTE — аргументы, указывающие, что максимальная длина выражается в символах или в байтах соответственно.
Если максимальная длина строковой переменной VARCHAR2 задается в символах (спецификатор CHAR ), то ее реальная длина в байтах вычисляется на основе максимального количества байтов, используемых для представления одного символа. Например, набор символов Юникода UTF-8 использует для представления некоторых символов до 4 байтов; следовательно, если вы работаете с UTF-8, объявление переменной типа VARCHAR2 , максимальная длина которой составляет 100 символов, эквивалентно объявлению этой же переменной с максимальной длиной 300 байт.
Спецификатор длины CHAR используется в основном при работе с многобайтовыми наборами символов — такими, как UTF-8.
Если в объявлении переменной VARCHAR2 опустить спецификатор CHAR или BYTE , тогда заданное значение длины будет интерпретировано в байтах или символах в зависимости от параметра инициализации NLS_LENGTH_SEMANTICS . Текущее значение можно узнать, обратившись с запросом к NLS_SESSION_PARAMETERS . Несколько примеров объявления строк типа VARCHAR2 :
Итак, максимальная длина переменной типа VARCHAR2 в PL/SQL составляет 32 767 байт. Это ограничение действует независимо от того, определяется ли длина строки в байтах или символах. До выхода версии 12c максимальная длина типа данных VARCHAR2 в SQL была равна 4000; в 12c она была увеличена до максимума PL/SQL: 32 767 байт. Однако следует учитывать, что SQL поддерживает этот максимум только в том случае, если параметру инициализации MAX_SQL_STRING_SIZE задано значение EXTENDED ; по умолчанию используется значение STANDARD .
Если вам понадобится работать со строками длиной более 4000 байт, рассмотрите возможность их хранения в столбцах типа CLOB .
Тип данных CHAR
Тип данных CHAR определяет строку фиксированной длины. При объявлении такой строки необходимо задать ее максимальную длину в диапазоне от 1 до 32 767 байт. Длина может задаваться как в байтах, так и в символах. Например, следующие два объявления создают строки длиной 100 байт и 100 символов соответственно:
Реальный размер 100-символьной строки в байтах зависит от текущего набора символов базы данных. Если используется набор символов с переменной длиной кодировки, PL/SQL выделяет для строки столько места, сколько необходимо для представления заданного количества символов с максимальным количеством байтов. Например, в наборе UTF-8, где символы имеют длину от 1 до 4 байт, PL/SQL при создании строки для хранения 100 символов зарезервирует 300 байт (3 байта ? 100 символов).
Мы уже знаем, что при отсутствии спецификатора CHAR или BYTE результат будет зависеть от параметра NLS_LENGTH_SEMANTICS . При компиляции программы эта настройка сохраняется вместе с ней и может использоваться повторно или заменяться при последующей перекомпиляции. С настройкой по умолчанию для следующего объявления будет создана строка длиной 100 байт:
Если длина строки не указана, PL/SQL объявит строку длиной 1 байт. Предположим, переменная объявляется так:
Как только этой переменной присваивается строка длиной более одного символа, PL/SQL инициирует универсальное исключение VALUE_ERROR . Но при этом не указывается, где именно возникла проблема. Если эта ошибка была получена при объявлении новых переменных или констант, проверьте свои объявления на небрежное использование CHAR . Чтобы избежать проблем и облегчить работу программистов, которые придут вам на смену, всегда указывайте длину строки типа CHAR . Несколько примеров:
Поскольку строка типа CHAR имеет фиксированную длину, PL/SQL при необходимости дополняет справа присвоенное значение пробелами, чтобы фактическая длина соответствовала максимальной, указанной в объявлении.
До выхода версии 12c максимальная длина типа данных CHAR в SQL была равна 2000; в 12c она была увеличена до максимума PL/SQL: 32 767 байт. Однако следует учитывать, что SQL поддерживает этот максимум только в том случае, если параметру инициализации MAX_SQL_STRING_SIZE задано значение EXTENDED .
Строковые подтипы
PL/SQL поддерживает некоторые строковые подтипы (табл. 2), которые тоже могут использоваться для объявления символьных строк. Многие из этих подтипов определены только для обеспечения совместимости со стандартом ANSI SQL. Вряд ли они вам когда-нибудь понадобятся, но знать о них все же нужно.
Каждый из перечисленных в таблице подтипов эквивалентен одному из базовых типов данных PL/SQL, указанных в правом столбце. Например:
Подтип VARCHAR заслуживает особого внимания. Уже на протяжении нескольких лет корпорация Oracle собирается изменить определение подтипа данных VARCHAR (в результате чего он перестанет быть эквивалентным VARCHAR2 ) и предупреждает, что пользоваться им не следует. Я согласен с этой рекомендацией: если существует опасность, что Oracle (или комитет ANSI) изменит поведение VARCHAR , неразумно полагаться на его поведение. Используйте вместо него VARCHAR2 .
Читайте также: