Column check, default, enum, set

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

По умолчанию создаваемое в таблице поле может иметь пустое значение NULL. Чтобы поле имело какое-нибудь значение, необходимо в описание поля вставить ограничение на значение NOT NULL. В этом случае при вставке или обновлении записи СУБД проверит значения и отклонит запись при неопределенности значения.

CREATE TABLE managers (  
  id       integer [NOT NULL] Primary Key, -- ключевое поле, NOT NULL можно не указывать 
  sname    varchar (10) NOT NULL,          -- значение NOT NULL значение должно быть определено
  city     varchar (10),                   -- по умолчанию NULL, значение может быть не определено
  birthday date NULL                       -- значение может быть не определено
); 

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

Ограничение по значению, CHECK

Оператор sql check обеспечивает ограничение по значению, которое определяет условие, которому должно удовлетворять значение поля, вводимое в таблицу. Любая попытка модифицировать или вставить в поле значение, которое могло бы нарушить условие ограничения, будет отклонена.

Имеется два основных типа ограничений check - ограничение столбца и ограничение таблицы. Различие между ними состоит в том, что ограничение столбца применяется только к индивидуальным столбцам, в то время как ограничение таблицы может применяться к группам из одного и более столбцов. Ограничение таблицы размещается после определения всех полей. Ограничение на значение столбца размещается после определения типа данных и перед запятой.

Синтаксис "check sql syntax"

Синтаксис ограничения по значению "check sql syntax" имеет следующий вид :

CREATE TABLE candidates (
   . . . 
   sex         char (1) check (sex in ( 'м', 'ж' )),  -- ограничение на столбец (пол)
   age         int NOT NULL check (age >= 25 
                               and age >= 35),        -- ограничение на столбец (возраст)
   experience  int NOT NULL,                          -- опыт работы
   post        varchar(32)                            -- должность
   CONSTRAINT chkExperiencePost                       -- ограничение на таблицу
    (experience >= 3 and post like 'Менеджер%')       -- опыт работы в должности
   . . .
); 

В представленном примере таблица кандидатов на вакантную должность включает ограничения значений для 2-х полей и одно ограничение на таблицу. Именнованное ограничение на таблицу "chkExperiencePost" создано оператором CONSTRAINT. Условие размещения записи в таблице - возраст кандидата в пределах 25...35 лет с опытом работы в должности менеджера не менее 3-х лет.

В MySQL оператор sql check не используется. Необходимо использовать операторы enum или set.

Значение поля по умолчанию, default value

Можно при определении поля таблицы указать значение по умолчанию. Например, для поля "пол сотрудника" можно определить значение по умолчанию ("sql default value"). При определении значения по умолчанию можно использовать функции СУБД. Как правило это широко используется при определении значения даты записи.

-- в поле 'dt' определена дата записи, устанавливаемая серверов автоматически
CREATE TABLE managers (  
   ... 
   region  varchar NOT NULL DEFAULT 'Moscow'
   sex     char (1) [NOT NULL] DEFAULT 'ж',      -- пол сотрудника
   dt      datetime DEFAULT GETDATE(),           -- дата записи
   ts      timestamp DEFAULT NOW()
); 

Функции работы с временем для разных СУБД могут отличаться. Смотрите документацию.

В MySQL значение поля типа "datetime", как правило хранит время в виде целого числа формата YYYYMMDDHHMMSS, используя для этого 8 байтов. Это время не зависит от временной зоны. Оно всегда отображается при выборке точно также, как было сохранено, независимо от того какой часовой пояс установлен. Поле типа "timestamp" хранит 4-байтное целое число, равное количеству секунд, прошедших с полуночи 1 января 1970 года по усреднённому времени Гринвича (т.е. нулевой часовой пояс, точка отсчёта часовых поясов). При получении из базы timestamp отображается с учётом часового пояса. Часовой пояс может быть задан в операционной системе, глобальных настройках или в конкретной сессии. "timestamp" сохраняет всегда количество секунд по UTC (универсальное координированное время, солнечное время на меридиане Гринвича), а не по локальному часовому поясу.

Тип перечисления, MySQL ENUM

Тип перечисления enum используется при определении поля таблицы в MySQL и ограничивает его значения списком допустимых enum values, явно перечисленных в определении столбца в момент создания таблицы.

Синтакис ENUM

field  ENUM( 'value1','value2',...) [NOT NULL] [DEFAULT 'value1']

Значением enum также может быть пустая строка ("") или NULL при определенных условиях :

