Delete from friends where age null сколько записей будет удалено

При удалении с ON DELETE SET NULL на таблице с внешними связями на саму себя удаляются так же связанные записи

Вторая имеет зависимость на саму себя. Далее я пытаюсь удалить одну запись, на которую ссылаются 3 другие записи, но разными командами. 1ая команда прямо указывает через id , что удалять:

DELETE FROM employee WHERE

Все записи, у которых было chief_id=17 теперь имеют chief_id=NULL , так как установлено ON DELETE SET NULL. 2ая команда использует подзапросы, но удаляет всё ТУ ЖЕ САМУЮ запись c id=17 :

DELETE FROM employee WHERE department_id = (SELECT id FROM department WHERE name = 'managment') AND chief_id IS NULL);

Но при это автоматически удаляются так же и все записи с chief_id=17 , то есть которые ссылались на удалённую запись! Я вообще не понимаю, как это может работать. У меня же установлено ON DELETE SET NULL . Но работает только для 1-ой команды почему-то. UPD: Добавляю примеры. department :

╔════╦════════════╗ ║ id ║ name ║ ╠════╬════════════╣ ║ 2 ║ management ║ ╚════╩════════════╝

╔══╦════╦═══════════════╦══════════╦═════════╦════════╗ ║ ║ id ║ department_id ║ chief_id ║ name ║ salary ║ ╠══╬════╬═══════════════╬══════════╬═════════╬════════╣ ║ ║ 5 ║ 2 ║ ║ Rita ║ 5000 ║ ║ ║ 6 ║ 2 ║ 5 ║ John ║ 1400 ║ ║ ║ 7 ║ 2 ║ 5 ║ Fatima ║ 100 ║ ║ ║ 8 ║ 2 ║ 5 ║ Chester ║ 2000 ║ ║ ║ ║ ║ ║ ║ ║ ╚══╩════╩═══════════════╩══════════╩═════════╩════════╝

Теперь если выполнить команду:

DELETE FROM employee WHERE

То результат будет такой:

╔══╦════╦═══════════════╦══════════╦═════════╦════════╗ ║ ║ id ║ department_id ║ chief_id ║ name ║ salary ║ ╠══╬════╬═══════════════╬══════════╬═════════╬════════╣ ║ ║ 8 ║ 2 ║ ║ Chester ║ 2000 ║ ║ ║ 7 ║ 2 ║ ║ Fatima ║ 100 ║ ║ ║ 6 ║ 2 ║ ║ John ║ 1400 ║ ║ ║ ║ ║ ║ ║ ║ ╚══╩════╩═══════════════╩══════════╩═════════╩════════╝

А если вместо нее выполнить команду (ссылается на ту же запись):

DELETE FROM employee WHERE department_id = (SELECT id FROM department WHERE name = 'management') AND chief_id IS NULL;

╔══╦════╦═══════════════╦══════════╦══════╦════════╗ ║ ║ id ║ department_id ║ chief_id ║ name ║ salary ║ ╠══╬════╬═══════════════╬══════════╬══════╬════════╣ ║ ║ ║ ║ ║ ║ ║ ╚══╩════╩═══════════════╩══════════╩══════╩════════╝

Мягкое удаление чаще всего не нужно

Те, кто знаком хотя бы с парой разных окружений баз данных продакшена, скорее всего, знают паттерн «мягкого удаления» (soft deletion): вместо удаления данных напрямую конструкцией DELETE таблицы получают дополнительную временную метку deleted_at и удаление выполняется конструкцией обновления:

UPDATE foo SET deleted_at = now() WHERE > Мягкое удаление необходимо для того, чтобы удаление выполнялось безопаснее и было обратимым. После того, как запись удалили «жёстким» DELETE , теоретически её всё равно можно восстановить, углубившись в слой хранения, но нужно сказать, что вернуть её очень сложно. В теории, при мягком удалении достаточно снова присвоить deleted_at значение NULL , и на этом всё:

-- и волшебным образом всё вернулось на место!! UPDATE foo SET deleted_at = NULL WHERE >

Недостатки: утечка кода

Однако этот паттерн имеет серьёзные недостатки. Во-первых, логика мягкого удаления растекается на все части кода. Все SELECT будут выглядеть примерно так:

SELECT * FROM customer WHERE AND deleted_at IS NULL;

А если забыть о дополнительном предикате для deleted_at , то это может иметь серьёзные последствия: случайно будут возвращены данные, которые больше не должны быть видимы.

