Maria DB

-- root 비번 잊었을 경우(2020.05.01 추가)

 

-- pom.xml 설정
<!-- MariaDB 연동을 위해 아래 dependency 추가 --> 
		<dependency>
			<groupId>org.mariadb.jdbc</groupId>
		    	<artifactId>mariadb-java-client</artifactId>
		    	<version>2.4.1</version>
		</dependency>
        
<!--
for MariaDB : 사진에서 주석으로 막혀있던 dependency중 아래는 삭제

        <dependency>
            <groupId>ojdbc</groupId>
            <artifactId>ojdbc</artifactId>
            <version>14</version>
            <scope>system</scope>
            <systemPath>${basedir}/src/main/webapp/WEB-INF/lib/ojdbc-14.jar</systemPath>
        </dependency>
       -->
       
<!--
		for MariaDB : 아래의 메모리DB dependency는 삭제
		<dependency>
			<groupId>org.hsqldb</groupId>
			<artifactId>hsqldb</artifactId>
			<version>2.3.2</version>
		</dependency>
		 -->
       
-- context-datasource.xml 설정
<!-- for MariaDB  -->
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="org.mariadb.jdbc.Driver"/>
        <property name="url" value="jdbc:mariadb://127.0.0.1:3306/NEPP" />
        <property name="username" value="scott"/>
        <property name="password" value="tiger"/>
    </bean> 
        

-- 오류 해결 java.sql.SQLException: No database selected

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
  <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
  <property name="url" value="jdbc:mysql://localhost:3306/이부분에 스키마 이름"></property>
  <property name="username" value="유저명"></property>
  <property name="password" value="비번"></property>
</bean>
    
-- 권한 관련 오류
사용자 계정에 대한 관리는 mysql database 에서 합니다.
mysql>use mysql;
-- 사용자 계정 생성
mysql> create database database명;
mysql> grant all privileges on database명.* to 아이디@localhost
identified by '비밀번호' with grant option;
mysql> FLUSH PRIVILEGES;

-- Incorrect integer value: '' for column ★★★★★★★★★★
my.ini 파일에서...
[mysqld]
sql-mode="EMPTY_STRING_IS_NULL"

입력 후 저장 재시작 해결...
-> 이렇게 하면 빈값 조회시 null로 되므로 조회가 안됨!!!!!!!!!!!!!!
 
 -> NULLIF(#{DEPT_ISSUPPORTER}, ''), 
 로 ''빈값을 NULLIF로 해서 ''일경우 NULL, 아닐경우 해당값을 리턴하는 것으로 해결!!!

 

마리아 DB vs. Oracle 문법 비교

기능	MySQL(MariaDB)	Oracle
Null일 경우 값 처리	IFNULL('컬럼 값', '대체 값')	NVL('컬럼 값', '대체 값')
문자열 합치기	CONCAT('합칠 값1', '합칠 값2', '합칠 값3' )	CONCAT('합칠 값1', '합칠 값2') - 오라클의 경우 값 2개까지만 병합가능  또는  '합칠 값1'||'합칠 값2'||'합칠 값3'
데이터 1개만 보기	LIMIT 1	WHERE ROWNUM = 1  (where 조건 절 안에서)
시스템 현재 시간	NOW()	SYSDATE
alias 사용법	as 'alias 명' 또는 alias 명 또는 as alias 명	as alias명 또는 alias 명(자동 대문자 전환)  대소문자 구분한 값을 보고 싶을 경우,  as "alias 명" 또는 "alias 명"
날짜형식 변환	DATE_FORMAT(NOW(), '%Y%m%d')	TO_DATE(SYSDATE, 'YYYYMMDD')
날짜 표기	'%Y-%m-%d %H:%s'(년도-월-날 시간:분)	'YYYY-MM-DD HH24:MI'
IF문 활용	IF(조건식 demoTable.empSeq >= 1234,  참이면 반환할 값, 거짓이면 반환할 값)	DECODE(조건식, 일치해야하는 조건값,  참이면 반환할 값, 거짓이면 반환할 값)  DECODE는 equal(즉, 등호)만 지원할 수 있   다.
다중 조건	CASE          WHEN '비교할 조건1' THEN '반환할 값'          WHEN '비교할 조건2' THEN '반환할 값2'          ELSE '그밖의 조건으로 반환할 값'    END

 

참조
  * http://sarc.io/index.php/mariadb/605-dbms-migration-oracle-to-mysql-mariadb
  * http://blog.naver.com/PostView.nhn?blogId=sensate1024&logNo=220439813849&parentCategoryNo=&categoryNo=75&viewDate=&isShowPopularPosts=true&from=search
  * http://www.sqlines.com/oracle-to-mariadb ==> Open Source Tool

1. 기본적인 차이에 대한 이해

  * Oracle : Multi-process 방식
  * MySQL : Single-process, Multi-thread  방식
 
2. 메타데이터

  * MySQL :  사용자 인증 정보, 스토어드 프로그램, 이벤트 정보, 복제 관련 정보
     ex) mysql.user
  * INFORMATION_SCHEMA : 메모리 내의 메타 정보를 테이블 형태로 접근할 수 있다.
     ex) information_schema.tables, information_schema.innodb_trx
  * PERFORMANCE_SCHEMA : MySQL 서버가 쿼리 처리 시 발생하는 각종 이벤트 및 잠금 현상 등을 숫자로 기억하는 테이블 저장 공간

 3. SQL 전환

  * 타입
    - NUMBER -> INT
    - VARCHAR2 -> VARCHAR
    - DATE -> DATETIME (참고로 SQL Server도 DATETIME)
 
  * 함수
    - NVL -> IFNULL
      ex) SELECT IFNULL(LAST_IP,'XXX') FROM CO_CD_ITEMCD_0;

    - NVL2(expr, expr1, expr2) -> CASE
      - expr의 값이 null이 아닐 경우 expr1값 리턴, null 인경우 expr2 리턴
      ex) Oracle: SELECT NVL2(MENU_ID, 'Y','N') AS NVL2 FROM MENU ;
            MariaDB: SELECT CASE
                                  WHEN MENU_ID IS NULL THEN 'N'
                                  ELSE 'Y'
                              END AS NVL2
                            FROM MENU

    - SYSDATE -> NOW()
      ex) Oracle: SELECT SYSDATE FROM DUAL;
            MariaDB: SELECT NOW(); 또는 SELECT SYSDATE();

    - TRUNC -> CURDATE, MySQL에서 TRUNC 하면 시간이 없는 일자만 나옴.
      ex) Oracle: SELECT TRUNC(SYSDATE) FROM DUAL;
            MariaDB: SELECT CURDATE() ;

    - DECODE -> CASE

    - TO_NUMBER -> CAST

    - CAST
      - CAST('' AS VARCHAR(1)) 사용 불가
      - CAST('' AS CHAR) 수정

    - DECODE()
      - MariaDB에서 DECODE는 암호화 예약어로 쓰임
      - CASE문으로 대체해서 사용해야 함
 
  * 시퀀스
    - 지원하지 않는다. 직접 함수를 만들어 사용해야 한다.
      - 혹은 AUTO_INCREMENT 속성 컬럼 사용
    - MariaDB 10.3 이후 지원
      - CREATE, ALTER SEQUENCE statements

	Oracle		MariaDB
1	CREATE SEQUENCE seqname		CREATE [OR REPLACE] SEQUENCE seqname
2	ALTER SEQUENCE seqname		ALTER SEQUENCE [IF EXISTS] seqname
3	INCREMENT BY num	Positive or negative increment, default is 1	INCREMENT BY num
4	START WITH num	Initial value	START WITH num
5	MAXVALUE num	Maximum value is num	MAXVALUE num
	NOMAXVALUE	System limit	NOMAXVALUE
6	MINVALUE num	Minimum value is num	MINVALUE num
	NOMINVALUE	System limit	NOMINVALUE
7	CYCLE	Reuse values after reaching the limit	CYCLE
	NOCYCLE	No reuse, this is default	NOCYCLE
8	CACHE num	Cache num values, default is 20	CACHE num	Default is 1000
	NOCACHE	Values are not preallocated	NOCACHE
9	ORDER	Guarantee numbers in order of requests	Option not supported, commented
	NOORDER	No guarantee, this is default	Option not supported, removed as it is default

      - Referencing sequence values:

	Oracle		MariaDB
1	seqname.CURRVAL	The current value of seqname	NEXTVAL(seqname)	NEXT VALUE FOR seqname
2	seqname.NEXTVAL	The next value of seqname	LASTVAL(seqname)	PREVIOUS VALUE FOR seqname

      - DROP SEQUENCE statements:
 

	Oracle	MariaDB
1	DROP SEQUENCE seqname	DROP SEQUENCE [IF EXISTS] seqname

 * 힌트
    - 다 지운다. 있어도 무시된다.
 
 * rownum
    - 조건에 rownum을 사용하고 있던 경우는 limit으로 대체

      SELECT * FROM ${TABLE_NAME} LIMIT 4,10;
      해석: 5번째 레코드부터 10개의 결과 추출, 시작이 0임에 유의하자.
 
  * 날짜 관련
    - TO_CHAR -> DATE_FORMAT()
      - DATE_FORMAT(): 데이터형을 문자로 변환
        ex) Oracle: SELECT TO_CHAR(SYSDATE, 'MMDDYYYYHH24MISS')
                         FROM DUAL ;
              MariaDB: SELECT DATE_FORMAT(now(),'%m%d%Y%H%i%s') ;
       ( '%Y%m%d' 대소문자 주의!!! Y는 4자리, y는 2자리, %m은 12기준 %M은 24기준 )
       ( now()함수는 SYSDATE()함수로도 사용가능 )
        ex) TO_CHAR(SYSDATE, 'YYYYMMDD') -> DATE_FORMAT(NOW(), '%Y%d%m')
      - LPAD(): 숫자를 문자로 변환하고 앞자리 공백대신 문자넣기
        ex) Oracle: SELECT TO_CHAR(1, 'FM00') FROM DUAL;
              MariaDB : SELECT LPAD(1, 2, '0') ;
              -> LPAD()함수는 오라클과 사용방법이 동일하다.
    - TO_DATE() -> STR_TO_DATE()
      ex) Oracle: SELECT TO_DATE('10/30/2012 00:00:00'
                                            , 'MM/DD/YYYYHH24:MI:SS')
                      FROM DUAL ;
            MariaDB: SELECT STR_TO_DATE('10/30/2012 00:00:00'                                                   , '%m/%d/%Y %H:%i:%s') ;
 
  * MERGE INTO
    - 쿼리문을 풀어서 수행한다.

  * START WITH CONNECT BY
    - WITH문을 이용한 RECURSIVE 쿼리로 변경
 
  * 서브쿼리
    - 서브쿼리에 alias가 없다면 붙여야 한다.
 
  * DELETE
    - DELETE [테이블명]으로 쓰이고 있는 부분이 있다면 DELETE FROM [테이블명] 으로 수정해야 한다.
 
  * OUTER JOIN
    - (참고) OUTER JOIN은 INNER JOIN을 확장한 것으로 공통 컬럼 값이 NULL이더라도 조회 결과에 포함된다.
    - MySQL에서는 LEFT OUTER JOIN ... ON, RIGHT OUTER JOIN ... ON 과 같이 ANSI SQL 타입으로 표기해야 한다.

1) LEFT OUTER JOIN
SELECT A.*, B.* FROM A, B WHERE A.ID = B.ID(+);
    - 다음과 같이 변경한다.
SELECT A.*, B.* FROM A LEFT OUTER JOIN B ON A.ID = B.ID;
2) RIGHT OUTER JOIN
SELECT A.*, B.* FROM A, B WHERE A.ID(+) = B.ID;
    - 다음과 같이 변경한다.
SELECT A.*, B.* FROM A RIGHT OUTER JOIN B ON A.ID = B.ID;
 
  * 테이블 확인
    - Oracle은 다음과 같이 사용한다.
SELECT COUNT(*) TABLE_COUNT FROM ALL_TABLES WHERE TABLE_NAME = [테이블명]
    - MySQL에서는 다음과 같다.
SELECT COUNT(*) TABLE_COUNT FROM information_schema.tables
WHERE table_schema = [DB명] AND table_name = [테이블명]

 

 

Oracle쿼리문을 MySQL쿼리로 변환 방법
[출처] Oracle쿼리문을 MySQL쿼리로 변환 방법|작성자 강철고양이





1. NVL 함수를  IFNULL 함수로 변경한다.  

2. SYSDATE 함수를 NOW() 함수로 변경한다.  

3. trunc(sysdate)를 하면 시간만 잘린 일자가 나온다.  
MySQL에서 날자만 반환해주는 CURDATE()를 쓴다.

