MySQL의 쿼리 작성 기초 및 연산자, 타입별 쿼리 작성법 정리

이번 포스팅에서는 MySQL의 쿼리 작성법에 대한 기초와 연산자, 타입별 쿼리 작성법들에 대해 알아보고 주의사항도 추가로 알아볼 예정이다.

참고로 ANSI 표준과 MySQL에서만 사용되는 ANSI 표준 형태가 아닌 연산자나 표기법을 함께 설명하지만

가독성이나 높은 호환성 측면에서는 최대한 ANSI 표준을 따라는 것이 좋을 것이다.

---

영어 대소문자 구분

MySQL 서버는 DB나 테이블이 디스크의 디렉토리나 파일로 매핑되기 때문에

설치된 운영체제에 따라 테이블명의 대소문자를 구분한다.

윈도우에서는 MySQL이 대소문자를 구분하지 않지만 Unix 계열의 운영체제에서는 대소문자를 구분한다.

따라서 윈도우에서 운영되던 데이터를 Unix 계열(Mac)로 가져오거나 그 반대의 경우 문제가 될 수 있다.

MySQL 서버가 운영체제와 관계없이 대소문자 구분 영향을 받지 않게하려면 아래와 같이 시스템 변수를 설정하면 된다.

(아래 시스템 변수는 MySQL 서버 초기화 시에만 적용 가능하다.)

-- my.cnf 설정 파일에 아래 시스템 변수 설정
lower_case_table_names=1

해당 값은 Window의 경우 기본값이 1, Mac의 경우 기본값이 2로 각 상수별로 다음과 같은 의미를 갖는다.

 

값	의미
0	저장 시 입력한 그대로 저장, 조회 시 대소문자 구분 O
1	저장 시 소문자로 저장, 조회 시 대소문자 구분 X
2	저장 시 입력한 그대로 저장, 조회 시 대소문자 구분 X

---

MySQL 예약어

생성하는 DB나 테이블, 컬럼 이름에 예약어를 사용할 경우 항상 역따옴표 (`)나 쌍따옴표(”)로 감싸야한다.

모든 예약어를 다 외울 순 없으므로 가장 좋은 방법은 직접 생성해보고

에러를 보고 판단하여 감쌀지 말지 여부를 판단하는 것도 나쁘지 않다.

---

MySQL 공식문서의 SQL 문법 표기를 읽는 방법

공식문서에서 쿼리문 작성법에 대해 처음보면 어떻게 사용하는지 이해가 힘든 부분이있다.

MySQL에서는 SQL 문법을 표기하는 방법이 있는데 이를 한번 알아보자.

-- INSERT Statement

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    { {VALUES | VALUE} (value_list) [, (value_list)] ... }
    [AS row_alias[(col_alias [, col_alias] ...)]]
    [ON DUPLICATE KEY UPDATE assignment_list]

value:
    {expr | DEFAULT}

value_list:
    value [, value] ...

• 대문자 : 키워드를 의미
• 이텔릭체 : 사용자가 선택해서 작성하는 (테이블명, 컬럼명등 표현식)토큰을 의미. value 또는 value_list와 같이 사용자가 작성하는 키워드명이 아니라면 해당 항목에 대해 상세한 문법이 설명되어 있다.
• 대괄호 : 해당 키워드나 표현식이 선택 사항임을 의미
• 파이프 : 앞과 뒤의 키워드나 표현식 중 단 하나만 선택할 수 있음을 의미
• 중괄호 : 괄호내의 아이템 중에서 반드시 하나를 사용해야함을 의미
• … : 앞에 명시된 키워드나 표현식의 조합이 반복될 수 있음을 의미

---

동등 비교 연산자

다른 DBMS와 마찬 같이로 동등 비교 시 “=”를 사용할 수 있지만

MySQL에서는 “<=>”와 같은 연산자도 제공한다.

“<=>”연산자는 “=” 연산자와 같지만 부가적으로 NULL 값에 대한 비교까지도 수행하며 NULL-Safe 비교 연산자라고도 한다.

“=”와 “<=>” 연산자의 차이를 보면 다음과 같다.

SELECT 1 = 1, NULL = NULL, 1 = NULL;

 

SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;

 

부정 비교를 위해서는 “<>”와 “!=”를 사용할 수 있다.

---

NOT 연산자

TRUE, FALSE 결과를 반대로 만드는 연산자로는 “NOT”, “!”을 사용한다.

두 연산자 모두 BOOLEAN 값 뿐아니라 숫자나 문자열 표현식에도 사용할 수 있어 주의가 필요한데

MySQL에서는 다음과 같은 숫자 문자도 BOOLEAN 값으로 표현한다.

• 0은 FALSE(0)로 취급
• 0이 아닌 모든 숫자는 TRUE(1)로 취급
• 문자열이 숫자로 시작하면 그 숫자를 추출해서 비교 (ex : SELECT ! ‘10kaki’는 1로 시작해 1의 부정형인 FALSE(0) 반환)
• 숫자로 시작하지 않는 문자열은 FALSE(0)을 반환
• 비어있지 않은 문자열은 FALSE(0)를 반환

이렇게 불리언이 아닌 값에 NOT을 사용하면 결과를 예측하기 어려울 수 있어서

가급적 명확한 비교 연산을 사용하는 것이 좋다.

---

나누기와 나머지 연산자

나머지 연산자는 “/”를 사용하고 나눈 목의 정수 부분만 가져오려면 DIV 연산자를 사용한다.

나눈 결과 몫이 아닌 나머지를 가져오기 위해선 “%” 또는 MOD 연산자를 사용한다.

SELECT 29/ 9; -- 결과 : 3.2222
SELECT 29 DIV 9; -- 결과 : 3
SELECT MOD(29,9); -- 결과 : 2
SELECT 29 MOD 9; -- 결과 : 2
SELECT 29 % 9; -- 결과 : 2

---

Regex 연산자

문자열 값이 어떤 패턴을 만족하는지 확인하는 연산자로 RLIKE는 REGEXP와 똑같은 비교를 수행하는 연산자이다.

(이 연산자를 사용할 경우 인덱스를 사용할 수 없다는 단점이 있다.)

RLIKE가 문자열값의 오른쪽 일치용 LIKE연산자(Right LIKE)로 혼동할 수 있는데

MySQL의 RLIKE는 정규표현식(Regular expression)을 비교하는 연산자라는 점을 기억하자.

 

REGEXP 연산자를 사용하려면 다음과 같이

REGEXP 연산자의 좌측에 비교 대상 문자열 값 또는 문자열 칼럼을 우측에 검증하고자하는 정규표현식을 사용하면 된다.

-- abc라는 문자열 값이 x, y, z 문자로 시작하는지 검증하는 표현식
SELECT 'abc' REGEXP '^[x-z]'; -- 결과 : 0 (FALSE)

REGEXP 연산자의 정규 표현식은 POSIX 표준으로 구현돼 있어 아래 AWS 문서를 참고하면 도움이 될 것이다.

https://docs.aws.amazon.com/ko_kr/redshift/latest/dg/pattern-matching-conditions-posix.html

---

LIKE 연산자

LIKE 연산자는 다음과 같은 와일드 카드와 같이 사용하며

REGEXP보다 간단하게 패턴 매칭에 사용하며 전체 문자열이 패턴과 일치해야한다.

• “%” : 0 또는 1개 이상의 모든 문자에 일치
• “_” : 정확히 1개의 문자에 일치

-- ka로 시작하는 문자열 전부
SELECT * FROM employees WHERE first_name LIKE 'ka%'; -- 결과 : kaki, kakao 등

-- ki으로 끝나는 문자열 전부
SELECT * FROM employees WHERE first_name LIKE '%ki'; -- 결과 : kaki, corki 등

-- 중간에 ak를 포함하는 문자열 전부
SELECT * FROM employees WHERE first_name LIKE '%ak%'; -- 결과 : kaki, boakk 등

-- 두 번째 글자만 다른 4글자 이름
SELECT * FROM employees WHERE first_name LIKE 'k_ki'; -- 결과 : kaki, kuki 등

-- 첫 번째 글자만 다른 4글자 이름
SELECT * FROM employees WHERE first_name LIKE '_aki'; -- 결과 : kaki, caki 등

 

만약 “%”나 “_” 문자 자체를 비교한다면 다음과 같이 ESCAPE 절을 LIKE 조건 뒤에 추가하면 된다.

SELECT 'a%' LIKE 'a/%' ESCAPE '/'; -- 결과 : 0 (TRUE)

주의할점으로 와일드카드를 검색어 앞에 사용하게 될 경우 인덱스 레인지 스캔을 사용하지 못하고

인덱스 풀 스캔 방식으로 쿼리가 처리된다는 것을 주의하자.

---

BETWEEN 연산자와 IN 연산자

“크거나 같다”, “작거나 같다”가 합쳐진 연산자이다.

이 연산자는 다른 비교 조건과 결합해 하나의 인덱스를 사용할 때 주의할점이 있다.

 

employees 테이블에 아래와 같이 복합인덱스가 설정되어 있다고 가정해보자.

-- CREATE INDEX idx_employee_composite ON employees (emp_no, name, salary);

SELECT * FROM employees 
WHERE emp_no = 'a002' AND name = 'ACE' AND salary >= 5000;

SELECT * FROM employees 
WHERE emp_no BETWEEN 'a002' AND 'a004' AND name = 'ACE' AND salary >= 5000;

첫 번재 쿼리는 emp.no와 name 모두 salary 범위 조건을 체크하기 전에 비교 범위를 줄여주는 방법으로 사용됐다.

하지만 두 번째 쿼리에서는 범위 연산자인 BETWEEN이 첫 번째 조건으로 사용되어 비교 범위를 줄여주지 못하고

처음부터 많은 레코드를 읽게 된다.

 

이 경우 다음과 같이 IN 연산자를 사용해 작업 범위를 줄이는 용도로 인덱스를 이용할 수 있도록 개선할수도 있다.

SELECT * FROM employees 
WHERE emp_no IN ('a002', 'a003', 'a004') AND name = 'ACE' AND salary >= 5000;

IN 연산자는 여러 개의 동등 비교를 하나로 묶은 것과 같은 연산자라 IN과 동등 비교 연산자는 같은 형태로 인덱스를 사용하기 때문에

BETWEEN 처럼 범위를 검색하는 것이 아닌 특정 레코드만 동등 비교를 여러 번 수행하는 것과 같은 효과가 있다.

---

문자열 타입 쿼리 작성

SQL 표준(ANSI 표준)에서 문자열은 항상 홑따움표(’)를 사용해 표시한다.

하지만 MySQL에서는 다음과 같이 쌍따옴표를 사용해 문자열을 표기할 수도 있다.

SELECT * from member WHERE name = 'kaki';
SELECT * from member WHERE name = "kaki";

또한 SQL 표준에서 문자열 값에 홑따옴표가 포함될 경우 두 번 연속해서 입력하면 되지만

MySQL에서는 쌍따옴표와 홑따옴표를 혼합해 이 문제를 피해갈 수 있다.

-- SQL 표준 방식
SELECT * from member WHERE name = 'k''aki'; -- k'aki를 찾음
SELECT * from member WHERE name = 'k"aki'; -- k"aki를 찾음 (쌍따옴표는 특별한 처리 필요 x)

-- MySQL 확장 방식
SELECT * from member WHERE name = "k'aki"; -- k'aki를 찾음 (홑따옴표는 특별한 처리 필요 x)
SELECT * from member WHERE name = "k""aki"; -- k"aki를 찾음

 

---

숫자 타입 쿼리 작성

숫자는 감싸는 것 없이 그대로 입력하면 된다.

만약 문자열 형태로 사용하더라도 숫자 값이거나 숫자 타입의 컬럼임이면 MySQL 서버가 숫자 값으로 자동 변환한다.

 

하지만 여기서 주의할점이 있다.

SELECT * from member WHERE number_column = '100';
SELECT * from member WHERE string_column = 100;

위 쿼리의 경우 두 비교 대상이 문자열과 숫자 타입으로 달라 자동으로 타입의 변환이 발생한다.

 

첫 번째 쿼리의 경우 주어진 상수값을 숫자로 변환하는데 상수값만 변환하므로 문제가 발생하지 않고 인덱스를 활용 가능하다.

반면 두 번째 쿼리의 경우 주어진 상수값이 숫자, 비교 컬럼이 문자열으로 비교되고 있다.

이 경우 '100', ' 100', '100a'와 같이 100으로 변환될 수 있는 다양한 문자열이 존재하기 때문에 

인덱스가 걸려있더라도 활용할 수 없는 문제가 발생할 수 있다.

또한 문자가 숫자가 아닌 알파벳과 같은 문자일 경우 숫자 변환 자체가 안되므로 쿼리가 실패한다.

 

이를 주의하기 위해 숫자 값은 숫자 타입의 컬럼과 비교할 수 있도록 하자.

---

날짜 타입 쿼리 작성

다른 DBMS는 날짜 타입을 비교하거나 저장하려면 문자열을 DATE 타입으로 변환해야하지만

MySQL에서는 정해진 형태의 날짜 타입(DATE, DATETIME, TIMESTAMP)을 표기하면 자동으로 지정한 타입으로 변환해준다.

타입	설명
DATE	문자열의 날짜 부분만 저장
DATETIME	문자열의 날짜, 시간 그대로 저장
TIMESTAMP	문자열의 날짜, 시간을 UTC로 변환하여 저장

 

자동 변환에 대해 좀더 이해하기 쉽도록 테스트한 쿼리의 결과이다.

CREATE TABLE date_test (
    id INT AUTO_INCREMENT PRIMARY KEY,
    date_col DATE,
    datetime_col DATETIME,
    timestamp_col TIMESTAMP
);

-- 1. 날짜만 입력
INSERT INTO date_test (date_col, datetime_col, timestamp_col) 
VALUES ('2024-12-28', '2024-12-28', '2024-12-28');

-- 2. 날짜와 시간 입력
INSERT INTO date_test (date_col, datetime_col, timestamp_col) 
VALUES ('2024-12-28 14:30:00', '2024-12-28 14:30:00', '2024-12-28 14:30:00');

-- 데이터 확인
SELECT * FROM date_test;

 

DATETIME VS TIMESTAMP

DATETIME과 TIMESTAMP가 유사해보이기도 한다.

하지만 다음과 같은 차이점이 있다.

	DATETIME	TIMESTAMP
저장	타임존과 무관하게 입력한 값을 그대로 저장	입력값을 UTC로 변환하여 저장
조회	저장된 값 그대로 조회	조회 시 현재 세션의 타임존으로 변환
저장 범위	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59	1970-01-01 00:00:01 ~ 2038-01-19 03:14:07 (UTC)
용량	8바이트	4바이트
사용 예시	- 타임존과 무관하게 항상 같은 값을 표시해야 할 때 (생일, 기념일 등 특정 시점을 기록)	- 여러 타임존의 사용자가 접근하는 시스템의 경우 (글로벌 서비스) - 서버가 다른 타임존으로 이전될 수 있는 경우

 

---

Boolean 타입 쿼리 작성

BOOL이나 BOOLEAN이라는 타입이 있지만 MySQL에서 내부적으로는 TINYINT(1)로 처리되어 저장된다.

이 때 TRUE는 1, FALSE는 0으로 저장된다.

대부분의 자바 DB 접근 기술들이 TINYINT를 Boolean 타입으로 자동 변환해주는 기능을 제공하지만

반환 타입을 Boolean 타입이 아닌 Object로 선언하게 될 경우 Integer 타입으로 1,0이 반환되는 문제가 생길 수도 있으니 주의가 필요하다.

---

참고자료

• Real MySQL 8.0 2장