• если в поле вставляется некорректное значение в столбец с типом enum, то будет вставлена пустая строка, что является указанием на ошибочное значение. Эта строка отличается от "обычной" пустой строки по тому признаку, что она имеет цифровое значение, равное 0.
• если поле с типом enum определено как NULL, то тогда NULL также является допустимым значением столбца и значение по умолчанию будет NULL. Если enum определяется как NOT NULL, то значением по умолчанию является первый элемент из списка допустимых значений.

Каждое значение перечисления enum имеет индекс :

• все значения из списка допустимых элементов enum values в спецификации столбца пронумерованы, начиная с 1;
• индексом значения NULL является само NULL;
• индексное значение пустого значения является значенияе 0.

Можно использовать следующий SQL запрос, чтобы найти строки, в которых были установлены недопустимые для enum значения :

select * from table_name
    where enum_col = 0;

Например, столбец, определенный как enum ("один", "два", "три"), может иметь любое из перечисленных в таблице значений.

Регистр не играет никакой роли при присвоении значения столбцу enum. Так например, если при определении столбца было задано значение «Школа», а при присвоении значения полю в таблице было использовано «ШКОЛА». Сервер все равно выдаст значение именно «Школа».

Списочный тип, MySQL SET

Значение поля списочного типа mysql set может быть составлено из разделенных запятыми подстрок, каждая из которых должна быть выбрана из списка допустимых значений, определенных при создании таблицы. Элементы множества "set" разделяются запятыми. Поэтому сами элементы множества "set value" не могут содержать запятых.

Синтакис mysql set

field  SET( 'value1', 'value2',...) [NOT NULL] [DEFAULT 'value1']

Например, столбец, определенный как SET("один", "два") NOT NULL может принимать следующие значения :

• ""
• "один"
• "два"
• "один,два"

Множество SET может иметь максимум 64 различных элемента.

Небольшой пример set :

CREATE TABLE tbl (
  a SET ( 'aaa', 'bbb', 'ccc' )
);

INSERT INTO tbl 
   VALUES ( 'aaa' ), ( 'aaa,bbb' ), ( 'bbb' ), ( 'ccc,aaa' );

SELECT a 'string', a + 0 'decimal', LPAD(BIN(a + 0), 8, '0') 'binary' 
   FROM tbl;

+---------+---------+----------+
| string  | decimal | binary   |
+---------+---------+----------+
| aaa     |       1 | 00000001 |
| aaa,bbb |       3 | 00000011 |
| bbb     |       2 | 00000010 |
| aaa,ccc |       5 | 00000101 |
+---------+---------+----------+
4 rows in set (0.00 sec)

В примере наглядно видно соответствие бит и строк. К сожалению, битовых индексов MySQL не имеет и ниже приведенные операции приводят к полному просмотру всех строк таблицы.

mysql> SELECT a FROM tbl WHERE a & 1;
+---------+
| a       |
+---------+
| aaa     |
| aaa,bbb |
| aaa,ccc |
+---------+
3 rows in set (0.00 sec)

mysql> SELECT a FROM tbl WHERE FIND_IN_SET( 'aaa', a);
+---------+
| a       |
+---------+
| aaa     |
| aaa,bbb |
| aaa,ccc |
+---------+
3 rows in set (0.00 sec)

mysql> SELECT a FROM tbl WHERE a LIKE '%aaa%';
+---------+
| a       |
+---------+
| aaa     |
| aaa,bbb |
| aaa,ccc |
+---------+
3 rows in set (0.00 sec)

И только равенство позволяет использовать индекс. Индексировать столбец SET можно, но бессмысленно.

mysql> SELECT a FROM tbl WHERE a = 'aaa';
+-----+
| a   |
+-----+
| aaa |
+-----+
1 row in set (0.00 sec)

Недостатки использования mysql set :

• Список всех возможных значений элементов можно получить только через INFORMATION_SCHEMA.COLUMNS и результат еще нужно парсить.
• Изменить состав возможных значений элементов можно только через ALTER TABLE, что требует прав и приведет к перестроению таблицы и всех ее индексов.
• Затруднительно сравнить столбцы типа set в двух разных таблицах. Если DDL столбцов полностью идентичны, то можно использовать равенство/неравенство и битовые операции, но данная схема не защищена от ошибки при изменении DDL столбцов.
• Возможно устанавливать и снимать отдельные биты столбца оперируя им как числом, но затруднительно удалить или добавить подстроку в CSV-список.