Notice
Recent Posts
Recent Comments
Link
«   2024/05   »
일 월 화 수 목 금 토
1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31
Tags more
Archives
Today
Total
관리 메뉴

nalaolla

Mysql 핵심 요약 정리 본문

MYSQL

Mysql 핵심 요약 정리

날아올라↗↗ 2015. 12. 20. 14:48
728x90

[데이터베이스 용어 (파일, DB 모델링-논리, 관계형 DB-물리)]

 

파일 시스템데이터베이스 모델링관계형 데이터베이스
파일(file)엔터티(Entity)테이블(table)
레코드(record)튜플(Tuple)행(row)
키(key)유일값(identifier)기본키(Primary key), unique
필드(field)어트리뷰트(attribute)컬럼(column)

 

 

[learning ORM (Many-to-many, Many-to-one, One-to-one) with django model]

http://programmingexamples.wikidot.com/django-model-layer

 

Many-to-many 는 양 모델간 서로 다 여러개를 가질 수 있음. 피자 위의 토핑. 원래는 중간에 테이블이 하나 더 들어가서 각 테이블의 기본키를 가져와 매핑하지만 장고 모델은 이를 간단히 표현.

양 쪽 어디든 models.ManyToManyField(연관모델이름) 을 필드에 넣어주면 됨. 하지만 보통 자식에게 넣음.

 

Many-to-one이나 One-to-Many나 똑같은 말임. Many가 One을 바라보는 것. 여러 자식이 부모를 바라봄.

자식에게 models.ForeignKey(부모연관모델이름) 을 필드에 넣어주면 됨. 외래키는 자식에게 할당되는 것.

 

One-to-one 도 표현 가능. 이것도 실제 DB에서는 일종의 외래키를 가지는 관계이지만 제약을 두어 한개에 대해 한개만 매핑되게 되는 것.

 

식별관계 (Identifying Relationship) 와 비식별관계 (Non Identifying Relationship) 라는 것도 있음.

식별관계는 부모의 기본키, 복합키가 자식의 기본키, 복합키가 되는 경우. 부모없이 자식은 존재할 수 없음.

비식별 관계는 부모의 복합키, 기본키가 자식의 Foreign key 나 일반속성이 되며 Primary key 는 아닌 상태로 자신만의 고유 식별자를 가지면서 어떤 부모와 연관성이 있는지를 나타내는 관계. 비식별 관계도 두가지로 나뉜다.

비식별 종속관계 (Non-Identifying Mandatory Relationship) : 자식의 정보가 반드시 있어야 할 경우. 아래에서 Optional 표기가 없는 경우. 배달업체의 경우 주소는 반드시 필요함. 신용카드회사의 경우 카드정보는 반드시 필요함. 즉, 식별관계 처럼 부모의 키가 자식을 구분하는 필수요소는 아니지만 부모가 몇몇 배송지 주소와 같은 정보는 필수적으로 가져야할 필요가 있을 때를 의미. 물건 배달시 배송지 주소가 없으면 안되므로… 물론 식별관계로 만들 수도 있지만 최소한의 원칙 (Row의 유일성을 구분할 수 있는 Key 들의 수는 가능한한 작게 해야하는…) 에 따라 가능한 한 비식별 관계로 하는 것이 좋습니다. 배송지 주소라는 테이블에 그만을 위한 id 값이 존재한다면 더욱더 그러하구요.

비식별 비종속관계 (Non-Identifying Non-Mandatory Relationship) : 자식의 정보가 없어도 되는 경우. 아래에서 Optional 의 경우. 배달업체에서 전달 메시지와 같은 경우는 없을 수도 있음. 신용카드회사의 경우 선호도 정보 같은 것은 없을 수도 있음. 물론 전달메시지 정보 하나만을 위해 다른 테이블을 생성하지는 않을 것임.

 

[shops] id, category_id, sub_category_id, name, domain, intro, avatar, region_id, address, tel, fax, website, creation_time, update_time

[shop_managers] id, shop_id, name, email, mobile, position, creation_time

[shop_sns_infos] shop_id, sns1, sns2, sns3, sns4, sns5, sns6, sns7, sns8, sns9, sns10, update_time

shops 와 shop_managers 의 경우 shop 은 shop_manager 를 여러명 가질 수 있으므로 1:N 관계입니다. 그리고 shop_id 가 shop_managers 의 FK 가 되어 비식별관계입니다. 또한 단 한 명의 shop_manager 를 가지지 않을 수도 있습니다. 물론 현실에서는 있어야 하겠지만 여기서는 단지 온라인 상의 정보라서 매니저 정보를 입력하지 않으면 없을 수 있습니다. 이럴 경우  비식별 비종속 관계입니다. 따로 id 값이 있음으로서 shop_manager 가 하나의 독립적인 주체가 될 수 있고 다른 테이블과 다시 관계를 맺을 수 있습니다.

반면 shop_sns_infos 의 경우 PK 가 shop_id 입니다. shops 의 식별자가 shop_sns_infos 의 PK 가 되어 식별관계입니다. 1:1 관계이면서 완전 종속되는 식별관계입니다. shops 에 shop 이 존재하지 않으면 shop_sns_info 또한 존재할 수 없는 구조입니다.

cardinality

실선 : 식별관계, 부모 테이블의 PK 가 자식 테이블의 PK/FK 가 되는 경우.

점선 : 비식별관계, 부모 테이블의 PK 가 자식 테이블의 일반 속성이 되는 경우.

O : Optional (선택) – 0개가 될 수 있다.

I : Mandatory – 한 개가 될 수 있다.

^ : Many – 여러개가 될 수 있다.

- : Exactly – 정해진 개수만 될 수 있다.

 

1:1, 1:N, M:N 등의 관계 의미와 식별, 비식별 관계의 의미는 다른 것입니다. 각각 다른 영역의 의미이며 그것이 ERD 에서는 모두 표현됩니다.

 

 

 

이러한 부분들은 DB 종류에 따른 차이와 여러가지 문제로 인해 ORM 과 같이 프로그램의 모델단에서 매핑하여 사용됨. 자바도 ibatis나 hibernate 등 많지만 너무 복잡해…

 

bi-directional, uni-directional의 개념이란 것도 있음.

 

[Mysql 자료형]

* (M) : 정수형자리 (D) : 소수점자리

 

자료형표 현길 이
Numeric
(숫자형)		TINYINT[(M)]signed : -128~127,
unsigned: 0~255, 2^8
SMALLINT[(M)]signed: -32768~32767,
unsigned: 0~65535, 2^16
MEDIUMINT[(M)]signed: -8388608~8388607,
unsigned: 0~16777215, 2^24
INT[(M)]signed: -2147483648~2147483647,
unsigned: 0~4294967295, 2^32
BIGINT[(M)]signed: -9223372036854775808~9223372036854775807,
unsigned: 18446744073709551616, 2^64
FLOAT[(precision)]단정도 부동소수점 실수
signed: : -3.402823466E+38 ~ 1.175494351E-38,
unsigned: 1.175494351E-38 ~ 3.402823466E+38)
DOUBLE[(M,D)]배정도 부동소수점 실수
signed: -1.7976931348623157E+308 ~ -2.2250738585072014E-308
unsigned: 2.2250738585072014E-308 ~ 1.7976931348623157E+308
REAL[(M,D)]위에 DOUBLE과 같다고 보면 됨. DECIMAL 부동 소수점 실수 CHAR 형태로 동작
날짜형DATE’1000-01-01′ ~ ’9999-12-31′을 지원합니다. Format: ’YYYY-MM-DD’
DATETIME’1000-01-01 00:00:00′~’9999-12-31 23:59:59′ 까지 지원.
Format: ’YYYY-MM-DD HH:MM:SS’
TIMESTAMP[(M)]1970-01-01 ~ 2037년 임의 시간
TIME-838:59:59 ~ 838:59:59
YEAR [(2|4)]901 ~ 2155, 0000
문 자CHAR(M) [BINARY]1~255개까지의 고정길이형 문자열.
고정폭이므로 왼쪽부터 저장하고 남은공간은 곤백으로 채움
VARCHAR(M)[BINARY]1~255개까지의 가변길이형 문자열.
가변적 길이이므로 문자의 길이만 정하고 저장할 공간을 크기만큼 따로 보한다. 그러므로 사용자와는 무관하지만 저장 공간을 CHAR보다는 1BYTE가 더 많게된다.
TINYBLOB, TINYTEXT최대길이 255(2^8) – 1) 의 BLOB, TEXT 형
BLOB, TEXT최대길이 65535(2^16 -1)의 BLOB, TEXT형
LONGBLOB, LONGTEXT최대길이 4294967295(2^32 – 1)의 BLOB, TEXT형
MEDIUMBLOB /MEDIUMTEXT BOLBTEXT형, 최대길이 16777215문자
LONGBLOB / LONGTEXT BOLB최대길이 4294967295문자
기 타ENUM 문자열 목록형최대 65535개, 저장된 문자열 목록 중에 오직 한가지만 얻을 수 있습니다.
SET 문자열 목록형최대 64개, 저장된 문자열 목록 중에 0, 1개 이상을 얻을 수 있습니다
BOOLfalse, true 값만 갖는 가장 단순한 자료형.
 BLOB과 TEXT형의 기타 사항
1. 인덱스를 생성할 수 없다.
2. 지정된 최대크기보다 작은 문자열이 저장 시에도 공백이 제거되지 않는다.
3. 기본 값을 지정할 수 없으므로 NOT NULL 은 무효이다.
4. 테이블이 아닌 다른 영역에 저장된다. ※MySql의 테이블의 자료형을 보기위한 명령어
mysql> desc 테이블명;

 

 

 

[SQL 관련 기본이 잘 정리된 곳]

http://database.sarang.net/?inc=front&criteria=mysql

http://sqlprogramming.co.kr/

 

 

 

[Mysql 최적화 관련]