Некоторые ORM или ORM-плагины упрощают ситуацию, автоматически добавляя дополнительный оператор deleted_at в каждый запрос (см., например, acts_as_paranoid ), но только то, что записи оказываются скрытыми, не улучшает положение. Если конструкция выполнит запрос к базе данных напрямую, то, скорее всего, о deleted_at забудут, потому что обычно этот оператор добавляет ORM.

Утеря внешних ключей

Ещё одним последствием мягкого удаления является, по сути, утеря внешних ключей.

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

Благодаря внешним ключам попытка удаления такого клиента без удаления накладных будет являться ошибкой:

ERROR: update or delete on table "customer" violates foreign key constraint "invoice_customer_id_fkey" on table "invoice" DETAIL: Key (id)=(64977e2b-40cc-4261-8879-1c1e6243699b) is still referenced from table "invoice".

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

Но из-за мягкого удаления вся система разваливается. Клиент может быть удалён установкой флага deleted_at , но мы снова можем забыть сделать то же самое для накладных. Их внешние ключи по-прежнему валидны, поскольку запись клиента, строго говоря, всё ещё существует, но нет эквивалентной проверки мягкого удаления накладных, поэтому у нас может остаться «удалённый» клиент и его действительные накладные.

Усложняется отсечение данных

За последние несколько лет сделаны серьёзные шаги для обеспечения защиты данных потребителей, например, ввод в действие GDPR в Европе. Поэтому в общем случае стоит опасаться неограниченного по времени хранения данных, а по умолчанию большинство мягко удалённых строк будет находиться именно в таком состоянии.

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

Однако те же внешние ключи, которые из-за мягкого удаления стали практически бесполезными, теперь усложняют эту работу, потому что запись нельзя удалить, не гарантировав удаления и всех её зависимостей ( ON DELETE CASCADE может делать это автоматически, однако использование каскада достаточно опасно и не рекомендуется для данных с повышенной точностью воспроизведения).

К счастью, это всё равно можно делать в системах, поддерживающих CTE, наподобие Postgres, но в конечном итоге придётся писать довольно сложные запросы. Вот фрагмент одного из них, который я написал недавно. Он обеспечивает соответствие требованиям всех внешних ключей, удаляя всё в рамках одной операции:

WITH team_deleted AS ( DELETE FROM team WHERE ( team.archived_at IS NOT NULL AND team.archived_at < @archived_at_horizon::timestamptz ) RETURNING * ), -- -- team resources -- cluster_deleted AS ( DELETE FROM cluster WHERE team_id IN ( SELECT id FROM team_deleted ) OR ( archived_at IS NOT NULL AND archived_at < @archived_at_horizon::timestamptz ) RETURNING * ), invoice_deleted AS ( DELETE FROM invoice WHERE team_id IN ( SELECT id FROM team_deleted ) OR ( archived_at IS NOT NULL AND archived_at < @archived_at_horizon::timestamptz ) RETURNING * ), -- -- cluster + team resources -- subscription_deleted AS ( DELETE FROM subscription WHERE cluster_id IN ( SELECT id FROM cluster_deleted ) OR team_id IN ( SELECT id FROM team_deleted ) RETURNING * ) SELECT 'cluster', array_agg(id) FROM cluster_deleted UNION ALL SELECT 'invoice', array_agg(id) FROM invoice_deleted UNION ALL SELECT 'subscription', array_agg(id) FROM subscription_deleted UNION ALL SELECT 'team', array_agg(id) FROM team_deleted;

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

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

Действительно ли работает восстановление?

Повторюсь, теоретически мягкое удаление является препятствием для случайной утери данных. И в качестве последнего аргумента против него я прошу вас реалистично оценить, действительно ли восстановление удалённого когда-то выполняется.

Когда я работал в Heroku, мы использовали мягкое удаление.

Когда я работал в Stripe, мы использовали мягкое удаление.

И на моей нынешней работе мы используем мягкое удаление.

Насколько я знаю, ни разу за десять с лишним лет ни в одной из этих компаний мягкое удаление не использовалось для восстановления данных. [Дополнение: бывший коллега из Stripe только что написал мне, что, по крайней мере, давно мы иногда восстанавливали записи клиентов для пользователей, попавших в реальные неприятности, но это было редко и не приветствовалось. Спасибо, О. Б.!]

