Let's have a cup of coffee.

문제 : 오라클 클라이언트 사용하여 Unload 시 10자리 초과의 숫자에 대해 지수형으로 저장하여 csv 파일을 다시 aurora로 이행시 지수형부분이 아래와 같이 뒷부분이 "000000"로 저장되는 문제 발생, 또한 99999999999 숫자의 경우 100000000000로 저장되는 문제 발생

Case 1)

오라클 ASIS 데이터 : 12345678912
Unload CSV 파일 : 1.23456E+10
Aurora 에서 데이터 : 1234000000

Case 2)

오라클 ASIS 데이터 : 99999999999
Unload CSV 파일 : 1.0000E+11
Aurora 에서 데이터 : 100000000000

해결 방안1)

SET NUM[WIDTH] {10|n}

SPOOL 파일에 SET NUMWIDTH 38 옵션을 추가, Default 값은 10, 11자리 이상은 지수로 표현함

n은 38까지 설정가능( 2~50 까지 가능하다고 하는데 테스트 해봐야함)

38자리로 설정하면 숫자는 공백을 포함하여 저장되는데 TOBE의 데이터 타입이 숫자형이라서 공백은 제외하고 데이터는 잘 들어간다.    

ex) 123 -> '                             123' -> 123

spool on

SET PAGES 0
SET FEEDBACK OFF
SET HEADING ON
SET ECHO OFF
SET TERMOUT OFF
SET TRIMSPOOL ON
SET LONG 1000000
SET LONGCHUNKSIZE 100000
SET NUMWIDTH 38

ALTER SESSION SET NLS_DATE_FORMAT='YYYYYMMDDHH24MISS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYYMMDDHH24MISSXFF';

spool [PATH]

[SQL]
;

spool off

ex) numwidth 설정 예제 

SQL> show numwidth 
numwidth 10

SQL> SELECT 1234567891 num1, 12345678912 num2 from dual;

num1        | num2       |
--------------------------
1234567891  | 1.2346E+10 |

SQL> set numwidth 20

num1        | num2        |
---------------------------
1234567891  | 12345678912 |

해결방안2)

SET NUMF[ORMAT] format

NUMFORMAT을 설정한다. NUMFORMAT은 TO_NUMBER 형식 모델과 같은 방식으로 작동한다. 기본값은 ""(설정되지 않음) 이다.

SQL> select 12345 from dual;

12345
------
12345

SQL> set numformat 0000000000
SQL> select 12345 from dual;

     12345
----------
0000012345

SQL> set numformat 99999.00
SQL> select 12345 from dual;

    12345
---------
 12345.00

해결방은 3)

Unload 하기 전에 SQL에 숫자형 데이터에 to_char() 로 문자형으로 형변환한다. 문자형은 지수변환하지 않고 데이터 그대로 저장하고 TOBE에 입력할 때 숫자형으로 변환된다.

하지만, 숫자 컬럼이 많아서 해당 방법은 적용하지 않았다.

결론은 1번 방안으로 선택하였다.

1. 문제점

​

Oracle에서 PK컬럼인 NO에 'ABC'와 'ABC '를 다른데이터로 인식하여 두개의 컬럼이 존재하였다.

[PK: NO]1행 : 'ABC'

[PK: NO]2행 : 'ABC '

2. MySQL로 전환시 오류 발생

​

MySQL에서 collation=euckr에서 Unique 체크시에 Pad Space 방식으로 데이터를 비교한다.

즉, 'ABC'와 'ABC '를 같은 데이터로 인식하여 중복데이터이므로 데이터삽입 오류가 발생한다. (둘 중에 먼저 삽입되는 하나만 들어간다.)

'ABC' => [A][B][C][ ] (공백을 채워서 Unique 비교를 수행)

'ABC ' => [A][B][C][ ]

3. 유일성 체크시 trim을 적용하여 중복된 데이터를 검색한다.

SELECT TRIM(NO), COUNT(*)
  FROM TABLE_NAME
 GROUP BY TRIM(NO)
HAVING COUNT(*) > 1;

4. 중복 데이터 제거하고 전환한다.

SELECT *
  FROM (SELECT NO, ROW_NUMBER() OVER(PARTITION BY TRIM(NO) ORDER BY UPD_DATE) RN
          FROM TABLE_NAME)
 WHERE RN <= 1;

SELECT 'SELECT JSON_OBJECT'||CHR(10)||'(' AS JSON_SCRIPT FROM DUAL
UNION ALL
SELECT JSON_SCRIPT
  FROM (
SELECT CASE WHEN COLUMN_ID = 1 THEN '  ' ELSE ', ' END
       ||' KEY '''||COLUMN_NAME|| ''' IS ''"''||' ||
       CASE WHEN DATA_TYPE IN ('DATE','TIMESTAMP') 
			THEN 'TO_CHAR('||COLUMN_NAME||',''YYYYMMDDHH24MISS'') '
            WHEN DATA_TYPE IN ('VARCHAR2','VARCHAR','CHAR')
            THEN 'REGEXP_REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TRIM('||COLUMN_NAME||'),''\'',''\\''),''"'',''\"''),''/'',''\/''),chr(8),''\b''),chr(9),''\t''),chr(10),''\n''),chr(12),''\f''),chr(13),''\r''),'''''''',''\\''''''),''[^[:print:]]'')  '
            ELSE 'TO_CHAR('||COLUMN_NAME||')'
       END
       || '||''"'''
       || ' FROMAT JSON' AS JSON_SCRIPT
 FROM ALL_TAB_COLUMNS A
WHERE OWNER = :OWNER
  AND TABLE_NAME = :TABLE_NAME 
ORDER BY COLUMN_ID
)
UNION ALL
SELECT ') AS JSON_OBJECT'||CHR(10)||' FROM '||:OWNER||'.'||:TABLE_NAME||CHR(10)||'WHERE 1=1'||CHR(10) AS JSON_SCRIPT FROM DUAL
;

SELECT JSON_OBJECT
       (
         KEY 'NO' IS '"'||TO_CHAR(NO)||'"' FORMAT JSON
       , KEY 'CUST_NO' IS '"'||TO_CHAR(CUST_NO) || '"' FORMAT JSON
       , KEY 'CONTENTS' IS '"'||REGEXP_REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TRIM(CONTENTS),'\','\\'),'"','\"'),'/','\/'),chr(8),'\b'),chr(9),'\t'),chr(10),'\n'),chr(12),'\f'),chr(13),'\r'),'''',''\\'''),'[^[:print:]]')||'"' FORMAT JSON
       , KEY 'CREATE_DATE' IS '"'||TO_CHAR(CREATE_DATE,'YYYYMMDDHH24MISS')|| '"' FORMAT JSON
       , ........
       ) AS JSON_SCRIPT
  FROM OWNER.TABLE_NAME
-- WHERE NO = 1111
;

{"NO":"1111","CUST_NO":"12341234","NAME":"JEUS\\'S PATH","CREATE_DATE":"20210611131313",...}

\b  Backspace (ASCII code x08)
\f  Form feed (ASCII code x0C)
\n  New line (ASCII code x0A)
\r  Carriage Return (ASCII code x0D)
\t  Horizontal Tab (ASCII code x09)
\v  Vertical tab (ASCII code x0B)
\'  Apostrophe / Single quote (ASCII code x27)
\"  Double quote (ASCII code x22)
\\  Backslash character / Reverse Solidis (ASCII code x5C)
\Uxxxx Unprintable / Unicode characters (x = hex digit)

[이슈사항]

Oracle to MySQL에서 Loader로 데이터 삽입시 행이 하나가 더 들어간다.

​

[원인]

loader를 사용하여 @가 포함되어있는 varchar를 mysql로 insert시에 행이 두개로 분리된다. 이문제는 특히 @가 맨뒤에 있을때 발생한다. @/n이 문제가 된다.

(유닉스에서 @/n 새로운 행으로 인식한다.)

​

예시)

ISSUE_COLUMN

-----------------------

이 @의 문제는 맨 끝에 올때 문제가 생긴다.@

@앞에오면 전혀 문제가 없다.@

.....

​

​

[해결]

CHR(13) = /r 은 동일한 자리에 첫번째 자리에 커서를 위치시킵니다.

CHR(10) = /n 은 현재 커서가 위치한 곳에서 한줄 아래로 내립니다.

엔터키 = CHR(13)CHR(10)

​

@가 행의 끝에 올때 @||CHR(10)의 형태로 있어서 데이터가 한개의 행이 두개의 행으로 분리되어서 삽입되는 현상이 발생한다. CHR(13) = /r을 추가해 데이터를 삽입하면 정상적으로 한줄만 데이터가 생성된다.

​

SQL)

SELECT /*+FULL(A) PARALLEL(A 16)*/

ISSUE_COLUMN

, REPLACE(ISSUE_COLUMN,'@'||CHR(10), '@'||CHR(13)||CHR(10))

FROM ISSUE_TABLE A

WHERE 1=1

AND ISSUE_COLUMN LIKE '%@'||CHR(10)||'%'

;

​

[결론]

행끝에 "@/n"가 오면 loader시에 "@/n" 기준으로 두개의 행으로 분리된다.

"@/n"이 끝에오면 "@/n" --> "@/r/n" 으로 변환하여 전환한다.

​

