카테고리 없음2015. 6. 30. 16:36

MySQL 특징

[작성일: 2013-01-18]


1. 동일한 계정( MySQL user ) 도 접속한 host 에 따라서 구분하여 권한이 관리됨.


설치 후, 전체 host 에 대한 계정 추가 ( 또는 기존 계정의 host 변경) 필요함.

** Host 필드를 ‘%’ 로 지정하면, localhost 를 제외한 모든 호스트 의미.

 

2. 주요 테이블 종류 구분

 

1) InnoDB 

MySQL 에서 유일하게 트랜잭션 지원

Foreign Key 지원

Primary Key 순서로 데이터가 쌓임 ( PK = Clustered Index )

Locking 레벨 : Row

MySQL 5.5 이후 기본 테이블 종류 (Engine)

MyISAM 비해서, 메모리와 저장공간을 1.5 ~ 2.5배 더 많이 사용함.


2) MyISAM 

트랜잭션 지원 안함

Foreign Key 지원 안함

Locking 레벨 : Table

쿼리 속도가 InnoDB 보다 빠름

단순 SELECT, INSERT 위주의, DML은 쿼리 속도가 InnoDB 보다 빠름

로그 성질의 테이블에 유리함.

 

3) 참조 :

** MySQL InnoDB Engine 사용 시 반드시 알아야 할 세 가지

http://dev.kthcorp.com/2011/06/10/mysql-innodb-engine-3-tips-you-must-know/

** 왜 MySQL 에서 InnoDB 를 써야하는가? InnoDB vs MyISAM 비교

http://dev.kthcorp.com/2011/06/17/why-we-need-to-use-innodb-on-mysql-vs-myisam-comparison/

 

3. Key 최대 크기 제한 ( InnoDB : 767 Bytes, MyISAM : 1000 Bytes )

 

** 에러 메세지 : Specified key was too long; max key length is 767 bytes

InnoDB, UTF-16 : 191 characters (MAX) // 한 문자당 최대 4 Bytes 사용 ( 4*191=764)

InnoDB, UTF-8 : 255 characters (MAX) // 한 문자당 최대 3 Bytes 사용 ( 3*255=765)

 

** 참조 : 

http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html

http://wildlyinaccurate.com/mysql-specified-key-was-too-long-max-key-length-is-767-bytes

 

4. database, table 이름의 대소문자를 구분함

 

1) 관련 옵션 : lower_case_table_names

0 : 데이터베이스와 테이블의 대소문자를 구분함 (unix, linux 버전 기본값)

1 : 데이터베이스와 테이블을 소문자로 변환하여 디스크에 저장함 (windows 버전 기본값)

2 : 데이터베이스와 테이블을 입력한대로 디스크에 저장하고, 쿼리시 대소문자   구안함. (단, Windows 시스템에서만 적용!!)

 

2) 설정 방법 ( Ubuntu 기준 )

:~$ sudo vi /etc/mysql/my.cnf

[mysqld] 항목의 아래에 다음 추가

lower_case_table_names=1

 

5. 최대 컬럼 개수 제한 : 1,000 개

 

6. WHERE 조건 없이 DELETE 못하는 옵션 있음.

 

** 옵션 조회하는 방법

mysql> show variables like 'sql_safe_updates';

 

** 쿼리에서 (임시로) 옵션 변경하는 방법

방법 1) MySQL 서버 실행옵션에서 disable 처리 한다 : sql_safe_updates

방법 2) 쿼리에서 잠시 disable 처리후, 다시 원복한다.

예)

mysql> SET sql_safe_updates=0;

mysql> DELETE FROM PI_Policy;

mysql> SET sql_safe_updates=1;

참조 : http://dev.mysql.com/doc/refman/5.0/en/mysql-tips.html  (4.5.1.6.2. Using the --safe-updates Option)

  

7. 동적 쿼리 실행시 사용하는 PREPARE 구문의 제약사항

 

1) USE database_name; 과 같이 database 선택하는 구문을 사용할 수 없음.

2) 복수의 쿼리 문장을 사용할 수 없음

 

8. 저장프로시져 내에서 커서 선언 위치

 

1) DECLARE 선언 아래

2) Handler 선언 위

그래서, Handler 선언 아래에서는, 별도의 BEGIN .. END; 블록 안에 선언해야 함.

 

9. Clustered Index 관련 특이사항

 

1) MyISAM : Clustered index 를 지원하지 않음

2) InnoDB : Primary Key 만 Clustered Index 가 됨. ( PK = Unique clustered index )

그래서, (InnoDB 에서도) Unique 한 컬럼이 아니라면, Clustered Index 로 지정할 수 없음.

 

10. MSSQL Server 와 MySQL 용어 비교

 

MSSQL Server ==> MySQL

Database ==> Database 또는 Schema

프로그래밍 기능 (Stored Procecure, Function) ==> Routines (Stored Procecure, Function)

Index, Key ==> Key 


저작자표시 변경금지 (새창열림)
Posted by 좋은나무
카테고리 없음2013. 1. 31. 15:14

MySQL 저장프로시져 안에 Event Scheduler 를 생성할 수 없음


관련 에러 메세지 : 

Error Code: 1576. Recursion of EVENT DDL statements is forbidden when body is present 0.000 sec


MySQL 에서는 저장프로시져(Stored Procedure) 안에서 Event Scheduler 를 생성할 수 없음...
여러 번 검색 해보았으나, 방법이 없는 것으로 보임. 음...

직접 아래와 같이 쿼리해야 함. (다행히, 관련 쿼리가 길지는 않음.)


1) 같은 이름가 Event 가 이미 존재할 경우 삭제

DROP EVENT IF EXISTS 'event_name_here';

2) 오늘 오후 11시(23시) 에 한번만 TEST_DB 데이터베이스의 sp_Foo() 저장 프로시져 호출.

SET @var_hour = 23;
SET @var_today_begin = DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00');

CREATE EVENT IF NOT EXISTS 'event_name_here'
ON SCHEDULE AT DATE_ADD(@var_today_begin, INTERVAL @var_hour HOUR)
COMMENT 'event comment here'
DO CALL TEST_DB.sp_Foo();

3) Event  상태 확인

SET @var_event_count = 0;

SELECT COUNT(EVENT_NAME) INTO @var_event_count FROM information_schema.EVENTS 
WHERE EVENT_SCHEMA = 'schema_name_here' 
AND EVENT_NAME = 'event_name_here' 
AND DEFINER = CURRENT_USER();
IF ( @var_event_count IS NOT NULL ) AND ( @var_event_count > 0 ) THEN
-- do something here!!
END IF;


4) Event Scheduler (Thread) 활성화

SET GLOBAL event_scheduler = ON;


5) Event Scheduler (Thread) 활성화 여부 확인

SHOW GLOBAL VARIABLES LIKE 'event_scheduler';


6) Event Scheduler (Thread) 상태 확인

SHOW PROCESSLIST;


[참조: Using the Event Scheduler] 

[참조: CREATE EVENT Syntax] 

[참조: DROP EVENT Syntax] 

[참조: MySQL 5.1 New Features: MySQL Events] 




저작자표시 변경금지 (새창열림)
Posted by 좋은나무
카테고리 없음2013. 1. 28. 13:39

MySQL 서브쿼리(SubQuery) 에서 Limit 사용하기

MySQL 서브쿼리(SubQuery) 에서 Limit 사용하기는 원래 MySQL 에서 허용하지 않지만,

아래와 같이, 별칭(alias)를 주는 방법으로 사용할 수는 있다....고 알려져 있다.


1. 관련 에러 메세지 : 

Error Code: 1235. This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'


2. 서브쿼리(SubQuery) 에서 Limit 사용하는 방법 : 

SELECT * FROM (SELECT * FROM UserInfo WHERE CreateDate >= '2010-01-01' LIMIT 0,10) AS temp_tbl;



[참고: MySQL Forums :: Transactions :: Limit in subquery]

저작자표시 변경금지 (새창열림)
Posted by 좋은나무
카테고리 없음2013. 1. 24. 12:01

MySQL 날짜 계산

이번 주 (This Week)


** 이번 주 시작일 0시 0분 0초 

SET @var_today = NOW();

DATE_FORMAT( DATE_SUB(@var_today, INTERVAL DAYOFWEEK(@var_today)-1 DAY), '%Y-%m-%d 00:00:00');


** 이번 주 종료일 (= 다음주 시작일 0시 0분 0초) 

SET @var_today = NOW();

DATE_FORMAT( DATE_ADD(@var_today, INTERVAL 8-DAYOFWEEK(@var_today) DAY), '%Y-%m-%d 00:00:00');


이번 달 (This Month)


** 이번 달 시작일 0시 0분 0초

SET @var_today = NOW();

DATE_FORMAT( DATE_SUB(@var_today, INTERVAL DAYOFMONTH(@var_today)-1 DAY), '%Y-%m-%d 00:00:00');


** 이번 달 종료일 (= 다음달 시작일 0시 0분 0초) 

SET @var_today = NOW();

DATE_FORMAT( DATE_ADD( LAST_DAY(@var_today), INTERVAL 1 DAY), '%Y-%m-%d 00:00:00');


지난 주 (Last Week)


** 지난 주 시작일 0시 0분 0초 

SET @var_today = NOW();

DATE_FORMAT( DATE_SUB(@var_today, INTERVAL DAYOFWEEK(@var_today)+6 DAY), '%Y-%m-%d 00:00:00');


** 지난 주 종료일 (= 이번 주 시작일 0시 0분 0초) 

SET @var_today = NOW();

DATE_FORMAT( DATE_SUB(@var_today, INTERVAL DAYOFWEEK(@var_today)-1 DAY), '%Y-%m-%d 00:00:00');


지난 달 (Last Month)


** 지난 달 시작일 0시 0분 0초

SET @var_today = NOW();

SET @var_lastmonth_end = DATE_FORMAT( DATE_SUB(@var_today, INTERVAL DAYOFMONTH(@var_today) DAY), '%Y-%m-%d 00:00:00');

DATE_SUB(@var_lastmonth_end, INTERVAL DAYOFMONTH(@var_lastmonth_end)-1 DAY);


** 지난 달 종료일 (= 이번 달 시작일 0시 0분 0초) 

SET @var_today = NOW();

DATE_FORMAT( DATE_SUB(@var_today, INTERVAL DAYOFMONTH(@var_today)-1 DAY), '%Y-%m-%d 00:00:00');


저작자표시 변경금지 (새창열림)
Posted by 좋은나무
카테고리 없음2013. 1. 17. 11:44

MySQL: PREPARE 구문에서 입출력 변수 사용하기

1. 입력 변수


1) MySQL Reference Manual 에 따르면, 아래와 같이 EXECUTE ... USING ... 형식으로 지정한다.

SET @var_user_id = 'Fantine';

SET @var_user_name = 'Anne';


SET @var_query = CONCAT('SELECT * FROM user_info WHERE user_id = ? OR user_name = ?');


PREPARE stmt1 FROM @var_query;

EXECUTE stmt1 USING @var_user_id, @var_user_name;

DEALLOCATE PREPARE stmt1;

 

2) 그런데, 그냥 다음과 같이 직접 써도 된다. 음...

SET @var_user_id = 'Fantine';

SET @var_user_name = 'Anne';


SET @var_query = CONCAT('SELECT * FROM user_info WHERE user_id = @var_user_id OR user_name = @var_user_name');


PREPARE stmt1 FROM @var_query;

EXECUTE stmt1;

DEALLOCATE PREPARE stmt1; 

 

3) 변수를 DECLARE 구문으로 선언한 경우에는 다음의 방법만 가능하다.

DECLARE var_user_id nvarchar(255);

DECLARE var_user_name nvarchar(255);

 

SET var_user_id = 'Fantine';

SET var_user_name = 'Anne';


SET @var_query = CONCAT('SELECT * FROM user_info WHERE user_id = ''', var_user_id, ''' OR user_name = ''', var_user_name, '''');


-- 또는

-- SET @var_query = CONCAT('SELECT * FROM user_info WHERE user_id = \'', var_user_id, '\' OR user_name = \'', var_user_name, '\'');


PREPARE stmt1 FROM @var_query;

EXECUTE stmt1;

DEALLOCATE PREPARE stmt1


 

2. 출력 변수

 

1) 변수에 직접 지정 한다.

SET @var_out_param = ''; -- (변수 초기화가 필요없으면) 생략 가능함!!!

 

SET @var_query = CONCAT('SELECT user_name INTO @var_out_param FROM user_info WHERE user_id = ''Fantine''');

-- 또는

-- SET @var_query = CONCAT('SELECT user_name INTO @var_out_param FROM user_info WHERE user_id = \'Fantine\'');


PREPARE stmt1 FROM @var_query;

EXECUTE stmt1;

DEALLOCATE PREPARE stmt1;

 

-- 결과 확인

-- SELECT @var_out_param;


2) 변수를 DECLARE 구문으로 선언한 경우에는 다음의 방법만 가능하다. 

DECLARE var_user_name nvarchar(255);


SET @var_out_param = ''; -- (변수 초기화가 필요없으면) 생략 가능함!!!

 

SET @var_query = CONCAT('SELECT user_name INTO @var_out_param FROM user_info WHERE user_id = ''Fantine''');

-- 또는

-- SET @var_query = CONCAT('SELECT user_name INTO @var_out_param FROM user_info WHERE user_id = \'Fantine\'');


PREPARE stmt1 FROM @var_query;

EXECUTE stmt1;

SET var_user_name = @var_out_param;

DEALLOCATE PREPARE stmt1;

 

-- 결과 확인

-- SELECT var_user_name;



저작자표시 변경금지 (새창열림)
Posted by 좋은나무
카테고리 없음2013. 1. 14. 15:20

MySQL 의 Clustered Index 이슈

MySQL 에 Clustered index 를 추가하려 할 때,


1) MyISAM : Clustered index 를 지원하지 않음.

2) InnoDB : Primary Key = Unique clustered index. 

- 하나의 테이블에는 한개의 clustered index 만 허용됨.

- InnoDB 는 반드시 한개의 Primary Key 가 존재함 [참조: MySQL의 NONCLUSTERED PRIMARY KEY 이슈]


그래서, ( InnoDB만 고려한다면 ) 

1) Non Unique Key 를 Clustered index 로 지정할 수 없다.

2) Primary Key 가 아닌 Clustered index 를 지정할 수 없다.


다시 말해서, MySQL 에서 Clustered index 를 지정하고 싶으면, 

- InnoDB 를 storage engine 으로 지정하고

- 해당 컬럼을 Primary Key 로 지정해야 한다. ( = Unique Clustered Index )



[참조: Clustered and Secondary Indexes] 

[참조: The MyISAM Storage Engine] 

[참조: The InnoDB Storage Engine] 

[참조: Understanding InnoDB clustered indexes] 

저작자표시 변경금지 (새창열림)
Posted by 좋은나무
카테고리 없음2013. 1. 14. 12:16

MySQL 에서 Application lock 걸기

1) Application lock 걸기


SELECT GET_LOCK('lock_name',10);


> 'lock_name' 이라는 이름의 application lock을 건다.

> (이미 lock 이 걸려 있으면) 10 초간 대기 한다.

> 반환값

1 : lock 성공

0 : lock 실패, timeout

NULL : lock 실패, 장애 발생



2) lock 해제 (release) 조건


조건1) RELEASE_LOCK() 실행시 : 


예) SELECT RELEASE_LOCK('lock_name');


> 반환값

1 : release 성공

0 : release 실패 (현재 쓰레드에서 lock 이 해제되지 않음)

NULL : release 실패 ('lock_name' 는 이름의 lock 이 존재하지 않음.)



조건2) 새로운 GET_LOCK() 실행시 : 


예) SELECT GET_LOCK('new_lock_name');


조건3) 연결 종료시



3) lock 확인


SELECT IS_FREE_LOCK('lock_name');

--> 'lock_name' 이라는 이름의 lock 이 사용중이면 (not free 이면) 0 반환,

사용중이 아니면 ( free 이면 ) 1 반환

--> NULL : 'lock_name' 는 이름의 lock 이 존재하지 않음.


SELECT IS_USED_LOCK('lock_name');

--> 'lock_name' 이라는 이름의 lock 이 사용중이면 (used 이면) 1 반환, 

사용중이 아니면 (not used 이면 ) 0 반환

--> NULL : 'lock_name' 는 이름의 lock 이 존재하지 않음.



4) 특이사항


> 트랜젹션과 함께 사용될 때, 트랜젹션이 종료(commit / rollback) 되더라도, lock 이 해제되지는 않는다. 

반드시 명시적으로 lock을 해제 해야 한다.


Locks obtained with GET_LOCK() do not interact with transactions. That is, committing a transaction does not release any such locks obtained during the transaction.



[참조: Miscellaneous Functions]



저작자표시 변경금지 (새창열림)
Posted by 좋은나무
카테고리 없음2013. 1. 14. 11:45

MySQL 저장 프로시져 내에서 발생한 에러 처리 (Error Handling)

MySQL 저장 프로시져 ( Stored Procedure ) 내에서, 쿼리 에러 발생시,

그 에러에 대한 HANDLER 가 정의되어 있지 않으면, 에러 발생 위치에서 Stored Procedure 가 종료됨.

이 때, 


1) 에러 처리 구문 실행 후, Stored Procedure 종료 하는 방법

2) 에러 처리 구문 실행 후, 다음 쿼리를 계속 실행 하는 방법

3) DECLARE ... HANDLER 구문 분석

4) DECLARE ... HANDLER 구문 특이사항



- 아 래 - 


1) 에러 처리 구문 실행 후, Stored Procedure 종료 하는 방법


DECLARE EXIT HANDLER FOR SQLEXCEPTION

  BEGIN

    -- 여기에 에러 처리 구문 추가!

  END;



2) 에러 처리 구문 실행 후, 다음 쿼리를 계속 실행 하는 방법


DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

  BEGIN

    -- 여기에 에러 처리 구문 추가!

  END;



3) DECLARE ... HANDLER 구문 분석


구문 : DECLARE [handler_action] HANDLER [condition_value] [statement]


[handler_action]

CONTINUE : 계속 진행

EXIT : 중지, 종료


[condition_value]


mysql_error_code : MySQL 에러 코드 ( number 값 )  [참조: Server Error Codes and Messages]


SQLSTATE [VALUE] : 

5 자리의 문자로 정의된, SQL 상태값. 

'00' 으로 시작하는 경우 정상. 

SQLSTATE 는 ODBC 에서 정의된 상태값이므로, mysql의 에러 코드와 100% 매칭되지는 않음.

[참조: Server Error Codes and Messages]


condition_name : 미리 정의한 조건의 상수값 [참조: DECLARE ... CONDITION Syntax]


예) mysql 에러 코드 1051 발생시의 에러 처리

DECLARE CONTINUE HANDLER FOR 1051

  BEGIN

    -- body of handler

  END;


또는 

DECLARE no_such_table CONDITION FOR 1051;

DECLARE CONTINUE HANDLER FOR no_such_table

  BEGIN

    -- body of handler

  END;



SQLWARNING : SQLSTATE 값이 '01' 로 시작하는 경우


NOT FOUND : SQLSTATE 값이 '02' 로 시작하는 경우


SQLEXCEPTION : SQLSTATE 값이 '00', '01', '02' 로 시작하지 않는 경우.


[statement] : 조건 만족시 실행할 구문



4) DECLARE ... HANDLER 구문 특이사항


[statement] 위치에  ITERATE 또는 LEAVE 구문을 사용할 수 없음.



[참조: DECLARE ... HANDLER Syntax]


저작자표시 변경금지 (새창열림)
Posted by 좋은나무
카테고리 없음2013. 1. 10. 15:49

MySQL 함수


* 날짜 비교 : 

--> DATEDIFF 는 날짜(Date)만 계산함.

--> DATEDIFF ( enddate, startdate )


MySQL : DATEDIFF( DATE_ADD( NOW(), INTERVAL 1 DAY) , '2013-01-01 09:00:00' )

--> NOW() + 1 : (1일이 아니라) 1초를 더함.

[참조 : MySQL의 DATEDIFF() 함수]


<비교>

MSSQL : DATEDIFF(day, '2013-01-01 09:00:00', GETDATE()+ 1)

--> DATEDIFF ( datepart , startdate , enddate )



* DATE_FORMAT()  사용해서 YYYYMMDD 형식으로 반환하기

SET @var_Date := DATE_FORMAT(NOW(), '%Y%m%d');

[참조 : MySQL 의 DATE_FORMAT() 함수]


* CURDATE()  사용해서 YYYYMMDD 형식으로 반환하기

SET @var_Date := CURDATE()+0;

[참고 : MySQL 의 CURDATE() 함수]


Posted by 좋은나무
카테고리 없음2013. 1. 10. 14:26

MySQL 의 PREPARE 구문의 제약사항

MySQL 에서 동적으로 SQL 을 생성해서 호출할 경우, 

다음과 같이  PREPARE 구문을 사용한다.

mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';

mysql> SET @a = 3;

mysql> SET @b = 4;

mysql> EXECUTE stmt1 USING @a, @b;

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

| hypotenuse |

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

|          5 |

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

mysql> DEALLOCATE PREPARE stmt1;


그런데, 이 구문은 다음과 같은 제약사항이 있다.

1) 사용할 수 있는 쿼리 문장이 제한된다.

The following SQL statements can be used in prepared statements: ALTER TABLE, CALL, COMMIT, CREATE INDEX, CREATE TABLE, DELETE, DO, DROP INDEX, DROP TABLE, INSERT, RENAME TABLE, REPLACE, SELECT, SET, TRUNCATE TABLE, UPDATE, and most SHOW statements. 

ANALYZE TABLE, OPTIMIZE TABLE, and REPAIR TABLE are also supported as of MySQL 5.0.23.

대부분의 구문을 지원하지만, 

결정적으로 USE testdb; 와 같이 Database 를 선택하는 구문은 사용할 수 없다. 


2) 복수의 쿼리문장을 지정할 수 없다.

mysql> PREPARE stmt1 FROM 'show tables; SELECT * from testtable;


[참고 : SQL Syntax for Prepared Statements]

Posted by 좋은나무

티스토리툴바