Самая важная причина этого заключается в том, что почти всегда удаление данных также имеет не относящиеся к данным побочные эффекты. Например, были сделаны вызовы к внешним системам, чтобы архивировать записи там, были удалены объекты из блоб-хранилищ или отключены серверы. Такие процессы невозможно обратить, просто присвоив NULL оператору deleted_at — для всех этих операций тоже должны существовать откаты, а их предусматривают редко.

Пару раз в Heroku важный пользователь случайно удалял приложение и хотел его восстановить. У нас использовалось мягкое удаление, и теоретически другие побочные эффекты удаления невозможно было откатить, но нас убеждали не пробовать, потому что никто раньше этого не делал, а пытаться делать что-то в срочном порядке — самое неподходящее для этого время; что-то совершенно точно пойдёт не так, и пользователь окажется в плохом состоянии. Вместо этого мы создавали новое приложение и помогали пользователю скопировать в него окружение и данные из удалённого приложения. То есть даже в ситуации, когда мягкое удаление теоретически наиболее полезно, мы его не использовали.

Альтернатива: таблица удалённых записей

Хотя на практике я так никогда и не видел использования восстановления, мягкое удаление не было совершенно бесполезным, поскольку иногда мы применяли его, чтобы ссылаться на удалённые данные. Обычно это ручной процесс, при котором кому-то нужно изучить удалённый объект, чтобы помочь с тикетом техподдержки или устранением бага.

И хотя я против традиционного паттерна мягкого удаления из-за перечисленных выше недостатков, к счастью, есть компромиссное решение.

Вместо того, чтобы хранить удалённые данные в тех же таблицах, откуда они были удалены, можно создать новую связь специально для хранения всех удалённых данных и с гибким столбцом jsonb , в который можно записывать свойства любой другой таблицы:

CREATE TABLE deleted_record ( id uuid PRIMARY KEY DEFAULT gen_ulid(), deleted_at timestamptz NOT NULL default now(), original_table varchar(200) NOT NULL, original_id uuid NOT NULL, data jsonb NOT NULL );

Тогда удаление будет выглядеть так:

WITH deleted AS ( DELETE FROM customer WHERE RETURNING * ) INSERT INTO deleted_record (original_table, original_id, data) SELECT 'foo', id, to_jsonb(deleted.*) FROM deleted RETURNING *;

Такая система имеет недостаток по сравнению с deleted_at : процесс выбора столбцов в jsonb не так-то легко обратить. Хотя это можно сделать, для этого, скорее всего, пришлось бы создавать запросы для единичных случаев и вмешиваться вручную. Но это вполне может вас устроить — просто вспомните, как часто вам на самом деле приходилось пробовать восстанавливать данные после удаления.

Эта техника решает все перечисленные выше проблемы:

В запросы для обычных неудалённых данных больше не нужно добавлять повсюду deleted_at IS NULL .
Внешние ключи продолжают работать. Попытка удаления записи без её зависимостей будет являться ошибкой.
Жёсткое удаление старых записей по требованию законодательства становится очень простым: DELETE FROM deleted_record WHERE deleted_at < now() - '1 year'::interval .

sql
базы данных
мягкое удаление
жёсткое удаление

Информационная безопасность
Администрирование баз данных

Удаление записей с помощью метода Delete

С помощью метода Delete помечает текущую запись или группу записей в объекте Recordset для удаления. Если объект Recordset не разрешает удаление записи, возникает ошибка. Если вы находитесь в режиме немедленного обновления, удаление происходит в базе данных немедленно. Если запись не может быть успешно удалена (например, из-за нарушений целостности базы данных), она останется в режиме редактирования после вызова update. Это означает, что необходимо отменить обновление с помощью CancelUpdate перед удалением текущей записи (например, с помощью команды Close, Move или NextRecordset).

Если вы находитесь в режиме пакетного обновления, записи помечаются для удаления из кэша, а фактическое удаление происходит при вызове метода UpdateBatch . (Чтобы просмотреть удаленные записи, присвойте свойству Filter значение adFilterAffectedRecords после вызова delete .)

Попытка получить значения полей из удаленной записи приводит к ошибке. После удаления текущей записи удаленная запись остается текущей, пока вы не перейдете к другой записи. После удаления удаленной записи она становится недоступной.

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

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

Если задано динамическое свойство Unique Table и recordset является результатом выполнения операции JOIN для нескольких таблиц, метод Delete удаляет строки только из таблицы, указанной в свойстве Unique Table .

Метод Delete принимает необязательный аргумент, позволяющий указать, какие записи будут затронуты операцией Delete . Единственными допустимыми значениями для этого аргумента являются следующие константы перечисления ADO AffectEnum :

adAffectCurrent Влияет только на текущую запись.
adAffectGroup Влияет только на записи, удовлетворяющие текущему параметру свойства Filter . Чтобы использовать этот параметр, свойству Filter необходимо задать значение FilterGroupEnum или массив Закладок .

В следующем коде показан пример указания adAffectGroup при вызове метода Delete . Этот пример добавляет некоторые записи в образец Recordset и обновляет базу данных. Затем он фильтрует набор записей с помощью константы перечисления фильтра adFilterAffectedRecords , которая оставляет в наборе записей видимыми только новые записи. Наконец, он вызывает метод Delete и указывает, что все записи, удовлетворяющие текущему параметру свойства Filter (новые записи), должны быть удалены.

'BeginDeleteGroup 'add some bogus records With objRs For i = 0 To 8 .AddNew .Fields("CompanyName") = "Shipper Number " & i + 1 .Fields("Phone") = "(425) 555-000" & (i + 1) .Update Next i ' update .UpdateBatch 'filter on newly added records .Filter = adFilterAffectedRecords Debug.Print "Deleting the " & .RecordCount & _ " records you just added." 'delete the newly added bogus records .Delete adAffectGroup .Filter = adFilterNone Debug.Print .RecordCount & " records remain." End With 'EndDeleteGroup

Обратная связь

Были ли сведения на этой странице полезными?

SQL-Ex blog

Оператор DELETE позволяет удалить одну или более строк из таблицы, включая временные таблицы. В целом оператор DELETE является довольно простым, но он не менее необходим в вашем арсенале инструментов DML, наряду с другими операторами DML - SELECT, INSERT, UPDATE .

Подготовка среды MySQL

Как и в предыдущих статьях, я использовал в этой статье те же самые базу данных и таблицы (база данных travel и таблицы manufacturers и airplanes). В данном случае, однако, я рекомендую вам для простоты начать с нуля и перестроить базу данных и таблицы. Для установки базы данных выполните следующий скрипт:

DROP DATABASE IF EXISTS travel; 
CREATE DATABASE travel; 
USE travel; 
CREATE TABLE manufacturers ( 
manufacturer_id INT UNSIGNED NOT NULL AUTO_INCREMENT, 
manufacturer VARCHAR(50) NOT NULL, 
create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 
last_update TIMESTAMP NOT NULL 
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
PRIMARY KEY (manufacturer_id) ) 
ENGINE=InnoDB AUTO_INCREMENT=1001; 
CREATE TABLE airplanes ( 
plane_id INT UNSIGNED NOT NULL AUTO_INCREMENT, 
plane VARCHAR(50) NOT NULL, 
manufacturer_id INT UNSIGNED NOT NULL, 
engine_type VARCHAR(50) NOT NULL, 
engine_count TINYINT NOT NULL, 
max_weight MEDIUMINT UNSIGNED NOT NULL, 
wingspan DECIMAL(5,2) NOT NULL, 
plane_length DECIMAL(5,2) NOT NULL, 
parking_area INT GENERATED ALWAYS AS ((wingspan * plane_length)) STORED, 
icao_code CHAR(4) NOT NULL, 
create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 
last_update TIMESTAMP NOT NULL 
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
PRIMARY KEY (plane_id), 
CONSTRAINT fk_manufacturer_id FOREIGN KEY (manufacturer_id) 
REFERENCES manufacturers (manufacturer_id) ) 
ENGINE=InnoDB AUTO_INCREMENT=101;

После создания базы данных вы можете добавить тестовые данные, необходимые для работы с примерами статьи. Начните с выполнения следующего оператора INSERT, чтобы добавить данные в таблицу manufacturers:

INSERT INTO manufacturers (manufacturer) 
VALUES ('Bombardier'), ('Beagle Aircraft Limited'); 
SELECT * 
FROM manufacturers;

Оператор добавляет две строки в таблицу manufacturers и выводит эти строки. Столбцу manufacturer_id для этих строк должны были быть присвоены значения 1001 и 1002.

Читать:

Как сделать чтобы айфон не включался при зарядке

После подтверждения наличия данных в таблице manufacturers вы должны выполнить следующий оператор INSERT, который заполнит таблицу airplanes:

INSERT INTO airplanes 
(plane, manufacturer_id, engine_type, engine_count, 
wingspan, plane_length, max_weight, icao_code) 
VALUES 
('Learjet 24',1001,'Jet',2,35.58,43.25,13000,'LJ24'), 
('Learjet 24A',1001,'Jet',2,35.58,43.25,12499,'LJ24'), 
('Challenger (BD-100-1A10) 300',1001,'Jet',2,63.83,68.75,38850,'CL30'), 
('Challenger (BD-100-1A10) 350',1001,'Jet',2,69,68.75,40600,'CL30'), 
('Challenger (CL-600-1A11) 600',1001,'Jet',2,64.33,68.42,36000,'CL60'), 
('Challenger (CL-600-2A12) 601',1001,'Jet',2,64.33,68.42,42100,'CL60'), 
('A.109 Airedale',1002,'piston',1,36.33,26.33,2750,'AIRD'), 
('A.61 Terrier',1002,'piston',1,36,23.25,2400,'AUS6'), 
('B.121 Pup',1002,'piston',1,31,23.17,1600,'PUP'), 
('B.206',1002,'piston',2,55,33.67,7500,'BASS'), 
('D.4-108',1002,'piston',1,36,23.33,1900,'D4'), 
('D.5-108 Husky',1002,'piston',1,36,23.17,2400,'D5'); 
SELECT * 
FROM airplanes;

Значения 1001 и 1002 из таблицы manufacturers предоставляют значения внешнего ключа для столбца manufacturer_id в таблице airplanes. После выполнения второго оператора INSERT, запрос SELECT должен подтвердить, что 12 строк был вставлены в таблицу airplanes. Первой строке должно быть присвоено значение 101 в столбце plane_id, для остальных строк значения plane_id должны последовательно возрастать.

Синтаксис оператора DELETE

Базовый синтаксис оператора DELETE довольно прост и включает много тех же самых элементов, которые вы видели в других операторах DML:

DELETE [IGNORE] FROM table_name 
[WHERE where_condition] 
[ORDER BY order_list] 
[LIMIT row_count]

Предложение DELETE, которое включает подчиненное предложение FROM, является единственным обязательным предложением в операторе DELETE. Это предложение задает таблицу, из которой будут удаляться данные. Вы можете указать несколько таблиц, что включает определение условий соединения, но я сфокусируюсь в этой статье на удалении из одной таблицы. Предложение DELETE также поддерживает использование модификатора IGNORE для возвращения предупреждающих сообщений, а не ошибок при возникновении проблем.
Предложение WHERE определяет, какие строки удалять на основе одного или более поисковых критериев. Предложение работает во многом подобно предложению WHERE в операторах SELECT и UPDATE. Хотя предложение WHERE не является обязательным, следует быть очень осторожным при выполнении оператора DELETE, который его не содержит. Без предложения WHERE оператор удалит каждую строку в целевой таблице, если не включено предложение LIMIT.
Предложение ORDER BY задает порядок, в котором должны удаляться строки. Это предложение используется, главным образом, в сочетании с предложением LIMIT, чтобы точнее указать, какие строки следует удалить. Предложение ORDER BY подобно аналогичному предложению в операторах SELECT и UPDATE. Это предложение не является обязательным и не может использоваться для удаления из нескольких таблиц.
Предложение LIMIT ограничивает число удаляемых строк. При использовании с предложением ORDER BY удаляемые строк будут определяться порядком сортировки, заданной этим предложением. Предложение LIMIT не является обязательным и не может использоваться для удаления из нескольких таблиц.

Удаление данных из таблицы MySQL

Как отмечалось выше, предложение DELETE является единственным обязательным предложением оператора DELETE, Если вы выполните оператор DELETE только с этим предложением, он удалит все данные из целевой таблицы - соображение, которое не следует воспринимать легкомысленно. Если именно это вам и нужно, достаточно указать только ключевые слова DELETE и FROM с последующим именем таблицы, как в следующем примере:

DELETE FROM airplanes;

Оператор удалит все данные из таблицы airplanes, если не включен режим безопасности. Режим безопасности обычно включен по умолчанию в экземпляре MySQL, чтобы ограничить возможность обновления или удаления данных в таблице.

Как и для оператора UPDATE, если режим безопасности включен в вашем экземпляре MySQL, вы получите следующее сообщение об ошибке при попытке выполнить приведенный выше оператор:

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.
(Вы используете режим безопасного обновления и пытаетесь обновить таблицу без предложения WHERE, которое использует ключевой столбец. Для отключения безопасного режимы переключите опцию в Preferences -> SQL Editor и повторно соединитесь.)

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

Чтобы временно выключить безопасный режим, используйте оператор SET для изменения значения системной переменной SQL_SAFE_UPDATES на 0 до выполнения оператора DELETE, а затем установкой переменной в 1 после выполнения оператора, как показано в следующем примере:

SET SQL_SAFE_UPDATES = 0; 
DELETE FROM airplanes; 
SET SQL_SAFE_UPDATES = 1;

Операторы SET выключают системную переменную, а затем включают во время текущей сессии. При этом имейте в виду, что если оператор DELETE генерирует ошибку, выполнение оператора прерывается, и второй оператор SET не будет выполнен. Поэтому убедитесь, что вы выполнили этот оператор, чтобы сбросить переменную SQL_SAFE_UPDATES в 1. Заметим также, что оператор SET поддерживает необязательный модификатор GLOBAL, который определяет переменную на глобальном уровне. Однако я рекомендую вам не использовать эту опцию при отключении безопасного удаления. Менее рисковано отключать режим безопасности на уровне сессии, чтобы избежать непреднамеренных модификаций данных. Используйте GLOBAL, только если это существенно в вашей ситуации.

Если безопасный режим выключен, выполнение оператора DELETE не должно вызывать ошибки. После выполнения вы можете подтвердить изменения с помощью простого оператора SELECT, который вернет все данные из таблицы airplanes. Этот оператор не должен возвращать строк.

Добавление предложения WHERE в оператор DELETE

В большинстве случаев вы будете включать предложение WHERE в операторы DELETE для того, чтобы указать строки в таблице, которые должны быть удалены (вместо удаления всех строк). Предложение WHERE определяет одно или более условий поиска, которые точно задают строки для удаления. Чтобы увидеть как это работает, вы должны сначала снова вернуть данные в таблицу airplanes (предполагается, что вы пытались выполнять примеры):

INSERT INTO airplanes 
(plane, manufacturer_id, engine_type, engine_count, 
wingspan, plane_length, max_weight, icao_code) 
VALUES 
('Learjet 24',1001,'Jet',2,35.58,43.25,13000,'LJ24'), 
('Learjet 24A',1001,'Jet',2,35.58,43.25,12499,'LJ24'), 
('Challenger (BD-100-1A10) 300' 
,1001,'Jet',2,63.83,68.75,38850,'CL30'), 
('Challenger (BD-100-1A10) 350' 
,1001,'Jet',2,69,68.75,40600,'CL30'), 
('Challenger (CL-600-1A11) 600' 
,1001,'Jet',2,64.33,68.42,36000,'CL60'), 
('Challenger (CL-600-2A12) 601' 
,1001,'Jet',2,64.33,68.42,42100,'CL60'), 
('A.109 Airedale',1002,'piston',1,36.33,26.33,2750,'AIRD'), 
('A.61 Terrier',1002,'piston',1,36,23.25,2400,'AUS6'), 
('B.121 Pup',1002,'piston',1,31,23.17,1600,'PUP'), 
('B.206',1002,'piston',2,55,33.67,7500,'BASS'), 
('D.4-108',1002,'piston',1,36,23.33,1900,'D4'), 
('D.5-108 Husky',1002,'piston',1,36,23.17,2400,'D5'); 
SELECT * 
FROM airplanes;

Подтвердите, что данные были добавлены в таблицу airplanes, проверив вывод оператора SELECT. Таблица должна теперь содержать те же 12 строк с одним заметным отличием. Первое значение plane_id теперь равно 113, а не 101, поскольку MySQL отслеживает последнее автоинкрементируемое значение, присвоенное строке, даже если эта строка была удалена.

После вставки данных в таблицу airplanes вы можете выполнить следующий оператор DELETE, который содержит базовое предложение WHERE:

DELETE FROM airplanes 
WHERE icao_code = 'pup';

Условие поиска указывает, что значение icao_code должно равняться pup для удаляемых строк. Однако, если попытаться выполнить этот оператор, и безопасный режим включен, MySQL снова вернет ошибку 1175:

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.
(Вы используете безопасный режм обновления и пытаетесь обновить таблицу без предложения WHERE, которое использует ключевой столбец. Для выключения безопасного режима переключите опцию в Preferences -> SQL Editor и повторно соединитесь.)

