Varchar2 100 byte сколько символов

varchar2 и Unicode для тех, кто ничего не понимает в базах данных Oracle или ORA-12899: value too large for column

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

Давайте изменим размер колонки, например, на следующий

alter table address modify street varchar2(150);

Как Вы думаете 150 — это длина в символах (как в других базах в общем-то)? Подсказка — нет 🙂 Скорее всего в байтах.

А в символах это

alter table address modify street varchar2(150 char);

Т.е. не указывая спецификацию char — byte мы оказываемся в серой зоне настроек базы данных по умолчанию. Причем во всех базах до которых мы смогли дотянуться (включая продакшн и не только наши) настройка по умолчанию — это байты.

А теперь давайте вспомним, что в UTF-8, например, один символ может занимать от одного до 4 байт (обычно 1 байт ANSI, 2 русские символы и некоторые которым больше повезло и до 4 для иероглифов).

И что это за дикая настройка по умолчанию для Unicode баз!? Но ведь, именно она, зараза такая, включена «из коробки». Ну т.е. да, я все понимаю: legacy, обратная совместимость для тех времен, когда Unicode’а еще и «в проекте не было», гордость за то, что backup 86 года можно восстановить последней редакцией imp — вот это вот все.

А почему ошибка возникала только иногда и только для некоторых колонок? Так как тот tool, которым мы генерируем базу изначально был настолько умным, что сразу в create table для всех колонок явно прописывал суффикс char 🙂

Выводы:

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

Скрипт для определения значения по умолчанию

SELECT value FROM NLSDATABASEPARAMETERS WHERE parameter='NLSLENGTHSEMANTICS';

Скрипт, который позволяет проверить, что у вас в базе «все ОК»:

SELECT TABLE_NAME, COLUMN_NAME, DATA_LENGTH, CHAR_USED FROM USER_TAB_COLUMNS WHERE DATA_TYPE = 'VARCHAR2' AND CHAR_USED = 'B' ORDER BY TABLE_NAME, COLUMN_NAME

P.S. Сразу оговорюсь, это нормально, если там где Вы это ожидаете размерность в байтах (например, там где 100% ansi символы), но вот для Unciode текста … Ушел плакать дальше на эту тему .

P.P.S. Regexp которым можно попробовать найти скрипты «серой зоны» varchar2\(\s*\d+\s*\)

P.P.P.P.S. А вот, что думает Oracle по поводу изменения значения параметра NLSLENGTHSEMANTICS на что-то более разумное «Oracle strongly recommends that you do NOT set the NLSLENGTHSEMANTICS parameter to CHAR in the instance or server parameter file. This may cause many existing installation scripts to unexpectedly create columns with character length semantics, resulting in runtime errors, including buffer overflows.» https://docs.oracle.com/cd/E2469301/server.11203/e24448/initparams149.htm

• Oracle
• PostgreSQL
• Java
• Microsoft SQL Server
• Администрирование баз данных

DeepEdit!

• 
• 
• 

Переменные этих типов используются для хранения строковых или сим­вольных данных. В это семейство входят типы VARCHAR2, CHAR и LONG, а также NCHAR и NVARCHAR2 (два последних типа доступны то­лько в Огас1е8 и выше).

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

где L — максимальная длина (length) переменной. Указание длины обяза­тельно — значения по умолчанию не существует. Максимальная длина пе­ременной типа VARCHAR2 составляет 32 767 байт. Обратите внимание, что в поле столбца базы данных, имеющем тип VARCHAR2, можно хра­нить только 4000 байт. Если длина Piy SQL-переменной типа VARCHAR2 превышает 4000 байт, ее можно ввести лишь в столбец базы данных, име­ющий тип LONG, максимальный размер которого составляет 2 Гбайт или GLOB (4 Гбайт). Аналогично, данные LONG и CLOB нельзя поместить в переменную VARCHAR2, если их размер превышает 32 767 байт.

Внимание

в поле столбца базы данных, имеющем тип VARCHAR2, можно сохранять 2000 байт. Таким образом, PL/SQL-переменная VARCHAR2мoжeт быть записана в столбец Oracle7 VARCHAR2, только если ее длина не превышает 2000 байт.

Для типа VARCHAR2 длина указывается не в символах, а в байтах. Информация хранится в базе данных с помощью принятого набора сим­волов, например ASCII, EBCDIC Code Page 500 или набора многобайто­вых символов переменной длины, такого как Unicode. Если в некотором

