Какие типы данных не поддерживают null значения sql
Перейти к содержимому

Какие типы данных не поддерживают null значения sql

  • автор:

Какие типы данных не поддерживают null значения sql

Этот раздел описывает различные типы данных, доступные в СУБД Firebird и MS SQL, а также соответствия типов для перехода с одной системы на другую.

Доступные типы данных СУБД MS SQL зависят от версии СУБД. В следующей таблице перечислены типы данных и версия СУБД, в которой они были введены.

Таблица 1. Таблица соответствия типов данных

Определение MSSQL и пояснения

8-байтные целочисленные данные.

Двоичные данные фиксированной длины. Максимальная длина 8000 байт. В версии 6.5 максимальная длина была 255 байт.

Целочисленные данные со значениями только 1 или 0 . Обычно заменяются константами ‘T’ и ‘F’ . (В СУБД Firebird так же можно использовать тип SMALLINT со значениями 0 и 1 . — прим. перев.)

Текстовые данные фиксированной длины (не-Unicode). Максимальная длина 8000 символов. В версии 6.5 максимальная длина была 255 байт. СУБД Firebird способна хранить до 32767 символов.

Ссылка на курсор. Этот тип используется только в хранимых процедурах и триггерах; этот тип не может использоваться при объявлении структуры таблиц.

Дата и время с 1 января 1753 года до 31 декабря 9999 года, точность 3/100 секунды (3.33 мс).

Числовые данные с фиксированной точностью. Диапазон от -10 38 -1 до 10 38 -1.

Вещественные числовые данные. Диапазон от -1.79E+38 до 1.79E+38.

Двоичные данные переменной длины. Максимальная длина 2 31 -1 (2147483647) байт.

4-байтные целочисленные данные. Диапазон от -2 31 (-2147483648) до 2 31 -1 (2147483647).

Денежные данные. Диапазон от -2 63 (-922337203685477.5808) до 2 63 -1 (+922337203685477.5807), точность до 1/10000 денежной единицы.

CHAR(x) CHARACTER SET UNICODE_FSS

Символные данные фиксированной длины (Unicode). Максимальная длина 4000 символов.

BLOB SUB_TYPE TEXT

Символьные данные переменной длины (Unicode). Максимальная длина 2 30 -1 (1073741823) символов.

В СУБД MS SQL decimal и numeric эквивалентны.

VARCHAR(x) CHARACTER SET UNICODE_FSS

Символьные данные переменной длины. Максимальная длина 4000 символов.

Вещественные данные. Диапазон от -3.40E+308 до 3.40E+308.

Дата и время с 1 января 1900 года до 6 июня 2079 года, точность 1 минута. Тип данных Firebird имеет бОльшие диапазон и точность.

2-байтные целочисленные данные. Диапазон от -2 15 (-32768) до 2 15 -1 (32767).

Денежные данные. Диапазон от -214748.3648 до +214748.3647, точность 1/10000 денежной единицы. Диапазон СУБД Firebird больше при указанной замене.

Данные различных типов.

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

BLOB SUB_TYPE TEXT

Символьные данные переменной длины (не-Unicode). Максимальная длина 2 31 -1 (2147483647) символов.

INTEGER или BIGINT

Уникальное для базы данных число. В СУБД Firebird Вам необходимо использовать механизм генераторов для этих целей.

1-байтовое целочисленное значение без знака. Диапазон от 0 до 255. В СУБД Firebird нет эквивалентного типа.

Двоичные данные переменной длины. Максимальная длина 8000 байт.

Символьные данные переменной длины (не-Unicode). Максимальная длина 8000 символов. СУБД Firebird способна хранить до 32765 символов. В СУБД MS SQL 6.5 максимум был 255 символов.

Глобально уникальный идентификатор (GUID). В СУБД Firebird Вам неоходимо использовать функции, определяемые пользователем (UDF), для генерации значения идентификатора. (Если Вы собираетесь индексировать поле с глобально уникальными идентификаторами, то лучше использовать UUID — это другой формат представления GUID, представляемый как CHAR(22). — прим. перев.)