MySQL возвращает ошибку, поскольку предложение WHERE не содержит ключевого столбца в условиях поиска. Чтобы обойти это, вы можете опять временно отключить режим безопасности на уровне сессии:

SET SQL_SAFE_UPDATES = 0; 
DELETE FROM airplanes 
WHERE icao_code = 'pup'; 
SET SQL_SAFE_UPDATES = 1;

Если выполнить запрос к таблице airplanes после выполнения этих операторов, в таблице теперь окажется только 11 строк, а не 12. Одна строка удовлетворяла условиям поиска, поэтому только она и была удалена.

В тех случаях, когда вы конкретно знаете, какие строки удалить, следует пытаться использовать ключевой столбец в условиях поска, чтобы избежать выключения безопасного режима. Например, строка, удаленная в предыдущем примере имела значение plane_id равное 121. В результаты вы могли переписать оператор DELETE следующим образом:

DELETE FROM airplanes 
WHERE plane_id = 121;

Конечно, не всегда практично использовать ключевой столбец, тогда вам следует определять предложение WHERE способом, налучшим образом подходящим к вашей ситуации, даже если это означает задание нескольких условий поиска, как в следующем примере:

SET SQL_SAFE_UPDATES = 0; 
DELETE FROM airplanes 
WHERE engine_type = 'piston' AND max_weight < 2500 ;
SET SQL_SAFE_UPDATES = 1;

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

Предложение WHERE включает два условия поиска. Первое указывает, что значение engine_type должно быть piston, а второе означает, что max_weight должно быть меньше 2500. Условия поиска соединяются логическим оператором AND, который означает, что оба условия должны быть истинны для удаляемых строк.

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

Добавление предложений ORDER BY и LIMIT в оператор DELETE

Предложения ORDER BY и LIMIT вместе помогают лучше управлять удалением строк из таблицы. Для того, чтобы увидеть их работу, начнем с возвращения строки в таблицу airplanes:

INSERT INTO airplanes 
(plane, manufacturer_id, engine_type, engine_count, 
wingspan, plane_length, max_weight, icao_code) 
VALUES 
('D.4-108',1002,'piston',1,36,23.33,1900,'D4');

Поскольку эта строка добавляется позже по времени, когда были добавлены предыдущие строки, она будет иметь отличное от других строк значение create_date. (Значение - временная метка.) Вы можете использовать это значение, чтобы выделить эту строку при построении оператора DELETE:

DELETE FROM airplanes 
ORDER BY create_date DESC 
LIMIT 1;

Обратите внимание, что оператор не включает предложения WHERE с ключевым столбцом, и что отсутствуют операторы SET. Вы можете так поступить здесь, поскольку оператор DELETE включает предложение LIMIT. При необходимости вы можете включить предложение WHERE с предложениями ORDER BY и LIMIT, но в этом нет необходимости в данном случае.

Предложение ORDER BY определяет, что строки должны удаляться на основе значений create_date, отсортированным в убывающем порядке. Это гарантрует, что последняя вставленная строка является первой удаляемой строкой в предположении уникальности значений create_date. Предложение LIMIT затем указывает, что удаляться должна только одна строка. Это будет первая строка, как определено в предложении ORDER BY. Вы можете применить этот прием при удалении архивных данных, за тем исключением, что указываться в предложении LIMIT будет не 1, а значения 100, 1000 или т.п.

Сложно сказать, насколько часто вы будете использовать предложения ORDER BY и LIMIT в ваших запросах. Но полезно знать, что у вас есть такой вариант, если вы хотите применить подобную логику при удалении данных из таблиц вашей базы данных.

Вы можете также использовать эти предложения независимо друг от друга. Например, вы можете использовать предложение LIMIT, если вам требуется удалить большое число строк из таблицы, и вы беспокоитесь о влиянии удаления на производительность базы данных. Вместо удаления сразу всех строк вы можете удалять их пакетами на основе числа строк, указанного в предложении LIMIT. Затем вы можете просто повторять оператор DELETE, пока не будут удалены все целевые строки.

Использование модификатора IGNORE в операторе DELETE

Как отмечалось ранее, оператор DELETE поддерживает использование необязательного модификатора IGNORE, который вы также видели в операторах INSERT и UPDATE. При использовании IGNORE оператор DELETE будет возвращать предупреждение, а не ошибку, при возникновении проблем. Кроме того, MySQL продолжит выполнение оператора. Если IGNORE не используется, MySQL будет возвращать ошибку и прерывать выполнение оператора, включая любые операторы в пакете, которые следуют за оператором DELETE.