4. Sequence는 사용자 함수를 만들어 사용한다.
사용자 함수 만드는 방법
http://blog.naver.com/steelcatx/60184547400

SELECT zon_sq01.nextval FROM dual; -> SELECT get_next_seq_val('zon_sq01') FROM dual;
SELECT zon_sq01.currval FROM dual; -> SELECT get_curr_seq_val('zon_sq01') FROM dual;

sequence 추가가 필요할시 mysql_seq_table에 seq_nm(시퀀스명), last_cached_val(마지막 사용된 값)를 입력한다.

5. 인덱스 힌트 제거한다. 
있더라도 큰 영향 없으므로 대상에서 제외

6. Date 함수 관련  
6-1. TO_CHAR()를 DATE_FORMAT()으로 변환한다.
TO_CHAR(sysdate,'MMDDYYYYHH24MISS')의 경우
DATE_FORMAT(now(),'%m%d%Y%H%i%s')로 변환 -- '%Y%m%d' 대소문자 주의!!! Y는 4자리, y는 2자리, %m은 12기준 %M은 24기준

6-2. 요일 변경
자바스크립트의 요일은(0-6), MySQL의 요일은(0-6), ORACLE의 요일은(1,7)
그러므로 자바스크립트와 oracle의 요일차이로 TO_CHAR(SYSDATE-1, 'D')를 
그대로 변경 시키면 아래구문이지만 
=> DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 DAY), '%w')

MySQL에서는 -1을 할 필요가 없으므로 
=> DATE_FORMAT(NOW(), '%w')

6-3. TO_DATE()를 STR_TO_DATE()으로 변환한다.
TO_DATE('10/30/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')의 경우 
=> STR_TO_DATE('10/30/2012 00:00:00', '%m/%d/%Y %H:%i:%s')로 변환

TRUNC(to_date('20130227', 'YYYYMMDD')+30) 의 경우
=> DATE_ADD(str_to_date('20130227', '%Y%m%d'), INTERVAL 30 DAY) -- '%Y%m%d' 대소문자 주의!!! Y는 4자리, y는 2자리, %m은 12기준 %M은 24기준

6-4. 날짜 가감 
oracle은 날짜형 데이터에 +1을 하면 1일이 추가되나 mysql은 이상하게 변함.
sysdate + 1
=> DATE_ADD(now(), interval 1 DAY)

DATE_ADD(date,INTERVAL expr  unit) -- 날짜 가산
DATE_SUB(date,INTERVAL expr  unit) -- 날짜 감산
DATE_ADD, DATE_SUB를 사용 할 경우 '0000-00-00 00:00:00'나 '0000/00/00 00:00:00'의 형태만 맞춰 주면 문자도 자동으로 변경하여 연산됨.

unit Value  expr 형태  
MICROSECOND  MICROSECONDS
SECOND   SECONDS
MINUTE   MINUTES
HOUR   HOURS
DAY   DAYS
WEEK   WEEKS
MONTH   MONTHS
QUARTER   QUARTERS
YEAR   YEARS
SECOND_MICROSECOND 'SECONDS.MICROSECONDS'
MINUTE_MICROSECOND 'MINUTES:SECONDS.MICROSECONDS'
MINUTE_SECOND  'MINUTES:SECONDS'
HOUR_MICROSECOND 'HOURS:MINUTES:SECONDS.MICROSECONDS'
HOUR_SECOND  'HOURS:MINUTES:SECONDS'
HOUR_MINUTE  'HOURS:MINUTES'
DAY_MICROSECOND  'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS'
DAY_SECOND  'DAYS HOURS:MINUTES:SECONDS'
DAY_MINUTE  'DAYS HOURS:MINUTES'
DAY_HOUR  'DAYS HOURS'
YEAR_MONTH  'YEARS-MONTHS'

예)
SELECT DATE_ADD('1900-01-01 00:00:00', INTERVAL '-1 10' DAY_HOUR); 
-> '1899-12-30 14:00:00' -- 34시간이 감산됨
SELECT DATE_ADD('1900-01-01 00:00:00', INTERVAL '1 -10' DAY_HOUR); 
-> '1900-01-02 10:00:00' -- 34시간이 가산됨

