[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




# 출력결과




  1. 비밀댓글입니다
    • 2020.10.08 16:13 신고 [Edit/Del]
      https://okky.kr/article/338754?note=1092397
      해당 페이지의 내용을 참고하시면 될것 같습니다.

      에러관련 질문이 아닌 제작은 별도의 비용을 받습니다.

Name __

Password __

Link (Your Website)

Comment

SECRET | 비밀글로 남기기

[MariaDB] Ubuntu MariaDB 시간 변경[MariaDB] Ubuntu MariaDB 시간 변경

Posted at 2019. 7. 30. 15:22 | Posted in MySQL/MariaDB




참고 : https://jootc.com/p/201905052779




■ 우분투 마리아DB 시간대 변경




 MariaDB> SELECT @@system_time_zone, NOW() FROM DUAL;




 $ sudo vim /etc/mysql/mariadb.conf.d/50-server.cnf


      /* 이 하 생 략 */


[mysqld]


user = mysql

pid-file = /var/run/mysqld/mysqld.pid

socket = /var/run/mysqld/mysqld.sock

port = 3306

basedir = /usr

datadir = /var/lib/mysql

tmpdir = /tmp

lc-messages-dir = /usr/share/mysql

skip-external-locking


default-time-zone = "+9:00"


      /* 이 하 생 략 */





 MariaDB> SELECT @@system_time_zone, NOW() FROM DUAL;








Name __

Password __

Link (Your Website)

Comment

SECRET | 비밀글로 남기기

[MariaDB] 테이블 구조 복사 쿼리[MariaDB] 테이블 구조 복사 쿼리

Posted at 2019. 1. 9. 00:30 | Posted in MySQL/MariaDB




참고 : https://extbrain.tistory.com/59




■ MariaDB 테이블 구조 복사 쿼리 생성




많은 Data Base 툴들은 기본적으로 DB 구조를 복사할 쿼리를 자동으로 생성해 주지만.


간혹 뭔가 지정해둔 속성이 빠지거나 하는 경우가 많아.


따로 정리하게 되었다.



 MariaDB > SHOW CREATE TABLE 테이블명



위의 결과를 출력하면 해당 테이블을 생성하는 CREATE 쿼리문이 자동으로 생성되어 나오는 것을 확인 할 수 있다.




■ MariaDB 테이블 컬럼명 조회




 MariaDB > SHOW COLUMNS FROM 테이블명

 







Name __

Password __

Link (Your Website)

Comment

SECRET | 비밀글로 남기기

[MariaDB] root 계정의 비밀번호 및 보안 설정 - Ubuntu[MariaDB] root 계정의 비밀번호 및 보안 설정 - Ubuntu

Posted at 2018. 12. 11. 22:33 | Posted in MySQL/MariaDB




참고 : https://github.com/helloheesu/SecretlyGreatly/wiki/맥에서-mysql-설치-후-환경설정하기





■ 우분투 마리아DB 비밀번호 및 보안 설정





우분투마리아DB 설치가 끝났다면 이제


접속할 수 있도록 관리자(root) 계정의 비밀번호 및 보안기능을 설정해 보자.



 $ sudo mysql_secure_installation




위 명령어를 실행하면 위와같이 이제 설정값을 어떻게 정할 건지 묻게된다.


아래 묻는 항목을 정리하였으니 하나하나 확인해 보며 설정을 끝마치도록 한다.









# 01. Enter current password for root



관리자(root) 계정의 현재 비밀번호를 묻는다.


비밀번호를 입력하고 ENETER 키를 누르면 된다.



그렇지만 [Ubuntu] MariaDB 설치 와 같이


마리아DB를 설치하고, $ sudo mysql_secure_jinstallation 를 통해


처음 설정하는 경우라면 ENETER를 치고  넘어가면 된다.




 Enter current password for root (enter for none): ENTER





# 02. Set root password?



관리자인 root 계정의 패스워드의 사용 여부를 묻는다.



 Set root password? [ Y / n ] Y





# 03. new password AND Re-enter new password



이제 root 계정의 비밀번호를 지정한다.



 Re-enter new password: 비밀번호






# 04. Remove anonymous users?



익명 사용자의 접속여부를 묻는다.


NO을 입력하면 $ mysql -u root 와 동시에 $ mysql도 사용이 가능하다.


되도록이면 YES를 선택하자.



 Remove anonymous users? [ Y / n ] Y






# 05. Disallow root login remotely?



127.0.0.1, localhost외에 다른 IProot 접속의 가능 여부를 묻는다.


YES를 선택하면 원격으로 root 계정의 접근을 막게된다.



 Disallow root login remotely? [ Y / n ] Y






# 06. Remove test database and access to it?



마리아DB 설치시 기본적으로 제공되는 test 데이터베이스의 삭제 여부를 묻는다.



 Remove test database and access to it? [ Y / n ] Y






# 07. Remove test database and access to it?



마지막으로 프리빌리지(privilege) 테이블의 재시작 여부를 묻는다.


YES를 선택하여 작업을 진행한다.



 Reload privilege tables now? [ Y / n ] Y










■ 우분투 마리아DB root 계정으로 접속




이제 모든 설정이 마무리 되었다면


관리자(root) 계정으로 접근할 수 있을 것이다.



 $ sudo mysql -u root -p

 Enter password: #03 에서 지정한 root 계정의 패스워드






이전글 : 우분투 마리아DB 설치하기

다음글 : 우분투에서 마리아DB 원격접속 설정하기




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 | 비밀글로 남기기

[MariaDB] MariaDB의 데이터 베이스 및 사용자 계정 추가하기[MariaDB] MariaDB의 데이터 베이스 및 사용자 계정 추가하기

Posted at 2018. 6. 26. 16:55 | Posted in MySQL/MariaDB




■ 마리아DB의 데이터 베이스 및 사용자 계정 추가하기





01. 먼저 사용할 데이터 베이스(DB)를 생성한다.


 MariaDB > CREATE DATABASE 데이터 베이스 명;





02. DB가 정상적으로 생성되었는지 확인한다.


 MariaDB > SHOW Databases;





03. 생성한 DB에 접근할 계정을 생성하기위해 mysql DB로 사용자를 변경한다.


 MariaDB > use mysql;





04. 이제 계정을 등록할 user 테이블을 살펴보도록 하자.

   아래와 같은 쿼리를 실행하면 현재 user 테이블에 어떤 계정이 등록되어 있는지 확인 할 수 있다.

 MariaDB > SELECT Host, User, Password FROM user;





05. 이제 계정을 등록할 user 테이블을 살펴보도록 하자.

  -. 계정을 등록할 user 테이블의 구조는 아래와 같다.

  -. 좌측부터 user 테이블 필드로 Host, Password, Select_priv 등등의 필드들이 존재하는데.

    아래 이미지에서는 46개의 필드가 존재하지만, 버전마다 필드의 개수가 조금씩 달랐다.

  -. user 테이블의 필드 개수는 버전마다 조금 차이가 있을 수 있지만 이 포스팅에서는 계정 생성에 필요한 것만 다룰 것이다.


 MariaDB > DESC user





06. 이제 새로운 계정을 user 테이블에 등록한다.


 MariaDB > INSERT INTO user (Host, User, Password, ssl_cipher, x509_issuer, x509_subject, authentication_string) VALUE ('IP정보', '계정명', password('비밀번호'), '', '', '', '');





07. 이제 다시 user 테이블을 조회해 보면 ⑥번에서 INSERT한 계정 정보가 생성된 것을 확인 할 수 있다.


 MariaDB > SELECT Host, User, Password FROM user;





08. 이제 db 테이블에 데이터 베이스 사용 권한을 등록해야 한다.

 -. 생성한 계정이 처음 ①번에서 만든 DB를 사용할 수 있게 해줘야 한다.

 -. 즉, 마리아DB에서 db 테이블에 레코드 값을 추가 해 주면 된다.

 -. 우선 db 테이블 부터 알아보도록 하자.


 MariaDB > desc db


 -. 위와같이 db 테이블은 22개의 필드로 구성된다.

 -. Host 필드는 데이터베이스가 존재하는 컴퓨터의 IP값을 가진다.

 -. Db 필드에는 사용자가 사용하려는 데이터 베이스의 이름이 들어간다.

 -. User 필드에는 사용자 계정을 입력한다.

 -. _priv 로 끝이나는 필드들에는 각각의 SQL 명령들을 사용할 수 있는 권한을 설정한다.


 


09. 이제 생성한 계정을 바탕으로 데이터 베이스를 사용할 권한을 부여해 보도록 하겠다.


 MariaDB > INSERT INTO db (Host, Db, User, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Create_tmp_table_priv, Lock_tables_priv, Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, Execute_priv, Event_priv, Trigger_priv) VALUE ('localhost', '데이터 베이스 명', '계정', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');





10. db 테이블에 계정이 잘 등록되었다면 아래 쿼리를 실행하여 확인 해 보자.


 MariaDB > SELECT Host, Db, User, Select_priv FROM db



 MariaDB > SELECT * FROM db;




12. 마지막으로 작성한 현재까지 작업한 내용을 마리아DB 시스템에 적용시킨다.


 MariaDB > FLUSH PRIVILEGES;





13. Ctrl 버튼 + C 버튼을 클릭하여 모든 마리아DB를 빠져나온다.




14. 이제 생성했던 마리아DB 계정과 패스워드를 입력하여 마리아DB에 접속 되는것을 확인 할 수 있다.

 C:\Users\Name>mysql -u -root -p

 Enter password: ******







DB

Name __

Password __

Link (Your Website)

Comment

SECRET | 비밀글로 남기기

[MariaDB] Windows에서 MariaDB 세팅하기[MariaDB] Windows에서 MariaDB 세팅하기

Posted at 2018. 6. 26. 14:54 | Posted in MySQL/MariaDB




■ 윈도우즈에서 마리아DB 세팅하기




01. 내 컴퓨터를 열고 상단의 시스템 속성을 클릭한다.




02. 좌측 카테고리에서 고급 시스템 설정을 클릭한다.




03. 시스템 속성 창에서 아래와 같이 작업을 수행한다.

 ① 상단 탭 메뉴에서 고급을 선택한다.

 ② 환경변수 버튼을 클릭한다.




04. 환경변수 창이 팝업되면 아래와 같이 작업을 계속 진행한다.

 ① 시스템 변수 목록에서 Path를 찾아 선택한다.

 ② 편집 버튼을 클릭한다.




05. 이제 Path 시스템 변수를 편집한다.

 ① 마리아DB가 설치된 경로의 bin 폴더의 경로까지의 주소를 복사한다.

 ② 시스템 변수 편집 창에서 변수 값 부분에 복사한 주소값을 추가한다.

     마지막에 꼭 세미콜론( ; ) 기호를 넣어주는거 잊지말자.

 ③ 확인 버튼을 클릭하여 시스템 변수 편집창을 닫는다.



 

06. 다시 환경 변수 창에서 확인 버튼을 클릭하여 환경 변수 창을 닫는다.




07. 시스템 속성창에서도 확인 버튼을 클릭하여 창을 닫아준다.




08. Windows 버튼 + R 버튼 키를 눌러서 실행창을 띄운다.

 ① cmd를 입력한다.

 ② 확인 버튼을 클릭한다.




09. 이제 명령 프롬프트 창을 열고 마리아DB를 실행시켜보자.

 C:\Users\Name>mysql -u -root -p

 Enter password: ******



위와같이 실행된다면 윈도우즈에서 마리아DB의 설치와 세팅이 완료된 것이다.





DB

Name __

Password __

Link (Your Website)

Comment

SECRET | 비밀글로 남기기

[MariaDB] Windows에 MariaDB 다운받고 설치하기[MariaDB] Windows에 MariaDB 다운받고 설치하기

Posted at 2018. 4. 10. 23:06 | Posted in MySQL/MariaDB




■ 윈도우즈에 마리아DB 다운받고 설치하기




01. https://downloads.mariadb.org/ 사이트에 접속하여 마리아 DB를 다운받자.




02. 설치할 윈도우즈에 맞는 mariadb.mis 설치 파일을 선택한다.




03. 그럼 페이지가 이동하고 다운로드 여부를 묻는 창이 뜰것이다. 파일 저장을 클릭하여 다운로드 받자.




04. 다운로드 받은 mariadb.msi 파일을 실행한다.




05. Next 버튼을 눌러 작업을 진행한다.




06. Brows버튼을 눌러 마리아DB를 설치할 경로를 지정한다.




07. 마리아DB를 설치할 경로를 지정하고, OK 버튼을 클릭한다.




08. 이제 Next 버튼을 눌러 설치를 계속 진행한다.




09. root 계정에서 사용할 패스워드를 지정하고, Next 버튼을 클릭한다.




10. 서비스네임 및 포트 번호를 특별히 수정할 일이 있다면 수정한 후에 Next 버튼을 눌러 설치를 진행한다.




11. Next 버튼을 눌러 설치를 계속 진행한다.




12. Install 버튼을 눌러 작업을 계속 진행하자.




13. Finish 버튼을 누름으로서 윈도우즈에 마리아DB 설치작업이 완료되었다.




14. 이제 마지막으로 명령 프롬프트 창을 열고 MariaDB에 접속해 보자.

 ① C:\> mysql -u root -p

 ② Enter Password : ⑨번에서 입력한 비밀번호








Name __

Password __

Link (Your Website)

Comment

SECRET | 비밀글로 남기기