1. VARCHAR, NVARCHAR

흔히 varchar(n)에 n은 바이트를 나타내고 nvarchar(n)에 n은 글자에 길이를 나타내는 것으로 알고 있었다.

 

하지만 이는 DBMS별로 다르고

MariaDB, Mysql에서는 varchar(n)역시 바이트제한이 아닌 글자의 길이를 나타낸다.

ex) varchar(10)이면 한글 영어 상관없이 10글자까지만 insert 가능하다

 

그럼에도 불구하고 MariaDB에 nvarchar가 존재하는데 그럼 도대체 일반 varchar와 뭐가 다른건지 궁금해서 알아보았다.

 

https://mariadb.com/kb/en/varchar/

마리아디비 문서에서 varchar페이지에 일부분이다.

대충 내용은 이렇다

varchar는 CHARACTER VARYING의 약어이고

nvarchar는 NATIONAL VARCHAR의 약어이다.

NATIONAL VARCHAR는 미리정해진 문자집합을 사용해야하는 varchar이며. mariadb는 utf8을 사용한다고 한다.

 

그럼결국 mariadb에서의 nvarchar는 문자집합 utf8을 사용하는 varchar일 뿐인걸까? 라고 생각하는 찰나 밑에 아예 그 생각이 맞다고 나와있다.

https://mariadb.com/kb/en/varchar/

 

2. utf8, utf8mb4

utf8은 최대 3바이트까지 지원하는데

🎀와 같은 이모지는 이모지당 4바이트를 사용한다.

그래서 데이터베이스에 이모지를 저장하려면 utf8이아닌 utf8mb4문자집합을 사용해야한다.

 

그래서 요즘 새로 만드는 데이터베이스나 테이블들은 기본설정부터 utf8mb4를 주로 사용하게 되는데 아까 nvarchar컬럼은 utf8사용이 강제된다 했으니 이 때 어떻게 될까 궁금해졌고 하는깅메 더불어 이 포스팅 내용 전체를 테스트 해보게 되었다.

 

3. 테스트

CREATE TABLE `abcde` (
  `seq`  INT UNSIGNED NOT NULL UNIQUE AUTO_INCREMENT,
  `test1` VARCHAR(10) NULL,
  `test2` NVARCHAR(10) NULL,
  `test3` VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
  `test4` VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
  PRIMARY KEY(seq)
) ENGINE=InnoDB;
DEFAULT CHARACTER SET = 'utf8mb4'
DEFAULT COLLATE = 'utf8mb4_general_ci'

따로 컬럼 문자집합을 정하지 않은 varchar와 nvarchar 컬럼 1개씩

특정 문자집합을 각각 utf8mb4, utf8로 정해놓은 varchar 컬럼 1개씩 만들었다.

아마 예상으로는 기본 문자집합이 utf8mb4이므로

test1 - utf8mb4

test2 - utf8

test3 - utf8mb4

test4 - utf8

이렇게 문자집합이 정해질것이다.

 

-- 1번 영어 10자 이하
insert into abcde( test1,test2,test3,test4 )
values( 'abcde', 'abcde', 'abcde', 'abcde' );

-- 2번 한글 10자 이하
insert into abcde( test1,test2,test3,test4 )
values( '테스트중임', '테스트중임', '테스트중임', '테스트중임' );

-- 3번 한글 10자
insert into abcde( test1,test2,test3,test4 )
values( '테스트중임열글자채워', '테스트중임열글자채워', '테스트중임열글자채워', '테스트중임열글자채워' );

-- 4번 이모지
insert into abcde( test1,test2,test3,test4 )
values( '🎀', '🎀', '🎀', '🎀' );

-- 5번 utfmb4에만 이모지
insert into abcde( test1,test3)
values( '🎀', '🎀' );

그리고 다음과 같이 6번에 insert문을 실행해 보았다.

1,2,3번은 문제없이 잘 insert 되었다. 

처음 얘기했던 varchar(n)가 mariadb에서는 바이트가아닌 문자길이임을 알 수 있다.

 

4번 insert문을 실행하면

