본 게시물의 내용과 이미지는 도서 Real MySQL의 내용을 재구성하여 작성되었습니다. 저자, 출판사에 의해 저작권 문제 발생시 게시물이 비공개 될 수 있음을 알립니다.
문자열 결합(CONCAT)
여러 개의 문자열을 연결해 하나의 문자열로 반환하는 함수로, 인자의 개수는 제한이 없다. 숫자는 문자열 타입으로 자동 변환된다. CAST는 함수를 사용해 명시적으로 타입을 문자열로 변환하는 방법도 있다.
mysql> SELECT CONCAT('Georgi', 'Christian') AS name; // GeorgiChristian
mysql> SELECT CONCAT('Georgi', 'Christian', 2) As name; // GeorgiChristian2
mysql> SELECT CONCAT('Georgi', 'Christian', CAST(2 AS CHAR)) As name; // GeorgiChristian2
비슷함 함수로 CONCAT_WS() 라는 함수가 있는데, 각 문자열을 연결할 때 구분자를 넣어준다는 점을 제외하면 CONCAT() 함수와 같다. CONACT_WS() 함수는 첫 번째 인자를 구분자로 사용할 문자로 인식하고, 두 번째 인자부터는 연결할 문자로 인식한다.
mysql> SELECT CONCAT_WS(',' 'Georgi', 'Christian' ) As name; // Georgi,Christian
GROUP BY 문자열 결합(GROUP_CONCAT)
GROUP_CONCAT은 GROUP BY와 함께 사용하며, GROUP BY가 없는 SQL에서 사용하면 단 하나의 결과 값만 만들어낸다. GROUP_CONCAT() 함수는 값들을 먼저 정렬한 후 연결하거나 각 값의 구분자 설정도 가능하며, 여러 값 중에서 중복을 제거하고 연결하는 것도 가능하므로 상당히 유용하게 사용된다.
mysql> SELECT GROUP_CONCAT(dept_no) FROM departments;
// d001, d002, d003, d004, d005
- 가장 기본적인 형태의 GROUP_CONCAT() 함수의 사용법이다. 쿼리를 실행하면 테이블의 모든
mysql> SELECT GROUP_CONCAT(dept_no SEPARATOR '|') FROM departments;
// d001|d002|d003|d004|d005
- dept_no 값들을 연결할 때 구분자를 "," 에서 "|"로 변경한 것이다.
mysql> SELECT GROUP_CONCAT(dept_no ORDER BY dept_name DESC) FROM departments;
// d003, d001, d005, d004, d002
- dept_name 의 칼럼을 역순으로 정렬해서 dept_no 칼럼의 값을 연결해서 가져오는 쿼리다.
mysql> SELECT GROUP_CONCAT(DISTINCT dept_no ORDER BY dept_name DESC) FROM departments;
- dept_no 값의 중복값을 제거하고 유니크한 dept_no 값만 가져온다.
GROUP_CONCAT() 함수는 지정한 칼럼의 값들을 연결하기 위해 제한적인 메모리 버퍼 공간을 사용한다. 시스템 변수에 지정된 크기를 초과하면 에러가 발생하므로 지정된 버퍼 크기를 초과하지 않도록 주의해야 한다.
GORUP_CONCAT() 함수가 사용하는 메모리 버퍼의 크기는 group_concat_max_len 시스템 변수로 조정할 수 있다. 기본 설정이 1KB 밖에 안 되기 때문에 적절이 늘려서 설정해 두는 것도 좋다.
값의 비교와 대체(CASE WHEN .. THEN END)
CASE WHEN은 프로그래밍 언어에서 제공하는 SWITCH 구문과 같은 역할을 한다. CASE로 시작해 반드시 END로 끝나야 하며, WHEN .. THEN ..은 필요한 만큼 반복해서 사용할 수 있다.
SELECT emp_no, first_name,
CASE gender WHEN 'M' THEN 'Man'
WHEN 'F' THEN 'Woman'
ELSE 'Unknown'
END AS gender
FROM employees LIMIT 10;
이 방법은 동등 연산자(=)로 비교할 수 있을 때 비교하고자 하는 칼럼이나 표현식을 CASE 와 WHEN 키워드 사이에 두고, 비교 기준값을 WHEN 뒤에 입력해서 사용하는 방식이다.
SELECT emp_no, first_name,
CASE WHEN hire_date<'1995-01-01' THEN 'Old'
ELSE 'New'
END AS gender
FROM employees LIMIT 10;
이 방식은 단순한 비교 대상 값의 동등 비교가 아니라 크가 또는 작다 비교와 같이 표현식으로 비교할 때 사용하는 방식이다. CASE와 WHEN 사이에는 아무것도 입력하지 않고 WHEN 절에 불리언 값을 반환할 수 있는 표현식을 적어 주면 된다.
타입의 변환(CAST, CONVERT)
프리페어 스테이트먼트를 제외하면 SQL은 텍스트(문자열) 기반으로 작동하기 때문에 SQL 에 포함된 모든 입력 값은 문자열처럼 취급된다. 명시적 타입 변환이 필요하면 CAST() 함수를 이용하면 된다. CONVERT() 함수도 CAST()와 거의 비슷하며, 단지 함수의 인자 사용 규칙만 다르다.
CAST() 함수를 통해 변환할 수 있는 데이터 타입은 DATE, TIME, DATETIME, BINARY, CHAR, DECIMAL, SIGNED, INTEGER, UNSIGNED INTEGER다.
SELECT CAST('12345' AS SIGNED INTEGER) AS converted_integer;
SELECT CAST('2000-01-01' AS DATE) AS converted_date;
CAST는 타입 변환할 값이나 표현식과 변환할 데이터 타입을 명시해 하나의 인자로 호출한다. 첫번째 분과 두번째 부분 구분을 위해 AS를 사용한다.
일반적으로 문자열, 숫자, 날짜 변환은 명시적으로 해주지 않아도 자동변환되지만 SIGNED이나 UNSIGNED 같은 부호 있는 정수 또는 부호 없는 정수값의 변환은 명시적으로 해주어야 한다.
mysql> SELECT CAST(1-2 AS UNSIGNED);
// 18446744073709551615
mysql> SELECT 1-2;
// -1
CONVERT() 함수는 CAST() 와 같이 타입 변환용도와 문자열 문자집합을 변환하는 용도로 사용할 수 있다.
mysql> SELECT CONVERT('ABC' USING 'utf8');
// ABC
이진값과 16진수(Hex String) 문자열 변환(HEX, UNHEX)
HEX() 함수는 이진값을 사람이 읽을 수 있는 형태의 16진수 문자열로 변환한다. UNHEX()는 16진수 문자열을 읽어 이진값(BINARY)로 변환한다. 여기서 이진값은 사람이 읽을 수 있는 형태의 문자열이나 숫자가 아니라 바이너리 값이다.
mysql> SELECT HEX('CAT');
// 636174
암호화 및 해시 함수(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)으로 정의하고 변환한 값을 저장하면 된다. BINARY 타입 이진값을 16진수 문자열로 되돌릴 때는 HEX() 함수를 사용하면 된다.
mysql> INSERT INTO tab_binary VALUES (UNHEX(MD5('abc')), UNHEX(SHA('abc')));
MD5() 함수나 SHA() 함수는 모두 비대칭 암호화알고리즘으로 결과 값은 중복 가능성이 매우 낮기 때문에 길이가 긴 데이터를 크기를 줄여 인덱싱(해시)하는 용도로 사용된다. 예를 들어 URL과 같은 값은 1KB를 넘을 때도 있다. 이러한 데이터를 검색하려면 인덱스가 필요하지만, 긴 칼럼에 대해 전체 값으로 인덱스 생성은 불가능하고 공간 낭비도 커진다. URL의 값을 MD5() 함수로 단축하면 16바이트로 저장할수 있고, 16바이트로 인덱스를 생성하기 때문에 상대적으로 효율적이다.
처리 대기 (SLEEP)
SLEEP() 함수는 프로그래밍 언어나 셸 스크립트 언어에서 제공하는 "sleep" 기능을 수행한다. DBMS 는 빠르게 쿼리를 처리하는 것을 최선으로 생각하는데 SQL의 개발이나 디버깅 용도로 잠깐 대기한다거나, 일부러 쿼리의 실행을 오랜 시간 동안 유지한다거나 할 때 상당히 유용한 함수다.
이 함수는 대기할 시간을 초 단위로 인자를 받으며 반환값을 넘겨주지 않는다. 단지 지정한 시간만큼 대기할 뿐이다.
SELECT SLEEP(10)
FROM employeses
WHERE emp_no BETWEEN 10001 AND 10010;
SLEEP() 함수는 레코드의 건수만큼 SLEEP() 함수를 호출하기 때문에 위의 쿼리는 조회되는 레코드 건수만큼 SLEEP() 함수를 호출하여 결국 100초 동안 쿼리를 실행하는 셈이 된다.
벤치마크(BENCHMARK)
BENCHMARK() 함수는 SLEEP() 함수와 같이 디버깅이나 간단한 함수 성능 테스트 용으로 유용하다. 2개의 인자를 필요로 하며, 첫 번째 인자는 반복 수행할 횟수이며, 두번 째인자는 실행할 표현식을 입력한다. 두 번째 인자의 표현식은 반드시 스칼라 값을 반환하는 표현식이어야 한다.
BENCHMARK() 함수는 반환값이 아닌 지정한 횟수만큼 반복 실행하는데 얼마나 시간이 소요됐는지가 중요할 뿐이다.
mysql> SELECT BENCHMARK(100000, MD5('abcdefghijk'));
// 0
1 row in set (0.20 sec)
mysql> SELECT BENCHMARK(100,
(SELECT COUNT(*) FROM employees));
// 0
1 row in set (0.08 sec)
SQL 문장이나 표현식의 성능을 BENCHMARK() 함수로 확인할 때 주의해야 할 사항은 SELECT BENCHMARK(10, expr)과 SELECT expr을 10번 직접 실행하는 것은 차이가 있다는 것이다.
"SELECT expr"을 10번 실행하는 경우에는 매번 쿼리의 파싱이나 최적화, 그리고 테이블 락이나 네트워크 비용이 소모된다. 하지만 "SELECT BENCHMARK(10 expr)" 로 실행하는 경우에는 벤치마크 횟수에 관계없이 단 1번의 네트워크, 쿼리 파싱 및 최적화 비용이 소요된다.
BENCHMARK() 함수로 얻은 쿼리나 함수의 성능은 그 자체로는 별로 의미가 없으며, 두 개의 동일 기능을 상대적으로 비교 분석하는 용도로 사용할 것을 권장한다.
IP 주소 변환(INET_ATON, INET_NTOA)
IP주소는 4바이트 부호 없는 정수이다. 대부분의 DBMS 에서는 IP정보를 VARCHAR(15) 타입에 '.'로 구분해서 저장하고 있다. 문자열로 저장된 IP주소는 저장 공간이 많이 필요하다. MySQL 에서는 INET_ATON() 함수와 INET_NTOA() 함수를 이용해 IP 주소를 문자열이 아닌 부호 없는 정수 타입(UNSIGNED INTEGER)에 저장할 수 있게 제공한다. INET_ATON() 함수는 문자열로 구성된 IP 주소를 정수형으로 변환하는 함수이며, INET_NTOA() 함수는 정수형의 IP 주소를 사람이 읽을 수 있는 형태의 '.'로 구분된 문자열로 변환한다.
mysql> CREATE TABLE tab_accesslog ( access_dttm DATETIME, ip_addr INTEGER UNSIGNED);
mysql> INSERT INTO tab_accesslog VALUES ( NOW(), INET_ATON('127.0.0.130'));
mysql> SELECT access_dttmm, INET_NTOA(ip_Addr) AS ip_addr
FROM tab_accesslog
WHERE ip_addr BETWEEN INET_ATON('127.0.0.128') AND INET_ATON('127.0.0.255');
MySQL 전용 암호화(PASSWORD, OLD_PASSWORD)
PASSWORD()와 OLD_PASSWORD() 함수는 일반 사용자가 사용해서는 안 될 함수이기에 언급한다. 이 함수는 MySQL DBMS 사용자의 비밀번호를 암호화하는 기능의 함수다. 문제는 이 함수의 알고리즘이 바뀌었고 앞으로 변경될 가능성이 있다는 것이다.
MySQL 4.0 이하 버전에서 사용되던 PASSWORD() 함수는 MySQL 4.1 이상의 버전에서는 OLD_PASSWORD()로 이름이 바뀌었다. 그리고 MySQL 4.1 이상 버전의 PASSWORD() 함수는 전혀 다른 알고리즘으로 대체되었다.
버전간 호환성으로 인해 함수 사용을 자제하기를 바란다. 또한 이 함수들은 비대칭 암호화 알고리즘을 사용하기 때문에 암호화된 데이터에서 암호화 되기 전의 평문을 다시 조립해 낼 수 없다. 결론적으로 PASSWORD() 함수는 MySQL DBMS 유저의 비밀번호를 관리하기 위한 함수이지, 일반 서비스의 고객 정보 암호화를 위한 용도로 적합하지 않다. 고객 정보를 암호화 하는 경우 MD5()나 SHA() 함수를 이용하는 것이 좋다.
VALUES()
이 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(*);
위 쿼리는 tab_accesslog 테이블의 모든 레코드를 member_id 칼럼으로 그룹핑해서 전체 건수를 tab_statistics 테이블에 INSERT 한다. 만약 프라이머리 키가 존재하면 그룹핑된 건수를 visit_count에 더한다. 그룹핑된 건수를 업데이트 하기 위해 COUNT(*) 값을 알아야 하는데, UPDATE 절에서는 SELECT.. GROUP BY 단위 쿼리의 일부가 아니므로 알아낼 방법이 없다. 이럴 때 VALUES() 함수를 사용하면 해당 칼럼에 INSERT 하려고 했던 값을 참조하는 것이 가능하다.
SELECT member_id, COUNT(*) AS cnt
FROM tab_accesslog
GROUP BY member_id
ON DUPLICATE KEY
UPDATE visit_count = visit_count + VALUES(COUNT(*));
VALUES() 함수의 인자값으로는 INSERT 문장에서 값을 저장하려고 했던 칼럼의 이름을 입력하면 된다.
COUNT()
COUNT() 함수는 결과 레코드의 건수를 반환한다. 칼럼이나 표현식을 인자로 받으며, "*"를 사용할 수도 있다. 여기서 "*"는 SELECT 절에 사용될 때처럼 모든 칼럼을 가져오라는 의미가 아니라 그냥 레코드 자체를 의미하는 것이다.
COUNT(*) 쿼리에서 가장 많이 하는 실수는 ORDER BY 구문이나 LEFT JOIN 같은 레코드 건수를 가져오는 것과는 전혀 무관한 작업을 포함하는 것이다. 대부분 COUNT(*) 쿼리는 페이징 처리를 위해 사용할 때가 많은데, 많은 개발자가 SELECT 쿼리를 그대로 복사해 칼럼이 명시된 부분만 삭제하고 그 부분을 COUNT(*) 함수로 대체해서 사용하곤 한다. 단순히 COUNT(*)만 실행하는 쿼리에도 ORDER BY가 포함되거나 LEFT JOIN이 사용된 채로 실행될 때가 많다.
COUNT(*) 쿼리에서 ORDER BY 절은 어떤 경우에도 필요치 않다. LEFT JOIN 또한 레코드 건수의 변화가 없거나 아우터 테이블에서 별도의 체크를 하지 않아도 되는 경우에는 모두 제거하는 것이 성능상 좋다.
인덱스를 제대로 사용하도록 튜닝하지 못한 COUNT(*)는 일반적으로 칼럼의 값을 SELECT하는 쿼리보다 몇 배 또는 몇십 배 더 느리게 실행될 수 있다. COUNT(*) 쿼리도 많은 부하를 일으키기 때문에 주의 깊게 작성해야 한다.