1.TABLE 관련
OPTIMIZE TABLE
table type이 myisam이나 bdb에서 사용하며 많은양의 데이터가 삭제되었거나 삽입되었을때 사용하면 속도향상에 좋습니다.
ex)optimaze table table_name;
2.QUERY 관련
JOIN
join이 subquery보다 빠르다.
join시 사용되는 컬럼은 동일한 column type과 길이를 가져야만 최적의 속도를 보장한다.
index설정
인덱스를 적절히 잡아줍니다.
필요한 컬럼만 select 한다.
select * 되어 있는부분을 필요할 필드만 가져옵니다.
processlist로 실행 쿼리를 mysql상태를 체크
cd /usr/local/mysql
./bin/mysqladmin -i3 processlist -ppassword
//3초에 한번씩 실행됩니다.
잘못된 query 발견
explain으로 질의과정 점검 몇번의 레코드를 검색하여 수행하는지 그리고 해당 컬럼이 index를 잘타는지 보고 쿼리를 수정합니다.
slow query log기능
slow query log를 걸어놓고 slow query time를 한 5초로 정해주시면 5초 이상 실행이 걸리는 쿼리는 모조리 로그에 남습니다.
그 query를 튜닝합니다.(explain이용)
query우선 설정
select 구문에는
select HIGH_PRIORITY id,name, … from table_name …
delete, insert, update 구문에는
delete LOW_PRIORITY from table_name ….
mysql 4.x 이상부터 지원!
query cache을 사용합니다.
int형과char형의 검색속도차이
int 가 char보다는 검색속도가 더 빠르다고 하지만 시스템구성과 환경마다 다릅니다.
3.하드웨어 관련
웹호스팅이 아닌 서버호스팅을 합니다.
평균 조회 레코드 50만건이상
조회 데이타가 50만건~100만건이면 DB서버와 웹서버를(이미지 서버등등) 나누어 분산처리(2-Tier)하며 조회 건수가 100만건 이상은 ORACLE같은 상용 DB로 교체해야 한다.
메모리를 증설 합니다.
1-2G 정도
하드디스크 스카시로 교체
e-ide보다 데이타 io(입/출력) 더 빠르다.(10000 rpm이상급)
[출처] 제트스윙 블로그 – http://www.zetswing.com

mysqlServer_Optimize20051008231013

 

 

 

[슬로우 쿼리 확인하는 법]

슬로우 쿼리(Slow Query) 보기

MySQL을 사용하다보면 데이터의 Insert가 오래걸린다던가..

어떤 내용을 보는데 페이지가 한참 걸린다던가 하는 등의 이상 현상들이 발생할 때가 있습니다.

이럴때는 쿼리가 처리되는데 얼마만큼 시간이 소요되었는지를 MySQL의 로그를 통해 볼 수 있습니다.

슬로우 로그를 남기려면 데몬 실행시 –log-slow-queries 라는 옵션을 줍니다.

safe_mysqld –log-slow-queries=slow_query.log

5 초 이상의 긴 쿼리 시간을 가지는 것만 로그를 남기고자 할 경우 는 long_query_time 옵션을 사용합니다.

$ safe_mysqld –log-slow-queries=slow_query.log -O long_query_time=5

MySQL config에 옵션을 저장하고자 할경우

vi my.cnf

log-slow-queries = /var/log/mysql/mysql-slow.log

long_query_time = 5

 

my.cnf  파일의 [mysqld] 항목에 아래 내용을 입력 후, mysql 재시작을 해줍니다.

> 슬로우 쿼리로 분류할 시간, 로그파일 위치

 

MySQL 5.1.34 이상 버전

slow_query_log = 10

slow_query_log_file = /usr/local/mysql/data/mysql-slow-queries.log

 

MySQL 4.x 이상 버전

long_query_time = 10

log-slow-queries = /usr/local/mysql/data/mysql-slow-queries.log

 

MySQL 3.x 이하 버전

set-variable = long_query_time = 10

log-slow-queries = /usr/local/mysql/data/mysql-slow-queries.log

 

 

 

 

[NULL 그리고 DEFAULT VALUES 와 ENUM, SQL_MODE]

http://www.peachpit.com/articles/article.aspx?p=30885&seqNum=8

http://lists.mysql.com/mysql/156823

http://dev.mysql.com/doc/refman/5.0/en/enum.html

http://www.mysqlfaqs.net/mysql-faqs/Client-Server-Commands/What-is-sql-mode-in-MySQL-and-how-can-we-set-it

https://drupal.org/node/164401

http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html

 

 

 

 

[Mysql creationTime 필드 관련]

XE 의 생성일 DB 필드 구조 : regdate varchar(14) utf8_general_ci / 20120510152625 / NULL 가능.

또다른 자바 버전 플랫폼에서는 : creation_time int(14) NOT NULL DEFAULT 0 / 1376184317 / NULL 가능. (이 방법이 제일 좋은듯함. unix 시간 형식.)

 

NOW(), CURTIME(), CURDATE(), …

http://www.nazuni.pe.kr/web/dev/mysql/functions_datetime.php

http://www.w3schools.com/sql/func_curdate.asp

 

DATE, TIME, DATETIME, TIMESTAMP

http://dev.mysql.com/doc/refman/5.5/en/datetime.html

 

위의 Mysql 함수들은 2013-05-29 00:00:00 의 형태를 기본으로 한다.

 

 

 

 

[Mysql 날짜 함수 정리]

MySQL 날짜 함수 정리

 

DATE 값을 기대하는 함수들은 일반적으로 DATETIME 값을 수용하고, TIME 부분은 무시한다. TIME 값을 기대하는 함수들은 일반적으로 DATETIME 값을 수용하고, DATE 부분은 무시한다.
현재 날짜나 시간을 반환(return)하는 함수들은 쿼리가 실행될 때 단 한번만 그 값을 구한다. 이것은 한 쿼리 안에 NOW()와 같은 함수가 여러번 사용되었을 경우에도 모두 같은 결과값을 참조한다는 것을 의미한다. 이 원칙은 CURDATE(), CURTIME(),UTC_DATE(), UTC_TIME(), UTC_TIMESTAMP() 등의 함수에도 적용된다.
MySQL 4.1.3 버전부터 제공된 CURRENT_TIMESTAMP(), CURRENT_TIME(), CURRENT_DATE(), FROM_UNIXTIME() 함수들은 연결 상태의 현재 시간대에 해당되는 반환값을 갖는다. 또한 UNIX_TIMESTAMP()도 그 인자(argument)가 현재 시간대에 해당되는 DATETIME 값이라는 것을 가정한다.
다음 함수 설명들의 반환값 범위는 완전한 날짜를 요구한다. 날짜가 ’0′이거나 ’2001-11-00′처럼 불완전하다면, DATE 부분을 추출하는 함수는 ’0′을 반환할 것이다. 예를 들어, DAYOFMONTH(’2001-11-00′)은 ’0′을 반환한다.

ADDDATE(date,INTERVAL expr type) , ADDDATE(expr,days)

ADDDATE()는 두번째 인자에서 INTERVAL과 함께 사용되면 DATE_ADD()의 별칭이 된다. 마찬가지로 SUBDATE()는 DATE_SUB()의 별칭이다. INTERVAL 인자에 관해서는 DATE_ADD() 설명을 참조하라.

 

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

MySQL 4.1.1 버전부터 두번째 문법이 허용되었다. expr 가 DATE 또는 DATETIME 형식일 때, days 는 expr 에 추가되는 일수이다.

mysql> SELECT ADDDATE('1998-01-02', 31);
        -> '1998-02-02'

ADDTIME(expr,expr2)

ADDTIME()는 expr 에 expr2 를 더하고 그 결과를 반환한다. expr 는 TIME 또는 DATETIME 형식이고, expr2 는 시간 표현이다.

mysql> SELECT ADDTIME('1997-12-31 23:59:59.999999', '1 1:1:1.000002');
        -> '1998-01-02 01:01:01.000001'
mysql> SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');
        -> '03:00:01.999997'

ADDTIME()는 MySQL 4.1.1 버전에서 추가되었다.

CONVERT_TZ(dt,from_tz,to_tz)

CONVERT_TZ()는 DATETIME 값 dt 를 from_tz 시간대에서 to_tz 시간대로 변환하고, 결과값을 반환한다. 이 함수는 인자가 유효하지 않으면 NULL 값을 반환한다.
from_tz 에서 UTC으로 변환될 때 입력값이 TIMESTAMP 형의 범위를 벗어나면 변환은 일어나지 않는다.

 

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');
        -> '2004-01-01 13:00:00'
mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','-07:00');
        -> '2004-01-01 05:00:00'

‘MET’이나 ‘Europe/Moscow’와 같은 시간대를 사용하기 위해서는, 시간대 표가 적절하게 설정되어야 한다.

CONVERT_TZ()는 MySQL 4.1.3 버전에서 추가되었다.

CURDATE()

함수가 문자열이나 숫자로 사용되었는지 문맥에 따라서 ‘YYYY-MM-DD’이나 YYYYMMDD 형식으로 현재 날짜를 반환한다.

 

mysql> SELECT CURDATE();
        -> '1997-12-15'
mysql> SELECT CURDATE() + 0;
        -> 19971215

CURRENT_DATE , CURRENT_DATE()

CURRENT_DATE와 CURRENT_DATE()는 CURDATE()의 별칭이다.

CURTIME()

함수가 문자열이나 숫자로 사용되었는지 문맥에 따라서 ‘HH:MM:SS’이나 HHMMSS 형식으로 현재 시간을 반환한다.

 

mysql> SELECT CURTIME();
        -> '23:50:26'
mysql> SELECT CURTIME() + 0;
        -> 235026

CURRENT_TIME , CURRENT_TIME()

CURRENT_TIME와 CURRENT_TIME()는 CURTIME()의 별칭이다.

