MySQL을 사용하여 소규모 데이터베이스 기반 웹 애플리케이션을 개발한 사람이라면 누구나 관계형 데이터베이스에서 테이블을 생성, 검색, 업데이트 및 삭제하는 과정이 비교적 간단한 프로세스라는 것을 알고 있습니다. 이론적으로 가장 일반적인 SQL 문의 사용법을 숙지하고 사용하기로 선택한 서버 측 스크립팅 언어에 익숙하다면 특히 빠른 MyISAM을 사용할 때 MySQL 테이블에 필요한 다양한 작업을 처리하는 데 충분합니다. 데이터베이스 엔진. 그러나 가장 단순한 경우에도 상황은 우리가 생각하는 것보다 더 복잡합니다. 아래에서는 일반적인 예를 사용하여 설명합니다. 거의 매일 업데이트되는 블로그 사이트를 운영하고 있으며 사이트에서 방문자가 게시물에 댓글을 달 수 있다고 가정해 보겠습니다.
이 경우 데이터베이스 스키마에는 최소한 두 개의 MyISAM 테이블이 포함되어야 합니다. 하나는 블로그 게시물을 저장하고 다른 하나는 방문자 의견을 처리하기 위한 것입니다. 분명히 이 두 테이블 사이에는 일대다 관계가 있으므로 데이터 행이 업데이트되거나 삭제될 때 데이터베이스의 무결성이 유지될 수 있도록 두 번째 테이블에 외래 키를 정의해야 합니다.
위와 같은 애플리케이션의 경우 두 테이블의 무결성을 유지하는 것이 심각한 과제일 뿐만 아니라 애플리케이션 수준에서 무결성을 유지해야 한다는 것이 가장 큰 어려움입니다. 이는 MyISAM 테이블이 뛰어난 성능을 제공하기 때문에 트랜잭션 사용이 필요하지 않은 대부분의 웹 프로젝트 개발 중에 취한 접근 방식입니다.
물론 앞서 말했듯이 애플리케이션은 데이터베이스의 무결성과 일관성을 유지해야 하며, 이는 다양한 테이블 간의 관계를 처리하기 위해 보다 복잡한 프로그래밍 논리를 구현해야 함을 의미합니다. 추상화 계층과 ORM 모듈을 사용하여 데이터베이스 액세스를 단순화할 수 있지만 애플리케이션에 필요한 데이터 테이블 수가 증가함에 따라 이를 처리하는 데 필요한 논리는 의심할 여지 없이 더욱 복잡해집니다.
그렇다면 MySQL의 경우 데이터베이스 무결성을 유지하는 데 도움이 되는 데이터베이스 수준의 외래 키 처리 방법이 있습니까? 다행히도 대답은 '예'입니다. MySQL은 InnoDB 테이블도 지원하므로 외래 키 제약 조건을 처리하는 매우 간단한 방법을 사용할 수 있습니다. 이 기능을 사용하면 미리 정의된 관계를 유지하기 위해 테이블의 특정 데이터 행을 업데이트하고 삭제하는 등의 특정 작업을 트리거할 수 있습니다.
모든 것에는 장단점이 있으며, InnoDB 테이블 사용의 주요 단점은 MyISAM보다 느리다는 것입니다. 특히 많은 테이블을 쿼리해야 하는 대규모 애플리케이션에서는 더욱 그렇습니다. 다행스럽게도 최신 버전의 MySQL에 있는 MyISAM 테이블은 외래 키 제약 조건도 지원합니다.
이 기사에서는 InnoDB 테이블에 외래 키 제약 조건을 적용하는 방법을 소개합니다. 또한 간단한 PHP 기반 MySQL 추상 클래스를 사용하여 관련 샘플 코드를 생성합니다. 물론 원하는 다른 서버 측 언어를 사용할 수도 있습니다. 이제 MySQL에 외래 키 제약 조건을 적용하는 방법을 소개합니다.
외래 키 제약 조건을 사용하는 경우
솔직히 MySQL에서 InnoDB 테이블을 사용할 때 반드시 외래 키 제약 조건을 사용할 필요는 없습니다. 그러나 특정 상황에서 외래 키 제약 조건을 사용하기 위해 앞서 언급한 예제의 코드를 사용하여 자세히 설명하겠습니다. 여기에는 블로그 게시물과 댓글을 저장하는 데 사용되는 두 개의 MyISAM 테이블이 포함되어 있습니다.
데이터베이스 스키마를 정의할 때 데이터 행(예: 댓글)을 특정 블로그 기사에 매핑하기 위해 댓글이 저장되는 테이블에 외래 키를 생성하여 두 테이블 사이에 일대다 관계를 설정해야 합니다. 다음은 샘플 MyISAM 테이블을 생성하는 기본 SQL 코드입니다.
`test`.`blogs`가 존재하는 경우 테이블 삭제;
CREATE TABLE `테스트`.`블로그`(
`id` INT(10) 서명되지 않은 AUTO_INCREMENT,
`제목` 텍스트,
'콘텐츠' 텍스트,
`작성자` VARCHAR(45) DEFAULT NULL,
우선순위 키(`id`)
) 엔진=MyISAM 기본 문자 집합=utf8;
`test`.`comments`가 존재하는 경우 테이블 삭제;
CREATE TABLE `테스트`.`설명`(
`id` INT(10) 서명되지 않은 AUTO_INCREMENT,
`blog_id` INT(10) 서명되지 않은 기본 NULL,
`댓글` 텍스트,
`작성자` VARCHAR(45) DEFAULT NULL,
우선순위 키(`id`)
) 엔진=MyISAM 기본 문자 집합=utf8;
위에서 우리는 블로그 애플리케이션의 데이터 계층을 형성하는 두 개의 MyISAM 테이블을 정의했습니다. 보시다시피 첫 번째 테이블은 블로그라고 합니다. 이 테이블은 각 블로그 게시물의 ID, 제목, 콘텐츠, 마지막으로 작성자를 저장하는 데 사용되는 몇 가지 명확한 필드로 구성됩니다. 두 번째 테이블은 comments라는 이름으로, 각 블로그 게시물과 관련된 댓글을 저장하는 데 사용됩니다. 블로그 게시물의 ID를 외래 키로 사용하여 일대다 관계를 설정합니다.
지금까지 우리 작업은 두 개의 간단한 MyISAM 테이블만 생성했기 때문에 쉬웠습니다. 다음으로 우리가 원하는 것은 첫 번째 테이블에서 항목이 삭제될 때 다른 테이블에서 수행해야 하는 작업을 추가로 보여주기 위해 일부 레코드로 이러한 테이블을 채우는 것입니다.
데이터베이스 무결성 업데이트 및 유지
이전 부분에서는 블로그 애플리케이션의 데이터 계층 역할을 하기 위해 두 개의 MyISAM 테이블을 만들었습니다. 물론 위의 소개는 여전히 매우 간단하므로 이에 대해 더 논의해야 합니다. 이를 위해 다음과 같이 SQL 명령을 사용하여 이러한 테이블을 일부 레코드로 채웁니다.
INSERT INTO 블로그 (id, 제목, 콘텐츠, 작성자) VALUES (NULL, '첫 번째 블로그 항목 제목', '첫 번째 블로그 항목 콘텐츠', 'Ian')
INSERT INTO 댓글(id, blog_id, comment, 작성자) VALUES (NULL, 1, '첫 번째 블로그 항목에 댓글 달기', 'Susan Norton'), (NULL, 1, '첫 번째 블로그 항목에 댓글 달기', 'Rose Wilson')
위의 코드는 실제로 독자 Susan과 Rose가 첫 번째 블로그에 댓글을 달았던 상황을 시뮬레이션합니다. 이제 첫 번째 블로그를 다른 게시물로 업데이트한다고 가정해 보겠습니다. 물론 이런 상황도 가능합니다.
이 경우 데이터베이스 일관성을 유지하려면 수동으로 또는 데이터 계층을 처리하는 애플리케이션을 통해 주석 테이블도 그에 따라 업데이트되어야 합니다. 이 예에서는 SQL 명령을 사용하여 다음과 같이 업데이트를 완료합니다.
UPDATE blogs SET id = 2, title = '첫 번째 블로그 항목의 제목', content = '첫 번째 블로그 항목의 내용', 작성자 = 'John Doe' WHERE id = 1
업데이트 댓글 SET blog_id = 2 WHERE blod_id = 1
앞서 언급한 것처럼 첫 번째 블로그의 데이터 항목 내용이 업데이트되었기 때문에 댓글 테이블에도 이러한 변경 사항이 반영되어야 합니다. 물론 실제로는 이 업데이트 작업이 수동이 아닌 애플리케이션 계층에서 완료되어야 합니다. 즉, 이 논리는 서버측 언어를 사용하여 구현되어야 합니다.
이 작업을 완료하기 위해 PHP는 간단한 하위 프로세스를 사용할 수 있지만 실제로 외래 키 제약 조건을 사용하는 경우 주석 테이블의 업데이트 작업을 데이터베이스에 위임할 수 있습니다.
기사 앞부분에서 언급했듯이 InnoDB MySQL 테이블은 이 기능을 완벽하게 지원합니다. 따라서 이후 부분에서는 외래 키 제약 조건을 사용하여 이전 예제 코드를 다시 생성하겠습니다.
데이터베이스에 대한 계단식 업데이트
아래에서는 기본 MyISAM 유형 대신 외래 키 제약 조건과 InnoDB 테이블을 사용하여 이전 예제 코드를 재구성합니다. 이를 수행하려면 먼저 특정 데이터베이스 엔진을 사용할 수 있도록 두 개의 샘플 테이블을 다시 정의하십시오. 이렇게 하려면 다음과 같은 SQL 코드를 사용할 수 있습니다.
`test`.`blogs`가 존재하는 경우 테이블 삭제;
CREATE TABLE `테스트`.`블로그`(
`id` INT(10) 서명되지 않은 AUTO_INCREMENT,
`제목` 텍스트,
'콘텐츠' 텍스트,
`작성자` VARCHAR(45) DEFAULT NULL,
우선순위 키(`id`)
) 엔진=InnoDB 기본 문자셋=utf8;
`test`.`comments`가 존재하는 경우 테이블 삭제;
CREATE TABLE `테스트`.`설명`(
`id` INT(10) 서명되지 않은 AUTO_INCREMENT,
`blog_id` INT(10) 서명되지 않은 기본 NULL,
`댓글` 텍스트,
`작성자` VARCHAR(45) DEFAULT NULL,
우선순위 키(`id`),
KEY `blog_ind`(`blog_id`),
CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`blog_id`) REFERENCES `blogs` (`id`) ON UPDATE CASCADE
) 엔진=InnoDB 기본 문자셋=utf8;
여기 코드와 이전 코드의 명백한 차이점은 두 테이블이 이제 InnoDB 스토리지 엔진을 사용하므로 외래 키 제약 조건을 지원할 수 있다는 것입니다. 또한, 주석 테이블을 정의하는 코드에도 주의를 기울여야 합니다.
CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`blog_id`) REFERENCES `blogs` (`id`) ON UPDATE CASCADE
실제로 이 명령문은 블로그 테이블이 업데이트되면 주석 테이블의 외래 키 blog_id 값도 업데이트되어야 함을 MySQL에 알립니다. 즉, 여기서 수행되는 작업은 MySQL이 계단식 방식으로 데이터베이스 무결성을 유지하도록 하는 것입니다. 즉, 블로그가 업데이트되면 블로그에 연결된 댓글에도 이러한 변경 사항이 즉시 반영되어야 한다는 의미입니다. . 애플리케이션 계층에서는 수행되지 않습니다.
두 개의 예제 MySQL 테이블이 정의되었습니다. 이제 이 두 테이블을 업데이트하는 것은 아래와 같이 UPDATE 문을 실행하는 것만큼 간단합니다.
"UPDATE 블로그 SET ID = 2, title = '첫 번째 블로그 항목 제목', content = '첫 번째 블로그 항목 콘텐츠', 작성자 = 'John Doe' WHERE id = 1"
앞서 언급했듯이 MySQL은 이를 자동으로 처리하므로 주석 테이블을 업데이트할 필요가 없습니다. 또한, 쿼리의 "ON UPDATE" 부분을 제거하거나 "NO ACTION" 및 "RESTRICT"를 지정하여 블로그 테이블의 행을 업데이트하려고 할 때 MySQL이 아무 작업도 수행하지 않도록 할 수 있습니다. 물론, MySQL이 다른 작업을 수행하도록 할 수도 있습니다. 이에 대해서는 후속 기사에서 소개하겠습니다.
위의 소개를 통해 MySQL의 InnoDB 테이블과 함께 외래 키 제약 조건을 사용하는 방법에 대해 모두가 명확하게 이해했다고 생각합니다. 물론 이 편리한 데이터베이스 기능에 대한 이해를 더욱 심화시키기 위해 즉각적인 코드를 작성할 수도 있습니다.