SQL-Ex blog

Как обычно, будем использовать свободно распространяемую тестовую базу Chinook, доступную в форматах многих реляционных СУБД. Эта база данных имитирует магазин цифровых медиа с образцовыми данными, так что все, что вам требуется, это загрузить необходимую копию, и вы получите скрипты создания структур данных и операторы вставки данных.

SQL Server

В SQL Server имеются следующие строковые типы данных: VARCHAR(n), VARCHAR(MAX) и NVARCHAR(n), NVARCHAR(MAX); имеется также тип TEXT, но поскольку он является устаревшим, его не следует больше использовать.

В типах данных VARCHAR n означает максимальное число хранимых байтов; это не число символов, и их может быть максимум 8000. Если же вы используете MAX, то максимально возможный размер достигает 2 Гб (размер хранилища) для символьного типа данных, не являющегося Unicode.

Аналогично в типах данных NVARCHAR n означает число пар байтов, поэтому предельное значение составляет 4000 (максимальная длина), а MAX означает максимальное количество в 2 Гб (максимальное хранилище) для символов Юникод.

Очевидно, что число байтов или пар байтов для латинских наборов символов эквивалентно числу символов, но для других наборов это не справедливо!

Давайте установим пример T-SQL, создающий новую таблицу, в которой мы будем хранить сообщения в клиентскую службу для магазина цифрового медиа.

CREATE TABLE CustomerMessages(

   MessageId int not null,

   CustomerId int NOT NULL,

   InsertDate smalldatetime NOT NULL,

   UpdateDate smalldatetime NULL,

   MessageContent varchar(5000)

 CONSTRAINT PK_Message PRIMARY KEY CLUSTERED 

(

   MessageId ASC

) ON [PRIMARY]

) ON [PRIMARY];

ALTER TABLE CustomerMessages ADD CONSTRAINT [FK_CustomerMessages_Customer] FOREIGN KEY(CustomerId)

REFERENCES Customer (CustomerId)

Как можно увидеть, мы создали новую таблицу, добавив внешний ключ для связи с CustomerID и проверки, что клиент существует, а также первичный ключ на MessageId. В этом случае мы получаем максимум 5000 байтов (в нашем случае, поскольку мы имеем набор латиницы, это 5000 символов) в столбце MessageContent.



Теперь предположим, что мы увидели, что 5000 символов недостаточно в некоторых случаях, а также заметили, что текст, в Messages сильно варьируется от нескольких слов до длинных параграфов, поэтому мы хотим изменить его на varchar(max). Это может быть сделано следующим образом.

ALTER TABLE CustomerMessages ALTER COLUMN MessageContent varchar(max)

Как видно, в SQL Server изменить тип очень просто!

Oracle

В Oracle строковые типы используются аналогично, но мы имеем другие предельные значения. Во-первых, есть VARCHAR2 и NVARCHAR2, которые имеют предел 4000 байтов, если параметр MAX_STRING_SIZE установлен в значение STANDARD (об этом ниже). Они более или менее похожи на строки переменной длины VARCHAR и NVARCHAR в SQL Server.

Два других типа, используемых для хранения большого текста, это CLOB и NCLOB. LOB - это сокращение для Large Objects (большие объекты), C здесь означает символы (characters), а N, как и N в NVARCHAR, обозначает National Character (национальные символы) для данных Юникод. Эти типы данных довольно удобны для хранения большого количества данных и ограничены размером в 4 Гб. Они могут быть очень сложными и, вообще говоря, не очень хороши с точки зрения производительности, т.к. хранятся в отдельных табличных пространствах, а также требуют специальной обработки на стороне приложения.

Давайте добавим ту же таблицу, что и для SQL Server.

CREATE TABLE CHINOOK.CUSTOMERMESSAGES

   (MESSAGEID NUMBER NOT NULL ENABLE, 

    CUSTOMERID NUMBER NOT NULL ENABLE, 

    INSERTDATE date NOT NULL ENABLE, 

    UPDATEDATE date,

    MESSAGECONTENT varchar2(4000), 

CONSTRAINT PK_MESSAGE PRIMARY KEY (MESSAGEID));

ALTER TABLE CHINOOK.CUSTOMERMESSAGES add CONSTRAINT FK_CUSTOMERID FOREIGN KEY (CUSTOMERID)

     REFERENCES CHINOOK.CUSTOMER (CUSTOMERID) ENABLE;

