Статьи

Как использовать уникальные индексы в MySQL и других базах данных

Декабрь 8, 2018

Если вы использовали базы данных некоторое время, вы, вероятно, установили первичный ключ в большинстве ваших таблиц. Первичный ключ — это уникальный идентификатор для каждой записи, например 

CREATE TABLE `phone` (
	`id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
	`country` DECIMAL(5,0) UNSIGNED NOT NULL,
	`area` DECIMAL(5,0) UNSIGNED NOT NULL,
	`number` DECIMAL(8,0) UNSIGNED NOT NULL,
	`extension` DECIMAL(5,0) UNSIGNED DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

В этом примере столбец id — это наш первичный ключ. Когда мы INSERTAUTO_INCREMENT

Предположим, вы добавили следующие данные:

Я бы страна площадь число расширение
1 1 234 567890 ЗНАЧЕНИЕ NULL
2 44 9876 54321 42
3 61 3 90908200 ЗНАЧЕНИЕ NULL

затем введите следующую INSERT 

 INSERT INTO `phone`
(`id`, `country`, `area`, `number`)
(1, 1, 234, 567890);

База данных откажется добавлять новую запись, потому что она уже существует с идентификатором 1. К счастью, мы можем опустить идентификатор из нашей INSERT 

 INSERT INTO `phone`
(`country`, `area`, `number`)
(1, 234, 567890);

Теперь у нас есть четыре записи:

Я бы страна площадь число расширение
1 1 234 567890 ЗНАЧЕНИЕ NULL
2 44 9876 54321 42
3 61 3 90908200 ЗНАЧЕНИЕ NULL
4 1 234 567890 ЗНАЧЕНИЕ NULL

Мы можем добавить почти 17 миллионов записей, прежде чем идентификатор выйдет за пределы диапазона.

Отлично — за исключением того, что записи 1 и 4 идентичны. Что если мы хотим убедиться, что все номера телефонов уникальны?

Уникальные индексы

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

В нашем примере мы хотим убедиться, что две записи не имеют одинаковую страну, область, номер и расширение. Мы можем сделать это, изменив нашу таблицу: 

 ALTER TABLE `phone` 
ADD UNIQUE INDEX `ix_phone` (`country`, `area`, `number`, `extension`);

Обратите внимание, что индексное имя «ix_phone» не является обязательным. В качестве альтернативы, мы могли бы воссоздать нашу таблицу: 

 DROP TABLE IF EXISTS `phone`;

CREATE TABLE `phone` (
	`id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
	`country` DECIMAL(5,0) UNSIGNED NOT NULL,
	`area` DECIMAL(5,0) UNSIGNED NOT NULL,
	`number` DECIMAL(8,0) UNSIGNED NOT NULL,
	`extension` DECIMAL(5,0) UNSIGNED DEFAULT NULL,
	PRIMARY KEY (`id`),
	UNIQUE KEY `ix_phone` (`country`, `area`, `number`, `extension`),
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

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

Давайте попробуем вставить повторяющуюся запись, даже если мы не указываем идентификатор: 

 INSERT INTO `phone`
(`country`, `area`, `number`, `extension`)
(44, 9876, 54321, 42);

Следующая ошибка будет сгенерирована, если вы используете MySQL: 

 Error Code: 1062
Duplicate entry '44-9876-54321-42' for key 'ix_phone'

Если вы используете практически любую базу данных, вы можете гарантировать, что ваши телефонные записи будут уникальными независимо от того, как вставлены данные.

MySQL NULL

Я говорю почти о любой базе данных, потому что MySQL имеет странную причуду NULL рассматривается как уникальное значение, поэтому вы не можете использовать сравнения, такие как value = NULLvalue IS NULL К сожалению, это также влияет на уникальные индексы, и никакая логика не была реализована, чтобы это исправить.

Мы можем выполнить наш оригинальный INSERT 

 INSERT INTO `phone`
(`country`, `area`, `number`)
(1, 234, 567890);

Да, это безумие. Я не знаю о проблеме в других базах данных, и даже MySQL работает должным образом, если вы используете механизм хранения BDB. Об этом сообщают как об ошибке MySQL, но нет никаких известных планов исправить это.

Решение: убедитесь, что все поля, определенные в уникальном индексе, не могут быть установлены в NULL . В этом примере мы могли бы указать, что нет добавочного номера, установив значение, такое как 0 или 99999. Или, возможно, мы могли бы сделать поле числом со знаком и установить -1. Это ужасно, но это сработает.

Несмотря на эту проблему, уникальные индексы полезны во многих ситуациях и помогают вам сохранить целостность данных, когда другие программисты и пользователи не так добросовестны!

34
Share: