7.3 MySQL 연산자와 내장 함수

7.3.1 리터럴 표기법

문자열

  • SQL 표준에서 문자열은 항상 홑따옴표(‘)를 사용해서 표시한다.

  • SELECT * FROM departments WHERE dept_no='d001';
SELECT * FROm departments WHERE dept_no="d001";

  • 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 표준 표기법만 사용할 수 있게 강제하려면 sql_mode 시스템 변수 값에 “ANSI”를 설정하면 된다. 하지만 이 설정은 대부분 쿼리의 작동 방식에 영향을 미치므로 프로젝트 초기에 적용하는 것이 좋다.

숫자

  • 숫자 값을 상수로 SQL에 사용할 때는 다른 DBMS와 마찬가지로 따옴표 없이 숫자 값을 입력하면 된다. 또한 문자열 형태로 따옴표를 사용하더라도 비교대상이 숫자 값이거나 숫자 타입의 칼럼이면 MySQL이 문자열 값을 숫자 값으로 자동 변환해준다.
  • MySQL은 숫자 타입과 문자열 타입 간의 비교에서 숫자 타입을 우선시하므로 문자열 값을 숫자 값으로 변환한 후 비교를 수행한다.
  • 즉, string_column 칼럼의 모든 문자열 값을 숫자로 변환해서 비교를 수행해야 하므로 string_column에 있다 하더라도 이를 이용하지 못한다. 만약 string_column에 알파벳과 같은 문자가 포함된 경우에는 숫자 값으로 변환할 수 없으므로 쿼리 자체가 실패할 수도 있다.

문제점을 제거하려면 숫자 값은 숫자 타입의 칼럼에만 저장해야 한다.

날짜

  • 다른 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');

불리언

  • 테이블의 칼럼을 BOOL로 생성한 뒤에 조회해보면 칼럼의 타입이 BOOL이 아니라 TINYINT라는 점을 알 수 있다.

  • CREATE TABLE tb_boolean (bool_value BOOLEAN);
INSERT INTO tb_boolean VALUES (FALSE);
SELEC * FROM tb_boolean WHERE bool_value=FALSE;
SELECT * FROm tb_boolean WHERE bool_value=TRUE;
    • 위의 쿼리에서 TRUE나 FALSE로 비교했지만 실제로는 값을 조회해 보면 0 또는 1값이 조회된다. TRUE, FALSE 값을 정수로 맵핑해서 사용한다.
    • 모든 숫자 값이 TRUE나 FALSE 두 개의 불리언 값으로 매핑되지 않는다. 불리언 타입을 꼭 사용하고 싶다면 ENUM 타입으로 관리하는 것이 조금 더 명확하다.

7.3.2 MySQL 연산자

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

  • 동등 비교는 다른 DBMS에서도 마찬가지로 “=” 기호를 사용해 비교를 수행하면 된다. 하지만 MySQL에서는 동등 비교를 위해 “<=>” 연산자도 제공한다. “<=>” 연산자는 “=” 연산자와 같으며, 부가적으로 NULL 값에 대한 비교까지 수행한다.
  • MySQL에서는 이 연산자를 NULL-Safe 비교 연산자라고 한다.
  • NULL-Safe 비교 연산자를 이용해 비교한 결과를 보면 양쪽 비교 대상 모두 NULL이면 TRUE를 반환하고, 한쪽만 NULL이라면 FALSE를 반환한다. 즉 “<=>” 연산자는 NULL을 하나의 값으로 인식하고 비교하는 방법이라고 볼 수 있다.

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

  • “같지 않다” 비교를 위한 연산자는 “◇”를 일반적으로 많이 사용한다.
  • 하나의 SQL문장에서 “◇”와 “!=”가 혼용되면 가독성이 떨어지므로 통일해서 사용하도록 한다.

NOT 연산자 ( ! )

  • TRUE 또는 FALSE 연산의 결과를 반대로 만드는 연산자로 “NOT”를 사용한다.
  • 사실 NOT이나 “!” 불리언 값뿐만 아니라 숫자나 문자열 표현식에서도 사용할 수 있지만 부정의 결과 값을 정확히 예측할 수 없는 경우에는 사용을 자제하는 것이 좋다.

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

  • 일반적으로 DBMS에서는 불리언 표현식의 결과를 결합하기 위해 AND나 OR을 사용한다. MySQL에서는 AND와 OR뿐 아니라 “&&”와 “   “의 사용도 허용한다.
  • &&는 AND 연산자와 같고   는 OR 연산자와 같다. 오라클에서는   가 불리언 표현식의 결합 연산자가 아니라 문자열을 결합하는 연산자로 사용한다.
  • 만약 오라클에서 운영되던 애플리케이션을 MySQL로 이관한다거나 문자열 결합 연산에 “   “를 사용하고 싶을 수도 있다. 이때는 sql_mode 시스템 변수 값에 PIPE_AS_CONCAT를 설정하면 된다. 물론 이 설정이 활성화되면 불리언 표현식을 결합할 때 “&&” 연산자는 사용할 수 있지만 “   “연산자는 사용할 수 없다.
  • SQL의 가독성을 높이기 위해 다른 용도로 사용될 수 있는 “&&” 연산자와 “   “연산자는 사용을 자제하는 것이 좋다.

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

  • 나누기 연산자는 일반적으로 알고 있는 “/” 연산자를 사용한다. 나눈 몫의 정수 부분만 가져오려면 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

