Tini's real life story

회사에 새로운 솔루션이 도입이 되면서, DBMS의 파티셔닝 테이블을 필요로 했다.
그리고 MySQL은 5.1부터 파티셔닝 테이블을 지원한다.
그러나...
CentOS 5에서는 MySQL 5.0 버전을 지원하고 있는 관계로 그냥 사용할 수가 없다.

물론 간단히 소스컴파일 해서 사용하면 되지 않겠냐고 말하는 분들 있겠지만..
과감히 이런 멘트를 날려준다.
'아직도 컴파일이 멋지다고 생각하는가? 가장 좋을거라 생각하는가? 착각이다'
최적의 환경에서, 최적의 빌드옵션으로 빌드 할 자신 없다면 관둬라
불필요한 컴파일로 성능저하나 안일어나면 다행이다.

서론이 길었다.
그럼 어떻게 해야될까? 나의 repo tree를 뒤적뒤적 찾아보니...
오! thanks for Remi!!
MySQL 5.1을 지원해주고 있는 repository를 찾았다. (덤으로 PHP 5.2까지..)
바로 설치에 들어가보자 고고싱..

1. Remi의 yum repository 세팅
/etc/yum.repos.d 안에 remi.repo 파일과 epel.repo 파일이 생성이 된다.
이제 사용할 준비가 완료 되었다. (너무 쉬운가?!)

2. MySQL 설치
remi의 repo를 enable 시켜줌과 동시에 모든 repo를 disable 시켜주는 이유는
centos base repo의 mysql(5.0)과 remi의 mysql(5.1)이 중복되지 않게 하기 위해서이다.

그리고 두번째 라인의 mysqlclient15 패키지는.. PHP에서 사용하는 라이브러리가 들어있다.
완전히 독립적인 DB 서버의 경우는 설치하지 않아도 되지만,
APM이 하나의 서버에서 운영되는 경우에는 반드시 설치해야 의존성 에러를 피할 수 있다.

자 설치가 다 끝났다. 이제 잘 쓰자.
참 쉽죠잉~?!

• DATE_FORMAT(date,format)

format 스트링에 따라서date 값을 포맷한다.

아래에 나와 있는 지정자 (specifier)들은 format 스트링안에서 사용할 수 있다. ‘%’ 문자는 지정자 문자를 포맷하기 전에 필요한 것이다.

Specifier	Description
%a	Abbreviated weekday name (Sun..Sat)
%b	Abbreviated month name (Jan..Dec)
%c	Month, numeric (0..12)
%D	Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
%d	Day of the month, numeric (00..31)
%e	Day of the month, numeric (0..31)
%f	Microseconds (000000..999999)
%H	Hour (00..23)
%h	Hour (01..12)
%I	Hour (01..12)
%i	Minutes, numeric (00..59)
%j	Day of year (001..366)
%k	Hour (0..23)
%l	Hour (1..12)
%M	Month name (January..December)
%m	Month, numeric (00..12)
%p	AM or PM
%r	Time, 12-hour (hh:mm:ss followed by AM or PM)
%S	Seconds (00..59)
%s	Seconds (00..59)
%T	Time, 24-hour (hh:mm:ss)
%U	Week (00..53), where Sunday is the first day of the week
%u	Week (00..53), where Monday is the first day of the week
%V	Week (01..53), where Sunday is the first day of the week; used with %X
%v	Week (01..53), where Monday is the first day of the week; used with %x
%W	Weekday name (Sunday..Saturday)
%w	Day of the week (0=Sunday..6=Saturday)
%X	Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x	Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y	Year, numeric, four digits
%y	Year, numeric (two digits)
%%	A literal ‘%’ character
%x	x, for any ‘x’ not listed above

달 및 날짜 지정자를 위한 범위는 0에서부터 시작을 한다..

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');

        -> 'Saturday October 1997'

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');

        -> '22:23:00'

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',

                          '%D %y %a %d %m %b %j');

        -> '4th 97 Sat 04 10 Oct 277'

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',

                          '%H %k %I %r %T %S %w');

        -> '22 22 10 10:23:00 PM 22:23:00 00 6'

mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');

        -> '1998 52'

mysql> SELECT DATE_FORMAT('2006-06-00', '%d');

        -> '00'

• DAY(date)

DAY() is a synonym for DAYOFMONTH().

• DAYNAME(date)

date에 대한 주간 요일 이름을 리턴한다.

mysql> SELECT DAYNAME('1998-02-05');

        -> 'Thursday'

• DAYOFMONTH(date)

0에서 31 사이의 달별 날짜를 리턴한다.

mysql> SELECT DAYOFMONTH('1998-02-03');

        -> 3

• DAYOFWEEK(date)

date에 대한 주간 요일 인덱스를 리턴한다 (1 = Sunday, 2 = Monday, …, 7 = Saturday). 이러한 인덱스들은 ODBC 표준을 따른다.

mysql> SELECT DAYOFWEEK('1998-02-03');

        -> 3

