-
Эта статья познакомит читателей с ограничениями внешнего ключа в MySQL. Прочитав эту статью, вы обнаружите, что при обновлении строк данных в родительской таблице легко инициировать каскадное обновление данных дочерней таблицы.
Эта статья познакомит читателей с ограничениями внешнего ключа в MySQL. Прочитав эту статью, вы обнаружите, что при обновлении строк данных в родительской таблице легко инициировать каскадное обновление данных дочерней таблицы.
1. Введение
Любой, кто разработал небольшое веб-приложение, управляемое базой данных, с использованием MySQL, знает, что создание, извлечение, обновление и удаление таблиц в реляционной базе данных — относительно простые процессы. Теоретически, если вы освоили использование наиболее распространенных операторов SQL и знакомы с выбранным вами серверным языком сценариев, этого достаточно для выполнения различных операций, требуемых с таблицами MySQL, особенно при использовании быстрого MyISAM. механизм базы данных, когда. Но даже в самых простых случаях все сложнее, чем мы думаем. Ниже мы используем типичный пример для иллюстрации. Предположим, у вас есть блог, который вы обновляете почти каждый день, и сайт позволяет посетителям комментировать ваши сообщения.
В этом случае наша схема базы данных должна включать как минимум две таблицы MyISAM: одну для хранения сообщений в блоге, а другую для обработки комментариев посетителей. Очевидно, что между этими двумя таблицами существует связь «один ко многим», поэтому нам необходимо определить внешний ключ во второй таблице, чтобы можно было поддерживать целостность базы данных при обновлении или удалении строк данных.
Для приложения, подобного приведенному выше, не только поддержание целостности двух таблиц является серьезной проблемой, но и самая большая трудность заключается в том, что нам приходится поддерживать их целостность на уровне приложения. Этот подход применяется при разработке большинства веб-проектов, не требующих использования транзакций, поскольку таблицы MyISAM обеспечивают отличную производительность.
Конечно, за это приходится платить. Как я уже говорил ранее, приложение должно поддерживать целостность и согласованность базы данных, что означает реализацию более сложной логики программирования для управления отношениями между различными таблицами. Хотя доступ к базе данных можно упростить за счет использования уровней абстракции и модулей ORM, по мере увеличения количества таблиц данных, необходимых приложению, логика, необходимая для их обработки, несомненно, станет более сложной.
Итак, существует ли для MySQL какой-либо метод обработки внешнего ключа на уровне базы данных, который поможет поддерживать целостность базы данных? К счастью, ответ — да! MySQL также может поддерживать таблицы InnoDB, что позволяет нам использовать очень простой способ обработки ограничений внешнего ключа. Эта функция позволяет нам инициировать определенные действия, такие как обновление и удаление определенных строк данных в таблице, для поддержания предопределенных связей.
У всего есть плюсы и минусы, и основным недостатком использования таблиц InnoDB является то, что они медленнее, чем MyISAM, особенно в крупномасштабных приложениях, где необходимо запрашивать множество таблиц. К счастью, таблица MyISAM в новой версии MySQL также поддерживает ограничения внешнего ключа.
В этой статье рассказывается, как применять ограничения внешнего ключа к таблицам InnoDB. Кроме того, мы будем использовать простой абстрактный класс MySQL на основе PHP для создания соответствующего примера кода. Конечно, вы также можете использовать другой ваш любимый серверный язык; Теперь мы начнем знакомить вас с тем, как применять ограничения внешнего ключа к MySQL.
2. Когда использовать ограничения внешнего ключа
Честно говоря, при использовании таблиц InnoDB в MySQL вам не обязательно использовать ограничения внешнего ключа. Однако для ограничений внешнего ключа в определенных ситуациях мы будем использовать код ранее упомянутого примера для подробного объяснения. Он включает в себя две таблицы MyISAM, используемые для хранения сообщений и комментариев блога.
При определении схемы базы данных нам необходимо установить связь «один ко многим» между двумя таблицами, создав внешний ключ в таблице, где хранятся комментарии, для сопоставления строк данных (т. е. комментариев) с конкретной статьей блога. Вот базовый код SQL для создания образца таблицы MyISAM:
УДАЛИТЬ ТАБЛИЦУ, ЕСЛИ СУЩЕСТВУЕТ `test`.`blogs`;
СОЗДАТЬ ТАБЛИЦУ `test`.`blogs` (
`id` INT(10) БЕЗЗНАКОВЫЙ AUTO_INCREMENT,
`заголовок` ТЕКСТ,
`содержание` ТЕКСТ,
`автор` VARCHAR(45) ПО УМОЛЧАНИЮ NULL,
ПРИРОЗНЫЙ КЛЮЧ (`id`)
) ENGINE=MyISAM CHARSET ПО УМОЛЧАНИЮ=utf8;
УДАЛИТЬ ТАБЛИЦУ, ЕСЛИ СУЩЕСТВУЕТ `test`.`comments`;
СОЗДАТЬ ТАБЛИЦУ `test`.`comments` (
`id` INT(10) БЕЗЗНАКОВЫЙ AUTO_INCREMENT,
`blog_id` INT(10) БЕЗЗНАКОВЫЙ ПО УМОЛЧАНИЮ NULL,
`комментарий` ТЕКСТ,
`автор` VARCHAR(45) ПО УМОЛЧАНИЮ NULL,
ПРИРОЗНЫЙ КЛЮЧ (`id`)
) ENGINE=MyISAM CHARSET ПО УМОЛЧАНИЮ=utf8;
Выше мы только что определили две таблицы MyISAM, которые формируют уровень данных приложения блога. Как видите, первая таблица называется «Блоги». Она состоит из нескольких очевидных полей, которые используются для хранения идентификатора, заголовка и содержимого каждого сообщения в блоге и, наконец, автора. Вторая таблица называется «comments» и используется для хранения комментариев, относящихся к каждому сообщению блога. В качестве внешнего ключа для установления связи «один-ко-многим» используется идентификатор сообщения в блоге.
До сих пор наша работа была простой, поскольку мы создали только две простые таблицы MyISAM. Далее мы хотим заполнить эти таблицы некоторыми записями, чтобы дополнительно продемонстрировать, что следует делать в другой таблице, когда запись удаляется в первой таблице.
3. Обновляйте сообщения в блоге и поддерживайте целостность базы данных.
В предыдущей части мы создали две таблицы MyISAM, которые будут служить уровнем данных приложения блога. Конечно, приведенное выше введение все еще очень простое, и нам нужно обсудить его дальше. Для этого мы заполним эти таблицы некоторыми записями с помощью команд SQL следующим образом:
INSERT INTO blogs (id, title, content, автор) ЗНАЧЕНИЯ (NULL, «Название первой записи в блоге», «Содержимое первой записи в блоге», «Ян»)
INSERT INTO комментариев (id, blog_id, comment,author) ЗНАЧЕНИЯ (NULL, 1, «Комментирование первой записи в блоге», «Сьюзан Нортон»), (NULL, 1, «Комментирование первой записи в блоге», «Роуз Уилсон») над код на самом деле имитирует ситуацию, когда читатели Сьюзен и Роуз прокомментировали нашу первую публикацию в блоге. Предположим, теперь мы хотим обновить первый блог еще одной публикацией. Конечно, такая ситуация возможна.
В этом случае, чтобы поддерживать согласованность базы данных, таблица комментариев также должна обновляться соответствующим образом либо вручную, либо с помощью приложения, обрабатывающего уровень данных. В этом примере мы будем использовать команду SQL для завершения обновления следующим образом:
UPDATE blogs SET id = 2, title = «Название первой записи в блоге», content = «Содержимое первой записи в блоге», автор = «Джон Доу», WHERE id = 1
ОБНОВЛЕНИЕ комментариев SET blog_id = 2 WHERE blod_id = 1 Как упоминалось ранее, поскольку содержимое элемента данных первого блога было обновлено, таблица комментариев также должна отражать это изменение. Конечно, на самом деле эта операция обновления должна выполняться на уровне приложения, а не вручную, а это означает, что эта логика должна быть реализована с использованием серверного языка.
Для завершения этой операции PHP может использовать простой подпроцесс, но на самом деле, если используются ограничения внешнего ключа, операцию обновления таблицы комментариев можно делегировать базе данных.
Как упоминалось ранее в статье, таблицы MySQL InnoDB обеспечивают полную поддержку этой функциональности. Поэтому в более поздней части мы будем использовать ограничения внешнего ключа, чтобы воссоздать код предыдущего примера.
4. Каскадное обновление базы данных.
Ниже мы реструктурируем код предыдущего примера, используя ограничения внешнего ключа и таблицу InnoDB (вместо типа MyISAM по умолчанию). Для этого сначала переопределите два примера таблиц, чтобы они могли использовать определенное ядро базы данных. Для этого вы можете использовать код SQL, подобный следующему:
УДАЛИТЬ ТАБЛИЦУ, ЕСЛИ СУЩЕСТВУЕТ `test`.`blogs`;
СОЗДАТЬ ТАБЛИЦУ `test`.`blogs` (
`id` INT(10) БЕЗЗНАКОВЫЙ AUTO_INCREMENT,
`заголовок` ТЕКСТ,
`содержание` ТЕКСТ,
`автор` VARCHAR(45) ПО УМОЛЧАНИЮ NULL,
ПРИРОЗНЫЙ КЛЮЧ (`id`)
) ENGINE=InnoDB CHARSET ПО УМОЛЧАНИЮ=utf8;
УДАЛИТЬ ТАБЛИЦУ, ЕСЛИ СУЩЕСТВУЕТ `test`.`comments`;
СОЗДАТЬ ТАБЛИЦУ `test`.`comments` (
`id` INT(10) БЕЗЗНАКОВЫЙ AUTO_INCREMENT,
`blog_id` INT(10) БЕЗЗНАКОВЫЙ ПО УМОЛЧАНИЮ NULL,
`комментарий` ТЕКСТ,
`автор` VARCHAR(45) ПО УМОЛЧАНИЮ NULL,
ПРИРОЗНЫЙ КЛЮЧ (`id`),
КЛЮЧ `blog_ind` (`blog_id`),
ОГРАНИЧЕНИЕ `comments_ibfk_1` ВНЕШНИЙ КЛЮЧ (`blog_id`) ССЫЛКИ на `блоги` (`id`) НА КАСКАДЕ ОБНОВЛЕНИЙ
) ENGINE=InnoDB DEFAULT CHARSET=utf8; Очевидная разница между этим кодом и предыдущим кодом заключается в том, что эти две таблицы теперь используют механизм хранения InnoDB, поэтому они могут поддерживать ограничения внешнего ключа. Кроме того, нам также необходимо обратить внимание на код, определяющий таблицу комментариев:
ОГРАНИЧЕНИЕ `comments_ibfk_1` FOREIGN KEY (`blog_id`) ССЫЛКИ на `blogs` (`id`) ПРИ КАСКАДЕ ОБНОВЛЕНИЙ Фактически, этот оператор уведомляет MySQLMySQL о том, что при обновлении таблицы блогов значение внешнего ключа blog_id в таблице комментариев должно также обновляться. Другими словами, здесь делается возможность MySQL поддерживать целостность базы данных каскадным образом. Это означает, что при обновлении блога комментарии, связанные с ним, также должны немедленно отражать это изменение. Что важно, так это реализация этой функции. Это не делается на уровне приложения.
Два примера таблиц MySQL определены. Теперь обновить эти две таблицы так же просто, как выполнить оператор UPDATE, как показано ниже:
«UPDATE blogs SET id = 2, title = «Название первой записи в блоге», content = «Содержимое первой записи в блоге», автор = «Джон Доу» WHERE id = 1». Как упоминалось ранее, нам не нужно обновите таблицу комментариев, потому что MySQL обрабатывает все это автоматически. Кроме того, вы можете заставить MySQL ничего не делать при попытке обновить строку в таблице блогов, удалив часть запроса «ON UPDATE» или указав «NO ACTION» и «RESTRICT». Конечно, вы также можете позволить MySQL делать и другие вещи, о которых будет рассказано в последующих статьях.
Я думаю, что благодаря приведенному выше введению каждый имеет четкое представление о том, как использовать ограничения внешнего ключа в сочетании с таблицами InnoDB в MySQL. Конечно, вы также можете написать непосредственный код, чтобы еще больше углубить понимание этой удобной функции базы данных.
5. Резюме
В этой статье мы подробно рассмотрим основы использования ограничений внешнего ключа с таблицами InnoDB в MySQL. Как вы можете видеть в примерах в этой статье, легко инициировать каскадные обновления элементов данных дочерней таблицы при обновлении содержимого родительской таблицы, и это также иллюстрирует, как приложения, обрабатывающие уровень данных, могут быть освобождены от необходимости для этого. Конечно, мы также можем обеспечить тот же каскадный эффект, когда родительская таблица удаляет строки данных, что мы объясним в следующей статье.