наборе символов базы данных содержатся многобайтовые символы,
максимальное число символов, которое может храниться в переменной
типа VARCHAR2, скорее всего, будет меньше указанной длины. Дело в
том, что для представления одного символа может использоваться бо­лее одного байта.
Подтипы VARCHAR и STRING эквивалентны типу VARCHAR2.
Совет

Почему существуютдва типа: VARCHAR и VARCHAR2? Тип VARCHAR определен ANSI, а тип VARCHAR2 определен Oracle. В настоящее время они ведут себя одинаково. Если тип ANSI VARCHAR изменится в будущем, то Oracle VARCHAR2 не изменится,

В Огас1е9г синтаксис объявления переменной VARCHAR2 расширен до VARCHAR2 (L [ CHAR | BYTE])
где L также является максимальной длиной переменной. CHAR или

BYTE используется для указания того, что L измеряется в символах или байтах соответственно (по умолчанию применяется CHAR). Максималь­ная длина, однако, по-прежнему составляет 32 767 байт. Допустим, что база данных использует набор символов UTF8, который содержит мно­гобайтовые символы переменной длины. Максимальная длина символа

UTF8 равна 3 байтам. Это означает, что переменная, объявленная как VARCHAR2(300 BYTE), может содержать максимум 100 символов в зави­симости от реальных хранимых символов.

CHAR Переменные этого типа представляют собой строки символов фиксированной длины. Синтаксис объявления переменной CHAR таков:

где L — максимальная длина в байтах. Однако в отличие от типа VARCHAR2 в этом случае указание длины необязательно. Если она не задана, прини­мается значение по умолчанию, равное 1, причем круглые скобки не нуж­ны. Переменные типа CHAR имеют фиксированную длину, поэтому при необходимости они заполняются до максимальной длины пробелами.

Следовательно, переменные типа CHAR не всегда будут совпадать при выполнении операций сравнения символов (см. главу 4).

Максимальная длина переменной типа CHAR равна 32 767 байт. Мак­симальная же ширина поля столбца базы данных, имеющего тип CHAR,

составляет 2000 байт. Таким образом, если в переменной CHAR содер­жится более 2000 байт, ее можно ввести только в столбец базы данных