6-5. 날짜와 날짜의 차이 구하기
SELECT to_date('20130314', 'YYYYMMDD')- to_date('20130313', 'YYYYMMDD') FROM DUAL -- 1일 이 조회됨
-> SELECT TO_DAYS(str_to_date('20130314', '%Y%m%d'))- TO_DAYS(str_to_date('20130313', '%Y%m%d')) FROM DUAL


7. Outerjoin 변경 
[Oracle]
left outer joing : SELECT t1.*, t2.* FROM t1, t2 where t1.i1 = t2.i2(+);
right outer joing: SELECT t1.*, t2.* FROM t1, t2 where t1.i1(+) = t2.i2; 

[MySQL] 
left outer joing : SELECT t1.*, t2.* FROM t1 LEFT OUTER JOIN t2 ON t1.i1 = t2.i2;
right outer joing: SELECT t1.*, t2.* FROM t1 RIGHT OUTER JOIN t2 ON t1.i1 = t2.i2;

% From절에서 테이블명 중간에 , 가 없다는것과 ON 구문이 From절 바로 뒤에 오게 
해야하는것에 주의 

8. rownum 변환
8-1. rownum이 조건으로 사용될 경우 limit를 사용
rownum >= 5 와 rownum <= 10 이면 limit 4, 5
ex) 리스트 페이징에서 11번째부터 20번째까지 limit 10, 10
처음부터 10개  ... LIMIT 10
5번째부터 10개  ... LIMIT 4, 10
5번째부터 마지막까지 ... LIMIT 4, -1

이때 주의 할 점은 첫번째 레코드의 시작은 “0”
즉 “LIMIT 1, 3 - 2번째부터 3개” 의 결과를 출력
예)
select * from table LIMIT 1, 3;
8-2. rownum이 컬럼으로 사용될 경우 변수 사용
select @rownum := @rownum + 1 as rownum
, table.* 
from table
JOIN (select @rownum := 0) r;
8-3. rownum이 조건 및 컬럼으로 모두 사용될 경우 2.를 적용한 서브쿼리로 작성
select *
from (
select @rownum := @rownum + 1 as rownum
, table.* 
from table
JOIN (select @rownum := 0) r
)
where rownum < 3;

9. Subquery에서 alias가 없다면 붙인다.  
select a, b from ( select a, b from ttt)  
--> select t.a, t.b from ( select a, b from ttt) t

10. DELETE 테이블명 쿼리문을 DELETE FROM 테이블명 쿼리문으로 바꾸어준다. 
oracle에서는 FROM을 생략해도 되지만 MySQL에서는 반드시 필요

11. Merge into의 경우는 쿼리문을 분리하여 처리로직으로 변경해준다. 
변경방법은 아래 두가지중에 하나를 선택해도 되고 다른 방법을 써도 된다.
11-1 조건이 맞는지(MATCHED)에 대한 SELECT문을 실행시켜서 결과값이 있으면 UPDATE문을 실행시켜 주고 결과값이 없으면 INSERT문을 실행시켜준다.

11-2 UPDATE문을 실행해서 UPDATE가 일어나면 그대로 가고 UPDATE가 일어나지 않으면 INSERT문을 실행시킨다. 

12. decode문 case로 변경
decode( 1,1,'true','false') -> case when 1=1 then 'true' else 'false' end

13. start with connet by 문은 with를 사용한 recursive 쿼리로 변경 
recursive 쿼리 작성 방법
http://blog.naver.com/steelcatx/60186185102

14. mysql은 대소문자 구분함. 모든 컬럼명과 테이블명의 대소문자 확인 

15. 오라클의 ||(Vertical Bar) 사용할 수없음. CONCAT으로 변경
TO_CHAR(NOW(), 'yymmdd') || LPAD(get_next_seq_val('ord_cl_info_sq01'), 9, '0')
--> CONCAT(DATE_FORMAT(NOW(), '%y%m%d') ,LPAD(get_next_seq_val('ord_cl_info_sq01'), 9, '0'))

16. TO_CHAR, TO_NUMBER -> CAST로 변경 
SELECT TO_CHAR( 11 ) AS AA FROM DUAL
--> SELECT CAST( 11 AS CHAR ) AS AA FROM DUAL
SELECT TO_NUMBER( '-11' ) AS AA FROM DUAL
--> SELECT CAST( '-11' AS SIGNED ) AS AA FROM DUAL


출처: https://stepping.tistory.com/22?category=691571 [디딤돌]