CURRENT_TIMESTAMP , CURRENT_TIMESTAMP()

CURRENT_TIMESTAMP와 CURRENT_TIMESTAMP()는 NOW()의 별칭이다.

DATE(expr)

날짜(date)나 DATETIME 표현 expr 에서 DATE 부분을 추출한다.

 

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

DATE()는 MySQL 4.1.1 버전부터 사용이 가능하다.

DATEDIFF(expr,expr2)

DATEDIFF()는 시작 날짜 expr 와 마지막 날짜 expr2 사이의 일수를 반환한다. expr 와 expr2 는 날짜(date) 또는 date-and-time 표현이다. 반환값의 DATE 부분만 계산된다.

 

mysql> SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');
        -> 1
mysql> SELECT DATEDIFF('1997-11-30 23:59:59','1997-12-31');
        -> -31

DATEDIFF()는 MySQL 4.1.1 버전에서 추가되었다.

DATE_ADD(date,INTERVAL expr type) ,DATE_SUB(date,INTERVAL expr type)

이 함수들은 날짜 계산을 수행한다. date 는 시작 날짜를 지정하는 DATETIME 또는 DATE 값이다. expr 는 시작 날짜로부터 더하거나 뺀 간격 값을 지정하는 표현이다. expr 는 문자열이다. 마이너스(‘-’)로 시작될 수도 있다. type 는 어떻게 해석할지를 지정하는 키워드이다.
INTERVAL 키워드와 type 지정자는 대소문자를 구분하지 않는다.
다음 표는 type 와 expr 인자가 어떤 관계인지 보여준다.

 

type 값기대되는 expr 형식
MICROSECONDMICROSECONDS
SECONDSECONDS
MINUTEMINUTES
HOURHOURS
DAYDAYS
WEEKWEEKS
MONTHMONTHS
QUARTERQUARTERS
YEARYEARS
SECOND_MICROSECOND‘SECONDS.MICROSECONDS’
MINUTE_MICROSECOND‘MINUTES.MICROSECONDS’
MINUTE_SECOND‘MINUTES:SECONDS’
HOUR_MICROSECOND‘HOURS.MICROSECONDS’
HOUR_SECOND‘HOURS:MINUTES:SECONDS’
HOUR_MINUTE‘HOURS:MINUTES’
DAY_MICROSECOND‘DAYS.MICROSECONDS’
DAY_SECOND‘DAYS HOURS:MINUTES:SECONDS’
DAY_MINUTE‘DAYS HOURS:MINUTES’
DAY_HOUR‘DAYS HOURS’
YEAR_MONTH‘YEARS-MONTHS’

type 값 DAY_MICROSECOND, HOUR_MICROSECOND, MINUTE_MICROSECOND, SECOND_MICROSECOND, MICROSECOND 는 MySQL 4.1.1 버전부터 사용이 가능하다. 값 QUARTER과 WEEK는 MySQL 5.0.0 버전부터 사용이 가능하다.

MySQL은 expr 형식 안에서 어떤 구문 구획자도 허용한다. 표에서 볼 수 있는 것들은 제안된 구획자들이다. date 인자가 DATE 값이고 단지 YEAR, MONTH, DAY 만 계산하고자 한다면(TIME 부분이 필요 없다면), 결과는 DATE 값이다. 그렇지 않다면, 결과는 DATETIME 값이다.
MySQL 3.23 버전부터, INTERVAL expr type 는 다른 부분이 DATE 또는 DATETIME 값으로 표현되어 있다면 어느 한쪽이라도 + 연산자의 사용을 허용한다. – 연산자는 오른쪽에만 허용된다. 간격에서 DATE 또는 DATETIME 값을 빼는 것은 무의미하기 때문이다. (아래 예문을 참조하라.)

 

mysql> SELECT '1997-12-31 23:59:59' + INTERVAL 1 SECOND;
        -> '1998-01-01 00:00:00'
mysql> SELECT INTERVAL 1 DAY + '1997-12-31';
        -> '1998-01-01'
mysql> SELECT '1998-01-01' - INTERVAL 1 SECOND;
        -> '1997-12-31 23:59:59'
mysql> SELECT DATE_ADD('1997-12-31 23:59:59', INTERVAL 1 SECOND);
        -> '1998-01-01 00:00:00'
mysql> SELECT DATE_ADD('1997-12-31 23:59:59', INTERVAL 1 DAY);
        -> '1998-01-01 23:59:59'
mysql> SELECT DATE_ADD('1997-12-31 23:59:59', INTERVAL '1:1' MINUTE_SECOND);
        -> '1998-01-01 00:01:00'
mysql> SELECT DATE_SUB('1998-01-01 00:00:00', INTERVAL '1 1:1:1' DAY_SECOND);
        -> '1997-12-30 22:58:59'
mysql> SELECT DATE_ADD('1998-01-01 00:00:00', INTERVAL '-1 10' DAY_HOUR);
        -> '1997-12-30 14:00:00'
mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
        -> '1997-12-02'
mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002', INTERVAL '1.999999' SECOND_MICROSECOND);
        -> '1993-01-01 00:00:01.000001'

지정한 간격(interval) 값이 너무 짧다면(type 키워드로부터 기대되는 모든 간격 부분이 포함되어 있지 않다면), MySQL은 간격 값의 왼쪽 부분을 남겼다고 가정한다. 예를 들어, type DAY_SECOND를 지정했다면, expr 값은 일, 시, 분, 초 부분이 기대된다. ’1:10′과 같은 값을 지정했다면, MySQL은 일, 시 부분이 없는 분, 초 값이라고 가정한다. 다르게 말하면, ’1:10′ DAY_SECOND는 ’1:10′ MINUTE_SECOND과 동일한 값으로 해석된다. 이것은 MySQL가 TIME 값을 시각보다 시간으로 해석하는 것과 비슷하다.

TIME 부분을 포함하는 어떤 값에서 date 를 더하거나 뺀다면, 결과는 자동으로 DATETIME 값으로 변환된다.

 

mysql> SELECT DATE_ADD('1999-01-01', INTERVAL 1 DAY);
        -> '1999-01-02'
mysql> SELECT DATE_ADD('1999-01-01', INTERVAL 1 HOUR);
        -> '1999-01-01 01:00:00'

기형적인 날짜를 입력하면 결과는 NULL이 된다. 만일 MONTH, YEAR_MONTH, 또는 YEAR를 더해서 새로운 달의 일수보다 더 큰 날짜가 된다면, 날짜는 새로운 달의 마지막 날로 보정된다.

mysql> SELECT DATE_ADD('1998-01-30', INTERVAL 1 MONTH);
        -> '1998-02-28'

DATE_FORMAT(date,format)

format 문자열에 따라 date 값을 형식화한다. format 문자열에는 다음 지정자들이 사용된다.

지정자설명
%aAbbreviated weekday name (Sun..Sat)
%bAbbreviated month name (Jan..Dec)
%cMonth, numeric (0..12)
%DDay of the month with English suffix (0th, 1st, 2nd, 3rd, …)
%dDay of the month, numeric (00..31)
%eDay of the month, numeric (0..31)
%fMicroseconds (000000..999999)
%HHour (00..23)
%hHour (01..12)
%IHour (01..12)
%iMinutes, numeric (00..59)
%jDay of year (001..366)
%kHour (0..23)
%lHour (1..12)
%MMonth name (January..December)
%mMonth, numeric (00..12)
%pAM or PM
%rTime, 12-hour (hh:mm:ss followed by AM or PM)
%SSeconds (00..59)
%sSeconds (00..59)
%TTime, 24-hour (hh:mm:ss)
%UWeek (00..53), where Sunday is the first day of the week
%uWeek (00..53), where Monday is the first day of the week
%VWeek (01..53), where Sunday is the first day of the week; used with %X
%vWeek (01..53), where Monday is the first day of the week; used with %x
%WWeekday name (Sunday..Saturday)
%wDay of the week (0=Sunday..6=Saturday)
%XYear for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%xYear for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%YYear, numeric, four digits
%yYear, numeric, two digits
%%A literal ‘%’.

다른 모든 문자들은 변환없이 결과에 그대로 복사된다.

%v, %V, %x, %X format 지정자들은 MySQL 3.23.8 버전부터 사용이 가능하고, %f는 MySQL 4.1.1 버전부터 가능하다.
MySQL 3.23 버전부터는 ‘%’ 문자가 format 지정문자 앞에 요구된다. 그 이전 버번에서 ‘%’는 선택사양이다.
월일의 범위가 ’0′으로 시작되기 때문에 MySQL 3.23 버전부터는 ’2004-00-00′와 같은 불완전한 날짜가 허용된다.

 

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'

DAY(date)

DAY()는 DAYOFMONTH()의 별칭이다. MySQL 4.1.1 버전부터 사용이 가능하다.

DAYNAME(date)

date 에 대한 요일 이름을 반환한다.

 

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

DAYOFMONTH(date)

date 에 대한 당월의 날짜를 반환한다. (범위 1~31)

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

DAYOFWEEK(date)

date 에 대하여 요일 색인(1 = 일요일, 2 = 월요일, …, 7 = 토요일)을 반환한다. 이 색인값들은 ODBC 표준에 따른다.

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

DAYOFYEAR(date)

date 가 해당 연도에 몇일째인지 반환한다. (범위 1~366)

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