nvarchar로 정해놓았던 test2번컬럼에서  Incorrect string value라는 에러가 난다. 테이블 기본 문자집합이 utf8mb4지만 nvarchar(n) = varchar(n) CHARACTER SET utf8 이므로 이모지데이터가 insert되지 않는다.

마찬가지로 test4 컬럼에도 insert되지 않는다.

 

5번 utf8mb4 컬럼인 test1, test3번 컬럼에는 이모지가 잘 insert 된다.

 

select
	test1,
    length(test1),
	test2,
    length(test2),
	test3,
    length(test3),
	test4,
    length(test4)
from 
	abcde a;

length()함수는 문자열의 바이트를 나타낸다.

mariadb utf8문자집합은 영문자는 1바이트, 한글은 3바이트, 이모지는 4바이트로 저장함을 볼 수 있다.

 

결론

주로 utf8mb4 문자집합을 사용하는 요즘 mariadb에서 nvarchar컬럼을 사용하면 오히려 해당 컬럼은 utf8문자집합을 사용하게 되므로

nvarchar는 사용할일 이 없어 보인다.

비개발자가 데이터를 쉽게 준비하기위해 기존 데이터를 csv로 export해서 전달했고

이제 추가된 데이터와 함께 다시 csv를 import 시켜야한다.

SQL Loader를 이용하면 되었고 간단하게 사용법을 정리한다.

 

1. 사용방법

LOAD DATA

INFILE '파일경로'

APPEND

INTO TABLE 테이블명

fields terminated by ","

(
COL1
COL2
....
)

간단하게 구성해보았다.

1. INFILE에 적은 파일을 읽어

2. 기존 데이터는 두고 새로운 행을 추가하여

2. 타겟 테이블에

3. 각각 필드 구분자는 ,로 인식해

4. (co1, co2...) 컬럼에 각각 넣는다

 

 

2. 옵션

LOAD DATA
CHARACTERSET UTF8

INFILE '파일경로'
BADFILE '파일경로'
LOGFILE '파일경로'
DISCARDFILE '파일경로'

--(REPLACE, APPEND, INSERT)
TRUNCATE

INTO TABLE 테이블명

WHEN (5) = 'test'

FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'

TRAILING NULLCOLS

(
COL1,
COL2,
COL3,
....
)

몇가지 옵션을 더 추가했다

 

인코딩 설정을 하고 

 

 BADFILE은 데이터가 잘못되었을때 그 행을 기록하는 파일

 LOGFILE은 해당 Load 작업 로그파일

 DISCARDFILE은 WHEN절에 맞지 않아 로드 못한 행을 기록하는 파일이다

모두 설정을 안해주면 

컨트롤 파일과 같은 경로, 이름으로 확장자만 다르게 저장된다.

 

 

INSERT : 비어있는 테이블에 데이터를 로드한다. (테이블이 비어있지 않으면 에러)

APPEND : 새로 데이터를 추가한다

REPLACE : 기존 데이터가 있으면 덮어쓴다

TRUNCATE : 해당 테이블을 truncate 한 후 데이터를 로드한다

 

 

WHEN (5) = 'test'는 5번째 필드 데이터가 test인 경우만 로드한다는 뜻으로 조건절이다.

여기서 조건이 맞지 않은 행은 DISCARDFILE에 기록된다.

 

 

FILEDS TERMINATED BY ',' 

필드 구분자를 ,로 설정하고

OPTIONALLT ENCLOSED BY '"'

" (큰따옴표) 로 감싸진 필드를 인식한다

위 2개 설정을하면 데이터파일은 다음과같이 준비해야한다

"col1","col2","col3".......
"col1-1","col2-1","col3-1".......

 

 

TRAILING NULLCOLS : 실제 필요한 필드수보다 데이터파일의 데이터가 적을경우 나머지는 Null로 등록하는 설정

 

 

더 자세한 내용, 옵션은 아래 링크 참조

https://docs.oracle.com/cd/B19306_01/server.102/b14215/part_ldr.htm

 

 

3. 실행

위와 같이 컨트롤파일을 작성 후 sqlldr 명령어로 다음과 같이 실행하면 된다.

