: 여러 DBMS에서 통용되는 기본적인 연산자들 또한 MySQL에서도 거의 비슷하게 사용되지만 MySQL에서만 사용되는 연산자나 표기법이 존재
1. 리터럴 표기법 문자열
1.
문자열
: SQL 표준에서 문자열은 항상 홑따옴표 ‘를 사용해서 표시, 하지만 MySQL에서는 쌍따옴표를 사용해 문자열을 표기할 수도 있음
: MySQL 서버의 sql_mode 시스템 변숫값에 ANSI_QUOTES를 설정하면 쌍따옴표는 문자열 리터럴 표기에 사용할 수 없으며 테이블명이나 칼럼명의 충돌명을 피하려면
역따옴표가 아니라 쌍따옴표를 사용해야함
2.
숫자
: 숫자 값을 상수로 SQL에 사용할 때는 다른 DBMS와 마찬가지로 따옴표 없이 숫자값을 입력하면 됨
: 문자열 형태로 따옴표를 사용하더라도 비교 대상이 숫자 값이거나 숫자 타입의 칼럼이면 MySQL 서버가 문자열 값을 숫자 값으로 자동으로 변환함, 하지만 이처럼
숫자 값과 문자열 값을 비교할 때는 한가지 주의할 사항이 있음
SELECT * FROM tab_test WHERE number_column='10001';
SELECT * FROM tab_test WHERE number_column=10001;
SQL
복사
: 문자열과 숫자 타입으로 다를 때는 타입의 변환이 발생, MySQL은 숫자와 문자열 간 비교에서 숫자를 우선시하여 문자열 값을 숫자 값으로 변환한 후 비교를 수행함
: 첫 번째 쿼리는 주어진 상숫값을 숫자로 변환하는데, 이때는 상숫값 하나만 변환하므로 성능과 관련된 이슈가 발생하지 않음
: 두 번째 쿼리는 주어진 상숫값이 숫자 값인데 비교되는 칼럼은 문자열 칼럼으로 이때 MySQL은 문자열 칼럼을 숫자로 변환해서 비교, 즉 string_column 칼럼의 모든
문자열 값을 숫자로 변환해야 하므로 string_column에 인덱스가 있더라도 이를 이용하지 못함
3.
날짜
: 다른 DBMS에서 날짜 타입을 비교하거나 INSERT하려면 문자열을 DATE 타입으로 변환하는 코드가 필요
: MySQL에서는 정해진 형태의 날짜 포맷으로 표기 시 MySQL 서버가 자동으로 DATE나 DATETIME 값으로 변환하므로 STR_TO_DATE()같은 함수를 사용하지 않아도 됨
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');
SQL
복사
: 첫 번째 쿼리와 같이 날짜 타입의 칼럼과 문자열 값을 비교하는 경우 MySQL 서버는 문자열 값을 DATE 타입으로 변환해서 비교
: 두 번째 쿼리는 SQL에서 문자열을 DATE 타입으로 강제 변환해서 비교하는데, 이 두 쿼리의 차이점은 없음
4.
불리언
: BOOL이나 BOOLEAN이라는 타입이 있지만 사실 이것은 TINYINT 타입의 동의어, 테이블의 칼럼을 BOOL로 아니라 TINYINT라는 점을 알 수 있음
: MySQL에서는 다음 예제 쿼리와 같이 TRUE 또는 FALSE 형태로 비교하거나 값을 저장할 수 있는데 이는 BOOL 타입뿐만 아니라 숫자 타입의 칼럼도 적용되는 비교 방법
SELECT * FROM tb_boolean WHERE bool_value=FALSE;
SELECT * FROM tb_boolean WHERE bool_value=TRUE;
SQL
복사
2. MySQL 연산자
1.
동등 비교(=, <=>)
: 동등 비교는 다른 DBMS에서와 마찬가지로 = 기호를 사용해 비교를 수행, 하지만 MySQL에서는 동등 비교를 위해 <=> 도 제공
: <=> 연산자는 기본적으로는 = 연산자와 같으며 부가적으로 NULL 값에 대한 비교까지 수행, MySQL에서는 이 연산자를 NULL-Safe 비교 연산자라고 함, 양쪽 비교
대상 모두 NULL이면 TRUE를 반환하고 한 쪽만 NULL이면 TRUE를 반환
2.
부정 비교 (<>, !=)
: 같지 않다 비교를 위한 연산자는 <>를 일반적으로 많이 사용, 이와 함께 !=도 사용할 수 있음, 통일하는 것을 권장
3.
NOT 연산자(!)
: TRUE 또는 FALSE 연산의 결과를 반대로 만드는 연산자로 NOT을 사용하나 같은 용법으로 !를 사용할 수 있음
4.
AND(&&)와 OR(||) 연산자
: 일반적으로 DBMS에서는 불리언 표현식의 결과를 결합하기 위해 AND나 OR를 사용, MySQL에서는 이뿐만 아니라 &&와 ||의 사용도 허용
5.
나누기(/, DIV)와 나머지(%, MOD) 연산자
: 나누기 연산자는 일반적으로 알고 있는 / 연산자를 사용, 나눈 몫의 정수 부분만 가져오려면 DIV 연산자를 사용하고 나눈 결과 몫이 아닌 나머지는 % 또는 MOD를 사용
6.
REGEXP 연산자
: 문자열 값이 어떤 패턴을 만족하는지 확인하는 연산자로 RLIKE는 REGEXP와 똑같은 비교를 수행하는 연산자,
: 다음 예제는 ‘abc’라는 문자열이 ‘x’, ‘y’, ‘z’로 시작하는지 검증하는 표현식의 예
SELECT 'abc' REGEXP '^[x-z]';
SQL
복사
•
^ : 문자열의 시작을 표시
•
$ : 문자열의 끝을 표시
•
[] : 문자 그룹을 표시, [xyz], [x-z]라고 표현하면 x, y, z 중 하나인지 확인
•
() : 문자열 그룹을 표시, (xyz)라고 표현하면 세 문자 중 한 문자가 있는지 체크하는 것이 아니라 반드시 xyz가 모두 있는지 확인
•
| : 연결된 문자열 중 하나인지 확인 abc|xyz라고 표현하면 abc이거나 xyz인지 확인
•
. : 어떤 문자든지 1개의 문자를 표현하며 ...라고 표현하면 어떤 문자인지 상관없이 3개의 문자로 구성된 문자열을 찾음
•
* : 이 기호 앞에 표시된 정규 표현식이 0 또는 1번 이상 반복될 수 있다는 표시
•
+ : 이 기호 앞에 표시된 정규 표현식이 1번 이상 반복될 수 있다는 표시
•
? : 이 기호 앞에 표시된 정규 표현식이 0 또는 1번만 올 수 있다는 표시
7.
LIKE 연산자
: REGEXP 연산자보다는 훨씬 단순한 문자열 패턴 비교 연산자로 주로 자주 사용되는 연산자, LIKE 연산자는 인덱스를 사용할 수 있음
: LIKE 연산자는 정규 표현식을 검사하는 것이 아니라 어떤 상수 문자열이 있는지 없는지 정도를 판단하는 연산자
•
% : 0 또는 1개 이상의 모든 문자에 일치
•
_ : 정확하게 1개의 문자에 일치
8.
BETWEEN 연산자
: BETWEEN 연산자는 ‘크거나 같다’와 ‘작거나 같다’라는 두 개의 연산자를 하나로 합친 연산자
: BETWEEN 연산자는 다른 비교 조건과 결합해 하나의 인덱스를 사용할 때 주의해야할 점이 있음
SELECT * FROM dept_emp
WHERE dept_no='d003' AND emp_no=10001;
SELECT * FROM dept_emp
WHERE dept_no BETWEEN 'd003' AND 'd005' AND emp_no=10001;
SQL
복사
: 첫 번째 쿼리의 경우 dept_no와 emp_no 조건 모두 인덱스를 이용해 범위를 줄일 수 있음
: 두 번째 쿼리의 경우 BETWEEN은 크다 또는 작다 연산자와 같이 범위를 읽어야 하는 연산자라 모든 인덱스의 범위를 검색하게 됨
: BETWEEN과 IN을 동일한 비교 연산자로 생각하는 사람도 있는데 사실 BETWEEN은 크다와 작다 비교를 하나로 묶어 둔 것에 가까우며 IN은 동등 비교 연산자와 비슷하게
동작함, IN 연산자는 여러 개의 동등 비교를 하나로 묶은 것과 같은 연산자라서 IN과 동등 비교 연산자는 같은 형태로 인덱스를 사용
9.
IN 연산자
: IN은 여러 개의 값에 대해 동등 비교 연산을 수행하는 연산자
: 여러 개의 값이 비교되지만 범위로 검색하는 것이 아니라 여러 번의 동등 비교로 실행하므로 일반적으로 빠르게 처리됨, IN 연산자는 상수가 사용된 경우와 서브 쿼리가
사용된 경우로 나누어 생각해볼 필요가 있음
•
상수가 사용된 경우는 동등 비교와 동일하게 작동하므로 매우 빠르게 쿼리가 처리됨
•
서브쿼리가 사용된 경우는 최적화가 매우 까다로운데 8.0부터는 세미조인의 최적화가 많이 안정화되었음
3. MySQL 내장 함수
: DBMS 종류와 상관없이 기본적인 기능의 SQL 함수는 대부분 동일하게 제공, MySQL의 함수는 MySQL에서 기본으로 제공하는 내장 함수와 사용자 정의 함수로 구분
1.
NULL 값 비교 및 대체(IFNULL, ISNULL)
IFNULL()은 칼럼이나 표현식의 값이 NULL인지 비교하고 NULL이면 다른 값으로 대체하는 용도로 사용할 수 있는 함수
ISNULL()은 이름 그대로 인자로 전달한 표현식이나 칼럼의 값이 NULL인지 아닌지 비교하는 함수, 반환 값은 NULL일 경우 TRUE, 아니면 FALSE
2.
현재 시각 조회(NOW, SYSDATE)
: 두 함수 모두 현재의 시간을 반환하는 함수로서 같은 기능을 수행하나 그 동작 방법에 있어서 차이가 있음
: NOW()의 경우 하나의 SQL에서 모든 NOW()함수는 같은 값을 가짐
: SYSDATE()의 경우, 하나의 SQL 내에서도 호출되는 시점에 따라 결괏값이 달라짐
⇒ 이 때문에 두 가지 큰 잠재적인 문제가 존재함
•
첫 번째로는 SYSDATE() 함수가 사용된 SQL은 레플리카 서버에서 안정적으로 복제되지 못함
•
두 번째로는 SYSDATE() 함수와 비교되는 칼럼은 인덱스를 효율적으로 사용하지 못함
3.
날짜와 시간의 포맷(DATE_FORMAT, STR_TO_DATE)
: DATETIME 타입의 칼럼이나 값을 원하는 형태의 문자열로 변환해야 할 때는 DATE_FORMAT() 함수를 이용하면 됨
: MySQL 서버가 문자열에 사용된 날짜 타입의 포맷을 알 수 없을 경우에는 STR_TO_DATE를 사용하면 됨
4.
날짜와 시간의 연산(DATE_ADD, DATE_SUB)
: 특정 날짜에서 연도나 월일 또는 시간 등을 더하거나 뺄 때 사용하는 함수, DATE_ADD로 더하거나 빼는 처리를 모두 할 수 있기 때문에 딱히 DATE_SUB는 필요 없다.
: DATE_ADD()의 첫 번째 인자는 연산을 수행할 날짜, 두 번째 인자는 더하거나 빼고자 하는 월의 수나 일자의 수 등을 입력
5.
타임스탬프 연산(UNIX_TIMESTAMP, FROM_UNIXTIME)
: UNIX_TIMESTAMP() 함수는 ‘1970-01-01 00:00:00’으로부터 경과된 초의 수를 반환하는 함수
: UNIX_TIMESTAMP()는 인자가 없으면 현재 시각을 인자로 특정 날짜를 제공하면 해당 날짜와 시간의 타임스탬프 값을 반환, FROM_UNIXTIME() 함수는 UNIX_TIMESTAMP() 함수완
달리 인자로 전달한 타임스탬프 값을 DATETIME 타입으로 변환하는 함수
: MySQL의 TIMESTAMP 타입은 4바이트 숫자 타입으로 저장되기 때문에 실제로 가질 수 있는 값의 범위는 ‘1970-01-01 00:00:01’ ~ ‘2038-01-09 03:14:07’까지로 제한
6.
문자열 처리(RPAD, LPAD / RTRIM, LTRIM, TRIM)
: RPAD()와 LPAD() 함수는 문자열의 좌측 또는 우측에 문자를 덧붙여서 지정된 길이의 문자열로 만드는 함수, 둘 다 3개의 인자가 필요
⇒ 첫 번째 인자는 패딩 처리를 할 문자열, 두 번째 인자는 몇 바이트까지 패딩할 것인지, 세 번째 인자는 어떤 문자를 패딩할 것인지를 의미
: RTRIM(), LTRIM() 함수는 문자열의 우측 또는 좌측에 연속된 공백 문자를 제거해주는 함수, TRIM() 함수는 LTRIM()과 RTRIM()을 동시에 수행하는 함수
7.
문자열 결합(CONCAT)
: 여러 개의 문자열을 연결해서 하나의 문자열로 반환하는 함수로 인자의 개수에는 제한이 없음, 숫자 값을 인자로 전달하면 문자열 타입으로 변환한 후 연결
: 의도된 결과가 아닌 경우에는 명시적으로 CAST() 함수를 이용해 타입을 문자열로 변환하는 편이 안전
8.
GROUP BY 문자열 결합(GROUP_CONCAT)
: COUNT()나 MAX(), MIN(), AVG()등과 같은 그룹 함수 중 하나로 다른 함수들과 동일하게 (Aggregate, 여러 레코드의 값을 병합해서 하나의 값을 만들어 내는 함수)
: 주로 GROUP BY와 함께 사용하며 GROUP BY가 없는 SQL에서 사용하면 단 하나의 결괏값만 만들어냄
9.
값의 비교와 대체(CASE WHEN … THEN … END)
: CASE WHEN은 함수가 아니라 SQL 구문이지만 포함해서 설명, SWITCH 구문과 같은 역할을 함, CASE로 시작하고 END로 끝나야하며 필요한만큼 반복 사용이 가능
: 크게 2가지 방법으로 사용할 수 있음
•
단순히 코드 값을 실제 값으로 변환
•
특정 일자를 기준으로 이전인지 이후인지 비교해 설명을 붙이는 용도
10.
타입의 변환(CAST, CONVERT)
: SQL은 텍스트 기반으로 작동하기 때문에 SQL에 포함된 모든 입력값은 문자열처럼 취급, 이럴때 명시적으로 타입의 변환이 필요하면 CAST() 함수를 이용
: CONVERT() 함수도 CAST()와 거의 비슷하게 동작하며 단지 함수의 인자 사용규칙이 조금 다름
11.
이진값과 16진수 문자열(Hex String) 변환(HEX, UNHEX)
: HEX() 함수는 이진값을 사람이 읽을 수 있는 형태의 16진수의 문자열로 변환하는 함수, UNHEX()는 16진수의 문자열을 읽어서 이진값으로 변환하는 함수
12.
암호화 및 해시 함수(MD5, SHA, SHA2)
: MD5와 SHA 모두 비대칭형 암호화 알고리즘으로 인자로 전달한 문자열을 각각 지정한 비트 수의 해시 값을 만들어내는 함수
: SHA() 함수는 SHA-1 암호화 알고리즘을 사용하며 결과로 160비트 해시 값을 반환
: SHA2() 함수는 SHA 암호화 알고리즘보다 더 강력한 224비트부터 512비트 암호화 알고리즘을 사용해 생성된 해시 값을 반환
: MD5() 함수는 메시지 다이제스트 알고리즘을 사용해 128비트 해시 값을 반환
13.
처리 대기(SLEEP)
: SLEEP() 함수는 프로그래밍 언어나 셸 스크립트 언어에서 제공하는 “sleep” 기능을 수행
: 일반적으로 DBMS는 빠르게 쿼리르 처리하는 것을 항상 최선으로 생각하는데, 쿼리 실행 도중 멈춰서 대기하는 기능이 왜 필요할까라고 할 수 있음
⇒ 이는 SQL의 개발이나 디버깅 용도로 잠깐 대기하거나 일부러 쿼리의 실행을 오랜 시간 유지하고자 할 때 상당히 유용
14.
벤치마크(BENCHMARK)
: BENCHMARK() 함수는 SLEEP() 함수와 같이 디버깅이나 간단한 함수의 성능 테스트 용으로 유용한 함수
: 2개의 인자를 필요로 하며 첫 번째 인자는 반복해서 수행할 횟수이며 두 번째 인자로는 반복해서 실행할 표현식을 입력
: BENCHMARK() 함수로 얻은 쿼리나 함수의 성능은 그 자체로 의미가 없으며 두 개의 동일 기능을 상대적으로 비교 분석하는 용도로 사용할 것을 권장
15.
IP 주소 변환(INET_ATON, INET_NTOA)
: INET_ATON() 함수와 INET_NTOA() 함수를 이용해 IPv4 주소를 문자열이 아닌 부호 없는 정수 타입에 저장할 수 있게 제공
16.
JSON 포맷(JSON_PRETTY)
: JSON_PRETTY() 함수를 이용하면 JSON 칼럼의 값을 읽기 쉬운 포맷으로 변환
17.
JSON 필드 크기(JSON_STORAGE_SIZE)
: JSON 데이터는 텍스트 기반이지만 MySQL 서버는 디스크의 저장 공간을 절약하기 위해 JSON 데이터를 실제 디스크에 저장할 때 BSON 포맷을 사용
: 이때 BSON으로 변환 시 저장 공간의 크기가 얼마나 될지 예측하기가 어려움, 이를 위해 MySQL 서버에서는 JSON_STORAGE_SIZE() 함수를 제공
18.
JSON 필드 추출(JSON_EXTRACT)
: JSON 도큐먼트에서 특정 필드의 값을 가져오는 방법으로 2개의 인자를 필요로 함
: 첫 번째 인자는 JSON 데이터가 저장된 칼럼 또는 JSON 도큐먼트 자체, 두 번째 인자는 가져오고자 하는 필드의 JSON 경로를 명시
19.
JSON 오브젝트 포함 여부 확인(JSON_CONTAINS)
: JSON 도큐먼트 또는 지정된 JSON 경로에 JSON 필드를 가지고 있는지 확인하는 함수
20.
JSON 오브젝트 생성(JSON_OBJECT)
: RDBMS 칼럼의 값을 이용해 JSON 오브젝트를 생성하는 함수
21.
JSON 칼럼으로 집계(JSON_OBJECTAGG & JSON_ARRAYAGG)
: JSON_OBJECTAGG()와 JSON_ARRAYAGG() 함수는 GROUP BY 절과 함께 사용되는 집계 함수로서 RDBMS 칼럼의 값들을 모아 JSON 배열 또는 도큐먼트를 생성하는 함수
: 첫 번째 인좌아 두 번째 인자를 받는데, 첫 번째 인자는 키, 두 번째 인자는 값으로 사용되어 키 밸류 쌍 JSON 도큐먼트를 만들어 반환
22.
JSON 데이터를 테이블로 변환(JSON_TABLE)
: JSON_TABLE() 함수는 JSON 데이터의 값들을 모아서 RDBMS 테이블을 만들어 반환