Структура и типы данных таблицы аналогичны использованным в SQL Server, но обратите внимание, что столбец MESSAGECONTENT имеет ограничение 4000 байтов (символов в нашем случае). Теперь давайте попытаемся изменить на 5000, как в SQL Server.

ALTER TABLE CHINOOK.CUSTOMERMESSAGES MODIFY (MESSAGECONTENT VARCHAR2(5000 BYTE)); 

Как видно, мы достигли предела, но, начиная с версии Oracle 12c, имеется решение, о котором я намекал ранее, которое позволяет увеличить предел типов данных VARCHAR2 и NVARCHAR2 до 32767 байтов.

Сначала давайте проверим упомянутый выше параметр MAX_STRING_SIZE.

SHOW PARAMETER MAX_STRING_SIZE 

Как ожидалось, параметр установлен в значение по умолчанию STANDARD. Для увеличения предела VARCHAR2 и NVARCHAR2 до 32767 байтов нам нужно установить этот инициализационный параметр в значение EXTENDED.

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

SELECT name, value, description FROM v$parameter WHERE name = 'compatible';

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

Я покажу эти шаги для изменения параметра на единственной PDB.

Первый шаг - это остановить PDB и снова открыть её в режиме UPGRADE (или миграции), что может быть сделано подключением к хосту в ssh, а затем подключением к Container DB или CDB с помощью sqlplus и выполнением команды:




Теперь мы можем выполнить команду для её открытия в режиме миграции:




Мы теперь можем подключиться к PDB и изменить параметр инициализации:





Проверим параметр:




Сейчас мы можем выполнить эти два скрипта для преобразования и перекомпиляции объектов в нашей базе данных:


и


Теперь мы готовы перезапустить базу данных (PDB):




И снова проверим параметр:



Теперь мы можем снова попытаться изменить столбец MESSAGECONTENT на 5000 байтов:

ALTER TABLE CHINOOK.CUSTOMERMESSAGES MODIFY (MESSAGECONTENT VARCHAR2(5000 BYTE));

Проверяя таблицу в Oracle Developer, мы видим, что теперь размер столбца равен 5000 байтов:



Помните, что предельное значение составляет 32767 байтов, поэтому VARCHAR2(32767) - это максимум, который мы можем задать.

ALTER TABLE CHINOOK.CUSTOMERMESSAGES MODIFY (MESSAGECONTENT VARCHAR2(32767 BYTE) );

PostgresSQL

В PostgreSQL у нас есть два типа данных, которые используются для хранения больших текстов: VARCHAR(N) и TEXT.

Имеется пара отличий от SQL Server и Oracle: первое, N является числом символов, а не байтов, более того, мы можем опустить это число, и тогда это будет эквивалентно VARCHAR(MAX) в SQL Server и типу данных TEXT. В этом случае предельное значение числа символов не указывается в официальной документации PostgreSQL, хотя имеется такая информация: "В любом случае максимальная строка символов, которая может быть сохранена, имеет длину около 1 Гб".

Теперь мы можем попробовать создать таблицу, аналогичную двум другим СУБД:

CREATE TABLE IF NOT EXISTS public."CustomerMessages"

(

   "MessageId" integer not null,

   "CustomerId" integer NOT NULL,

   "InsertDate" timestamp without time zone NOT NULL,

   "UpdateDate" timestamp without time zone,

   "MessageContent" varchar(5000),

 CONSTRAINT "PK_Message" PRIMARY KEY ("MessageId")

)

ALTER TABLE public."CustomerMessages"

    ADD CONSTRAINT "FK_CustomerMessages_Customer" FOREIGN KEY("CustomerId")

    REFERENCES public."Customer" ("CustomerId") MATCH SIMPLE

    ON UPDATE NO ACTION

    ON DELETE NO ACTION;

Обратите внимание на предложение IF NOT EXISTS в CREATE TABLE, это очень удобно!

Поскольку мы увидели, что 5000 символов недостаточно, модифицируем столбец, выходя за предел, чтобы мы могли хранить максимальное символов:

ALTER TABLE "CustomerMessages" ALTER COLUMN "MessageContent" type varchar

Вот и все для PostgreSQL!

Заключение

Здесь мы рассмотрели типы данных, которые обычно используются для хранения больших строк в SQL Server, Oracle и PostgreSQL. Мы увидели различные предельные значения синтаксис, а также модификацию инициализационных параметров базы данных для увеличения предельного значения в Oracle.