은빛 개발자

10.4.3. The BLOB and TEXT Types

BLOB은 가변형 데이터를 저장하는 바이너리 대용량 객체이다. BLOB은 TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB이 있다. 이것은 저장 용량의 최대 길이에 따른 분류이다. TEXT는 TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT가 있다. TEXT 타입은 BLOB 타입과 일치하며 최대 길이와 용량도 같다.
 
BLOB 컬럼은 바이너리 스트링값(바이트형)을 가지며 TEXT 컬럼은 넌바이너리 스트링값(캐릭터형)을 가진다. BLOB 컬럼은 캐릭터셋이 없으며 컬럼값에서 바이트형의 숫자값으로 비교하고 정렬된다. TEXT 컬럼은 캐릭터셋을 가지며 캐릭터셋의 collation 에 따라 비교하여 정렬된 값을 갖는다.

strict SQL mode가 enable이 아니면 컬럼의 최대 길이를 초과하는 값을 BLOB이나 TEXT 컬럼에 대입할 것이고 그 값은 길이에 맞게 버림을 할 것이며 경고가 발생하게 된다. 공백이 없는 캐릭터를 자르면 (경고보다는)에러가 발생하게 되며 strict SQL mode를 사용하여 값의 삽입을 막을 수 있다.

MySQL 5.1.24의 시작에서, SQL mode와 상관없이, TEXT 컬럼에 삽입된 값에서 생성된 공백의, 초과를 자르면 항상 경고가 발생한다.

TEXT 컬럼이 인덱스되어있으면 인덱스 엔트리의 비교로 공백을 채운다. 즉, 인덱스에 유니크 값을 넣어야 하는데 동일한 키를 넣게 되면 앞서 넣은 값과는 다른 값을 넣으라고 에러가 발생한다. 예를 들자면, 테이블에 'a'를 넣고 다시 'a'를 저장하려고 하면 동일한 키로 인해 에러가 발생한다. 그러나 BOLB 컬럼에서는 그렇지 않다.

많은 값을 넣는데 대부분 BLOB 컬럼을 VARBINARY 컬럼으로 생각하기도 한다. 이와 유사하게 TEXT 컬럼을 VARCHAR 컬럼이라고 생각한다. 그러나 BLOB과 TEXT는 VARBINARY, VARCHAR와 여러 면에서 다르다.

- BLOB과 TEXT에서 인덱스의 prefix길이를 설정해야 하지만 CHAR와 VARCHAR에서는 선택이다.

- BLOB과 TEXT은 디폴트 값을 가질 수 없다.

LONG과 LONG VARCHAR는 MEDIUMTEXT 데이터 타입과 대응한다. 이것은 호환성을 말하는데, TEXT 데이터 타입의 BINARY 속성을 사용하면 컬럼은 캐릭터셋 컬럼의 binary collation으로 지정된다.

MySQL Connector/ODBC 는 BLOB 값을 LONGVARBINARY로 TEXT 값을 LONGVARCHAR로 정의한다.

BLOB과 TEXT 값은 매우 길기 때문에 두 타입을 사용할 때는 다음과 같은 제약사항이 존재한다.

- 정렬할 때는 컬럼의 최초 max_sort_length 바이트를 사용하며 디폴트 값은 1024바이트이다. 이 값은 MySQL서버를 시작할 때 설정하는 max_sort_length=N 옵션에 따라 다르다. 런타임시 max_sort_length 값의 증가를 그룹화하거나 정렬함으로서 더 큰 바이트를 나타낼 수 있다. 클라이언트는 각 세션의 max_sort_length 변수를 변경할 수 있다.

mysql> SET max_sort_length = 2000;mysql> SELECT id, comment FROM t -> ORDER BY comment;

max_sort_length 바이트 이상의 값을 나타내는데 GROUP BY나 ORDER BY를 사용하면 길이가 긴 값을 BLOB이나
TEXT 컬럼에 넣으면 컬럼 값을 고정된 길이의 객체로 변환할 수 있다. 이때 일반적으로 SUBSTRING() 함수를 사용한다. 그 예로, 다음 statement는 comment 컬럼의 2000바이트를 정렬을 뜻한다.

mysql> SELECT id, SUBSTRING(comment,1,2000) FROM t -> ORDER BY SUBSTRING(comment,1,2000);

