Длина типа varchar2 oracle

Обновлено: 04.07.2024

В одной из моих таблиц есть столбец типа varchar2(2000) , но значит ли это 2000 bytes ? или 2000 characters ? Потому что я знаю, что есть разница между байтами и символами .

2 ответа

По умолчанию используется один из NLS параметров вашего сеанса, а именно NLS_LENGTH_SEMANTICS . Вот как я могу проверить это из моей сессии:

Вы можете изменить сеанс, чтобы изменить значение (или вы можете сделать это через графический интерфейс в чем-то вроде SQL Developer). Вы также можете поместить команду ALTER SESSION в вашу LOGIN.SQL (или, глобально, GLOGIN.SQL ), если вы ее используете, если вы хотите, чтобы при запуске сеанса назначалось определенное значение. В противном случае, когда вы начинаете новый сеанс, по умолчанию будет использоваться ваш SPFile (скорее всего).

Вот как я могу проверить, что находится в моем SPFile:

Я также могу изменить свою систему, чтобы изменить содержимое SPFile, но это работа администратора баз данных (я думаю). В любом случае, это МОЖЕТ быть изменено.

Это похоже на другие параметры NLS - рассмотрим, например, NLS_DATE_FORMAT , поведение очень похоже.

В дополнение к ответу @ mathguy, если у вас есть существующая таблица, вы можете запросить словарь данных, чтобы выяснить, указан ли размер, который вы видите (например, через describe ), в байтах или символах:

Где char_used - C для ограничения символов и B для ограничения байтов.

data_length всегда в байтах, так как это реальный верхний предел, и ограничен (до 4000 в моей БД - может быть 32k; в 12c + с MAX_STRING_SIZE установленным в EXTENDED ) - так хотя я и сказал, что в col2 разрешено 2000 символов, но если я использую многобайтовые символы, мне может быть разрешено менее 2000 символов, поскольку общее число байтов может превышать 4000. Я бы на самом деле увидел 4000 как длина данных для всего, что объявлено больше, чем 999 char , поскольку для AL32UTF8 допускается до четырех байтов на символ.

Команда describe в SQL * Plus и т. Д. Показывает исходный размер, а также показывает, являются ли это байтами или символами, но при этом пропускается, если соответствует параметру NLS_LENGTH_SEMANTICS сеанса:

Этот параметр также используется для семантики по умолчанию, если вы не укажете char или byte в операторе создания, также как объясняет @mathguy; но, как и в большинстве настроек NLS, обычно лучше (IMO) явно указать это, чтобы не было двусмысленности или путаницы в отношении того, что произойдет.

Переменные символьных типов предназначены для хранения текста, а для работы с ними используются символьные функции. Работа с символьными данными значительно различается по сложности от простой до весьма нетривиальной. В этой статье базовые средства 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 .

Я хочу знать, почему Oracle нуждается в параметре size в определении VARCHAR2 .

Я думаю, что это за ограничение. Было бы лучше, если бы oracle приняла этот параметр как необязательный, например NUMBER тип данных?

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

это то же самое, чтобы определить тип VARCHAR2(10 ) или VARCHAR2(1000) .

Я думаю, это ненужное ограничение. Если нет, знаете ли вы о реальном случае, когда это ограничение привело к чему-то полезному? И почему нет такой декларации в NUMBER тип ?

Это то же самое, чтобы определить тип varchar2(10) или varchar2(1000).

нет, это совсем не одно и то же.

длина столбца-полезные метаданные для разработчиков, строящих экраны.
аналогично автоматические инструменты запросов, такие как TOAD и SQL Developer, используют длину столбца при отображении результатов.
база данных использует длину переменной при выделении памяти для коллекций PL/SQL. Поскольку эта память выходит из PGA, превышение объявления переменной может привести к сбою программ, потому что у сервера закончилась память.
существуют аналогичные проблемы с объявлением отдельных переменных в программах PL/SQL, просто коллекции имеют тенденцию умножать проблему.
Сверхразмерные столбцы создают проблемы для составных индексов. Следующие на базу с 8K блоков

