[MySQL] ON DUPLICATE KEY UPDATE 사용하기[MySQL] ON DUPLICATE KEY UPDATE 사용하기

Posted at 2020. 5. 14. 17:13 | Posted in MySQL/MySQL





참고 : https://sdevstudy.tistory.com/12






■ ON DUPLICATE KEY UPDATE 사용하기




MySQL에서는 대량의 파일을 등록 및 수정하는경우


기존 데이터는 UPDATE하고 신규 데이터는 INSERT 시키는 ON DUPLICATE KEY UPDATE 기능을 제공한다.


해당 기능을 잘 사용하게 되면  대량의 데이터 처리하는데 많은 도움이 된다.




먼저 ON DUPLICATE KEY UPDATE 기능을 알아볼 테스트 테이블을 하나 생성해 보자.




# 테이블 생성

  CREATE TABLE onepiece (

      seq_num INT( 11 ) NOT NULL AUTO_INCREMENT

    , name VARCHAR( 20 ) NOT NULL

    , age INT( 2 ) NOT NULL

    , stature INT( 3 ) DEFAULT NULL

    , prize_money VARCHAR( 20 ) DEFAULT NULL

    , blood VARCHAR( 2 ) DEFAULT NULL

    , PRIMARY KEY ( seq_num )

  ) ENGINE=INNODB DEFAULT CHARSET=utf8 




테이블이 정상적으로 생성되었다면 초기값을 먼저 세팅해 보자.




# 데이터 입력 - 초기값 설정

  INSERT INTO onepiece (nameagestatureprize_money, blood) VALUE ('Monkey D. Luffy', '17', '172', '3억', 'F');

  INSERT INTO onepiece (nameagestatureprize_money, blood) VALUE ('Roronoa Zoro', '19', '178', '1억 2천만', 'XF');

  INSERT INTO onepiece (nameagestatureprize_money, blood) VALUE ('Sanji', '19', '177', '7천 7백만', 'S'); 





초기값을 INSERT 하였다면 SELECT 하여 내용을 한번 확인해 보자.




# 데이터 출력 - 입력내역 확인

  SELECT seq_numnameagestatureprize_money, blood FROM onepiece




이제 해당 샘플 테이블에서 절대로 변경되지 않을 name 컬럼을


UNIQUE KEY로 지정해야 한다.




# 테이블 변경 - UNIQUE KEY 적용

 ALTER TABLE onepiece ADD UNIQUE name ); 




UNIQUE KEY 지정이 완료되었다면 


본격적으로 ON DUPLICATE KEY UPDATE 사용하였을때 어떤 결과를 얻을 수 있는지


새로운 데이터를 INSERT 하여 확인해보자.




# 데이터 입력 - 변경 및 추가 데이터 입력

  -- 업데이트 할 기존 데이터 

  INSERT INTO onepiece ( nameagestatureprize_money, blood ) VALUE ( 'Monkey D. Luffy', '19', '174', '15억', 'F' ) 

  ON DUPLICATE KEY UPDATE ageage + 2, stature = VALUESstature ), prize_moneyVALUESprize_money );

  INSERT INTO onepiece ( nameagestatureprize_money, blood ) VALUE ( 'Roronoa Zoro', '21', '181', '3억 2천만', 'XF' ) 

  ON DUPLICATE KEY UPDATE age = age + 2stature = VALUESstature ), prize_money = VALUESprize_money );

  INSERT INTO onepiece ( nameagestatureprize_money, blood ) VALUE ( 'Sanji', '21', '180', '3억 3천만', 'S' )

  ON DUPLICATE KEY UPDATE age = age + 2stature = VALUESstature ), prize_money = VALUESprize_money );


  -- 신규 데이터

  INSERT INTO onepiece ( nameagestatureprize_money, blood ) VALUE ( 'Usopp', '19', '176', '2억', 'S' )

  ON DUPLICATE KEY UPDATE age = age + 2stature = VALUESstature ), prize_money = VALUESprize_money );

  INSERT INTO onepiece ( nameagestatureprize_money, blood ) VALUE ( 'Nami', '20', '170', '6천 6백만', 'X' )

  ON DUPLICATE KEY UPDATE age = age + 2stature = VALUESstature ), prize_money = VALUESprize_money );

  INSERT INTO onepiece ( nameagestatureprize_money, blood ) VALUE ( 'Tony Tony Chopper', '17', '90', '백', 'X' )

  ON DUPLICATE KEY UPDATE age = age + 2stature = VALUESstature ), prize_money = VALUESprize_money );

  INSERT INTO onepiece ( nameagestatureprize_money, blood ) VALUE ( 'Nico Robin', '30', '188', '1억 3백', 'S' )

  ON DUPLICATE KEY UPDATE age = age + 2stature = VALUESstature ), prize_money = VALUESprize_money );




