dBack

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

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

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