음머어's 까망별

블로그 이미지
무지 인간의 첫 걸음..
by 까망별 음머어
  • 303,486Total hit
  • 137Today hit
  • 147Yesterday hit

'MySQL'에 해당되는 글 12건

  1. 2009.05.29
    [MySQL] ERROR 1201 (1)
  2. 2009.01.09
    [MySQL] ERROR 1005 (errno: 150) (7)
  3. 2008.12.29
    [MySQL] 시스템 변수 date_format, datetime_format (2)
  4. 2008.12.26
    [MySQL] 잊고 있었던 문자열 함수들
  5. 2008.12.22
    [MySQL] Oracle의 그룹별 번호 매기기와 같은 기능 구현하기 (7)
  6. 2008.11.25
    [MySQL] NULL은 INDEX를 탈까? (2)
  7. 2008.11.18
    [MySQL] Oracle의 DBLink 와 유사한 Federated Engine (2)
  8. 2008.11.17
    [MySQL] FIRST, AFTER, LAST
  9. 2008.11.17
    [MySQL] MySQL DECODE, ENCODE
  10. 2008.11.17
    [Oracle] MySQL의 Limit처럼 사용해보자.
평소 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

저작자 표시 비영리
신고
TRACKBACK 0 AND COMMENT 1

일전에 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, 자료형, 유니크 키) 를 반드시 확인해 보도록 하자.

그 때도 해결이 안된다면, 위의 링크들을 다시 한 번 꼼꼼히 살펴보도록 한다.
저작자 표시 비영리
신고
TRACKBACK 1 AND COMMENT 7
MySQL의 변수를 살펴보다, 유용하다 싶은 것을 하나 발견했다.

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


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


 mysql> show variables like '%date%';
+------------------------------+--------------+
| Variable_name                       | Value            |
+------------------------------+--------------+
| date_format                            | %Y-%m-%d   |
| datetime_format                      | %Y%m%d%H%i%s |
| 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)

mysql> SHOW VARIABLES LIKE 'datetime_format';
+-----------------+--------------+
| Variable_name   | Value             |
+-----------------+--------------+
| datetime_format | %Y%m%d%H%i%s |
+-----------------+--------------+
1 row in set (0.02 sec)

mysql> SELECT sysdate() FROM dual;
+---------------------+
| sysdate()                 |
+---------------------+
| 2008-12-29 15:54:35    |
+---------------------+
1 row in set (0.00 sec)


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

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

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

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

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



저작자 표시 비영리
신고
TRACKBACK 0 AND COMMENT 2

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

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

# 아래는 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)



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


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


저작자 표시 비영리
신고
TRACKBACK 0 AND COMMENT 0

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

[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);

저작자 표시 비영리
신고
TRACKBACK 1 AND COMMENT 7

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

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로 사용할 수 있다.
   (사족이지만 이것도 좋은 정보)


라고 나온다....

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




저작자 표시 비영리
신고
TRACKBACK 0 AND COMMENT 2
오라클에서는 물리적으로 떨어져 있는 삽입/삭제/수정/조회/조인 등의
서버간의 데이터 이동이 요구될 때, DBLink 라는 녀석을 사용한다...... 고 하지만,
맞을까봐 개인적으로는 사용만 해보고 실제로 만든 적은 없다.. --;;

한 가지 분명한 것은 synonym이 설정되어 있지 않은 이상,
@를 사용하여 접근 한다는 것뿐...????? -0-;;;

암튼 간에,
물리적으로 떨어져 있지만,
논리적으로는 바로 옆의 테이블인양 사용하는 것은,
Performance에 상관 없이 매력적이고, 편리한 것이라는 점에 이견이 없을 듯 하다.

그렇다면, Oracle에는 있는 이 DBLink 기술이,
MySQL에도 있지 않을까?

그래서,
여기 저기 물어도 보고, 레퍼런스 메뉴얼도 달달이 읽다가,
FEDERATED ENGINE 이라는 녀석을 발견했다.

[HELP FEDERATED ENGINE]
길다.. 생략..... -0-;;;;;;;

[FEDERATED ENGINE 설명]
Federated Engine은,
실제적으로 물리적인 저장공간을 확보하여 데이터를 복사해서 갖는 것이 아니다.
무슨 말이냐 하면... 흠...

마치 C에서의 포인터나, C++, Java에서의 참조형 처럼,
단순히 틀에 대한 정보만을 지니고,
그것을 원거리에서 MySQL의 API를 이용하여 접근하는 방식이다.


그리하여, 물리적으로 격리되어 있는 다른 MySQL 서버의 테이블로의 접근이
로컬 내에서 가능하게 만들어준다.
(5.0 에서는 MySQL 서버간에만 사용가능하며, 추후에 이기종 DB 와의 연계도 개발할꺼라는데...
언제나 될런지 -0-;;;)

물론,
DBLink와 마찬가지로 물리적 거리감은 실행 속도의 하락과
DDL(Data Definition Language) 같은 쿼리는 적용 되지 않는 부효과를 발생시킨다.

그런 이유로,
만약 로컬에서 DROP TABLE 명령어를 사용하여 테이블을 DROP 시킨다면,
로컬만 DROP되고, 원거리의 TABLE에는 영향을 미치지 않는다.



[FEDERATED ENGINE 사용방법]
먼저, 원본 테이블이 어딘가 존재해야 한다.

# 원본 테이블
USE testdb;
CREATE TABLE testtbl (
 id INT AUTO_INCREMENT PRIMARY KEY,
 col1 VARCHAR(100) DEFAULT NULL,
 col2 VARCHAR(200) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=UTF8;


그리고 난 후에야, 로컬에 동일한 구조로 테이블을 생성한다.
단, 이번에는,
****************************************************************************************
1. ENIGINE :  ENGINE=FEDERATED
2. CONNECTION 정보 : 'mysql://아이디:패스워드@IP또는DOMAIN:PORT/DB명/TABLE명'
****************************************************************************************

# FEDERATED 된 테이블
CREATE TABLE testtbl (
 id INT AUTO_INCREMENT PRIMARY KEY,
 col1 VARCHAR(100) DEFAULT NULL,
 col2 VARCHAR(200) DEFAULT NULL
) ENGINE=FEDERATED 
  DEFAULT CHARSET=UTF8
  CONNECTION='mysql://scott:tiger@192.168.10.2:3306/testdb/testtbl;


라고 만들면,
그 때부터 INSERT, DELETE, UPDATE, SELECT 가 가능해지며, 
INDEX를 사용할 수 있게 된다.

[FEDERATED 주의할 점]
1. 반드시 #원본테이블 처럼, 대상이 되는 테이블이 먼저 존재해야 한다.
2. Transaction은 지원되지 않는다.
3. #원본테이블 데이터를 제외한 필드명/타입/구조 등의 변경 사항은
   #FEDERATED 된 테이블 적용이 되지 않기 때문에,
   수동으로 변경해 주어야 한다.
4. Query Cache는 이용할 수 없다.
5. !!!!! 중요 !!!!! SHOW CREATE TABLE #FEDERATED된 테이블
   입력시 #원본테이블로의 접속정보가 고스란히 노출 되므로, 주의가 요구된다.

이상!!!

PS:
흠흠... 작명이 저 따위로 되어있다고 해서, 절대로 따라하지 마시길 -0-;;
작명은 사람이 이해하기 쉽고, 눈에 확 들어오도록 정해진, 또는 나름 정한 Naming Rule을 이용하세용~


저작자 표시 비영리
신고
TRACKBACK 0 AND COMMENT 2

당신은... 개발 테스트를 위해,
테이블을 생성하고, 데이터를 때려 붓고.......

그러다, '아! 필드 하나 추가하자!' 할 때가 있지 않은가?

그리고, '아! 이러면 테이블의 맨 밑에 새로운 필드가 붙을텐데.... 원래 중간에 있어야 하는데.. ' 라고 하며,
데이타까지 그득하게 있는 테이블을 눈물을 머금고,
DROP하고 다시 CREATE 문을 실행 시킨적은 없는가?

..... 난 있는데 -___-;;;

자.. 그럴 경우를 위해 FIRST, AFTER, LAST  3종 세트가 있다.

결론부터 말하자면, FIRST, AFTER, LAST 는
ALTER TABLE을 이용하여 필드의 추가 / 위치 조정시 사용하는 예약어이다.


[HELP ALTER TABLE]
********************************************************************************************
mysql> help alter table;
Name: 'ALTER TABLE'
Description:
Syntax:
ALTER [IGNORE] TABLE tbl_name
    alter_specification [, alter_specification] ...

alter_specification:
    table_option ...
  | ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ]
  | ADD [COLUMN] (col_name column_definition,...)
  | ADD {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
  | ADD [CONSTRAINT [symbol]]
        PRIMARY KEY [index_type] (index_col_name,...)
  | ADD [CONSTRAINT [symbol]]
        UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...)
  | ADD [FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (index_col_name,...)
  | ADD [CONSTRAINT [symbol]]
        FOREIGN KEY [index_name] (index_col_name,...)
        reference_definition
  | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  | CHANGE [COLUMN] old_col_name new_col_name column_definition
        [FIRST|AFTER col_name]
  | MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
  | DROP [COLUMN] col_name
  | DROP PRIMARY KEY
  | DROP {INDEX|KEY} index_name
  | DROP FOREIGN KEY fk_symbol
  | DISABLE KEYS
  | ENABLE KEYS
  | RENAME [TO] new_tbl_name
  | ORDER BY col_name [, col_name] ...
  | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
  | [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]
  | DISCARD TABLESPACE
  | IMPORT TABLESPACE

index_col_name:
    col_name [(length)] [ASC | DESC]

index_type:
    USING {BTREE | HASH | RTREE}

ALTER TABLE enables you to change the structure of an existing table.
For example, you can add or delete columns, create or destroy indexes,
change the type of existing columns, or rename columns or the table
itself. You can also change the comment for the table and type of the
table.

URL: http://dev.mysql.com/doc/refman/5.0/en/alter-table.html
********************************************************************************************

[사용 방법]
위의 HELP 결과중, 금색으로 칠해져 있는 부분을 보면,
ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ]
MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]

를 볼 수 있을 것이다.

즉,
ALTER TABLE ADD col2 varchar(12) FIRST;        # 테이블의 가장 윗줄에 col2 를 추가하겠다.
ALTER TABLE ADD col2 varchar(12) AFTER col1; # col1 이후에 col2를 추가하겠다.
ALTER TABLE ADD col2 varchar(12) LAST;         # 가장 아랫줄에 col2를 추가하겠다. (DEFAULT)
이고,

마찬가지로
ALTER TABLE MODIFY col2 varchar(12) FIRST;   # 테이블의 가장 윗줄에 col2 를 이동하겠다.
ALTER TABLE MODIFY col2 varchar(12) AFTER col1;   # col1 이후에 col2를 이동하겠다.
ALTER TABLE MODIFY col2 varchar(12) LAST;           # 가장 아랫줄에 col2를 이동하겠다.

와 같이 사용할 수 있다는 이야기이다.


[사담]
개인적으로는,
우리가 그리는 모델링 된 모습과 동일하게 구성하고,
인간이 인지하기 편하게 만들고자 하는 것이 큰 이득이라고 생각한다.

다시 말해서 데이터를 입력할 때 발생할 수 있는,
개념적 시각에 의한 차이로 인해 발생할 수 있는 오류를 없엘 수 있다는 의미가 아닐까 라는 생각이다.

가령,
CREATE TABLE test (
              id int,
              col2 varchar(12)
);
라고 생성된 테이블에

ALTER TABLE ADD col1 varchar(12);
라고 입력하면,

test에는 id, col2, col1 순으로 필드를 갖게 된다.

만약, test의 필드를 적지 않고, 단순히
INSERT INTO test VALUES( 1, 'col1 str', 'col2 str');
라고 입력한다면,

col2 에는 'col1 str' 이 col1 에는 'col2 str'이 들어가게 되므로,
직관적이지 못하다.

그러므로,
ALTER TABLE ADD col1 varchar(12) AFTER id;
라고 하는 것이,

글의 시작에서도 말했듯이 테이블 DROP을 발생시키지 않고,
깔끔하게 처리할 수 있는 방법이 아닐까 한다.











저작자 표시 비영리
신고
TRACKBACK 0 AND COMMENT 0

Oracle을 사용하던 사람들이 MySQL을 사용할 때 착각하기 쉬운 것 중 하나가
바로 DECODE 일 것이다.

내 블로그에 있는 http://blackbull.tistory.com/10
글을 보면, Oracle에서 사용하던 DECODE 에 대해 설명하고 있으니 그것은 별도로 참고하고,

오늘은 MySQL만을 살펴보도록 하자.

MySQL 툴이나, 기타 SQL관련 프로그램을 사용하다보면,
MySQL에서 DECODE의 글씨 색이 다른 것을 볼 수 있으며,
이미 예약어로 잡혀있는 것을 볼 수 있을 것이다.

즉, MySQL도 DECODE가 존재 한다는 것인데,
웃기게도 이것 때문에 Oracle의 DECODE와 혼동하는 사람들을 매번 낚고 있다 --;;


[MySQL에서 help를 이용하여 DECODE에 대해 알아본 결과]
************************************************************************************************
mysql> help decode
Name: 'DECODE'
Description:
Syntax:
DECODE(crypt_str,pass_str)

Decrypts the encrypted string crypt_str using pass_str as the password
crypt_str should be a string returned from ENCODE().

URL: http://dev.mysql.com/doc/refman/5.0/en/encryption-functions.html
************************************************************************************************

[DECODE, ENCODE 사용방법]
DECODE(암호화 할 대상 문자열, 암호화 용도로 사용되는 패스워드 키)
ENCODE(암호화 할 대상 문자열, 암호화 용도로 사용되는 패스워드 키)

[DECODE, ENCODE 설명]
MySQL에서의 DECODE는,
ENCODE 로 encryptioin 된 문자열을 역으로 바꿀 때 사용하는 함수이다.


바꿔 말하자면,
ENCODE 된 문자열이 존재해야 DECODE를 사용할 수 있다는 의미이다.

가령,
CREATE TABLE test (
  str blob
)ENGINE=INNODB DEFAULT CHARSET=UTF8;


이라고 테이블을 생성하고,
encryption된 값을 대입해 보자.

단, 여기서 주의할 점은 문자열이 encryption될 경우,
어떤 문자가 올지 모르기 때문에,
반드시 blob과 같은 binary형태의 자료형을 선언해 두어야 한다는 것이다.
만약 문자열이라고 char, varchar, text 등으로 자료형을 선언한다면,
warning 메세지를 받게 되며, 데이터가 입력되지 않게 된다.

아무튼,
INSERT test (str) VALUES (ENCODE('My Love Is Forever', 'Love'));
라고 입력 후

SELECT str FROM test;
해보자.

결과는?
*************************************************************************
jw
tj"R
*************************************************************************
라고 나온다.

즉, 'My Love Is Forever' 라는 문자열이, 특정한 알고리즘을 이용하여,
'Love' 라는 키워드를 이용하여 위의 결과와 같은 암호화된 결과를 만들어 낸 것이다.

그렇다면, 만약 이것을 정상적으로 보고자 한다면?

SELECT DECODE(str, 'Love') FROM test;
라고 해보자.

결과는?
*************************************************************************
My Love Is Forever
*************************************************************************
라고 나온다.


[결론]
자!!!
이것이 바로 MySQL의 DECODE인 것이다!!!!!!

그러니 앞으로 Oracle의 DECODE와 햇갈리는 사태를 벌이지 말도록 하자~


저작자 표시 비영리
신고
TRACKBACK 0 AND COMMENT 0

내가 지식이 얕은 건지, 어떻게든 돌아가게는 만든다는 주먹구구식 마인드 때문인지,
Oracle DB에 입력되어 있는 순차적인 데이터의 선택적 추출은
이 방법밖에 알지 못한다..

자, 이제 그 방법에 대해 알아보자.

먼저 MySQL 에서는
# [MySQL]
SELECT a.love_id
FROM love a
LIMIT 1000, 2000;


하면, 실제 입력된 데이터의 순서에 맞추어 1000번 부터 2000번까지의 데이터가 나온다.

하지만.... 망할 ORACLE 에는 없다는 거....

어떤 분들은 'rownum으로  WHERE rownum BETWEEN 1000 AND 2000 하면 되지 않느냐??'
라고 하는데..... 잘 찾아보면, rownum 은 결과 값에 매겨지는 상대적인 값이므로,
절대적인 신뢰감이 없고, 위의 수식도 먹지 않는다.....
뭐.. 안 믿으시면 한 번 해보시라.. --;;

그럼.. 어떻게 해야할까?
우리의 친구 꼼수를 한 번 발동 시켜볼까?

* 해법 *
************************************************************************************************
# [Oracle]
1. 아래 쿼리는, 데이터가 실제적으로 들어간 순서에 맞게 rownum을 부여 하여,
그 값을 하나의 컬럼으로 출력될 수 있게 해준다.
SELECT a.rownum as rnum, a.love_id
FROM love a;

2. 1에서 만들어진 쿼리를 아래와 같이 서브쿼리화 하여, between 사용이 가능해진다.
SELECT b.love_id
FROM (SELECT a.rownum as rnum, a.love_id FROM love a) as b
WHERE b.rnum BETWEEN 1000 AND 2000;

************************************************************************************************

자, 그럼,, 저 위의 방식을 이용하여 마음껏(???) limit 인 척 해보자.



저작자 표시 비영리
신고
TRACKBACK 0 AND COMMENT 0

ARTICLE CATEGORY

모든 이야기 (77)
독백 (22)
MySQL (25)
Oracle (7)
영화 (6)
Linux (3)
DW(Data Warehouse) (0)
(7)
음악 (1)
Python (3)
Postgresql (2)

ARCHIVE