본 게시물의 내용과 이미지는 도서 Real MySQL의 내용을 재구성하여 작성되었습니다. 저자, 출판사에 의해 저작권 문제 발생시 게시물이 비공개 될 수 있음을 알립니다.
매뉴얼의 SQL 문법 표기를 읽는 방법
SQL에서 각 키워드는 위의 그림과 같이 키워드나 표현식이 표기된 순서대로만 사용할 수 있다.
표기법에 대문자로 표현된 단어는 모두 키워드를 의미한다. 키워드는 대소문자를 특별히 구분하지 않고 사용할 수 있다.
대괄호("[ ]")는 해당 키워드나 표현식 자체가 선택 사항임을 의미한다. 즉 대괄호로 묶인 키워드나 표현식은 없어도 문법적인 오류를 일으키지 않으며, 있어도 문법적인 오류가 발생하지 않음을 의미한다.
파이프("|")는 앞과 뒤의 키워드나 표현식 중에서 단 하나만 선택해서 사용할 수 있음을 의미한다.
중괄호 ("{ }")는 괄호 내의 아이템 중에서 반드시 하나를 사용해야 하는 경우를 의미한다.
"..." 표기는 명시된 키워드나 표현식의 조합이 반복될 수 있음을 의미한다.
리터럴 표기법
SQL 표준에서 문자열은 항상 홑따옴표(')를 사용해서 표시한다. 하지만 MySQL에서는 쌍따옴표를 사용해 문자열을 표기할 수도 있다.
SELECT * FROM departments WHERE dept_no='d001';
SELECT * FROM departments WHERE dept_no="d001";
SQL 표준에서는 문자열값에 홑따옴표가 포함돼 있을 때 홑따옴표를 두 번 연속해서 입력하면 된다. 하지만 MySQL 에서는 쌍또옴표와 홑따옴표를 혼합해 이러한 문제를 피해 가기도 한다. 마찬가지로 문자열 값이 쌍따옴표를 가지고 있을 때는 쌍따옴표를 두 번 연속해서 사용할 수 있다. 다음 예제는 아무 문제없이 사용할수 있는 문자열 표기법이다.
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";
MySQL에서는 사용되는 식별자(테이블이나 칼럼명)가 키워드와 충돌할 때 역따옴표로 감싸서 사용하면 예약어와의 충돌을 피할 수 있다.
CREATE TABLE tab_test (`table` varchar(20) not null, ...);
SELECT `column` FROM tab_test;
MySQL 서버의 sql_mode 시스템 변수 값에 "ANSI_QUOTES"를 설정하면 쌍따옴표는 문자열 리터럴에 사용할 수 없다. 그리고 테이블명이나 칼럼명의 충돌을 피하려면 역따옴표(`)가 아니라 쌍 따옴표를 사용해야 한다.
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;
숫자
숫자 값을 상수로 SQL에 사용할 때는 따옴표 없이 숫자를 입력하면 된다. 숫자값과 문자열 값을 비교할 때 주의사항이 있다.
SELECT * FROM tab_test WHERE number_column='10001';
SELECT * FROM tab_test WHERE string_column=10001;
두 비교 대상이 문자열과 숫자 타입으로 다를 때는 자동으로 타입의 변환이 발생한다. 숫자 타입과 문자열 타입 간의 비교에서 숫자 타입을 우선시하므로 문자열을 숫자로 변환한 후 비교를 수행한다.
첫번째 쿼리는 상수값을 숫자로 변환하는데 상수값 하나만 변환하므로 성능과 관련된 문제가 발생하지 않는다.
두번째 쿼리는 주어진 상수값이 숫자값인데 비교되는 칼럼은 문자열 칼럼이다. 이때 MySQL은 문자열 칼럼을 숫자로 변환해서 비교한다. string_column의 값을 숫자로 변환해서 비교를 수행해야 하므로 string_column에 인덱스가 있다 하더라도 이를 이용하지 못한다. string_column에 알파뱃과 같은 문자가 포함된 경우 숫자 값으로 변환할 수 없으므로 쿼리 자체가 실패할 수도 있다.
원천적으로 이러한 문제를 해결하려면 숫자 타입은 숫자 타입의 칼럼에만 저장해야 한다.
날짜
MySQL 에서는 정해진 날짜 포맷으로 표기하면 MySQL 서버가 자동으로 DATE나 DATETIME 값으로 변환한다.
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');
첫 번째 쿼리와 같이 날짜 타입 칼럼과 문자열을 비교하면, MySQL 서버는 문자열 값을 DATE 타입으로 변환해서 비교한다. 두 번째 쿼리는 문자열을 DATE 타입으로 강제 변환해서 비교하는 예제인데 두 쿼리의 차이점은 거의 없다. 첫 번째 쿼리와 같이 비교하더라도 from_date 타입을 문자열로 변환해서 비교하지 않기 때문에 from_date 칼럼으로 생성된 인덱스를 이용하는데 문제가 되지 않는다.
불리언
BOOL 이나 BOOLEAN이라는 타입이 있지만 사실 이것은 TINYINT 타입에 대한 동의어일 뿐이다. 테이블의 칼럼을 BOOL로 생성한 뒤에 조회해보면 칼럼의 타입이 BOOL이 아니라 TINYINT라는 점을 알 수 있다. MySQL 에서는 다음 예제 쿼리와 같이 'TRUE 또는 FALSE 형태로 비교하거나 값을 저장할 수 있다. 하지만 이는 BOOL 타입뿐 아니라 숫자 타입의 칼럼에도 모두 적용되는 비교 방법이다.
CREATE TABLE tb_boolean (bool_value BOOLEAN);
INSERT INTO tb_boolean VALUES (FALSE);
SELECT * FROM tb_boolean WHERE bool_value=FALSE;
SELECT * FROM tb_boolean WHERE bool_value=TRUE;
위의 쿼리에서 TRUE 나 FALSE로 비교했지만 실제로는 값을 조회해보면 0 또는 1 값이 조회된다. MySQL은 C/C++ 언어에서처럼 TRUE 또는 FALSE 같은 불리언 값을 정수로 맵핑해서 사용한다 이때 MySQL 에서는 FALSE가 C/C++ 언어에서 처럼 정수값 0이 되지만, TRUE는 C/C++ 과는 달리 1만을 의미한다는 점에서 주의해야 한다. 그래서 숫자 값이 저장된 칼럼을 TRUE 나 FALSE로 조회하면 0이나 1이외의 숫자 값은 조회되지 않는다.
CREATE TABLE tb_boolean (bool_value BOOLEAN);
INSERT INTO tb_boolean VALUES (FALSE), (TRUE), (2), (3), (4), (5);
SELECT * FROM tb_boolean WHERE bool_value IN (FALSE, TRUE);
Result
bool_value
0
1
모든 숫자 값이 TRUE나 FALSE 두 개의 불리언 값으로 매핑되지 않는다는 것은 혼란스럽고 애플리케이션의 버그로 연결될 가능성이 크다. 만약 불리언 타입을 꼭 사용하고 싶다면 ENUM 타입으로 정하는 것이 조금 더 명확하고 실수할 가능성도 줄일 수 있다.
