Owl-Networks Archive

Perl / DBI / MySQL : MySQL에서 바로 이전/바로 이후 열(row)을 선택(SELECT)하기

| 분류: Perl | 최초 작성: 2010-05-25 22:37:45 |

블로그 툴을 제작하면서, 게시물 보기 모드 하단에 이전 글 / 다음 글 이동 링크를 달기 위해 온갖 삽질을 다 했다. (사실 처음에는 여러 글 보기 모드에서처럼 페이지 이동 메뉴를 달려고 했으나, 그게 만만치 않음을 깨닫고 그냥 이전 글 / 다음 글 이동 메뉴로 만족하기로 한 것이다.)

처음에는 모든 게시물의 게시물 코드를 배열로 만들어놓고, 거기서 +1/-1 을 하려고 했다.

(주. MySQL 상에서 게시물 코드는 article_db 라는 테이블에 RowID 라는 이름의 필드로 저장되며, 이 필드에는 AUTO_INCREMENT 옵션이 지정되어 있어 자동으로 게시물 코드를 생성하게 된다.)

use DBI;

my @IDX;  # 인덱스를 저장할 배열 정의.
my @TempArray;  # 임시 배열

my $dbh = DBI->connect( $dsn, $userid, $password );
my $query = $dbh->prepare(qq{
  SELECT RowID FROM article_db ORDER BY RowID DESC
});
$query->execute;
while( @TempArray = $query->fetchrow ) {
  @IDX = ( @IDX, $TempArray[0] );
}
$query->finish;
undef @TempArray;
undef $query;
$dbh->disconnect;
undef $dbh;

그러나 DB에서 RowID 를 모두 가져오는 데에 시간이 엄청나게 들어가는 것을 보고 포기. 현재야 3-400개 정도밖에 안 되는 사이즈이지만, 크기가 더 커지면 대책이 없었기 때문이다. 게다가, 이렇게 코드를 작성한다면 페이지 이동 메뉴를 표시할 때마다 인덱스 전체를 전방위 스캔을 해야 하고, 그것을 회피하기 위해서는 게시물 코드 이외에 인덱스의 위치를 가리키는 상대 번호를 함께 부여해야 하는 문제가 있었다. 무엇보다도, 파일시스템 DB를 버리고 MySQL 이라는 DBMS 를 사용하게 되는 이점을 전혀 누릴 수 없는 방법이었다.

그렇다면 현재의 RowID (게시물의 절대 코드) 에 +1/-1 을 해서 바로 읽어오는 것은? 게시물 코드를 AUTO_INCREMENT 옵션으로 자동으로 생성하기 때문에, 게시물들은 순차적으로 1씩 증가하는 게시물 코드를 갖게 되므로, 이것도 가능한 방법이었다. 예를 들면, 다음의 코드는 바로 전 (바로 최근) 의 게시물 코드를 (존재한다면) 가져오게 된다.

(주. 블로그에서의 게시물 출력 순서는 가장 최근 게시물로부터 오래 된 게시물의 순서대로 출력된다고 가정하며, 따라서 바로 이전 게시물은 현재보다 하나 더 최근의 게시물을, 바로 다음 게시물은 현재보다 하나 더 오래 된 게시물을 의미한다. 이 점 착오 없으시기 바란다.)

use DBI;

my $Cur_code = 51;  # 현재의 게시물 코드 (예시).
my $Prev_code = $Cur_code + 1;  # 바로 전 (바로 최근) 의 게시물 코드

my $dbh = DBI->connect( $dsn, $userid, $password );
my $query = $dbh->prepare(qq{
  SELECT RowID FROM article_db WHERE RowID = '$Prev_code' LIMIT 1
});
$query->execute;
@TempArray = $query->fetchrow;
$query->finish;
undef $query;
$dbh->disconnect;
undef $dbh;

