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
Администрирование баз данных

Максимальный размер строки типа данных varchar2(4000). Oracle DB.

При использовании UTF-8 кодировки один символ может занимать (1 – 4) байта.

Русские символы занимают 2 байта, английские 1 байт

SELECT LENGTHB(‘ф’),LENGTHB(‘f’) FROM DUAL;

Поле в таблице можно определить с указанием CHAR размер поля будет определяется кол-вом символов, но ограничен общим размером типа 4000 байт

CREATE TABLE TZ_VARCHAR2 (

V_10 VARCHAR2 (10)

, V_10_CHAR VARCHAR2 (10 CHAR)

INSERT INTO TZ_VARCHAR2 (V_10) VALUES (‘фффффффффф’); — ошибка при вставке

INSERT INTO TZ_VARCHAR2 (V_10_CHAR) VALUES (‘фффффффффф’); — успех, значение укладывается в 10 символов и размер не превышает общего ограничения 4000 байт

Максимальный размер можно увеличить через настройки:

32767 байтов или символов, если MAX_STRING_SIZE = EXTENDED

4000 байтов или символов, если MAX_STRING_SIZE = STANDARD

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 — Урок 2. Типы данных

Этот урок носит больше теоретический характер, но пропустить его нельзя. В дальнейшем вы сможете возвращаться к нему, как к справочному уроку, сейчас же просто ознакомьтесь. В прошлом уроке говорилось, что MySQL поддерживает числовые, строковые, календарные данные и данные типа NULL. Рассмотрим их по очереди.

Числовые типы данных

Тип данных	Объем памяти	Диапазон	Описание
TINYINT (M)	1 байт	от -128 до 127 или от 0 до 255	Целое число. Может быть объявлено положительным с помощью ключевого слова UNSIGNED, тогда элементам столбца нельзя будет присвоить отрицательное значение. Необязательный параметр М — количество отводимых под число символов. Необязательный атрибут ZEROFILL позволяет свободные позиции по умолчанию заполнить нулями.

TINYINT — хранит любое число в диапазоне от -128 до 127.

TINYINT UNSIGNED — хранит любое число в диапазоне от 0 до 255.

TINYINT (2) — предполагается, что значения будут двузначными, но по факту будет хранить и трехзначные.

SMALLINT — хранит любое число в диапазоне от -32768 до 32767.

SMALLINT UNSIGNED — хранит любое число в диапазоне от 0 до 65535.

SMALLINT (4) — предполагается, что значения будут четырехзначные, но по факту будет хранить и пятизначные.

MEDIUMINT — хранит любое число в диапазоне от -8388608 до 8388608.

MEDIUMINT UNSIGNED — хранит любое число в диапазоне от 0 до 16777215.

MEDIUMINT (4) — предполагается, что значения будут четырехзначные, но по факту будет хранить и семизначные.

INT — хранит любое число в диапазоне от -2147683648 до 2147683648.

INT UNSIGNED — хранит любое число в диапазоне от 0 до 4294967295.

INT (4) — предполагается, что значения будут четырехзначные, но по факту будет хранить максимально возможные.

BIGINT — хранит любое число в диапазоне от -2 63 до 2 63 -1.

BIGINT UNSIGNED — хранит любое число в диапазоне от 0 до 2 64 .

BIGINT (4) — предполагается, что значения будут четырехзначные, но по факту будет хранить максимально возможные.

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

Строковые типы данных

Тип данных	Объем памяти	Максимальный размер	Описание
CHAR (M)	M символов	М символов	Позволяет хранить строку фиксированной длины М. Значение М — от 0 до 65535.

Календарные типы данных

Тип данных	Объем памяти	Диапазон	Описание
DATE	3 байта	от ‘1000-01-01’ до ‘9999-12-31’	Предназначен для хранения даты. В качестве первого значения указывается год в формате «YYYY», через дефис — месяц в формате «ММ», а затем день в формате «DD». В качестве разделителя может выступать не только дефис, а любой символ отличный от цифры.
TIME	3 байта	от ‘-838:59:59’ до ‘838:59:59’	Предназначен для хранения времени суток. Значение вводится и хранится в привычном формате — hh:mm:ss, где hh — часы, mm — минуты, ss — секунды. В качестве разделителя может выступать любой символ отличный от цифры.
DATATIME	8 байт	от ‘1000-01-01 00:00:00’ до ‘9999-12-31 23:59:59’	Предназначен для хранения и даты и времени суток. Значение вводится и хранится в формате — YYYY-MM-DD hh:mm:ss. В качестве разделителей могут выступать любые символы отличные от цифры.
TIMESTAMP	4 байта	от ‘1970-01-01 00:00:00’ до ‘2037-12-31 23:59:59’	Предназначен для хранения даты и времени суток в виде количества секунд, прошедших с полуночи 1 января 1970 года (начало эпохи UNIX).
YEAR (M)	1 байт	от 1970 до 2069 для М=2 и от 1901 до 2155 для М=4	Предназначен для хранения года. М — задает формат года. Например, YEAR (2) — 70, а YEAR (4) — 1970. Если параметр М не указан, то по умолчанию считается, что он равен 4.

Тип данных NULL

Вообще-то это лишь условно можно назвать типом данных. По сути это скорее указатель возможности отсутствия значения. Например, когда вы регистрируетесь на каком-либо сайте, вам предлагается заполнить форму, в которой присутствуют, как обязательные, так и необязательные поля. Понятно, что регистрация пользователя невозможна без указания логина и пароля, а вот дату рождения и пол пользователь может указать по желанию. Для того, чтобы хранить такую информацию в БД и используют два значения:

NOT NULL (значение не может отсутствовать) для полей логин и пароль,

NULL (значение может отсутствовать) для полей дата рождения и пол.

По умолчанию всем столбцам присваивается тип NOT NULL, поэтому его можно явно не указывать.

create table users (login varchar(20), password varchar(15), sex enum(‘man’, ‘woman’) NULL, date_birth date NULL);

Таким образом, мы создаем таблицу с 4 столбцами: логин (не более 20 символов) обязательное, пароль (не более 15 символов) обязательное, пол (мужской или женский) не обязательное, дата рождения (тип дата) необязательное.

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

Программирование на Python для начинающих

Онлайн-курс. Освойте востребованную профессию с зарплатой от 70 000 руб в месяц!

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