sqlldr userid=loader/loader control='/파일경로/load.ctl'

 

 

Load 작업을 하며 삽질했던 내용을 공유하며 글을 마무리한다.

1. 마지막 필드는 반드시 값이 있어야한다 (행의 끝이 구분자로 끝나면 안됨)
2. 윈도우에서 csv파일을 만들고 리눅스 환경에서 load를 진행하였는데 자꾸만 invalid number가 나오며 로드가 안됐는데 알고보니 윈도우 개행문자가 각 행 끝에 포함되어 있어서 발생한 문자였다. 윈도우 개행문자 삭제 후 해결
윈도우 개행문자는 다음 링크 참조
https://sleepyeyes.tistory.com/83?category=799682

 

'데이터베이스 > Oracle DB' 카테고리의 다른 글

[Oracle DB] SQL Plus csv export (spool)  (0) 2021.09.30

oracle db 데이터를 엑셀파일로 만들어야하는 상황이 생겼는데 tool을 이용하지도 못하는 상황이라 이리저리 찾아보는 도중 sqlplus에서 실행한 쿼리 결과를 파일로 만들어주는 spool 명령어를 알게 되었다.

 

1. 사용방법

set 옵션 값
spool 파일명(경로까지)
내보낼 sql문(select문)
spool off

 

예시

--컬럼 구분자 
set colsep ,

-- pagesize의 default는 14이며 그대로 하면 14줄마다 1줄씩 공백이 생기므로 
-- 그런 현상을 방지하기 위해 크게 지정
set pagesize 50000	

-- linesize도 데이터 길이만큼 지정하여 
-- 아래로 구분되지 않도록 크게 지정
set linesize 10000

-- linesize 이하로 행이 짧을 때 나머지부분을 자른다. 줄 끝 공백제거
set trimspool on

-- 컬럼명 헤더 출력 여부
set heading on

-- 결과 파일 경로
spool /home/oracle/test.csv

SELECT *
FROM TB_TABLE;

spool off

 

 

이외에도 여러 옵션이 있다. 아래에서 참조

https://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12040.htm#i2683501

 

 

편하게 .sql 스크립트 파일을 작성하고

sqlplus에 로그인해서 해당 스크립트 파일을 아래와 같이 불러주면 된다.

@test.sql

 

'데이터베이스 > Oracle DB' 카테고리의 다른 글

[Oracle DB] csv import (SQL Loader)  (0) 2021.10.01

웹서비스를 운영하면서 사이트가 느려진다면 많은 이유가 있겠지만

그중에 한가지 이유로 db관련 이슈가 있다.

 

그럼 개발자로써 해결을 해야하는데 많고 많은 웹서비스에 쿼리들 중에서 어떤 부분이 문제인지 어떻게 찾아야할까?

mysql이 옵션중에 설정해놓은 시간 이상 걸리는 쿼리를 기록하는 옵션이있다.

 

 

먼저 해당 옵션이 켜져있는지 꺼져있는지 확인해보자.

나의 윈도우 컴퓨터는 나도 모르게 켜져있었다..

 

 

실습환경 : ubuntu 18.04

DB : mariadb 10.1.40

1. 옵션 on/off 확인하는방법

먼저 mysql에 접속한후

$ mysql -u root -p

 

show variables like 'slow_query_%';

명령어로 확인해보자

OFF 되있는걸 볼 수 있다.

밑에 log_file은 ON일 경우 느린 쿼리 기록을 남기는 파일 경로이다.

 

 

2. 슬로우쿼리 옵션 설정

mysql설정파일 즉 윈도우환경이면 my.ini 리눅스면 my.cnf를 켜고 

[mysqld] 아래에 다음과 같은 #slow-query-setting부분을 적고 저장하자 

slow_query_log=1 이 ON상태이고 0이 OFF상태이다

fie은 아까 말했던대로 log파일을 남길 경로이며

long_query_time은 로그를 남길 기준시간이다.

즉 지금 1로 설정해놓았는데 쿼리실행시간이 1초가 넘어가야 로그를 남긴다.

 

저장후 mysql 재시작을 해준다.

$ service mysqld restart