ON DUPLICATE KEY UPDATE를 사용하는 기존 데이터와 신규 데이터의 입력이 완료되었다면


다시 SELECT 문으로 테스트 테이블의 내용을 확인해 보자.




# 데이터 출력 - 변경 내역 확인

 SELECT seq_numnameagestatureprize_money, blood FROM onepiece



ON DUPLICATE KEY UPDATE 를 사용한 출결결과를 보면


여기서 age = age + 2 는 기존 값이 존재한다면 기존 값에서 +2를 더해주었다.


완전히 새로운 데이터로 초기화 하기 위해서는 stature = VALUESstature ), prize_money = VALUESprize_money ) 와 같이


VALUES를 활용하게 되면 완전히 새로운 데이터로 치환되는것을 확인 할 수 있다.



한가지 더 눈여겨 봐야 할 것은 데이터가 추가될 때마다 자동 증가되는 seq_num 컬럼 시퀀스 값이다.


1, 2, 3 다음에 4, 5, 6이 생략되고 7, 8, 9 10이 추가되는 것을 볼 수 있는데


이를 통해 ON DUPLICATE KEY UPDATE 작동방식을 유추해 볼 수 있다.




   INSERT 문을 실행하여 신규 데이터를 입력한다.


   UNIQUE KEYname컬럼 )의 에 중복되는 값을 확인한다.


  중복된 UNIQUE KEY값이 존재하면 ON DUPLICATE KEY UPDATE 지정된 컬럼( agestatureprize_money )컬럼을 UPDATE 한다.


  마지막으로 새로 INSERT된 중복되는 UNIQUE KEY값을 가진 항목을 DELETE 한다.




필자는 항상 ON DUPLICATE KEY UPDATE 를 알기 전까지는 항상



  if( UNIQUE KEY == TRUE ) { console.log( UPDATE문 실행. ); } else { console.log( INSERT문 실행. ); } 



의 선택지 외에는 존재하지 않았다.


물론 이와 같은 방법으로도 해결은 가능하지만, 












참고 : https://mssun.tistory.com/entry/MySQL-replace-into



REPLACE INTO 관련내용 정리





Name __

Password __

Link (Your Website)

Comment

SECRET | 비밀글로 남기기

[MySQL] 전체 날짜 출력하기 - 데이터가 없는 경우 포함[MySQL] 전체 날짜 출력하기 - 데이터가 없는 경우 포함

Posted at 2020. 2. 7. 10:22 | Posted in MySQL/MySQL




참고 : https://dyang34.tistory.com/362





■ 값이 존재하지 않는 데이터의 날짜 출력하기




# 조회쿼리

SELECT a.date_ymd FROM (

SELECT CURDATE() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as date_ymd 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

) AS a

WHERE 1 = 1

AND a.date_ymd BETWEEN '2020-01-01 00:00:00' AND '2020-01-31 23:59:59'

-- AND a.date_ymd LIKE '2020-01-%' -- 한달만 검색 하려고 하는경우

ORDER BY a.date_ymd ASC




# 출력결과




Name __

Password __

Link (Your Website)

Comment

SECRET | 비밀글로 남기기

[PHP] MySQL 한글 깨지는 경우[PHP] MySQL 한글 깨지는 경우

Posted at 2018. 9. 9. 08:29 | Posted in MySQL/MySQL



define("DB_HOST", "DB IP주소");
define("DB_USER", "DB접속 계정");
define("DB_PASS", "DB접속 패스워드");
define("DB_NAME", "DB이름");


$connect = mysql_connect($mysql_sale_host, $mysql_sale_user, $mysql_sale_password) or die('MySQL 서버에 연결할 수 없습니다.');
mysql_select_db($mysql_sale_db, $connect);
mysql_query("SET SESSION character_set_connection=utf8;");
mysql_query("SET SESSION character_set_results=utf8;");
mysql_query("SET SESSION character_set_client=utf8;");

Name __

Password __

Link (Your Website)

Comment

SECRET | 비밀글로 남기기

[MySQL] 날짜 YYYY-MM-DD 형식으로 출력하기.[MySQL] 날짜 YYYY-MM-DD 형식으로 출력하기.

Posted at 2018. 8. 20. 17:22 | Posted in MySQL/MySQL




■ DATE_FORMAT 함수를 이용한 날짜 출력




 mysql> SELECT DATE_FORMAT(NOW(), '%Y-%m-%d') AS to_day FROM DUAL;





Name __

Password __

Link (Your Website)

Comment

SECRET | 비밀글로 남기기