Небольшое различие в поведении в СУБД Firebird между типами NUMERIC и DECIMAL , которое приходит на ум, заключается в том, что определение NUMERIC означает четко указанную точность (общее число цифр), в то время как DECIMAL одначает как минимум указанную точность. В СУБД MS SQL оба типа numeric и decimal эквивалентны.

Существует также квази-тип данных — identity (идентификатор), который может использоваться только для указания в определениях таблиц. Фактически, это тип данных int , значение поля автоматически генерируется при добавлении новой записи и не может быть впоследствии изменено.

Перенос типа данных bit

Тип данных bit используется для хранения булевого (логического) значения, 0 или 1 . СУБД MS SQL не поддерживает присваивание значения NULL таким полям. Пользователи СУБД Firebird могут эмулировать поведение логического типа, используя типы SMALLINT или CHAR(1) .

Возможные значения полей логического типа в СУБД Firebird могут быть ограничены через использование доменов (domains).

Перенос типа данных identity (идентификатор)

Существует несколько способов переноса механизма идентификаторов СУБД MS SQL. В общем, СУБД Firebird имеет бОльшую гибкость и мощь в этом вопросе.

Самый простой способ создаия аналога типа identity — создание триггера BEFORE INSERT (перед вставкой новой записи в базу данных) для интересующей Вас таблицы, и в этом триггере присваивать очередное значение генератора. Такой метод гарантирует уникальность (при условии, что шаг для генератора отличен от нуля и всегда имеет один и то же знак — прим. перев.).

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

Еще одна обычная техника — создание хранимой процедуры, которая возвращает очередное значение генератора. Это так же позволяет получать значение генератора и использовать его для нескольких операций (например, при добавлении записи в основную таблицу, а затем добавлении нескольких записей в подчиненную таблицу).

CREATE TABLE my_table ( my_number integer not null primary key )
CREATE GENERATOR my_generator
CREATE TRIGGER my_before_trigger FOR my_table BEFORE INSERT AS BEGIN IF (NEW.my_number IS NULL) THEN NEW.my_number = GEN_ID(my_generator, 1); END
CREATE PROCEDURE get_my_generator RETURNS (new_value INTEGER) AS BEGIN new_value = GEN_ID(my_generator, 1); END

Перенос типа данных uniqueidentifier (глобально уникальный идентификатор)

В СУБД MS SQL тип uniqueidentifier используется для репликации. Также это простой способ определения уникальных глобальных идентификаторов для записей базы данных.

Для использования аналогичного типа данных в СУБД Firebird создайте триггер BEFORE INSERT (перед вставкой новой записи) для интересующей Вас таблицы с полем типа uniqueidentifier , и используйте значение функции, определяемой пользователем (UDF), для получения очередного значения GUID.

Вы можете использовать библиотеку uuidUDF (прим. перев.):

/************************************************************** UUID_CREATE Returns a UUID (cstring(22), compressed, reversed, URL compatible UUID) Parameters: cstring(22) dummy to allow Interbase to perform memory management Returns: cstring(22) **************************************************************/ DECLARE EXTERNAL FUNCTION UUID_CREATE CSTRING(22) RETURNS PARAMETER 1 ENTRY_POINT 'fn_uuid_create' MODULE_NAME 'uuidlib'; /************************************************************** GUID_CREATE Returns a GUID (cstring(36), standard readable representation of a UUID in the xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx format) Parameters: cstring(36) dummy to allow Interbase to perform memory management Returns: cstring(36) **************************************************************/ DECLARE EXTERNAL FUNCTION GUID_CREATE CSTRING(36) RETURNS PARAMETER 1 ENTRY_POINT 'fn_guid_create' MODULE_NAME 'uuidlib';
Firebird Documentation Index → Переход с MS SQL на Firebird → Типы данных

SQL — Урок 2. Типы данных

Этот урок носит больше теоретический характер, но пропустить его нельзя. В дальнейшем вы сможете возвращаться к нему, как к справочному уроку, сейчас же просто ознакомьтесь. В прошлом уроке говорилось, что MySQL поддерживает числовые, строковые, календарные данные и данные типа NULL. Рассмотрим их по очереди.

Числовые типы данных