но выше все остальное, размеры столбцов-это форма проверки ошибок. Если столбец должен быть длиной десять символов, а какой-то автономный процесс пытается загрузить тысячу символов, то что-то не так. Процесс должен завершиться неудачей, поэтому мы можем исследовать, почему мы загружаем данные duff. Альтернативой является база данных, полная мусора, и если это то, что мы хотели, мы должны были просто дать всем Excel и сделать с ним.

Это правда, что изменение размера столбца, когда получается мы недооценили может быть утомительно. Но это происходит не очень часто, и мы можем смягчить боль, используя объявления %TYPE и SUBTYPE в нашем PL/SQL вместо длины переменных жесткого кодирования.

"почему нет такого объявления в типе номера"

цифры разные. Для начала максимальный размер числа намного меньше текстового эквивалента (38 цифр гарантированной точности).

но ключевая разница это Oracle хранит числовые значения в научной нотации таким образом, нет прямой зависимости между арифметическим размером числа и пространством хранения, которое оно потребляет.

тем не менее, остается хорошей практикой указывать масштаб и точность везде, где это возможно, особенно когда мы имеем дело с целыми числами, скажем, или деньгами.

Я думаю, что важно помнить исторический контекст, в котором были разработаны реляционные базы данных. В то время, когда они разрабатывались (конец 70 - х-начало 80-х годов), общедоступные компьютеры были намного меньше (с точки зрения памяти и дискового пространства) и менее мощными (с точки зрения процессора), чем у нас сейчас, и управление этими ресурсами обязательно было насущной проблемой. COBOL был общим языком бизнес-вычислений (и до сих пор широко используется), а объектно-ориентированные языки, такие как как Smalltalk и C++ были неизвестны, для всех практических целей. В то время ожидалось, что программы объявят точно, сколько памяти им понадобится для каждого элемента данных, например, 10 байт для строки, 2 байта для короткого целого числа, 4 байта для float и т. д., и поэтому этот стиль объявления использовался тогда только что разработанными реляционными базами данных. Более того,предположение было сделано, чтобы каждый элемент данных объявлял (неявно или явно) сумму это требовало хранения, и это было закодировано в реляционные двигатели на очень фундаментальном уровне.

теперь со временем это требование несколько ослабло, по крайней мере, в том, что касается хранения данных на диске. Я считаю, что в Oracle тип данных NUMBER будет гибко выделять пространство, так что фактически будет использоваться только минимальный объем пространства, необходимый для хранения его значения, и что столбцы VARCHAR2 будут использовать достаточно места на диске для хранения фактических данных без хранения конечных пробелов, хотя вам все равно нужно объявить максимальный объем хранилища, необходимый для VARCHAR2.

вы можете взглянуть на SYS.Стандартный пакет, чтобы получить представление о том, как объявить подтипы VARCHAR2. Например, если вам нужен собственный тип "string", который вы можете использовать без привязки к спецификации длины, вы можете попробовать:

однако будьте осторожны, если вы собираетесь индексировать рассматриваемый столбец (как указывалось ранее @APC).

Я согласен что я бы предпочел просто объявить строку (которая, кстати, определена в SYS.Стандарт как подтип VARCHAR2) без необходимости объявлять длину, но это просто не так, как работает Oracle, и поскольку я не собираюсь начинать писать свою собственную реляционную базу данных (у меня есть свои собственные ветряные мельницы, на которых можно наклонить, спасибо :-) я просто соглашусь с статус-кво.

надеюсь, это поможет.

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

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

несмотря на то, что он не выделяет заданное количество байтов на диске, как поле char, все еще есть достойные причины для размера:

выделение памяти на считывателях данных (на основе максимального размера строки)
индексирование большой столбец приносит размеры блоков в игру
Etc.