- BLOB과 TEXT 객체의 최대 크기는 타입에 따라 결정된다. 서버와 클라이언트 간에 전송할 수 있는 최댓값은 가용메모리의 양과 communications 버퍼의 크기에 의해 결정된다. message 버퍼의 크기는 max_allowed_packet 변수로 변경할 수 있으며 서 버와 클라이언트 프로그램을 위해 변경해야 한다. MySQL과 MySQLdump에서는 클라이언트측의 max_allowed packet 변수를 변경할 수 있게 한다. 또한 패킷 사이즈와 저장된 데이터 객체의 사이즈를 비교할 수 있다.

각 BLOB이나 TEXT 값은 개별적으로 할당된 객체에 의해 내부적으로 대응된다. 테이블이 개방되었을 때 컬럼에 하나씩 공간이 할당되는 것은 다른 모든 데이터 타입과는 다르다.
 
BLOB이나 TEXT 컬럼에서 바이너리 데이터를 미디어 파일로 저장하는 것이 나은 경우도 있다. 그러한 데이터를 처리하기에 유용한 MySQL의 string 처리함수를 찾아보는 것이다. 보안이나 여러 가지 이유로 사용된다.

데스크탑에 오라클10g를 설치했지만 테스트를 위해(과연!?) 랩탑에 MySQL5.1을 그냥 설치했다.

나의 설치 과정

1. MySQL 설치
http://dev.mysql.com/downloads/
MySQL Community Server → windows → Windows ZIP/Setup.EXE (x86) →Pick a mirror → 다운로드

1) mysql-5.1.30-win32.zip 파일의 압축을 풀어준다.
2) Setup.exe 파일이 생성된다.
3) 실행
Setup Type : custom
Custom Setup : Developer Components → install, Install path → change
Configuration Type : Detailed Configuration
Server Type : Developer Machine
Database Usage : Multifunctional Database
InnoDB Tablespace Settings : 알아서 경로 설정
Decision Support(DSS)/OLAP 체크
Enable TCP/IP Networking 체크, Add firewall exception for this port 체크, Enable Strict Mode 체크
Best Support For Multilingualism 체크 - 'UTF-8', euc-kr은 아래 메뉴에서 선택 가능
Install As Windows Service 체크(service name확인), Include Bin Directory in Windows PATH 체크
Modify Security Settings 비번 체크
Execute
4) MySQL 실행
도스 창에서 명령 실행, 설정할 때 체크한 server name으로 실행한다.
실행
>net start mysql
중지
>net stop mysql
5)MySQL 접속
관리자 접속 - 비번은 setting에 넣은 비번
>mysql -u root -p
접속 끊기
>quit

2. MySQL 드라이버 설치
http://dev.mysql.com/downloads/
Connectors → Connector/J → 5.1 Source and Binaries (zip) → 다운로드

1) mysql-connector-java-5.1.7.zip 파일의 압축을 풀어준다.
2) mysql-connector-java-5.1.7 폴더가 생성된다.

3. 이클립스에 MySQL 연동
1) Build Path로 드라이버(mysql-connector-java-5.1.7-bin.jar)를 잡아준다.

DB 플러그인(DBedit)은 설치했다가 삭제했다.
설치는 완벽했으나...
SQL문 작성을 위해 파일을 생성하려면 에러 발생이 무한으로 반복되어 이클립스가 날아가는 증상도 반복-_-
대체 무엇이 문제였을까?

1. JDBC드라이버 로딩
오라클 - oracle.jdbc.driver.OracleDriver

  try{
  Class.forName("oracle.jdbc.driver.OracleDriver");//대소문자 주의
  }catch(ClassNotFoundException cnfe){
   cnfe.printStackTrace();
  }

2. 데이터베이스 커넥션 구함

 public Connection getConnection(){
  Connection con=null;
  String dburl = "jdbc:oracle:thin:@localhost:1521:orcl";//127.0.0.1루프백, 도메인, 로컬호스트
  String dboid = "scott";
  String dbopass = "tiger";
    
  try{
  con = DriverManager.getConnection(dburl,dboid,dbopass);
  }
  catch(SQLException e){
   e.printStackTrace();
  }//end catch
  
  return con;
 }