Тип данных Объем памяти Диапазон Описание
TINYINT (M) 1 байт от -128 до 127 или от 0 до 255 Целое число. Может быть объявлено положительным с помощью ключевого слова UNSIGNED, тогда элементам столбца нельзя будет присвоить отрицательное значение. Необязательный параметр М — количество отводимых под число символов. Необязательный атрибут ZEROFILL позволяет свободные позиции по умолчанию заполнить нулями.

TINYINT — хранит любое число в диапазоне от -128 до 127.

TINYINT UNSIGNED — хранит любое число в диапазоне от 0 до 255.

TINYINT (2) — предполагается, что значения будут двузначными, но по факту будет хранить и трехзначные.

SMALLINT — хранит любое число в диапазоне от -32768 до 32767.

SMALLINT UNSIGNED — хранит любое число в диапазоне от 0 до 65535.

SMALLINT (4) — предполагается, что значения будут четырехзначные, но по факту будет хранить и пятизначные.

MEDIUMINT — хранит любое число в диапазоне от -8388608 до 8388608.

MEDIUMINT UNSIGNED — хранит любое число в диапазоне от 0 до 16777215.

MEDIUMINT (4) — предполагается, что значения будут четырехзначные, но по факту будет хранить и семизначные.

INT — хранит любое число в диапазоне от -2147683648 до 2147683648.

INT UNSIGNED — хранит любое число в диапазоне от 0 до 4294967295.

INT (4) — предполагается, что значения будут четырехзначные, но по факту будет хранить максимально возможные.

BIGINT — хранит любое число в диапазоне от -2 63 до 2 63 -1.

BIGINT UNSIGNED — хранит любое число в диапазоне от 0 до 2 64 .

BIGINT (4) — предполагается, что значения будут четырехзначные, но по факту будет хранить максимально возможные.

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

Строковые типы данных

Тип данных Объем памяти Максимальный размер Описание
CHAR (M) M символов М символов Позволяет хранить строку фиксированной длины М. Значение М — от 0 до 65535.

Календарные типы данных

Тип данных Объем памяти Диапазон Описание
DATE 3 байта от ‘1000-01-01’ до ‘9999-12-31’ Предназначен для хранения даты. В качестве первого значения указывается год в формате «YYYY», через дефис — месяц в формате «ММ», а затем день в формате «DD». В качестве разделителя может выступать не только дефис, а любой символ отличный от цифры.
TIME 3 байта от ‘-838:59:59’ до ‘838:59:59’ Предназначен для хранения времени суток. Значение вводится и хранится в привычном формате — hh:mm:ss, где hh — часы, mm — минуты, ss — секунды. В качестве разделителя может выступать любой символ отличный от цифры.
DATATIME 8 байт от ‘1000-01-01 00:00:00’ до ‘9999-12-31 23:59:59’ Предназначен для хранения и даты и времени суток. Значение вводится и хранится в формате — YYYY-MM-DD hh:mm:ss. В качестве разделителей могут выступать любые символы отличные от цифры.
TIMESTAMP 4 байта от ‘1970-01-01 00:00:00’ до ‘2037-12-31 23:59:59’ Предназначен для хранения даты и времени суток в виде количества секунд, прошедших с полуночи 1 января 1970 года (начало эпохи UNIX).
YEAR (M) 1 байт от 1970 до 2069 для М=2 и от 1901 до 2155 для М=4 Предназначен для хранения года. М — задает формат года. Например, YEAR (2) — 70, а YEAR (4) — 1970. Если параметр М не указан, то по умолчанию считается, что он равен 4.

Тип данных NULL

Вообще-то это лишь условно можно назвать типом данных. По сути это скорее указатель возможности отсутствия значения. Например, когда вы регистрируетесь на каком-либо сайте, вам предлагается заполнить форму, в которой присутствуют, как обязательные, так и необязательные поля. Понятно, что регистрация пользователя невозможна без указания логина и пароля, а вот дату рождения и пол пользователь может указать по желанию. Для того, чтобы хранить такую информацию в БД и используют два значения:

NOT NULL (значение не может отсутствовать) для полей логин и пароль,

NULL (значение может отсутствовать) для полей дата рождения и пол.

По умолчанию всем столбцам присваивается тип NOT NULL, поэтому его можно явно не указывать.

create table users (login varchar(20), password varchar(15), sex enum(‘man’, ‘woman’) NULL, date_birth date NULL);

Таким образом, мы создаем таблицу с 4 столбцами: логин (не более 20 символов) обязательное, пароль (не более 15 символов) обязательное, пол (мужской или женский) не обязательное, дата рождения (тип дата) необязательное.

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

Программирование на Python для начинающих

Онлайн-курс. Освойте востребованную профессию с зарплатой от 70 000 руб в месяц!

Теперь нажмите кнопку, что бы не забыть адрес и вернуться к нам снова.

SQL-Ex blog

Логический (булев) тип данных отсутствует в SQL Server. Другие базы данных, подобные Oracle и MySQL, поддерживают логический тип данных, который принимает значения TRUE и FALSE.

SQL Server использует тип данных Bit, который принимает значения 0, 1 и NULL, которые могут использоваться вместо значений TRUE, FALSE и NULL. Здесь мы рассмотрим несколько примеров на вставку и выборку этих значений.

Что это за тип данных bit?

Этот тип данных может хранить 3 значения, 1, 0 и NULL. Этот тип данных более эффективен, чем тип данных Boolean, используемый другим СУБД, поскольку он использует только один бит для хранения данных. Давайте рассмотрим примеры его использования.

Примеры логического типа в SQL Server

Следующий пример создаст переменную с именем myBoolean типом данных bit. Затем мы установим для переменной значение true и посмотрим результаты.

declare @myBoolean bit 
set @myBoolean='true' 
select @myBoolean result

Заметьте, что когда мы выбираем это значение, то выводится 1, означающее true.

Аналогично вы можете установить булево значение в false.

declare @myBoolean bit 
set @myBoolean='false' 
select @myBoolean result

Наоборот, и как лучшая практика, вы можете присваивать числа этой переменной. Следующий пример присвоит переменной myBoolean значение 1, а не true. Это предпочтительный вариант.

declare @myBoolean bit 
set @myBoolean=1 
select @myBoolean result

Вместо значения false мы присваиваем значение 0.

declare @myBoolean bit 
set @myBoolean=0 
select @myBoolean result

Резюмируя сказанное, вы можете присваивать битовой переменной значения 0, 1 или использовать ‘TRUE’ и ‘FALSE’.

Наконец, вы можете установить переменную bit в значение NULL, если это необходимо. В следующем примере переменной @myBoolean присваивается значение NULL.

declare @myBoolean bit 
set @myBoolean=NULL 
select @myBoolean result

Как заменить значения 0, 1 на значения true или false

Как вы уже видели, тип bit показывает 0 и 1 вместо TRUE и FALSE. Как можно заменить значения 0 и 1 на TRUE и FALSE?

Следующий пример демонстрирует как это сделать. Мы будем использовать оператор CASE для замены значений. Вот этот пример.

declare @myBoolean bit 
set @myBoolean='TRUE' 
select 
case 
WHEN @myBoolean=1 then 'TRUE' 
WHEN @myBoolean=0 then 'FALSE' 
ELSE NULL 
end as result

Код выполняет следующее. Если значение равно 1, то будет возвращаться TRUE. Если значение переменной равно 0, то возвращается FALSE. В противном случае возвращается NULL.

Как создать таблицу со столбцом типа данных bit

В следующем примере создается таблица с именем myBooleanTable и двумя столбцами. Столбец ID имеет тип INTEGER, а столбец ispair — тип данных BIT.

create table myBooleanTable 
( 
id int, 
ispair bit 
)

Как вставить данные логического типа

Для вставки данных типа bit вы можете использовать только значения 0, 1 и NULL. Например, ниже мы вставляем данные в ранее созданную таблицу myBooleanTable.

insert into myBooleanTable values 
(1,0), 
(2,1), 
(5,NULL)

Работа с запросами SQL

Следующий пример использует учебную базу данных Adventureworks. Если она у вас не установлена, обратитесь к этой статье относительно ее установки и конфигурирования.

В этом примере мы будем использовать таблицу HumanResource.Employee, которая содержит несколько столбцов типа данных bit. В первом примере мы используем битовый столбец SalariedFlag.

Следующий пример выводит столбцы BusinessEntityID, NationalIDNumber и SalariedFlag, когда SalariedFlag равен 1.

SELECT [BusinessEntityID] 
,[NationalIDNumber] 
,[SalariedFlag] 
FROM [HumanResources].[Employee] 
WHERE SalariedFlag =1

Для получения аналогичной информации для сотрудников, у которых Salariedflag равен false, вы можете поменять значение флага SalariedFlag на false (0).

SELECT [BusinessEntityID] 
,[NationalIDNumber] 
,[SalariedFlag] 
FROM [HumanResources].[Employee] 
WHERE SalariedFlag =0

И, наоборот, вы можете использовать значения TRUE и FALSE вместо 0 и 1, но это не рекомендуется.

SELECT [BusinessEntityID] 
,[NationalIDNumber] 
,[SalariedFlag] 
FROM [HumanResources].[Employee] 
WHERE SalariedFlag ='true'

Наконец, мы можем использовать значение NULL в предложении WHERE. Использование NULL несколько отличается от использования значений 0 и 1, поскольку вы должны применять оператор IS вместо равенства.

Следующий пример иллюстрирует это.

SELECT [BusinessEntityID] 
,[NationalIDNumber] 
,[SalariedFlag] 
FROM [HumanResources].[Employee] 
WHERE SalariedFlag IS NULL

Преобразование логических значений к другим типам данных

Следующий пример показывает результат сложения числового значения со значением типа bit. Мы объявляем переменную myBoolean, устанавливая ее значение в true, а затем прибавляем 2.

declare @myBoolean bit 
set @myBoolean='true' 
select @myBoolean+2 result

Итак, в этом примере myBoolean равен 1, а 1+2 равно 3, это означает, что битовое значение неявно преобразуется к числовому.

Если мы попытаемся выполнить конкатенацию (оператор +), то получим ошибку. Следующий пример иллюстрирует эту проблему.

declare @myBoolean bit 
set @myBoolean='true' 
select 'The value is'+@myBoolean

Сообщение об ошибке гласит:

Типы данных varchar и bit несовместимы в операции сложения.

Лучшим решением этой проблемы является использование оператора CONCAT, который преобразует переменную bit к строке.

declare @myBoolean bit 
set @myBoolean=1 
select CONCAT ('The value is ',@myBoolean) as result

CONCAT конкатенирует строку с переменной bit, что даст следующий результат:

Как нерекомендуемый вариант, мы можем использовать функцию CONVERT для преобразования переменной bit в строку. Для данного сценария использование CONCAT является лучшим решением. Однако для других сценариев вы, возможно, не сможете использовать CONCAT, и потребуется использовать CONVERT вместо этого.

declare @myBoolean bit 
set @myBoolean='true' 
select 'The value is'+CONVERT(varchar(1),@myBoolean) as result

Функция CONVERT преобразует тип данных bit переменной @myBoolean в varchar(1), которое уже может конкатенироваться со строкой.

Другим вариантом является использование CAST. CAST подобна CONVERT, мы включаем этот вариант только для того, что вы были знакомы с этим вариантом, который может вам встретиться где-то еще.

declare @myBoolean bit 
set @myBoolean='true' 
select 'The value is'+CAST(@myBoolean as varchar(1)) as result

Синтаксис CAST несколько отличается от CONVERT. Вы должны указать выражение, а затем тип данных.

Хранимые процедуры с логическими переменными

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

В этом примере мы покажем, как создать хранимую процедуру с переменными типа bit и выполнить её.

Сначала создадим хранимую процедуру с именем salariedFlag, которая выводит LoginID пользователей с salariedFlag, равным true (1) или false (0). В соответствии с переменной @salariedFlag выводиться будут значения, указанные в предложении WHERE. Значения будут извлекаться из таблицы Employee.

create procedure salariedFlag 
( 
@salariedFlag bit 
) 
as 
select LoginID 
from [HumanResources].[Employee] 
where salariedFlag = @salariedFlag

Для выполнения хранимой процедуры используется предложение EXEC и указывается значение для переменной (параметра) @salariedFlag.

exec salariedFlag 1

Этот код выполняет salariedFlag, при этом @salaried получает значение 1 (TRUE).

Вот результат, возвращаемый хранимой процедурой:

Эти значения LoginID соответствуют SalariedFlag, равному TRUE. Если мы хотим увидеть LoginID, соответствующие значению FALSE (0), вы можете вызвать эту процедуру с другим значением параметра. Мы можем использовать EXECUTE вместо EXEC. Мы можем также включить в вызов имя параметра (что является лучшим вариантом использования, который легче понять и поддерживать, особенно в случае хранимых процедур с многими параметрами).

execute salariedFlag @salariedFlag='False'

Как видно в этом примере, мы используем команду EXECUTE, а не EXEC, и имя параметра @salariedFlag, в отличие от предыдущего примера. Наконец, мы используем значение, равное строке False, а не значению 0 (не рекомендуется, но работает).

Результат выполнения хранимой процедуры представлен ниже.

Неполные данные и null

Что может означать тот факт, что у студента null в столбце GroupId?

  • Значение неизвестно (нет информации, из какой группы студент)
  • Значение неверно (студент учится в какой-то группе, но эта группа не представлена в БД)
  • Значение еще/уже не существует (студент был зачислен, но еще не распределен в группу или уже отчислен)
  • Значение не имеет смысла (студент из другого университета, который пришел с какими-то целями в ИТМО)
  • Значение недоступно (недостаточно прав узнать группу)

На основе этих предположений можно сделать вывод, что значение null сильно зависит от контекста (какую предметную область мы моделируем итд.).
Вполне возможно, что возникнет необходимость различать разные виды того, что значение в том или ином смысле отсутствует.

Можно ли обойтись без null?

Как представить кортеж с неопределенными частями в нашем случае?

  • Разбить на 2 группы и сделать необязательную связь 1:1. В таком случае, в дополнительной таблице будет запись (StudentId, GroupId) тогда и только тогда, когда у студента определена группа

Где еще появляется null

  • Результаты внешних соединений
  • Результаты множественных операций

Оказывается, что в некоторых случаях без null не обойтись и надо уметь с ним работать.

Тернарная логика с использованием null

С точки зрения SQL, результат логического выражения может быть true, false или unknown.
С другой стороны есть тип boolean, и у него есть 3 значения: true, false и null
То есть формально unknown — это результат вычисления, а null — это конкретное значение, которое может быть записано в БД. На практике unknown представляется значением null, и это различие не будет иметь большого значения.

Конъюнкция

[math]\bf[/math] [math]\bf[/math] [math]\bf[/math] [math]\bf[/math]
[math]\bf[/math] [math]true[/math] [math]unknown[/math] [math]false[/math]
[math]\bf[/math] [math]unknown[/math] [math]unknown[/math] [math]false[/math]
[math]\bf[/math] [math]false[/math] [math]false[/math] [math]false[/math]

Дизъюнкция

[math]\bf[/math] [math]\bf[/math] [math]\bf[/math] [math]\bf[/math]
[math]\bf[/math] [math]true[/math] [math]true[/math] [math]true[/math]
[math]\bf[/math] [math]true[/math] [math]unknown[/math] [math]unknown[/math]
[math]\bf[/math] [math]true[/math] [math]unknown[/math] [math]false[/math]

Отрицание

[math][/math] [math]\bf[/math] [math]\bf[/math] [math]\bf[/math]
[math]\bf[/math] [math]false[/math] [math]unknown[/math] [math]true[/math]

Сравнение

Равенство
[math]\bf[/math] [math]\bf[/math] [math]\bf[/math] [math]\bf[/math]
[math]\bf[/math] [math]true[/math] [math]unknown[/math] [math]false[/math]
[math]\bf[/math] [math]unknown[/math] [math]unknown[/math] [math]unknown[/math]
[math]\bf[/math] [math]false[/math] [math]unknown[/math] [math]true[/math]
is
[math][/math] [math]\bf[/math] [math]\bf[/math] [math]\bf[/math]
[math]\bf[/math] [math]true[/math] [math]false[/math] [math]false[/math]
[math]\bf[/math] [math]false[/math] [math]true[/math] [math]false[/math]
[math]\bf[/math] [math]false[/math] [math]false[/math] [math]true[/math]
[math]\bf[/math] [math]false[/math] [math]true[/math] [math]true[/math]
[math]\bf[/math] [math]true[/math] [math]false[/math] [math]true[/math]
[math]\bf[/math] [math]true[/math] [math]true[/math] [math]false[/math]

Проблемы при работе с null

При работе с null в процессе разработки БД, во избежание непредвиденных ошибок, необъодимо заранее ознакомиться с тем, какие проблемы могут возникнуть.

Вывод логических выражений

В новой тернарной логике работают не все правила преобразований, присущие двоичной. Например, нельзя полагать, что [math](A\ \vee\ \neg\ A)[/math] всегда истинно, потому что теперь может получиться unknown.
Поэтому при каждом преобразовании троичного логического выражения, лучше сверяться с таблицами истинности.

Скалярные операции, порождающие null

Следующие операции с null порождают null, и иногда это может сбивать с толку начинающих разработчиков.

  • [math]=[/math] , [math]\lt \gt [/math] , [math]\lt [/math] , [math]\lt =[/math] , [math]\gt [/math] , [math]\gt =[/math]
  • [math]+[/math] , [math]−[/math] , [math]*[/math] , [math]/[/math]
  • [math]\|[/math]
  • [math]in[/math]

Рассмотрим несколько примеров.

select (1 + null) from Students;

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

select StudentId from Students where GroupId = null;

Это частая ошибка, сравнение с null дает unknown, а значит запрос вернет пустую таблицу.

Говоря об операции сравнения, стоит отметить, что она не транзитивна и не рефлексивна.

  • [math]x\ =\ x[/math] — true или null
  • [math]x\ \lt \gt \ x[/math] — true или null
  • [math]x\ or\ x[/math] — true или null
  • [math]x\ or\ not\ x[/math] — true или null
  • [math]x\ and\ not\ x[/math] — false или null

Дубликаты и null

Так как null ≠ null, сравнения кортежей, содержащих null не обладают интуитивными свойствами, например:

  • [math]R \cup R[/math] — не всегда [math]R[/math]
  • [math]R \cap R[/math] — не всегда [math]R[/math]
  • [math]R \bowtie R[/math] — не всегда [math]R[/math]

Неинтуитивность null

Рассмотрим запрос, для нахождения студентов не из группы ‘M34391’.

select * from Students where GroupId <> 'M34391'

Корректность запроса зависит от смысла null. Неясно, надо ли возвращать в этом запросе студента, о котором нет информации, в какой группе он учится.

Следующий запрос, хоть и выглядит странно, предполагает просто поиск всевозможных студентов

select * from Students where GroupId <> 'M34391' union select * from Students where GroupId = 'M34391'

Но из-за наличия null, этот запрос не отработает так, как предполагалось. Если GroupId студента null, то сравнение не вернет true, а значит в результате это учтено не будет.
Подробнее у работе функции where будет рассказано в следующем разделе.

Работа с null в SQL

Несмотря на множество проблем, описанных выше, в SQL существуют механизмы, позволяющие корректно обработать null.

Проверки значений

Для сравнения с null используется is null (или is not null ). Получить всех студентов с null в поле GroupId можно следующим образом:

select StudentId from Students where GroupId is null;

В общем виде синтаксис проверки значений выглядит следующим образом: значение is [ not ] < null |true|false|unknown >, например:

  • x is not true
  • x or x is not null

Так же в SQL существует функция coalesce(v1, v2, . ), которая принимает произвольное число аргументов и возвращает первый не не null. Если все аргументы null, то возвращает null.

Ключи и null

Можно использовать null:

  • Альтернативные ключи
  • Внешние ключи
    • Простые
    • Составные, отсутствующие целиком

    Первичные ключи не могут содержать null.

    Предикаты

    DML

    where и having считают истинным предикат только если он вернул true Зная этот факт можно, например убедиться, что false and unknown дает false. Следующий запрос вернет 1:

    select 1 where not (0 = 1 and 0 = null)
    DDL

    C точки зрения check constraint-ов не подходит только false. Unknown превращается в true

    Различимость

    Два null не равны и не различимы. Это важно для distinct и group by
    Например, кортежи (1, null) и (1, null) склеятся в случае distinct и не породят разные группы в случае group by , т.к. не различимы

    Типы столбцов

    В SQL столбцы могут быть nullable (по умолчанию) и не nullable

    birthday date birthday date not null

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

    Прочее

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *