infoqoch's 개발 블로그

mysql, ANSI를 기준으로 보는 MySQL의 특수기호, 리터럴, 연산자

• • •

리터럴과 관련한 MySQL의 특징과 주의사항

  • MySQL의 사용에 있어서 다양한 명령이나 쿼리를 작성할 때, 백틱 등 특수 기호나 문자나 숫자 등 값으로 대입하고 and나 or 등 연산자를 사용한다. 이를 잘못 작성하면 오류가 발생하거나 혹은 쿼리 성능에 큰 영향을 미칠 수 있다. 그러므로 DDL, DML 등의 문법만큼 쿼리 문장 작성 규칙을 이해하고 정확하게 사용할 필요가 있다.
  • 더불어 ANSI 표준에 없는 MySQL만의 표현법이 존재한다. 가능하면 ANSI에 맞춰 리터럴과 연산자를 사용한다.

SQL MODE

  • sql_mode는 SQL 문장 작성 규칙 및 데이터 타입 변환 등 다양한 동작 방식을 결정한다.

  • sql_mode는 서비스 도중에 변경 가능하나 이 변경으로 인하여 사이드 이펙트가 발생할 수 있으므로 유의하자.

  • 해당 옵션에 익숙하지 않으면 기본값을 사용해도 좋으며, 기본값은 다음과 같다.
    • ONLY_FULL_GROUP_BY
    • STRICT_TRANS_TABLES
    • NO_ZERO_IN_DATE
    • NO_ZERO_DATE
    • ERROR_FOR_DIVISION_BY_ZERO
    • NO_ENGINE_SUBSTITUTION
  • STRICT_TRANS_TABLES, STRICT_ALL_TABLES
    • 둘 중 하나 이상이 sql mode에 들어가면 (Strict SQL Mode)[https://dev.mysql.com/doc/refman/8.4/en/sql-mode.html#sql-mode-stricts]로 동작한다.
    • 다른 옵션과 결합되어 경고(show warnings;)가 아닌 에러를 만들어 쿼리를 실패하게 만든다.
    • 트랜잭션이 없는 테이블에서는 다중 레코드에 대한 INSERT 및 UPDATE에서 partial update란 치명적인 문제가 발생할 수 있다. 기타 복잡한 내용이 있는데 해당 내용은 위 링크를 참고하자!
    • 둘 다 활성하는 것을 권장한다.
  • NO_ZERO_IN_DATE,NO_ZERO_DATE: ‘0000-00-00’과 같은 존재하지 않는 날짜를 허용한다.
  • ANSI_QUOTES: ANSI 표준에 따라 문자열은 홑따옴표만 사용하고, 칼럼명이나 테이블명은 쌍따옴표를 사용한다.

  • ANSI: 최대한 ANSI 표준에 맞도록 설정하며 다음으로 구성된다 : REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, ONLY_FULL_GROUP_BY

  • 아래는 NO_ZERO_IN_DATE,NO_ZERO_DATE와 STRICT_TRANS_TABLES,STRICT_ALL_TABLES를 사용하여 실제로 날짜 타입의 삽입과 엄격 모드의 관계를 확인하였다. 실제로 테스트를 하지 않으면 어떻게 동작하는지 이해하기 어렵다. 아래 쿼리를 직접 사용해보자!
create table t(p int, v date);

-- 테스트1. 
set sql_mode ='NO_ZERO_IN_DATE,NO_ZERO_DATE';
select @@session.sql_mode;
insert into t values (1, '0000-00-00'); -- 안 들어감!
show warnings; -- warning 1264!

-- 테스트2
set sql_mode ='STRICT_TRANS_TABLES,STRICT_ALL_TABLES';
select @@session.sql_mode;
insert into t values (2, '0000-00-00'); -- 잘 들어감!
show warnings; -- 없음!

-- 테스트3
set sql_mode ='STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE';
select @@session.sql_mode;
insert into t values (3, '0000-00-00'); -- error 발생!

select * from t;

영문 대소문자 구분과 lower_case_table_names

  • MySQL은 영문자로 된 테이블명, 칼럼명, 쿼리의 문자열에 대한 대소문자에 대해 구분할 수도 있고 구분하지 않을 수도 있다.
  • lower_case_table_names 변수를 통해 설정 가능하다.
    • 0: 기본값. 테이블명의 대소문자를 구분한다.
    • 1: 모두 소문자로 저장된다.
    • 2: 저장은 대소문자로 구분하지만 쿼리는 대소문자를 구분하지 않는다.
  • lower_case_table_names와 관계 없이 테이블명이나 칼럼명을 대문자 또는 소문자로 통일해서 사용하는 것을 권장한다.

역따옴표(백틱, `) 사용 지양

  • MySQL은 예약어를 테이블명으로 사용 가능하다. 다만, 해당 이름을 역따옴표로 감싸야만 가능하다.
  • 예약어를 이름으로 사용하는 것은 차후 버그가 될 수 있다. 그러므로 MySQL에서 DDL을 작성할 때 역따옴표로 감싸지 않고 바로 이름을 작성한다. 에러가 발생할 경우 해당 이름을 사용하지 않는다.

MySQL과 리터럴

  • MySQL의 리터럴은 ANSI 표준을 따르지만 다른 DBMS에 존재하지 않는 리터럴 표현 방식이 존재한다. MySQL의 리터럴 표현 방식을 정리하고자 한다.

문자열

  • SQL 표준에 따라 문자열은 항상 홑따옴표(‘)를 사용한다.
  • 문자열 중간에 홑따옴표가 있을 경우, 홑따옴표를 두 번 작성하는 것으로 해결 가능하다.
select 'He said ''I gotta go!'', and he left soon.';

숫자

  • 숫자는 따옴표 없이 바로 숫자를 입력한다.
  • 다만, 숫자를 따옴표로 감쌀 경우 MySQL은 자동변환을 수행하는데, 이때 문제가 발생할 수 있다.
  • 아래의 예제를 보면,
    • 첫 번째 쿼리는 숫자 칼럼을 검색할 때 숫자를 문자열 타입과 비교한다. 이 경우 검색 값을 자동 변환하여 숫자 타입으로 변환한다.
    • 두 번째 쿼리는 문자열 칼럼을 숫자 타입과 비교한다. 이 경우 검색 값이 아닌 문자열 칼럼 전체를 숫자로 변환하여 비교한다. 그러므로 인덱스 등을 전혀 사용하지 못하고 불필요한 성능 문제가 발생한다. 혹은 쿼리 자체가 실패할 수도 있다.
  • 칼럼의 타입에 맞춰 정확하게 검색 값의 타입을 입력해야 한다.
SELECT * FROM tab_test WHERE number_column='10001';
SELECT * FROM tab_test WHERE string_column=10001;

날짜

  • MySQL은 날짜타입 포맷으로 문자열을 작성할 경우 자동으로 날짜 타입으로 변환한다.
  • 아래 쿼리는, 함수(STR_TO_DATE)를 통해 문자열을 날짜로 반환하는 것과 그렇지 않은 것이다. 둘 다 정상적으로 동작한다.
select DATE_ADD('2023-01-01', interval 1 day);
select DATE_ADD(STR_TO_DATE('2023-01-01', '%Y-%m-%d'), interval 1 day);

불리언

  • BOOL, BOOLEAN 타입이 존재하나 TINYINT와 같다.
  • TRUE, FALSE 등으로 값을 저장할 수 있으나 숫자 1과 0으로 저장된다.
  • tinyint 타입이므로 1과 0을 제외한 숫자(2,3,4 등)를 입력할 수 있다.
  • 다소 모호하기 때문에 엄격한 관리를 위해서는 불리언보다는 ENUM타입을 권장한다.
CREATE TABLE tb_boolean (bool_value BOOLEAN, string_value varchar(100));
INSERT INTO tb_boolean VALUES
    (FALSE, 'FALSE') -- 0
    ,(false, 'false') -- 0
    ,(0, '0') -- 0
    ,(TRUE, 'TRUE') -- 1
    ,(true, 'true') -- 1
    ,(1, '1') -- 1
    ,(2, '2') -- 2
;

SELECT * FROM tb_boolean WHERE bool_value=FALSE;
SELECT * FROM tb_boolean WHERE bool_value=TRUE;

MySQL 연산자

동등(equal) 비교(=, <=>)

  • =: 동등비교. NULL이 존재하면 비교할 수 없음(NULL 반환)
  • <=>: NULL-Safe 비교. NULL에 대해서도 비교 가능
SELECT 
       1 = 1         -- 1
     , 1 <=> 1       -- 1
     , NULL = NULL   -- null
     , NULL <=> NULL -- 1
     , 1 = NULL      -- null
     , 1 <=> NULL    -- 0;
;

부정(Not-Equal) 비교(<>, !=)

  • 부정비교는 <>, != 둘 다 사용 가능.
  • 둘 중 하나로 통일을 권장.

NOT 연산자(!)

  • TRUE 혹은 FALSE의 결과값을 반대로(부정) 만드는 연산자
  • 연산 결과가 숫자 이외에도 적용된다. 이 경우는 제한된 상황에서만 사용한다.
select
       !1,        -- 0
       NOT 1,     -- 0
       ! false,   -- 1
       NOT false, -- 1
       !'hi',     -- 1
       NOT 'hi',  -- 1
       ! NULL,    -- null
       NOT NULL   -- null
;

AND(&&)와 OR(||) 연산자

  • MySQL에서는 AND, &&와 OR   를 비교 연산자로 사용한다.
  • 오라클에서는   을 문자열의 결합 연산자로 사용한다.
  • 이런 혼란을 해소하기 위하여 가능하면 AND와 OR만 사용한다.
  • AND와 OR가 괄호 없이 있을 경우 AND 부터 계산한다.
    • SELECT TRUE OR FALSE AND FALSE; -- 1
    • 순서대로 계산했으면 (TRUE OR FALSE) AND FALSE이므로 0을 반환해야 하나, 1을 반환한 것을 볼 수 있다.
    • 모호하므로 괄호로 연산을 묶자.

나누기(/, DIV)와 나머지(%, MOD) 연산자

  • /는 나누기, DIV는 나눈 후 소수점을 버린 정수, %와 MOD는 나눈 후 나머지를 가져온다.
SELECT
       29 / 9   -- 3.222
     , 29 DIV 9 -- 3
     , 29 % 9   -- 2
     , 29 MOD 9 -- 2
;

REGEXP 연산자

  • 정규표현식을 통한 비교 연산자
  • 정규표현식은 인덱스를 사용할 수 없다. 그러므로 최소한으로 사용한다. REGEX보다는 LIKE를 주로 사용.
  • SELECT 'abc' REGEXP '^[a-z]{3}$'; -- 1

LIKE 연산자

  • 정규표현식보다 간단하고 인덱스를 사용할 수 있는 LIKE를 주로 사용한다.
  • 다만, 검색값이 와일드카드부터 시작하면 인덱스를 사용할 수 없다.
  • 와일드 카드는 _와 %를 사용 가능하되 전자는 단 하나의 문자만 가능하나 후자는 갯수와 관계 없이 허용.
  • ESCAPE: %혹은 _을 검색 단어로 사용할 경우, 다른 문자를 와일드카드로 설정할 수 있다.
SELECT
    'abc' LIKE 'abc' -- 1
    , 'abc' LIKE 'ab' -- 0
    , 'abc' LIKE 'a%' -- 1
    , 'abc' LIKE 'a_' -- 0
    , 'abc' LIKE 'ab%' -- 1
    , 'abc' LIKE 'ab_' -- 1
    , 'abc%' LIKE 'abc/%' ESCAPE '/'-- 1
    , 'abc%' LIKE 'abc/%' ESCAPE '!'-- 0
;

BETWEEN 연산자

  • ‘크거나 같다’, ‘작거나 같다’ 연산 두 개를 합친 연산자. 인덱스 사용 가능.
  • BETWEEN은 선형으로 범위 전체를 읽으며 IN은 상수로 동등 비교를 여러 차례 수행한다. 그러므로 가능하다면 BETWEEN보다 IN을 사용한다.
-- dept_no와 emp_no이 정확하게 일치하는 레코드만 검색하며 rows는 3의 결과는 3이다.
SELECT * FROM dept_emp
WHERE dept_no IN('d003', 'd004', 'd005') AND emp_no = 10001;

-- dept_no의 범위에 해당하는 모든 레코드 약 10만개이며, 10만개 중 emp_no와 일치하는 레코드를 검색한다.
SELECT * FROM dept_emp
WHERE dept_no BETWEEN 'd003' AND 'd005' AND emp_no=10001;

IN 연산자

  • BETWEEN과 다르게 IN은 여러 값을 동등비교한다.
  • MySQL 8.0 이후부터는 튜플에 대한 검색을 하더라도 인덱스를 최적화할 수 있다.
SELECT * FROM dept_emp WHERE (dept_no, emp_no) IN (('d003', 10001), ('d001', 10017));

  • 위에서 검색 대상인 dept_no, emp_no은 PK이며 그 순서 또한 일치한다. 이때 실행계획을 살펴보면 key: PRIMARY, key_len: 20로서 프라이머리키 인덱스 전체를 정상적으로 사용함을 확인할 수 있다.

  • NOT IN은 동등이 아닌 인덱스 풀 스캔을 수행한다. 다만 프라이머리 키는 IN과 같이 효율적으로 처리된다.

RealMySQL 8.0, 11장을 참고하여 작성하였습니다.