하늘소리 전하는 자

DB를 건드려 본적이 있는 사람이라면 누구나 엑셀파일을 CSV로 저장하여 DB에 넣는 일을 경험하였을 것이고,
생각만큼 잘 안될 경우를 접하였을 것입니다.
엑셀↔MySQL, 아웃룩주소록↔MySQL, 엑셀↔아웃룩주소록 상호변환에 대해 정리중인 문서를 올립니다.
아무쪼록 내용이 부실하더라도 이해하시고, 미비한 부분은 정리가 완료되는대로 수정하도록 하겠습니다.

1. CSV 파일이란?
흔히 엑셀에 의해서 만들어지는 것으로만 알고 있는 CSV(Comma-Separated Values) 파일은 아무런 구조적 상호관계가 없는 레코드들이 들어있는 플랫파일, 필드의 구분은 Comma(, 0x2C) 레코드의 구분은 엔터(0x0D0A)로 한다.

2. 엑셀에서 CSV로 변환이 가능하지 않거나, 다르게 변환되는 문자
1) 탭
기본적으로 엑셀은 행(Line)과 열(Column)으로 구성되어 있으며, 열의 구분은 탭(0x09)로 되어 있다.
그리고 한개의 셀 안에 탭을 포함시키는 것 또한 불가능하다.(제가 아는 바로는)
그러므로 CSV 파일로 변환할 경우, 탭은 없다. 그렇지만, 텍스트데이터에서 강제적으로 탭을 입력한다면, 값으로 인식될 수 있다.
2) 콤마 ,
CSV이라는 것 자체가 콤마에 의해 구분되므로 엑셀의 셀안에 콤마가 있을 경우 그 필드의 시작과 끝에 모두 쿼테이션마크(" 0x22)를 넣어 둘러싸게 된다.
3) 셀의 처음위치에서의 어퍼스트로피 '
엑셀의 셀의 처음위치에 어퍼스트로피를 사용할 경우 그셀의 (셀 서식 - 표시형식)을 강제적으로 텍스트의 형식으로 변경하고 자기 자신은 보이지 않게 한다. 예를 들어 (셀 서식 - 표시형식)이 일반으로 되어 있을 경우 0123 은 123 이 되고 '0123 은 0123 이 된다.
※ 주의사항 : 첫번째 바이트의 어퍼스트로피만 해당, 나머지는 문자열로 취급
어퍼스트로피가 CSV로 변환될때에는 셀에 보이는 그대로 변환된다.
4) 엔터
엑셀의 셀 안에서 엔터를 사용하는 방법은 "alt + 엔터" 이다. 이것을 CSV로 변환할 경우
사용한 엔터는 \n(0x0A Line Feed) 로 되며 필드의 처음과 끝을 쿼테이션마크로 둘러싸게 된다.
5) 쿼테이션마크 "
콤마가 있을 경우 쿼테이션마크를 사용한다고 했다. 그렇다면 쿼테이션마크는 어떻게 될까...
쿼테이션마크는 쿼테이션마크 2개로 변환된다. 그리고 필드의 처음과 끝을 다시 쿼테이션마크로 둘러싸게 된다.
예를 들어 " 1개는 """" 4개가 된다.
6) 변환이 될지 의심스럽지만 변환되지 않는 문자
그레이브(`), 샵(#), 백슬래쉬(\), 달러($), 퍼센트(%), 기타등등의 문자

3. MySQL 구문을 사용하여 CSV 파일을 MySQL 테이블 안에 넣기
굳이 MySQL이 아니고 다른 DB라도 동일하게 처리되어야 될 것 같기도 하다.
MySQL이 CSV를 곧바로 받아들이기엔 CSV 자체에는 문제가 있다. SQL문을 통해 필드의 구분(fields terminated by ','), 레코드의 구분(lines terminated by '\r\n'), 필드의 둘러싸임(fields enclosed by '\"')이 모두 동일한 방법으로 되어야 하는데,
엑셀로부터 콤마(,)나 쿼테이션마크(")가 포함되어 그 필드만 따로 구분만 되어있다면,
정상적으로 이 구문을 사용할 경우 에러가 보이게 된다.
여기서 주의할 점은 lines terminated by '\r\n' 을 하지 않았을 경우 \r(0x0D Carrige Return)이 맨 마지막 필드안에 포함된다는 점이다.
이럴 경우는 절대로 이 방법은 택하지 말자. CSV전체의 셀 둘러싸임을 모두 동일하게 수정하고, 쿼테이션으로 사용해야 될 부분을 \" 로 수정하고, 싱글쿼테이션으로 사용해야될 부분은 \'로 수정하여야 정상적으로 원하는 결과를 얻을 수 있을 것이다.
실제로 이 일을 해보면 노가다라는 것을 알 수 있을 것이다.
사용조건 : CSV파일의 모든 필드의 구분, 모든 레코드의 구분, 모든 필드둘러싸임 이 동일하여야 한다.
사용방법 : LOAD DATA INFILE ~ 구문을 사용한다.

4. 엑셀로부터 텍스트에디터에 붙여넣고 MySQL 테이블 안에 넣기 => Tab-Seperated Values
가장 손쉽게 할 수 있는 방법이다. 엑셀로부터 셀을 선택후 복사하여 텍스트에디터에 붙여넣으면 필드는 탭구분, 레코드는 엔터로 처리된다. 파일을 마음대로 파일로 저장후 MySQL안에 넣을 때, fields terminated by '\t' lines terminated by '\r\n' 만 해주면 된다.
그러나, 엑셀의 버그인지 잘 모르겠지만, 마지막 필드가 없을 경우 NULL필드로 처리되어 필드구분자가 안들어가게 되기때문에(CSV로 저장할때도 마찬가지임) 필드수가 틀리게 될 수도 있다.
이럴때에는 필드의 순서를 바꾸어 항상 값이 있는 데이터가 가장 끝에 오게 하던지 하는 방법을 사용하면 된다.
사용자가 아니라 개발자가 작업할 때는 이 방법을 적극 추천한다.
사용조건 : 텍스트에디터에서 받쳐 줄 만한 파일크기, 한개의 필드안에 탭이 없을 것
사용방법 : LOAD DATA INFILE ~ 구문을 사용한다.

5. PHP의 fgetcsv 펑션을 사용하여 CSV파일을 MySQL 테이블 안에 넣기 DB에 통째로 넣는 일은 초기에 하게 되고, 평소에는 거의 하지 않는 짓(?)이다.
특별히 CSV파일 업로드 기능을 두거나 하지 않는 경우엔 프로그램까지 동원할 필요가 없을 듯 싶다.
사용조건 : 일반유저가 CSV 파일 업로드 기능 사용, 동일한 테이블, 동일한 필드에 업로드시에만 사용할 것!!
물론 꼭 그래야 되는 것 아니지만, 그래야 후일이 편하다.
대다수의 일반유저가 메모장이나 텍스트에디터이란 것을 모르고, 엑셀과 웹브라우저만 안다.
사용방법 : 파일 업로드후 필드체크하고 fgetscv 펑션을 사용하여 배열화시키고 insert into 구문을 사용한다.

6. PHP가 아닌 csv 파일을 지원하지 않는 다른 CGI에서의 처리방법
PHP의 fgetcsv에 해당하는 함수를 만들고 insert into 구문을 사용한다.

7. DB 테이블로부터 CSV 또는 TSV(Tab-Serperated Value) 파일을 추출하여 엑셀로
여기서 또한 그냥 넣으면 절대로 안된다.

ex) 3-4,5,서울 강남구 신사동,33-8,3/6 => 03월04일,5,서울 강남구 신사동,Aug-33,03월06일

날짜형식이나 숫자형식에 맞으면 데이터타입이 자동으로 변경된다. 날짜 형식으로 바뀌는 건 절망적이다.
눈여겨 보았으면 잘 알겠지만, 앞에 나온 어퍼스트로피(')를 사용하여 텍스트로 구성된 필드가 정상적으로 보이게 한다.

8. DB 테이블로부터 XLS 파일 만들기
엑셀파일을 텍스트파일로 구성할 수 있다. 이방법은 PHP가 웹페이지를 parsing해주는 것과 동일하다.
이때 각 셀에 들어가는 데이터는 html의 table과 동일하게 구성하면 되며, 참조사이트는 매우 많다.
여기서도 주의사항은 텍스트형식의 필드에서는 반드시 어퍼스트로피(')를 사용해야 한다는 점이다.

Posted by MNet

■ CREATE DATABASE database_name (데이타베이스이름)
데이타 베이스 이름은 문자와 숫자, '_' 를 포함하는 32 byte 이내로
작성한다. 이 명령은 새로운 데이타베이스 공간을 생성시킨다.
Oracle로 말하자면 tablespace와 같은 테이블이 들어가는 저장 공간을 말한다.


■ DROP DATABASE database_name존재하는 데이타베이스를 제거한다. 데이타베이스 상에 존재하는 테이블도 모두 삭제 되므로 매우 조심하여 작업하여야 한다.
한번 삭제되면 복구는 불가능 하므로 데이타 베이스 상의 테이블과 데이타가 모두 백업이 되었는지 확인하고 작업한다.


■ CREATE TABLE table_name ( create_definition,... )
데이타베이스 상에 테이블을 생성시키는 명령이다. 자세한 옵션은 다음과 같다.
Oracle 의 table 생성 명령과 거의 유사하며 부가적인 type 이 존재한다.

create_definition:
column_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]
[ PRIMARY KEY ] [reference_definition]
or PRIMARY KEY ( index_column_name,... )
or KEY [index_name] KEY( index_column_name,...)
or INDEX [index_name] ( index_column_name,...)
or UNIQUE [index_name] ( index_column_name,...)
or FOREIGN KEY index_name ( index_column_name,...) [reference_definition]
or CHECK (expr)

type:
TINYINT[(length)] [UNSIGNED] [ZEROFILL]
or SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
or MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
or INT[(length)] [UNSIGNED] [ZEROFILL]
or INTEGER[(length)] [UNSIGNED] [ZEROFILL]
or BIGINT[(length)] [UNSIGNED] [ZEROFILL]
or REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
or DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
or FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
or DECIMAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
or NUMERIC[(length,decimals)] [UNSIGNED] [ZEROFILL]
or CHAR(length) [BINARY],
or VARCHAR(length) [BINARY],
or DATE
or TIME
or TIMESTAMP
or DATETIME
or TINYBLOB
or BLOB
or MEDIUMBLOB
or LONGBLOB
or TINYTEXT
or TEXT
or MEDIUMTEXT
or ENUM(value1,value2,value3...)
or SET(value1,value2,value3...)

index_column_name:
column_name [ (length) ]

reference_definition:


REFERENCES table_name [( index_column_name,...)]
[ MATCH FULL | MATCH PARTIAL]
[ ON DELETE reference_option]
[ ON UPDATE reference_option]

reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT


■ ALTER [IGNORE] TABLE table_name alter_spec [, alter_spec ...]
존재하는 테이블을 변경 및 columnd의 추가 작업시 사용된다.

alter_specification:
ADD [COLUMN] create_definition
or CHANGE [COLUMN] old_column_name create_definition
or ALTER [COLUMN] column_name { SET DEFAULT literal | DROP DEFAULT }
or ADD INDEX [index_name] ( index_column_name,...)
or ADD UNIQUE [index_name] ( index_column_name,...)
or DROP [COLUMN] column_name
or DROP PRIMARY KEY
or DROP INDEX key_name
or RENAME AS new_table_name


■ DROP TABLE table_name [, table_name...]
존재하는 테이블을 제거할 경우 사용되는 명령이다.


■ DELETE FROM table_name WHERE where_definition
존재하는 테이블 상의 데이타를 삭제할때 사용하는 명령이다.


■ SELECT 문
존재하는 테이블 상의 데이타를 조회할때 사용하는 명령이다.
기본적인 사용법은 다음과 같다.

SELECT [STRAIGHT_JOIN] [DISTINCT | ALL] select_expression,...
[INTO OUTFILE 'file_name' ...]
[ FROM table_references
[WHERE where_definition ]
[GROUP BY column,...]
[HAVING where_definition]
[ ORDER BY column [ASC | DESC] ,..] [LIMIT [offset,] rows]
[PROCEDURE procedure_name]]


■ JOIN 문
Oracle 에서 사용하는 join 문보다 좀더 확장된 join 문을 지원한다.

table_reference, table_reference
table_reference [CROSS] JOIN table_reference
table_reference LEFT [OUTER] JOIN table_reference ON conditional-expr
table_reference LEFT [OUTER] JOIN table_reference USING (column-commalist)
table_reference NATURAL LEFT [OUTER] JOIN table_reference
{ oj table_reference LEFT OUTER JOIN table_reference ON conditional-expr }


■ INSERT 문
존재하는 테이블에 데이타를 입력할때 사용한다.

INSERT INTO table [ (column_name,...) ] VALUES (expression,...)
or INSERT INTO table [ (column_name,...) ] SELECT ...


■ REPLACE 문
이 문장은 INSERT문장과 거의 유사하게 동작한다. 같은 레코드가 있을경우 이 레코드를 삭제한후 INSERT 작업을 한다. 같은 ?코드가 없을 경우는 INSERT 와 같이 동작한다.

REPLACE INTO table [ (column_name,...) ] VALUES (expression,...)
or REPLACE INTO table [ (column_name,...) ] SELECT ...


■ LOAD DATA INFILE 문
Server 상에 위치하는 text 파일로 부터 데이타를 읽어서 테이블에 입력작업을 할 경우 사용한다. 매우 속도가 빠르다.

LOAD DATA INFILE 'text_file_name.text' [REPLACE | IGNORE]
INTO TABLE table_name
[FIELDS [TERMINATED BY ',' [OPTIONALLY] ENCLOSED BY '"' ESCAPED BY '\\'
]]
[LINES TERMINATED BY '\n']
[(Field1, Field2...)]

테이블로 부터 데이타를 읽어서 text 파일에 데이타를 저장할경우 다음과 같이 사용한다.

SELECT ...
INTO OUTFILE 'interval.txt' fields terminated by ','
enclosed by '"'
escaped by '\\' lines terminated by '\n'
FROM ...


■ UPDATE 문
테이블 상에 존재하는 데이타를 변경할 경우 사용한다.


UPDATE table SET column=expression,... WHERE where_definition


■ SHOW syntax. Get information about tables, columns...
MySQL 상의 각종 정보를 보여준다. 데이타 베이스, 테이블, column등을 확인할수 있다.

SHOW DATABASES [LIKE wild]
or SHOW TABLES [FROM database] [LIKE wild]
or SHOW COLUMNS FROM table [FROM database] [LIKE wild]
or SHOW INDEX FROM table [FROM database]
or SHOW STATUS
or SHOW VARIABLES [LIKE wild]


■ EXPLAIN syntax. Get information about a SELECT.
select 문과 이때 요구되는 테이블에 대한 정보를 준다.
일반 select 문의 처음에 EXPLAIN 을 추가하면 동작한다.

EXPLAIN SELECT select_options


■ DESCRIBE syntax
존재하는 테이블에서 column 정보를 가지고 온다.

(DESCRIBE | DESC) table [column]


■ LOCK TABLES syntax
테이블에 lock 을 설정하여 타인이 읽지 못하게 할때 사용한다.
주의할 점은 한사람이 lock 을 실행하면 이 사람이 사용하는 모든 테이블이
lock되므로 사용후 꼭 unlock를 사용하여 lock를 풀도록 한다.

LOCK TABLES table_name [AS alias] READ|WRITE [, table_name READ|WRITE]
...
UNLOCK TABLES


■ SET OPTION syntax.
현재 사용중인 세션이 사용되는 동안 지속적으로 영향을 준다.
여러 옵션이 있으므로 참조하기 바란다.

SET [OPTION] SQL_VALUE_OPTION=value, ...


■ GRANT 문 ( 호환명령)
이 명령을 사용하지는 않고 단지 호환성문제 때문에 MySQL 존재한다.
이 명령은 다른 SQL 서버로 부터 MySQL로 쉽게 변환하기 위하여 만들졌다.
MySQL 에서의 특권은 MySQL 허가 테이블을 이용하여 다루어 진다.

GRANT (ALL PRIVILEGES | (SELECT, INSERT, UPDATE, DELETE,
REFERENCES (column list), USAGE))
ON table TO user,... [WITH GRANT OPTION]


■ CREATE INDEX 문 (호환명령)
이 명령을 사용하지는 않고 단지 호환성문제 때문에 MySQL 존재한다.
ALTER TABLE 을 사용하여 새로운 index 를 생성할수 있다.

CREATE [UNIQUE] INDEX index_name ON table_name ( column_name,... )


■ DROP INDEX 문 (호환명령)
이 명령은 항성 성공할 것이다. ALTER TABLE 을 사용하여 index 를 제거할수있다.

DROP INDEX index_name


■ Comment 문법
한줄의 주석은 # 로 하고 주석의 내용이 여러줄일 경우는 /* */ 를 이용하여 주석을 처리할수 있다.


■ CREATE FUNCTION 문법
MySQL 의 기본 함수인 ABS(), constr() 처럼 새로운 사용자 정의 함수를 생성
하는 함수이다. 사용자정의함수의 소스는 C, C++로 작성되어야 하고 동적으로
읽혀지는것이 요구되어 진다. 예제로서 배포판에 보면 udf_example.cc 가 있
고 5개의 새로운 사용자정의함수가 작성되어 있다.

CREATE FUNCTION RETURNS [string|real|integer]
SONAME

DROP FUNCTION

Posted by MNet