REGEXP 연산자

  • 문자열 값이 어떤 패턴을 만족하는지 확인하는 연산자
  • RLIKE는 REGEXP와 똑같은 비교를 수행하는 연산자다. RLIKE는 가끔 문자열 갑의 오른쪽 일치용 LIKE 연산자로 혼동할 때가 있는데, MySQL의 RLIKE는 정규 표현식을 비교하는 연산자이다.

  • REGEXP 연산자를 사용하는 방법은 REGEXP 연산자의 와측에 비교 대상 문자열 값 또는 문자열 칼럼, 그리고 우측에 검증하고자 하는 정규 표현식을 사용하면 된다.

  • // "abc"라는 문자열 값이 'x', 'y', 'z' 문자로 시작하는지 검증하는 표현식
SELECT 'abc' REGEXP '^[x-z]';
= 0
POSIX 정규 표현식 심벌 종류
  • ^ : 문자열의 시작을 표시, 정규 표현식은 그 표현식에 일치하는 부분이 문자열의 시작이나 중간 또는 끝 부분 어디에 나타나든 관계없지만 “^” 심벌을 표현식의 앞쪽에 넣어 주면 반드시 일치하는 부분이 문자열의 제일 앞쪽에 있어야 함을 의미한다.
  • $ : 문자열의 끝을 표시. “^”와는 반대로 표현식의 끝 부분에 “$”를 넣어 주면 반드시 일치하는 부분이 문자열의 제일 끝에 있어야 한다.
  • [] : 문자 그룹을 표시 [xyz] 또는 [x-z]라고 표현하면 ‘x’, ‘y’ 또는 ‘z’ 문자 중 하나인지 확인하는 것이 된다. 대괄호는 문자열이 아니라 문자 하나와 일치하는지를 확인하는 것이다.
  • () : 문자열 그룹을 표시 (xyz)라고 표현하면 세 문자 중 한 문자가 있느지 체크하는 것이 아니라 반드시 “xyz”가 모두 있는지 확인하는 것이다.
  • : “ “로 연결된 문자열 중 하나인지 확인한다. abc xyz라고 표현하면 “abc”이거나 “xyz”인지 확인하는 것이다.
  • . : 어떠한 문자든지 1개의 문자를 표시하며, 정규 표현식으로 “…“이라고 표현했다면 3개의 문자로 구성된 문자열을 찾는 것이다.
  • * : 이 기호 앞에 표시된 정규 표현식이 0 또는 1번이상 반복될 수 있다는 표시다.
  • + : 이 기호 앞에 표시된 정규 표현식이 1번이상 반복될 수 있다는 표시다.
  • ? : 이 기호 앞에 표시된 정규 표현식이 0 또는 1번만 올 수 있다는 표시다.
  1. [0-9]* : 0~9까지의 숫자만 0 또는 1번 이상 반복되는 문자열을 위한 정규 표현
  2. [a-z]* : a~z까지의 소문자 알파벳만 0 또는 1번 이상 반복되는 문자열을 위한 정규 표현
  3. [a-zA-Z]* : a~z까지 그리고 A~Z까지 대소문자 알파벳만 0 또는 1번 이상 반복되는 문자열을 위한 정규 표현
  4. [a-zA-Z0-9]* : 영문 대소문자와 숫자만으로 구성된 문자열에 대한 정규 표현
  5. ^Tear : Tear 문자열로 시작하는 정규 표현
  6. Tear$ : Tear 문자열로 끝나는 정규 표현
  7. ^Tear$ : Tear와 같은 무자열에 대한 정규 표현, 이 경우는 T로 시작하고 연속해서 ear이 나타나야 하며, 그 뒤에 아무런 문자가 없어야 한다.

REGEXP 연산자를 문자열 칼럼 비교에 사용할 때 REGEXP 조건의 비교는 인덱스 레인지 스캔을 사용할 수 없다. 따라서 WHERE 조건절에 REGEXP 연산자를 사용한 조건을 단독으로 사용하는 것은 성능상 좋지 않다. 가능하다면 범위를 줄일 수 있는 조건과 함께 REGEXP 연산자를 사용해야 한다.

