쿼리와 연관된 시스템 설정

쿼리와 연관된 시스템 설정

대소문자 구분, 문자열 표기 방법 등과 같은 SQL작성 규칙은 MySQL 서버의 시스템 설정에 따라 달라진다.

​

​

1. SQL 모드

MySQL 서버의 sql_mode라는 시스템 설정에는 여러 개의 값이 동시에 설정될 수 있다. 그 중에 SQL작성과 결과에 영향을 미치는 값들을 살펴보자.

​

set sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES,STRICT_TRANS_TABLES,IGNORE_SPACE,PIPES_AS_CONCAT,ANSI_QUOTES';

​

show variables like '%sql_mode%';

Variable_name	Value
sql_mode	PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,STRICT_ALL_TABLES

1) STRICT_ALL_TABLES (use)

일반적으로 MySQL에서는 저장하려는 값의 길이가 컬럼의 길이보다 더 긴 경우라 하더라도 에러가 발생하지 않는다. 컬럼의 길이를 초과하는 부분은 버리고 저장 가능한 만큼만 컬럼에 저장한다. sql_mode 시스템 변수에 STRICT_ALL_TABLES가 설정되면 컬럼의 정해진 길이보다 큰 값을 저장할 때 경고가 아닌 오류가 발생하고 쿼리 실행이 중지된다.

​

2) STRICT_TRANS_TABLES (use)

컬럼의 타입과 호환되지 않는 값을 저장할 때, MySQL 서버는 비슷한 값으로 최대한 바꿔서 저장하려고 한다. STRICT_TRANS_TABLES를 설정하면 원하지 않는 데이터 타입의 변환이 필요할 때 MySQL 서버는 강제 변환하지 않고 에러를 발생시킨다.

​

3) TRADITIONAL

STRICT_TRANS_TABLES나 STRICT_ALL_TABLES와 비슷하지만 조금 더 엄격한 방법으로 SQL의 작동을 제어한다. STRINCT_ALL_TABLES,STRICT_TRANS_TABLES,TRADITIONAL등의 설정은 MySQL 서버가 조금 더 ANSI 표준 모드로 작동하도록 유도한다.

​

4) ANSI_QUOTES (use or no use)

MySQL에서는 문자열 값(리터럴)을 표현하기 위해 홑따옴표(')와 쌍따옴표(")를 동시에 사용할 수 있다. 하지만 오라클과 같은 DBMS에서는 single quotation(') 는 문자열 값을 표기하는데 사용하고, double quotation(")은 컬럼명이나 테이블명과 같은 식별자(identifier)를 구분하는 용도로만 사용한다.

sql_mode 시스템 설정에 ANSI_QUOTES를 설정하면 single quotation만 문자열 값 표기로 사용할 수 있고, double quotation은 식별자를 표기하는 데만 사용할 수 있다.

​

set sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

select * from emp where ename = "SMITH"; -- 에러안남

-- ANSI_QUOTES 적용 시

set sql_mode = 'STRICT_TRANS_TABLES,ANSI_QUOTES,NO_ENGINE_SUBSTITUTION';

select * from emp where ename = "SMITH"; --에러난다

-- SQL Error [1054] [42S22]: Unknown column 'SMITH' in 'where clause'

select * from emp where ename = 'SMITH'; -- 싱글 쿼테이션만 문자를 표기하는데 사용할 수 있다.

​

5) ONLY_FULL_GROUP_BY (use)

MySQL의 쿼리에서는 GROUP BY절에 포함되지 않은 컬럼이라도 집합 함수의 사용 없이 그대로 SELECT절이나 HAVING절에 사용할 수 있다. sql_mode 시스템 설정에 ONLY_FULL_GROUP_BY를 설정하면 GROUP BY 문법에 맞게 작성해야 오류가 안난다.

​

[ONLY_FULL_GROUP_BY 설정 안할 때]

select deptno, count(*) from emp;

select deptno, empno, sal from emp group by deptno;

select deptno, empno, sal from emp group by deptno having sal>2000;

​

[ONLY_FULL_GROUP_BY 설정 할 때]

set sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

-- 위에 쿼리 실행 시 모두 오류난다.

-- 아래 쿼리는 sql 문법에 맞게 group by절을 작성해서 오류없이 작동한다.

select deptno, count(*) from emp group by deptno;

select deptno, empno, sal from emp group by deptno, empno, sal;

select deptno, empno, sal from emp group by deptno, empno, sal having sal >2000;

select deptno, count(*) from emp group by deptno having count(*) > 5;

​

6) PIPES_AS_CONCAT (use)

MySQL에서 "||"는 OR 연산자와 같은 의미로 사용된다. 하지만 sql_mode 시스템 설정에 PIPES_AS_CONCAT값을 설정하면 문자열 연결(CONCAT) 연산자로 사용할 수 있다.

​

set sql_mode = 'STRICT_TRANS_TABLES';

select 1 and 1 from dual; -- 1

select 0 or 1 from dual; -- 1

select 0 or 0 from dual; -- 0

