Mriadb or mysql 대소문자 구분관련 정리

오라클에서 마리아DB로 이관을 준비하던 도중 동일한 이름으로 만들었고 사용하려던 도중에 오류가 발생해서 의아해서 찾아본 부분들이 존재했다. 바로 대소문자에 관련된 부분들!

 

1.오라클의 경우 따로 테이블명에 대한 대소문자 구분이 존재하지 않는다.

그러나 mysql류의 경우 설정으로 그걸 조절이 가능하다. 아래와같음 동일하게 맞춰주었다!

 

테이블명 대소문자 구분 여부 확인 및 세팅 변경방법

show variables like 'lower_case_table_names';

위에서 해당 값이 0인지, 1인지, 2인지 확인해야 합니다.

제 서버에서는 0으로 설정되어있었는데요,

0일경우 --> 대소문자 구분함

1일경우 --> 대소문자 구분안함

위와 같습니다.

따라서 대소문자 구분을 하지않도록 하기위해 lower_case_table_names의 값을 1로 설정해줍시다.

출처: <https://m.blog.naver.com/zzang9ha/221989693170>

 

2. 오라클의 데이터를 마리아DB로 넣어보는 테스트를 하던중 PK가 중복되는 형태가 발생하며 오류를 뿜어냈다. 알고봤더니 해당부분도 대소문자 구분에 의한것 ㅠㅠ 아래글을 보고 동일하게 BINARY값을 주는형태로 해결하였다.

현상 : 어라! MariaDB에서 쿼리를 날렸는데 정확하게 매칭되는것만 결과로 줘야하는데.... 누구냐넌! 왜왜~대소문자를 가리지 않고 오는겨?

 

원인 : MySQL MariaDB에서 문자열 대소문자를 구분하지 않는다.  mysql 공식문서 참고 : https://dev.mysql.com/doc/refman/5.6/en/case-sensitivity.html

 

해결 : 대소문자 구분하고 싶으면, BINARY 함수를 사용! 또는 테이블 생성시, BINARY옵션을 줘서 해결!

 

 

예시) EMPLYR_ID = 'test' 인 사용자가 하나 있다고 하자. 'TEST'로 검색시 안나와야하는데 나옴 ㅡ,.ㅡ

 

해결방법1

 

 

before	SELECT * FROM comtnemplyrinfo WHERE EMPLYR_ID = 'TEST'	===> test 란 소문자로된 사용자 아이디가 검색이 됨;;;; 원치않는동작!!!
after	SELECT * FROM comtnemplyrinfo WHERE BINARY(EMPLYR_ID) = 'TEST'	===> 검색결과 안나옴! 고로치! 그기야!

 

해결방법2 (강추)

ALTER TABLE comtnemplyrinfo CHANGE EMPLYR_ID EMPLYR_ID VARCHAR(100) BINARY NOT NULL

 

해결방법3

타입을 VARBINARY로 변경하는 방법.... 하지만 검색결과를 볼시, BLOB으로 표현되어 유지보수 측면에서 패스~

 

해결방법4

더있는데 생략한다.

 

 

 

보너스 트랙 (테이블명 대소문자 가릴까 말까?)

- 테이블 명을 소문자로만 취급할지 아니면 대문자로만 취급할지는 아래의 정보를 이용해 설정할것!

먼저 조회부터 해보자! ==> SHOW VARIABLES LIKE 'lower_case_table_names'

 

값의 의미는 아래 표를 보자

lower_case_table_names 값	의미	비고
0	쿼리시, 테이블명 대·소문자 구분함	==OS별 기본값== 리눅스, 유닉스 = 0 윈도우 = 1  맥키토시 =  2
1	쿼리시, 테이블명이 대/소문자든 무조건 소문자로 인식
2	윈도우에서 테이블 명을 대/소문자를 구분해서 생성

 

예시) 1일경우, 테이블명은 대문자로 주던 소문자로 주던 동작함.(참고로 저희 회사는 모든 프로젝트를 1로 설정해서 사용중)

SELECT * FROM COMTNEMPLYRINFO 

SELECT * FROM comtnemplyrinfo

 

그럼 우째 변경해야하나?

1. AWS의 RDS라면, RDS > 파라미터 그룹에 가서 lower_case_table_names 검색하면 설정가능하다!

2. 서버에 설치버전이면,  /etc/my.cnf 경로에 가서 원하는 값으로 편집하라!

출처: https://papababo.tistory.com/entry/MySQL-MariaDB에서-문자열-대소문자를-구분하지-않는다 [비니유니파파의 좌충우돌 인생 & 개발 스토리]