LINUX.ORG.RU

Уникальные значения из нескольких столбцов таблицы MySQL

 ,


0

3

Доброго времени суток, LOR.

Есть дурацкий вопрос по индексам, но сначала преамбула:

Потихоньку пилится база данных с двумя таблицами: «сущности» и «связи».

«Сущности» - это плоский список заметок с уникальными заголовками и произвольным содержанием:

CREATE TABLE `entities` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Номер по порядку',
	`created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Дата/время создания',
	`modified` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Дата/время изменения',
	`title` VARCHAR(255) NOT NULL COMMENT 'Заголовок заметки',
	`content` MEDIUMTEXT NOT NULL COMMENT 'Текст заметки',
	PRIMARY KEY (`id`),
	UNIQUE INDEX `title` (`title`)
)
COMMENT='Сущности. /*TODO: Реализовать резервное копирование изменяемых записей*/'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=7
;

«Связи», как подсказывает К.О., соединяют заметки из первой таблицы по принципу «многие ко многим»:

CREATE TABLE `relations` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Номер по порядку',
	`created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Дата/время создания',
	`from` INT(10) UNSIGNED NOT NULL COMMENT 'Источник связи',
	`to` INT(10) UNSIGNED NOT NULL COMMENT 'Назначение связи',
	PRIMARY KEY (`id`),
	UNIQUE INDEX `LINK` (`from`, `to`),
	INDEX `FK_from` (`from`),
	INDEX `FK_to` (`to`),
	CONSTRAINT `FK_from` FOREIGN KEY (`from`) REFERENCES `entities` (`id`) ON UPDATE NO ACTION ON DELETE CASCADE,
	CONSTRAINT `FK_to` FOREIGN KEY (`to`) REFERENCES `entities` (`id`) ON UPDATE NO ACTION ON DELETE CASCADE
)
COMMENT='Связи.  /*TODO: Реализовать резервное копирование удаляемых записей!!!*/'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=12
;

Связи получаются направленные, типа «1->2», «2->4», «3->2», «4->1», и т.д. В итоге выходит нормальный такой ориентированный граф. Проблема в том, что он мне не нужен. Точнее, нужен, но не ориентированный, а простой, без направленных связей. Я, конечно, могу игнорировать направление связи и представлять её как «1-2», «2-4», «3-2», «4-1»...

Но появляется, собственно, фабула: связи «1->2» и «2->1» для MySQL разные и это естественно. Так что UNIQUE INDEX `LINK` (`from`, `to`) мне явно недостаточно, чтобы исключить возможность появления логических дублей существующих связей. И вот тут я залипаю...

Как проще всего избежать появления «противонаправленных» связей?

------------

Решено: Мой вариант

- добавить поле типа varchar, в котором будут храниться from и to (через рпзделитель). Первое число должно быть меньшим.
- проиндексировать
- сделать триггер.

crutch_master ★★★★★ ()
Последнее исправление: crutch_master (всего исправлений: 2)
Ответ на: комментарий от crutch_master

Смысл первых двух действий? А триггеры я и так планирую - для лога правок, так что просто ещё и на INSERT придётся повесить, видимо.

dhameoelin ★★★★★ ()

Как проще всего избежать появления «противонаправленных» связей?

Ну так вставляй так чтобы например from < to, написание логики в БД - прошлый век.

anonymous ()
Ответ на: комментарий от dhameoelin

Смысл первых двух действий?

Поле, чтобы суть 1->2 и 2->1 была одна и также.
Индекс, чтобы не было дублирования двух одинаковых сущностный

crutch_master ★★★★★ ()
Ответ на: комментарий от anonymous

Ну так вставляй так чтобы например from < to

А это как придётся. Не хотелось бы так изгаляться.

написание логики в БД - прошлый век

Ну, вот такой я замшелый. Хочу максимально логику в БД утрамбовать.

Лучше посоветуйте, как на INSERT триггер с проверкой запилить, а то идея хороша, но синтаксис там наркоманский.

dhameoelin ★★★★★ ()
Ответ на: комментарий от crutch_master

Поле, чтобы суть 1->2 и 2->1 была одна и также.
Индекс, чтобы не было дублирования двух одинаковых сущностный

А нормальными механизмами БД не проще пользоваться?

dhameoelin ★★★★★ ()
Ответ на: комментарий от crutch_master

Проверять/менять в триггере на insert перед вставкой?

Именно:

CREATE TRIGGER `relations_before_insert` BEFORE INSERT ON `relations`
FOR EACH ROW
BEGIN
	DECLARE some_var int default 0;
	SELECT COUNT(*) INTO some_var FROM `relations` WHERE relations.from=NEW.to AND relations.to=NEW.from;
	IF some_var > 0 THEN
		SIGNAL SQLSTATE '45000' SET
			MYSQL_ERRNO = 1062,
			MESSAGE_TEXT = "Reverse link already exists";
	END IF;
END
Чего смешного?

Зы. Ну, а что ты от меня хотел?

Явно не таких костылей, как ты предлагал :)

dhameoelin ★★★★★ ()
Последнее исправление: dhameoelin (всего исправлений: 1)
Ответ на: комментарий от dhameoelin

Именно:

Он зачем? Ты можешь просто ставить всегда меньшее число в поле from. У тебя никогда не будет двух связей 1->2 2->1, т.к. будет только 1->2 и при вставке mysql, естественно будет ругаться на индекс. Или я чего-то не понимаю?

не таких

Ну..какие были.

crutch_master ★★★★★ ()
Ответ на: комментарий от crutch_master

Ты можешь просто ставить всегда меньшее число в поле from.

Могу. Но не хочу этим заморачиваться на уровне фронтенда. Фронтенд должен быть максимально прост. По крайней мере, та его часть, что отвечает за наполнение. К аналитической части я ещё не планировал даже подбираться.

У тебя никогда не будет двух связей 1->2 2->1...

Дадада, не будет. Только поиск связей в этой каше превратится в пытку даже для компьютера. Твой вариант идеально неудобен для меня. Странно, что ты не понимаешь, почему...

dhameoelin ★★★★★ ()
Ответ на: комментарий от crutch_master

Он зачем?

За мат тут скор срезают, так что попробую цензурно - он работает. При добавлении новой связи проверяет, нет ли встречной. Если есть - блокирует добавление, имитируя ошибку нарушения уникальности индекса 'LINK'.

dhameoelin ★★★★★ ()
Ответ на: комментарий от crutch_master

Так, стоп. Ты предлагаешь упорядочивать from и to в триггере?! Я попробую, там должна экономиться операция имитации ошибки. Спасибо!

dhameoelin ★★★★★ ()
Вы не можете добавлять комментарии в эту тему. Тема перемещена в архив.