• DAYOFYEAR(date)

1에서 366 사이의 date에 해당하는 일수를 리턴한다.

mysql> SELECT DAYOFYEAR('1998-02-03');

        -> 34

• EXTRACT(unit FROM date)

EXTRACT() 함수는 DATE_ADD() 또는 DATE_SUB()과 같은 종류의 유닛 지정자를 사용하지만, 날짜 산술식을 실행하는 것이 아닌 날짜에서 부분을 추출하는 기능을 수행한다..

mysql> SELECT EXTRACT(YEAR FROM '1999-07-02');

       -> 1999

mysql> SELECT EXTRACT(YEAR_MONTH FROM '1999-07-02 01:02:03');

       -> 199907

mysql> SELECT EXTRACT(DAY_MINUTE FROM '1999-07-02 01:02:03');

       -> 20102

mysql> SELECT EXTRACT(MICROSECOND

    ->                FROM '2003-01-02 10:30:00.00123');

        -> 123

• FROM_DAYS(N)

주어진 날짜 숫자 N에 대해서 DATE 값을 리턴한다.

mysql> SELECT FROM_DAYS(729669);

        -> '1997-10-07'

• FROM_UNIXTIME(unix_timestamp), FROM_UNIXTIME(unix_timestamp,format)

unix_timestamp 인수에 대한 표현식을 이 함수가 사용된 문장에 따라서 'YYYY-MM-DD HH:MM:SS' 또는 YYYYMMDDHHMMSS 포맷으로 리턴한다. unix_timestamp는 UNIX_TIMESTAMP() 함수가 만들어 내는 것과 같은 내부 타임 스탬프 값이다.

만일 format 을 주게 되면, 그 결과는 format 스트링에 따라서 포맷이 되는데, 이것은 DATE_FORMAT() 함수에 대한 엔트리에 목록화 되는 방식과 같은 방식을 사용한다.

mysql> SELECT FROM_UNIXTIME(875996580);

        -> '1997-10-04 22:23:00'

mysql> SELECT FROM_UNIXTIME(875996580) + 0;

        -> 19971004222300

mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),

    ->                      '%Y %D %M %h:%i:%s %x');

        -> '2003 6th August 06:22:58 2003'

• GET_FORMAT(DATE|TIME|DATETIME, 'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL')

포맷 스트링을 리턴한다. 이 함수는 DATE_FORMAT() 및 STR_TO_DATE() 함수를 결합하는데 있어서 매우 유용한 것이다.

첫 번째 인수와 두 번째 인수에 대해서 몇 가지 포맷 스트링 값이 사용 가능하다. 이 값들은 ISO 9075를 참조한다.

Function Call	Result
GET_FORMAT(DATE,'USA')	'%m.%d.%Y'
GET_FORMAT(DATE,'JIS')	'%Y-%m-%d'
GET_FORMAT(DATE,'ISO')	'%Y-%m-%d'
GET_FORMAT(DATE,'EUR')	'%d.%m.%Y'
GET_FORMAT(DATE,'INTERNAL')	'%Y%m%d'
GET_FORMAT(DATETIME,'USA')	'%Y-%m-%d-%H.%i.%s'
GET_FORMAT(DATETIME,'JIS')	'%Y-%m-%d %H:%i:%s'
GET_FORMAT(DATETIME,'ISO')	'%Y-%m-%d %H:%i:%s'
GET_FORMAT(DATETIME,'EUR')	'%Y-%m-%d-%H.%i.%s'
GET_FORMAT(DATETIME,'INTERNAL')	'%Y%m%d%H%i%s'
GET_FORMAT(TIME,'USA')	'%h:%i:%s %p'
GET_FORMAT(TIME,'JIS')	'%H:%i:%s'
GET_FORMAT(TIME,'ISO')	'%H:%i:%s'
GET_FORMAT(TIME,'EUR')	'%H.%i.%S'
GET_FORMAT(TIME,'INTERNAL')	'%H%i%s'

TIMESTAMP는 또한 GET_FORMAT() 함수의 첫 번째 인수로 사용이 가능한데, 이와 같은 경우, 함수는 DATETIME 함수와 같은 값을 리턴한다.

mysql> SELECT DATE_FORMAT('2003-10-03',GET_FORMAT(DATE,'EUR'));

        -> '03.10.2003'

mysql> SELECT STR_TO_DATE('10.31.2003',GET_FORMAT(DATE,'USA'));

        -> '2003-10-31'

• HOUR(time)

time에 대해서 시간 (hour)를 리턴한다. 일별 시간에 대해서는 0에서 23 사이의 값을 가진다. 하지만, TIME 값의 실제 범위는 이것보다 훨씬 크기 때문에, HOUR는 23 보다 큰 값을 리턴할 수 있다.

mysql> SELECT HOUR('10:05:03');

        -> 10

mysql> SELECT HOUR('272:59:59');

        -> 272