select 0 || 0 from dual; -- 0

select 'a' || 'b' from dual; -- 0

​

set sql_mode = 'PIPES_AS_CONCAT,STRICT_TRANS_TABLES';

select 0 or 0 from dual; -- 0

select 0 || 0 from dual; --'00'

select 'a' || 'b' from dual; --'ab'

​

7) PAD_CHAR_TO_FULL_LENGTH

MySQL에서는 CHAR타입이라 하더라도 VARCHAR와 같이 유효 문자열 뒤의 공백 문자는 제거되어 반환된다. CHAR 타입의 컬럼값을 가져오는 경우, 뒤쪽의 공백이 제거되지 않고 반환돼야 한다면 sql_mode 시스템 설정에 PAD_CHAR_TO_FULL_LENGTH를 추가하면 된다.

​

CREATE TABLE tb_pad_char(

col_no int NOT NULL,

col_char char(5) COLLATE euckr_bin NOT NULL,

col_varchar varchar(5) COLLATE euckr_bin NOT NULL,

PRIMARY KEY (col_no)

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

​

insert into tb_pad_char values(1,'col1', 'col1');

insert into tb_pad_char values(2,'col2 ', 'col2 '); -- 공백 1개

insert into tb_pad_char values(3,'col3 ', 'col3 '); -- 공백 2개

​

select length('col2 '), length('col3 ') from dual; -- 5, 6

​

set sql_mode = 'PIPES_AS_CONCAT';

select col_no, ''''||col_char||'''', ''''||col_varchar||'''', length(col_char), length(col_varchar)

from tb_pad_char a;

char는 공백을 저장 안하고 varchar는 공백을 저장한다. 실제 varchar는 공백을 2개인데도 1개만 저장하였다.

​

set sql_mode = 'PIPES_AS_CONCAT,PAD_CHAR_TO_FULL_LENGTH';

select col_no, ''''||col_char||'''', ''''||col_varchar||'''', length(col_char), length(col_varchar)

from tb_pad_char a;

PAD_CHAR_TO_FULL_LENGTH 를 sql_mode에 추가 했을 때 char는 자릿수에서 문자를 뺀 나머지 공간을 공백으로 채운다.(문자+공백 = 자릿수) 그래서 이 설정값은 잘 쓰이지 않는다.

​

8) NO_BACKSLASH_ESCAPES

MySQL에서도 일반적인 프로그래밍 언어에서처럼 역 슬래시 문자를 이스케이프 문자로 사용할 수 있다. sql_mode 시스템 설정에 NO_BACKSLASH_ESCAPES를 추가하면 역 슬래시를 문자의 이스케이프 용도로 사용하지 못한다. 이 설정을 활성화하면 백 슬래시 문자도 다른 문자와 동일하게 취급한다.

​

9) IGNORE_SPACE (use)

MySQL에서 스토어드 프로시저나 함수의 이름 뒤에 공백이 있으면 "스토어드 프로시저나 함수가 없습니다"라는 에러가 출력될 수도 있다. MySQL에서는 스토어드 프로시저나 함수명과 괄호 사이에 있는 공백까지도 스토어드 프로시저나 함수의 이름으로 간주한다. 이 동작 방식이 기본 모드이므로 몇 번이고 함수가 있는지 확인하기도 한다. sql_mode 시스템 설정에 IGNORE_SPACE를 추가하면 프로시저나 함수명과 괄호 사이의 공백은 무시한다.

​

10) ANSI

이 값은 위에서 설명한 여러가지 옵션을 조합해서 MySQL서버가 최대한 SQL 표준에 맞게 동작하게 만들어 준다.

​

​

2. 영문 대소문자 구분

MySQL 서버가 운영체제에 상관없이 DB나 테이블명의 대소문자 구분의 영향을 받지 않게 하려면 MySQL 서버의 설정 파일에 "lower_case_table_names" 시스템 변수를 설정하면 된다. 이 변수를 1로 설정하면 모두 소문자로만 저장되고, MySQL 서버가 대소문자를 구분하지 않게 해준다. 이 변수가 0이면 DB나 테이블명에 대해 대소문자를 구분하지 않는다. 또한 이 설정 값에 2를 설정할 수도 있는데 이경우에는 저장은 대소문자를 구분해서 하지만 MySQL 쿼리에서는 대소문자를 구분하지 않게 해준다. (기본값은 1)

​

show variables like 'lower_case_table_names';

​

3. MySQL 예약어

데이터베이스나 테이블, 그리고 컬럼의 이름을 예약어와 같은 키워드로 생성하면 해당 컬럼이나 테이블을 SQL에서 사용하기 위해서는 항상 역따옴표(')나 쌍따옴표(")로 감싸줘야 한다. MySQL에서 이미 등록된 예약어의 개수는 적지 않으며, 예약어별로 문제가 되지 않는 키워드들도 있다. 메뉴얼을 통해 예약어인지 아닌지를 찾아 보든지 예약어로 MySQL에 직업 테이블을 생성해보면 MySQL서버가 에러로 알려준다.