공식 문서 참고 : https://mariadb.com/kb/en/authentication-from-mariadb-104/
하여 작성을 하였으며, MariaDB 10.4 이상부터는 작업을 보다 쉽고 직관적으로 만들기 위해 인증 프로세스에 여러 변경 사항을 도입했다고 합니다. 그중 가장 이슈가 되는 부분은 바로 ROOT 패스워드 입력 없이 Unix_socket 방식을 이용하여 바로 DB에 접속하는 방식을 말합니다. 먼저 이것을 설명하기 위해 바로 테스트를 진행해보도록 하겠습니다.

[테스트 환경]
□ OS : Rocky Linux release 8.5 (Green Obsidian)
□ DB : 10.3.28-MariaDB
 DB : 10.4.24-MariaDB [10.4.X 이상]

 DB : 10.6.7-MariaDB


◇ 먼저 설치 직 후에는 패스워드가 설정되어 있지 않기 때문에 플러그인 상관없이 바로 접근이 됩니다.

◇ 보안 및 패스워드 설정을 위해 [# mysql(mariadb)-secure-installation] 실행의 차이점

이후 아래와 같이 각 버전에 루트 인증을 시도해보면 다음과 같은 결과가 나옵니다.

 

■ MariaDB 10.3 버전에서 루트(Root)로그인 인증

[root@Rocky85 ~]# mysql -u root -p
Enter password: [패스워드 없이 엔터만 누를 경우]

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) [인증 불가]

■ MariaDB 10.4 이상 버전에서 루트(Root)로그인 인증[Unix_socket 방식]

[root@Rocky85 ~]# mysql -u root -p

Enter password: [패스워드 없이 엔터만 누를 경우]

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 19
Server version: 10.4.24-MariaDB MariaDB Server [패스워드 인증 없이 바로 DB 접속]

 

◇ 아래는 로그인 인증 플러그인 비교