• LAST_DAY(date)

날짜 또는 데이트타임 값을 가져와서 그 달의 가장 마지막 날짜에 해당하는 값을 리턴한다. 만일 인수가 올바르지 않으면 NULL을 리턴한다.

mysql> SELECT LAST_DAY('2003-02-05');

        -> '2003-02-28'

mysql> SELECT LAST_DAY('2004-02-05');

        -> '2004-02-29'

mysql> SELECT LAST_DAY('2004-01-01 01:01:01');

        -> '2004-01-31'

mysql> SELECT LAST_DAY('2003-03-32');

        -> NULL

• LOCALTIME, LOCALTIME()

LOCALTIME 및 LOCALTIME()는 NOW()과 동일한 것이다.

• LOCALTIMESTAMP, LOCALTIMESTAMP()

LOCALTIMESTAMP 및 LOCALTIMESTAMP()는 NOW()과 동일하다.

• MAKEDATE(year,dayofyear)

주어진 연도 및 연도별 날짜 값을 가지고서, 해당하는 날짜를 리턴한다. dayofyear 인수는 0 보다 커야 하며, 그렇지 않을 경우에는 NULL을 리턴한다.

mysql> SELECT MAKEDATE(2001,31), MAKEDATE(2001,32);

        -> '2001-01-31', '2001-02-01'

mysql> SELECT MAKEDATE(2001,365), MAKEDATE(2004,365);

        -> '2001-12-31', '2004-12-30'

mysql> SELECT MAKEDATE(2001,0);

        -> NULL

• MAKETIME(hour,minute,second)

hour, minute, 그리고 second 인수를 가지고 계산된 시간 값을 리턴한다.

mysql> SELECT MAKETIME(12,15,30);

        -> '12:15:30'

• MICROSECOND(expr)

시간 또는 데이트타임 수식 expr 에서 마이크로 세컨드(microsecond) 값을 리턴하는데, 그 범위는0에서 999999 사이가 된다.

mysql> SELECT MICROSECOND('12:00:00.123456');

        -> 123456

mysql> SELECT MICROSECOND('1997-12-31 23:59:59.000010');

        -> 10

• MINUTE(time)

time에서 분 (minute)에 해당하는 값을 리턴하는데, 그 범위는 0에서 59 사이가 된다.

mysql> SELECT MINUTE('98-02-03 10:05:03');

        -> 5

• MONTH(date)

date에서 월에 해당하는 값을 리턴하는데, 그 범위는 0에서 12 사이가 된다.

mysql> SELECT MONTH('1998-02-03');

        -> 2

• MONTHNAME(date)

date에 해당하는 월을 전체 이름으로 표시한다.

mysql> SELECT MONTHNAME('1998-02-05');

        -> 'February'

• NOW()

이 함수가 사용되는 문장에 따라서 그 형태를 'YYYY-MM-DD HH:MM:SS' 또는 YYYYMMDDHHMMSS로 해서 현재의 날짜 및 시간을 리턴한다.

mysql> SELECT NOW();

        -> '1997-12-15 23:50:26'

mysql> SELECT NOW() + 0;

        -> 19971215235026

NOW()는 이 명령문이 실행을 시작하는 시점을 나타내는 시간을 상수 값으로 리턴한다. (스토어드 루틴 또는 트리거 안에서는, NOW()는 루틴 또는 트리거링 명령문이 실행되는 시점 값을 리턴한다.) 이 함수는 SYSDATE()와는 차이점을 가지는데, 후자의 경우는 5.0.13 이후에는 함수가 실행된 정확한 시간을 리턴한다.

mysql> SELECT NOW(), SLEEP(2), NOW();

+---------------------+----------+---------------------+

| NOW()               | SLEEP(2) | NOW()               |

+---------------------+----------+---------------------+

| 2006-04-12 13:47:36 |        0 | 2006-04-12 13:47:36 |

+---------------------+----------+---------------------+

 

mysql> SELECT SYSDATE(), SLEEP(2), SYSDATE();

+---------------------+----------+---------------------+

| SYSDATE()           | SLEEP(2) | SYSDATE()           |

+---------------------+----------+---------------------+

| 2006-04-12 13:47:44 |        0 | 2006-04-12 13:47:46 |

+---------------------+----------+---------------------+

이 두 함수간의 차이점에 대해서는 SYSDATE() 함수 설명을 참고로 하기 바란다.

• PERIOD_ADD(P,N)

N 월 (month)을 기간 P 에 추가한다 (YYMM 또는 YYYYMM 포맷). 리턴되는 값은 YYYYMM 포맷을 가진다. 기간 인수 P 는 날짜 값이 아니라는 점을 유의한다.

mysql> SELECT PERIOD_ADD(9801,2);

        -> 199803

• PERIOD_DIFF(P1,P2)

P1 와P2 사이의 월별 간격을 리턴하는데, 여기에서 P1 과 P2 는 YYMM 또는 YYYYMM 형태가 되어야 한다. 인수 P1 과 P2 는 날짜 값이 아니라는 점을 유의한다.

mysql> SELECT PERIOD_DIFF(9802,199703);

        -> 11

• QUARTER(date)

Date에 해당하는 분기를 리턴하는데, 범위는1 에서 4 사이가 된다

mysql> SELECT QUARTER('98-04-01');

        -> 2

• SECOND(time)

time에서 초 부분을 리턴하는데, 범위는 0 에서 59 사이가 된다.

mysql> SELECT SECOND('10:05:03');

        -> 3

• SEC_TO_TIME(seconds)

seconds 인수를 리턴하는데, 이 함수가 사용되는 문장에 따라서 'HH:MM:SS' 또는 HHMMSS 포맷의 값으로 시간, 분, 초로 변환을 시킨다.

mysql> SELECT SEC_TO_TIME(2378);

        -> '00:39:38'

mysql> SELECT SEC_TO_TIME(2378) + 0;

        -> 3938

• STR_TO_DATE(str,format)

이 함수는 DATE_FORMAT() 함수와는 정 반대 값을 리턴한다. 이 함수는 스트링 str 을 가져와서 스트링 format형태로 포맷을 한다. STR_TO_DATE()는, 포맷 스트링이 날짜 및 시간 부분을 모두 가지고 있는 경우에는 DATETIME 값을, 또는 스트링이 날짜 또는 시간 부분만을 가지고 있는 경우에는 DATE 또는 TIME 값을 리턴한다.

Str에 포함되어 있는 날짜, 시간, 또는 데이트타임 값은 ormat에 의해 지정되는 포맷으로 주어져야 한다. format에서 사용될 수 있는 지정자의 경우는 DATE_FORMAT() 함수 설명을 참조하기 바란다. 만일 str이 올바르지 않은 날짜, 시간, 또는 데이트타임 값을 가지고 있다면, STR_TO_DATE()는 NULL을 리턴한다. MySQL 5.0.3 이후에서는 경고문도 함께 발생한다.

날짜 값의 범위 검사는 Section 11.3.1, “DATETIME, DATE, 그리고 TIMESTAMP 타입”에서 설명을 하고 있다. 예를 들면, 이것은, “제로 (zero)” 날짜 또는 날짜 부분에 0을 가지고 있는 날짜는 SQL 모드가 이러한 값을 허용하지 않도록 설정되지 않는 한 사용 가능하다는 것을 의미한다.

mysql> SELECT STR_TO_DATE('00/00/0000', '%m/%d/%Y');

        -> '0000-00-00'

mysql> SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');

        -> '2004-04-31'

• SUBDATE(date,INTERVAL expr unit), SUBDATE(expr,days)

두 번째 인수에 대해서 INTERVAL 폼을 사용해서 호출을 한다면, SUBDATE()는 DATE_SUB()과 동일한 값을 리턴하게 된다.

mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);

        -> '1997-12-02'

mysql> SELECT SUBDATE('1998-01-02', INTERVAL 31 DAY);

        -> '1997-12-02'

두 번째 형태는 days에 대해서 정수 값을 사용할 수 있도록 하고 있다. 이와 같은 경우, 날짜 또는 데이트타입 수식 expr에서 빼기가 되어야 하는 날짜 수로 해석이 된다.

mysql> SELECT SUBDATE('1998-01-02 12:00:00', 31);

        -> '1997-12-02 12:00:00'

Note: "%X%V" 형태를 사용해서 년-주간 스트링을 날짜로 변환 시킬수는 없는데, 그 이유는 연도와 주간의 결합은 해당 주간이 두 개의 달에 걸쳐 있을 경우에는 연도 및 달을 구분할 수 없기 때문이다. 연도-주간 값을 날짜로 변환하기 위해서는, 주간 요일 (weekday)을 함께 지정해 주어야 한다:

mysql> SELECT STR_TO_DATE('200442 Monday', '%X%V %W');

        -> '2004-10-18'

• SUBTIME(expr1,expr2)

SUBTIME() 함수는 expr1 – expr2 수식의 결과 값을 리턴하느데, 그 포맷은 expr1을 따른다.  expr1 은 시간 또는 데이트 타임 수식을 사용할 수 있으며, expr2은 시간 수식이 된다.

mysql> SELECT SUBTIME('1997-12-31 23:59:59.999999','1 1:1:1.000002');

        -> '1997-12-30 22:58:58.999997'

mysql> SELECT SUBTIME('01:00:00.999999', '02:00:00.999998');

        -> '-00:59:59.999999'

• SYSDATE()

함수가 사용된 문장에 따라서 'YYYY-MM-DD HH:MM:SS' 또는 YYYYMMDDHHMMSS 포맷으로 현재 날짜 및 시간을 리턴한다.

MySQL 5.0.13 이후부터는, SYSDATE()은 이것이 실행된 시간을 리턴한다. 이 함수는 NOW()과 차이를 가지는데, 후자의 경우는 명령문이 실행을 시작한 상수 시간을 리턴한다.

mysql> SELECT NOW(), SLEEP(2), NOW();

+---------------------+----------+---------------------+

| NOW()               | SLEEP(2) | NOW()               |

+---------------------+----------+---------------------+

| 2006-04-12 13:47:36 |        0 | 2006-04-12 13:47:36 |

+---------------------+----------+---------------------+

 

mysql> SELECT SYSDATE(), SLEEP(2), SYSDATE();

+---------------------+----------+---------------------+

| SYSDATE()           | SLEEP(2) | SYSDATE()           |

+---------------------+----------+---------------------+

| 2006-04-12 13:47:44 |        0 | 2006-04-12 13:47:46 |

+---------------------+----------+---------------------+

또한, SET TIMESTAMP 명령문은 NOW()가 리턴하는 값에는 영향을 주지만, SYSDATE()가 리턴하는 값에는 영향을 주지 않는다. 이것이 의미하는 것은 바이너리 로그에서 설정된 타임 스탬프가 SYSDATE() 함수 호출에는 영향을 주지 않는다는 것을 나타내는 것이다.

SYSDATE()는 동일한 명령문 안에서도 서로 다른 값을 리턴하고, SET TIMESTAMP에 의해서도 영향을 받지 않기 때문에, 이 함수는 논-디터미니스틱 (non-deterministic)이며 따라서 리플리케이션에서는 안전하지가 못하게 된다. 만일 이것이 문제가 된다면, 서버를 --sysdate-is-now 옵션과 함께 구동 시킴으로서 SYSDATE()가 NOW()의 별칭으로 동작하도록 할 수는 있다.

• TIME(expr)

시간 또는 데이트 타임 수식 expr 에서 시간 부분을 추출하고 그 값을 스트링으로 리턴한다.

mysql> SELECT TIME('2003-12-31 01:02:03');

        -> '01:02:03'

mysql> SELECT TIME('2003-12-31 01:02:03.000123');

        -> '01:02:03.000123'

• TIMEDIFF(expr1,expr2)

TIMEDIFF()은 expr1 – expr2 수식의 결과를 시간 값으로 리턴한다. expr1 및 expr2 는 시간 또는 날짜-시간 수식이 될 수 있지만, 양쪽 모두 동일한 타입이어야 한다.

mysql> SELECT TIMEDIFF('2000:01:01 00:00:00',

    ->                 '2000:01:01 00:00:00.000001');

        -> '-00:00:00.000001'

mysql> SELECT TIMEDIFF('1997-12-31 23:59:59.000001',

    ->                 '1997-12-30 01:01:01.000002');

        -> '46:58:57.999999'

• TIMESTAMP(expr), TIMESTAMP(expr1,expr2)

단일 인수를 사용하게 되면, 이 함수는 날짜 또는 데이트 타임 수식 expr 을 데이트 타임 값으로 리턴한다. 두 개의 인수를 사용하게 되면, 이 함수는 시간 수식 expr2 를 날짜 또는 데이트 타임 수식 expr1 에 추가를 하게 되고 그 결과를 데이트타임 값 형태로 리턴한다.

mysql> SELECT TIMESTAMP('2003-12-31');

        -> '2003-12-31 00:00:00'

mysql> SELECT TIMESTAMP('2003-12-31 12:00:00','12:00:00');

        -> '2004-01-01 00:00:00'

• TIMESTAMPADD(unit,interval,datetime_expr)

정수 수식 interval 를 날짜 또는 데이트타임 수식 datetime_expr에 추가를 한다. interval 에 대한 유닛은 unit 인수에 의해 주어지는데, 이 인수는 다음의 값 중에 하나가 되어야 한다: FRAC_SECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.

unit 값은 위에 나와 있는 키워드중의 하나를 사용하거나 또는 지정될 수 있다. SQL_TSI_의 접두어를 사용해서 지정할 수가 있다. 예를 들면, DAY 및 SQL_TSI_DAY 는 모두 유효하다.

mysql> SELECT TIMESTAMPADD(MINUTE,1,'2003-01-02');

        -> '2003-01-02 00:01:00'

mysql> SELECT TIMESTAMPADD(WEEK,1,'2003-01-02');

        -> '2003-01-09'

TIMESTAMPADD()는 5.0.0 이후에 사용 가능해졌다.

• TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

날짜 또는 데이트 타임 수식 datetime_expr1 및 datetime_expr2간의 정수 차이를 리턴한다. 그 결과에 대한 유닛은 unit 인수에 의해 주어진다. unit 에 대한 유효 값은 TIMESTAMPADD() 함수에서 설명된 리스트 값과 같다.

mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');

        -> 3

mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');

        -> -1

TIMESTAMPDIFF()는 5.0.0 이후에 사용 가능해졌다.

• TIME_FORMAT(time,format)

이 함수는 DATE_FORMAT() 함수와 비슷하게 사용되지만, format 스트링은 시간, 분, 그리고 초에만 해당하는 지정자를 가질 수도 있다. 다른 지정자들을 사용하면 NULL 값 또는 0이 나오게 된다.

만일 time 값이 23 보다 큰 시간 부분을 가진다면, %H 및 %k 시간 포맷 지정자는 0에서 23 보다 큰 값을 만들게 된다. 다른 시간 포맷 지정자는 시간 값 모듈로 12를 만든다.

mysql> SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l');

        -> '100 100 04 04 4'

• TIME_TO_SEC(time)

time 인수를 초로 변환해서 리턴한다.

mysql> SELECT TIME_TO_SEC('22:23:00');

        -> 80580

mysql> SELECT TIME_TO_SEC('00:39:38');

        -> 2378

• TO_DAYS(date)

주어진 날짜 date에 대해서, 연도 0에서부터 계산된 날짜 숫자를 리턴한다.

mysql> SELECT TO_DAYS(950501);

        -> 728779

mysql> SELECT TO_DAYS('1997-10-07');

        -> 729669

• UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)

만일 아무런 인수를 지정하지 않고 호출을 한다면, 부호를 사용하지 않은 유닉스 타임 스탬프를 리턴한다 ('1970-01-01 00:00:00' UTC 이후의 초). 만일 UNIX_TIMESTAMP()가 date 인수를 사용해서 호출되었다면, 이 함수는 '1970-01-01 00:00:00' UTC 이후의 초 형태로 인수 값을 리턴한다. Date는 DATE 스트링, DATETIME 스트링, TIMESTAMP, 또는 YYMMDD 또는 YYYYMMDD에 있는 숫자가 될 수도 있다. 서버는 date를 현재의 타임 존에 있는 값으로 해석을 해서 UTC의 내부 값으로 변환을 시킨다. 클라이언트는 자신의 타임 존을 Section 5.11.8, “MySQL 서버 타임 존 지원”에서 설명한 방식으로 설정을 한다.

mysql> SELECT UNIX_TIMESTAMP();

        -> 882226357

mysql> SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');

        -> 875996580

UNIX_TIMESTAMP를 TIMESTAMP 컬럼에서 사용하는 경우, 이 함수는 내부 타임 스탬프 값을 직접 리턴하고, 의미적인 (implicit) “스트링에서 유닉스 타임 스탬프로 변환”을 실행하지 않는다. 만일 날짜 범위를 벗어난 값을 UNIX_TIMESTAMP()에 주게 되면, 함수는 0을 리턴한다.

• UTC_DATE, UTC_DATE()

이 함수가 사용된 문장에 따라서, 현재의 UTC 날짜를 'YYYY-MM-DD' 또는 YYYYMMDD 포맷으로 리턴한다.

mysql> SELECT UTC_DATE(), UTC_DATE() + 0;

        -> '2003-08-14', 20030814

• UTC_TIME, UTC_TIME()

이 함수가 사용된 문장에 따라서, 현재의 UTC 시간을 'HH:MM:SS' 또는 HHMMSS 포맷으로 리턴한다.

mysql> SELECT UTC_TIME(), UTC_TIME() + 0;

        -> '18:07:53', 180753

• UTC_TIMESTAMP, UTC_TIMESTAMP()

이 함수가 사용된 문장에 따라서, 현재의 UTC 날짜를 'YYYY-MM-DD HH:MM:SS' 또는 YYYYMMDDHHMMSS 포맷으로 리턴한다.

mysql> SELECT UTC_TIMESTAMP(), UTC_TIMESTAMP() + 0;

        -> '2003-08-14 18:08:04', 20030814180804

• WEEK(date[,mode])

이 함수는 date에 해당하는 주간 숫자를 리턴한다. WEEK() 함수에 두 개의 인수를 사용하면 해당 주가 일요일 또는 월요일에 시작을 하는지를 지정할 수 있으며 또한 리턴되는 값이 0에서 53 사이 또는 1에서 53 사이에 있는지를 지정할 수가 있게 된다. 만일 mode 인수를 생략한다면, default_week_format 시스템 변수가 사용된다. Section 5.2.2, “서버 시스템 변수”를 참조할 것.

아래의 테이블은 mode 인수가 어떻게 동작을 하는지를 보여주는 것이다.

	First day
Mode	of week	Range	Week 1 is the first week …
0	Sunday	0-53	with a Sunday in this year
1	Monday	0-53	with more than 3 days this year
2	Sunday	1-53	with a Sunday in this year
3	Monday	1-53	with more than 3 days this year
4	Sunday	0-53	with more than 3 days this year
5	Monday	0-53	with a Monday in this year
6	Sunday	1-53	with more than 3 days this year
7	Monday	1-53	with a Monday in this year

mysql> SELECT WEEK('1998-02-20');

        -> 7

mysql> SELECT WEEK('1998-02-20',0);

        -> 7

