Типы данных SQL: стандарт ANSI/ISO и отличия в разных СУБД

Какие типы данных есть в SQL?

В стандарте ANSI/ISO SQL описываются различные типы данных, которые мо­гут храниться в SQL-базе данных и обрабатываться с помощью SQL. Исходный стандарт SQL1 определял лишь минимальный набор типов данных. Последующие версии стандарта расширили список, добавив в него строки переменной длины, дату и время, битовые строки, XML и другие типы данных. Современные коммер­ческие СУБД в состоянии работать с данными самых разных типов, причем между наборами типов данных у разных СУБД имеются существенные отличия. Ниже рассмотрим типичные типы данных.

  • Целые числа. Обычно это данные о ценах, количествах, возрасте и т.п. Целочисленные столбцы часто используются также для хранения иден­тификаторов, таких как идентификатор клиента, служащего или номер заказа.
  • Десятичные числа. Числа, имеющие дробную часть, но которые вычис­ляются точно,— например, курсы валют или проценты. Зачастую это денежные величины.
  • Числа с плавающей точкой. Величины, которые можно вычислить при­близительно, такие как вес или расстояние. Числа с плавающей точкой могут представлять более широкий диапазон значений, чем десятичные числа, но при работе с ними возможны погрешности округления.
  • Строки символов постоянной длины. Обычно это строки, имеющие одну и ту же длину, — например, почтовые коды, аббревиатуры стран или штатов, краткие описания и т.п. Если реальная строка оказывается короче, чем строки, хранящиеся в данном столбце, она дополняется про­белами, с тем чтобы ее длина соответствовала указанной в описании типа данных.
  • Строки символов переменной длины. Строки символов, длина кото­рых изменяется от строки к строке до некоторого максимального значе­ния. (В стандарте SQL1 были определены только строки постоянной дли­ны, которые проще в работе, но требуют значительно больше простран­ства для хранения.) Столбцы с этим типом данных обычно хранят имена людей или названия компаний, адреса, описания и т.д. В отличие от строк постоянной длины, здесь нет дополнения пробелами— хранится только необходимое количество символов, а также длина строки.
  • Денежные величины. Во многих СУБД поддерживается тип данных MONEY или CURRENCY, который обычно хранится в виде десятичного чис­ла или числа с плавающей точкой. Наличие отдельного типа данных для представления денежных величин позволяет правильно форматировать их при выводе на экран. Однако в стандарте SQL такие типы данных не определяются.
  • Дата и время. Поддержка значений даты/времени также широко рас­пространена в различных СУБД, хотя способы ее реализации довольно сильно отличаются друг от друга, поскольку до принятия стандарта раз­личные производители по-разному реализовывали этот тип данных. Обычно поддерживаются различные комбинации дат, времени, времен­ных интервалов и арифметических действий над этими величинами. Стандарт SQL включает детальную спецификацию типов данных date, TIME, TIMESTAMP и INTERVAL, а также поддержку часовых поясов и точ­ного времени (например, десятые или сотые доли секунды).
  • Логические величины. Одни СУБД, в частности Microsoft SQL Server, явно поддерживают логические значения (true или false), а другие разрешают выполнять в инструкциях SQL логические операции (сравнение, логическое И/ИЛИ и другие) над данными.
  • Длинные символьные объекты. В стандарт SQL:1999 добавлен тип данных CLOB, который обеспечивает хранение больших символьных строк, до предельных размеров, которые обычно составляют несколько гигабай­тов. Это позволяет хранить в базе данных целые документы, описания то­варов, технические статьи, резюме и другие неструктурированные тек­стовые данные. Ряд SQL-баз данных поддерживает собственные типы данных (добавленные до введения стандарта SQL:1999) для хранения длинных текстовых строк (обычно до 32000 или 65000 символов, а в ряде случаев — и того больше). Обычно СУБД ограничивает применение таких столбцов в интерактивных запросах и поисках.
  • Большие бинарные объекты. В стандарт SQL:1999 добавлен тип данных BLOB, который поддерживает хранение неструктурированных последо­вательностей байтов переменной длины. Столбцы, содержащие такие данные, используются для хранения видеоизображений, выполнимого кода и прочей неструктурированной информации. До публикации стан­дарта разработчики реализовывали собственные решения, такие как ти­пы данных IMAGE в SQL Server или LONG RAW в Oracle, которые могут хранить до 2 Гбайт данных.
  • Азиатские символы. С ростом поддержки базами данных глобальных приложений производители СУБД добавляют поддержку в строках фик­сированной и переменной длины многобайтовых символов, используемых для представления иероглифического письма или арабских символов. Для этого использовались собственные типы данных, такие как GRAPHIC и VARGRAPHIC в SQL Server. В настоящее время стандарт ANSI/ISO опреде­ляет различные символьные типы данных для наборов национальных сим­волов (nchar, NVARCHAR и nclob). Хотя большинство современных баз дан­ных поддерживает сохранение и выборку таких символов (зачастую с при­менением UNICODE для их представления), поддержка поиска и сорти­ровки достаточно сильно варьируется в различных базах данных.

В табл. 1 перечислены типы данных, определенные в стандарте ANSI/ISO.

Таблица 1. Типы данных ANSI/ISO SQL

Тип данных

Сокращение

Описание

CHARACTER(длина)

CHAR

Строки символов постоянной длины

CHARACTER VARYING (длина)

CHAR VARYING, VARCHAR

Строки символов переменной длины

CHARACTER LARGE OBJECT (длина)

CLOB

Большие строки символов переменной длины

NATIONAL CHARACTER (длина)

NATIONAL CHAR, NCHAR

Строки символов постоянной длины с наборами национальных символов

NATIONAL CHARACTER

VARYING(длина)

NATIONAL CHAR

VARYING, NCHAR

Строки символов переменной длины с наборами национальных символов

NATIONAL CHARACTER LARGE

OBJECT(длина)

NCLOB

Большие строки символов переменной длины с наборами национальных сим­волов

BIT(длина)

 

Битовые строки постоянной длины

BIT VARYING (длина)

 

Битовые строки переменной длины

INTEGER

INT

Целые числа

SMALLINT

 

Малые целые числа

NUMERIC (точность, масштаб)

 

Десятичные числа

DECIMAL(точность, масштаб)

DEC

Десятичные числа

FLOAT(точность)

Числа с плавающей точкой

REAL

Числа с плавающей точкой малой точ­ности

DOUBLE PRECISION

Числа с плавающей точкой большой точности

DATE

Календарные даты

TIME(точность)

Время

TIME WITH TIME ZONE ( точность)

Поясное время

TIMESTAMP (точность)

Дата и время

TIMESTAMP WITH TIME

ZONE (точность)

Дата и поясное время

INTERVAL

Временные интервалы

XML (модификатор типа [вторичный модификатор типа] )

Символьные данные в XML-формате

Различия в поддержке типов данных в разных реализациях SQL препятствуют переносимости SQL-приложений. Причины подобных различий следует искать в самом пути, по которому развивались реляционные базы данных. Вот типичная схема такого пути.

  • Производитель СУБД добавил в свой продукт новый тип данных, кото­рый обеспечивает полезные новые возможности для определенной груп­пы пользователей.
  • Другие производители ввели поддержку того же типа данных, но с не­большими модификациями, чтобы их нельзя было обвинить в слепом ко­пировании.
  • По прошествии нескольких лет роста популярности нового типа данных он появляется в большинстве ведущих СУБД, став частью "джентль­менского набора" базовых типов данных, поддерживаемых основными реализациями
  • Далее этой идеей начинают интересоваться комитеты по стандартизации, задачей которых является устранение произвольных различий в реализа­циях ведущих СУБД. Но чем больше таких различий, тем труднее найти компромисс. Как правило, результатом деятельности комитета является вариант, который не соответствует в точности ни одной из реализаций.
  • Производители СУБД начинают потихоньку внедрять поддержку полу­ченного стандартизированного типа данных, но поскольку они распола­гают обширной базой уже инсталлированных продуктов, то вынуждены сопровождать и свой собственный старый вариант типа данных.
  • По прошествии длительного времени (обычно включающего выпуск не­скольких новых версий СУБД) пользователи, наконец, полностью пере­ходят к использованию стандартного варианта рассматриваемого типа данных и производитель СУБД может начать процесс удаления под­держки старого варианта из своего продукта.

В качестве отличного примера рассмотрим форматы представления даты и времени. Так, DB2 давно поддерживает представление дат и времени при помо­щи трех различных типов данных.

DATE

Хранит дату наподобие "June 30,2008"

TIME

Хранит время суток наподобие "12:30:00 р.m."

TIMESTAMP

Представляет конкретный момент времени с точностью до наносекунд

 

Значения даты и времени можно представлять в виде строковых констант. Кроме того, поддерживаются арифметические операции над значениями даты. Ниже приведен пример с использованием дат DB2, в котором предполагается, что в столбце HIRE_DATE содержатся данные типа date.

 

SELECT NAME, HIRE_DATE FROM SALESREPS
    WHERE HIRE_DATE >= '05/30/2007' + 15 DAYS;

В СУБД SQL Server введен единый тип данных для представления даты и време­ни — DATETIME, который напоминает тип данных TIMESTAMP из DB2. Если столбец HIRE_DATE имеет тип DATETIME, в этой СУБД можно выполнить такой запрос.

SELECT NAME, HIRE_DATE
   FROM SALESREPS
WHERE HIRE_DATE >= '06/14/2007';

Поскольку в запросе не указано конкретное время 14 июня 2007 года, SQL Server по умолчанию считает, что время соответствует полуночи. Таким образом, запрос для SQL Server в действительности означает следующее.

SELECT NAME, HIRE_DATE
   FROM SALESREPS
WHERE HIRE_DATE >= '06/14/2007 12:00AM';

Кроме того, SQL Server поддерживает арифметические операции над датами с помощью набора встроенных функций. Так, рассматриваемый выше запрос из DB2 можно переписать для SQL Server следующим образом.

 

SELECT NAME, HIRE_DATE
   FROM SALESREPS
