음머어's 까망별 ::

'MySQL'에 해당되는 글 24건

2009/09/22
[MySQL] 인덱스 그대로 다시 만들기.
2009/05/29
[MySQL] ERROR 1201 (1)
2009/05/13
[MySQL] 다른 사용자의 procedure 보기 (3)
2009/03/25
[MySQL] MySQL 5.1 이상 버전의 Federated Engine Enable (2)
2009/01/09
[MySQL] ERROR 1005 (errno: 150) (6)
2008/12/29
[MySQL] 시스템 변수 date_format, datetime_format (2)
2008/12/26
[MySQL] 잊고 있었던 문자열 함수들
2008/12/22
[MySQL] INT(11) 에서의 (11)의 의미를 아세요? (6)
2008/12/22
[MySQL] Oracle의 그룹별 번호 매기기와 같은 기능 구현하기 (5)
2008/11/25
[MySQL] NULL은 INDEX를 탈까? (2)

[MySQL] 인덱스 그대로 다시 만들기.

MySQL 2009/09/22 15:33

현재 테이블은 드랍해야겠고, 인덱스는 그대로 생성해야겠는데,
어떤 방법이 있을깡?

1. CREATE TABLE test.testtbl LIKE test.testtbl;
-> 이 방법은 동일한 구조의 테이블을 만들어 준다.

2. 기존 인덱스 생성쿼리만 뽑아오기
-> 이게 왜 필요하냐면, 프로그램에 의해 테이블의 드랍/생성이 발생할 경우
    인덱스를 그대로 가져오려면 하드코딩할 수 밖에 없는데,
    그럴 경우 인덱스를 별도로 생성하는 작업을 수행하기 위해 필요하다.

    뭐...... 좀더 심도있는 이유는... 귀찮아서? -_-;;

SELECT CONCAT_WS('', 'alter table ',
      table_name,
      IF( index_name ='PRIMARY',
       ' add primary key',
       IF( non_unique =1,
        CONCAT_WS('',' add index ', index_name ),
        CONCAT_WS('',' add unique index ', index_name )
       )
       ),
      '(',
      GROUP_CONCAT(column_name ORDER BY seq_in_index SEPARATOR ','),
      ');' ) AS idxqry
FROM information_schema.statistics
WHERE table_schema = 'test' -- table_name = 'testtbl'
GROUP BY table_name, index_name;

Posted by 까망별 음머어

TRACKBACK 0 AND COMMENT 0

[MySQL] ERROR 1201

MySQL 2009/05/29 15:36

평소 Master 장비로 사용하던 서버를, Replication Slave 로 바꿔서 사용하고자 했을 때,
my.cnf 를 변경하고도 아래와 같은 에러 메세지가 발생하였다.

## 에러메세지 #################################################################################
ERROR 1201 (HY000) at line 22: Could not initialize master info structure; more error messages can be found in the MySQL error log
################################################################################################

흠....
"왜 그럴까? 그럼 리셋을 해보자." 라는 마음으로

mysql > master reset; 을 수행하였다. -> 땡!! 안 돼더라...
mysql > slave reset; 을 수행하였다. -> 딩동댕.... 됀다~~~!!!!

슬레이브 셋팅을 초기화 하니깐 해결.......

원인 분석은..... 지금 바쁘니깐, 패스~ 하고 나중에.... 할 수 있으려나 -0-?

참고 링크: http://forums.mysql.com/read.php?26,259192,260483#msg-260483

Posted by 까망별 음머어

ERROR 1201, MySQL, 리플리케이션 슬레이브

TRACKBACK 0 AND COMMENT 1

[MySQL] 다른 사용자의 procedure 보기

MySQL 2009/05/13 19:47

간만에 글을 적는군 에헴....... 너무 놀았나 --;;

사용자에게 DB별로 권한을 부여하여 줄 경우,
분명히 프로시저를 관리할 수 있는

CREATE ROUTINE,

ALTER ROUTINE,

EXECUTE

이 있음에도 다른 사람의 프로시저를 열람할 수 없는 경우가 있다.

이와 관련된 리퍼런스 링크는 아래와 같으며,
http://dev.mysql.com/doc/refman/5.0/en/show-create-procedure.html

결론부터 말하자면,
mysql DB의 proc 테이블에 대한 SELECT 권한을 추가 부여해 주면 된다.

GRANT SELECT ON mysql.proc TO 사용자@아이피;

아잣! 이제부터 정리좀 다시 하면서 살아가자!!!

Posted by 까망별 음머어

mysql procedure null, mysql 프로시저, 다른 사람의 프로시저 열람

TRACKBACK 0 AND COMMENT 3

[MySQL] MySQL 5.1 이상 버전의 Federated Engine Enable

MySQL 2009/03/25 17:04

머리가 나쁘면 3대가 고생한다고....
간단한 것을 못 찾아서 한참을 헤멘다 ㅠ,.ㅠ

MySQL 5.1버전 이상부터는 Federated Engine이 디폴트로 뜨지 않는다.
(현재 나의 MySQL 서버가 Federated Engine이 사용가능한지 여부는 SHOW ENGINES; 입력해보면 알 수 있다. )