mysql> SELECT WEEK('1998-02-20',1);

        -> 8

mysql> SELECT WEEK('1998-12-31',1);

        -> 53

만일 어떤 날짜가 바로 전년도의 마지막 주에 있다면, 여러분이 옵션 인수MySQL은 0을 리턴한다:

mysql> SELECT YEAR('2000-01-01'), WEEK('2000-01-01',0);

        -> 2000, 0

• WEEKDAY(date)

Date에 해당하는 주간 요일 인덱스를 리턴한다 (0 = Monday, 1 = Tuesday, … 6 = Sunday).

mysql> SELECT WEEKDAY('1998-02-03 22:23:00');

        -> 1

mysql> SELECT WEEKDAY('1997-11-05');

        -> 2

• WEEKOFYEAR(date)

1에서 53 사이의 달력 주간 숫자를 리턴한다.

mysql> SELECT WEEKOFYEAR('1998-02-20');

        -> 8

• YEAR(date)

1000에서 9999 사이의 date에 해당하는 연도를 리턴하거나, 또는 “제로” 날짜일 경우에는 0을 리턴한다.

mysql> SELECT YEAR('98-02-03');

        -> 1998

• YEARWEEK(date), YEARWEEK(date,start)

해당되는 연도 및 주를 리턴한다. start 인수는 WEEK() 함수에서 사용되는 것과 동일하게 동작을 한다. 결과에 나오는 연도는 날짜 인수에 표시되어 있는 연도와 다르게 나올 수도 있다.

mysql> SELECT YEARWEEK('1987-01-01');

        -> 198653

Understanding the Query Cache

흰콧수염

webmaster@10mbps.com

Last translated on Sep 08. 2004.

5.11 The MySQL Query Cache

MySQL Version 4.0.1 이상부터, qeury cache가 도입되었다. qeury cache가 이용될 경우 클라이언트로부터 입력된 SELECT 쿼리를 캐쉬에 저장한다. 이후 정확히 같은 쿼리가 입력되었을 때, 해당 쿼리를 MySQL 서버에서 실행하지 않고 캐쉬로부터 가져와 클라이언트에게 전달한다.

query cache는 테이블의 내용이 자주 변하지 않고 정확히 같은 쿼리를 반복적으로 사용할 경우 큰 효과를 보여준다.

주의 :

1. query cache는 오래된 data를 클라이언트에게 전달하지 않는다. 테이블의 내용이 변경될 경우, 캐쉬에 저장된 관련 쿼리의 내용을 모두 초기화시킨다.

2. 하나의 서버에 여러 mysqld 데몬을 운영하는 경우에는 동작하지 않는다.

5.11.1 How the Query Cache Operates

모든 query는 클라이언트에게 전달하기 전에 서로 비교하는 과정을 거친다. 따라서 query cache는 아래의 두 query문은 서로 다르게 인식한다.

        SELECT * FROM tbl_name;

        Select * from tbl_name;

query는 동일하게 인식되기 위해서는 정확히 일치해야 한다. 또한 같은 query문이라도 몇몇 상황에서는 다르게 받아들여진다. 다른 database, 다른 protocol 버전, 다른 문자 셑을 가질 경우 각각 서로 다른 query문으로 간주되어 따로 따로 cache 된다.

query cache로부터 결과값이 클라이언트에게 전달되면, 서버는 Qcache_hits 값을 자동 증가시킨다.

특정 테이블의 내용이 변경되면, 해당 테이블로부터 쿼리 cache된 모든 내용은 삭제된다. 테이블의 내용은  INSERT, UPDATE, DELETE, TRUNCATE, ALTER TABLE, DROP TABLE, 또는 DROP DATABASE와 같은 쿼리로 인하여 변경된다.

query cache는 SELECT SQL_CALC_FOUND_ROWS ...  와  SELECT  FOUND_ROWS() 타입의 쿼리에도 적용된다. FOUND_ROWS()는 선행 query가 cache에 저장되어 있다하더라도 record의 개수 또한 cache에 저장되어 있으므로 정확한 값을 반환한다.

아래의 함수를 포함한 query문은 cache를 이용하지 않는다 :

BENCHMARK()	CONNECTION_ID()	CURDATE()
CURRENT_DATE()	CURRENT_TIME()	CURRENT_TIMESTAMP()
CURTIME()	DATABASE()	ENCRYPT() with one parameter
FOUND_ROWS()	GET_LOCK()	LAST_INSERT_ID()
LOAD_FILE()	MASTER_POS_WAIT()	NOW()
RAND()	RELEASE_LOCK()	SYSDATE()
UNIX_TIMESTAMP() with no parameters	USER()

또 query가 아래와 같은 조건일 경우에도 cached 되지 않는다 :

       

        ❚ 사용자 정의 함수(UDF)가 포함된 경우.

        ❚ 사용자 변수(user variables)가 포함된 경우.

        ❚ mysql의 system database에 있는 테이블을 조회하는 경우.

        ❚ 아래의 형태를 가진 경우 :

       