Я уверен, что есть больше причин, которые кто-то еще может придумать, но это те, которые я видел в прошлом проекте, где кто-то решил varchar2(4000) всё.

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

или купить очень большие конверты.

возможно влияние производительности: в MySQL, temporary tables и MEMORY tables магазине VARCHAR столбец как столбец фиксированной длины, проложенный к ее максимальной длине.

если вы дизайн VARCHAR столбцы намного больше, чем самый большой размер, который вам нужен, вы будете потреблять больше памяти, чем вам нужно. Это влияет cache efficiency, sorting speed, etc .

таким образом, вы даете максимальную длину, которая находится под вашей строкой. например, если вы максимальная длина персонажа 10, поэтому не давайте его длину 100 или более.

Проблема максимальной длины типа данных VARCHAR2 является запутанным вопросом, потому что VARCHAR2 разделен на типы переменных в типах данных PL / SQL и Oracle Тип поля в базе данных. Проще говоря, это зависит от того, в каком сценарии приложения вы находитесь, иначе сложно ответить на вопрос о максимальной длине типа данных VARCHAR2.

ORACLEТип поля базы данных

Что касается максимальной длины поля типа VARCHAR2 в базе данных Oracle, давайте посмотрим на следующий пример:

Как показано выше, в базе данных Oracle тип поля VARCHAR2 имеет максимальное значение 4000. В Справочном руководстве по SQL также четко указано, что максимальный размер VARCHAR2 составляет 4000. Обратите внимание на максимальную длину здесь Степень относится к длине байтов, а не к количеству символов. Это имеет определенную связь с параметром NLS_LENGTH_SEMANTICS, как показано ниже, когда параметр NLS_LENGTH_SEMANTICS является байтовым, а длина определенной переменной - байтовой длиной.

Как показано ниже, значение NLS_CHARACTERSET этой базы данных равно AL32UTF8, а китайский символ занимает три байта.

Если параметр NLS_LENGTH_SEMANTICS определен, VARCHAR2 (7) означает

Независимо от того, значение параметра NLS_LENGTH_SEMANTICS является символом или байтом, количество байтов строки, которое он может содержать, не может превышать 4000.

PL/SQLТип переменной:

Затем давайте посмотрим на тип переменной VARCHAR2 в PL / SQL. Как показано в официальном документе ниже, его максимальная длина в байтах составляет 32767 байтов, а количество символов, которое можно разместить, зависит от набора символов.

Declaring Variables for Multibyte Characters

The maximum size of a CHAR or VARCHAR2 variable is 32,767 bytes, whether you specify the maximum size in characters or bytes. The maximum number of characters in the variable depends on the character set type and sometimes on the characters themselves:

Character Set Type

Maximum Number of Characters

Single-byte character set

n-byte fixed-width multibyte character set (for example, AL16UTF16)

n-byte variable-width multibyte character set with character widths between 1 and n bytes (for example, JA16SJIS or AL32UTF8)

Depends on characters themselves—can be anything from 32,767 (for a string containing only 1-byte characters) through FLOOR(32,767/n) (for a string containing only n-byte characters).

When declaring a CHAR or VARCHAR2 variable, to ensure that it can always hold n characters in any multibyte character set, declare its length in characters—that is, CHAR(n CHAR) or VARCHAR2(n CHAR), where n does not exceed FLOOR(32767/4) = 8191.

Вы можете проверить это с помощью следующего кода PL / SQL.Как показано ниже, вы можете определить переменную типа VARCHAR2 и назначить ей 6000 строк.

Если присвоение переменной типа VARCHAR2 превышает 23767, он сообщит PLS-00215: String length constraints must be in range (1 .. 32767) ошибка.

Так случилось, что продукт, который мы разрабатываем работает с несколькими реляционными базами данных. Сейчас это 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 :)

Выводы:

Неплохо бы иногда проверять~~, не прокрался ли враг~~ или, если Вы достаточно смелый, изменить эту настройку по умолчанию.

Читайте также: