13장. 고급 Mysql 프로그래밍
1. LOAD DATA INFILE 문
- 파일에서 테이블 데이터를 읽어올 때 사용함. 실행 시간을 매우 줄일수 있다.
ex) LOAD DATA INFILE "newbook.txt" INTO TABLE books;
- 위의 예제처럼 하면 newbook.txt에서 데이트를 한 줄 읽어와서 테이블 books에 삽입한다.
- 기본적으로 데이터 필드는 작은따옴표로 둘러싸인 채 탭으로 분리되어 있어야 하며 각 줄은 줄바꿈(\n) 문자로 분이되어야 한다
- LOAD DATA INFILE 문을 사용하려면 FILE 권한을 가지고 있어야 한다
2. 저장 엔진
- MYSQL은 저장 엔진 혹은 테이블 형이라고 불리는 것을 여러가지 지원하고 있다. (다시 말해 테이블의 실제 구현되는 방식을 선택 가능)
- 테이블을 생성할 때 다음과 같이하면 원하는 테이블 형으로 저장 가능
ex) CREATE TABLE table TYPE=type...
- 사용할 수 있는 테이블 형
* MyLSAM : 테이블형의 기본값. 전통적인 ISAM(Indexed Sequential Access Method)에 기반
하고 있는 테이블 형으로 레코드와 파일을 저장해 MyISAM은 ISAM형에 여러가지 장점들을 덧붙
였다. 다른 저장 엔진들에 비해 MyIAM은 테이블 검사하고 수정하는데 필요한 많은 툴을 가지고 있
다. MyISAM 테이블을 압축할 수 있고 전체 내용 검색을 할 수 있다. 하지만 트랜잭션을 안전하게 지원하지 못하여 외래 키도 사용할 수 없다
* MEMORY : (HEAP이라고 알려져 있다) 이 타입의 테이블은 메모리에 저장하며 인덱스는 해싱한
다. 따라서 MEMORY 테이블은 매우 빠르지만 정전이 된다든가 하는 일이 생기면 데이터를 잃어버
리게 된다. 그런 특성 때문에 MEMORY 테이블은 임시 데이터를 저장하기 위한 이상적인 테이블.
CREATE TABLE 문을 사용해서 테이블을 만들 때 MAX_ROWS를 명시해야 하며 그렇지 않았을
경우 테이블이 모든 메모리를 점유해버릴 수 있다. MEMORY 테이블에서는 BLOB, TEXT, AUTO
INCREMENT열을 사용할 수 없다.
* MERGE : 쿼리를할 때 MyISAM 테이블을 한 테이블 처럼 취급할 수 있게 한다. 운영체제에서 두
고 있는 파일 크기 제한을 MERGE 테이블 형을 통해 극복할 수 있다.
* ARCHIVE : 이들 테이블은 양은 많지만 저장 공간은 적게 차지하는 데이터를 저장한다. 이 종류
의 테이블은 오로지 INSERT와 SELECT 쿼리만을 지원하며 DELETE, UPDATE나 REPLACE는 지
원하지 않는다. 또한 인덱스가 사용되지 않는다.
* CSV : 이들 테이블은 서버에 있으며 쉼표로 분리된 값을 포함하는 단 하나의 파일에 저장된다.
이런 종류의 테이블은 마이크로소프트사의 엑셀과 같은 외부 스프레드시트 응용프로그램과 데이터
를 공유해야 할 때 등에 유용하다.
* InnoDB : 이 테이블 형에서는 트랜잭션을 사용할 수 있다. 즉, COMMIT과 ROLLBACK 기능을 제
공한다. InnoDB는 외래 키도 지원한다. MyISAM 테이블보다는 느리지만 트랜잭션을 지원하길 원
한다면 InnoDB를 사용하는 것이 좋겠다.
- SELECT 문이나 INSERT문을 많이 사용한다면 MyISAM 테이블을 사용하는 편이 빠르다.
- SELECT 문이나 INSERT 문이 중간 중간에 섞여있다면 InnoDB를 사용하는 편이 좋다
- 임시 테이블이나 살펴보기를 구현할 때 MEMORY테이블을 사용할 수 있고, 매우 큰 MyISAM 테이블을 만들어야 할 때에 MERGE 테이블을 사용하자
- 테이블을 만들고 난 후에 ALTER TABLE문을 사용해서 테이블 형을 바꿀 수 있다
ex) alter table orders type-innodb;
3. 트랜잭션
- 트랜잭션 ? 데이터베이스 일관성을 유지하기 위한 메커니즘으로 특히 오류가 발생했을 때나 서버가 갑작스레 꺼지는 일에 대비하는 방법.
3-1) 트랜잭션 정의
- 트랜잭션 : 데이터베이스에서 한꺼번에 실행되거나 아예 실행되지 말아야 하는 하나의 쿼리나 여러 쿼리를 말함. 데이터베이스는 트랜잭션이 성공하거나 말거나 상관없이 일관성 있는 상태로 남아 있어야 함
- ACID 협정 (ACID : 트랜잭션이 갖추어야하는 네가지 요구사항)
* Atomiciry : 트랜잭션은 나눌 수 없다. 완전히 실행되던가 실행되지 말아야 한다
* Consistency : 트랜잭션은 데이터베이스가 일관성 있는 상태를 유지시켜야 한다
* Isolation : 완전히 실행되지 못한 트랜잭션은 데이터베이스의 다른 사용자에게 보여져서
는 안된다. 트랜잭션이 완성되기 전에는 분리되어야 한다
* Durability : 데이터베이스에 쓰여진 뒤로는 트랜잭션은 영구적이거나 오랫동안 유효해야 한다
- 데이터베이스에 완전히 쓰여진 트랜잭션은 커밋되었다고 한다.
- 데이터베이스에 완전히 쓰여지지 못한(따라서 트랜잭션이 일어나기 전 상태로 돌아갔을 때) 트랜잭션이 롤백되었다고 함
3-2) InnoDB를 사용한 트랜잭션
- 기본적으로 Mysql은 자동 커밋 모드로 실행 됨.
- 현재 세션에 대해 자동 커밋 모드를 꺼놓는다
ex. set autocommit = 0;
- 만약 자동 커밋이 활성화되어 있다면 다음 명령으로 트랜잭션을 시작함
ex. start transaction;
- 자동 커밋되어있지 않다면 위 명령을 사용할 필요 없음 ( sql 문을 시작할 떄에 트랜잭션이 자동으로 실행되기 때문)
- sql문을 모두 실행해서 트랜잭션을 수행하면 데이터베이스에 커밋함
ex. commit;
- 트랜잭션을 끝내기 전에 만약 마음을 바꿔서 데이터베이스의 예쩐 상태로 바꾸고 싶다면 롤백함
ex. rollback;
3-3) 저장 프로시저
- 저장 프로시저는 Mysql에서 만들어 저장되어 있는 프로그램 함수. SQL문과 특별한 제어 구조를 사용하여 만듬.
- 같은 기능을 여러 응용프로그램이나 플랫폼에서 사용해야 할 때나 기능을 캡슐화해야 할 때가 저장 프로시저가 쓰여야할 곳이다.
- 데이터베이스에 위치한 저장 프로시저는 프로그래밍 언어 중 객체 지향 언어와 유사하다.
- 저장 프로시저를 사용하면 데이터를 접근하는 방식을 제어할 수 있다
ex. 저장 프로시저 선언
delimiter // createprocedure total_order (out tota float) begin select sum(amount) into total from orders; end // delimiter;
- 한줄씩 살펴보자.
- 첫번째 구문 delimiter // : 문장의 끝을 가리키는 구분 문자를 현재 값(대체로 ';'이다)을 뒤에 따라오는 '/' 두개로 바꾼다. 그래야 저장 프로시저 선언이 끝났다고 Mysql이 착각하지 않도록 하면서도 저장 프로시저 안에 ';'을 사용해서 SQL 문장의 끝을 알릴 수 있다
- 다음 구문 create procedure total_orders (out total float) : 실제 프로시저를 만듬.
프로시저는 total이라는 파라미터를 하나 받는데 앞으로 계산할 값.
- OUT이라는 키워드는 이 파라미터가 입력받는 것인지 리턴할 때 쓸 것인지 알려줌
- IN이라고 선언된 파라미터는 프로시저 밖에서 들어오는 값을 뜻하고 INOUT이라고 선언된 파라미터는 프로시저 안에 어떤 값을 들여오고 프로시저에서 바뀐 결과값을 저장하여 리턴
- float : 라파미터의 데이터 형
- 하나 이상의 파라미터를 사용하기 위해서는 ','로 분리해서 계속 열거
- 프로시저 내용은 BEGIN으로 시작하여 END로 끝남
- 프로시저를 선언하고 나면 문장의 끝을 알리는 구분 문자를 ';'으로 다시 되돌림
ex. delimiter;
- 프로시저를 선언하고 나면 call이라는 키워드를 사용해서 프로시저를 호출
ex. call total_orders(@t);
- 이 문장은 결과를 저장할 변수를 넘겨주면서 total_orders 프로시저를 호출하였다. 결과를 알아보기 위해 변수를 살펴본다
ex. select @t;
- 저장 함수 선언
delimiter //
create function add_tax (price float) returns float return price*1.1; // delimiter;
- procedure 키워드 대신 function 키워드를 사용해서 선언
- 파라미터가 모두 IN, 즉 입력 피라미터이기 때문에 굳이 IN이나 OUT을 알려주지 않는다.
3-4) 지역 변수
- BEGIN과 END문 사이에 DECLATE 문을 사용해서 지역 변수를 선언 할 수 있다
- 변수를 사용하는 저장 함수 선언
delimiter // create function add_tax (price float) returns float begin declare tax float default 0.10; return price*(1+tax); end // delimiter;
- decare를 사용하여 변수 선언. declare 다음에 나오는 것은 변수의 이름이고 그 다음은 데이터 형
- default 부분은 옵션이며 변수의 초기값을 정의