EXTRACT(type 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

EXTRACT()는 MySQL 3.23.0 버전에서 추가되었다.

FROM_DAYS(N)

일수 N 가 주어지면, DATE 값을 반환한다.

 

mysql> SELECT FROM_DAYS(729669);
        -> '1997-10-07'

FROM_DAYS()는 그레고리안 달력의 출현(1582년) 이전의 값을 사용할 수 있도록 계획되지 않았다. 달력이 바뀌었을 때 손실된 날짜는 고려하지 않는다.

FROM_UNIXTIME(unix_timestamp) , FROM_UNIXTIME(unix_timestamp,format)

함수에 사용된 문맥이 문자열인지 숫자인지에 따라 ‘YYYY-MM-DD HH:MM:SS’ 또는 YYYYMMDDHHMMSS format 값으로unix_timestamp 인자가 표시되어 반환된다.

 

mysql> SELECT FROM_UNIXTIME(875996580);
        -> '1997-10-04 22:23:00'
mysql> SELECT FROM_UNIXTIME(875996580) + 0;
        -> 19971004222300

format 이 주어진다면 결과는 format 문자열에 따라 형식화된다. format 은 DATE_FORMAT() 함수에 쓰이는 지정자를 똑같이 사용한다.

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() 함수와 조합할 때 유용하다.

첫번째 인자로는 3가지 가능한 값이 있고, 두번째 인자로는 5가지 가능한 값이 있어서 결과적으로 15가지 형식 문자열이 가능하다. (사용되는 지정자를 위해서 DATE_FORMAT() 설명을 참조하라.)

 

함수 호출결과
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’

ISO 형식은 ISO 8601이 아니라 ISO 9075이다.

MySQL 4.1.4 버전부터는 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

GET_FORMAT()는 MySQL 4.1.1 버전부터 사용이 가능하다.

HOUR(time)

time 에서 시간을 반환한다. (범위 0~23)

 

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

그러나 TIME 값의 크기는 실제로 훨씬 더 크다. HOUR는 23보다 더 큰 값을 반환할 수 있다.

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

LAST_DAY(date)

DATE 또는 DATETIME 값을 입력하면 당월의 마지막 날에 대한 상응하는 값을 반환한다. 인자가 유효하지 않으면 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

LAST_DAY()는 MySQL 4.1.1 버전부터 사용이 가능하다.

LOCALTIME , LOCALTIME()

LOCALTIME과 LOCALTIME()은 NOW()의 별칭이다.
두 함수는 MySQL 4.0.6 버전에서 추가되었다.

LOCALTIMESTAMP , LOCALTIMESTAMP()

LOCALTIMESTAMP와 LOCALTIMESTAMP()는 NOW()의 별칭이다.
두 함수는 MySQL 4.0.6 버전에서 추가되었다.

MAKEDATE(year,dayofyear)

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

MAKEDATE()는 MySQL 4.1.1 버전부터 사용이 가능하다.

MAKETIME(hour,minute,second)

hour,minute,second 인자로부터 계산된 시간 값을 반환한다.

 

mysql> SELECT MAKETIME(12,15,30);
        -> '12:15:30'

MAKETIME()는 MySQL 4.1.1 버전부터 사용이 가능하다.

MICROSECOND(expr)

TIME 또는 DATETIME 형식의 expr 로부터 마이크로초를 반환한다. (범위 0~999999)

 

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

MICROSECOND()는 MySQL 4.1.1 버전부터 사용이 가능하다.

MINUTE(time)

time 에 대하여 몇 분인지 반환한다. (범위 0~59)

 

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

MONTH(date)

date 에 대하여 몇 월인지 반환한다. (범위 1~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

PERIOD_ADD(P,N)

기간 P 에 N 월을 더한다(YYMM 또는 YYYYMM 형식으로). YYYYMM 형식으로 결과를 반환한다. 기간 P 가 DATE 값이 아니라는 것에 주의하라.

mysql> SELECT PERIOD_ADD(9801,2);
        -> 199803

PERIOD_DIFF(P1,P2)

기간 P1, P2 사이의 개월수를 반환한다. P1 과 P2 는 YYMM 또는 YYYYMM 형식이어야 한다. 기간 P1, P2 가 DATE 값이 아니라는 것에 주의하라.

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 에 포함된 DATE, TIME 또는 DATETIME 값은 format 에 의해 지정된 형식으로 주어져야 한다. format 에 사용할 수 있는 지정자에 대해서는 DATE_FORMAT() 설명을 참조하라. 다른 모든 문자는 해석되지 않고 그대로 반영된다. 만일 str 가 유효하지 않은 값을 포함한다면 NULL이 반환된다. MySQL 5.0.3 버전부터는 잘못된 값 또한 경고를 발생한다.

 

mysql> SELECT STR_TO_DATE('03.10.2003 09.20', '%d.%m.%Y %H.%i');
        -> '2003-10-03 09:20:00'
mysql> SELECT STR_TO_DATE('10arp', '%carp');
        -> '0000-10-00 00:00:00'
mysql> SELECT STR_TO_DATE('2003-15-10 00:00:00', '%Y-%m-%d %H:%i:%s');
        -> NULL

어떤 달의 일수보다 큰 일수를 가진 날짜는 1-31 범위 안에서 허용된다. 또한 ’0′이나 ’0′값을 가진 날짜도 허용된다.

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'

STR_TO_DATE()는 MySQL 4.1.1 버전부터 사용이 가능하다.

SUBDATE(date,INTERVAL expr type) , SUBDATE(expr,days)

두번째 인자 INTERVAL 형식을 포함하여 사용되었을 때 SUBDATE()는 DATE_SUB()의 별칭이다. INTERVAL 인자에 대한 정보는DATE_ADD() 설명을 참조하라.

 

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'

MySQL 4.1.1 버전부터 두번째 문법이 허용된다. expr 는 DATE 또는 DATETIME 형식이고 days 는 expr 에서 뺄 일수이다.

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

SUBTIME(expr,expr2)

SUBTIME()는 expr 에서 expr2 를 빼고 그 값을 반환한다. expr 는 TIME 또는 DATETIME 형식이고, expr2 는 TIME 형식이다.

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'

SUBTIME()는 MySQL 4.1.1 버전에서 추가되었다.

SYSDATE()

SYSDATE()는 NOW()의 별칭이다.

TIME(expr)

TIME 또는 DATETIME 형식의 expr 에서 TIME 부분을 추출한다.

 

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'

TIME()는 MySQL 4.1.1 버전부터 사용이 가능하다.

TIMEDIFF(expr,expr2)

TIMEDIFF()는 시작 시간 expr 와 마지막 시간 expr2 와의 차이를 TIME 값으로 반환한다. expr 와 expr2 는 TIME 또는 DATETIME 형식이고, 두 형식은 같아야 한다.

 

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'

TIMEDIFF()는 MySQL 4.1.1 버전에서 추가되었다.

TIMESTAMP(expr) , TIMESTAMP(expr,expr2)

인자 하나만을 사용한다면, DATE 또는 DATETIME expr 를 DATETIME 값으로 반환한다. 인자 두 개를 사용한다면, DATE 또는 DATETIME 형식의 expr 에 TIME 형식의 expr2 를 더하고 그 DATETIME 값을 반환한다.

 

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'

TIMESTAMP()는 MySQL 4.1.1 버전부터 사용이 가능하다.

TIMESTAMPADD(interval,int_expr,DATETIME_expr)

DATE 또는 DATETIME 형식의 DATETIME_expr 에 정수 형식의 int_expr 를 더한다. int_expr 의 단위는 interval 인자로 주어지는데, 다음 값 가운데 하나이어야 한다. FRAC_SECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR.
interval 값은 상기된 키워드 가운데 하나를 지정하거나 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()는 MySQL 5.0.0 버전부터 사용이 가능하다.

TIMESTAMPDIFF(interval,DATETIME_expr1,DATETIME_expr2)

DATE 또는 DATETIME 형식의 DATETIME_expr1,DATETIME_expr2 사이의 격차를 정수값으로 반환한다. 결과값의 단위는interval 인자에 의해 주어진다. interval 의 허용값은 TIMESTAMPADD() 함수와 같다.

 

mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');
        -> 3
mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');
        -> -1

TIMESTAMPDIFF()는 MySQL 5.0.0 버전부터 사용이 가능하다.

TIME_FORMAT(time,format)

이 함수는 DATE_FORMAT() 함수처럼 사용되지만, format 문자열은 시, 분, 초에 관련된 지정자만을 포함할 수 있다. 다른 지정자들은 NULL 값이나 ’0′을 발생한다.
time 값이 TIME 부분에서 23보다 큰 값을 갖는다면, %H와 %k 시간 지정자는 일상적인 범위 0-23보다 더 큰 값을 발생한다. 다른 시간 지정자들은 12 법(modulo)의 값을 발생한다.

 

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

TO_DAYS()는 그레고리안 달력의 출현(1582년) 이전의 값을 사용할 수 있도록 계획되지 않았다. 달력이 바뀌었을 때 손실된 날짜는 고려하지 않는다.

MySQL는 날짜에 있는 2자리 형식의 연도를 4자리 형식으로 변환한다는 것을 기억하라. 예를 들어, ’1997-10-07′과 ’97-10-07′는 동일한 날짜로 간주한다.

 

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

1582년 이전의 다른 날짜에 대해서는 이 함수는 결과값이 정의되지 않았다.

UNIX_TIMESTAMP() , UNIX_TIMESTAMP(date)

인자 없이 호출이 된다면, 부호없는 정수의 유닉스 시간(’1970-01-01 00:00:00′ GMT부터 계산된 초)을 반환한다. UNIX_TIMESTAMP()가 date 인자와 함께 호출된다면, ’1970-01-01 00:00:00′ GMT부터 계산된 초 값을 반한다. date 는 DATE 문자열, DATETIME 문자열, TIMESTAMP 문자열, YYMMDD 또는 YYYYMMDD 형식의 숫자를 허용한다.

 

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

UNIX_TIMESTAMP가 TIMESTAMP 형식으로 사용되었을 때 이 함수는 내부의 TIMESTAMP 값을 직접 반환한다. 무조건 문자열을 유닉스 시간으로 변환하지 않는다. UNIX_TIMESTAMP()에 범위에서 벗어난 날짜를 입력했다면 0이 반환되지만, 기본적인 범위만 확인된다는 것에 주의하라. (연도는1970-2037, 월 01-12, 일 01-31)

UTC_DATE , UTC_DATE()

함수가 사용된 문맥에 따라 ‘YYYY-MM-DD’ 또는 YYYYMMDD 형식으로 현재의 UTC 날짜 값을 반환한다.

 

mysql> SELECT UTC_DATE(), UTC_DATE() + 0;
        -> '2003-08-14', 20030814

UTC_DATE()는 MySQL 4.1.1 버전부터 사용이 가능하다.

UTC_TIME , UTC_TIME()

함수가 사용된 문맥에 따라 ‘HH:MM:SS’ 또는 HHMMSS 형식으로 현재의 UTC 시간 값을 반환한다.

 

mysql> SELECT UTC_TIME(), UTC_TIME() + 0;
        -> '18:07:53', 180753

UTC_TIME()는 MySQL 4.1.1 버전부터 사용이 가능하다.

UTC_TIMESTAMP , UTC_TIMESTAMP()

함수가 사용된 문맥에 따라 ‘YYYY-MM-DD HH:MM:SS’ 또는 YYYYMMDDHHMMSS 형식으로 현재의 UTC 일시 값을 반환한다.

 

mysql> SELECT UTC_TIMESTAMP(), UTC_TIMESTAMP() + 0;
        -> '2003-08-14 18:08:04', 20030814180804

UTC_TIMESTAMP()는 MySQL 4.1.1 버전부터 사용이 가능하다.

WEEK(date[,mode])

date 에 대하여 몇번째 주인지 반환한다. 2개 인자를 사용하는 형식에서는, 한 주의 시작을 일요일로 할 것인지 월요일로 할 것인지, 결과값의 범위를 0-53으로 할 것인지 1-53으로 할 것인지를 지정할 수 있다. mode 인자가 생략되면 시스템 기본값이 사용된다. (MySQL 4.0.14 버전 이전에는 0)
mode 인자는 아래 표와 같이 작동한다.

 

모드한 주의 시작요일범위Week 1 is the first week…
0Sunday0-53with a Sunday in this year
1Monday0-53with more than 3 days this year
2Sunday1-53with a Sunday in this year
3Monday1-53with more than 3 days this year
4Sunday0-53with more than 3 days this year
5Monday0-53with a Monday in this year
6Sunday1-53with more than 3 days this year
7Monday1-53with a Monday in this year

mode 3은 MySQL 4.0.5 버전부터 사용할 수 있으며, 4 이상의 mode 는 MySQL 4.0.17 버전부터 사용할 수 있다.

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 4.0 버전에서 WEEK(date,0)는 미국 달력에 알맞게 변했다. 그 전에 WEEK()는 미국 날짜에서 잘못 계산되었다. (사실상 WEEK(date)와 WEEK(date,0)는 모든 경우에 오류가 있었다.)

이전 연도의 마지막주에서 날짜가 맞아떨어지면, mode 인자를 2, 3, 6, 7 으로 선택하지 않는 한 MySQL은 0을 반환한다.

 

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

어떤 이는 실제로 주어진 날짜가 1999년의 52째주이기 때문에 WEEK() 함수가 52를 반환해야 한다고 주장한다. 우리는 주어진 연도에서 몇 째주인지 반환할 것을 원했기 때문에 그 대신 0을 반환하기로 했다. 이것은 WEEK() 함수를 날짜에서 DATE 부분을 추출하는 다른 함수들과 결합하여 사용할 때 유용하다.

주어진 날짜의 주간 첫날을 포함한 해를 고려한 결과값을 원한다면, mode 인자를 0, 2, 5, 7 로 선택해야 한다.

 

mysql> SELECT WEEK('2000-01-01',2);
        -> 52

YEARWEEK() 함수를 사용하는 것도 대안이 될 수 있다.

mysql> SELECT YEARWEEK('2000-01-01');
        -> 199952
mysql> SELECT MID(YEARWEEK('2000-01-01'),5,2);
        -> '52'

WEEKDAY(date)

date 에 대한 요일 색인값(0 = 월요일, 1 = 화요일, … 6 = 일요일)을 반환한다.

mysql> SELECT WEEKDAY('1998-02-03 22:23:00');
        -> 1
mysql> SELECT WEEKDAY('1997-11-05');
        -> 2

WEEKOFYEAR(date)

date 의 달력에서 몇 주째인지 1-53 범위의 값을 반환한다. 이 함수는 WEEK(date,3)과 동일하다.

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

WEEKOFYEAR()는 MySQL 4.1.1 버전부터 사용이 가능하다.

YEAR(date)

date 에서 해당 연도를 반환한다. (범위 1000-9999)

 

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

YEARWEEK(date) , YEARWEEK(date,start)

date 에 대하여 연도와 몇 주째인지 반환한다. 첫번째 인자는 WEEK()의 첫번째 인자와 정확하게 작동한다. 결과 안의 연도는 첫 주와 마지막 주에 한하여 date 인자의 연도와 다를 수 있다.

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

주수는 선택 인자 0 또는 1 에 대하여 WEEK() 함수가 반환하는 것과 다르다. WEEK()는 주어진 연도의 문맥에서 주 값을 반환한다.

YEARWEEK()는 MySQL 3.23.8 버전에서 추가되었다.

# by 몽삼이

 

 

 

 

[nick_name 이 이메일 주소인 것들 찾기 sql]

- nick_name이 이메일 주소인 것들 select 쿼리

SELECT * FROM xe_member WHERE nick_name REGEXP ‘^[a-zA-Z0-9][a-zA-Z0-9._-]*[a-zA-Z0-9]@[a-zA-Z0-9][a-zA-Z0-9._-]*[a-zA-Z0-9].[a-zA-Z]{2,4}$’

 

- nick_name이 이메일 주소인 것들을 email_id로 update하는 쿼리

UPDATE xe_member SET nick_name=email_id WHERE nick_name REGEXP ‘^[a-zA-Z0-9][a-zA-Z0-9._-]*[a-zA-Z0-9]@[a-zA-Z0-9][a-zA-Z0-9._-]*[a-zA-Z0-9].[a-zA-Z]{2,4}$’

 

 

 

 

[MSSQL -> MySQL 데이터 복사]

가끔씩 이기종 DBMS 자료 이관(복사)를 하게 될 때가 있는데,
파일로 export 후 import 하는 과정을 해도 되지만 스케줄러에 의해 수행되도록 하기 위해서는 스크립트에서 수행되도록 코드를 짜는게 편리한 것 같습니다.
이 스크립트(php)를 수행하기 위해서는 SQLServerDriverForPHP.EXE 가 설치되어 있어야 합니다.
저는 PC에 PHP for Windows를 설치하고 SQLServerDriver를 설치했습니다.
그리고 콘솔(command or cmd)을 이용해서 자료 이관 명령을 사용합니다.
이와 유사한 일에 종사하는 분에게 필요할 지 몰라서 지식과 정보 교류를 위해 관련 소스를 올려봅니다.
<?php
//MSSQL DB 정보
$uid = “id1″;
$pwd = “pass1″;
$host = “192.168.10.2″;
$connectionInfo = array(“UID” => $uid, “PWD” => $pwd, “Database”=>”DB1″);
$conn = sqlsrv_connect( $host, $connectionInfo);
//이관 타겟 DB 정보
$conn2 = mysql_connect(“192.168.10.3″, “id2″, “pass2″);
mysql_select_db(“DB2″, $conn2);
if( $conn ) {
echo “MSSQL Connection established.n”;
} else {
echo “MSSQL Connection could not be established.n”;
die( print_r( sqlsrv_errors(), true));
}
if( $conn2 ) {
echo “MySQL Connection established.n”;
} else {
echo “MySQL Connection could not be established.n”;
die( print_r( mysql_error()));
}
$sql = “select EventPIN, convert(varchar(19), EventTime,120) as EventTime, EventType, GateNum, GateInfo, Channel, FunctionKey, FailType, TryCount from TEvent where EventPIN <>” and convert(varchar(10), RecordTime,120) >= ’2010-01-01′ and convert(varchar(10), RecordTime,120) <= ’2010-12-31′ order by EventTime”;
$stmt = sqlsrv_query( $conn, $sql);
if( $stmt === false ) {
echo “Error in statement preparation/execution.n”;
die( print_r( sqlsrv_errors(), true));
}
$i=0;
while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)) {
$row['GateInfo'] = trim($row['GateInfo']);
$str = “{$row['EventPIN']}t{$row['EventTime']}t{$row['GateNum']}t{$row['GateInfo']}t{$row['Channel']}t{$row['FailType']}t{$row['TryCount']}rn”;
$sql2 = “insert into ncsp.TEvent2010 set EventPIN=’{$row['EventPIN']}’, EventTime=’{$row['EventTime']}’, GateNum=’{$row['GateNum']}’, GateInfo=’{$row['GateInfo']}’, Channel=’{$row['Channel']}’, FunctionKey=’{$row['FunctionKey']}’, FailType=’{$row['FailType']}’, TryCount=’{$row['TryCount']}’”;
mysql_query($sql2) or die(mysql_error());
echo $str;
$i++;
}
sqlsrv_free_stmt($stmt);
sqlsrv_close($conn);
mysql_close($conn2);
?>

 

 

 

 

[INDEX의 의미?]

http://www.mysqlkorea.co.kr/sub.html?mcode=manual&scode=01&m_no=21714&cat1=7&cat2=219&cat3=253&lang=k

 

INDEX의 의미?

 

RDBMS에서 검색속도를 높이기 사용하는 하나의 기술이입니다.

INDEX는 색인입니다. 해당 TABLE의 컬럼을 색인화(따로 파일로 저장)하여 검색시 해당 TABLE의 레코드를 full scan 하는게 아니라 색인화 되어있는 INDEX 파일을 검색하여 검색속도를 빠르게 합니다.

이런 INDEX는 TREE구조로 색인화합니다. RDBMS 에서 사용하는 INDEX는 Balance Search Tree 를 사용합니다.

실제로는 RDBMS 에서 사용되는 B-Tree 는 B-Tree 에서 파생된 B+ Tree 를 사용한다고 합니다.

 

참고로 ORACLE이나 MSSQL에서는 여러종류의 TREE를 선택하여 사용가능하다.

 

INDEX의 원리?

 

INDEX를 해당 컬럼에 주게 되면 초기 TABLE생성시 만들어진 MYD,MYI,FRM 3개의 파일중에서

MYI에 해당 컬럼을 색인화 하여 저장합니다. 물론 INDEX를 사용안할시에는 MYI파일은 비어 있습니다. 그래서 INDEX를 해당컬럼에 만들게 되면 해당컬럼을 따로 인덱싱하여 MYI 파일에 입력합니다. 그래서 사용자가 SELECT쿼리로 INDEX가 사용하는 쿼리를 사용시 해당 TABLE을 검색하는것이 아니라 빠른 TREE로 정리해둔 MYI파일의 내용을 검색합니다.

만약 INDEX를 사용하지 않은 SEELCT쿼리라면 해당 TABLE full scan하여 모두 검색합니다.

이는 책의 뒷부분에 찾아보기와 같은 의미로 정리해둔 단어중에서 원하는 단어를 찾아서 페이지수를 보고 쉽게 찾을수 있는 개념과 같습니다. 만약 이 찾아보기 없다면 처음부터 끝까지 모든 페이지를 보고 찾아야 할것입니다.

 

INDEX의 장점?

 

보통 INDEX를 사용하지 않은 select쿼리와 INDEX를 사용한 쿼리의 검색속도는 6.5배가 차이납니다.이는 데이타양이 많아질수록 더욱더 차이납니다.

 

INDEX의 단점?

 

1.디스크용량 감소

인덱스를 사용하명 MYI파일에  추가 입력되기 때문에 디스크용량이 늘어납니다.

모든컬럼을 인덱스하면 데이타파일보다 인덱스파일이더 커질수도 있습니다.

적당하게 사용해야 합니다.

인덱스를 사용해도 디스크 용량은 염려할 정도로 많이 안먹으며 그에 비해 대부분의 경우는 퍼포먼스의 향상을 가져오게 되므로 좋은점이 더 많습니다.

 

2.INSERT,UPDATE속도 저하

해당 TABLE에 INDEX을 주게되면 INSERT,UPDATE가 조금 느려집니다.

왜냐하면 매번 해당 table과 table의 index를 검사해야 하기때문에 해당 table만 검사했을때보다

느리다.

 

인덱스 삭제

 

drop index 컬럼명 on 테이블명;

 

INDEX의 목적?

 

RDBMS에는 INDEX가 있습니다. 인덱스의 목적은 해당 RDBMS의 검색 속도를 높이는데 있습니다.

SELECT 쿼리의 WHERE절이나 JOIN 예약어를 사용했을때만 인덱스를 사용되며 SELECT 쿼리의 검색 속도를 빠르게 하는데 목적을 두고 있습니다.

※ DELETE,INSERT,UPDATE쿼리에는 해당 사항없으며 INDEX사용시 좀 느려집니다.

INDEX 를 사용해야 하는 경우

데이터 양이 많고 검색이 변경보다 빈번한 경우
인덱스를 걸고자 하는 필드의 값이 다양할 값을 가질 경우
(성별과 같이 데이터의 값의 종류가 일정한 경우 인덱스 효과 없음)

INDEX 사용시 조심할점

Index가 가해지는 필드는 가능한 Null값이 없어야 한다.

 

한 테이블에 5개 이상의 인덱스 적용은 권장하지 않는다.

(이화식 씨의 대용량 데이터베이스 설계 중에서)

 

인덱스를 사용한 필드를 조건에서 연산,가공하여 사용하면 인덱스효과는 없다.
ex : Select * from 테이블 where 인덱스필드 * 10 > 100
–> Select * from 테이블 where 인덱스필드  > 100 / 10 으로 사용

 

INDEX가 동작하지 않는 경우

 

다음 연산자는 인덱스를 타지 않는다.

not,<> 는 인덱스 사용못함(= >= <= 는 사용가능)

like ‘%value’ 와 like ‘%value%’는 인덱스 사용못함(like ‘like%’는 사용가능)

조건 컬럼을 가공하거나 연산을 하면 인덱스를 사용 못합니다.

문자열 타입에 인덱스를 걸경우 150 바이트 이하까지만 인덱스가 적용됩니다.

 

INDEX의 적용?

 

TABLE 생성과 동시에 해당 컬럼에 INDEX 주기

 

CREATE TABLE member (
idx int not null auto_increment primary key,
name varchar(10),
age varchar(10),

INDEX index1 (name),
INDEX index2 (age)

);

 

CREATE TABLE member (
idx int not null auto_increment primary key,
name varchar(10),
age varchar(10),

INDEX index1 (name,age)
);

 

설명:맨뒤에 INDEX index (name) 라고 주며 INDEX는 index생성을 말하며

index1(name)은 index명을 index1이라고 하며 사용자가 임의로 결정해 줍니다.

name컬럼에 준다는 애기입니다.

ex)

레코드가 100개 있을때 explain select * from member where name=’13′; 을 하면

rows가 1이 나온다.만약 index을 사용안하면 100이 나온다. 이는 검색을 위해

100개의 레코드를 검사했다는 애기입니다 rows가 1인것은 레코드를 1개 검사

했다는 애기입니다.

 

TABLE이 생성되어있는경우 컬럼에 INDEX 삽입하기

 

alter table member add index index1(name);

 

TABLE이 생성되어있는 경우 INDEX 삭제하기

show keys from table_name;
– 현재 table_name 테이블의 인덱스상태 상태확인

ALTER TABLE table_name drop INDEX key_name;

– 현재 table_name 테이블의 key_name 인덱스키를 삭제
– 쿼리에서 사용하지 않는 인덱스는 삭제하는게 좋다.
– ex : ALTER TABLE g4_write_dr_tv_ta drop INDEX wr_num_2;

desc member1;
–key컬럼을 보고 MUL(index지정) 삭제되었나 확인

INDEX의 주의점?

 

1.인덱스가 해당 컬럼에 줄때는 반드시 not null이어야 한다.

 

INDEX의 사용(varchar)?

 

CREATE TABLE member1(
idx int not null auto_increment primary key,
name varchar(10),
age int,
INDEX index1 (name),
INDEX index2 (age)
);

 

insert into member1 (name,age) values (’11′,11);
insert into member1 (name,age) values (’12′,12);
insert into member1 (name,age) values (’13′,13);
insert into member1 (name,age) values (’14′,14);
CREATE TABLE member2(
idx int not null auto_increment primary key,
name varchar(10),
age int,
INDEX index1 (name,age)
);

 

insert into member2 (name,age) values (’11′,11);
insert into member2 (name,age) values (’12′,12);
insert into member2 (name,age) values (’13′,13);
insert into member2 (name,age) values (’14′,14);

 

CREATE TABLE member3(
idx int not null auto_increment primary key,
name varchar(10),
age int
);

 

insert into member3 (name,age) values (’11′,11);
insert into member3 (name,age) values (’12′,12);
insert into member3 (name,age) values (’13′,13);
insert into member3 (name,age) values (’14′,14);

 

explain select * from member1 where name =’13′;
yes
explain select * from member2 where name =’13′;
yes
explain select * from member3 where name =’13′;
no

 

explain select * from member1 where age >10;
yes

explain select * from member2 where age >10;

no
explain select * from member3 where age >10;
no

설명 : 위와같이 age가 설정되면 index로 설정되면 age의 순서에 따라 정렬된 index를 가지기 때문에 레코드수의 증가와 거의 관계없이  query시간이 일정합니다. 그리고 당연히 order by age와 같이 정렬이 필요한 경우에도 따로 sort해줄 필요 없기 때문에 퀘리 시간이 짧아집니다.

 

explain select * from member1 where name =’13′ and age =13;
yes
explain select * from member2 where name =’13′ and age =13;
yes
explain select * from member3 where name =’13′ and age =13;
no

 

explain select * from member1 where age =13;
yes
explain select * from member2 where age =13;
no

이유 : INDEX index1 (name,age)로 선언된 인덱스는 name,age의 값이 concate된 값이

index로 작용합니다.name이란 필드하나는 index의 역활을 하지 못합니다.
explain select * from member3 where age =13;
no

 

explain select * from member1 order by age;
no
explain select * from member2 order by age;
no
explain select * from member3 order by age;
no

 

explain select * from member1 where name=’12′ order by age;
yes
explain select * from member2 where name=’12′ order by age;
yes
explain select * from member3 where name=’12′ order by age;
no

 

explain select * from member1 where name like ’12%’ order by age;
yes
explain select * from member2 where name like ’12%’ order by age;
yes
explain select * from member3 where name like ’12%’ order by age;
no

 

explain select * from member1 where name like ‘%12′ order by age;
explain select * from member1 where name like ‘%12%’ order by age;
no
explain select * from member2 where name like ‘%12′ order by age;
explain select * from member2 where name like ‘%12%’ order by age;
no
explain select * from member3 where name like ‘%12′ order by age;
explain select * from member3 where name like ‘%12%’ order by age;
no

 

explain select * from member1 where  name like ’12%’ or age =12 order by age;
no
explain select * from member2 where  name like ’12%’ or age =12 order by age;
no
explain select * from member3 where  name like ’12%’ or age =12 order by age;
no

 

INDEX의 사용(int,char,date)?

 

create table member(
idx int auto_increment primary key,
name char(20),
age int,
day date,
index index1(name),
index index2(age),
index index3(day)
);

 

insert into member values(null,’junsik1′,11,date_add(now(),interval 1 day));

에서 차례대로
insert into member values(null,’junsik8′,18,date_add(now(),interval 8 day));
까지 입력한다.

 

explain select * from member where day between ’2005-07-19′ and ’2005-07-23′;
index를 타지만 풀스캔을 한다.

 

explain select * from member where day = ’2005/07/17′
yes

 

explain select * from member where name in(‘junsik1′,’junsik2′);
yes

 

explain select * from member where age in(’12′,’13′,’14′);
no

 

explain select * from member where age between 12 and 14;
yes

 

explain select * from member where age < 14;
yes

인덱스는 where절이 아닌 order by절에도 줘야 한다.
select * from jun_table where wr_is_comment = 0  order by wr_num, wr_reply   limit 0, 30;
위의 쿼리가 느릴때는 wr_is_comment 컬럼만이 아닌 복합인덱스로 wr_is_comment, wr_num, wr_reply 컬럼을 주면 효과적이다.

 

 

 

 

 

kth 데이터지능팀 성동찬

안녕하세요. 오늘은 MySQL을 사용할 때 지켜야할 사항 몇 가지 정리합니다.

나름 혼자서 정리를 해 본 것들인데, MySQL로 서비스를 준비 중이라면 한 번쯤은 고려를 해봤으면 하는 내용입니다.^^

테이블 설계 시 유의 사항

1. 반드시 Primary Key를 정의하고 최대한 작은 데이터 타입을 선정한다.

  • 로그 성 테이블에도 기본적으로 PK 생성을 원칙으로 함
  • InnoDB에서 PK는 인덱스와 밀접한 관계를 가지므로 최대한 작은 데이터 타입을 가지도록 유지

2. 테이블 Primary Key는 auto_increment를 사용한다.

  • InnoDB에서는 기본 키 순서로 데이터가 저장되므로, Random PK 저장 시 불필요한 DISK I/O가 발생 가능
  • InnoDB의 PK는 절대 갱신되지 않도록 유지
    (갱신 시 갱신된 행 이후 데이터를 하나씩 새 위치로 옮겨야 함)

3. 데이터 타입은 최대한 작게 설계한다.

  • 시간정보는 MySQL데이터 타입 date/datetime/timestamp 활용
  • IP는 INET_ATON(‘IP’), INET_NTOA(int) 함수를 활용
  • 정수 타입으로 저장 가능한 문자열 패턴은 최대한 정수 타입으로 저장

4. 테이블 내 모든 필드에 NOT NULL 속성을 추가한다.

  • NULL을 유지를 위한 추가 비용 발생
    (NULL 허용 칼럼을 인덱싱 할 때 항목마다 한 바이트 씩 더 소요)

5. Partitioning을 적절하게 고려하여 데이터를 물리적으로 구분한다.

  • 데이터 및 인덱스 파일이 커질수록 성능이 저하되므로Partitioning 유도
  • PK 존재 시 PK 내부에 반드시 Partitioning 조건이 포함되어야 함

인덱스 설계 시 유의 사항

1. 인덱스 개수를 최소화 한다.

  • 현재 인덱스로 Range Scan이 가능한지 여부를 사전에 체크
  • 인덱스도 서버 자원을 소모하는 자료구조이므로 성능에 영향을 줌

2. 인덱스 칼럼은 분포도를 고려하여 선정한다.

  • 인덱스 칼럼 데이터의 중복이 줄어들수록 인덱스는 최대의 효과를 가짐
  • 하단 쿼리 결과 값이 1에 가까울수록(0.9이상 권고) 인덱스 컬럼으로 적합함
    1
2
    SELECT count(distinct INDEX_COLUMN)/count(*)
FROM TABLE;

3. 커버링 인덱스(Covering Index)를 활용한다.

4. 스토리지 엔진 별 INDEX 특성을 정확히 인지한다.

  • InnoDB에서 데이터는 PK 순서로 저장되고, 인덱스는 PK를 Value로 가짐
  • MyISAM은 PK와 일반 인덱스의 구조는 동일하나, Prefix 압축 인덱스를 사용
    (MyISAM 엔진에서 ORDER BY 시 DESC는 가급적 지양)

5. 문자열을 인덱싱 시 Prefix 인덱스 활용한다.

  • 긴 문자열 경우 Prefix 인덱스(앞 자리 몇 글자만 인덱싱)를 적용
    1
    CREATE INDEX IDX01 ON TAB1(COL(4), COL(4))
  • Prifix Size는 앞 글자 분포도에 따라 적절하게 설정
    (하단 결과가 1에 가까울 수록 최적의 성능 유지, 0.9이상 권고)

     

    1
2
    SELECT count(distinct LEFT(INDEX_COLUMN,3))/count(*)
FROM TABLE;

6. CRC32함수 및 Trigger를 활용하여 인덱스 생성한다.

  • URL/Email같이 문자 길이기 긴 경우 유용
  • INSERT/UPDATE 발생 시 Trigger로 CRC32 함수 실행 결과 값을 인덱싱
  • CRC32 결과값을 저장할 칼럼 추가 및 인덱스 생성
    1
2
    alter table user_tbl add email_crc int unsigned not null;
create index idx01_email_crc on user_tbl (email_crc);
  • Insert Trigger 생성
    1
2
3
4
5
6
    create trigger trg_user_tbl_insert
before insert on user_tbl
for each row
begin
set new.email_crc = crc32(lower(trim(new.email)));
end$$
  • Update Trigger 생성
    1
2
3
4
5
6
7
8
    create trigger trg_user_tbl_update
before update on user_tbl
for each row
begin
if old.email<> new.email then
set new.email_crc = crc32(lower(trim(new.email)));
end if;
end$$
  • 검색 쿼리
    1
2
3
4
    select *
from user_tbl
where email_crc = crc32(lower(trim('mail@domain.com')))
and email= 'mail@domain.com'

    CRC32 결과가 중복되어도, email값을 직접 비교하는 부분에서 중복이 제거됩니다.

7. 중복 인덱스 생성 회피

  • MySQL은 동일한 인덱스를 중복 생성해도 에러를 발생하지 않음
  • Primary Key로 구성된 칼럼과 동일한 인덱스를 생성하지 않도록 주의

 

 

 

 

[mysql 초기 설치 및 설정]

Linux에서 가장 많이 쓰이는 데이터베이스 서버는 MySQL이다. CentOS 패키지에 포함되어 있음로 설치하는 방법은 간단하다.

yum install mysql-server

MySQL 실행하는 명령은 다음과 같다.

service mysqld start

설치 후 가장 먼저 할 것은 root 사용자의 비밀번호 설정이다. 다음과 같이 명령하여 MySQL에 접속한다.

mysql -u root -p mysql

[Enter password:]라고 나오는 데 비밀번호 설정이 안 되어 있으므로 그냥 엔터를 친다.

다음과 같이 명령하여 root의 비밀번호를 만들어준다. (new-password를 원하는 비밀번호로 바꾼다.)

update user set password=password(‘new-password’) where user=’root’;

권한 테이블을 업데이트 한다.

flush privileges;

MySQL에서 나온다.

quit;

MySQL 작업은 명령어로 할 수도 있고, phpMyAdmin 같은 도구를 이용하여 할 수도 있다.

 

 

MySQL 사용하기

MySQL에서 root 암호 설정

mysqladmin을 이용하여 root 암호 설정

MySQL을 처음 설치하면 root 암호가 설정되어 있지 않기 때문에 반드시 설치 후 바로 root 암호를 먼저 설정해주세요.%%% root 암호를 설정하기 위해서는

 $ mysqladmin -u root -p password new-password

이러한 명령을 실행하면 됩니다. 실행하면 패스워드를 입력하라고 하는데 처음 root의 패스워드는 없으므로 그냥 엔터를 치시면 root 패스워드가 변경됩니다.dddd

update문을 이용하여 root 암호 설정

 $ mysql -u root mysql

 mysql> update user set password = password('new-password') where user = 'root';
 mysql> flush privileges;

set password를 이용하여 root 암호 설정

 mysql> set password for root = password('new-password');

마지막으로 패스워드가 재대로 변경되었는지 테스트를 합니다.

 $ mysql -u root -p

MySQL에서 Database 보기와 생성

데이터베이스 보기

 myslq> show databases;

데이터베이스 생성

 mysql> create database DB명;

MySQL에서 새로운 사용자 추가

