В стандарте 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 давно поддерживает представление дат и времени при помощи трех различных типов данных.
| Хранит дату наподобие "June 30,2008" |
| Хранит время суток наподобие "12:30:00 р.m." |
| Представляет конкретный момент времени с точностью до наносекунд |
Значения даты и времени можно представлять в виде строковых констант. Кроме того, поддерживаются арифметические операции над значениями даты. Ниже приведен пример с использованием дат 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 при переносе почти всегда приходится несколько видоизменять. Чем сложнее приложение, тем вероятнее его зависимость от возможностей и нюансов конкретной СУБД и тем менее оно переносимо.