Автоматическая очистка SQL с таблицами 0 ..1: 1 .. n в MySQL

ProgramBox

Автоматическая очистка SQL с таблицами 0 ..1: 1 .. n в MySQL

Post author:admin
Запись опубликована:23 апреля, 2023
Post category:Вопросы по программированию

#mysql #sql #resource-cleanup

#mysql #sql #очистка ресурсов

Вопрос:

Я пишу приложение, которое требует от всех пользователей доступа к данным в центральной базе данных с использованием MySQL, и мне было кое-что интересно.

Допустим, у меня есть эта настройка.

 CREATE TABLE A
(
    id   INT PRIMARY KEY AUTO_INCREMENT,
    data INT NOT NULL;
);

CREATE TABLE B
(
   id    INT PRIMARY KEY AUTO_INCREMENT,
   a_id  INT,
   FOREIGN KEY (a_id) REFERENCES A(id) ON DELETE SET NULL
);

Теперь я хочу настроить это так, чтобы на таблицу A всегда ссылалась строка в таблице B. Однако строка в таблице B может ссылаться, а может и не ссылаться на строку в таблице A. Соотношение равно 1: n в том смысле, что несколько строк в таблице B могут ссылаться на одну строку в таблице A. Мне просто интересно, возможно ли, чтобы база данных MySQL автоматически удаляла строку в A, если на нее больше не ссылается ни одна строка в таблице B.

Идея здесь в том, что я могу просто установить a_id в таблице B равным NULL и заставить базу данных очистить все, что осталось. Я думаю, что теперь, когда я думаю об этом, это похоже на сборку мусора Java. Если нет ключа для автоматического применения ограничения, сработает ли триггер, выполняемый после обновления?

РЕДАКТИРОВАТЬ: добавление дополнительного ограничения взаимосвязи.

1. Итак, вы хотите реализовать 1::1..n взаимосвязь, верно?

2. Возможно, вам следует поместить это в заголовок. Я не думаю, что это легко в MySQL.

3. И поскольку вы говорите, что «строка в таблице B может ссылаться, а может и не ссылаться на строку в таблице A» , взаимосвязь скорее 0..1::1..n

4. Это становится действительно придирчивым к вопросу, но я думаю, вы правы.

5. Я комментирую только потому, что, во-первых, это не то общее, 1::0..n которое обычно обозначается как 1:n , а во-вторых, потому что отклонение на два пункта от общего делает работу с ним еще сложнее, особенно в MySQL. Существуют трудности с обеспечением правильной вставки и удаления.

Ответ №1:

Запустите следующий запрос с определенным интервалом:

 DELETE tableA
FROM tableA LEFT JOIN tableB B ON A.id = B.a_id
WHERE B.a_id IS NULL;

Или, для поддержания согласованности в реальном времени, вы могли бы создать триггер onChange в TableB, который выполняет аналогичную функцию.

1. Я думаю, что это сработало бы в других базах данных, но MySQL, по-видимому, не позволяет вам обновлять таблицу, для которой вы используете SELECT. Каждый раз, когда я запускаю это, я получаю сообщение об ошибке «Вы не можете указать целевую таблицу ‘tablea’ для обновления в предложении FROM». Похоже, что причиной является вложенный оператор SELECT. Возможно, я смогу выяснить, что делать, проанализировав его. Спасибо.

Ответ №2:

Есть ли причина для структуры таблицы, которую вы используете? То, как вы используете внешние ключи, мне кажется обратным. Вместо размещения вашего ключа в таблице B, вы могли бы переместить его в таблицу A.

Это дало бы вам структуру, которая больше походила бы:

 tableA columns        tableB columns
  id
  b_id                id
  [values]            [values]

  fk: a.b_id=b.id

Запись, добавленная в таблицу A, потребует соответствующего поля в таблице B, но B может не иметь значений в таблице A. Затем, если вы хотите очистить значения в таблице A, вы могли бы просто использовать:

 delete from tableA where b_id=[recordIdToNull];

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

1. Я вижу, как это помогло бы, если бы соотношение было 1: 1. Я забыл упомянуть, что это отношение 1: n, в котором многие строки в TableB могут ссылаться на одну строку в TableA. Если я помещу внешний ключ в TableA, это будет означать, что мне понадобится строка в таблице A для каждой ссылки, правильно?

2. Теперь я понимаю, о чем вы пытались спросить. Если вы посмотрите в руководстве mysql на предмет удаления, вы действительно можете использовать инструкцию delete, которую NGLN написал с одной модификацией — вы должны указать, на какую таблицу следует ориентироваться в начале удаления (т. Е. удалить TableA из TableA left join …)