MySQL에서 사용할 Database를 만든 후에 사용자를 추가하려면, 일단 root로 접속한 후

 $ mysql (?h localhost) ?u root ?p
 Enter password: *******

 mysql> use mysql
 mysql> INSERT INTO user VALUES('%', '사용자', PASSWORD('비밀번호'),
     -> 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');

이렇게 해서 사용자를 하나 만들었습니다. 맨 처음 ‘%’ 가 붙은 것은 다른 어느 호스트에서도 접속이 가능하게 하기 위해서 입니다. 간단하게 추가 하는 방법이 있습니다.

‘GRANT’ 명령을 이용하면 쉽게 사용자 추가 및 권한 설정이 가능합니다. (MySQL Manual 4.3.5)

  • 일반 사용자 추가
 mysql> grant all privileges on dbuser.* to dbuser@localhost identified by 'password' with grant option;
  • 특정 이름의 데이터베이스에 대한 모든 권한을 가지는 사용자 추가
 mysql> grant all privileges on `dbuser_%`.* to dbuser@localhost identified by 'password' with grant option;

‘dbuser_’으로 시작되는 데이터베이스에 대한 모든 권한을 가지는 ‘dbuser’ 사용자 계정 추가%%% 이런식으로 계정을 만들면 새로운 사용자에 대한 개별적인 데이터베이스 권한 설정을 생략할 수 있습니다. — 이현진

사용자에게 Database 사용 권한을 주기

 mysql> INSERT INTO db (Host, Db, User, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv)
     -> VALUES('%', 'DB명', '사용자', 'Y', 'Y', 'Y', 'Y','Y','Y');

모든 권한을 준다면 아래와 같은 방법을 하는것이 더욱 편리힙니다.

 mysql> GRANT ALL on DB명.* TO id@'localhost'  ;
 mysql> GRANT ALL on DB명.* TO id;
 mysql> GRANT ALL on DB명.* TO id@'xxx.xxx.xxx.%';

첫번째 localhost?로컬 호스트 (DB가 자기컴퓨터에 깔려 있는경우..에만 접속을 허용하겠다는 뜻입니다.

두번째 문장은 로컬 호스트를 제외하고는 모든 ip에서 접근을 시키겠다는 내용입니다.

세번째는 c클래스만 허용하겠다는 의미 입니다. xxx.xxx.%.% 면 B클래스 xxx.%.%.%면 A클래스로 제한을 할수 있습니다.

그리고 변경된 내용을 메모리에 반영하기 위해 다음 명령을 실행합니다.

 mysql> FLUSH PRIVILEGES;

이렇게 MySQL을 설정해 놓으면 사용자가 다른 호스트에서도 접속할 수 있고 테이블 추가 삭제 등도 할 수 있습니다.

이렇게 수동으로도 가능 하지만 요즘은 툴을 많이 사용합니다. 툴은 DB에 대한 기본적인 지식만 있으면 무리가 없이 사용 되는 녀석이죠 ^^%%% 저같이 쿼리에 약한 사람들에게는 정말 유리 합니다. 아래 페이지에 가셔서 다운로드를 받으세요  한글 사용에 약간의 제약이 있습니다.%%% 다른 여러가지 DB관리 툴이 있지만 상당히 고가의 툴이랍니다  암흑의 루트에서 돌고 있는 녀석들이 종종 있습니다.

http://www.mysql.com/downloads/mysqlcc.html

 

 

 

MySQL DBMS 를 설치할 때 제가 적용하는 내용을 공유합니다.
root 계정으로 설치 준비를 하고, mysql 계정으로 DB를 구동합니다.
일단 하단 내용들은 root계정으로 수행을 합니다. 

OS 계정 추가

다음과 같이 dba 그룹을 추가하고 그 밑에 mysql 계정을 추가합니다.

1
2
3
groupadd -g 600 dba
useradd -g 600 -u 605 mysql
passwd mysql

Linux 설정 변경

세션 Limit 를 설정합니다.

1
2
3
4
5
6
vi /etc/security/limits.conf
##하단 내용 추가
mysql            soft    nproc  8192
mysql            hard    nproc  16384
mysql            soft    nofile 8192
mysql            hard    nofile 65536

OS에서 limits.conf 파일을 읽어들이도록 설정합니다. 없으면 생성합니다.

1
2
3
vi /etc/pam.d/login
## 하단 내용 추가
session    required     pam_limits.so

/etc/profile 에 다음 내용을 추가하여 login 시 적용되도록 합니다.

1
2
3
4
5
6
7
8
9
10
vi /etc/profile
##
if [ $USER = "mysql" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi

MySQL 데이터 저장 디렉토리를 생성합니다.

1
2
3
4
mkdir -p /data/mysql/mysql-data
mkdir -p /data/mysql/mysql-tmp
mkdir -p /data/mysql/mysql-iblog
mkdir -p /data/mysql/mysql-binlog

MySQL 설치 파일 다운로드

하단 실행 시 x86_64 가 있으면 64비트이고, i686 이 있으면 32비트입니다.

1
2
3
## OS 버전 확인 ##
uname -a
Linux ..중략.. EDT 2010 x86_64 x86_64 x86_64 GNU/Linux

이제 MySQL Download 의 “Linux – Generic” 탭에서 자신의 OS에 맞는 MySQL Server 받으세요. 현재 Release되는 주 버전은 MySQL 5.5.x이나, 여기서는 MySQL 5.1.57 64비트 버전으로 설명드리겠습니다.
굴욕적이지만, 한국보다는 일본 mirror서버에서 받는 것이 빠르다는..-_-;;

1
2
3
cd /usr/local/
## 설치 파일 다운로드
wget http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.19-linux2.6-x86_64.tar.gz/from/http://ftp.iij.ad.jp/pub/db/mysql/

MySQL 기본 설정

시스템에 따라 데이터 파일과 같은 일부 변수 값이 달라질 수 있으니, 자신의 시스템에 맞게 수정해서 사용하세요.

1
vi /etc/my.cnf
[client]
port = 3306
socket = /tmp/mysql.sock

[mysqld]
# generic configuration options
port = 3306
socket = /tmp/mysql.sock

back_log = 100
max_connections = 500
max_connect_errors = 10
table_open_cache = 2048
max_allowed_packet = 16M
join_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
sort_buffer_size = 8M
bulk_insert_buffer_size = 16M
thread_cache_size = 128
thread_concurrency = 16
query_cache_type = 0
default_storage_engine = innodb
thread_stack = 192K
lower_case_table_names = 1
max_heap_table_size = 128M
tmp_table_size = 128M
local_infile = 0
max_prepared_stmt_count = 256K
event_scheduler = ON
log_bin_trust_function_creators = 1
secure_auth = 1
skip_external_locking
skip_symbolic_links
#skip_name_resolve

## config server and data path
basedir = /usr/local/mysql
datadir = /data/mysql/mysql-data
tmpdir = /data/mysql/mysql-tmp
log_bin = /data/mysql/mysql-binlog/mysql-bin
relay_log = /data/mysql/mysql-binlog/mysql-relay
innodb_data_home_dir = /data/mysql/mysql-data
innodb_log_group_home_dir = /data/mysql/mysql-iblog

## config character set
##utf8
character_set_client_handshake = FALSE
character_set_server = utf8
collation_server = utf8_general_ci
init_connect = "SET collation_connection = utf8_general_ci"
init_connect = "SET NAMES utf8"

## bin log
binlog_format = row
binlog_cache_size = 4M

## Replication related settings
server_id = 1
expire_logs_days = 7
slave_net_timeout = 60
log_slave_updates
#read_only

## MyISAM Specific options
key_buffer_size = 32M
myisam_sort_buffer_size = 8M
myisam_max_sort_file_size = 16M
myisam_repair_threads = 1
myisam_recover = FORCE,BACKUP

# *** INNODB Specific options ***
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 2G

innodb_data_file_path = ibdata1:1G:autoextend
innodb_file_per_table = 1
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 8M
innodb_log_file_size = 128M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_flush_method = O_DIRECT
innodb_lock_wait_timeout = 120
innodb_support_xa = 0
innodb_file_io_threads = 8

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no_auto_rehash

[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[mysqlhotcopy]
interactive_timeout

[mysqld_safe]
open_files_limit = 8192

MySQL Server 설치

1
2
3
4
5
6
7
8
9
10
11
12
13
## 압축 해제
cd /usr/local
tar xzvf mysql-5.5.19-linux2.6-x86_64.tar.gz
## 관리를 위한 심볼릭 링크 생성
ln -s mysql-5.5.19-linux2.6-x86_64 mysql
## 설치 파일 권한 변경
chown -R mysql.dba /usr/local/mysql*
## 시작 스크립트 복사
cp mysql/support-files/mysql.server /etc/init.d/mysqld
## 관련 파일 권한 설정
chown mysql.dba /data/mysql/*
chown mysql.dba /etc/my.cnf
chown mysql.dba /usr/local/mysql*

여기서부터는 이제 mysql 계정으로 실행을 합니다.
관리를 위해서 몇몇 alias를 설정하는 부분입니다.^^

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
su - mysql
cat >> ~/.bash_profile
## 하단 내용 입력
export MYSQL_HOME=/usr/local/mysql
export PATH=$PATH:$MYSQL_HOME/bin:.
export ADMIN_PWD="ROOT 패스워드"

alias ll="ls -al --color=auto"
alias mydba="mysql -uroot -p$ADMIN_PWD"
alias mymaster="mysql -uroot -p$ADMIN_PWD -e'show master status;'"
alias myslave="mysql -uroot -p$ADMIN_PWD -e'show slave statusG'"
alias mh="cd $MYSQL_HOME"
alias md="cd /data/mysql/mysql-data"
alias mt="cd /data/mysql/mysql-tmp"
alias mb="cd /data/mysql/mysql-binlog"
alias mi="cd /data/mysql/mysql-data"
alias dp="cd /data/mysql/mysql-data"

## 환경 변수 적용
. ~/.bash_profile

MySQL Server 구동

1
2
3
4
5
cd /usr/local/mysql
## 기본 데이터베이스 설치
./scripts/mysql_install_db
## MySQL 데몬 Startup
/etc/init.d/mysqld start

MySQL 보안 설정

처음 DB를 올리면 보안 면에서 취약한 부분이 있습니다.
기본적인 보안 정책을 적용하도록 합니다.
mysql root  계정 패스워드만 설정하고 나머지는 Enter만 쭉 치면 됩니다.

1
2
cd /usr/local/mysql
./bin/mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!

In order to log into MySQL to secure it, we'll need the current
password for the root user. If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

Change the root password? [Y/n]
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!

By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n]
... Success!

Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n]
... Success!

By default, MySQL comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n]
- Dropping test database...
ERROR 1008 (HY000) at line 1: Can't drop database 'test'; database doesn't exist
... Failed! Not critical, keep moving...
- Removing privileges on test database...
... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n]
... Success!

Cleaning up...

All done! If you've completed all of the above steps, your MySQL
installation should now be secure.

이제 MySQL DB 설치가 다 끝났습니다. 

728x90
저작자표시

'MYSQL' 카테고리의 다른 글

DMV를 활용한 병목 점검 예제  (0) 2015.12.20
ms-sql에서 mysql 마이그레이션 후 문제발생 및 처리  (0) 2015.12.20
파라메터를 사용한 동적쿼리 프로시저 예제  (0) 2015.12.20
MySQL 명령어 정리  (0) 2015.12.20
MySql 데이터 타입  (0) 2015.12.20
'MYSQL' Related Articles more