사이트 구축/최적화 활용 팁

      WHERE a.date BETWEEN DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -30 DAY),
                                       '%Y-%m-%d 00:00:00')
                       AND DATE_FORMAT(NOW(), '%Y-%m-%d 23:29:55')

쿼리문만 가지고 MySQL, MariaDB 날짜검색시 BETWEEN 안에 포함되는 날짜 달력처럼 전체 출력하는 방법입니다.
( generate days from date range )
테이블 없이 Inline Query, Cross Join, Union, Outer Join를 이용하였습니다.
데이터가 없는 날짜는 IFNULL 함수를 이용하여 0으로 표시가 되게 합니다.

날짜의 검색 조건은 최근 한달로 설정을 하였습니다.

      WHERE a.date BETWEEN DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -30 DAY),
                                       '%Y-%m-%d 00:00:00')
                       AND DATE_FORMAT(NOW(), '%Y-%m-%d 23:29:55')

SELECT calendar.date AS reg_date, ifnull(calc.cnt, 0) cnt
FROM (SELECT DATE_FORMAT(a.date, '%m-%d') AS Date, 0 AS cnt
      FROM (SELECT   curdate()
                   - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a)) DAY
                      AS Date
            FROM (SELECT 0 AS a
                  UNION ALL
                  SELECT 1
                  UNION ALL
                  SELECT 2
                  UNION ALL
                  SELECT 3
                  UNION ALL
                  SELECT 4
                  UNION ALL
                  SELECT 5
                  UNION ALL
                  SELECT 6
                  UNION ALL
                  SELECT 7
                  UNION ALL
                  SELECT 8
                  UNION ALL
                  SELECT 9) AS a
                 CROSS JOIN (SELECT 0 AS a
                             UNION ALL
                             SELECT 1
                             UNION ALL
                             SELECT 2
                             UNION ALL
                             SELECT 3
                             UNION ALL
                             SELECT 4
                             UNION ALL
                             SELECT 5
                             UNION ALL
                             SELECT 6
                             UNION ALL
                             SELECT 7
                             UNION ALL
                             SELECT 8
                             UNION ALL
                             SELECT 9) AS b
                 CROSS JOIN (SELECT 0 AS a
                             UNION ALL
                             SELECT 1
                             UNION ALL
                             SELECT 2
                             UNION ALL
                             SELECT 3
                             UNION ALL
                             SELECT 4
                             UNION ALL
                             SELECT 5
                             UNION ALL
                             SELECT 6
                             UNION ALL
                             SELECT 7
                             UNION ALL
                             SELECT 8
                             UNION ALL
                             SELECT 9) AS c
                 CROSS JOIN (SELECT 0 AS a
                             UNION ALL
                             SELECT 1
                             UNION ALL
                             SELECT 2
                             UNION ALL
                             SELECT 3
                             UNION ALL
                             SELECT 4
                             UNION ALL
                             SELECT 5
                             UNION ALL
                             SELECT 6
                             UNION ALL
                             SELECT 7
                             UNION ALL
                             SELECT 8
                             UNION ALL
                             SELECT 9) AS d) a
      WHERE a.date BETWEEN DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -30 DAY),
                                       '%Y-%m-%d 00:00:00')
                       AND DATE_FORMAT(NOW(), '%Y-%m-%d 23:29:55'))
     AS calendar
     LEFT JOIN
     (SELECT DATE_FORMAT(reg_date, '%m-%d') reg_date, COUNT(1) cnt
      FROM opps_calc_cont
      WHERE reg_date BETWEEN DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -30 DAY),
                                         '%Y-%m-%d 00:00:00')
                         AND DATE_FORMAT(NOW(), '%Y-%m-%d 23:29:55')
      GROUP BY DATE_FORMAT(reg_date, '%m-%d')) calc
        ON calendar.date = calc.reg_date
ORDER BY calendar.date

2021.05.18 - [유용한 활용팁] - Javascript Jquery Flot Charts 날짜별 통계 선 차트 만들기 팁

CentOS 7에서 mariaDB를 설치하고 테이블 생성 시 아래와 같은 에러가 발생했을 때 해결 방법입니다.

테이블 생성 스크립트

CREATE TABLE TAB 
(
  COL VARCHAR(200)
);

에러 메시지

mariadb specified key was too long max key length is 767 bytes

에러 발생 원인

character가 utf8mb4로 되어 있어서 입니다.

utf8, utf8mb4에 따라서 VARCHAR 최대값이 달라서 발생을 한 것입니다.

INNODB utf8 VARCHAR(255)
INNODB utf8mb4 VARCHAR(191)

해결 방법

/etc/my.cnf 설정 파일에서 utf8로 변경하여 DB 재시작 후
Database부터 새로 생성을 하여 테이블을 만들면 원하는 사이즈의 테이블이 생성됩니다.

character-set-server = utf8
collation-server = utf8_general_ci

mariadb specified key was too long max key length is 767 bytes 에러가 발생했던 설정 내용

/etc/my.cnf
character-set-server = utf8mb4
collation_server = utf8mb4_general_ci