[Mysql] Upsert(Update&Insert) 기능(Primary Key or Unique Key 값이 이미 있을 경우 Update, 없다면 Insert)

데이터를 업데이트 하려 할 때, 기존에 있던 값이면 업데이트, 없던 값이면 추가하고 싶을 때가 있다.
학교 학생 정보의 경우 학번을 기준으로 값이 있다면 Update, 없으면 Insert하는 느낌이다.
하지만 그러기 위해서는 '학번'이라는 키가 Primary Key거나 Unique Key여야한다.
처음부터 php로 Upsert 적용까지 살펴보자.

1. Primary, Unique Key 설정

[정의]

기본키(Primary Key): 해당 테이블의 식별자 역할을 하는 제약조건으로 테이블에 하나만 설정할 수 있다.

Unique Key: 유일성을 가지기 위해 설정해 놓은 것으로, 중복을 방지한다.

[구문]

-- PRIMARY 추가하는 방법

ALTER TABLE person ADD PRIMARY KEY (name)

-- UNIQUE INDEX를 추가하는 방법

ALTER TABLE iqms.dept_info ADD UNIQUE (deptCD);

[설정]

ALTER TABLE iqms.dept_info ADD UNIQUE (deptCD);

iqms 데이터베이스의 user_info테이블의 user_id값을 Unique Key로 놓았다.

 

2. Upsert 구문

[구문]

INSERT INTO [table 명] ( [컬럼명1], [컬럼명2], ... ) 
VALUES ( [컬럼명1의 값], [컬럼명2의 값], ... ) 
ON DUPLICATE KEY UPDATE [업데이트시 바뀔 컬럼명1]=[바뀔 값], [바뀔 컬럼명2]=[바뀔 값], ...

 

[설정]

foreach ($summary as $key => $value) {
  if($value['PERNR']=="")   $value['PERNR']="미확인";	#학번
  if($value['ENAME']=="")   $value['ENAME']="미확인";	#이름
  if($value['ORGEH']=="")   $value['ORGEH']="미확인";	#코드
  if($value['ZLEVTX']=="")  $value['ZLEVTX']="미확인";	#
  if($value['ZEMAIL']=="")  $value['ZEMAIL']="미확인";	#이메일



  $sql =  "INSERT INTO iqms.user_info                                                   ";
  $sql .= "(                                                                            ";
  $sql .= "   user_id, passwd, old_passwd, name, deptCD, call_grade,                    ";
  $sql .= "		email, role, logintry, voc_ui, voc_ui_edit, admin_ui,           ";
  $sql .= "		admin_ui_edit, dashboard_ui, dashboard_ui_edit, last_logindate, ";
  $sql .= "		pw_default, user_ui, user_ui_edit, bldg_ui, bldg_ui_edit,       ";
  $sql .= "		data_ui, data_ui_edit, car_ui, car_ui_edit, disaster_role       ";
  $sql .= ")                                                                            ";
  $sql .= "VALUES  	   					  			";
  $sql .= "(                                                                            ";
  $sql .= "		?, '1234', '', ?, ?, ?, ?, 'ROLE_USER', '0', 'Y', 'Y' ,'N',     ";
  $sql .= "		'N', 'Y', 'Y', '1000-01-01 00:00:00', 'N', 'Y', 'Y', 'Y',       ";
  $sql .= "		'Y', 'N', 'N','Y', 'Y', 'NORMAL'    				";
  $sql .= ")                                                                            ";
  $sql .= "ON DUPLICATE KEY UPDATE name=?, deptCD=?, call_grade=?, email=?;             ";

  #echo "\n\n".$sql."\n\n";

  $sql_obj = $conn->prepare($sql);
  $sql_obj->bind_param("sssssssss",$value['PERNR'],$value['ENAME'],$value['ORGEH'],$value['ZLEVTX'],$value['ZEMAIL'],$value['ENAME'],$value['ORGEH'],$value['ZLEVTX'],$value['ZEMAIL']);
  $sql_obj->execute();
  $result = $sql_obj->get_result();
}

 

iqms.user_info 테이블에 있는 Primary값 혹은 Unique값(user_id)와 중복되는 값을 추가하려 한다면 UPDATE, 중복되지 않는다면 INSERT한다.