REGEXP나 RLIKE 연산자의 경우, 바이트 단위의 비교를 수행하므로 멀티 바이트 문자나 악센트가 포함된 문자에 대한 패턴 검사는 정확하지 않을 수도 있다. 알파벳이나 숫자 이외의 문자셋이 저장되는 카럼에 REGEXP나 RLIKE를 사용할 때는 테스트를 충분히 하는 것이 좋다.

LIKE 연산자

  • REGEXP 연산자보다는 훨씬 단순한 문자열 패턴 비교 연산자이지만 DBMS에서는 LIKE 연산자를 더 많이 사용한다.
  • REGEXP 연산자는 인덱스를 전혀 사용하지 못한다는 단점이 있다. LIKE 연산자는 인덱스를 이용해 처리할 수도 있다.
SELECT 'abcdef' LIKE 'abc%';
= 1

SELECT 'abcdef' LIKE '%abc';
= 0

SELECT 'abcdef' LIKE '%ef';
= 1
  • LIKE에서 사용할 수 있는 와일드카드 문자는 %와 _가 전부다. REGEXP는 비교 대상 문자열의 일부에 대해서만 일치해도 TRUE를 반환하는 반면, LIKE는 항상 비교 대상 문자열의 처음부터 끝까지 일치하는 경우에만 TRUE를 반환한다.
    • % : 0 또는 1개 이상의 모든 문자에 일치 (문자 내용 관계없이)
    • _ : 정확히 1개의 문자에 일치 (문자의 내용 관계없이)
  • LIKE 연산자는 와일드카드 문자가 검색어의 뒤쪽에 있다면 인덱스 레인지 스캔으로 사용할 수 있지만 와일드카드가 검색어 앞쪽에 있으면 인덱스 레인지 스캔을 사용할 수 없다.
  • 와일드 카드가 검색어의 앞쪽에 있으면 인덱스의 Left-most 특성으로 인해 레인지 스캔을 사용하지 못하고 인덱스를 처음부터 끝까지 읽는 인덱스 풀스캔방식으로 쿼리가 처리됨을 알 수 있다.

BETWEEN 연산자

  • BETWEEN 연산자는 “크거나 같다”와 “작거나 같다”라는 두 개의 연산자를 하나로 합친 연산자다.
  • 이 연산자는 다른 비교 조건과 결합해 하나의 인덱스를 사용할 때 주의할 점이 있다.
    • 동등 비교 연산자와 BETWEEN 연산자를 이용해 부서 번호와 사원 번호로 dept_emp 테이블을 조회하는 다음 쿼리를 한번 생각해 보자.
    • 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;

  • dept_emp 테이블에는 (dept_no+emp_no) 칼럼으로 인덱스가 생성돼 있다. 그래서 첫 쿼리는 dept_no와 emp_no 조건 모두 인덱스를 이용해 범위를 줄여주는 방법으로 사용할 수 있다. 하지만 두 번째 쿼리에서 사용한 BETWEEN은 크다 또는 작다 연산자와 같이, 범위를 읽어야 하는 연산자라서 dept_no가 ‘d003’보다 크거나 같고 ‘d005’보다 작거나 같은 모든 인덱스의 범위를 검색해야만 한다. 결국 BETWEEN이 사용된 두 번째 쿼리에서 emp_no=10001 조건은 비교 범위를 줄이는 역할을 하지 못한다.

  • BETWEEN과 IN을 비슷한 비교 연산자로 생각하는 사람도 있는데 사실 BETWEEN은 크다와 작다 비교를 하나로 묶어둔 것에 가깝다. 그리고 IN 연산자의 처리 방법은 동등 비교(=) 연산자와 비슷하다. IN 연산자는 여러 개의 동등 비교를 하나로 묶은 것과 같다.
SELECT * FROM employees WHERE emp_no BETWEEN 10001 AND 400000;
SELECT * FROM employees WHERE emp_no>= 10001 AND emp_no<=400000;
  • BETWEEN 비교는 하나의 비교 조건으로 처리하지만, 크다와 작다의 조합으로 비교하는 경우 두개의 비교 조건으로 처리한다는 것이 큰 차이다. 실제 MySQL의 옵티마이저가 최적화해서 실행하기 직전의 쿼리를 봐도 BETWEEN 연산자를 크다 작다의 연산자로 변환하지 않고 BETWEEN을 그대로 유지한다.