Чтобы увидеть как это работает, начнем с выполнения следующего оператора INSERT, который добавляет несколько строк в таблицу manufacturers:

INSERT INTO manufacturers (manufacturer) 
VALUES ('Airbus'), ('Beechcraft'), ('Cessna'), ('Piper'); 
SELECT * 
FROM manufacturers;

В выводе этого пакета вы должны обнаружить, что строка Airbus имеет значение manufacturer_id, равное 1003. Вы будете использовать это значение как внешний ключ при добавлении строки в таблицу airplanes для самолета Airbus. Чтобы добавить эту строку, выполните следующий оператор INSERT:

INSERT INTO airplanes 
(plane, manufacturer_id, engine_type, engine_count, 
wingspan, plane_length, max_weight, icao_code) 
VALUES 
('A220-100',1003,'Jet',2,115.08,114.75,134000,'BCS1');

Предположим теперь, что вы хотите удалить все строки, которые вы только что добавили в таблицу manufacturers. Вы могли бы попытаться выполнить следующий оператор DELETE, используя значения manufacturer_id для этих строк:

DELETE FROM manufacturers 
WHERE manufacturer_id IN (1003, 1004, 1005, 1006);

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

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`travel`.`airplanes`, CONSTRAINT `fk_manufacturer_id` FOREIGN KEY (`manufacturer_id`) REFERENCES `manufacturers` (`manufacturer_id`))

MySQL вернула эту ошибку, поскольку вы пытались удалить строку, на которую ссылалась таблица airplanes. В результате весь оператор потерпел неудачу, и никакие строки не были удалены. Однако вы можете гарантировать продолжение выполнения оператора DELETE, даже если одно из удалений сбоит, включив модификатор IGNORE:

DELETE IGNORE FROM manufacturers 
WHERE manufacturer_id IN (1003, 1004, 1005, 1006);

Теперь оператор возвращает следующее сообщение, которое показывает число строк, которое было обработано, и предупреждение:

3 row(s) affected, 1 warning(s): 1451 Cannot delete or update a parent row: a foreign key constraint fails (`travel`.`airplanes`, CONSTRAINT `fk_manufacturer_id` FOREIGN KEY (`manufacturer_id`) REFERENCES `manufacturers` (`manufacturer_id`))

Из этого сообщения следует, что три строки были удалены, и что имелось нарушение ограничения внешнего ключа. Если обратиться к таблице manufacturers, вы обнаружите, что она содержит теперь только три строки, включая одну для Airbus. Чтобы удалить все данные Airbus, вы должны сначала удалить любые ссылающиеся записи из таблицы airplanes:

DELETE FROM airplanes 
WHERE manufacturer_id = 1003;

Затем вы сможете выполнить следующий оператор DELETE для удаления записи Airbus из таблицы manufacturers, в которой останется только две строки:

DELETE FROM manufacturers 
WHERE manufacturer_id = 1003;

Модификатор IGNORE может оказаться полезным, когда вам нужно выполнять удаление по расписанию, и вы хотите, чтобы оператор продолжал выполяться, даже если некоторые строки не могут быть удалены. Это особенно полезно при удалени больших наборов данных. После удаления основной массы записей вы можете вернуться к рассмотрению предупреждений. Имейте в виду, что модификатор IGNORE работает только для ошибок определенных типов.

Работа с оператором DELETE в MySQL

Наряду с операторами SELECT, INSERT и UPDATE, оператор DELETE представляет наиболее важный оператор, который используется для работы с данными MySQL. Однако это не все операторы DML. MySQL также поддерживает такие операторы DML, как CALL, LOAD DATA, REPLACE и TABLE.

Но рассмотренные в этой серии четыре оператора отлично подходят для начала работы с данными, при этом оператор DELETE завершает картину. Этот оператор делает возможным легко удалять данные, являющиеся некорректными или устаревшими. Фактически оператор слишком легок в использовании, и вы должны быть осторожны, чтобы непреднамеренно не удалить данные по ошибке. Тем не менее, оператор DELETE чрезвычайно полезен, и вы должны быть уверены, что вполне понимаете, как его использовать наряду с операторами SELECT, INSERT и UPDATE.

Обратные ссылки

Нет обратных ссылок