사실 제일 간단한 방법이었다. 그러나 금방 문제가 발견되었다. 만약 중간에 게시물이 삭제되는 경우, 예를 들면 현재 38번 게시물에서 바로 다음 게시물을 찾는다면, 38-1=37번 게시물을 읽어오게 될 것이다. 그러나, 만약 37번 게시물이 삭제되거나 하는 등의 이유로 존재하지 않는다면, 중간에 내비게이션 링크가 끊기는 사태가 벌어지게 된다. (혹은 SQL Error가 발생할 수도 있다.)

한참을 고민하다가, 결국 구글신에게 도움을 요청했다. 그리고, 내가 지금까지 했던 모든 시행착오를 포함한 멋진 해답이 있었다*. 예를 들자면, 아래의 코드는 현재 게시물의 바로 다음 게시물 코드를 찾아낸다.

use DBI;

my $Cur_code = 51;  # 현재의 게시물 코드 (예시).
my $Next_code;  # 바로 다음 (이 게시물보다 바로 전) 의 게시물 코드

my $dbh = DBI->connect( $dsn, $userid, $password );
my $query = $dbh->prepare(qq{
  SELECT RowID FROM article_db WHERE RowID < $Cur_code ORDER BY RowID DESC LIMIT 1
});
$query->execute;
$Next_code = $query->fetchrow;
$query->finish;
undef $query;
$dbh->disconnect;
undef $dbh;

즉, 현재의 게시물 코드보다 작은 게시물 코드(RowID < $Cur_code)를 숫자가 큰 것부터(DESC) 순서대로 늘어놓은 후, 가장 위의 것 하나를 취하는 방법이다. 이렇게 하면 중간에 비는 게시물 코드가 생긴다 하더라도, MySQL에 의해서 항상 가장 근접한 다음의 코드를 찾아주게 된다. 만약 반대로, 현재 게시물의 하나 이전 게시물 코드를 찾고자 한다면, 다음과 같이 쿼리를 바꾸면 된다.

my $query = $dbh->prepare(qq{
SELECT RowID FROM article_db WHERE RowID > $Cur_code ORDER BY RowID ASC LIMIT 1
});

즉, 현재의 게시물 코드보다 큰 게시물 코드(RowID > $Cur_code)를 숫자가 작은 것부터(ASC) 늘어놓은 후 가장 위의 것 하나를 취하면 된다.

만약, 비밀글 지정으로 인하여 비밀글 지정된 게시물을 건너 뛰어야 한다면, 다음과 같이 한번 더 쿼리를 수정하면 되겠다. (단, 비밀글 기능은 HIDDEN 필드의 값이 0인 경우 보여주고, 1인 경우 비밀글로 하여 보여주지 않는 것으로 한다.)

my $query = $dbh->prepare(qq{
SELECT RowID FROM article_db WHERE RowID > $Cur_code AND HIDDEN = 0 ORDER BY RowID ASC LIMIT 1
});

* 이 게시물은 다음의 게시물로부터 해답을 얻어 작성된 것입니다. 필자의 고민을 한방에 해결해 준 원문의 작성자에게 깊이 감사드립니다. 필자가 했던 모든 삽질까지 망라되어 있는 이 게시물을 발견하고, 필자는 약 10초간 아무 것도 할 수 없이 멍하니 모니터만 쳐다보고 있었습니다. (...) 덕분에, 이 게시물은 (의도와는 달리) 사실상 원 게시물의 번역본이나 마찬가지인 게 되어 버렸습니다.

http://www.scottklarr.com/topic/111/how-to-select-previousnext-rows-in-mysql/

☞ 태그: DBI, ROWS, MySQL, SELECT,

☞ 트랙백 접수 모듈이 설치되지 않았습니다.

☞ 덧글이 없고, 트랙백이 없습니다.

덧글을 남기시려면 여기를 클릭하십시오.

[489] < [360] [359] [358] [357] [355] ... [352] ... [350] [346] [345] [344] [341] > [19]