짬타이거 화이팅!

--프로시저 실행일 다음달 1일부터 말일까지 테이블 생성

--템플릿 테이블 필요(테이블명_exam)

--생성테이블 규칙 : TB_NAME_yyyyMMdd

DROP PROCEDURE IF EXISTS `create_log_table`;

DELIMITER $$

CREATE PROCEDURE create_log_table(IN TB_NAME VARCHAR(50))

BEGIN

DECLARE i INT DEFAULT 0;

        SET @startDay = DATE_ADD(LAST_DAY(NOW()), INTERVAL 1 DAY);

SET @lastDay = DAY(LAST_DAY(DATE_ADD(NOW(),interval 1 MONTH)));

WHILE i < @lastDay DO

SET @log_date = DATE_FORMAT(DATE_ADD(@startDay, INTERVAL i DAY), '%Y%m%d');

SET @stmt_query = CONCAT('CREATE TABLE ',TB_NAME,'_',@log_date,' LIKE ',TB_NAME,'_exam;');

            PREPARE stmt FROM @stmt_query;

            START TRANSACTION;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

COMMIT;

SET i = i + 1;

END WHILE;

END$$

DELIMITER $$

-- 1달에 1번 실행, 시작일 2017-12-01

DROP EVENT IF EXISTS event_create_log_table;

DELIMITER $$

CREATE EVENT event_create_log_table

     -- ON SCHEDULE EVERY 1 MONTH STARTS TIMESTAMP('2017-12-01') ENABLE

        ON SCHEDULE EVERY 1 MONTH STARTS '2017-12-01 00:00:00' ENABLE

    DO 

    job1:BEGIN

CALL log.create_log_table('viewlog');

CALL log.create_log_table('clicklog');

                CALL log.create_log_table('ad_log');

END job1

$$

DELIMITER ;

-- procedure 등록 구문

DELIMITER $$

CREATE PROCEDURE 프로시저명()

BEGIN

    DELETE FROM 테이블명A;

    INSERT 테이블명A

    SELECT cid

           ,count(*) AS viewcount

           ,SYSDATE() AS regdt

      FROM 테이블명B

     WHERE regdt BETWEEN DATE_ADD(SYSDATE(),INTERVAL -12 HOUR) AND SYSDATE()

     GROUP BY cid

     ORDER BY count(*) DESC,regdt DESC

     LIMIT 0,5;

END$$

DELIMITER ;

-- procedure 식행 구문

call 프로시저명();

-- event scheduler 구문

CREATE EVENT IF NOT EXISTS DB명.이벤트스케쥴러명

    ON SCHEDULE

        EVERY 10 MINUTE -- event scheduler 실행 간격(년,월,일,시,분,초)

        STARTS '2014-12-30 16:00:00' -- scheduler 등록시간 +10분

    DO

        call DB명.프로시저명(); -- event scheduler로 등록할 쿼리구문

/*

fn_LPAD(문자열,길이,빈공간을채울문자)

*/

CREATE FUNCTION fn_LPAD

(@ename VARCHAR(20),@ct INT, @chr VARCHAR(2))

RETURNS VARCHAR(100)

AS

BEGIN

RETURN (REPLICATE(@chr,@ct-LEN(@ename))+@ename)

END

*결과*

[출처] mysql의 zerofill 따라하기|작성자 클리프

SELECT CONCAT(CAST(int_col AS CHAR), char_col);

mysql> SELECT CONCAT('My', 'S', 'QL');
        -> 'MySQL'
mysql> SELECT CONCAT('My', NULL, 'QL');
        -> NULL
mysql> SELECT CONCAT(14.3);
        -> '14.3'

mysql> SELECT CONCAT_WS(',','First name','Second name','Last Name');
        -> 'First name,Second name,Last Name'
mysql> SELECT CONCAT_WS(',','First name',NULL,'Last Name');
        -> 'First name,Last Name'

출처 : http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat

UPDATE TABLE_A A, TABLE_B B

       SET A.COLUMN_1 = B.COLUMN_2

  WHERE A.COLUMN_3 = B.COLUMN_5;

UPDATE TABLE_A A 

     LEFT JOIN TABLE_B B 

        ON A.COLUMN_1= B.COLUMN_2 

       SET A.COLUMN = COLUMN_2

  WHERE A.COLUMN2 IS NULL

출처 : http://dev.mysql.com/doc/refman/5.0/en/update.html

# 에러 메세지

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Queries and reconnect.

# 원인

Safe Update 모드 상태로 설정되어 있어서 UPDATE시 KEY 컬럼을 이용하지 않을 경우 업데이트할 수 없음.

# 해결방법

- Query

SET SQL_SAFE_UPDATES =0;

- Workbench 설정 

safe 모드 체크 해지

CREATE PROCEDURE FAQ_l_byChar 

@moduleID               INT  

, @startCharCode        INT  

, @endCharCode          INT  

AS  

BEGIN  

 SET NOCOUNT ON  

    SELECT  * 

      FROM  dbo.FAQ 

     WHERE  title >= NCHAR(@startCharCode)   

       AND  title < NCHAR(@endCharCode)   

  ORDER BY  title ASC 

END  

private int[] searchIndex = { 12593, 12596, 12599, 12601, 12609, 12610, 12613, 12615, 12616, 12618, 12619, 12620, 12621, 12622, 55203 };

dbcc sqlperf(logspace)

BACKUP LOG database_dbf TO DISK='NUL'

DBCC SHRINKDATABASE (database_dbf)