IN 연산자

  • 여러 개의 값에 대해 동등 비교 연산을 수행하는 연산자다. 여러 개의 값이 비교되지만 범위로 검색하는 것이 아니라 여러 번의 동등 비교로 실행하기 때문에 일반적으로 빠르게 처리된다.
  • MySQL에서 IN 연산자는 사용법에 따라 상당히 비효율적으로 처리될 때도 많다.
// 사원번호가 10001, 10002, 10003 인 사원의 정보를 조회한다.
SELECT * FROM employees WHERE emp_no IN (10001, 10002, 10003);

// 다음 예제와 같이 IN 연산자를 이용해 NULL 값을 검색할 수는 없다. 값이 NULL인 레코드를 검색하려면 NULL-Safe 연산자인 "<=>" 또는 IS NULL 연산자 등을 사용해야 한다.
SELECT * FROM employees WHERE emp_no IN (10001, 10002, NULL);

7.3.3 MySQL 내장 함수

  • DBMS 종류에 관게없이 기본적인 기능의 SQL 함수는 대부분 동일하게 제공된다. 하지만 함수의 이름이나 사용법은 표준이 없으므로 DBMS별로 거이 호환되지 않는다.
  • MySQL의 함수는 MySQL에서 기본적으로 제공하는 내장 함수와 사용자가 직접 작성해서 추가할 수 있는 사용자 정의 함수(UDF)로 구분된다.
  • MySQL에서 제공하는 C/C++ API를 이용해 사용자가 원하는 기능을 직접 함수로 만들어 추가할 수 있는데, 이를 사용자 정의 함수라고 한다.

NULL 값 비교 및 대체(IFNULL, ISNULL)

  • IFNULL() : 칼럼이나 표현식의 값이 NULL인지 비교하고, NULL이면 다른 값으로 대체하는 용도로 사용할 수 있는 함수, 2개의 인자를 전달하는데 첫 번째 인자는 NULL인지 아닌지 비교하려면 칼럼이나 표현식을, 두 번째 인자로는 첫 번째 인자의 값이 NULL일 경우 대체할 값이나 칼럼을 설정한다. 반환값은 첫 번째 인자가 NULL이 아니면 첫 번째 인자의 값을, 첫 번째 인자의 값이 NULL이면 두 번째 인자의 값을 반환한다.
  • ISNULL() : 인자로 전달한 표현식이나 칼럼의 값이 NULL인지 아닌지 비교하는 함수다. 반환되는 값은 인자의 표현식이 NULL이면 True(1), NULL이 아니면 FALSE(0)을 반환한다.
SELECt IFNULL(NULL, 1)
= 1

SELECT IFNULL(0, 1);
= 0

SELECT ISNULL(0);
= 0

SELECT ISNULL(1/0);
= 1

현재 시각 조회(NOW, SYSDATE)

  • 두 함수 모두 현재의 시간을 반환하는 함수로서 같은 기능을 수행한다. 하지만 NOW()와 SYSDATE() 함수는 작동 방식에서 큰 차이가 있다. 하나의 SQL에서 모든 NOW() 함수는 같은 값을 가지지만 SYSDATE() 함수는 SQL 내에서도 호출되는 시점에 따라 결과 값이 달라진다.
  • NOW()는 SLEEP()를 사용해도 같은 결과를 반환함. SYSDATE()는 SLEEP()를 사용하면 해당 시간만큼 시간 차이가 난다.

SYSDATE()

  • 함수가 호출될 때마다 다른값을 반환하므로 상수가 아니다. 그래서 인덱스를 스캔할 때도 매번 비교되는 레코드마다 함수를 실행해야 한다.

NOW()

  • 쿼리가 실행되는 시점에서 실행되고 값을 할당받아서 그 값을 SQL 문장의 모든 부분에서 사용하게 되기 때문에 쿼리가 1시간동안 실행되더라도 실행되는 위치나 시점에 관계없이 항상 같은 값을 보장할 수 있는 것이다.

꼭 필요한 때가 아니라면 SYSDATE() 함수를 사용하지 않는 편이 좋겠지만 이미 SYSDATE() 함수를 사용하고 있다면 MySQL 서버의 설정파일에 sysdate-is-now 설정을 넣어 주는 것이 이런 문제점을 제거하는 빠른 해결책이다.

sysdate-is-now가 설정되면 SYSDATE() 함수도 NOW() 함수와 같이 함수의 호출 시점에 관게없이 하나의 SQL에서는 같은 값을 갖게 된다.

