SQL Типы данных для различных блоков данных
Типы данных и диапазоны для Microsoft Access, MySQL и SQL Server.
Типы Microsoft для доступа к данным
| Тип данных | Описание | Место хранения |
|---|---|---|
| Text | Используйте для текста или их комбинации текста и чисел. максимум 255 символов | |
| Memo | Memo используется для больших объемов текста. Сохраняет до 65536 символов. Note: Вы не можете сортировать поле МЕМО. Тем не менее, они будут доступны для поиска | |
| Byte | Позволяет целые числа от 0 до 255 | 1 байт |
| Integer | Позволяет целые числа между 32768 и 32767 | 2 байта |
| Long | Позволяет целые числа между -2147483648 и 2147483647 | 4 байта |
| Single | Одинарной точности с плавающей запятой. Будет ли работать с большинством знаков после запятой | 4 байта |
| Double | Двойной точности с плавающей запятой. Будет ли работать с большинством знаков после запятой | 8 байт |
| Currency | Используйте для валюты. Вмещает до 15 цифр целых долларов, плюс 4 знаков после запятой. Tip: Вы можете выбрать какой страны валюту использовать | 8 байт |
| AutoNumber | Autonumber поля автоматически дают каждой записи свой собственный номер, как правило, начиная с 1 | 4 байта |
| Date/Time | Используйте для даты и времени | 8 байт |
| Yes/No | Логическое поле может отображаться как Да / Нет, True / False, или вкл / выкл. В коде, используйте константы истинные и ложные (эквивалент -1 и 0). Note: Нулевые значения не допускаются в Да / Нет полей | 1 бит |
| Ole Object | Можно хранить фотографии, аудио, видео или другие двоичные объекты (больших двоичных объектов) | до 1 Гб |
| Hyperlink | Содержать ссылки на другие файлы, в том числе веб-страниц | |
| Lookup Wizard | Пусть вам ввести список опций, которые затем могут быть выбраны из выпадающего списка | 4 байта |
Типы данных MySQL
В MySQL есть три основных типа: текст, номер и дата типов / времени.
| Тип данных | Описание |
|---|---|
| CHAR(size) | Содержит строку с фиксированной длиной (могут содержать буквы, цифры и специальные символы). Фиксированный размер указан в скобках. Можно хранить до 255 символов |
| VARCHAR(size) | Имеет переменную длину строки (может содержать буквы, цифры и специальные символы). Максимальный размер указан в скобках. Можно хранить до 255 символов. Note: Если поставить большее значение , чем 255 он будет преобразован к типу TEXT |
| TINYTEXT | Содержит строку с максимальной длиной 255 символов |
| TEXT | Содержит строку с максимальной длиной 65535 символов |
| BLOB | Для Blobs (больших двоичных объектов). Вмещает до 65535 байт данных |
| MEDIUMTEXT | Содержит строку с максимальной длиной 16,777,215 символов |
| MEDIUMBLOB | Для Blobs (больших двоичных объектов). Вмещает до 16,777,215 байт данных |
| LONGTEXT | Содержит строку с максимальной длиной 4294967295 символов |
| LONGBLOB | Для Blobs (больших двоичных объектов). Вмещает до 4,294,967,295 байт данных |
| ENUM(x,y,z,etc.) | Пусть вам ввести список возможных значений. Вы можете перечислить до 65535 значений в списке ENUM. Если значение вставляется, что не в списке, то пустое значение будет вставлена. |
Note: Значения сортируются в порядке их ввода.
Вы вводите возможные значения в этом формате: ENUM ( 'X', 'Y', 'Z')
Number types:
| Тип данных | Описание |
|---|---|
| TINYINT(size) | -128 До 127 нормально. От 0 до 255 UNSIGNED *. Максимальное количество цифр, который может быть указан в скобках |
| SMALLINT(size) | -32768 До +32767 нормально. От 0 до 65535 UNSIGNED *. Максимальное количество цифр, который может быть указан в скобках |
| MEDIUMINT(size) | -8388608 До 8388607 нормально. От 0 до 16777215 UNSIGNED *. Максимальное количество цифр, который может быть указан в скобках |
| INT(size) | -2147483648 До 2147483647 нормально. От 0 до 4294967295 UNSIGNED *. Максимальное количество цифр, который может быть указан в скобках |
| BIGINT(size) | -9223372036854775808 До 9223372036854775807 нормально. От 0 до 18446744073709551615 UNSIGNED *. Максимальное количество цифр, который может быть указан в скобках |
| FLOAT(size,d) | Небольшое число с плавающей запятой. Максимальное количество цифр, который может быть указан в параметре размера. Максимальное количество цифр справа от десятичной точки задается в параметре г |
| DOUBLE(size,d) | Большое число с плавающей запятой. Максимальное количество цифр, который может быть указан в параметре размера. Максимальное количество цифр справа от десятичной точки задается в параметре г |
| DECIMAL(size,d) | ДВОЙНОЙ хранится в виде строки, что позволяет фиксированной запятой. Максимальное количество цифр, который может быть указан в параметре размера. Максимальное количество цифр справа от десятичной точки задается в параметре г |
* Типы целочисленных имеют дополнительный вариант называется UNSIGNED. Как правило, число идет от отрицательного к положительному значению. Добавление UNSIGNED атрибута будет двигаться этот диапазон таким образом он начинается с нуля вместо отрицательного числа.
Note: Поддерживаемый диапазон от '1000-01-01' до '9999-12-31'
Note: Поддерживаемый диапазон от '1000-01-01 00:00:00' до '9999-12-31 23:59:59'
Note: Поддерживаемый диапазон от '1970-01-01 00:00:01' UTC к '2038-01-09 3:14:07' UTC
Note: Поддерживаемый диапазон от '-838: 59: 59' до '838: 59: 59'
Note: Допустимые значения в формате четырех цифр: 1901 до 2155. Допустимые значения в формате двух цифр: от 70 до 69 лет , что составляет лет с 1970 по 2069
* Даже если DATETIME и TIMESTAMP возвращают один и тот же формат, что они работают очень по-разному. В INSERT или UPDATE запроса, то автоматически устанавливается ТШЕЗТАМР себя к текущей дате и времени. TIMESTAMP также принимает различные форматы, такие как YYYYMMDDHHMISS, YYMMDDHHMISS, YYYYMMDD или YYMMDD.
Типы данных SQL Server
String types:
| Тип данных | Описание | Место хранения |
|---|---|---|
| char(n) | Фиксированная ширина строка символов. Максимум 8000 символов | Определяется ширина |
| varchar(n) | Переменная строка ширина символа. Максимум 8000 символов | 2 байта + количество символов |
| varchar(max) | Переменная строка ширина символа. Максимум 1073741824 символов | 2 байта + количество символов |
| text | Переменная строка ширина символа. Максимальное 2GB текстовых данных | 4 байта + количество символов |
| nchar | Фиксированная ширина строки Unicode. Максимум 4000 символов | Определяется ширина х 2 |
| nvarchar | Переменная строка ширина Unicode. Максимум 4000 символов | |
| nvarchar(max) | Переменная строка ширина Unicode. Максимальное количество символов 536,870,912 | |
| ntext | Переменная строка ширина Unicode. Максимальное 2GB текстовых данных | |
| bit | Позволяет 0, 1 или NULL | |
| binary(n) | Фиксированная ширина двоичная строка. Максимум 8000 байтов | |
| varbinary | Переменная ширина двоичная строка. Максимум 8000 байтов | |
| varbinary(max) | Переменная ширина двоичная строка. Максимальная 2GB | |
| image | Переменная ширина двоичная строка. Максимальная 2GB |
Number types:
| Тип данных | Описание | Место хранения |
|---|---|---|
| tinyint | Позволяет целые числа от 0 до 255 | 1 байт |
| smallint | Позволяет целые числа между 32768 и 32767 | 2 байта |
| int | Позволяет целые числа между -2147483648 и 2147483647 | 4 байта |
| bigint | Позволяет целые числа между -9,223,372,036,854,775,808 и 9,223,372,036,854,775,807 | 8 байт |
| decimal(p,s) | Фиксированная точность и масштаб числа. |
Позволяет числа от -10 ^ 38 + 1 до 10 ^ 38 -1.
Параметр р указывает максимальное общее количество цифр, которые могут быть сохранены (как слева и справа от десятичной точки). р должно иметь значение от 1 до 38. По умолчанию 18.
Параметр s указывает максимальное количество цифр, сохраненных справа от десятичной точки. s должен иметь значение от 0 до р. Значение по умолчанию равно 0
Позволяет числа от -10 ^ 38 + 1 до 10 ^ 38 -1.
Параметр р указывает максимальное общее количество цифр, которые могут быть сохранены (как слева и справа от десятичной точки). р должно иметь значение от 1 до 38. По умолчанию 18.
Параметр s указывает максимальное количество цифр, сохраненных справа от десятичной точки. s должен иметь значение от 0 до р. Значение по умолчанию равно 0
П параметр указывает, следует ли поле держать 4 или 8 байт. с плавающей точкой (24) имеет поле 4 байта и с плавающей точкой (53) имеет поле 8 байт. По умолчанию значение п 53.
Подробно о типах данных
В следующих выпусках рубрики «T-SQL для начинающих» будет рассказано о применении синтаксиса T-SQL для построения таблиц. Но прежде чем начинать строить таблицы, необходимо поговорить о типах данных. Для каждого столбца таблицы должен быть определен некоторый тип данных, например, целые (integer) или символьные (character). Это позволяет системе в последующем контролировать данные, которые будут помещаться в этот столбец. В предлагаемой статье автор рассматривает предусмотренные в системе типы данных и рассказывает о том, как создавать собственные типы данных. Подобные дополнительные типы данных следует создать до того как начнется построение таблиц, ведь при создании таблиц могут понадобиться ссылки на эти типы данных. Определяемые пользователями типы данных позволят существенно усилить контроль над данными и повысить их целостность, особенно в проектах, в которых с базами данных параллельно работают несколько разработчиков.
Столбцам необходимы типы данных
При создании таблицы необходимо указать тип данных для каждого столбца. Любые данные, помещаемые в столбец, должны отвечать этому типу данных. В некоторых случаях следует указывать и допустимую длину данных в столбце. В листинге 1 приведен оператор SQL, который создает таблицу авторов Authors в базе данных публикаций Pubs, спользуя только предусмотренные в системе типы данных.
Большинство столбцов в этой таблице являются текстовыми полями. Номер телефона, обозначение штата и почтовый индекс всегда имеют постоянную длину, поэтому соответствующие им столбцы phone, state и ZIP удобнее объявить символьными (char). Имя автора, его адрес и город, где он живет, могут различаться по длине, поэтому для них предпочтительнее использовать тип символьных данных переменной длины (varchar). Столбец contract с информацией о наличии контракта с автором, в котором данные представляются в форме да/нет, целесообразно отнести к двоичному типу данных bit, у которого имеется два возможных значения — 0 и 1. Теперь остановимся подробнее на системных типах данных.
Типы данных, предусмотренные в системе
В версии SQL Server 6.0 определено 19 типов данных, а в версии SQL Server 7.0 добавлено еще 4 типа для удобства работы с закодированными с помощью Unicode данными, а также для поддержки приложений, основанных на использовании хранилищ данных. Каждый тип данных имеет ряд разновидностей, отличающихся возможным набором значений. Знание этих значений облегчит выбор подходящего типа данных.
Символьные данные
Символьные данные типа char предсавляют собой один из наиболее распространенных типов данных. К этому типу относятся такие символьные данные, как имена или адреса. В версии SQL Server 6.5 длина любого столбца с символьными данными ограничивалась 255 знаками. Если же информация оказывалась длиннее, к примеру, если в столбец предполагалось вводить свободный комментарий по поводу контракта, то тогда следовало использовать текстовый тип данных (text). В SQL Server 7.0 это ограничение отменено, так что символьное поле может содержать до 8000 байтов. Верхняя граница обусловлена размером страницы памяти, поскольку ни одна запись не может располагаться в памяти на нескольких страницах. В тех случаях, когда для столбца определен тип данных char, данные следует помещать в одинарных кавычках, как показано в следующем примере:
Как отмечалось в статье «Работа с символьными данными», помещенной в майском номере журнала, можно выбрать символьный тип данных либо фиксированной длины, char, либо переменной длины, varchar. Фиксированный размер оказывается предпочтительным в тех случаях, когда данные имеют одинаковую или сходную длину, например, при вводе идентификатора автора (часто в этом качестве используют индивидуальный номер системы социальной безопасности, что, кстати, является плохим примером для подражания). В большинстве ситуаций применение переменной длины данных не приводит к сколько-нибудь заметному увеличению времени обработки. В то же время фамилия автора может быть очень длинной, так что использование типа varchar оказывается вполне оправданным. Применительно к подавляющему большинству фамилий фиксированная длина поля означает потерю значительного объема памяти, поэтому лучше использовать тип данных varchar. При выборе того или иного типа данных всегда следует искать компромисс с учетом двух аспектов: с одной стороны, потери полезного объема памяти при использовании данных фиксированной длины, а с другой стороны, увеличения времени обработки в случае применения данных переменной длины.
SQL Server 7.0 поддерживает набор символов Unicode. В связи с этим, чтобы воспользоваться всеми преимуществами, предоставляемыми расширенными возможностями Unicode, необходимо было ввести дополнительный тип данных.Если вам захочется использовать символьные данные Unicode, то следует указать тип данных Nchar или, если это информация переменной длины, то Nvarchar. При вводе данных Unicode их следует заключать в одиночные кавычки, причем непосредственно перед ними необходимо поставить заглавную латинскую букву N. Если в рассмотренном ранее примере имя автора отнесено к типу данных Unicode, то предыдущий оператор обновления примет следующий вид:
Ограничение максимальной длины информации при работе с типом данных Unicode составляет 4000 знаков. Это объясняется тем, что для хранения каждого символа Unicode требуется два байта памяти. Поэтому на стандартную страницу памяти размером 8К можно поместить в два раза меньше символов Unicode, чем при использовании обычных символов.
Целые
Базовый тип целых чисел integer охватывает диапазон от -2 147 483 638 до 2 147 483 647. Уменьшенные целые smallint включают числа от -32 768 до 32767. Зачастую, когда точно известно, что диапазон возможных числовых значений данных невелик, лучше применять тип данных smallint. К примеру, в базе данных личной коллекции компакт-дисков при выборе типа данных для первичного ключа целесообразно использовать тип данных smallint. Ведь предположение о том, что число компакт-дисков в такой коллекции превысит 32 676 единиц, кажется неправдоподобным. В этой ситуации не следует для оптимизации памяти использовать тип данных tinyint, поскольку он позволяет обрабатывать только значения от 0 до 255. Такой тип данных можно было бы применить для нумерации депозитных сертификатов, а для коллекции компакт-дисков он может оказаться недостаточным.
Точные числовые данные
Если приложение таково, что необходимо получать из базы данных числовое значение в строго указанном формате, то для этого следует выбрать один из точных числовых типов данных. Существуют два точных числовых типа данных: десятичный (decimal) и числовой (numeric), которые по существу совпадают друг с другом. Для них можно задать требуемые точность p и масштаб s в формате decimal (p,s). Точность представляет собой число значащих символов по обе стороны от десятичной запятой, а масштаб — количество символов справа от нее. Например, число 123,4567 можно хранить в столбце, для которого тип данных задан в виде (7,4). Если число, которое должно быть помещено в столбец с точным числовым типом данных, содержит больше десятичных знаков, чем указано в типе данных, то такое число округляется до требуемой точности. Числовые типы данных могут использовать для хранения значений от 2 до 17 байтов.
Приближенные числовые данные
Некоторые числа нельзя точно представить в десятичном виде с ограниченным числом знаков, например, одну треть или число пи. Для записи таких чисел используются действительный (real) или плавающий (float) типы данных. Данные действительного типа хранятся с точностью от 1 до 7 знаков. Плавающий формат, который иногда называют еще форматом двойной точности, может хранить числа, содержащие от 8 до 15 значащих цифр. Действительный и плавающий типы данных применяются в научных приложениях для хранения чисел, не требующих точного двоичного выражения. Одна-две последние цифры могут не вполне точно сохраняться при преобразованиях в двоичный формат. Поэтому такие числа не следует использовать в операциях точного сравнения, применяемых для формулирования условий оборота WHERE.
Двоичные
Предположим, что в базе данных необходимо хранить двоичную информацию. В этой ситуации имеется выбор между двумя форматами представления: c фиксированной или переменной длиной. Данным фиксированной длины соответствует тип данных binary, а двоичным данным переменной длины соответствует тип данных varbinary.
Денежные
Для представления денежных значений используются два типа данных: денежный (money) и малый денежный (smallmoney). Тип данных денежный применяется для хранения значений от плюс до минус 922 триллионов. Большинство из нас вполне может пользоваться малым денежным типом данных, который перекрывает диапазон значений от -214 748,3648 до +214 748,3647. По существующей договоренности денежный тип данных при хранении имеет четыре десятичных знака после запятой, а для его представления пользователям требуется только два знака . Отметим, что многие финансовые транзакции не используют денежный тип данных. Например, в биржевыех торговых операциях применяются 1/32 доли, для хранения которых необходимо пять десятичных мест.
В SQL Server и дата и время хранятся в одном столбце, так что если с помощью функции GETDATE() запросить текущую дату, то при этом система сообщит и время. Для дат применяются два типа данных, datetime и smalldatetime. Тип данных smalldatetime охватывает период времени от 1 января 1900 года до 6 июня 2079 года и включает время с точностью до минуты. Такого диапазона достаточно для подавляющего большинства проектов. Тип данных datetime годен для использования до 31 декабря 9999 года (это следует учитывать при решении проблемы 10К года). Начало диапазона этого типа данных датируется 1 января 1753 года. Почему 1753? Это связано с переходом с юлианского на грегорианский календарь. Несмотря на то, что грегорианский календарь был предложен некоторое время назад, процесс его принятия продолжался в течение приблизительно 30 лет. На протяжении этого периода некоторые страны уже приняли грегорианский, а другие еще нет. Поэтому для того, чтобы дата воспринималась однозначно, надо знать географическое положение объекта. Кроме того, год начинался не 1 января, а 1 марта. Поэтому такая дата, как 15 января 1792 года, может интерпретироваться и как середина первого месяца 1492 года, и как середина 11-го месяца 1793 года. Создатели SQL Server решили не рисковать, и поэтому приняли решение не воспринимать даты, относящиеся к периоду времени до начала 1753 года. Следует отметить также, что тип данных datetime показывает тысячные доли секунды, хотя точность гарантируется только до 1/300 части секунды.
Тексты и образы
Для символьных данных длиннее, чем 255 знаков, в SQL Server 6.5 следует применять тип данных текст (text). В SQL Server 7.0 граница применимости этого типа данных отодвигается до 8000 знаков. Для больших двоичных объектов (BLOB), таких как цифровые образы, используется тип данных образ (image). ?анные типа текст или образ не хранятся в строках, поэтому к ним не применимо ограничение на размер страницы памяти. В строках хранятся лишь указатели на страницы базы данных, в которых находится информация. Для обновления этих типов данных необходимы специальные процедуры, которые выходят за рамки рассмотрения настоящей статьи. (Более подробно об этом написано в статье Майкла Оути «Нам не страшен огромный BLOB», опубликованной в апрельском номере журнала.) Здесь необходимо отметить только то, что изменения текстовых данных или образов не фиксируются в журнале, а указатели не обновляются. Изменяются только сами поля, содержащие текст или образ.
Специальные типы данных
Некоторые типы данных трудно отнести к какой-либо категории. Один из таких типов — битовые данные (bit). Это целое число, которое может принимать только два значения — 0 и 1 (в одном байте можно хранить восемь подобных величин). Битовые значения часто применяются в качестве флагов, принимающих значения истина или ложь. Их можно использовать, например, для хранения сведений о том, заключен ли контракт с автором, или принадлежат ли его книги к бестселлерам, или для чего-либо аналогичного. Одно незначительнгое отличие версии SQL Server 7.0 состоит в том, что для столбцов с битовыми данными теперь разрешены и неопределенные значения, в то время как в версии SQL Server 6.5 допустимы были только значения 0 и 1.
Для внутренних целей в SQL Server используется тип данных метка времени (timestamp). Этот тип данных генерирует уникальное значение, которое обновляется каждый раз, когда модифицируется информация в строке таблицы. Метки времени являются внутренними значениями, поддерживаемыми в SQL Server. Они не соответствуют реальным датам и времени.
В SQL Server 7.0 введены два новых типа данных, которые более подробно будут рассмотрены в последующих публикациях. тип данных уникальный идентификатор (uniqueidentifier) позволяет присвоить столбцу уникальное в глобальном масштабе значение. Глобальная уникальность означает неповторимость не только в рамках конкретной базы данных или в пределах одного компьютера, но вообще везде. Этот тип данных играет важную роль при работе с хранилищами данных, когда информация собирается в него из множества разнообразных источников. Тип данных курсор (cursor) применяется для переменных курсора. Использование курсора — отдельная большая тема, которой автор собирается посвятить целую статью.
Определяемые пользователями типы данных
SQL Server позволяет пользователям определять собственные типы данных, которые являются комбинацией системных типов данных. Как правило, они базируются на системном типе данных определенной длины (имеются в виду числовые и символьные значения), для которого назначаются правила и значения по умолчанию. Например, можно определить тип данных Почтовый индекс как char(10), а тип данных Телефонный номер как char(14). Тогда тип данных Почтовый индекс будет гарантировать целостность форматов почтовых индексов во всех столбцах, для любого клиента, поставщика, служащего или контактного лица где угодно по всему миру.
Но в тех случаях, когда пользователь хочет создать тип данных, имея лишь набросок желаемого, SQL Server оказывается не слишком полезным. К примеру, нельзя создать новый тип данных, обладающий целым набором связанных с ним свойств, для таких понятий как широта или долгота для географических приложений. Ведь для него пришлось бы прибегнуть к методам сферической тригонометрии для вычисления расстояния между двумя точками. Возможно, когда-нибудь SQL Server станет настолько гибким, что сможет помочь и в таких ситуациях.
На данный момент добавлять новые типы данных можно с помощью SQL Server Enterprise Manager или из анализатора запросов Query Analyzer (в версии SQL Server 6.5 это производится из окна ISQL/w). Каждый тип данных, который добавляют пользователи, действует только в пределах конкретной базы данных. Если же необходимо создать тип данных для всех баз, то его следует поместить в базу данных моделей Model. С момента создания в этой базе данных новый тип данных будет доступен во всех остальных базах. Существует другой способ решения этой проблемы — написать сценарий или сгенерировать его с помощью утилиты генерации сценариев Generate SQL Script, входящей в состав SQL Server. Тогда этот сценарий можно будет запускать из любой базы данных. В Enterprise Manager следует дважды щелкнуть правой кнопкой мыши на названии базы данных, а затем последовательно выбрать из меню Все задачи (All tasks), Генерировать сценарий SQL (Generate SQL Script) и наконец, выбрать пункт создания сценария для всех типов данных.
В окне Enterprise Manager следует выбрать пункт Базы данных (Databases), выбрать конкретную базу из списка, а затем перейти к пункту Определяемые пользователем типы данных (User-Defined Data Types). Щелкните правой кнопкой мыши и выберите пункт Свойства новых определяемых пользователем типов данных (New User-defined Datatype Properties). После этого приступайте к определению типа данных.
Как принято в SQL Server, добавить новый тип данных можно также с помощью анализатора запросов Query Analyzer. Обратите внимание на то, что кавычки заключают название системного типа данных, но вокруг названия вновь создаваемого типа данных их не ставят. Кроме того, полезно сразу указать, допускает ли создаваемый тип данных неопределенные значения. Для конкретного столбца можно будет в последующем переопределить эту установку, но первоначальная спецификация допустимых возможностей облегчает введение стандартов. А это и является той целью, ради которой вводятся новые типы данных.
При определении типов данных оператор создания таблицы может выглядеть так, как показано на листинге 2. Для тех столбцов, к которым применяются определенные пользователем типы данных, не требуется указывать длину помещаемых в него сведений, — ведь она уже была определена при создании типа данных. Можно также не сообщать системе о том, допустимы ли в рассматриваемом столбце неопределенные значения, поскольку это задано в спецификации типа данных. Хотя не повредит включить такое упоминание и в этот оператор.
Выберите правильный тип
Правильный подбор типов данных является частью проектирования таблицы. В SQL Server 6.5 очень трудно изменить свойства столбца после того, как он был создан. В версии SQL Server 7.0 это возможно, но проводить такую процедуру следует с крайней осторожностью.
Введение определяемых пользователем типов данных является одним из способов обеспечения целостности информации в различных приложениях, над которыми трудятся разные команды разработчиков. При этом предполагается , что все они используют SQL Server. Однако при перенесении кода SQL в другие СУБД целесообразно избегать применения определяемых пользователем типов данных.
Типы данных в MS SQL. Transact-SQL
Согласно процедурному расширению языка SQL (Transact-SQL), для работы с MS SQL Server предусмотрены различные типы данных. Конкретный тип данных указывается для любого столбца во время создания таблицы и определяет, какие именно значения можно будет хранить, а также сколько места в памяти компьютера они будут занимать. Эта статья представляет собой обзор существующих типов данных для MS SQL Server, которые, в зависимости от поддерживаемых значений, делятся на группы.
Несколько слов о T-SQL
Transact-SQL является процедурным расширением SQL (structured query language), которое создано корпорацией Microsoft специально для баз данных Microsoft SQL Server и Sybase ASE. Известный язык программирования SQL был расширен рядом дополнительных возможностей:
- управляющими операторами,
- локальными и глобальными переменными,
- вспомогательными функциями для обработки строк, дат и пр.,
- поддержкой аутентификации Microsoft Windows.
Сегодня язык Transact-SQL — это, по сути, ключ к применению MS SQL Server. Таким образом, все программные приложения, взаимодействующие с экземпляром MS SQL Server, вне зависимости от реализации и UI, выполняют отправку серверу инструкций Transact-SQL.
Директивами сценария являются специфические команды, используемые лишь в MS SQL. Такие команды позволяют серверу определять правила работы с транзакциями и скриптом.
Типы данных (data types)
Как это принято и в других языках программирования, язык T-SQL поддерживает множество разных типов данных, используемых для хранения переменных в MS SQL. Эти типы данных можно разделить на группы:
- числовые. Служат для хранения числовых переменных;
- типы данных для дат. Обеспечивают хранение значений времени и даты;
- символьный. Нужен для символьных типов данных;
- двоичные. Обеспечивают хранение бинарных типов данных;
- большого объема. Этот тип данных (data type) нужен для хранения больших бинарных значений;
- специальные. Сюда входит широкий спектр типов данных: указатели, таблицы и пр.
Давайте рассмотрим некоторые из вышеописанных типов данных подробнее.
Числовые типы данных
К числовым типам данных относят:
- BIT: может храниться 0 либо 1. По сути, это аналог булевого типа в программировании. Занимает всего один байт;
- TINYINT: хранит целые числа в пределах 0-255. Тоже занимает 1 байт и часто используется в целях хранения небольших чисел;
- SMALLINT: сохраняет числа в пределах от –32 768 до 32 767. Занимает уже 2 байта;
- INT: диапазон хранения — от –2 147 483 648 до 2 147 483 647. 4 байта. Тип данных int чаще всего применяется для сохранения числовых значений;
- BIGINT: предназначен для очень больших значений (от -9 223 372 036 854 775 808 до 9 223 372 036 854 775 807) и занимает в памяти 8 байт;
- DECIMAL: для хранения переменных c фиксированной точностью. В зависимости от количества чисел после запятой переменная типа Decimal может занимать от 5 до 17 байт.
Также этот тип данных способен принимать 2 параметра — scale и precision: DECIMAL(precision, scale).
Параметр precision служит для представления наибольшего количества цифр, способных хранить числовое значение. По дефолту оно равняется 18-ти, а вообще должно располагаться в диапазоне 1-38.
Параметр scale служит для представления максимального количества цифр, которые способно содержать число после запятой. Такое значение должно быть в диапазоне от 0 до значения, которое указывается параметром precision. По умолчанию равно нулю;
- NUMERIC: этот тип данных аналогичен предыдущему;
- SMALLMONEY: сохраняет дробные значения в пределах от -214 748.3648 до 214 748.3647. Служит для хранения денежных величин. Эквивалентен DECIMAL(10,4), занимает 4 байта;
- MONEY: сохраняет дробные значения в диапазоне от -922 337 203 685 477.5808 до 922 337 203 685 477.5807. Служит для представления денежных величин, занимает 8 байт. Соответствует DECIMAL(19,4).
- FLOAT: для хранения значений от –1.79E+308 до 1.79E+308. В зависимости от дробной части может занимать от 4 до 8 байт.
Этот тип данных способен иметь форму определения в формате FLOAT(n), где n — это количество бит, используемых в целях хранения десятичной части числа (речь идет о мантиссе). По дефолту n равняется 53;
- REAL: сохраняет числа в диапазоне от –340E+38 to 3.40E+38. Соответствует FLOAT(24), занимает 4 байта.
Рассмотрим пример числового столбца с таким типом данных:
Тип данных для времени
Существуют типы данных, предназначенных для отображения даты и времени:
- DATE: сохраняет даты, начиная с 1 января 0001 года (0001-01-01), заканчивая 31 декабрём 9999 г. (9999-12-31). Занимает 3 байта;
- TIME: сохраняет время в диапазоне 00:00:00.0000000 — 23:59:59.9999999. Способен иметь форму TIME(n), причем n здесь представляет число цифр от 0 до 7 в дробной части секунд. Занимает 3-5 байт;
- DATETIME: обеспечивает сохранение объектов даты и времени в диапазоне 01/01/1753 — 31/12/9999. 8 байт;
- DATETIME2: сохраняет дату и время в диапазоне 01/01/0001 00:00:00.0000000 — 31/12/9999 23:59:59.9999999. Может занимать 6-8 байт, что зависит от временной точности. Способен иметь форму DATETIME2(n), причем n здесь — это число цифр в пределах 0-7 в дробной части секунд;
- SMALLDATETIME: сохраняет даты и время в пределах 01/01/1900-06/06/2079, то есть речь идет о ближайших датах. 4 байта;
- DATETIMEOFFSET: для дат и времени в диапазоне от 0001-01-01 до 9999-12-31. Обеспечивается сохранение детальной информации о времени, точность составляет 100 наносекунд. 10 байт;
Наиболее распространены следующие форматы дат:
В этом формате 2-значные числа в пределах 00-49 воспринимаются в качестве дат диапазона 2000-2049. А числовые значения 50-99 воспринимаются в качестве диапазона 1950-1999.
Распространены следующие временные форматы:
Строковый тип данных
Какие строковые типы данных бывают:
- CHAR: для строк длиной 1-8000 символов. На каждый символ выделяется 1 байт. Этот тип данных не подходит для многих языков, а все потому, что он сохраняет символы не в кодировке Unicode.
Число символов, которое способен хранить столбец, передается с помощью скобок. К примеру, для столбца CHAR(10) выделится 10 байт. Когда мы сохраняем в столбце строку размером меньше десяти символов, она дополнится пробелами;
- VARCHAR: может хранить строки, причем здесь тоже на каждый символ происходит выделение 1 байта. Для столбца можно указать определенную длину в диапазоне 1-8 000 символов, к примеру, VARCHAR(10). Когда строка должна иметь более 8000 символов, задается размер MAX, причем на хранение строки может выделять до 2 Гб: VARCHAR(MAX).
Этот тип данных также не подходит для многих языков и все по той же причине: хранит символы не в Unicode;
- NCHAR: для строки с кодировкой Unicode и длиной 1-4 000 символов. На каждый символ — 2 байта;
- NVARCHAR: сохраняет строку в Unicode. На каждый символ — 2 байта. Есть возможность задать конкретный размер в пределах 1-4 000 символов. Когда строка должна включать более 4000 символов, задается размер MAX, а на хранение строки выделяется до 2 Гб.
Также существуют такие типы данных, как TEXT и NTEXT. Они считаются устаревшими, поэтому их лучше не использовать. Вместо только что упомянутых типов данных применяют VARCHAR и NVARCHAR.
Рассмотрим примеры определения строковых столбцов:
О бинарных типах данных
Пришла очередь и двоичным данным:
- BINARY: для сохранения бинарных типов данных в качестве последовательности в пределах 1-8 000 байт;
- VARBINARY: для хранения бинарных типов данных в качестве последовательности в диапазоне 1-8 000 байт, либо до 2^31–1 байт в случае применения значения MAX (VARBINARY(MAX)).
Такой бинарный тип, как IMAGE, тоже устарел, поэтому вместо него желательно использовать тип данных VARBINARY.
Специальные типы данных
В этой группе перечислим оставшиеся типы данных:
- UNIQUEIDENTIFIER: уникальный идентификатор GUID (строка с уникальным значением), занимающий 16 байт;
- TIMESTAMP: некоторое число, хранящее номер версии строки в таблице. Может занимать 8 байт;
- CURSOR: для набора строк;
- HIERARCHYID: для представления позиции в иерархии;
- SQL_VARIANT: для хранения значений практически любого другого типа данных T-SQL;
- XML: для хранения документов XML либо фрагментов XML-документов. Занимает до двух гигабайт;
- TABLE: представляет определение таблицы;
- GEOGRAPHY: обеспечивает хранение географических данных (широты, долготы);
- GEOMETRY: для координат местонахождения на плоскости.
Примечание: для работы с русскими символами тип данных применяют, используя приставку «n» (nchar, nvarchar, ntext). Такие приставки кодируют символы 2-мя байтами. Если говорить иными словами, то для работы с кодировкой Unicode используют тип данных с «n» (вспоминаем слово national). Так же записываются и строковые константы.
Когда речь идет о типе данных для значения переменной длины, задействуют тип данных, имеющий приставку «var» (character varying). Тип данных без приставки «var» характеризуется фиксированной длиной области памяти, причем неиспользованная часть заполняется пробелами либо нулями.
PostgreSQL – тип данных
В этой главе мы обсудим типы данных, используемые в PostgreSQL. При создании таблицы для каждого столбца вы указываете тип данных, т. Е. Какой тип данных вы хотите хранить в полях таблицы.
Это дает несколько преимуществ –
Согласованность. Операции со столбцами одного типа данных дают согласованные результаты и обычно являются самыми быстрыми.
Валидация – правильное использование типов данных подразумевает проверку формата данных и отклонение данных вне области действия типа данных.
Компактность – поскольку столбец может хранить значения одного типа, он хранится в компактном виде.
Производительность – правильное использование типов данных обеспечивает наиболее эффективное хранение данных. Сохраненные значения можно быстро обработать, что повышает производительность.
Согласованность. Операции со столбцами одного типа данных дают согласованные результаты и обычно являются самыми быстрыми.
Валидация – правильное использование типов данных подразумевает проверку формата данных и отклонение данных вне области действия типа данных.
Компактность – поскольку столбец может хранить значения одного типа, он хранится в компактном виде.
Производительность – правильное использование типов данных обеспечивает наиболее эффективное хранение данных. Сохраненные значения можно быстро обработать, что повышает производительность.
PostgreSQL поддерживает широкий набор типов данных. Кроме того, пользователи могут создавать свои собственные типы данных с помощью команды CREATE TYPE SQL. В PostgreSQL существуют разные категории типов данных. Они обсуждаются ниже.
Числовые Типы
Числовые типы состоят из двухбайтовых, четырехбайтовых и восьмибайтовых целых чисел, четырехбайтовых и восьмибайтовых чисел с плавающей запятой и десятичных дробей с выбираемой точностью. В следующей таблице перечислены доступные типы.
| название | Размер хранилища | Описание | Спектр |
|---|---|---|---|
| SMALLINT | 2 байта | целое число малого диапазона | От -32768 до +32767 |
| целое число | 4 байта | типичный выбор для целого числа | От -2147483648 до +2147483647 |
| BIGINT | 8 байт | большое целое число | От -9223372036854775808 до 9223372036854775807 |
| десятичный | переменная | указанная пользователем точность, точная | до 131072 цифр перед десятичной точкой; до 16383 знаков после запятой |
| числовой | переменная | указанная пользователем точность, точная | до 131072 цифр перед десятичной точкой; до 16383 знаков после запятой |
| реальный | 4 байта | переменная точность, неточная | Точность 6 десятичных цифр |
| двойная точность | 8 байт | переменная точность, неточная | Точность 15 десятичных цифр |
| smallserial | 2 байта | небольшое автоинкрементное целое число | От 1 до 32767 |
| последовательный | 4 байта | автоинкрементное целое число | 1 до 2147483647 |
| bigserial | 8 байт | большое автоинкрементное целое число | 1 до 9223372036854775807 |
Денежные Типы
Тип money хранит сумму в валюте с фиксированной дробной точностью. Значения типов данных numeric, int и bigint могут быть приведены к деньгам . Использование чисел с плавающей точкой не рекомендуется для обработки денег из-за возможной ошибки округления.
| название | Размер хранилища | Описание | Спектр |
|---|---|---|---|
| Деньги | 8 байт | сумма в валюте | От -92233720368547758.08 до +92233720368547758.07 |
Типы персонажей
В приведенной ниже таблице перечислены типы символов общего назначения, доступные в PostgreSQL.
различные символы (n), varchar (n)
переменная длина с ограничением
символ (n), символ (n)
фиксированная длина, с подкладкой
переменная неограниченная длина
различные символы (n), varchar (n)
переменная длина с ограничением
символ (n), символ (n)
фиксированная длина, с подкладкой
переменная неограниченная длина
Двоичные типы данных
Тип данных bytea позволяет хранить двоичные строки, как показано в таблице ниже.
| название | Размер хранилища | Описание |
|---|---|---|
| BYTEA | 1 или 4 байта плюс фактическая двоичная строка | двоичная строка переменной длины |
Типы даты / времени
PostgreSQL поддерживает полный набор типов даты и времени SQL, как показано в таблице ниже. Даты считаются по григорианскому календарю. Здесь все типы имеют разрешение 1 микросекунда / 14 цифр, кроме типа даты , разрешение которого – день .
| название | Размер хранилища | Описание | Низкое значение | Высокое значение |
|---|---|---|---|---|
| отметка времени [(p)] [без часового пояса] | 8 байт | дата и время (без часового пояса) | 4713 г. до н.э. | 294276 н.э. |
| TIMESTAMPTZ | 8 байт | дата и время с часовым поясом | 4713 г. до н.э. | 294276 н.э. |
| Дата | 4 байта | дата (без времени суток) | 4713 г. до н.э. | 5874897 н.э. |
| время [(p)] [без часового пояса] | 8 байт | время суток (без даты) | 00:00:00 | 24:00:00 |
| время [(p)] с часовым поясом | 12 байт | только время суток, с часовым поясом | 00: 00: 00 + 1459 | 24: 00: 00-1459 |
| интервал [поля] [(p)] | 12 байт | интервал времени | -178000000 лет | 178000000 лет |
Логический тип
PostgreSQL предоставляет стандартный тип SQL Boolean. Тип данных Boolean может иметь состояния true , false и третье состояние неизвестно , которое представлено нулевым значением SQL.
| название | Размер хранилища | Описание |
|---|---|---|
| логический | 1 байт | состояние истинного или ложного |
Перечислимый тип
Перечислимые (enum) типы – это типы данных, которые содержат статический упорядоченный набор значений. Они эквивалентны типам enum, поддерживаемым во многих языках программирования.
В отличие от других типов, перечисляемые типы необходимо создавать с помощью команды CREATE TYPE. Этот тип используется для хранения статического упорядоченного набора значений. Например, направления по компасу, например, СЕВЕР, ЮГ, ВОСТОК и ЗАПАД или дни недели, как показано ниже –
Перечисленные, однажды созданные, могут использоваться как любые другие типы.
Геометрический тип
Геометрические типы данных представляют собой двумерные пространственные объекты. Самый фундаментальный тип, точка, формирует основу для всех других типов.
| название | Размер хранилища | Представление | Описание |
|---|---|---|---|
| точка | 16 байт | Точка на плоскости | (Х, у) |
| линия | 32 байта | Бесконечная линия (не полностью реализована) | ((X1, y1), (х2, у2)) |
| LSEG | 32 байта | Конечный отрезок | ((X1, y1), (х2, у2)) |
| коробка | 32 байта | Прямоугольная коробка | ((X1, y1), (х2, у2)) |
| дорожка | 16 + 16n байт | Закрытый путь (похож на полигон) | ((X1, y1), …) |
| дорожка | 16 + 16n байт | Открытый путь | [(X1, y1), …] |
| многоугольник | 40 + 16n | Полигон (похож на замкнутый путь) | ((X1, y1), …) |
| круг | 24 байта | Круг | <(x, y), r> (центральная точка и радиус) |
Тип сетевого адреса
PostgreSQL предлагает типы данных для хранения IPv4, IPv6 и MAC-адресов. Лучше использовать эти типы вместо обычных текстовых типов для хранения сетевых адресов, потому что эти типы предлагают проверку ошибок ввода и специализированные операторы и функции.
| название | Размер хранилища | Описание |
|---|---|---|
| CIDR | 7 или 19 байт | Сети IPv4 и IPv6 |
| инет | 7 или 19 байт | IPv4 и IPv6 хосты и сети |
| MacAddr | 6 байт | MAC-адреса |
Тип битовой строки
Типы битовых строк используются для хранения битовых масок. Это либо 0, либо 1. Существует два типа битов SQL: bit (n) и bitinging (n) , где n – положительное целое число.
Тип текстового поиска
Этот тип поддерживает полнотекстовый поиск, который представляет собой поиск в наборе документов на естественном языке для поиска документов, которые лучше всего соответствуют запросу. Для этого есть два типа данных:
поисковый вектор
Это отсортированный список отдельных слов, которые были нормализованы для объединения различных вариантов одного и того же слова, называемые «лексемы».
Это хранит лексемы, которые нужно искать, и объединяет их, соблюдая логические операторы & (AND), | (Или) и! (НЕ). Круглые скобки могут быть использованы для принудительной группировки операторов.
поисковый вектор
Это отсортированный список отдельных слов, которые были нормализованы для объединения различных вариантов одного и того же слова, называемые «лексемы».
Это хранит лексемы, которые нужно искать, и объединяет их, соблюдая логические операторы & (AND), | (Или) и! (НЕ). Круглые скобки могут быть использованы для принудительной группировки операторов.
Тип UUID
UUID (универсальные уникальные идентификаторы) записывается в виде последовательности шестнадцатеричных цифр в нижнем регистре, в нескольких группах, разделенных дефисами, в частности, в группе из восьми цифр, за которой следуют три группы из четырех цифр, за которыми следует группа из 12 цифр, для всего 32 цифры, представляющие 128 бит.
Пример UUID – 550e8400-e29b-41d4-a716-446655440000
Тип XML
Тип данных XML может использоваться для хранения данных XML. Для хранения данных XML сначала необходимо создать значения XML с помощью функции xmlparse следующим образом:
Тип JSON
Тип данных json может использоваться для хранения данных JSON (JavaScript Object Notation). Такие данные также могут быть сохранены в виде текста , но у типа данных json есть преимущество проверки того, что каждое сохраненное значение является допустимым значением JSON. Также доступны связанные вспомогательные функции, которые можно использовать непосредственно для обработки типа данных JSON следующим образом.
| пример | Пример результата |
|---|---|
| array_to_json ( ‘<<1,5>, <99100>>’ :: Int []) | [[1,5], [99100]] |
| row_to_json (строка (1, ‘Foo’)) |
Тип массива
PostgreSQL дает возможность определять столбец таблицы как многомерный массив переменной длины. Могут быть созданы массивы любого встроенного или определенного пользователем базового типа, типа enum или составного типа.
Декларация массивов
Тип массива может быть объявлен как
или используя ключевое слово “ARRAY” как
Вставка значений
Значения массива могут быть вставлены в виде литеральной константы, заключая значения элементов в фигурные скобки и разделяя их запятыми. Пример показан ниже –
Доступ к массивам
Пример доступа к массивам приведен ниже. Приведенная ниже команда выберет людей, чьи сбережения больше во втором квартале, чем в четвертом квартале.
Модификация массивов
Пример изменения массивов приведен ниже.
или используя синтаксис выражения ARRAY –
Поиск массивов
Пример поиска в массивах приведен ниже.
Если размер массива известен, можно использовать метод поиска, указанный выше. Иначе, следующий пример показывает, как искать, когда размер неизвестен.
Композитные типы
Этот тип представляет список имен полей и их типов данных, т. Е. Структуру строки или записи таблицы.
Декларация составных типов
В следующем примере показано, как объявить составной тип
Этот тип данных может использоваться в таблицах создания, как показано ниже:
Ввод композитного значения
Составные значения могут быть вставлены в виде литеральной константы, заключая значения полей в круглые скобки и разделяя их запятыми. Пример показан ниже –
Это действительно для описанного выше инвентаризационного элемента. Ключевое слово ROW на самом деле является необязательным, если у вас есть более одного поля в выражении.
Доступ к составным типам
Чтобы получить доступ к полю составного столбца, используйте точку, за которой следует имя поля, подобно выбору поля из имени таблицы. Например, чтобы выбрать некоторые подполя из нашей таблицы примеров on_hand, запрос будет выглядеть так, как показано ниже –
Вы даже можете использовать имя таблицы (например, в многопользовательском запросе), например так:
Типы диапазонов
Типы диапазона представляют собой типы данных, которые используют диапазон данных. Типом диапазона могут быть дискретные диапазоны (например, все целочисленные значения от 1 до 10) или непрерывные диапазоны (например, любой момент времени между 10:00 и 11:00).
Доступные встроенные типы диапазонов включают следующие диапазоны –
int4range – диапазон целого числа
int8range – диапазон от bigint
numrange – диапазон числовых значений
tsrange – диапазон меток времени без часового пояса
tstzrange – диапазон меток времени с часовым поясом
диапазон дат – диапазон дат
int4range – диапазон целого числа
int8range – диапазон от bigint
numrange – диапазон числовых значений
tsrange – диапазон меток времени без часового пояса
tstzrange – диапазон меток времени с часовым поясом
диапазон дат – диапазон дат
Пользовательские типы диапазонов могут быть созданы, чтобы сделать доступными новые типы диапазонов, такие как диапазоны IP-адресов, использующие тип inet в качестве базы, или диапазоны с плавающей точкой, использующие тип данных float в качестве базы.
Типы диапазонов поддерживают включающие и исключающие границы диапазона, используя символы [] и () соответственно. Например, «[4,9)» представляет все целые числа, начиная с 4 и включая до 9, но не включая 9.
Типы идентификаторов объектов
Идентификаторы объектов (OID) используются внутри PostgreSQL в качестве первичных ключей для различных системных таблиц. Если указано WITH OIDS или включена переменная конфигурации default_with_oids , только тогда в таких случаях OID добавляются в созданные пользователем таблицы. В следующей таблице перечислены несколько типов псевдонимов. Типы псевдонимов OID не имеют собственных операций, за исключением специализированных подпрограмм ввода и вывода.
| название | Рекомендации | Описание | Пример значения |
|---|---|---|---|
| подъязычная | любой | числовой идентификатор объекта | 564182 |
| regproc | pg_proc | имя функции | сумма |
| regprocedure | pg_proc | функция с типами аргументов | сумма (int4) |
| regoper | pg_operator | имя оператора | + |
| regoperator | pg_operator | оператор с типами аргументов | * (целое число, целое число) или – (НЕТ, целое число) |
| regclass | pg_class | имя отношения | pg_type |
| regtype | pg_type | имя типа данных | целое число |
| regconfig | pg_ts_config | конфигурация текстового поиска | английский |
| regdictionary | pg_ts_dict | словарь текстового поиска | просто |
Псевдо-типы
Система типов PostgreSQL содержит ряд записей специального назначения, которые в совокупности называются псевдотипами. Псевдотип нельзя использовать в качестве типа данных столбца, но его можно использовать для объявления аргумента функции или типа результата.
В приведенной ниже таблице перечислены существующие псевдотипы.
Указывает, что функция принимает любой тип входных данных.
Указывает, что функция принимает любой тип данных.
Указывает, что функция принимает любой тип данных массива.
anynonarray
Указывает, что функция принимает любой тип данных, отличный от массива.
Указывает, что функция принимает любой тип данных enum.
Указывает, что функция принимает любой тип данных диапазона.
Указывает, что функция принимает или возвращает строку C с нулевым символом в конце.
Указывает, что функция принимает или возвращает внутренний тип данных сервера.
language_handler
Обработчик вызова процедурного языка объявляется как возвращающий language_handler.
fdw_handler
Обработчик обёртки сторонних данных объявляется как возвращающий fdw_handler.