style24.doc/산출물/4.구축/mysql로_개발시_팁.sql

# autocommit 활성화 여부 조회
SELECT @@AUTOCOMMIT;
SHOW VARIABLES LIKE 'autocommit';

# autocommit 비활성화
SET AUTOCOMMIT = 0;

#시퀀스 생성 관련
1. 테이블의 PK를 INT UNSIGNED AUTO_INCREMENT 로 지정 예) LOGIN_HST_SQ INT UNSIGNED AUTO_INCREMENT COMMENT '로그인이력일련번호'
2. mybatis XML의 insert문에 keyProperty 속성 추가. 예) keyProperty="loginHstSq"
3. JAVA 소스에서는 Domain의 get Method를 이용해 추출.
	예)
		void createLoginHistory(GaaLoginHst loginHst);
		loginHst.getLoginHstSq();

# 오라클 -> mysql 변환 함수
CONNECT BY -> RECURSIVE 형태로 변경 (참조: ~System.xml 파일의 getLoginMenuList)
SYSDATE -> NOW()
|| -> CONCAT
TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') -> DATE_FORMAT(NOW(), '%Y%m%d%H%i%S')
LISTAGG(ROLE_CD,',') WITHIN GROUP (ORDER BY DISP_ORD) -> GROUP_CONCAT(ROLE_CD ORDER BY DISP_ORD SEPARATOR ',')
ROWNUM , @rownum:=@rownum+1 AS RNUM T1, (SELECT @rownum:=0) //T2 식으로 변수를 사용해 구현이 가능하나, 성능이 매우 취약함. 전체 데이터에 대해 상수를 적용하기 때문에, 데이터가 적은경우에만 사용함. 단순히 Paging 목적이라면, LIMIT를 사용해 변경해야 한다. LIMIT 0,10 (주의 0부터 시작함 -> 오라클 Between 은 1부터 시작)
TO_CHAR(1000) -> CONVERT(1000, char)
TO_DATE('2013-02-11','YYYY-MM-DD') -> STR_TO_DATE('2013-02-11', '%Y-%m-%d')

# 현재시간에 1초 더하기
DATE_ADD(NOW(), INTERVAL 1 SECOND)

# 현재시간에 1분 더하기
DATE_ADD(NOW(), INTERVAL 1 MINUTE)

# 현재시간에 1시간 더하기
DATE_ADD(NOW(), INTERVAL 1 HOUR)

# 현재시간에 1일 더하기
DATE_ADD(NOW(), INTERVAL 1 DAY)

# 현재시간에 1달 더하기
DATE_ADD(NOW(), INTERVAL 1 MONTH)

# 현재시간에 1년 더하기
DATE_ADD(NOW(), INTERVAL 1 YEAR)

# 두 날짜의 차이 구하기
DATEDIFF(날짜1, 날짜2)
TIMESTAMPDIFF(SECOND,날짜1,날짜2) // SECOND:초, MINUTE:분, HOUR:시, DAY:일, WEEK:주, MONTH:월, QUARTER:분기, YEAR:연)

ADD_MONTHS(SYSDATE, -3) -> DATE_ADD(NOW(), INTERVAL - 3 MONTH) 또는 TIMESTAMPADD(MONTH,-3,CURRENT_TIMESTAMP)
TO_NUMBER('100') -> CAST('100' AS UNSIGNED)
TRUNC(SYSDATE) -> TRUNCATE(NOW(),1)
MONTHS_BETWEEN(startdate, enddate) -> TIMESTAMPDIFF(MONTH, startdate, enddate)
IF(A.UP_FLD_CD = 0, GRP_SEQ, UP_FLD_CD) AS upfolderCode, -> CASE A WHEN '1' THNE B ELSE END AS upfolderCode,
DBMS_RANDOM.VALUE -> RAND()
SYS_CONNECT_BY_PATH -> GROUP_CONCAT(GRP_nm SEPARATOR ',')
GROUP BY ROLLUP(A.REG_DTM) -> GROUP BY A.REG_DTM WITH ROLLUP
DBMS_RANDOM.STRING('X',#{length}) -> CONVERT(TRUNCATE(RAND() * CAST(CONCAT(1,LPAD(0,(#{length} - 1),'0')) AS UNSIGNED),0),CHAR)


# Doamin Class 변수 생성
WITH DATA_TYPE_TXT AS (
						SELECT 'int' AS DATA_TYPE, 'Integer' AS JAVA_DATA_TYPE FROM DUAL UNION ALL
						SELECT 'bigint' AS DATA_TYPE, 'Long' AS JAVA_DATA_TYPE FROM DUAL UNION ALL
						SELECT 'smallint' AS DATA_TYPE, 'Long' AS JAVA_DATA_TYPE FROM DUAL UNION ALL
						SELECT 'char' AS DATA_TYPE, 'String' AS JAVA_DATA_TYPE FROM DUAL UNION ALL
						SELECT 'varchar' AS DATA_TYPE, 'String' AS JAVA_DATA_TYPE FROM DUAL UNION ALL
						SELECT 'longtext' AS DATA_TYPE, 'String' AS JAVA_DATA_TYPE FROM DUAL UNION ALL
						SELECT 'timestamp' AS DATA_TYPE, 'String' AS JAVA_DATA_TYPE FROM DUAL
					  )
SELECT CONCAT(
              'private '
			, A.JAVA_DATA_TYPE
			, ' '
			, A.COLUMN_NAME_VAL
			, ';		//'
			, A.COLUMN_COMMENT
			) AS SCRIPT_TEXT
FROM (	  
		SELECT A.JAVA_DATA_TYPE
			  , CASE WHEN INSTR(COLUMN_NAME_VAL, '_') = 0 THEN COLUMN_NAME_VAL
		             ELSE CONCAT(SUBSTRING(COLUMN_NAME_VAL, 1, INSTR(COLUMN_NAME_VAL, '_') - 1)
		                       , UPPER(SUBSTRING(COLUMN_NAME_VAL, INSTR(COLUMN_NAME_VAL, '_') + 1, 1))
		                       , SUBSTRING(COLUMN_NAME_VAL, INSTR(COLUMN_NAME_VAL, '_') + 2) )
		        END COLUMN_NAME_VAL
			  , A.COLUMN_COMMENT	  
			  , A.ORDINAL_POSITION
		FROM (
				SELECT A.JAVA_DATA_TYPE
					  , CASE WHEN INSTR(COLUMN_NAME_VAL, '_') = 0 THEN COLUMN_NAME_VAL
				             ELSE CONCAT(SUBSTRING(COLUMN_NAME_VAL, 1, INSTR(COLUMN_NAME_VAL, '_') - 1)
				                       , UPPER(SUBSTRING(COLUMN_NAME_VAL, INSTR(COLUMN_NAME_VAL, '_') + 1, 1))
				                       , SUBSTRING(COLUMN_NAME_VAL, INSTR(COLUMN_NAME_VAL, '_') + 2) )
				        END COLUMN_NAME_VAL
					  , A.COLUMN_COMMENT	  
					  , A.ORDINAL_POSITION
				FROM (
						SELECT COLUMN_NAME 
						     , CASE WHEN INSTR(COLUMN_NAME, '_') = 0 THEN LOWER(COLUMN_NAME)
						            ELSE CONCAT(SUBSTRING(LOWER(COLUMN_NAME), 1, INSTR(LOWER(COLUMN_NAME), '_') - 1)
						                      , UPPER(SUBSTRING(LOWER(COLUMN_NAME), INSTR(LOWER(COLUMN_NAME), '_') + 1, 1))
						                      , SUBSTRING(LOWER(COLUMN_NAME), INSTR(LOWER(COLUMN_NAME), '_') + 2) )
						       END COLUMN_NAME_VAL
						     , ( SELECT JAVA_DATA_TYPE FROM DATA_TYPE_TXT WHERE DATA_TYPE = A.DATA_TYPE) AS JAVA_DATA_TYPE
						     , COLUMN_COMMENT 
						     , ORDINAL_POSITION
						FROM INFORMATION_SCHEMA.COLUMNS A
						WHERE TABLE_NAME = 'TB_SURVEY' /* 테이블 명 */
						AND COLUMN_NAME NOT IN ('REG_NO', 'REG_DT', 'UPD_NO', 'UPD_DT') /* BASE DOMAIN 제외 */
					) A
			) A			
	) A
ORDER BY A.ORDINAL_POSITION 
;