그렇기 때문에 서버 관리자가 직접 사용가능하도록 수정해 주어야 하는데....

방법은 굉장히 간단하다.
my.cnf 또는 my.ini 내의 [mysqld] 구간에 federated라고 입력해주고,
서버를 내렸다 올리기만 하면 된다.....

<< 예시 >>

[mysqld]

# generic configuration options
port            = 3306
socket          = /tmp/mysql.sock
skip-character-set-client-handshake
character-set-filesystem = utf8
default-character-set = utf8
skip-locking
federated

참 쉽죠잉~~~

Posted by 까망별 음머어

federated engine, mysql 5.1

TRACKBACK 0 AND COMMENT 2

[MySQL] ERROR 1005 (errno: 150)

MySQL 2009/01/09 11:25

일전에 errno 1005상황에 대해 설명하면서, 아주 약간 errno: 150을 언급했었다..
******************************************************************************
LINK : [MySQL] ERROR 1005 (errno: 121) 에 대한 나의 글.
******************************************************************************

흠.. 그 당시에는, 별로 신경쓰지 않았던 errno:150.
그런데 errno:150을 직접 맞딱드리게 되니,
이넘에 대해 알아보고픈 욕구를 스믈스믈 피워올리게 만든다. --;;

이 문제를 해결하기 위해선 아래의 링크를 참조하면 좋을 듯 하다.
******************************************************************************
LINK1 : FOREIGN KEY 사용에 대한 리퍼런스(한글),
FOREIGN KEY 사용에 대한 리퍼런스(영어),

LINK2 : 에러 상황에 대한 MySQL 리퍼런스(영어)

LINK3 : FORUM 에서 이루어졌던, errno: 150 에 대한 토론(영어)
******************************************************************************

나름 위 LINK들을 요약하자면 다음과 같다.
******************************************************************************
INNODB에서의 FOREIGN KEY는
1. 반드시 아버지와 자식은 INNODB 여야 한다.
2. 아들이 참조하는 키는 아버지가 가지고 있는 키의 자료형과 동일하게 사용해야 한다.
3. 아버지가 UNIQUE한 값을 갖는 키들만, 아들이 가져가서 참조할 수 있다.
******************************************************************************

<< 에러 상황 예제>>

흠... 일단 내가 맞딱드렸던 ,에러 상황을 살펴보자.
******************************************************************************
# 아버지 테이블 : 유니크한 키값을 갖는다.
CREATE TABLE father (
id INT UNSIGNED COMMENT 'father 테이블의 유니크한 키',
UNIQUE KEY test_id_idx (id)
)ENGINE=INNODB DEFAULT CHARSET = UTF8 COMMENT='자식을 가질 아버지 테이블';

# 나쁜 아들 테이블 : 테이블 생성이 되지 않는다.

CREATE TABLE bad_son1 (
father_id INT COMMENT 'father 테이블의 id를 FK로 하는 id',
CONSTRAINT son_father_id_fk FOREIGN KEY (father_id) REFERENCES father(id)
)ENGINE=INNODB DEFAULT CHARSET = UTF8 COMMENT='아들, 이녀석은 UNSIGNED를 안 써서 errno:150을 뿌린다';

# 착한 딸 테이블 : 테이블의 정상적인 생성이 이루어진다.

CREATE TABLE good_daughter (
father_id INT UNSIGNED COMMENT 'father 테이블의 id를 FK로 하는 id',
CONSTRAINT good_daughter_father_id_fk FOREIGN KEY (father_id) REFERENCES father(id)
)ENGINE=INNODB DEFAULT CHARSET = UTF8 COMMENT='딸, 얘는 아버지를 쏙 빼다 닮아서 정상적으로 생성된다.';
******************************************************************************

<< 수행결과 >>

mysql> # 아버지 테이블 : 유니크한 키값을 갖는다.
mysql> CREATE TABLE father (
    -> id INT UNSIGNED COMMENT 'father 테이블의 유니크한 키',
    -> UNIQUE KEY test_id_idx (id)
    -> )ENGINE=INNODB DEFAULT CHARSET = UTF8 COMMENT='자식을 가질 아버지 테이블';
Query OK, 0 rows affected (0.09 sec)

mysql> # 나쁜 아들 테이블 : 테이블 생성이 되지 않는다.
mysql> CREATE TABLE bad_son1 (
    -> father_id INT COMMENT 'father 테이블의 id를 FK로 하는 id',
    -> CONSTRAINT son_father_id_fk FOREIGN KEY (father_id) REFERENCES father(id)
    -> )ENGINE=INNODB DEFAULT CHARSET = UTF8 COMMENT='아들, 이녀석은 UNSIGNED를 안 써서 errno:150을 뿌린다';
ERROR 1005 (HY000): Can't create table 'test2.bad_son1' (errno: 150)

mysql> # 착한 딸 테이블 : 테이블의 정상적인 생성이 이루어진다.
mysql> CREATE TABLE good_daughter (
    -> father_id INT UNSIGNED COMMENT 'father 테이블의 id를 FK로 하는 id',
    -> CONSTRAINT good_daughter_father_id_fk FOREIGN KEY (father_id) REFERENCES father(id)
    -> )ENGINE=INNODB DEFAULT CHARSET = UTF8 COMMENT='딸, 얘는 아버지를 쏙 빼다 닮아서 정상적으로 생성된다.';
Query OK, 0 rows affected (0.11 sec)

아들이 왜 삑사리가 났는가?
아버지와 동일한 형태의 자료형을 사용하지 않았기 때문이다.

LINK3의 맨 마지막 글과 동일한 상황이었던, 은연중에 사용했던 UNSIGNED 가 문제가 되었던 것...

<< 정리 및 해법>>
자.. 정리하자.

일단, errno:150 이 나타났다면,
그것은 FOREIGN KEY를 생성하는 구문에 문제가 있음을 의미하며,
위에 언급했던 3가지 (INNODB, 자료형, 유니크 키) 를 반드시 확인해 보도록 하자.

그 때도 해결이 안된다면, 위의 링크들을 다시 한 번 꼼꼼히 살펴보도록 한다.

Posted by 까망별 음머어

errno: 150, ERROR 1005, FOREIGN KEY CONSTRAINT, MySQL

TRACKBACK 0 AND COMMENT 6

[MySQL] 시스템 변수 date_format, datetime_format

MySQL 2008/12/29 16:00

MySQL의 변수를 살펴보다, 유용하다 싶은 것을 하나 발견했다.

!!주의!! : 여기서 말하는 date_format, datetime_format는 함수가 아니라 변수가 주제입니다.
함수는 따로 있습니다. ^^;;

아래 글을 읽지 않으실 분께...
결론 : date_format, datetime_format 변수는 사용하실 수 없는 변수입니다..

| log_slave_updates                  | OFF               |
| low_priority_updates               | OFF               |
| query_cache_wlock_invalidate | OFF               |
| sql_log_update                     | ON                |
| sql_low_priority_updates          | OFF              |
| sql_safe_updates                    | OFF              |
+------------------------------+--------------+
8 rows in set (0.02 sec)

바로 date_format, datetime_format 이었는데,
직관적으로 살펴봐도 웬지.... 내맘대로 날짜형을 표현해 줄 것 같은 느낌이 들었다...

날짜를 내맘대로 표현할 수 있다는 것은,
일반적으로 사용하는 스트링형태의 날짜 형태 (%Y%m%d%H%i%s : 200812291550) 로
표현해 줄 수 있다는 것이며,
이로 인해 날짜를 스트링으로 변환하여 저장하고자 하는 개발자들의 욕구를 충족시켜줄 수 있음을 의미한다.

그래서, 바로 변수의 출력 방식을 적용하기 시작했다.

mysql> SET GLOBAL datetime_format ='%Y%m%d%H%i%s';
Query OK, 0 rows affected (0.00 sec)

그런데.. 웬걸... 안 된다...
왜 안되지... 내가 뭘 잘못했나... 동적 변수가 아닌가...
서버를 내렸다 올려볼까?..... 등등을 생각해보며, 별 희안한 쇼를 다 해본다...
그래도 안 된다.

그래서 리퍼런스를 찾아봤다.
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_datetime_format

어잌후!!! 이런...
This variables is unused 란다....
맹글기는 했지만 구현은 안 되었다는 거지.....

우띠... 시간 아깝게 ㅠ,.ㅠ

암튼 빛좋은 개살구 하나를 발견했다.
다른 분들은 나처럼 삽질하지 않으시기를 .....

Posted by 까망별 음머어

datetime_format, date_format, MySQL, 시스템변수

TRACKBACK 0 AND COMMENT 2

[MySQL] 잊고 있었던 문자열 함수들

MySQL 2008/12/26 18:30

그 동안 알고는 있었지만 정작 써먹지 않았던,
문자열 함수 몇개를 적고 넘어갈까 한다.

다년간에 걸친 니코틴과 카페인 섭취로 인하여 뇌기능이 비약적으로 떨어진 것을
메모를 통해 강제 각인 시키고자 한다 할까? (-_-);;

# 아래는 MySQL 한글 레펀런스의 문자열 함수 페이지를 링크해 둔 것이다.
<<Link :MySQL 한글 레퍼런스 - 스트링함수>>

# 함수 설명

1. LEFT, RIGHT
이눔의 함수들은 문자열의 오른쪽, 왼쪽을 끊어 오는 기능을 한다.

<<기억 안 났을 때>>
이걸 기억 못해서,
맨날 LEFT는 SUBSTRING 함수를 써서 일일이 끊어 왔고,
RIGHT는 LENGTH함수를 써서 그만큼 SUBSTRING으로 끊어오거나, REVERSE를 사용해서 비교했었다..
(ㅠ,.ㅠ)

<<사용방법>>
LEFT(문자열, 끊어올 문자 개수)
RIGHT(문자열, 끊어올 문자 개수)

<<실행화면>>

mysql> SELECT LEFT('1234567890', 5);
+-----------------------+
| LEFT('1234567890', 5) |
+-----------------------+
| 12345 |
+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT RIGHT('1234567890', 5);
+------------------------+
| RIGHT('1234567890', 5) |
+------------------------+
| 67890 |
+------------------------+
1 row in set (0.00 sec)

2. SUBSTRING_INDEX
찾고자 하는 문자열의 이전 문자열 전부를 끊어 온다.
요눔의 매력 포인트는, 찾고자 하는 문자열의 순번을 입력할 수 있다는 것인데,
이것은 몇번째 놈 이전의 데이터를 가져올 것이냐 하는 것을 결정할 수 있음을 의미한다.

<<기억 안 났을 때>>
나는 주로,
LOCATE함수로 포지션을 찾아서 다시 SUBSTRING 함수로 이를 다시 끊어오는 방법을 사용했었는데..
내가 .. 미쳤었나봅니다~ (ㅠ,.ㅠ);;

<<사용방법>>
SUBSTRING_INDEX(문자열, 찾고자 하는 문자열, 찾고자 하는 문자열의 순번)

<<실행화면>>

mysql> SELECT SUBSTRING_INDEX('12345674890', '4',1);
+---------------------------------------+
| SUBSTRING_INDEX('12345674890', '4',1) |
+---------------------------------------+
| 123 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING_INDEX('12345674890', '4',2);
+---------------------------------------+
| SUBSTRING_INDEX('12345674890', '4',2) |
+---------------------------------------+
| 1234567 |
+---------------------------------------+
1 row in set (0.00 sec)

3. TRIM
이거 앞뒤 공백 문자만 지워주는 녀석인줄 알았는데,
(※주의 : 문자열 중간에 끼어있는 공백은 못 지운다. -0-;;)
의외로 다른 문자도 지워주는 쎈스가 있는 녀석이다....

문자열의 앞뒤에 특정 문자패턴이 반복적으로 입력되어 있다면,
그 녀석을 날려버릴 수가 있다.

<<기억 안 났을 때>>
대표적인 예가 ZEROFILL된 숫자가 문자열화 된 것이 있는데,
이럴 경우 나는 REPLACE해주는 방식을 사용했더랬지....

문제는 REPLACE의 경우 왼쪽에서, 또는 오른쪽에서 선택적인 사용을 할 수 없기 때문에,
만약 뒤에 0이 있다면 대략난감 (-_-);;;;

아.. 그리고 자매품 RTRIM, LTRIM도 있지만,
이것들은 공백만 제거하므로 주의해서 사용할 것 -0-/

<<사용방법>>
TRIM({LEADING | TRAILING | BOTH} 바꾸고자 하는 문자열 FROM 문자열)
BOTH : 좌우로 모두 제거
LEADING : 좌측만 제거
TRAILING : 우측만 제거

<<실행화면>>

mysql> SELECT TRIM(BOTH '0' FROM '0001200034000' );
+--------------------------------------+
| TRIM(BOTH '0' FROM '0001200034000' ) |
+--------------------------------------+
| 1200034 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT TRIM(LEADING'0' FROM '0001200034000' );
+----------------------------------------+
| TRIM(LEADING'0' FROM '0001200034000' ) |
+----------------------------------------+
| 1200034000 |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT TRIM(TRAILING '0' FROM '0001200034000' );
+------------------------------------------+
| TRIM(TRAILING '0' FROM '0001200034000' ) |
+------------------------------------------+
| 0001200034 |
+------------------------------------------+
1 row in set (0.00 sec)

머리가 나쁘면 몸이 피곤하고,
머리를 안 쓰면 삼대가 고생하며,
머리를 안 감으면 냄새가 난다(?) <-- 모여 이거 (-_-);;

고로, 아는 것을 까먹지 말자.....

Posted by 까망별 음머어

Left, ltrim, MySQL, Right, rtrim, SUBSTRING_INDEX, trim, 문자열 함수

TRACKBACK 0 AND COMMENT 0

[MySQL] INT(11) 에서의 (11)의 의미를 아세요?

MySQL 2008/12/22 18:20

메모만 해두고 블로그에 적는 것을 까먹고 있었던 내용을 간만에 적어본다.

혹시 테이블 생성할 때, INT 에 괄호치고 값을 넣어 본 기억이 있는가?
아니면, 왜 이거 INT(8)이야? 하면서 테이블 생성자에게 화를 내본적이 있는가?

그렇다면....... 당신은 바보~ -0-/
헉...!!! ..... 내가 말을 잘 못했소... 돌던지지 말아주시오~~

(*,.ㅜ);;

자... 간단히 테스트를 해보자.

<< 예제 >>
##################################################################################################
# 1. 테스트 테이블 생성
CREATE TABLE int_test (
    int_1 INT(1),
    int_1_z INT(1) ZEROFILL,
    int_5 INT(5),
    int_5_z INT(5) ZEROFILL,
    int_9 INT(9),
    int_9_z INT(9) ZEROFILL,
    int_11 INT(11),
   int_11_z INT(11) ZEROFILL
)ENGINE=INNODB DEFAULT CHAR SET =UTF8;

# ---------------------------------------------------------------------------

# 2. 테스트 값 넣어보기
INSERT INTO int_test VALUES(1,1,1,1,1,1,1,1);
INSERT INTO int_test VALUES(12345,12345,12345,12345,12345,12345,12345,12345);
INSERT INTO int_test VALUES(1234567890,1234567890,1234567890,1234567890,1234567890,1234567890,1234567890,1234567890);
##################################################################################################

당신의 평소 생각대로라면,
# ------------------------------
SELECT *
FROM int_test;
# ------------------------------
의 결과는?

<< 수행 결과 >>

mysql> SELECT * FROM int_test;
+------------+------------+------------+------------+------------+------------+------------+-------------+
| int_1           | int_1_z        | int_5          | int_5_z      | int_9           | int_9_z       | int_11          | int_11_z     |
+------------+------------+------------+------------+------------+------------+------------+-------------+
|                1 |              1 |                1 |        00001 |               1 | 000000001 |                1 | 00000000001 |
|         12345 |       12345 |          12345 |          12345 |        12345 | 000012345 |        12345 | 00000012345 |
|   1234567890 | 1234567890 |   1234567890 |  1234567890 | 1234567890 | 1234567890 | 1234567890 | 01234567890 |
+------------+------------+------------+------------+------------+------------+------------+-------------+
3 rows in set (0.00 sec)

얼래? 이거... 당신이 예상하던 값과는 다르지 아니한가?

분명히 INT(n)하면 n개만 입력될 것이라 생각했었는데,
값이 몽땅 들어가버리네???

http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html
를 보면, 이에 대한 명확한 설명이 있다..

짧은 잉글리쉬 실력으로 요약해 보자면,

INT의 괄호는 보여지는 숫자의 개수의 제약을 의미하는 것이 아니다.
아무리 INT 옆에 괄호치고 숫자를 넣어봐라, 들어갈 수 있는 한계는 언제나 같을 껄? ㅋㅋㅋ (의역 -0-;;)
INT의 괄호 옵션은 ZEROFILL을 위한 거다.
만약 괄호 안에 5를 집어넣었다면(INT(5)), 5자리 내의 숫자는 0으로 채워지게 된다.
쏼라쏼라....

개뿔.. 이넘의 짧은 영어... 영어 때문에 속이 울렁거린당 (-ㅠ-);;

요약하자면,
INT의 괄호 옵션 기능은 ZEROFILL에서 0이 채워지게 되는 개수이고,
실제 저장공간 및 입력 가능 한계는 동일하다는 의미이다.

위 실행결과에서도 볼 수 있듯이,
INT(1), INT(5), INT(9), INT(11)에 들어가는 값은 동일하며,
INT(1) ZEROFILL, INT(5) ZEROFILL, INT(9) ZEROFILL, INT(11) ZEROFILL 에서만,
0이 들어가는 개수에 차이가 있음을 볼 수 있다.

그나마 ZEROFILL의 한계를 넘어버리면, 그 때부턴 구분도 안 간다 -0-;;;;;

자, 정리하자!!

앞으로 INT(n) 에서 n이 자기 맘대로 들어가 있다고 애꿎은 사람 잡지말고,
'풋! ZEROFILL을 위한 공간이군! 쯧쯧쯧...'
하면서 아는체 해주는 쎈쓰를 보여라!!!

그럼, 당신은 위대한 인물로 추앙받고 존경받으리~~~ ......... 는 아니겠징 -0-;;;;

이상!

[2009-01-09] 덧글에 대한 설명을 위해 아래와 같이 추가합니다 ^0^/

결론부터 말씀드리자면, 오라클에서는 실제 자리수를 표현하는데 사용됩니다. ^^
아래 예제를 볼까요?

<< 예제 >>
##################################################################################################
# 1. 테스트 테이블 생성
CREATE TABLE number_test (
NUMBER_2 NUMBER(2),
NUMBER_5 NUMBER(5)
);
# ---------------------------------------------------------------------------

# 2. 테스트 값 넣어보기
INSERT INTO number_test VALUES(12,12);
INSERT INTO number_test VALUES(12345,12345);
##################################################################################################

의 결과는?

<< 수행 결과 >>

SQL> INSERT INTO number_test VALUES(12,12);

1 개의 행이 만들어졌습니다.

SQL> INSERT INTO number_test VALUES(12345,12345);
INSERT INTO number_test VALUES(12345,12345)
*
1행에 오류:
ORA-01438: 이 열에 대해 지정된 전체 자릿수보다 큰 값이 허용됩니다.

SQL> SELECT * FROM number_test;

NUMBER_2 NUMBER_5
---------- ----------
12 12

참고가 되셨으면 합니다. ^^

Posted by 까망별 음머어

INT OPTIONAL EXTENSION, INT 괄호의 의미, ZEROFILL

TRACKBACK 1 AND COMMENT 6

[MySQL] Oracle의 그룹별 번호 매기기와 같은 기능 구현하기

MySQL 2008/12/22 15:24

쿼리를 날리다 보면, 필요에 따라 그룹별로 순위를 매겨야 할 때가 있다.
이에 대해 오라클에서는 그러한 기능을 제공하는데,
아래가 바로 그 예이다.

[Oracle]
SELECT empno, ename, job, sal,
ROW_NUMBER() OVER(PARTITION BY job ORDER BY sal) AS rnum
FROM scott.emp;

<<결과>>

     EMPNO ENAME                JOB                       SAL       RNUM
---------- -------------------- ------------------ ---------- ----------
      7902 FORD                 ANALYST                    3000          1
      7788 SCOTT                ANALYST                   3000          2
      7369 SMITH                 CLERK                       800          1
      7900 JAMES                CLERK                       950          2
      7876 ADAMS                CLERK                       1100          3
      7934 MILLER               CLERK                       1300          4
      7782 CLARK               MANAGER                  2450          1
      7698 BLAKE               MANAGER                  2850          2
      7566 JONES               MANAGER                  2975          3
      7839 KING                    PRESIDENT                5000          1
      7654 MARTIN               SALESMAN                 1250          1
      7521 WARD                  SALESMAN                 1250          2
      7844 TURNER               SALESMAN                 1500          3
      7499 ALLEN                 SALESMAN                 1600          4

14 개의 행이 선택되었습니다.

상기 쿼리는,
emp 테이블의 JOB을 기준으로 하여 그룹을 정하고 (PARTITION BY job), -- 1
sal을 기준으로 하여 순위를 매겨(ORDER BY sal),
각각의 행에 ROW_NUMBER를 부여하겠다는 의미이다.                         -- 2

여기서 'PARTITION BY job'은 job별 정렬을 발생시킨다.
즉, 최종 결과물의 넘버링은 ORDER BY job, sal의 순으로 결과가 나오는 것이다.

[MySQL]
그런데, 불행하게도..... MySQL에는 저 기능이 없다.
그렇기 때문에 우리의 친구 꼼수(?)를 이용하여 저것을 구현해 내야 하는데.....

SELECT empno, ename, job, sal, rnum
FROM (
   SELECT a.*,
           (CASE @vjob WHEN a.job THEN @rownum:=@rownum+1 ELSE @rownum:=1 END) rnum,
           (@vjob:=a.job) vjob
   FROM emp a, (SELECT @vjob:='', @rownum:=0 FROM DUAL) b
   ORDER BY a.job, a.sal
) c;

<<결과>>

+-------+--------+-----------+------+------+
| empno | ename | job       | sal | rnum |
+-------+--------+-----------+------+------+
| 7902 | FORD   | ANALYST   | 3000 |    1 |
| 7788 | SCOTT | ANALYST   | 3000 |    2 |
| 7369 | SMITH | CLERK     | 800 |    1 |
| 7900 | JAMES | CLERK     | 950 |    2 |
| 7876 | ADAMS | CLERK     | 1100 |    3 |
| 7934 | MILLER | CLERK     | 1300 |    4 |
| 7782 | CLARK | MANAGER   | 2450 |    1 |
| 7698 | BLAKE | MANAGER   | 2850 |    2 |
| 7566 | JONES | MANAGER   | 2975 |    3 |
| 7839 | KING   | PRESIDENT | 5000 |    1 |
| 7654 | MARTIN | SALESMAN | 1250 |    1 |
| 7521 | WARD   | SALESMAN | 1250 |    2 |
| 7844 | TURNER | SALESMAN | 1500 |    3 |
| 7499 | ALLEN | SALESMAN | 1600 |    4 |
+-------+--------+-----------+------+------+
14 rows in set (0.00 sec)

어때... 결과가 같아 보이는가?

자, 그럼 쿼리를 뜯어보자.
여기서 궁금하게 생각되는 부분은 아래 3개의 쿼리라고 예상 된다.

1. (CASE @vjob WHEN a.job THEN @rownum:=@rownum+1 ELSE @rownum:=1 END) rnum,
--> 이전 job 필드와 동일한 그룹인가를 판별하고, 그룹에 따라 순번을 부여하기 위함이며,
      테이블에서 각각의 행을 읽을 때마다,
      변수 @vjob 값이 지금 새로 읽은 job 과 같다면 변수 @rownum을 1증가 시키고,
      그렇지 않은 경우(@vjob이 현재 읽은 job값과 같지 않다면) @rownum을 1로 초기화 시킨다.

2. (@vjob:=a.job) as vjob
--> 테이블에서 각각의 행을 읽을 때마다,
     그룹 판별을 위해 현재 읽고 있는 행의 job값을 변수 @vjob에 입력

3. (SELECT @vjob:='', @rownum:=0 FROM DUAL) b
--> 원래는 쿼리를 수행하기 이전에,
      SET @vjob:=0, @rownum:=0; 을 수행하여 변수를 초기화 해야 한다.
      만약 해주지 않으면, NULL 값이 들어가게 된다.

      하지만 그럴 경우 쿼리가 2번 수행되어야 하기 때문에,
      하나의 쿼리로 만들기 위해서 이런 식의 서브 쿼리를 이용한 것이다.
      이 서브쿼리는 초기 테이블 확인시 1회만 수행되고,
      이후부터는 열람되지 않는다.

      !! 주의 !!
      서브쿼리 안에서의 결과값만 가지고 현재의 결과값을 얻고자 할 때,
      변수가 되는 항목의 값을 동일한 자료형으로 맞춰주지 않으면,
      정상적인 결과값이 나오지 않는다.
      가령 위의 예를 이용하자면, @vjob의 초기값을 @vjob:=0 으로 수행 하고
      서브쿼리만을 수행하면 정상적인 결과값이 나오지 않게 된다.
      한 번 해보자~

이 3가지를 이해한다면 아마 이해할 수 있을 것이라 생각되지만,
한 가지 짚고 넘어가야 할 것이 있다.

Q. 우리가 흔히 쓰는 SELECT 문장의 수행순서는 어떻게 될까?
무슨의미냐 하면..
위에서 사용한 것처럼 변수를 이용한 SELECT 내 연속적인 값의 할당은,
수행결과에 영향을 미치게 되지 않을까?
라는 질문이다.

흠.. 내가 말을 써놓고 난해하군..
예제를 보도록 하자.

<<예제>>
SET @val1=0, @val2=0;    #아까도 말했듯이 변수 초기화는 먼저 선행되어야 한다.
SELECT @val1:=@val1+1, @val2:=@val1+1, @val2:=0, @val1=@val2+1
FROM DUAL;

자.... 당신이 예상하는 결과는?.....

<<쿼리 수행 결과>>

+----------------+----------------+----------+---------------+
| @val1:=@val1+1 | @val2:=@val1+1 | @val2:=0 | @val1=@val2+1 |
+----------------+----------------+----------+---------------+
| 1 | 2 | 0 | 1 |
+----------------+----------------+----------+---------------+
1 row in set (0.00 sec)

상기와 같이 SELECT 내 수행 결과는,
왼쪽에서 오른쪽으로 순차적인 수행이 이루어짐을 알 수 있다.

즉, @val1:=@val1+1 → @val2:=@val1+1 → @val2:=0 → @val1=@val2+1
로 수행 순서가 정해진다는 의미.

그러므로,
변수를 이용한 SELECT를 이용할 때는 반드시 수행순서를 염두해 두고 쿼리를 작성하도록 하자.

PS : 오라클에는 예제 테이블이 있지만 MySQL 에는 없으니
       혹시 테스트 해보고 싶은 사람은 아래 쿼리를 수행해서 테스트 해보도록...

CREATE TABLE emp (
   empno INT,
   ename VARCHAR(30),
   job VARCHAR(30),
   sal INT
)ENGINE=INNODB DEFAULT CHAR SET=UTF8;

INSERT INTO emp
VALUES

(7902,'FORD','ANALYST',3000),

(7788,'SCOTT','ANALYST',3000),

(7369,'SMITH','CLERK',800),

(7900,'JAMES','CLERK',950),

(7876,'ADAMS','CLERK',1100),

(7934,'MILLER','CLERK',1300),

(7782,'CLARK','MANAGER',2450),

(7698,'BLAKE','MANAGER',2850),

(7566,'JONES','MANAGER',2975),

(7839,'KING','PRESIDENT',5000),

(7654,'MARTIN','SALESMAN',1250),

(7521,'WARD','SALESMAN',1250),

(7844,'TURNER','SALESMAN',1500),

(7499,'ALLEN','SALESMAN',1600);

Posted by 까망별 음머어

MySQL, over(partition by order by), 그룹별 순번, 넘버링, 오라클

TRACKBACK 1 AND COMMENT 5

[MySQL] NULL은 INDEX를 탈까?

MySQL 2008/11/25 11:48

[스포일러]
결론부터 말하자면...

MyISAM, InnoDB, BDB, MEMORY 엔진을 사용할 경우,
NULL도 INDEX를 탄다 !!!

아래는 그 테스트와 MySQL 리퍼런스 링크에 관련된 내용이다.

[문제 제기]
요즘 나의 궁금중 중 하나가, 'MySQL에서 NULL 값이 인덱스를 탈까?' 라는 것이었다.

참고로,
오라클의 경우에는 NULL 일 경우 인덱스를 타지 않고,
그로 인해 NULL일 경우를 검색해야 한다면, FULL SCAN을 해야 한다고 알고 있다.

그래서인지 주위에서는 억지로 인덱스를 태우기 위해
DEFAULT '' 를 속성의 기본값으로 사용하는 경우를 볼 수 있었다.

흠... 평소에도 쓸데 없이 NULL을 사용하지 않고 ''를 사용하던 것에 불만이었던 나...
게다가 얼마 전에 읽었던,
The Top 20 Design Tips For MySQL Enterprise Data Architects 라는 짧은 PPT에서는
'필요 없는 경우에 굳이 NOT NULL 을 쓰는 것은 안 좋은 경우' 라고 얘기했기에,

'그럼 한 번 NULL이 인덱스를 타느냐 안 타느냐를 가지고 테스트 해보자'
라는 생각을 하게 되었다.

[테스트]
1. 테이블 준비 및 값 넣기
1.1. 임시 테이블 생성
-- ---------------------------------------------------------------
CREATE TABLE `nulltest` (
      `id` int(11) NOT NULL auto_increment,
      `col1` varchar(50) default NULL,
      `col2` varchar(50) default NULL,
      PRIMARY KEY(`id`),
      KEY `nulltest_cols` (`col1`, `col2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
-- ---------------------------------------------------------------

1.2. 임시 테이블에 널 값의 교차적인 값 입력
(아래 프로시저는 주석을 모두 제거해야 컴파일 됩니다. ^^;;)
-- ---------------------------------------------------------------
CREATE PROCEDURE `p_nulltest`()
BEGIN
DECLARE number INT DEFAULT 0; -- 반복 회수를 위한 변수
SET @number = 1; -- 1로 초기화
WHILE @number < 100000 DO -- 100000번 수행
   INSERT INTO nulltest (col1,col2) VALUES (
           -- 2로 나누어질 경우 NULL, 그렇지 않을 경우 랜덤숫자를 생성하여 입력
           (CASE mod(@number,2)  WHEN 0 THEN NULL ELSE CASE(ROUND(RAND()*10000) AS CHAR) END),
           -- 3로 나누어질 경우 NULL, 그렇지 않을 경우 랜덤숫자를 생성하여 입력
           (CASE MOD(@number,3)  WHEN 0 THEN NULL ELSE CAST(ROUND(RAND()*10000) AS CHAR) END) );
   SET @number = @number+1; -- 변수 1증가
END WHILE;
END
-- ---------------------------------------------------------------

1.3. 테스트 수행
1.3.1. col1 이 NULL인 경우 인덱스를 타는가?

EXPLAIN SELECT id FROM nulltest WHERE col1 IS NULL;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	nulltest	ref	nulltest_cols	nulltest_cols	153	const	50278	Using where; Using index

=> 인덱스를 탄다.

1.3.2. col1, col2 가 NULL인 경우 인덱스를 타는가?

EXPLAIN SELECT id FROM nulltest WHERE col1 IS NULL AND col2 IS NULL;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	nulltest	ref	nulltest_cols	nulltest_cols	306	const,const	13298	Using where; Using index

=> 역시 인덱스를 탄다.

1.4. 분석
위의 실행계획들을 보면, 둘다 인덱스를 이용하는 것을 볼 수 있으며,
이로 인해 NULL값도 인덱스에 들어갈 수 있다는 것을 알 수 있다.

[참고 문서]
나.. 웬지 괜히 테스트 한 듯...
MySQL 외국 리퍼런스를 보면.. (난 영어 능력이 딸려서, 한글만 검색하는뎅 ㅠ,.ㅠ)

http://dev.mysql.com/doc/refman/5.0/en/create-index.html

In MySQL 5.0 :
* 오직 MyISAM, InnoDB, BDB, MEMORY 에서만 인덱스에 NULL을 더 할 수 있다.
* 오직 MyISAM, InnoDB, BDB 에서는 BLOB, TEXT를 INDEX로 사용할 수 있다.
(사족이지만 이것도 좋은 정보)

라고 나온다....

으흐흑~ ㅠ.ㅠ
이넘의 저질 영어 울렁병... 언제나 고칠런지....
담부턴... 영어 리퍼런스도 항상 같이 보도록 노력하자..

Posted by 까망별 음머어

Index, MySQL, null, 가능

TRACKBACK 0 AND COMMENT 2

티스토리 툴바

음머어's 까망별

MY 블로거뉴스

'MySQL'에 해당되는 글 24건

[MySQL] 인덱스 그대로 다시 만들기.

TRACKBACK http://blackbull.tistory.com/trackback/66

[MySQL] ERROR 1201

TRACKBACK http://blackbull.tistory.com/trackback/62

[MySQL] 다른 사용자의 procedure 보기

TRACKBACK http://blackbull.tistory.com/trackback/60

[MySQL] MySQL 5.1 이상 버전의 Federated Engine Enable

TRACKBACK http://blackbull.tistory.com/trackback/59

[MySQL] ERROR 1005 (errno: 150)

TRACKBACK http://blackbull.tistory.com/trackback/54

[MySQL] 시스템 변수 date_format, datetime_format

TRACKBACK http://blackbull.tistory.com/trackback/47

[MySQL] 잊고 있었던 문자열 함수들

TRACKBACK http://blackbull.tistory.com/trackback/46

[MySQL] INT(11) 에서의 (11)의 의미를 아세요?

TRACKBACK http://blackbull.tistory.com/trackback/44

[MySQL] Oracle의 그룹별 번호 매기기와 같은 기능 구현하기

TRACKBACK http://blackbull.tistory.com/trackback/43

[MySQL] NULL은 INDEX를 탈까?

TRACKBACK http://blackbull.tistory.com/trackback/36

ARTICLE CATEGORY

RECENT ARTICLE

RECENT COMMENT

TAG CLOUD

ARCHIVE

LINK

RECENT TRACKBACK