SELECT ... IN SHARE MODE

SELECT ... INTO OUTFILE ...

SELECT ... INTO DUMPFILE ...

SELECT * FROM ... WHERE autoincrement_col IS NULL

       

        ❚ TEMPORARY tables을 사용하는 경우.

        ❚ table를 사용하지 않는 경우.

        ❚ user가 관련 table에 column 레벨의 권한만을 가진 경우.

 ❚ query가 cache로부터 값을 가져오기 전에 MySQL은 user가 관련 database와 table에 SELECT 권한을 가지고 있는 지 체크한다. 권한이 없을 경우 cached 된 값을 반환하지 않는다.

5.11.2 Query Cache SELECT Options

SELECT 구문에 정의할 수 있는 두 개의 cache 관련 옵션이 있다.

        SQL_CACHE

            query_cache_type이 ON 이거나 DEMAND 인 경우, 결과 값을 cache로부터 반환시킨다.

        SQL_NO_CACHE

            쿼리 결과를 cache 시키지 않는다.

예 :

        SELECT SQL_CACHE id, name FROM customer;

        SELECT SQL_NO_CACHE id, name FROM customer;

5.11.3 Query Cache Configuration

have_query_cache DB 환경 변수는 query cache를 이용할 수 있는 지 여부를 확인할 수 있다.

        mysql> SHOW VARIABLES LIKE 'have_query_cache';

         

Variable_name	Value
have_query_cache	YES

query cache를 이용하기 위한 옵션으로 몇 가지가 있다. 이 옵션들은 /etc/my.cnf 에 정의하거나 mysqld 구동 스크립트에 정의 할 수 있다. query cache 관련 변수는 query_cache_ 로 시작한다.

query cache의 크기를 지정하기 위해서는 query_cache_size를 이용한다. 0으로 설정하면 query cache는 더 이상 사용되지 않는다. 기본값은 0이다.

query cache가 enable 된 경우, query_cache_type 변수는 SELECT 방법에 영향을 준다.

        0 또는 OFF 인 경우 cache시키거나 혹은 cached 된 결과를 가져올 수 없다.

        1 또는 ON 인 경우 SELECT SQL_NO_CACHE 인 구문을 제외하고 cache 기능을 이용한다.

        2 또는 DEMAND 인 경우 SELECT SQL_CACHE 구문만 cache 기능을 이용한다.

클라이언트는 query cache 기능을 이용하고 싶지 않을 경우 아래와 같이 할 수 있다:

        mysql> SET SESSION query_cache_type = OFF;

하나의 query 결과를 caching 하기 위한 최대 값은 query_cache_limit 변수로 수정할 수 있으며, 기본값은 1MB를 가진다.

5.11.4 Query Cache Status and Maintenance

query cache 기능이 존재하는 알고 싶을 경우 아래의 구문을 사용한다.

        mysql> SHOW VARIABLES LIKE 'have_query_cache';

        +------------------+-------+

        | Variable_name        | Value   |

        +------------------+-------+

        | have_query_cache    | YES    |

        +------------------+-------+

좀 더 효율적으로 메모리를 사용하기 위해 FLUSH QUERY CACHE 구문으로 조각모음을 할 수 있다. 그렇다고하여 cache로부터 query된 내용을 삭제하지는 않는다.

query cache로부터 query 결과를 삭제하기 위해서는 RESET  QUERY  CACHE 또는 FLUSH TABLES 구문을 사용한다.

query cache 성능을 모니터링하기 위해서는 SHOW STATUS를 이용한다.

        mysql> SHOW STATUS LIKE 'Qcache%';

        +-------------------------+--------+

        | Variable_name                 | Value    |

        +-------------------------+--------+

        | Qcache_free_blocks            | 36       |

        | Qcache_free_memory          | 138488   |

        | Qcache_hits                   | 79570   |

        | Qcache_inserts                | 27087   |

        | Qcache_lowmem_prunes       | 3114     |

        | Qcache_not_cached           | 22989   |

        | Qcache_queries_in_cache      | 415      |

        | Qcache_total_blocks           | 912      |

        +-------------------------+--------+

SELECT query 전체 구문은 :

          Com_select

        + Qcache_hits

        + queries with errors found by parser

Com_select 값은 :

          Qcache_inserts

        + Qcache_not_cached

        + queries with errors found during columns/rights check

와 같다.

query cache는 가변 길이의 block을 이용한다. 따라서 Qcache_total_blocks 와 Qcache_free_blocks는 분산된 memory 조각을 가르킨다. FLUSH  QUERY  CACHE을 하고 난 뒤에는 하나의 free block만 남게된다.

모든 캐쉬에 저장된 query는 최소 2 block를 가진다. query에 이용된 모든 테이블은 1 block을 갖는다. 그러나 같은 테이블을 2 또는 그 이상의 query에서 조회한다면 1 block을 할당해야한다.