여기서 위 결과값을 비교를 해보았을 때 이상한 부분이 보이지 않나요? 네.. 바로 10.4.X 버전의 Plugin 부분을 보면 [mysql_native_password]으로 패스워드 인증 방식으로 되어 있지만 막상 [# mysql -u root -p]해보면 패스워드 없이 엔터만 눌러도 바로 DB로 접속되는 것을 확인할 수 있습니다.

 

문서를 자세히 보면 왜 이렇게 되는지 이유를 알 수 있는데요.

※(중요) 모든 사용자 계정, 암호, 인증방식 권한은 이제 [ mysql.global_priv ] 테이블에 저장됩니다.

[ mysql.user ]테이블은 여전히 존재하며, 이전과 정확히 동일한 컬럼 집합을 가지고 있지만 이제는

[ mysql.global_priv ]테이블을 참조하는 뷰(View)를 하는 역할로 바뀌게 됩니다.

[설명]

원래 이 기술은 Otto Kekäläinen개발자 분께서 Debian MariaDB 패키지에서 개척했으며 이미 MariaDB 10.0부터 리눅스 데비안[Debian]에서 사용되어 왔다고 합니다. CentOS/RedHat 배포판에서는 10.4 버전부터 적용을 하기 시작했습니다. [ Unix_socket ]을 사용하는 것은 시스템 ROOT 사용자인 경우 암호 없이 root@localhost로 로그인할 수 있음을 의미합니다. 그럼 왜 이렇게 적용을 했을까요?

 

① 콘솔에서 DB에 접속하는 것은 시스템 ROOT 권한이 있는 사용자가 내부(Localhost)에만 접속을 하므로 패스워드에는 의미가 없다는 뜻

② 패스워드가 없다는 뜻은 MariaDB ROOT 암호 재설정에 대한 설명서가 필요하지 않음, 즉 잊어버릴 염가 없습니다.

③ 암호가 없으니 일반 텍스트로 암호를 저장할 필요가 없어 노출될 일이 없다.

④ 접속하기 편하다. [ # sudo mysql ] 명령어를 통해 바로 접속 할 수 있다.

 

가 개발자의 뜻이지만.. 과연 보안담당자들은 어떻게 볼까요. 우리가 데이터베이스에 접근을 할 때 서버가 내부 환경이면 문제가 없겠지만 웹이나 DB서버가 분리가 되어 있을 경우는 어쩔 수 없이 외부 아이피에서 접근을 해야 하는 경우가 있습니다. 보안에 취약하여 서버의 ROOT가 탈취를 당하면 데이터베이스도 자유롭게 접근이 가능하므로 이것 자체를 취약점로 봅니다.


■ 이전 패스워드 인증 밥벙으로 설정하기

이미 MariaDB를 설치 하고 root@localhost 사용자 계정이 이미 unix_socket 인증을 사용하는 경우

MariaDB [mysql]> ALTER USER root@localhost IDENTIFIED VIA mysql_native_password USING PASSWORD("패스워드");

※ 또하나의 방법은 [ mysql_install_db (mariadb-install-db) ]를 이용한 방법입니다. 하지만 MariaDB 설치 직후 바로 실행하면 자동으로 계정을 생성하기 때문에 제외하였습니다.

문서 자료 : https://mariadb.com/kb/en/mysql_install_db/


■ 다시 [ Unix_socket ] 인증 방식으로 설정하기

MariaDB [mysql]> ALTER USER root@localhost IDENTIFIED VIA unix_socket;


■ 원격 서버[클라이언트 프로그램]에서 MariaDB접속 시 반응

① 원격 호스트 IP를 추가하고 Unix_socket 설정했을 때 암호 없이 접근이 가능한지 테스트

MariaDB [mysql]> grant all privileges on *.* to 'root'@'원격지IP' identified by '패스워드';

MariaDB [mysql]> ALTER USER root@원격지IP IDENTIFIED VIA unix_socket;

 

② 패스워드 인증으로 변경 후 접속 테스트

MariaDB [mysql]> ALTER USER root@192.168.150.1 IDENTIFIED VIA mysql_native_password USING PASSWORD("패스워드");

[결론]

원격은 무조건 패스워드 인증으로만 접근이 가능하다.


■ 실수로 ROOT 권한에 문제가 생기거나 패스워드를 잊어버렸을 경우

◇ ROOT 계정을 삭제했을 경우[예시]

MariaDB [mysql]> drop user root@localhost;

만약 다른 계정 중에 모든 권한이 있는 일반 계정이 있다면 우회 접속하여 다시 생성하면 되겠지만, ROOT계정만으로 관리를 했다면 지금과 같은 곤란한 상황이 발생할 수 있습니다.

복원을 하기 위해서는 MariaDB를 실행을 할 때 [ --skip-grant-tables ] 이용합니다. 리눅스 싱글 부팅하는 것과 비슷한 원리입니다. 다만 우리는 대부분 RPM으로 설치하면 기본적으로 [ systemctl ]을 이용하여 MariaDB를 중지 실행을 하기 때문에 실제 실행 위치를 모를 수 있습니다.

[root@Rocky85 mysql]# systemctl status mariadb  [스테이터스를 이용하여 스크립트 실행 로드 확인]

Loaded : /usr/lib/systemd/system/mariadb.service

[root@Rocky85 mysql]# vi /usr/lib/systemd/system/mariadb.service  [스크립트 파일 열기]

에서 ExecStart 부분을 확인해보면 어떤 경로에서 Mariadb를 실행하는지 알 수 있습니다.

예외상황 발생①

[root@Rocky85 mysql]# /usr/sbin/mysqld --skip-grant-tables & [실행 오류]
2022-03-23 22:39:53 0 [Note] /usr/sbin/mysqld (mysqld 10.4.24-MariaDB) starting as process 1889 ...
/usr/sbin/mysqld: Please consult the Knowledge Base to find out how to run mysqld as root!
2022-03-23 22:39:53 0 [ERROR] Aborting

아.. 이렇게 하면 권한 때문에 실행이 안됩니다. 기본 설치 data경로가 [/var/lib/mysql/] mysql 권한으로 실행되므로 다음과 같이 시스템 권한을 mysql 유저 권한으로 변경합니다. 혹시나 Passwd를 확인하여 mysql 계정이 로그인이 가능한지 확인합니다.

[root@Rocky85 ~]# cat /etc/passwd | grep mysql
mysql:x:27:27:MySQL Server:/var/lib/mysql:/sbin/nologin  [nologin을 /bin/bash로 변경]

 

[root@Rocky85 ~]# vi /etc/passwd

[root@Rocky85 ~]# su - mysql  [시스템 권한 유저(mysql)로 로그인]
[mysql@Rocky85 ~]$ /usr/sbin/mysqld --skip-grant-tables &  [실행 오류]

예외상황 발생②

2022-03-24  0:13:28 0 [ERROR] InnoDB: Missing MLOG_CHECKPOINT at 61021 between the checkpoint 61021 and the end 61030.
2022-03-24  0:13:28 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error

테스트한다고 강제로 죽였다가 살렸다가 반복했더니 ib_logfile 데이터가 충돌 나는 문제가 발생했습니다.

그렇게 심각한 문제는 아니므로 다음과 같이 조치를 합니다.
데이터 파일이 있는 경로에 ib_logfile을 이름을 변경(권고), 또는 삭제를 합니다.

[mysql@Rocky85 ~]$ cd /var/lib/mysql/

[mysql@Rocky85 ~]$ mv ib_logfile0 ib_logfile0.bak

[mysql@Rocky85 ~]$ mv ib_logfile1 ib_logfile1.bak

 

DB실행

[mysql@Rocky85 ~]$ /usr/sbin/mysqld --skip-grant-tables &  [MariaDB 실행]

아래와 같이 정상적으로 Mysql이 실행되신 것을 확인할 수 있습니다.

※ 세팅 방법에 따라 실행 권한이나 구동 방법이 다를 수 있습니다!

이제 다시 MariaDB 접속을 시도합니다.

[mysql@Rocky85 ~]$ mysql -u root -p

Enter password: [패스워드 미입력(엔터)]

 

MariaDB [(none)]> FLUSH PRIVILEGES; [접속 후 바로 해당 명령어 입력]

MariaDB [(none)]> grant all privileges on *.* to 'root'@'localhost' identified by '패스워드';

이제 종료를 하고 기존에 실행했던 MariaDB를 중지하고 시스템 ROOT권한으로 돌아온 후에 정상적인 방법으로 다시 구동하여 root@localhost 접속이 되는지 확인합니다. 혹시나 DB가 중지가 되지 않으면 강제 종료[ kill ] 한 후에 시도를 하면 됩니다.

※ 10.6.X 버전에서도 동일하게 되는 것을 확인하였습니다.


MariaDB가 아무리 Mysql기반으로 개발이 되었지만, 계속 업데이트하면서 변하는 부분이 생기네요. 덕분에 일주일 동안 많은 시행착오를 겪으면서 좋은 경험을 하게 되었습니다. 계정을 변경하거나 삭제할 경우는 꼭 다른 계정을 생성 또는 기존 다른 유저에게 모든 권한을 부여한 후에 진행하시기 바랍니다.

서버 버전 확인

실행 중인 MySQL 또는 MariaDB 서버 버전에 따라 다른 명령어로 루트 암호를 복구해야 합니다.

다음 명령을 실행하여 서버 버전을 확인합니다.

mysql --version

시스템에 MySQL이 설치되어 있는 경우 출력은 다음과 같습니다.

mysql  Ver 14.14 Distrib 5.7.22, for Linux (x86_64) using  EditLine wrapper

또는 MariaDB에 대해 다음과 같이 출력합니다.

mysql  Ver 15.1 Distrib 10.3.32-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

 

MySQL 또는 MariaDB 루트 비밀번호를 재설정하는 방법

MySQL/MariaDB 루트 비밀번호를 재설정하려면 다음 단계를 수행합니다.

 

1. MySQL/MariaDB 서비스 중지

만일 mysql이나 mariaDB가 기동 중에 있을 경우, 루트 암호를 변경하려면 먼저 MySQL 서버를 중지해야 합니다. 

 

sudo systemctl stop mysql

 

2. 그랜트 테이블을 로드하지 않고 MySQL/MariaDB 서버를 시작합니다.

그랜트 테이블을 로드하지 않고 데이터베이스 서버를 시작합니다:

sudo mysqld_safe --skip-grant-tables &

위의 명령 끝에 있는 &(앰퍼샌드)는 프로그램이 백그라운드에서 실행 되도록 하므로 쉘을 계속 사용할 수 있습니다.

--skip-grant-tables옵션을 사용하면 누구나 암호 없이 데이터베이스 서버에 연결할 수 있습니다.

 

3. MySQL 셸에 로그인

이제 루트 사용자로 데이터베이스 서버에 연결할 수 있습니다.

mysql -u root

 

4. 새 루트 비밀번호 설정

MySQL 5.7.5 및 이전 버전 또는 MariaDB 10.1.20 및 이전 버전인 경우

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MY_NEW_PASSWORD');
FLUSH PRIVILEGES;

 

MySQL 5.7.6 이상 또는 MariaDB 10.1.20 이상인 경우

MariaDB> ALTER USER 'root'@'localhost' IDENTIFIED BY 'NEW_PASSWORD';
MariaDB> FLUSH PRIVILEGES;

 

ALTER USER문이 수행되지 않고 아래와 같이 오류가 발생하면 UPDATE 문을 수행합니다.

MariaDB [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';
ERROR 1290 (HY000): The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement

 

오류 발생 시 아래 SQL문을 수행합니다.

MariaDB> UPDATE mysql.user SET authentication_string = PASSWORD('NEW_PASSWORD')
WHERE User = 'root' AND Host = 'localhost';

MariaDB> FLUSH PRIVILEGES;

 

5. 데이터베이스 서버를 정상적으로 중지 및 시작

이제 루트 암호가 설정되었으므로 데이터베이스 서버를 중지하고 정상적으로 시작합니다.

$ mysqladmin -u root -p shutdown

새 루트 암호를 입력하라는 메시지가 표시됩니다. 암호를 입력하면 데이터베이스 서버를 정상적으로 중지합니다.

 

데이터베이스 기동

MySQL의 경우 다음을 입력합니다.

sudo systemctl start mysql

MariaDB의 경우 다음을 입력합니다.

sudo systemctl start mariadb

 

***  참고 ***

WSL 에서  sudo systemctl start mysql, sudo systemctl start mariadb 명령어로 DB 기동 시 아래 오류가 발생하면

 

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

System has not been booted with systemd as init system (PID 1). Can't operate.
Failed to connect to bus: Host is down

 

아래 명령어로 DB를 기동한다.

sudo /etc/init.d/mysql start

 

 

6. 비밀번호 확인

새 루트 암호가 올바르게 적용되었는지 확인하려면 다음을 입력합니다.

sudo mysql -u root -p

새 루트 암호를 입력하라는 메시지가 표시됩니다. 입력하면 데이터베이스 서버에 로그인 합니다.

SELECT TIME_TO_SEC('05:15:40');

 

Sample Output:

mysql> SELECT TIME_TO_SEC('05:15:40');

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

| TIME_TO_SEC('05:15:40') |

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

|                       18940 |

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

1 row in set (0.02 sec)

 

mysql> SELECT SEC_TO_TIME(18940);

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

|  SEC_TO_TIME(18940)  |

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

|                    05:15:40 |

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

1 row in set (0.02 sec)

 

 

 

참고: https://www.w3resource.com/mysql/date-and-time-functions/mysql-time_to_sec-function.php

SQL 집계함수

SQL에서는 집계함수를 많이 사용합니다.

GROUP BY와 함께 COUNT, SUM 등을 많이 사용하게 됩니다.

 

집계의 예제를 다시 살펴보겠습니다.

다음은 GROUP BY와 COUNT를 사용해, 나라별 인원 집계를 내려본 것입니다.

SELECT Country, COUNT(*)
FROM customers
GROUP BY Country;

 

위 쿼리의 실행 결과 중 상위 5개는 다음과 같습니다.

 

만약 데이터에 숫자인 컬럼이 있었다면, SUM이나 AVG도 사용해 볼 수 있을 것입니다.

 

윈도우 함수

윈도우 함수는 이 GROUP BY와 비슷하지만, 집계가 없는 것이라고 생각하면 됩니다.

행과 행간의 관계를 쉽게 정의 하기 위해 만든 함수입니다.

따라서 SQL에서 지원하는 집계 함수 외에도, RANK나 ROW_NUMBER와 같은 윈도우 전용 함수가 나왔습니다.

 

윈도우 함수는 보통 OVER 괄호 안에 PARTITION BY 또는 ORDER BY를 사용하게 됩니다.

아래는 나라에 따른 인원을 COUNT했지만, 집계는 하지 않은 예제입니다.

SELECT Country, 
    Count(*) OVER (PARTITION BY Country) AS cnt
FROM customers

 

위 쿼리의 결과 중 상위 10개는 다음과 같습니다.

결과를 보면 아시겠지만, Country값이 같으면 cnt가 같습니다.

하지만 GROUP BY를 사용할때와는 다르게, 행을 집계하지는 않았습니다.

 

RANK

앞서 RANK와 ROW_NUMBER는 윈도우 함수 전용이라고 언급했습니다.

위의 결과에 RANK를 적용해 어느 나라의 사람이 많은지 알아보겠습니다.

참고로, 윈도우 함수는 중첩해서 사용할 수는 없습니다.

하지만 서브쿼리는 사용 가능하기에 아래와 같은 예제를 실행해볼 수 있습니다.

SELECT Country, cnt, 
    RANK() OVER (ORDER BY cnt DESC) AS rnk 
FROM(
    SELECT Country, 
        Count(*) OVER (PARTITION BY Country) AS cnt
    FROM customers
) AS counts;

 

FROM안에 있는 SELECT문은 바로 위에 있는 예제입니다.

위 쿼리의 결과 중 상위 10개는 다음과 같습니다.

미국에 Customer가 제일 많아 USA가 최 상위로 나오게 됩니다.

 

위를 조금 더 실용적으로 표현해보기 위해 집계와 윈도우를 함께 사용해보겠습니다.

다음은 내부 윈도우함수를 GROUP BY를 사용한 집계로 바꿔본 것입니다.

SELECT *, RANK() OVER (ORDER BY cnt DESC) AS rnk 
FROM(
	SELECT Country, Count(*) AS cnt
	FROM customers
    GROUP BY Country
) AS counts;

 

위 쿼리의 결과 중 상위 10개는 다음과 같습니다.

 

 

실제 쿼리 사용

SELECT 
       COUNT(*) OVER(PARTITION BY B.r_no, B.r_g_no) -- 2컬럼별 항목 갯수
     , COUNT(*) OVER(PARTITION BY B.r_no) -- 1컬럼별 항목 갯수
  FROM A
  JOIN B ON B.no = A.no AND B.r_no = A.r_no
  JOIN C ON C.no = B.no AND C.r_g_no = B.r_g_no
 WHERE A.no = 1001
   AND A.cstmr_no = 1

 

 

 

 

 

출처: jyoondev.tistory.com/89

// code_se: VARCHAR2(100)

INSERT INTO

    tb_blog(
          blog_no
        , blog_nm
        ...
) VALUES (

          (SELECT cd FROM tb_code sa WHERE code_se = 1)

          , ''

        ...

)

 

 

강제로 Integer를 Insert 문에 넣지 못하는 에러 코드 이다.

 

(SELECT cd FROM tb_code sa WHERE code_se = '1')

로 바꾸면 에러 나지 않는다.

MySQL/MariaDB 사용시 Multi rows insert duplicate key update (merge) 문 예시 입니다.

 

 

INSERT INTO

    temp_table (

       temp_seq

      , name

      , blabla

    ) VALUES

                  (1, '임시이름1', '어쩌고')

                  (2, '임시이름2', '저쩌고')

                  (3, '임시이름3', '블라')

                  (4, '임시이름4', '블라')

    ON DUPLICATE KEY
        UPDATE
            name = VALUES(name)
          , blabla= VALUES(blabla)

          // 하위는 임시
          , updt_no = VALUES(regist_no)
          , updt_dt = VALUES(regist_dt)

;

 

 

참조: stackoverflow.com/questions/2714587/mysql-on-duplicate-key-update-for-multiple-rows-insert-in-single-query

'Database > Mysql, MariaDB' 카테고리의 다른 글

[Mysql] count over PARTITION BY  (0) 2021.04.14
(1292): Truncated incorrect DOUBLE value  (0) 2020.11.19
[Mysql] Explain Plan 보는법  (0) 2020.11.18
[Mysql] Strict mode  (0) 2020.11.17
[MariaDB] sequence 생성 사용법  (0) 2020.11.17

type

조인타입, 아래와 같다. 우수한 순서대로 뒤로갈수록 나쁜 조인형태이다.

  • system
    테이블에 단 하나의 행만 존재(시스템 테이블). const join 의 특수한 경우이다.

  • const
    많아야 하나의 매치되는 행만 존재하는 경우. 하나의 행이기 때문에 각 컬럼값은 나머지 연산에서 상수로 간주되며, 처음 한번만 읽어들이면 되기 때문에 무지 빠르다.
    PRIMARY KEY 나 UNIQUE index 를 상수와 비교하는 경우.
    아래의 경우에서 tbl_name 은 const table 로 조인된다.

    SELECT * FROM tbl_name WHERE primary_key=1;
    SELECT * FROM tbl_name
    WHERE primary_key_part1=1 AND primary_key_part2=2;

  • eq_ref
    조인수행을 위해 각 테이블에서 하나씩의 행만이 읽혀지는 형태. const 타입이외에 가장 훌륭한 조인타입니다.
    조인연산에 PRIMARY KEY 나 UNIQUE index 인덱스가 사용되는 경우.
    인덱스된 컬럼이 = 연산에 사용되는 경우. 비교되는 값은 상수이거나 이전조인결과의 컬럼값일수 있다.
    다음 예에서 MySQL 은 ref_table 을 처리하는데 eq_ref 조인을 사용한다.

    SELECT * FROM ref_table,other_table
    WHERE ref_table.key_column=other_table.column;
    SELECT * FROM ref_table,other_table
    WHERE ref_table.key_column_part1= other_table.column
    AND ref_table.key_column_part2=1;
  • ref
    이전 테이블과의 조인에 사용될 매치되는 인덱스의 모든행이 이 테이블에서 읽혀진다. leftmost prefix 키만을 사용하거나 사용된 키가 PRIMARY KEY 나 UNIQUE 가 아닐때(즉 키값으로 단일행을 추출할수 없을때) 사용되는 조인.
    만약 사용된 키가 적은수의 행과 매치될때 이것은 적절한 조인 타입니다.
    ref 는 인덱스된 컬럼과 = 연산에서 사용된다.
    아래 예에서 MySQL 은 ref_table 처리에 ref 조인 타입을 사용한다.

    SELECT * FROM ref_table WHERE key_column=expr;
    SELECT * FROM ref_table,other_table
    WHERE ref_table.key_column=other_table.column;
    SELECT * FROM ref_table,other_table
    WHERE ref_table.key_column_part1= other_table.column
    AND ref_table.key_column_part2=1;

  • ref_or_nullSELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;See Section 7.2.7, “How MySQL Optimizes IS NULL”.

  • ref 와 같지만 NULL 값을 포함하는 행에대한 검색이 수반된다.
    4.1.1 에서 새롭게 도입된 조인타입이며 서브쿼리 처리에서 대개 사용된다.
    아래 예에서 MySQL 은 ref_table 처리에 ref_or_null 조인타입을 사용한다.
  • index_merge
    인덱스 병합 최적화가 적용되는 조인 타입.
    이 경우, key 컬럼은 사용된 인덱스의 리스트를 나타내며 key_len 컬럼은 사용된 인덱스중 가장 긴 key 명을 나타낸다.
    For more information, see Section 7.2.6, “Index Merge Optimization”.
    unique_subqueryunique_subquery 는 성능향상을 위해 서브쿼리를 단순 index 검색 함수로 대체한다.
    이것은 아래와 같은 몇몇 IN 서브쿼리 처리에서 ref 타입대신 사용된다.

    value IN (SELECT primary_key FROM single_table WHERE some_expr)

  • index_subquery
    unique_subquery 와 마찬가지로 IN 서브쿼리를 대체한다. 그러나 이것은 아래와 같이 서브쿼리에서 non-unique 인덱스가 사용될때 동작한다.

    value IN (SELECT key_column FROM single_table WHERE some_expr)

  • range
    SELECT * FROM tbl_name WHERE key_column = 10; SELECT * FROM tbl_name WHERE key_column BETWEEN 10 and 20; SELECT * FROM tbl_name WHERE key_column IN (10,20,30); SELECT * FROM tbl_name WHERE key_part1= 10 AND key_part2 IN (10,20,30);

    인덱스를 사용하여 주어진 범위 내의 행들만 추출된다. key 컬럼은 사용된 인덱스를 나타내고 key_len 는 사용된 가장 긴 key 부분을 나타낸다.
    ref 컬럼은 이 타입의 조인에서 NULL 이다.
    range 타입은 키 컬럼이 상수와 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN 또는 IN 연산에 사용될때 적용된다.

  • index
    이 타입은 인덱스가 스캔된다는걸 제외하면 ALL 과 같다. 일반적으로 인덱스 파일이 데이타파일보다 작기 때문에 ALL 보다는 빠르다.
    MySQL 은 쿼리에서 단일 인덱스의 일부분인 컬럼을 사용할때 이 조인타입을 적용한다.

  • ALL
    이전 테이블과의 조인을 위해 풀스캔이 된다. 만약 (조인에 쓰인) 첫번째 테이블이 고정이 아니라면 비효율적이다, 그리고 대부분의 경우에 아주 느린 성능을 보인다. 보통 상수값이나 상수인 컬럼값으로 row를 추출하도록 인덱스를 추가함으로써 ALL 타입을 피할 수 있다.

 

참조: m.blog.naver.com/PostView.nhn?blogId=hmoai&logNo=50033300449&proxyReferer=https:%2F%2Fwww.google.com%2F

MySQL 의 CHAR 나  VARCHAR 컬럼에 데이타를 입력할 경우 컬럼 사이즈보다 큰 데이타가 입력됐을때 당연히 DB 차원에서 insert 가 안 되는줄 알았다.

 

그런데 특정 서버에서는 사이즈보다 큰 부분이 truncate 되고 insert 되어 찾아보니 MySQL 서버의 sql_mode 라는 설정의 strict mode에 따라 데이타 검증을 수행하는데 5.5의 경우 기본적으로 이 모드가 꺼져 있다고 한다.

 

MySQL 버전마다 다르겠지만 현재 사용하고 있는 5.5.40 의 기본 sql_mode 설정은 '' 이며 (https://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_sql-mode)

이 경우 STRICT_ALL_TABLES, STRICT_TRANS_TABLES 가 꺼지며 CHAR, VARCHAR 의 크기를 초과하는 데이타 입력시 에러를 내지 않고 잘리게 된다.

 

개인적인 생각으로는 이런 데이타의 무결성을  app 에서 하는것 보다는 DBMS 에서 체크하는게 더 편하고 이를 위해 DBMS 를 쓰는 것이므로 비록 속도가 저하되더라도 STRICT_MODE 로 쓰는게 좋다고 보다.

그러면 STRICT MODE 로 설정을 변경해 보자.

 

 

// my.cnf

[mysqld] sql_mode="NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES"

 

 

STRICT MODE 로 전환되었다면 다음 SQL 문은 오류가 나고 정상적으로 동작하지 않는다.

 

MySQL 5.7 부터는 STRICT_MODE 가 기본 설정이라고 하니 별도의 설정 작업이 필요없다.

 

 

 

참고 url: www.lesstif.com/dbms/mysql-strict-mode-24445406.html

MariaDB version 10.3 이후 에서는

기본적으로 시퀀스 생성을 사용할 수 있다.

 

-- 버전 확인

SELECT VERSION(); // 10.4.12-MariaDB-1:10.4.12+maria~bionic-log

 

-- 시퀀스 생성문

CREATE SEQUENCE seq_template;

 

-- 현재 값
SELECT LASTVAL(seq_template);
-- 다음 값
select NEXTVAL(seq_template);

 

-- 현재 시퀀스 값 초기화
ALTER SEQUENCE seq_template RESTART 1;

SELECT 

DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01')

, DATE_FORMAT(CURDATE(), '%Y-%m-%d')

FROM DUAL



// 비교

, CASE WHEN

DATE_FORMAT(B.DEL_DATE, '%Y-%m-01') <= DATE_FORMAT(CURDATE(), '%Y-%m-%d') AND DATE_FORMAT(DATE_ADD(B.DEL_DATE, INTERVAL 1 MONTH), '%Y-%m-01') >= DATE_FORMAT(CURDATE(), '%Y-%m-%d') THEN 'Y'

ELSE 'N'

+ Recent posts