//singletonpattern이용

3. 쿼리 실행을 위한 statement 객체 생성

Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;

   con = SingletonConnection.getInstance().getConnection();
   StringBuffer selectQuery = new StringBuffer();
   selectQuery.append("select num, name, address, phone, age from classinfo");
   pstmt = con.prepareStatement(selectQuery.toString());
  
4. 쿼리 실행

 rs = pstmt.executeQuery();

5. 쿼리 실행 결과 사용

 while(rs.next()){
    //조회된 결과를 자바빈즈로 생성
    xmlDateDTO xdd = new xmlDateDTO(
            rs.getInt("num"),
            rs.getInt("age"),
            rs.getString("name"),
            rs.getString("address"),
            rs.getString("phone"));//빈즈를 list로 관리
    classList.add(xdd);
   }

6. statement 종료

if(rs!=null){rs.close();}
if(pstmt!=null){pstmt.close();}

7. 데이터베이스 커넥션 종료

if(con!=null){con.close();}

※데이터의 무결성
- 데이터의 정확성과 일관성이 보장된 상태
- 무결성 제약 조건은 데이터베이스에 저장된 데이터의 정확성을 보장하기 위해 정확하지 않은 데이터가 데이터베이스 내에 저장되는 것을 방지하기 위한 제약 조건

※무결성의 종류
1. 엔티티 무결성 : 한 엔티티는 중복과 누락이 있을 수가 없음. 동일한 PK를 가질 수 없으며 PK의 속성이 Null을 허용할 수 없음
2. 참조 무결성 : 외래키가 참조하는 다른 개체의 기본키에 해당하는 값이 기본키 값이나 Null이어야 함
3. 속성 무결성 : 속성의 값은 기본값, Null여뷰, 지정된 도메인(데이터타입, 길이)규칙을 준수하여 존재해야 함
4. 사용자 무결성 : 사용자의 의미적 요구사항을 준수해야 함

※제약조건의 종류
1. 엔티티 무결성 : Primary Key. Unique Index
2. 속성 무결성 : Check, Null/Not Null, Default
3. 참조 무결성 : Foreign Key
4. 사용자 정의 무결성 : Trigger, User Define Data Type

※각 요소의 내용

Primary Key	컬럼의 유일성 보장, PK는 Null이 될 수 없음
Unique Index	다중의 보조 키 개념을 지원, 컬럼의 유일성 보장, Null 허용
Foreign Key	테이블 간의 논리적 관계가 유지됨을 보장, FK는 참조하는 테이블의 PK값, Null 허용 Cascaded Option : Master 삭제 시 레코드가 함께 삭제 Nullified Option : Master 삭제 시 해당 값을 Null로 세팅 Restricted Option : FK가 존재하면 Master 레코드를 삭제할 수 없음
Data Type	데이터의 형을 제한하여 데이터 무결성 유지
Check	데이터를 추가할 때마다 SQL서버가 해당 값이 해당 컬럼들에 지정된 Check제약을 위배하는지를 검사함으로써 데이터 무결성 유지
Default	특정 컬럼에 대해 명시적으로 값을 입력하지 않은 경우에 SQL서버가 자동적으로 지정된 값을 삽입할 수 있도록 함으로써 데이터 무결성 유지 INSERT 또는 UPDATE에서 DEFAULT 키워드를 사용할 수 있음
Trigger	테이블의 내용을 변경하려는 특정 사건(DB연산)에 대해서 DBMS가 미리 정의된 일련의 행동(DB연산)을 수행하는 메커니즘, DBMS서버에 의해 자동으로 호출 데이터에 대한 변경을 시도할 때마다 자동적으로 호출 트랜젝션의 철회(rollback)와 같은 동작을 수행가능 저장 프로시저의 특별할 형태로서 SQL의 모든 기능을 이용가능 참조 무결성을 위해 사용가능

※정규화(Normalization)
- 테이블의 속성들이 상호 종속적인 관계를 갖는 특성을 이용하여 테이블을 무손실 분해하는 과정
- 가능한 한 중복을 제거하여 삽입, 삭제, 갱신 이상의 발생 가능성을 줄이는 것