WHERE HIRE_DATE >= DATEADD(DAY, 15, '05/30/2007');

Это, конечно же, значительно отличается от синтаксиса DB2.

СУБД Oracle издавна поддерживает единственный тип данных для представле­ния даты и времени, который называется DATE (заметим, что начиная с Oracle 9i в нем поддерживаются стандартные типы SQL datetime и timestamp). Как и тип данных datetime в SQL Server, тип данных DATE в Oracle фактически соответству­ет типу данных TIMESTAMP из DB2. Как и в SQL Server, временная часть значения типа DATE по умолчанию принимается равной полуночи. Формат даты, принятый в Oracle по умолчанию, отличается от форматов, принятых в DB2 и SQL Server, по­этому версия запроса для Oracle имеет следующий вид.

 

SELECT NAME, HIRE_DATE
   FROM SALESREPS
WHERE HIRE_DATE >= '14-JUN-07';

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

 

SELECT NAME, HIRE_DATE
    FROM SALESREPS
WHERE HIRE_DATE >= '30-MAY-07' + 15;

Заметим, однако, что эта инструкция требует от СУБД неявного преобразова­ния строки в соответствующий тип данных, прежде чем будет выполнено сумми­рование, и что не все реализации SQL поддерживают такое преобразование. На­пример, Oracle сообщит об ошибке, если перед выполнением арифметических действий не будет применена функция TO_DATE или CAST, преобразующая сим­вольную строку в тип данных Oracle DATE или datetime.

К счастью, в связи с наступлением 2000 года большинство производителей СУБД добавили универсальную поддержку дат в инструкциях SQL с годом из че­тырех цифр в стандартном формате yyyy-mm-DD, который мы и будем использовать в большинстве примеров в моем блоге. В случае Oracle формат по умолча­нию остается тем же, что и в приведенных ранее примерах, но его можно изме­нить одной командой в сессии базы данных или пользователя. Если вы работаете с Oracle и хотите выполнить пример из моего блога, просто введите приведен­ную команду для изменения формата даты по умолчанию.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';

При формировании запроса поиска точной даты с применением оператора ра­венства (=) следует быть осторожным, поскольку соответствующие данные хранят еще и время суток. Рассмотрим следующий пример.

 

SELECT NAME, HIRE_DATE
   FROM SALESREPS
WHERE HIRE_DATE = '06/14/2007';

Если информация о дате приема служащего на работу была сохранена в базе данных в полдень 14 июня 2007 года, то строка, содержащая сведения об этом че­ловеке, не попадет в результаты запроса в случае баз данных Oracle или SQL Server. СУБД полагает, что к строке даты в инструкции SQL следует добавить вре­мя, соответствующее полночи, а так как полдень и полночь — "две большие раз­ницы", данная строка не будет отобрана. С другой стороны, она будет отобрана в DB2 при хранении данных в формате DATE.

Наконец, начиная с SQL2 в стандарт ANSI/ISO был введен набор типов данных для работы с датой и временем, основанных на рассмотренных типах данных из DB2, но не идентичных им. В дополнение к типам данных DATE, TIME и TIMESTAMP стандарт определяет тип данных INTERVAL, предназначенный для хранения зна­чений интервалов времени (например, продолжительность какого-то процесса, измеренная в днях, часах, минутах и секундах). В стандарте определены тщатель­но продуманные сложные методы для выполнения арифметических операций над значениями даты и времени, принципы задания точности вычисления интервалов времени, учета разницы между часовыми поясами и т.д. Большинство реализаций SQL в настоящее время поддерживает эти стандартные типы данных. Одно важное исключение состоит в том, что SQL Server давно использует тип данных timestamp для совершенно иной цели, так что поддержка спецификации ANSI/ISO в настоящее время весьма сомнительна.

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

Эти отличия могут даже привести к тому, что, выполнив один и тот же запрос в различных СУБД, можно получить немного отличающиеся результаты. Таким образом, повсеместно восхваляемая переносимость SQL существует только в са­мом общем смысле. Приложение и в самом деле можно перенести с одной СУБД на другую, и оно может быть высокопереносимо только при использовании ос­новных, широко распространенных, возможностей SQL. Однако небольшие отли­чия в реализациях SQL приводят к тому, что типы данных и инструкции SQL при переносе почти всегда приходится несколько видоизменять. Чем сложнее прило­жение, тем вероятнее его зависимость от возможностей и нюансов конкретной СУБД и тем менее оно переносимо.

Вас заинтересует / Intresting for you:

Назначение языка SQL и необход...
Назначение языка SQL и необход... 1681 просмотров Ирина Светлова Mon, 28 Oct 2019, 05:40:06
Правила именование объектов SQ...
Правила именование объектов SQ... 115 просмотров Дэн Sat, 05 Jun 2021, 09:02:07
Операторы SQL для работы с баз...
Операторы SQL для работы с баз... 8734 просмотров sepia Mon, 16 Apr 2018, 13:56:44
Операции SQL в базе данных Ora...
Операции SQL в базе данных Ora... 4186 просмотров Antoni Wed, 11 Apr 2018, 12:22:28
Войдите чтобы комментировать