SQL-Ex blog

Подготовка среды 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. Вы можете обратиться к документации MySQL, чтобы увидеть полный синтаксис. Вот описание представленных элементов синтаксиса:

• Предложение DELETE, которое включает подчиненное предложение FROM, является единственным обязательным предложением в операторе DELETE. Это предложение задает таблицу, из которой будут удаляться данные. Вы можете указать несколько таблиц, что включает определение условий соединения, но я сфокусируюсь в этой статье на удалении из одной таблицы. Предложение DELETE также поддерживает использование модификатора IGNORE для возвращения предупреждающих сообщений, а не ошибок при возникновении проблем.


• Предложение WHERE определяет, какие строки удалять на основе одного или более поисковых критериев. Предложение работает во многом подобно предложению WHERE в операторах SELECT и UPDATE. Хотя предложение WHERE не является обязательным, следует быть очень осторожным при выполнении оператора DELETE, который его не содержит. Без предложения WHERE оператор удалит каждую строку в целевой таблице, если не включено предложение LIMIT.


• Предложение ORDER BY задает порядок, в котором должны удаляться строки. Это предложение используется, главным образом, в сочетании с предложением LIMIT, чтобы точнее указать, какие строки следует удалить. Предложение ORDER BY подобно аналогичному предложению в операторах SELECT и UPDATE. Это предложение не является обязательным и не может использоваться для удаления из нескольких таблиц.


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

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

Удаление данных из таблицы 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.