날짜와 시간의 포맷(DATE_FORMAT, STR_TO_DATE)

  • DATETIME 타입의 칼럼이나 값을 원하는 형태의 문자열로 변환해야 할 때는 DATE_FORMAT() 함수를 이용하면 된다.

    • 지정문자 내용
      %Y 4자리 년도
      %m 2자리 숫자 표시의 월(00 ~ 12)
      %d 2자리 숫자 표시의 일자(00 ~ 31)
      %H 2자리 숫자 표시의 시(00 ~ 23)
      %i 2자리 숫자 표시의 분(00 ~ 59)
      %s 2자리 숫자 표시의 초(00 ~ 59)

  • 위의 지정자를 이용해 다음과 같이 필요한 포맷 또는 필요한 부분만의 문자열로 변환할 수 있다.

  • SELECT DATE_FORMAT(NOW(), '%Y-%m-%d') AS current_dt;
= 2011-04-30
  
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS current_dttm;
= 2011-04-30 15:13:24
  • SQL에서 표준 형태(년-월-일 시:분:초)로 입력된 문자열은 필요한 경우 자동적으로 DATETIME 타입으로 변환되어 처리된다. 물론 이밖에도 자동으로 DATETIME으로 자동 변환이 가능한 형태가 있다. 하지만 그렇지 않은 형태는 MySQL 서버가 문자열에 사용된 날짜 타입의 포맷을 알 수 없으므로 명시적으로 날짜 타입으로 변환해 주어야 한다. 이때 STR_TO_DATE() 함수를 이용해 문자열을 DATETIME 타입으로 변환할 수 있다. 날짜의 각 부분을 명시하는 지정자는 DATE_FORMAT() 함수에서 사용했던 지정자와 동일하게 사용하면 된다.
SELECT STR_TO_DATE('2011-04-30', '%Y-%m-%d') AS current_dt;
= 2011-04-30

SELECT STR_TO_DATE('2011-04-30 15:13:25', '%Y-%m-%d %H:%i:%s') AS current_dttm;
= 2011-04-30

날짜와 시간의 연산(DATE_ADD, DATE_SUB)

  • 특정 날짜에서 년도나 월일 또는 시간 등을 더하거나 뺄 때는 DATE_ADD() 함수나 DATE_SUB() 함수를 사용한다. 사실 DATE_ADD() 함수로 더하거나 빼는 처리를 모두 할 수 있기 때문에 DATE_SUB()는 크게 필요하지 않다. DATE_ADD() 함수 모두 두 개의 인자를 필요로 하는데, 첫 번째 인자는 연산을 수행할 날짜이며, 두 번째 인자는 더하거나 빼고자 하는 월의 수나 일자의 수 등을 입력하면 된다. 두 번째 인자는 INTERVAL n [YEAR, MONTH, DAY, HOUR, MINUTE, SECOND…] 형태로 입력해야 한다. 여기서 n은 더하거나 빼고자 하는 차이 값이며, 그 뒤에 명시되는 단위에 따라 하루를 더할 것인지 한달을 더할 것인지 결정한다.
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY) AS tomorrow;
= 2011-05-01 15:21:33

SELECT DATE_ADD(NOW(), INTERVAL -1 DAY) AS yesterday;
= 2011-04-29 15:21:42
키워드 의미
YEAR 년도(중간의 숫자 값은 더하거나 뺄 년 수를 의미)
MONTH 월(중간의 숫자 값은 더하거나 뺄 개월 수를 의미)
DAY 일(중간의 숫자 값은 더하거나 뺄 일자 수를 의미)
HOUR 시(중간의 숫자 값은 더하거나 뺄 시를 의미)
MINUTE 분(중간의 숫자 값은 더하거나 뺄 분 수를 의미)
SECOND 초(중간의 숫자 값은 더하거나 뺄 초 수를 의미)
QUARTER 분기(중간의 숫자 값은 더하거나 뺄 분기의 수를 의미)
WEEK 주(중간의 숫자 값은 더하거나 뺄 주 수를 의미)

타임 스탬프 연산(UNIX_TIMESTAMP, FROM_UNIXTIME)

  • UNIX_TIMESTAMP() 함수는 ‘1970-01-01 00:00:00’로부터 경과된 초의 수를 반환하는 함수다. 다른 운영체제나 프로그래밍 언어에서도 같은 방식으로 타임스탬프를 산출하는 경우에는 상호 호환해서 사용할 수 있다. UNIX_TIMESTAMP() 함수는 인자가 없으면 현재 날짜와 시간의 타임스탬프 값을, 인자로 특정 날짜를 전달하면 그 날짜와 시간의 타임스탬프를 반호나한다. FROM_UNIXtIME() 함수는 UNIX_TIMESTAMP() 함수와 반대로, 인자로 전달한 타임스탬프 값은 DATETIME 타입으로 변환하는 함수다.

  • SELECT UNIX_TIMESTAMP('2005-03-27 03:00:00');
= 1111860000
  
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('2005-03-27 03:00:00'));
= 2005-03-27 03:00:00

유닉스 시간(UNIX TimeStamp)는 POSIX 시간이나 Epoch 시간이라고 부르기도 하고, 1970년 1월 1일 00:00:00 협정 세계시(UTC)부터 경과시간을 초로 환산하여 정수로 나타낸것이다.

문자열 처리 (RPAD, LPAD /TRTIM, LTRIM, TRIM)

  • RPAD()와 LPAD() 함수는 문자열의 좌측 또는 우측에 문자를 덧붙여서 지정된 길이의 문자열로 만드는 함수다. RPAD() 함수나 LPAD() 함수는 3개의 인자가 필요하다. 첫 번째 인자는 패딩 처리를 할 문자열이며, 두 번째 인자는 몇 바이트까지 패딩할 것인지, 세 번째 인자는 어떤 문자를 패딩할 것인지를 의미한다.
SELECT RPAD('Cloee', 10, '_');
 = Cloee_____
 
 SELECT LPAD('123', 10, '0');
 = 0000000123
  • RTRIM() 함수와 LTRIM() 함수는 문자열의 우측 또는 좌측에 연속된 공백 문자(Space, NewLine, Tab 문자)를 제거하는 함수다. TRIM() 함수는 LTRIM()과 RTRIM()을 동시에 수행하는 함수다.
SELECT RTRIM('Cloee		') AS name;
= Cloee

SELECT LTRIM('		Cloee') AS name;
= Cloee

SELECT TRIM('	Cloee	') AS name;
= Cloee

문자열 결합(CONCAT)

  • 여러 개의 문자열을 연결해서 하나의 문자열로 반환하는 함수로, 인자의 개수는 제한이 없다. 숫자 값을 인자로 전달하면 문자열 타입으로 자동 변환한 후 연결한다.
  • 만약 의도된 결과가 아닌 경우에는 명시적으로 CAST 함수를 이용해 타입을 문자열로 변환하는 편이 안전하다.
SELECT CONCAT('Georgi', 'Christian') AS name;
= GeorgiChristian

SELECT CONCAT('Georgi', 'Christian', 2) AS name;
= GeorgiChristian2

SELECT CONCAT('Georgi', 'Christian', CAST(2 AS CHAR)) AS name;
= GeorgiChristian2

비슷한 함수로 CONCAT_WS()라는 함수가 있는데. 각 문자열을 연결할 때 구분자를 넣어준다는 점을 제외하면 CONCAT()와 같다.

SELECT CONCAT_WS(',', 'Georgi', 'Christian') AS name;
= Georgi,Christian

GROUP BY 문자열 결합(GROUP_CONCAT)

  • COUNT()나 MAX(), MIN(), AVG() 등과 같은 그룹함수(Aggregate, 여러 레코드의 값을 병합해서 하나의 값으 만들어내는 함수) 중 하나다. 주로 GROUP BY와 함께 사용하며, GROUP BY가 없는 SQL에서 사용하면 단 하나의 결과 값만 만들어낸다. GROUP_CONCAT() 함수는 값들을 먼저 정렬한 후 연결하거나 각 값의 구분자 설정도 가능하며, 여러 값 중에서 중복을 제거하고 연결하는 것도 가능하므로 상당히 유용하게 사용되는 함수다.
SELECT GROUP_CONCAT(dept_no) FROM departments;
= d001,d002,d003,d004,d005,d006,d007,d008,d009

SELECT GROUP_CONCAT(dept_no SEPARATOR '|') FROM departments;
= d001|d002|d003|d004|d005|d006|d007|d008|d009

SELECT GROUP_CONCAT(dept_no ORDER BY dept_name DESC) FROM departments;
= d007,d008,d006,d004,d001,d003,d002,d005,d009

SELECT GROUP_CONCAT(DISTINCH dept_no ORDER BY dept_name DESC) FROM departments;
= d007, d008, d006, d004, d001, d003, d002, d005, d009
  • GROUP_CONCAT() 함수가 사용하는 메모리 버퍼의 크기는 group_concat_max_len 시스템 변수로 조정할 수 있다. 기본적으로 설정된 버퍼의 크기가 1KB밖에 안되기 때문에 GROUP_CONCAT() 함수를 자주 사용한다면 버퍼의 크기를 적절히 늘려서 설정해 두는것이 좋다.

값의 비교와 대체(CASE WHEN .. THEN .. END)

  • CASE WHEN은 프로그래밍 언어에서 제공하는 SWITCH 구문과 같은 역할을 한다. CASE로 시작하고 반드시 END로 끝나야 하며, WHEN .. THEN .. 은 필요한 만큼 반복해서 사용할 수 있다.

  • 방법 2가지

    1. 단순히 코드 값을 실제 값으로 변환한다거나, 특정 일자를 기준으로 이전인지 이후인지 비교해 설명을 붙이는 용도로 CASE WHEN이 사용됐다.
    • SELECT emp_no,first_name,
	CASE gender WHEN 'M' THEN 'Man'
				WHEN 'F' THEN 'Woman'
				ELSE 'Unknown'
	END AS gender
FROM employees LIMIT 10;
    1. 동등 연산자(=)로 비교할 수 있을 때 비교하고자 하는 칼럼이나 표현식을 CASE와 WHEN 키워드 사이에 두고, 비교 기준값을 WHEN 뒤에 입력해서 사용하는 방식이다. 이 방식은 일반적인 프로그래밍 언어의 SWITCH 문법과 같은 방식으로 사용한다.
    • SELECT emp_no, first_name,
	CASE WHEN hire_date<'1995-01-01' THEN 'Old'
		ELSE 'New'
	END AS gender
FROM employees LIMIT 10;

타입의 변환(CAST, CONVERT)

  • 프리페어 스테이트먼트를 제외하면 SQL은 텍스트(문자열) 기반으로 작동하기 때문에 SQL에 포함된 모든 입력 값은 문자열처럼 취급된다. 이럴 때 만약 명시적으로 타입의 변환이 필요하다면 CAST() 함수를 이용하면 된다. CONVERT() 함수도 CAST()와 거의 비슷하며, 단지 함수의 인자 사용 규칙만 조금 다르다.
  • CAST() 함수를 통해 변환할 수 있는 데이터 타입은 DATE, TIME< DATETIME, BINARY, CHAR, DECIMAL, SIGNED INTEGER, UNSIGNED INTEGER다.
  • ex) CAST(expr AS type) AS converted_integer
  • ex) CONVERT(expr, type) -> expr = 값지정, type = 변환하고 싶은 데이터 타입

이진값과 16진수(Hex String) 문자열 변환(HEX, UNHEX)

  • HEX() 함수는 이진값을 사람이 읽을 수 있는(Human readable) 형태의 16진수의 문자열(Hex-string)로 변환하는 함수이고, UNHEX() 함수는 16진수의 문자열(Hex-string)을 읽어서 이진값으로 변환하는 함수다.
  • 여기서 이진 값은 사람이 읽을 수 있는 형태의 문자열이나 숫자가 아니라 바이너리 값이다.

암호화 및 해시 함수(MD5, SHA)

  • MD5와 SHA 모두 비대칭형 암호화 알고리즘인데, 인자로 전달한 문자열을 각각 지정된 비트 수의 해시 값을 만들어내는 함수다. SHA() 함수는 SHA-1 암호화 알고리즘을 사용하며, 결과로 160비트(20바이트) 해시 값을 반환한다. MD5는 메시지 다이제스트 알고리즘을 사용해 128비트(16바이트) 해시 값을 반환한다.

  • 두 함수 모두 사용자의 비밀번호와 같은 암호화가 필요한 정보를 인코딩하는 데 사용되며, 특히 MD5() 함수는 말 그대로 입력된 문자열(Message)의 길이를 줄이는(Digest) 용도로도 사용된다. 두 함수의 출력 값은 16진수로 표시되기 때문에 저장하려면 저장 공간이 각각 20바이트와 16바이트의 두 배씩 필요하다. 그래서 암호화된 값을 저장해 두기 위해 MD5() 함수는 CHAR(32), SHA() 함수는 CHAR(40)의 타입을 필요로 한다.

  • 저장 공간을 원래의 16바이트와 20바이트로 줄이고 싶다면 CHAR나 VARCHAR 타입이 아닌 BINARY 형태의 타입에 저장하면 된다. 이때는 칼럼의 타입을 BINARY(16) 또는 BINARY(20)으로 정의하고, MD5() 함수나 SHA() 함수의 결과를 UNHEX() 함수를 이용해 이진값으로 변환해서 저장하면 된다. BINARY 타입에 저장된 이진값을 사람이 읽을 수 있는 16진수 문자열로 다시 되돌릴 때는 HEX() 함수를 사용하면 된다.

  • CREATE TABLE tab_binary (col_mod5 BINARY(16), col_sha BINARY(20));
INSERT INTO tab_binary VALUES (UNHEX(MD5('abc')), UNHEX(SHA('abc')));
SELECT HEX(col_md5), HEX(col_sha) FROM tab_binary;