​

[행이 여러개로 삽입될때 해결방법2]

다른 방법으로 컬럼에 엔터와 스페이스가 남발하고 엔터로 인해 행이 여러개 추가된다면 아래의 방법을 사용할 수 도 있다.

​

chr(10) ==> '##enter##'로 바꾸고

스페이스문자는 '( )+' ==> chr(32)로 바꾸고

컬럼간의 구분문자('||||') 를 추가해준다.

​

SELECT NVL(COL1, 'NULL')

||'||||'||NVL(COL2,'NULL')

||'||||'||NVL(REGEXP_REPLACE(REPLACE(COL3,CHR(10),'##enter##'),'( )+,CHR(32)),'NULL')

||'||||'||NVL(COL4,'NULL') AS SCRIPT

FROM TABLE_DATA

ORDER BY COL1

​

[loader.ctl 파일의 내용중 일부]

load data

infile ...

append

into table tobe_table_data

filelds terminated by '||||'

trailing nuullcols

(col1

,col2

,col3

,col4

)

​

[이행후 작업]

이행후에 'NULL' 을 ''으로 엔터를 /n('##enter##'=> chr(10) 으로 변경해 준다.

​

UPDATE (

SELECT COL1, DECODE(COL1,'NULL','',COL1) AS COL1_UPD

COL2, DECODE(COL2,'NULL','',COL2) AS COL2_UPD

COL3, REPLACE(DECODE(COL3,'NULL','',COL3),'##enter##',CHR(10)) AS COL3_UPD

COL4, DECODE(COL4,'NULL','',COL4) AS COL4_UPD

FROM TABLE_DATA

)

SET

COL1 = COL1_UPD

,COL2 = COL2_UPD

,COL3 = COL3_UPD

,COL4 = COL4_UPD

;

​

COMMIT;

​

Recursive CTE를 이용한 계층쿼리 구현 테스트

*MySQL8.0 부터 구현 가능

​

-- --------------------------------------------------------------------------------------------------------

-- SAMPLE DATA

-- --------------------------------------------------------------------------------------------------------

SHOW COLLATION WHERE charset = 'euckr';

-- euckr_bin은 문자를 코드화 하여 비교한다.

-- 'A'와 'a'가 다르다

-- euckr_korean_ci은 문자와 문자를 비교 --> 비교대상(필터링) 많아짐

-- 'A'와 'a'가 같다

​

DROP DATABASE IF EXISTS scott;

CREATE DATABASE IF NOT EXISTS scott;

USE scott;

​

​

DROP TABLE IF EXISTS EMP;

DROP TABLE IF EXISTS DEPT;

DROP TABLE IF EXISTS SALGRADE;

​

CREATE TABLE DEPT (

DEPTNO INT(10) NOT NULL,

DNAME VARCHAR(14) NOT NULL,

LOC VARCHAR(13) NOT NULL,

PRIMARY KEY (DEPTNO)

) ENGINE=InnoDB DEFAULT CHARSET=euckr COLLATE=euckr_bin;

​

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', '뉴욕');

INSERT INTO DEPT VALUES (20, 'RESEARCH', '달라스');

INSERT INTO DEPT VALUES (30, 'SALES', '시카고');

INSERT INTO DEPT VALUES (40, 'OPERATIONS', '보스턴');

​

SELECT * FROM DEPT;

​

CREATE TABLE EMP (

EMPNO INT(4) NOT NULL,

ENAME VARCHAR(10) NOT NULL,

JOB VARCHAR(9) NOT NULL,

MGR INT(4) ,

HIREDATE DATE NOT NULL,

SAL DECIMAL(7,2) NOT NULL,

COMM DECIMAL(7,2) ,

DEPTNO INT(10) NOT NULL,

PRIMARY KEY (EMPNO)

) ENGINE=InnoDB DEFAULT CHARSET=euckr COLLATE=euckr_bin;

​

INSERT INTO EMP VALUES (7839,'김가훈' ,'사장' ,NULL,'1981-11-17',5000,NULL,10);

INSERT INTO EMP VALUES (7698,'박지영' ,'매니저' ,7839,'1981-05-01',2850,NULL,30);

INSERT INTO EMP VALUES (7782,'박재연' ,'매니저' ,7839,'1981-05-09',2450,NULL,10);

INSERT INTO EMP VALUES (7566,'김가연' ,'매니저' ,7839,'1981-04-01',2975,NULL,20);

INSERT INTO EMP VALUES (7654,'홍하연' ,'판매자' ,7698,'1981-09-10',1250,1400,30);

INSERT INTO EMP VALUES (7499,'신지영' ,'판매자' ,7698,'1981-02-11',1600,300 ,30);

INSERT INTO EMP VALUES (7844,'신민영' ,'판매자' ,7698,'1981-08-21',1500,0 ,30);

INSERT INTO EMP VALUES (7900,'최성군' ,'점원' ,7698,'1981-12-11',950 ,NULL,30);

INSERT INTO EMP VALUES (7521,'하동길' ,'판매자' ,7698,'1981-02-23',1250,500 ,30);

INSERT INTO EMP VALUES (7902,'염지훈' ,'분석가' ,7566,'1981-12-11',3000,NULL,20);

INSERT INTO EMP VALUES (7369,'서동진' ,'점원' ,7902,'1980-12-09',800 ,NULL,20);

INSERT INTO EMP VALUES (7788,'노길준' ,'분석가' ,7566,'1982-12-22',3000,NULL,20);

INSERT INTO EMP VALUES (7876,'김성하' ,'점원' ,7788,'1983-01-15',1100,NULL,20);

INSERT INTO EMP VALUES (7934,'김지혜' ,'점원' ,7782,'1982-01-11',1300,NULL,10);

SELECT * FROM EMP;

​

​

CREATE TABLE SALGRADE

( GRADE INT(10) NOT NULL,

LOSAL INT(10) NOT NULL,

HISAL INT(10) NOT NULL,

PRIMARY KEY (GRADE)

) ENGINE=InnoDB DEFAULT CHARSET=euckr COLLATE=euckr_bin;

​

INSERT INTO SALGRADE VALUES(1,700,1200);

INSERT INTO SALGRADE VALUES(2,1201,1400);

INSERT INTO SALGRADE VALUES(3,1401,2000);

INSERT INTO SALGRADE VALUES(4,2001,3000);

INSERT INTO SALGRADE VALUES(5,3001,9999);

​

SELECT * FROM SALGRADE;

​

COMMIT;

​

​

-- --------------------------------------------------------------------------------------------------------

-- CONNECT BY 예제

-- MySQL 8.0부터 Recursive CTE 사용 가능

-- --------------------------------------------------------------------------------------------------------

-- 재귀서브팩토링(Recursive subquery factoring) 기능을 사용 한다.

-- CTE(Common Table Expression) 구문 이라고도 한다.

​

SELECT VERSION();

-- 참고사이트 : https://dev.mysql.com/doc/refman/8.0/en/with.html

-- WITH절 다음 RECURSIVE구문을 사용한다

-- UNION ALL 기준 상위 쿼리 : CTE에 초기값 설정

-- UNION ALL 기준 아래 쿼리 : CTE를 이용해 RECURCIVE 구문에 의해 반복적으로 실행하여 결과값 CTE에 저장

WITH RECURSIVE CTE (n) AS

(

SELECT 1 -- Anchor member

UNION ALL

SELECT n + 1 FROM CTE -- Recursive member

WHERE n < 5 -- Termination member

)

SELECT * FROM CTE; -- Query that users CTE

​

​

-- EMP 테이블의 계층관계

-- 자릿수 설정 안할시 에러 발생 : SQL Error[1406][22001] : Data truncation : Data too long for column 'PRN_ENAME' at row 1

-- PRN_ENAME : CAST로 CHAR(10) 로 자릿수 설정

-- PT : CAST로 CHAR(30) 로 자릿수 설정

WITH RECURSIVE CTE (EMPNO, ENAME, JOB, MGR, LV ,PRN_ENAME, PT)

AS (

SELECT EMPNO, ENAME, JOB, MGR

,1 AS LV -- LEVEL

,CAST('' AS CHAR(10)) AS PRN_ENAME -- PRIOR

,CAST(EMPNO AS CHAR(30)) AS PT -- SYS_CONNECT_BY_PATH

FROM EMP

WHERE MGR IS NULL

UNION ALL

SELECT C.EMPNO, C.ENAME, C.JOB, C.MGR

,P.LV + 1 AS LV -- LEVEL

,P.ENAME AS PRN_ENAME -- PRIOR

,CONCAT_WS('>',P.PT ,C.EMPNO) AS PT -- SYS_CONNECT_BY_PATH

FROM CTE P

INNER JOIN EMP C

ON P.EMPNO = C.MGR

)

SELECT LV, EMPNO, ENAME, JOB, MGR, PRN_ENAME, PT

FROM CTE

ORDER BY PT;

​

테스트 환경 : MySQL 8.0

​

1. NO PAD 방식

​

1-1. CHAR 공백 UNIQUE 테스트

공백을 저장 안하므로 'data '를 'data'로 저장한다.(공백제거)

unique 비교시 'data' 와 'data ' 를 다른데이터로 보지만

UNIQUE 값 저장시에 unique 컬럼의 값에 'data' 가 이미 존재한다면

CHAR는 공백을 제거하고 저장하므로 'data ' 가 'data' 가 되어 같은데이터로 인식하여 저장되지 않는다.

공백이 포함된 데이터를 이관시 공백제거 후 같은 데이터가 있는지 점검이 필요하다.

​

1-2. VARCHAR 공백 UNIQUE 테스트

공백을 저장하여 'data ' 를 'data ' 로 저장한다.(공백유지)

unique 비교시 NOPAD 방식이므로 'data'와 'data '를 다른데이터로 인식한다.

공백이 포함된 데이터를 이관시 공백제거 후 같은 데이터가 있는지 점검이 필요없다.

(하지만, 데이터 자체로 보면 중복이므로 정제가 필요하다.)

​

​

2. PAD SPACE 방식

​

2-1. CHAR 공백 UNIQUE 테스트

공백을 저장 안하므로 'data '를 'data'로 저장한다.(공백제거)

unique 비교시 'data' 와 'data ' 를 같은데이터로 본다. (공백PAD를 채워서 비교)

UNIQUE 값 저장시에 unique 컬럼의 값에 'data' 가 이미 존재한다면

'data '(공백포함) 가 같은데이터로 인식되어 저장되지 않는다.

공백이 포함된 데이터를 이관시 공백제거 후 같은 데이터가 있는지 점검이 필요하다.

​

2-2. VARCHAR 공백 UNIQUE 테스트

공백을 저장하여 'data ' 를 'data ' 로 저장한다.(공백유지)

unique 비교시 PADSPACE 방식이므로 'data'와 'data '를 같은데이터로 인식한다.

공백이 포함된 데이터를 이관시 공백제거 후 같은 데이터가 있는지 점검이 필요하다.

​

-- PADSPACE라는 것은 공백PAD를 길이가 큰쪽에 맞춰서 붙여서 공백의 영향없이 비교될 수 있는 것을 의미한다.

​

​

3. 결론

​

COLLATION(정렬) 방식에 따라 비교 방식이 다르다.

NO PAD는 공백을 채우지 않고 비교하여 'data' 와 'data ' 를 다른 데이터로 인식한다.

PAD SPACE 는 공백을 채우고 비교하여 'data' 와 'data ' 를 같은 데이터로 인식한다.

​

CHAR 타입은 공백을 제거하고 저장하고 VARCHAR 타입은 공백을 저장한다.

​

* COLLATION 조회

Pad_attribute 속성 값 => NO PAD or PAD SPACE

SHOW COLLATION WHERE Charset = 'utf8mb4';

​

SELECT COLLATION_NAME, PAD_ATTRIBUTE

FROM INFORMATION_SCHEMA.COLLATIONS

WHERE CHARACTER_SET_NAME = 'utf8mb4';

​

[TEST]

-- ---------------------------------------------------------------------------------------------------------------------

-- NO PAD 방식

-- ---------------------------------------------------------------------------------------------------------------------

use employees;

​

SHOW COLLATION WHERE Charset = 'utf8mb4';

-- NO PAD 방식

SHOW COLLATION WHERE collation = 'utf8mb4_0900_ai_ci';

​

-- 공백이 다르다고 인식한다.

select case when 'data' = 'data ' then 1 else 2 end from dual;

​

-- varchar 공백 테스트

drop table blink_test1;

create table blink_test1(

col1 varchar(14) NOT NULL

)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ;

​

insert into blink_test1(col1) values('data');

insert into blink_test1(col1) values('data '); -- 공백도 저장한다.

insert into blink_test1(col1) values('data '); -- 공백도 저장한다.

commit;

​

-- 결과 3건이 나온다.

select * from blink_test1;

-- 결과 1건이 나온다.

select * from blink_test1 where col1 = 'data';

-- 결과 3건이 나온다.

select * from blink_test1 where col1 like 'data%';

​

-- varchar 공백 pk 테스트(unique 비교시 공백도 저장하므로 'data'와 'data '를 다른데이터로 인식한다)

drop table blink_test2;

create table blink_test2(

col1 varchar(14) NOT NULL

,PRIMARY KEY (col1)

)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ;

​

insert into blink_test2(col1) values('data');

insert into blink_test2(col1) values('data '); -- 공백도 저장한다.

insert into blink_test2(col1) values('data '); -- 공백도 저장한다.

commit;

-- 결과 3건이 나온다.

select * from blink_test2;

-- 결과 1건이 나온다.

select * from blink_test2 where col1 = 'data';

-- 결과 3건이 나온다.

select * from blink_test2 where col1 like 'data%';

​

-- varchar 공백 pk 테스트(unique 비교시 공백도 저장하므로 'data'와 'data '를 다른데이터로 인식한다)

drop table blink_test3;

create table blink_test3(

col1 varchar(14) NOT NULL

,col2 varchar(14) NOT NULL

,PRIMARY KEY (col1, col2)

)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ;

​

insert into blink_test3(col1,col2) values('data','data2');

insert into blink_test3(col1,col2) values('data','data2 ');

insert into blink_test3(col1,col2) values('data','data2 ');

commit;

-- 결과 3건이 나온다.

select * from blink_test3;

-- 결과 1건이 나온다.

select * from blink_test3 where col1 = 'data' and col2 = 'data2';

-- 결과 3건이 나온다.

select * from blink_test3 where col1 = 'data' and col2 like 'data%';

​

-- char 공백 pk 테스트(unique 비교시 공백을 저장 안하므로 'data'와 'data '를 같은데이터로 인식한다)

drop table blink_test4;

create table blink_test4(

col1 char(14) NOT NULL

,col2 char(14) NOT NULL

,PRIMARY KEY (col1, col2)

)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ;

​

insert into blink_test4(col1,col2) values('data','data2 '); -- 공백을 제거하고 저장한다.

insert into blink_test4(col1,col2) values('data','data2'); -- Duplicate entry 'data-data2' for key 'blink_test4.PRIMARY'

insert into blink_test4(col1,col2) values('data','data2 ');-- Duplicate entry 'data-data2' for key 'blink_test4.PRIMARY'

commit;

-- 결과 1건이 나온다.

select * from blink_test4;

-- 결과 1건이 나온다.

select * from blink_test4 where col1 = 'data' and col2 = 'data2';

-- 결과 0건이 나온다.

select * from blink_test4 where col1 = 'data ' and col2 = 'data2 ';

​

-- char 공백 테스트(공백을 저장 안하므로 'data'와 'data '를 같은데이터로 인식한다)

drop table blink_test5;

create table blink_test5(

col1 char(14) NOT NULL

,col2 char(14) NOT NULL

)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ;

​

insert into blink_test5(col1,col2) values('data','data2');

insert into blink_test5(col1,col2) values('data','data2 '); -- 공백을 제거하고 저장한다.

insert into blink_test5(col1,col2) values('data','data2 ');-- 공백을 제거하고 저장한다.

commit;

-- 결과 3건이 나온다.

select * from blink_test5;

-- 결과 3건이 나온다.

select * from blink_test5 where col1 = 'data' and col2 = 'data2';

-- 결과 0건이 나온다.

select * from blink_test5 where col1 = 'data' and col2 = 'data2 ';

-- 결과 3건이 나온다.

select * from blink_test5 where col1 = 'data' and col2 like 'data2%';

​

select cast('data' as char) from dual;

​

select cast('data' as char) = cast('data ' as char) ; -- 0

select cast('data' as varchar) = cast('data ' as varchar);

​

​

​

​

-- ---------------------------------------------------------------------------------------------------------------------

-- PAD SPACE 방식

-- ---------------------------------------------------------------------------------------------------------------------

-- PADSPACE라는 것은 공백PAD를 길이가 큰쪽에 맞춰서 붙여서 공백의 영향없이 비교될 수 있는 것을 의미한다.

SHOW COLLATION WHERE collation = 'utf8mb4_bin';

show COLLATION where charset = 'utf8mb4';

​

-- varchar 공백 테스트

drop table blink_test1;

create table blink_test1(

col1 varchar(14) NOT NULL

)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ;

​

insert into blink_test1(col1) values('data');

insert into blink_test1(col1) values('data '); -- 공백도 저장한다.

insert into blink_test1(col1) values('data '); -- 공백도 저장한다.

commit;

​

-- 결과 3건이 나온다.

select * from blink_test1;

-- 결과 1건이 나온다. --> 결과 3건이 나온다.

select * from blink_test1 where col1 = 'data';

-- [추가]

-- 결과 3건이 나온다. PADSPACE를 큰것에 맞춰서 비교한다.

select * from blink_test1 where col1 = 'data ';

-- 결과 3건이 나온다.

select * from blink_test1 where col1 like 'data%';

​

-- varchar 공백 pk 테스트(unique 비교시 공백을 채워서 비교하므로 'data'와 'data '를 같은 데이터로 인식한다)

drop table blink_test2;

create table blink_test2(

col1 varchar(14) NOT NULL

,PRIMARY KEY (col1)

)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ;

​

insert into blink_test2(col1) values('data');

insert into blink_test2(col1) values('data '); -- UNIQUE 체크를 위해서 공백 채워서 비교하여 저장하지 못한다.

-- 18:33:00 insert into blink_test2(col1) values('data ') Error Code: 1062. Duplicate entry 'data ' for key 'blink_test2.PRIMARY' 0.000 sec

insert into blink_test2(col1) values('data '); -- UNIQUE 체크를 위해서 공백 채워서 비교하여 저장하지 못한다.

-- 18:33:31 insert into blink_test2(col1) values('data ') Error Code: 1062. Duplicate entry 'data ' for key 'blink_test2.PRIMARY' 0.000 sec

​

commit;

-- 결과 3건이 나온다. --> 결과 1건이 나온다.

select * from blink_test2;

-- 결과 1건이 나온다.

select * from blink_test2 where col1 = 'data';

-- 결과 3건이 나온다. --> 결과 1건이 나온다.

select * from blink_test2 where col1 like 'data%';

​

-- varchar 공백 pk 테스트(unique 비교시 공백도 저장하므로 'data'와 'data '를 같은 데이터로 인식한다)

drop table blink_test3;

create table blink_test3(

col1 varchar(14) NOT NULL

,col2 varchar(14) NOT NULL

,PRIMARY KEY (col1, col2)

)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ;

​

insert into blink_test3(col1,col2) values('data','data2 '); -- 공백을 저장한다.

insert into blink_test3(col1,col2) values('data','data2'); -- Error Code: 1062. Duplicate entry 'data-data2 ' for key 'blink_test3.PRIMARY'

insert into blink_test3(col1,col2) values('data','data2 ');-- Error Code: 1062. Duplicate entry 'data-data2 ' for key 'blink_test3.PRIMARY'

commit;

-- 결과 3건이 나온다. --> 결과 1건이 나온다.

select * from blink_test3;

-- 결과 1건이 나온다.

select * from blink_test3 where col1 = 'data' and col2 = 'data2';

-- [추가]

-- 결과 1건이 나온다. (공백을 추가해도 같은데이터로 본다)

select * from blink_test3 where col1 = 'data ' and col2 = 'data2 ';

-- 결과 3건이 나온다. --> 결과 1건이 나온다.

select * from blink_test3 where col1 = 'data' and col2 like 'data%';

​

-- char 공백 pk 테스트(unique 비교시 공백을 저장 안하므로 'data'와 'data '를 같은데이터로 인식한다)

drop table blink_test4;

create table blink_test4(

col1 char(14) NOT NULL

,col2 char(14) NOT NULL

,PRIMARY KEY (col1, col2)

)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ;

​

insert into blink_test4(col1,col2) values('data','data2 '); -- 공백을 제거하고 저장한다.

insert into blink_test4(col1,col2) values('data','data2'); -- Duplicate entry 'data-data2' for key 'blink_test4.PRIMARY'

insert into blink_test4(col1,col2) values('data','data2 ');-- Duplicate entry 'data-data2' for key 'blink_test4.PRIMARY'

commit;

-- 결과 1건이 나온다.

select * from blink_test4;

-- 결과 1건이 나온다.

select * from blink_test4 where col1 = 'data' and col2 = 'data2';

-- 결과 0건이 나온다. --> 결과 1건이 나온다.

select * from blink_test4 where col1 = 'data ' and col2 = 'data2 ';

​

-- char 공백 테스트(공백을 저장 안하므로 'data'와 'data '를 같은데이터로 인식한다)

drop table blink_test5;

create table blink_test5(

col1 char(14) NOT NULL

,col2 char(14) NOT NULL

)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ;

​

insert into blink_test5(col1,col2) values('data','data2');

insert into blink_test5(col1,col2) values('data','data2 '); -- 공백을 제거하고 저장한다.

insert into blink_test5(col1,col2) values('data','data2 ');-- 공백을 제거하고 저장한다.

commit;

-- 결과 3건이 나온다.

select * from blink_test5;

-- 결과 3건이 나온다.

select * from blink_test5 where col1 = 'data' and col2 = 'data2';

-- 결과 0건이 나온다. --> 결과 3건이 나온다. 공백을 채워서 비교하여 'data2' 와 'data2 '를 같은 데이터로 인식한다.

select * from blink_test5 where col1 = 'data' and col2 = 'data2 ';

-- 결과 3건이 나온다.

select * from blink_test5 where col1 = 'data' and col2 like 'data2%';

-----------------------------------------------------------------------------------------------------------------------

MySQL의 파티션키는 PK가 존재한다면 파티션키로 사용하는 컬럼이 PK에 포함되어야 한다.

MySQL은 파티션 단위로 인덱스를 생성한다. 그러므로 MySQL파티션은 로컬 인덱스만 생성이 가능하다. 파티션키를 포함한 글로벌인덱스를 못 만든다.

​

아래는 테스트 SQL이다

-> KEY1으로 파티션 생성시 성공

-> KEY2으로 파티션 생성시 실패한다.

​

USE EMPLOYEES;

[생성 실패 : PK키가 포함 안됨]

CREATE TABLE PARTITION_TEST1

(ID INT not null,

KEY1 VARCHAR(8),

KEY2 VARCHAR(8),

PRIMARY KEY(ID, KEY1))

PARTITION BY RANGE COLUMNS(KEY2)

(PARTITION P202012 VALUES LESS THAN('20201231') ENGINE = INNODB);

​

SQL Error [1503] [HY000]: A PRIMARY KEY must include all columns in the table's partitioning function (prefixed columns are not considered).

​

[생성 성공 : PK키가 포함됨]

CREATE TABLE PARTITION_TEST2

(ID INT not null,

KEY1 VARCHAR(8),

KEY2 VARCHAR(8),

PRIMARY KEY(ID, KEY1))

PARTITION BY RANGE COLUMNS(KEY1)

(PARTITION P202012 VALUES LESS THAN('20201231') ENGINE = INNODB);

​

[생성 실패 : PK키가 포함 안됨, UNIQUE KEY만으로 생성 안됨 ]

CREATE TABLE PARTITION_TEST3

(ID INT not null,

KEY1 VARCHAR(8),

KEY2 VARCHAR(8),

KEY3 VARCHAR(8),

KEY4 VARCHAR(8),

PRIMARY KEY(ID, KEY1),

UNIQUE KEY(KEY2)

)

PARTITION BY RANGE COLUMNS(KEY2)

(PARTITION P202012 VALUES LESS THAN('20201231') ENGINE = INNODB);

​

[생성완료 : PK가 없음]

CREATE TABLE PARTITION_TEST4

(ID INT not null,

KEY1 INT not null,

KEY2 INT not null,

KEY3 INT not null,

KEY4 VARCHAR(8)

) ENGINE=INNODB

PARTITION BY HASH(KEY1)

partitions 4;

​

[생성완료 : PK Prefix]

CREATE TABLE PARTITION_TEST5

(ID INT not null,

KEY1 INT not null,

KEY2 INT not null,

KEY3 INT not null,

KEY4 VARCHAR(8),

primary key(ID)

) ENGINE=INNODB

PARTITION BY HASH(ID)

partitions 4(

PARTITION P1 ENGINE=INNODB,

PARTITION P2 ENGINE=INNODB,

PARTITION P3 ENGINE=INNODB,

PARTITION P4 ENGINE=INNODB

);

​

CREATE TABLE PARTITION_TEST6

(ID INT not null,

KEY1 INT not null,

KEY2 INT not null,

KEY3 INT not null,

KEY4 VARCHAR(8),

primary key(ID, KEY1)

) ENGINE=INNODB

PARTITION BY HASH(ID)

partitions 4(

PARTITION P1 ENGINE=INNODB,

PARTITION P2 ENGINE=INNODB,

PARTITION P3 ENGINE=INNODB,

PARTITION P4 ENGINE=INNODB

);

​

​

[생성완료 : PK Postfix]

CREATE TABLE PARTITION_TEST7

(ID INT not null,

KEY1 INT not null,

KEY2 INT not null,

KEY3 INT not null,

KEY4 VARCHAR(8),

primary key(ID, KEY1)

) ENGINE=INNODB

PARTITION BY HASH(KEY1)

partitions 4(

PARTITION P1 ENGINE=INNODB,

PARTITION P2 ENGINE=INNODB,

PARTITION P3 ENGINE=INNODB,

PARTITION P4 ENGINE=INNODB

);

​

[생성실패 : PK존재할 때 Unique Key만 사용]

CREATE TABLE PARTITION_TEST8

(ID INT not null,

KEY1 INT not null,

KEY2 INT not null,

KEY3 INT not null,

KEY4 VARCHAR(8),

primary key(ID, KEY1)

unique key (KEY2)

) ENGINE=INNODB

PARTITION BY HASH(KEY2)

partitions 4(

PARTITION P1 ENGINE=INNODB,

PARTITION P2 ENGINE=INNODB,

PARTITION P3 ENGINE=INNODB,

PARTITION P4 ENGINE=INNODB

);

​

[생성완료 : Unique Key만 존재]

CREATE TABLE PARTITION_TEST9

(ID INT not null,

KEY1 INT not null,

KEY2 INT not null,

KEY3 INT not null,

KEY4 VARCHAR(8),

-- primary key(ID, KEY1)

unique key (KEY2)

) ENGINE=INNODB

PARTITION BY HASH(KEY2)

partitions 4(

PARTITION P1 ENGINE=INNODB,

PARTITION P2 ENGINE=INNODB,

PARTITION P3 ENGINE=INNODB,

PARTITION P4 ENGINE=INNODB

);