윈도우에선 gui로 서비스 재시작을 해주자

 

재시작후 다시 1번을 하면 아래와 같이 설정이 바뀌어있다.

 

3. 로그 폴더 생성 / 권한부여

$ mkdir /var/log/slowlog

$ chown mysql:mysql /var/log/mysql

 

4.  TEST

mysql 접속을해서

select sleep(5);

의도적으로 시간을 지연시키는 쿼리를 보내고

 

나와서 확인을 해보자

Time 쿼리를 수행한 시간

User@Host 접속계정과 host정보

Query_time 쿼리가 수행되기까지의 시간

Lock_time 테이블 독점시간

그리고 밑에 수행한 쿼리문도 출력된다.

 

 

참고

https://2dubbing.tistory.com/19

https://zetawiki.com/wiki/MySQL_%EC%8A%AC%EB%A1%9C%EC%9A%B0_%EC%BF%BC%EB%A6%AC_%EB%A1%9C%EA%B7%B8_%EC%84%A4%EC%A0%95

덤프뜨기 (백업하기)


mysqldump -u (사용자이름[ex:root]) -p 백업할데이터베이스명 테이블명1 테이블명2  > 백업파일명.sql


mysqldump -u (사용자이름[ex:root]) -p --all-databases > 백업파일명.sql


특정 데이터베이스를 덤프하거나 모든 DB를 백업하는 방법이다.

테이블명을 입력하면 입력한 테이블명만 dump되고 

데이터베이스 안에 모든 테이블을 받으려면 입력하지 않으면 된다.




덤프저장 (백업복구)


mysql -u (사용자이름[ex:root]) -p 복구할데이터베이스명 < 백업파일명.sql


mysql -u (사용자이름[ex:root]) -p  < 백업파일명.sql


특정 데이터베이스를 덤프하거나 모든 DB를 복구하는 방법이다.

복구는 테이블 한개든 여러개든 전부든 테이블명을 입력하지 않아도 된다.

접속하기

 

$ mysql -u root -p

 

먼저 root 계정으로 접속한다.

 

 

 

사용자 확인하기

 

mysql> use mysql;

 

mysql> select host,user from user;

 

 

 

 

사용자 추가하기

 

mysql> create user '사용자'@'localhost(또는 %)' identified by '비밀번호';

 

%는 외부에서의 접근을 허용한다

 

 

 

사용자 삭제하기

 

mysql> drop user '사용자'@'localhost';

 

 

 

 

사용자에게 데이터베이스 권한 부여

 

mysql> grant all privileges on *.* to '사용자'@'localhost';

 

mysql> grant all privileges on DB이름.* to '사용자'@'localhost';

 

mysql> grant all privileges on DB이름.테이블명 to '사용자'@'localhost';

 

mysql> grant select on DB이름.테이블명 to '사용자'@'localhost';

 

mysql> grant update(컬럼1, 컬럼2) on DB이름.테이블명 to '사용자'@'localhost';

 

이렇게 특정 데이터베이스의 권한을 줄 수도 

특정 권한 ex) select 권한만 줄 수도

특정 컬럼에만 update의 권한을 줄 수도 있다.

 

all privieges 는 모든 권한을 

*.*은 모든 데이터베이스의 모든 테이블을 뜻한다.

 

@뒤에 특정 ip를 설정 할 수 있다.

특정 ip : @'192.168.1.18'

특정 ip 대역대 : @'192.168.1.%'

전체 : @'%'

 

 

사용자 생성, 권한 부여 한번에 하기

 

mysql> grant all privileges on *.* to '사용자'@'localhost' identified by '비밀번호';

 

이렇게 한번에 사용자 생성과 권한 부여를 할 수 있다.

 

 

사용자 권한 삭제하기

 

mysql> revoke all on DB이름.* from '사용자'@'localhost';

 

권한 부여와 구조가 같다. 

마찬가지로 응용해서 특정한 조건을 줄 수 있다.

 

 

변경사항 반영하기

mysql> flush privileges;

 

사용자 권한 확인하기

 

mysql> show grants for '사용자명'@'localhost';

 

 

 

+ Recent posts