типа VARCHAR2 (если длина
Внимание
В Огасlе 7 поле столбца базы данных, имеющем тип CHAR, можно сохранять до 255 байт.


Как и для длина переменной типа CHAR указывается не в

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

Подтипом CHAR, имеющим те же ограничения, является CHARACTER. Семантика переменных VARCHAR2 и переменных CHAR существенно различается (см. главу 4).

В ОгасЫЬ» синтаксис объявления переменной CHAR расширен до
CHAR[(Z[CHAR| BYTE])]

где L также является максимальной длиной переменной. CHAR и BYTE используются для указания на то, что L будет измеряться в символах или байтах соответственно (по умолчанию применяется CHAR). Максималь­ная длина, однако, по-прежнему составляет 32 767 байт. Предположим, что база данных использует набор символов UTF8, который содержит

многобайтовые символы переменной длины. В UTF8 максимальная дли­на символа равна 3 байтам. Это означает, что переменная, объявленная как CHAR(300 BYTE), сможет содержать максимум 100 символов (в слу­чае необходимости дополненных пробелами) в зависимости от реально используемых символов.

LONG В отличие от типа LONG, используемого в базах данных и позво­ляющего хранить до 2 Гбайт информации, при помощи типа LONG PL/SQL можно сохранять последовательности символов переменной длины, максимальный размер которых равен 32 760 байт. Переменные LONG очень похожи на переменные VARCHAR2. Если в поле столбца

LONG базы данных содержится более 32 760 байт информации, помес­тить эту информацию в PL/SQL-переменную LONG нельзя. Однако мак­симальная длина PL/SQL-переменной LONG меньше, чем поле LONG базы данных, поэтому PL/SQL-переменная LONG может быть помещена

в столбец LONG базы данных безо всяких ограничений.

NCHAR и NVARCHAR2 В Огас1е8 предусмотрены два дополнительных типа. Это символьные типы M.S(National Language Support — поддержка национальных языков): NCHAR и NVARCHAR2. Они служат для хранения строк символов с применением набора символов, отличного от того, ко­торый используется в языке программирования PL/SQL. Такой набор называется национальным набором символов (national character set). Переменные типов NCHAR и NVARCHAR2 описываются и использу­ются точно так же, как переменные типов CHAR и VARCHAR2. Однако длина может меняться в зависимости от применяемого национального

набора символов. Если в таком наборе размер символов фиксирован, дли­на указывается в символах. Если же их размер может меняться, длина ука­зывается в байтах.

Внимание
В Oracle длина NCHAR и NVARCHAR2 всегда определяется в символах.

Более подробно о типах NCHAR, NVARCHAR2 и о NLS рассказывает­ся в справочном руководстве «Server SQL Reference».

Артём Санников

Данная книга является руководством для начинающих специалистов в области анализа и обработки данных. В книге рассматривается язык SQL и его процедурное расширение PL/SQL от компании Oracle.

Главная › Базы данных › Oracle PL/SQL › Строки › Тип данных VARCHAR2 в PL/SQL Oracle

Тип данных VARCHAR2 в PL/SQL Oracle

В переменных типа VARCHAR2 хранятся символьные строки переменной длины. При объявлении такой строки для нее определяется максимальная длина в диапазоне от 1 до 32 767 байт. Максимальная длина может задаваться в байтах или символах, но в любом случае компилятор определяет ее в байтах.

Синтаксис объявления VARCHAR2

имя_переменной VARCHAR2 (макс_длина [CHAR | BYTE]);

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

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

Спецификатор длины CHAR используется в основном при работе с многобайтовыми наборами символов — такими, как UTF-8.

Если в объявлении переменной VARCHAR2 опустить спецификатор CHAR или BYTE , тогда заданное значение длины будет интерпретировано в байтах или символах в зависимости от параметра инициализации NLS_LENGTH_SEMANTICS . Текущее значение можно узнать, обратившись с запросом к NLS_SESSION_PARAMETERS .

Несколько примеров объявления строк типа VARCHAR2 :

DECLARE small_string VARCHAR2(4); line_of_text VARCHAR2(2000); feature_name VARCHAR2(100 BYTE); -- Строка длиной 100 байт emp_name VARCHAR2(30 CHAR); ------- Строка длиной 30 символов

Итак, максимальная длина переменной типа VARCHAR2 в PL/SQL составляет 32 767 байт. Это ограничение действует независимо от того, определяется ли длина строки в байтах или символах. Однако следует учитывать, что SQL поддерживает этот максимум только в том случае, если параметру инициализации MAX_SQL_STRING_SIZE задано значение EXTENDED ; по умолчанию используется значение STANDARD .

Если вам понадобится работать со строками длиной более 4000 байт, рассмотрите возможность их хранения в столбцах типа CLOB .

Записи по теме

Типы char и varchar (Transact-SQL)

Символьные типы данных имеют фиксированный (char) или переменный (varchar) размер. Начиная с SQL Server 2019 (15.x) при использовании параметров сортировки с поддержкой UTF-8 эти типы данных хранят весь диапазон символьных данных Юникод и используют кодировку UTF-8. Если указаны параметры сортировки без поддержки UTF-8, эти типы данных хранят только подмножество символьных данных, поддерживаемых соответствующей кодовой страницей указанных параметров сортировки.

Аргументы

char [ ( n ) ]

Строковые данные фиксированного размера. n определяет размер строки в байтах и должно иметь значение от 1 до 8000. Для наборов символов однобайтовой кодировки, таких как Latin , размер хранилища равен n байтам, а количество символов, которые можно хранить, также равно n. Для многобайтовых кодировок размер при хранения тоже равен n байт, но количество хранимых символов может быть меньше n. Синонимом по стандарту ISO для типа char является character. Дополнительные сведения о кодировках см. в статье Однобайтовые и многобайтовые кодировки.

varchar [ ( n | max ) ]

Строковые данные переменного размера. Используйте n для определения размера строки в байтах и может быть значением от 1 до 8 000 или использовать максимальное значение, чтобы указать размер ограничения столбца до максимального объема хранилища 2^31-1 байт (2 ГБ). Для наборов символов кодировки с одним байтом, например Latin , размер хранилища равен n байтам + 2 байтам, а количество символов, которые можно сохранить, также равно n. Для многобайтовых кодировок размер при хранения тоже равен n байт + 2 байта, но количество хранимых символов может быть меньше n. Синонимы ISO для varchar являются разными или символьными. Дополнительные сведения о кодировках см. в статье Однобайтовые и многобайтовые кодировки.

Замечания

Распространенное заблуждение заключается в том, чтобы думать, что с char(n) и varchar(n), n определяет количество символов. Однако в char(n) и varchar(n) n определяет длину строки в байтах (от 0 до 8 000). n никогда не определяет количество хранимых символов. Это аналогично определению nchar(n) и nvarchar(n).

Неправильное представление происходит, так как при использовании однобайтовой кодировки размер хранилища char и varchar составляет n байтов, а число символов также равно n. Однако для многобайтовой кодировки, например UTF-8, более высокие диапазоны Юникода (от 128 до 114 111) приводят к одному символу с использованием двух или более байтов. Например, в столбце, определенном как char(10), ядро СУБД может хранить 10 символов, использующих однобайтовое кодирование (диапазон Юникода от 0 до 127), но менее 10 символов при использовании многобайтовой кодировки (диапазон Юникода от 128 до 114 111). Дополнительные сведения о хранении символов Юникода и их диапазонах см. в разделе Различия в хранении UTF-8 и UTF-16.

Если значение n в определении данных или инструкции объявления переменной не указано, длина по умолчанию равна 1. Если n не указан при использовании CAST и CONVERT функциях, длина по умолчанию составляет 30.

Объекты, использующие char или varchar , назначаются параметры сортировки по умолчанию базы данных, если только не назначено определенное параметры сортировки с помощью COLLATE предложения. Параметры сортировки контролируют кодовую страницу, используемую для хранения символьных данных.

Многобайтовые кодировки в SQL Server включают следующие:

• двухбайтовые кодировки (DBCS) для некоторых языков Восточной Азии, использующих кодовые страницы 936 и 950 (китайский), 932 (японский) или 949 (корейский).
• UTF-8 с кодовой страницей 65001. Область применения: SQL Server 2019 (15.x) и более поздних версий.

Если у вас есть сайты, поддерживающие несколько языков, примите к сведению следующие рекомендации:

• Для поддержки Юникода и минимизации проблем с преобразованием символов рекомендуем использовать параметры сортировки с поддержкой UTF-8 (начиная с SQL Server 2019 (15.x)).
• Если используется предыдущая версия SQL Server ядро СУБД, рекомендуется использовать типы данных Юникод nchar или nvarchar, чтобы свести к минимуму проблемы с преобразованием символов.

Если вы используете char или varchar, рекомендуется:

• Если размеры записей данных столбцов постоянны, используйте char.
• Если размеры записей данных столбцов значительно изменяются, используйте varchar.
• использовать varchar(max), если размеры записей данных в столбцах существенно отличаются и длина строки может превышать 8000 байт.

Если SET ANSI_PADDING выполняется OFF либо CREATE TABLE ALTER TABLE выполняется, столбец char, определенный как NULL, обрабатывается как varchar.

Для каждого столбца varchar(max) или nvarchar(max) требуется 24 байта дополнительного фиксированного выделения, которое подсчитывает ограничение строки 8060 байтов во время операции сортировки. Это может неявно ограничивать число ненулевых столбцов varchar(max) или nvarchar(max), которые могут быть созданы в таблице.

При создании таблицы или во время вставки данных не возникает особых ошибок (кроме обычного предупреждения о том, что максимальный размер строки превышает максимально допустимое значение в 8060 байт). Такой размер строки может вызывать ошибки (например, ошибку 512) во время некоторых обычных операций, таких как обновление ключа кластеризованного индекса, или сортировки полного набора столбцов, которая происходит только во время выполнения операции.

Преобразование символьных данных

При преобразовании символьного выражения в символьный тип данных другой длины значения, слишком длинные для нового типа данных, усекаются. Тип uniqueidentifier считается символьным типом, используемым при преобразовании из символьного выражения, поэтому на него распространяются правила усечения при преобразовании в символьный тип. См. раздел «Примеры».

Если символьное выражение преобразуется в символьное выражение другого типа данных или размера, например из char(5) в varchar(5) или из char(20) в char(15), то преобразованному значению присваиваются параметры сортировки входного значения. Если несимвольное выражение преобразуется в символьный тип данных, то преобразованному значению присваиваются параметры сортировки, заданные по умолчанию в текущей базе данных. В любом случае необходимые параметры сортировки можно присвоить с помощью предложения COLLATE.

Преобразования страниц кода поддерживаются для типов данных char и varchar, но не для текстового типа данных. Как и в ранних версиях SQL Server, о потере данных во время преобразования кодовых страниц не сообщается.

Символьные выражения, которые преобразуются в приближенный тип данных numeric, могут содержать необязательную экспоненциальную нотацию Это нотация является строчным или верхним регистром e E , за которым следует необязательный знак плюс ( + ) или минус ( — ), а затем число.

Символьные выражения, которые преобразуются в точный числовый тип данных, должны состоять из цифр, десятичной запятой и необязательного плюса ( + ) или минуса ( — ). Начальные пробелы не учитываются. Разделители запятой, такие как разделитель 123,456.00 тысяч, не допускаются в строке.

Символьные выражения, преобразованные в типы данных money или smallmoney , также могут включать необязательный десятичный знак и знак доллара ( $ ). Разделители запятой, как и в $123,456.00 , разрешены.

Когда пустая строка преобразуется в int, его значение становится 0 . Когда пустая строка преобразовывается в дату, ее значением становится значение даты по умолчанию, то есть 1900-01-01 .

Примеры

А. Отображение значения по умолчанию n при использовании в объявлении переменной

В следующем примере показано значение по умолчанию n равно 1 для типов данных char и varchar , когда они используются в объявлении переменной.

DECLARE @myVariable AS VARCHAR = 'abc'; DECLARE @myNextVariable AS CHAR = 'abc'; --The following returns 1 SELECT DATALENGTH(@myVariable), DATALENGTH(@myNextVariable); GO 

B. Отображение значения по умолчанию n при использовании varchar с CAST и CONVERT

В следующем примере показано, что значение по умолчанию n равно 30, если типы данных char или varchar используются с CAST и CONVERT функциями.

DECLARE @myVariable AS VARCHAR(40); SET @myVariable = 'This string is longer than thirty characters'; SELECT CAST(@myVariable AS VARCHAR); SELECT DATALENGTH(CAST(@myVariable AS VARCHAR)) AS 'VarcharDefaultLength'; SELECT CONVERT(CHAR, @myVariable); SELECT DATALENGTH(CONVERT(CHAR, @myVariable)) AS 'VarcharDefaultLength'; 

C. Преобразование данных для отображения

В следующем примере два столбца преобразуются в символьные типы, после чего к ним применяется стиль, применяющий к отображаемым данным конкретный формат. Тип денег преобразуется в символьные данные и стиль 1 , который отображает значения с запятыми каждые три цифры слева от десятичной точки и две цифры справа от десятичной запятой. Тип даты и времени преобразуется в символьные данные и стиль 3 , который отображает данные в формате dd/mm/yy . WHERE В предложении тип денег привязывается к типу символов для выполнения операции сравнения строк.

USE AdventureWorks2022; GO SELECT BusinessEntityID, SalesYTD, CONVERT (VARCHAR(12),SalesYTD,1) AS MoneyDisplayStyle1, GETDATE() AS CurrentDate, CONVERT(VARCHAR(12), GETDATE(), 3) AS DateDisplayStyle3 FROM Sales.SalesPerson WHERE CAST(SalesYTD AS VARCHAR(20) ) LIKE '1%'; 

BusinessEntityID SalesYTD DisplayFormat CurrentDate DisplayDateFormat ---------------- --------------------- ------------- ----------------------- ----------------- 278 1453719.4653 1,453,719.47 2011-05-07 14:29:01.193 07/05/11 280 1352577.1325 1,352,577.13 2011-05-07 14:29:01.193 07/05/11 283 1573012.9383 1,573,012.94 2011-05-07 14:29:01.193 07/05/11 284 1576562.1966 1,576,562.20 2011-05-07 14:29:01.193 07/05/11 285 172524.4512 172,524.45 2011-05-07 14:29:01.193 07/05/11 286 1421810.9242 1,421,810.92 2011-05-07 14:29:01.193 07/05/11 288 1827066.7118 1,827,066.71 2011-05-07 14:29:01.193 07/05/11 

D. Преобразование данных uniqueidentifer

В следующем примере значение uniqueidentifier преобразуется в тип данных char.

DECLARE @myid uniqueidentifier = NEWID(); SELECT CONVERT(CHAR(255), @myid) AS 'char'; 

Следующий пример показывает усечение данных, когда значение является слишком длинным для преобразования в заданный тип данных. Так как тип данных uniqueidentifier ограничен 36 символами, все символы, выходящие за пределы этой длины, будут усечены.

DECLARE @ID NVARCHAR(max) = N'0E984725-C51C-4BF4-9960-E1C80E27ABA0wrong'; SELECT @ID, CONVERT(uniqueidentifier, @ID) AS TruncatedValue; 

String TruncatedValue -------------------------------------------- ------------------------------------ 0E984725-C51C-4BF4-9960-E1C80E27ABA0wrong 0E984725-C51C-4BF4-9960-E1C80E27ABA0 (1 row(s) affected) 

См. также

• nchar и nvarchar (Transact-SQL)
• CAST и CONVERT (Transact-SQL)
• COLLATE (Transact-SQL)
• Преобразование типов данных (ядро СУБД)
• Типы данных (Transact-SQL)
• Оценка размера базы данных
• Поддержка параметров сортировки и Юникода
• Однобайтовые и многобайтовые кодировки