정규화	정규화 내용
1차 정규화	복수의 속성값을 갖는 속성을 분리
2차 정규화	주식별자에 종속적이지 않은 속성을 분리, 부분종속 속성을 분리
3차 정규화	속성에 종속적 속성을 분리, 이전(이행적)종속(Transitive Dependency) 속성을 분리
보이스-코드 정규화	다수의 주식별자를 분리
4차 정규화	다가 종속(Multi-Valued Dependency) 속성 분리
5차 정규화	결합 종속(Join Dependency)일 경우, 두 개 이상의 N개로 분리

※함수적 종속
- 어떤 테이블 R에서 X와 Y를 각각 R의 속성집합의 부분 집합이라고 하면, 속성 X의 값 각각에 대해 시간에 관계없이 항상 속성 Y의 값이 오직 하나만 연관되어 있을 때, Y는 X에 함수적 종속 또는 X가 Y를 함수적으로 결정한다고 하고, X→Y로 표기한다.
- X→Y의 관계를 갖는 속성 X와 Y에서 X를 결정자, Y를 종속자라고 한다.

위 표를 함수적 종속성으로 보자면 다음과 같다.

종속성	개념
함수적 종속성(1NF)	릴레이션의 한 속성 X가 다른 속성 Y를 결정지을 때 Y는 X에서 함수적으로 종속
부분함수적 종속성(2NF)	Y가 X의 부분 집합에 대해서도 함수적으로 종속되는 경우
이행함수적 종속성(3NF)	릴레이션 R에서, A→X이고 X→Y이면 A→Y이다
결정자함수적종속성(BCNF)	함수적 종속이 되는 결정자가 후보키가 아닌 경우
다중값 종속성(4NF)	한 관계에 둘 이상의 독립적 다중값 속성이 존재하는 경우
조인 종속성(5NF)	둘로 나눌 때는 원래의 관계로 회복할 수 없으나 셋 또는 그 이상으로 분리시킬 때는 원래의 관계를 복원할 수 있는 특수한 경우

※완전 함수적 종속
- 어떤 테이블 R에서 속성 Y가 다른 속성 집합 X 전체에 대해 함수적 종속이면서 속성 집합X의 어떠한 진부분집합 Z(Z⊂X)에도 함수적 종속이 아닐 때
- 어떤 속성이 기본키에 대해 완전히 종속적일 때

※부분 함수적 종속
- 어떤 테이블 R에서 속성 Y가 다른 속성 집합 X 전체에 대해 함수적 종속이면서 속성 집합X의 임의의 진부분집합 Z(Z⊂X)에 대해 함수적 종속일 때
- 어떤 속성이 기본키의 일부에 대해 종속적일 때

※정규화와 정규형
- 정규화 : 무엇이 수행되어야 하는 과정
- 정규형 : 무엇이 수행된 결과

테이블에 원자적 데이터가 있다고해서 정규테이블이 된 것은 아닙니다. 완전한 정규 테이블이 되려면 제1정규형(1NF)의 형태여야만 합니다.

1NF형태를 갖추려면 다음의 두 규칙을 만족시켜야 합니다.

1. 각행의 데이터들은 원자적 값을 가져야 한다.

2. 같은 데이터가 여러 열에 반복되지 말아야 한다.


※원자적인 데이터
원자는 쪼갤수 없는 작은 조각의 정보입니다. 데이터가 원자적이면 이는 쪼갤 수  없는 가장 작은 조각으로 쪼개졌다는 의미입니다. 데이터베이스에서 원자적인 데이터란 쓰는 사람의 입장해서 충분하게 원자적인 것이면 됩니다.
- 원자적 데이터 규칙1 : 원자적 데이터로 구성된 열은 그 열에 같은 타입의 데이터를 여러개 가질 수 없다.
- 원자적 데이터 규칙2 : 원자적 데이터로 구성된 테이블은 같은 타입의 데이터를 여러열에 가질 수 없다. 

※기본키
기본키는 각 레코드를 식별하는데 사용합니다. 이는 곧 기본키열의 데이터는 중복될수 없다는 의미입니다.
- 기본키는 NULL이 될수 없습니다.
- 기본키는 레코드가 삽입될 때 값이 있어야 합니다.
- 기본키는 간결해야 합니다.
- 기본키의 값은 변경불가입니다