CHAR и VARCHAR

Недавно мне довелось искать ошибку в решении, которое содержало такое преобразование:

CAST(model AS VARCHAR)

Те, кто изучил схему «Компьютеры», подумают о бессмысленности преобразования типа в тот же самый тип (столбец model определен как VARCHAR(50)). Однако именно это преобразование и делало запрос неверным.

Дело в том, что, если размер типа при преобразовании не указан, то в SQL Server принимается значение по умолчанию, которое для VARCHAR равно 30. При этом если преобразуемая строка имеет больший размер, то отсекаются все символы кроме первых 30-ти. Разумеется, никакой ошибки при этом не возникает. Как раз на «длинных» номерах моделей предложенное решение и давало неверный результат. Как говорится в таких случаях, читайте документацию. Однако интересно, что по этому поводу говорит Стандарт?

Согласно стандарту, если для типов CHAR и VARCHAR размер не указан, то подразумевается CHAR(1) и VARCHAR(1) соответственно. Давайте проверим, как следуют стандарту доступные на сайте СУБД: SQL Server, MySQL, PostgreSQL и Oracle.

Тут имеется два аспекта:

1. Преобразование типа

2. Использование типов CHAR/VARCHAR при описании схемы (DDL).

Начнем с преобразования типа.

SQL Server

SELECT
CAST('11111111112222222222333333333344444444445555555555' AS CHAR) chr,
CAST('11111111112222222222333333333344444444445555555555' AS VARCHAR) vchr;

В результате получим

То есть оба символьных типа усекаются до значения по умолчанию, которое равно 30. Никаких сообщений об ошибках не возникает, что, собственно, соответствует стандарту.

PostgreSQL

SELECT
CAST('11111111112222222222333333333344444444445555555555' AS CHAR) AS chr,
CAST('11111111112222222222333333333344444444445555555555' AS VARCHAR) AS vchr;

Налицо половинчатое следование стандарту, т.е. соответствие ему в отношении типа CHAR. Что касается типа VARCHAR, то согласно документации, если длина строки не указана, принимается строка любого размера, т.е. усечения не происходит. (If character varying is used without length specifier, the type accepts strings of any size.)

MySQL

Чем дальше, тем «страньше». Оказывается, в MySQL преобразование к типу VARCHAR вообще не поддерживается. Нам остается проверить только преобразование к CHAR:

SELECT CAST('11111111112222222222333333333344444444445555555555' AS CHAR) chr;

Т.е. строка не усекается; при этом в документации читаю: «Если при использовании функций CAST и CONVERT размер не указан, то длина по умолчанию – 30.» (When n is not specified when using the CAST and CONVERT functions, the default length is 30).

Посмотрим теперь, как обстоят дела с определением данных. Ниже приведен тестовый скрипт.

CREATE TABLE Test_char(
chr CHAR,
vchr VARCHAR
);

DELETE FROM Test_char;

INSERT INTO Test_char
VALUES
('1','11111111112222222222333333333344444444445555555555');
INSERT INTO Test_char
VALUES
('11111111112222222222333333333344444444445555555555', '1');
INSERT INTO Test_char
VALUES
('2',CAST('111111111122222222223333333333' AS VARCHAR));
INSERT INTO Test_char
VALUES
(CAST('111111111122222222223333333333' AS CHAR), '2');
INSERT INTO Test_char
VALUES
('3', '3');

SELECT * FROM Test_char;

SQL Server

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

Хотя здесь выдержано соответствие стандарту, мне кажется, что есть некое противоречие в том, что не работает явное приведение к типу столбца таблицы:

INSERT INTO Test_char
VALUES
(CAST('111111111122222222223333333333' AS CHAR), '2');

PostgreSQL

Можно отметить последовательность в поведении: VARCHAR имеет произвольный размер; вторая строка не была вставлена из-за ошибки превышения размера (ERROR: value too long for type character(1)); явное преобразование значения к типу столбца таблицы работает, отсекая лишние символы справа.

MySQL

Не поддерживается тип VARCHAR без указания размера строки. CHAR соответствует CHAR(1) – по стандарту. Поскольку явное преобразование к CHAR оставляет длину строки без изменения, то в таблицу, определенную как

CREATE TABLE Test_char(
chr CHAR,
vchr VARCHAR(1)
);

в итоге, как и в SQL Server, добавится единственная строка:

Выводы. По моему скромному мнению, ни одна из упомянутых СУБД не отвечает стандартному поведению в тех случаях, когда размер типа не указывается. Наиболее последовательной в «особенностях реализации» является, на мой взгляд, PostgreSQL. В целях переносимости кода я бы рекомендовал всегда явно указывать размер.

Рассмотрим следующий код.

DECLARE @ch AS VARCHAR(2)='1';
DECLARE @t TABLE(ch VARCHAR(2));
insert into @t values('123');
select @ch=CONCAT(@ch,'2','3');
select @ch "var",(select ch from @t) "col";

В этом коде мы определяем простую переменную типа VARCHAR(2) и таблицу (табличную переменную) с единственным столбцом того же типа данных - VARCHAR(2).

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

Этим объясняется результат - NULL в столбце col, т.е. данные не были вставлены в таблицу.

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

SELECT LEN(REPLICATE('a',100000)) "varchar"
      ,LEN(REPLICATE(CAST('a' AS NVARCHAR),100000)) "nvarchar"
      ,LEN(REPLICATE(CAST('c' AS VARCHAR(MAX)),100000)) "varchar_max";

Данный пример показывает, что простые символьные типы усекаются до размера страницы SQL Server - 8000 байтов (тип NVARCHAR использует 2 байта на символ). Тип VARCHAR(MAX) позволяет хранить данные до 2Гб.

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

SELECT LEN(CONCAT(REPLICATE('a', 7000), REPLICATE('b', 7000))) ch;

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