처리 대기(SLEEP)

  • SLEEP() 함수는 프로그래밍 언어나 셸 스크립트 언어에서 제공하는 “sleep” 기능을 수행한다. DBMS는 빠르게 쿼리를 처리하는 것을 항상 최선으로 생각하는데, 쿼리 실행 도중 멈춰서 대기하는 기능이 왜 필요할까, 라고 생각할 수도 있다. 하지만 SQL의 개발이나 디버깅 용도로 잠깐 대기한다거나, 일부러 쿼리의 실행을 오랜 시간 동안 유지한다거나 할 때 상당히 유용한 함수다.

  • 이 함수는 대기할 시간을 초 단위로 인자를 받으며, 특별히 어떠한 처리를 하거나 반환값을 넘겨주지 않는다. 단지 지정한 시간만큼 대기할 뿐이다.

  • SELECT SLEEP(10)
FROM employees
WHERE emp_no BETWEEN 10001 AND 10010;
    • SLEEP() 함수는 레코드의 건수만큼 SLEEP( ) 함수를 호출하기 때문에 위의 쿼리는 employees 테이블에서 조회되는 레코드 건수만큼 SLEEP() 함수를 호출한다. 결국 100초 동안 쿼리를 실행하는 셈이다.

벤치마크(BENCHMARK)

  • SLEEP() 함수와 같이 디버깅이나 간단한 함수의 성능 테스트용으로 아주 유용한 함수다.
  • 2개의 인자를 필요로 한다. 첫 번째 인자는 반복해서 수행할 횟수이며, 두 번째 인자로는 반복해서 실행할 표현식을 입력하면 된다. 두 번째 인자의 표현식은 반드시 스칼라 값을 반환하는 표현식이어야 한다. 즉 SELECT 쿼리를 BECHMARK() 함수에 사용하는 것도 가능하지만, 반드시 스칼라 값(하나의 칼럼을 가진 하나의 레코드)만 반환하는 SELECT 쿼리만 사용할 수 있다.

IP 주소 변환(INET_ATON, INET_NTOA)

  • IP 주소는 4바이트의 부호없는 정수로 이루어져 있다.
  • 대부분의 DBMS에서는 IP 정보를 VARCHAR(15) 타입에 ‘.’으로 구분해서 저장하고 있다. 이렇게 문자열로 저장된 IP 주소는 저장 공간을 훨씬 많이 필요로 한다. 게다가 일반적으로 IP 주소를 저장할 때 “127.0.0.1”형태로 저장하므로 IP 주소 자체를 A, B, C 클래스로 구분하는 것도 불가능하다.
  • INET_ATON() 함수는 문자열로 구성된 IP주소를 정수형으로 변환하는 함수다
  • INET_NTOA() 함수는 정수형의 IP 주소를 사람이 읽을 수 있는 형태의 ‘.’으로 구분된 문자열로 변환하는 함수다.

MySQL 전용 암호화(PASSWORD, OLD_PASSWORD)

  • PASSWORD() 함수는 MySQL DBMS 유저의 비밀번호를 관리하기 위한 함수이다. 일반 서비스의 고객 정보를 암호화하기 위한 용도로는 적합하지 않다.
  • 서비스용 고객정보를 암호화해야 할 때는 MD5() 함수나 SHA() 함수를 이용하는 것이 좋다.

VALUES()

  • 이 함수는 INSERT INTO … ON DUPLICATE KEY UPDATE … 형태의 SQL 문장에서만 사용할 수 있다. MySQL의 REPLACE와 비슷한 기능의 쿼리 문장인데, 프라이머리 키나 유니크 키가 중복되는 경우에는 UPDATE를 수행하고, 그렇지 않으면 INSERT를 실행하는 문장이다.

  • INSERT INTO tab_statistics (member_id, visit_count)
SELECT member_id, COUNT(*) AS cnt
	FROM tab_accesslog
	GROUP BY member_id
ON DUPLICATE KEY
	UPDATE visit_count = visit_count + COUNT(*);

COUNT()

  • 결과 레코드의 건수를 반환하는 함수다.
  • COUNT() 함수는 칼럼이나 표현식을 인자로 받으며, 특별한 형태로 “*“를 사용할 수도 있다.

  • “*“은 SELECT 절에 사용될 때처럼 모든 칼럼을 가져오라는 의미가 아니라 그냥 레코드 자체를 의미하는 것이다.

  • 주의사항 : 칼럼명이나 표현식이 인자로 사용되면 그 칼럼이나 표현식의 결과가 NULL이 아닌 레코드 건수만 반환한다. 예를 들어 “COUNT(column1)” 이라고 SELECT 쿼리에 사용하면 column1이 NULL이 아닌 레코드의 건수를 가져온다. 그래서 NULL이 될 수 있는 칼럼을 COUNT() 함수에 사용할 때는 의도대로 쿼리가 작동하는지 확인하는 것이 좋다.