Mysql like 대소문자 구분 없이 - Mysql like daesomunja gubun eobs-i

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

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

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

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

해결방법1

해결방법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'

값의 의미는 아래 표를 보자

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

SELECT * FROM COMTNEMPLYRINFO 

SELECT * FROM comtnemplyrinfo

그럼 우째 변경해야하나?

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

2. 서버에 설치버전이면,  /etc/my.cnf 경로에 가서 원하는 값으로 편집하라!(당근 재기동해야겠쥬?)

Mysql 또는 Maria를 리눅스 서버에 설치하고 사용하다보면 테이블을 대소문자 구분하도록 설정이 되어 있는 경우가 있다.

해당 설정을 확인하는 방법은 아래 명령어를 통해 알 수 있다.

대소문자 구분상태 확인하기

show variables like 'lower_case_table_names';

Value값이 0이면 구분, 1이면 구분하지 않는 상태이다.

구분하지 않도록 설정하기 위해선 설정값을 변경하고 재기동이 필요하다.

설정 변경하기

vi, vim 등의 에디터를 통해 설정 파일을 먼저 열어준다.

vim /etc/my.cnf

설정 값 중에 [mysqld] 영역이 존재할텐데, 최하단에 아래의 설정값을 입력한다.

[mysqld]
lower_case_table_names=1

* [mysqld] 자체가 없다면 추가하고 입력하면 된다.

이후 서비스를 재기동하면 구분하지 않는 것을 볼 수 있다.

systemctl restart mariadb   #maria 재기동
service mysql restart       #mysql 재기동

- 다음과 같은 명령을 실행하고 확인 하였을 경우
         lower_case_table_names의 값이 0인지 1인지 2인지를 확인해야 한다.
         : show variables like 'lower_case_table_names';
         <<<<<<<<<<<<<<<<<<<<<<<<<<<<<
         0일 경우 : 대소문자 구분함
         1일 경우 : 대소문자 구분안함
         >>>>>>>>>>>>>>>>>>>>>>>>>>>>>

       - vi로 my.cnf 파일을 보도록 하자
       - sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

         : [mysqld]
         : lower_case_table_names = 1

       - mysqld 서비스 재시작
         : sudo systemctl restart mysql

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log

[client]

default-character-set=utf8

[mysql]

default-character-set=utf8

[mysqld]
default-time-zone='+9:00'
init_connect = SET collation_connection = utf8_general_ci
init_connect = SET NAMES utf8
character-set-server = utf8
collation-server = utf8_general_ci
lower_case_table_names = 1

[mysqldump]
default-character-set=utf8

# By default we only accept connections from localhost
#bind-address   = 127.0.0.1
bind-address    = 0.0.0.0
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

=========================================

MySQL로 간혹가다 작업 도중 분명히 테이블이 존재하는데

 Table '테이블명' doesn't exist

라는 문구가 나올때가 존재한다.

윈도우에서는 대부분 대소문자 구분없이 설치가 되지만, 

간혹가다가 리눅스용 MySQL은 대소문자 구분하는 형식으로 설치가 되있는 경우가 있다 .

show variables like 'lower_case_table_names';

다음과 같은 명령을 실행하고 확인 하였을 경우

lower_case_table_names의 값이 0인지 1인지 2인지를 확인해야 한다.

본인의 테스트 서버는 0이어서 오류가 났었던 것이다.

보통 2는본적이 없어서 모르겠고 0 또는 1인것을 보았었다.

0일 경우 : 대소문자 구분함

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

본인은 대소문자 구분을 하지 않기위하여  lower_case_table_names의 값을 1로 설정을 해주고자 한다.

MySQL의 my.cnf 파일을 건드려 주어야 하는데

보통은 /etc/my.cnf에 존재한다.

vi로 my.cnf 파일을 보도록 하자

접속하다보면 [mysqld] << 이영역이 있을텐데

영역의 맨밑에 

lower_case_table_names = 1

라고 설정을 해주도록 하자

비교를 하기 위해 한줄 내려서 작성하였다.

붙여서 상관은 없다

위치는 [mysqld] < 밑에 나열된 명령어들 그리고 다음 영역의 [] 가 나오기 전에만 작성해 주면 

되겠다.

저장하고 나온다음에 mysqld 서비스를 재시작 해주었다.

쿼리에서 다시 명령어를 실행하여 대소문자 구분값이 변경되었는지 확인해보자

본인은 1로 잘 변경이 되었다.

한번 다시 아까 오류난 쿼리를 재실행 해보도록 하겠다.

정상적으로 소문자 테이블인대 대문자로 명령어를 실행해도 잘나온다

유의하도록 하자...