[Real MySQL 8.0 2] 11.3 - MySQL 연산자와 내장 함수
여타 DMBS에서 사용되는 기본적인 연산자는 MySQL에서도 거의 비슷하게 사용되지만 MySQL에서만 사용되는 연산자나 표기법이 있다. 여기에는 ANSI 표준 형태가 아닌 연산자가 많이 있는데, 이러한 부분은 MySQL을 처음 사용하는 사용자를 혼란스럽게 만들기도 한다. 이번 절에서는 MySQL에서만 사용 가능한 연산자도 함께 살펴보겠지만 가능하면 SQL의 가독성을 높이기 위해 ANSI 표준 형태의 연산자를 사용하길 권장한다.
1. 리터럴 표기법 문자열
1.1 문자열
SQL 표준에서 문자열은 항상 홑따옴표(')를 사용해서 표시한다. 하지만 MySQL에서는 다음과 같이 쌍따옴표(")를 사용해 문자열을 표기할 수도 있다.
1
2
SELECT * FROM departments WHERE dept_no='d001';
SELECT * FROM departments WHERE dept_no="d001";
SQL 표준에서는 문자열 값에 홑따옴표가 포함돼 있을 때 홑따옴표를 두 번 연속해서 입력하면 된다. 하지만 MySQL에서는 쌍따옴표와 홑따옴표를 혼합해서 이러한 문제를 피해 가기도 한다. 마찬가지로 문자열 값이 쌍따옴표를 가지고 있을 때는 쌍따옴표를 두 번 연속해서 사용할 수 있다. 다음 첫 번째, 두 번째 쿼리는 SQL 표준이지만, 세 번째와 네 번째는 MySQL에서만 지원되는 방식이다.
1
2
3
4
SELECT * FROM departments WHERE dept_no='d''001';
SELECT * FROM departments WHERE dept_no='d"001';
SELECT * FROM departments WHERE dept_no="d'001";
SELECT * FROM departments WHERE dept_no="d""001";
SQL에서 사용되는 식별자(테이블명이나 컬럼명 등)가 키워드와 충돌할 때 오라클이나 PostgreSQL에서는 쌍따옴표나 대괄호로 감싸서 충돌을 피한다. MySQL에서는 역따옴표(`)로 감싸서 사용하면 키워드와의 충돌을 피할 수 있다.
1
2
CREATE TABLE tab_test (`table` VARCHAR(20) NOT NULL, ...);
SELECT `column` FROM tab_test;
MySQL 서버의 sql_mode 시스템 변숫값에 ANSI_QUOTES를 설정하면 쌍따옴표는 문자열 리터럴 표기에 사용할 수 없다. 그리고 테이블명이나 컬럼명의 충돌을 피하려면 역따옴표가 아니라 쌍따옴표를 사용해야 한다.
1
2
3
4
5
SELECT * FROM departments WHERE dept_no='d''001';
SELECT * FROM departments WHERE dept_no='d"001';
CREATE TABLE tab_test ("table" VARCHAR(20) NOT NULL, ...);
SELECT "column" FROM tab_test;
1.2 숫자
숫자 값을 상수로 SQL에 사용할 때는 다른 DBMS와 마찬가지로 따옴표 없이 숫자 값을 입력하면 된다. 또한 문자열 형태로 따옴표를 사용하더라도 비교 대상이 숫자 값이거나 숫자 타입의 컬럼이면 MySQL 서버가 문자열 값을 숫자 값으로 자동 변환한다.
아래의 첫 번째 쿼리는 주어진 문자열 하나만 숫자로 변환한 후 비교하게 되며 성능 관련 문제가 발생하지 않는다. 하지만 두 번째 쿼리는 string_column의 모든 문자열 값을 숫자로 변환한 후 비교를 수행하게 되며 이 때문에 인덱스가 있어도 이용하지 못하고 숫자로 변환할 수 없는 문자열이 포함되어 있으면 쿼리가 실패할 수도 있다.
1
2
SELECT * FROM tab_test WHERE number_column='10001';
SELECT * FROM tab_test WHERE string_column=10001;
1.3 날짜
다른 DBMS에서 날짜 타입을 비교하거나 INSERT하려면 문자열을 DATE 타입으로 변환하는 코드가 필요하다. 하지만 MySQL에서는 정해진 형태의 날짜 포맷으로 표기하면 MySQL 서버가 자동으로 DATE나 DATETIME 값으로 변환하기 때문에 복잡하게 STR_TO_DATE() 같은 함수를 사용하지 않아도 된다.
아래 첫 번째 쿼리처럼 날짜 타입 컬럼과 문자열 값을 비교하는 경우 MySQL 서버는 문자열 값을 DATE 타입으로 변환해서 비교한다. 두 번째 쿼리는 SQL에서 문자열을 DATE 타입으로 강제 변환해서 비교하는 예제인데, 두 쿼리의 차이점은 없다. 첫 번째 쿼리와 같이 비교한다고 해서 from_date 컬럼의 값을 문자열로 변환해서 비교하지 않기 때문에 from_date 컬럼으로 생성된 인덱스를 이용하는 데 문제가 되지 않는다.
1
2
SELECT * FROM dept_emp WHERE from_date='2011-04-29';
SELECT * FROM dept_emp WHERE from_date=STR_TO_DATE('2011-04-29', '%Y-%m-%d');
1.4 불리언
BOOL이나 BOOLEAN이라는 타입이 있지만 이는 TINYINT 타입에 대한 동의어일 뿐이다. 불리언 값 TRUE, FALSE는 정수 1, 0에 매핑된다. TRUE, FALSE 같은 불리언 타입을 꼭 사용하고 싶다면 ENUM 타입으로 관리하는 것이 조금 더 명확하고 실수할 가능성도 줄일 수 있다.
2. MySQL 연산자
2.1 동등 비교
동등 비교는 다른 DBMS에서와 마찬가지로 = 기호를 사용해 비교를 수행하면 된다. 하지만 MySQL에서는 동등 비교를 위해 <=> 연산자(NULL-Safe 비교 연산자)도 제공하는데 <=> 연산자는 = 연산자와 같으며 부가적으로 NULL 값에 대한 비교까지 수행한다. = 연산자는 한쪽 비교 대상이 NULL이면 NULL을 반환하는데 반해 <=> 연산자는 양쪽 비교 대상이 모두 NULL이라면 TRUE를 반환하고, 한쪽만 NULL이라면 FALSE를 반환한다.
2.2 부정 비교
부정 비교는 <>와 != 연산자 모두를 사용할 수 있으며 가독성을 위해 통일해서 사용하자.
2.3 NOT 연산자
TRUE 또는 FALSE 연산의 결과를 반대로 만드는 연산자로 NOT을 사용한다. 또한 !도 같은 목적으로 사용할 수 있다.
2.4 AND와 OR 연산자
MySQL에서는 AND와 OR 뿐만 아니라 &&와 ||의 사용도 허용한다. 연산자 우선순위는 AND가 OR보다 높다.
2.5 나누기와 나머지 연산자
나누기 연산자는 / 연산자를 사용하면 되며 나눈 몫의 정수 부분만 가져오려면 DIV 연산자를 사용하면 된다. 나머지는 %나 MOD 연산자(함수)를 사용한다.
2.6 RECEXP 연산자
문자열 값의 패턴을 확인하는 RECEXP 연산자가 있어서 정규 표현식 기반으로 패턴 매칭을 할 수 있다. 정규 표현식은 POSIX 표준으로 구현돼 있다.
2.7 LIKE 연산자
RECEXP 연산자보다 훨씬 단순한 패턴 비교 연산자인 LIKE가 있으며 DBMS에서는 LIKE 연산자를 더 많이 사용한다. RECEXP 연산자는 인덱스를 전혀 사용하지 못하지만 LIKE 연산자는 와일드카드가 검색어 앞쪽에 없다면 인덱스 레인지 스캔을 이용해 처리할 수 있다. 와일드카드로 %와 _가 있으며 %는 0개 이상의 문자열, _는 한 개의 문자를 의미한다.
2.8 BETWEEN 연산자
BETWEEN 연산자는 “크거나 같다”와 “작거나 같다”라는 두 개의 연산자를 하나로 합친 연산자다.
2.9 IN 연산자
IN 연산자는 여러 개의 값에 대해 동등 비교 연산을 수행하는 연산자다. 여러 개의 값이 비교되지만 범위로 검색하는 것이 아니라 여러 번의 동등 비교로 실행하기 때문에 일반적으로 빠르게 처리된다. IN 연산자는 상수 또는 서브쿼리에 대해 적용할 수 있다. MySQL 8.0 이전 버전까지는 튜플에 대한 비교에서 항상 풀 테이블 스캔을 했었는데, 이제는 인덱스를 최적으로 사용할 수 있게 개선됐다.
3. MySQL 내장 함수
MySQL은 워낙 다양한 내장 함수를 지원한다. 필요할 때 찾아보자.(MySQL - Functions and Operators)
Ref
- Real MySQL 8.0 2 - p.7 ~ p.52