MySQL table의 행을 삭제한 경우 자동으로 숫자 update하는 방법

yuri-0415 commented 4 years ago

여러차례 postman을 날리다보니 survey_surveyresult table에 중복 값, 정제되지 않은 값이 들어가서 해당 행을 delete로 삭제했습니다. 그러나 이후 다시 survey를 생성한 경우 번호가 삭제된 값 다음 번호부터 생기는 것을 확인할 수 있었습니다.

그래서 이를 자동으로 번호를 재정렬하고 싶어서

UPDATE survey_surveyresult SET survey_surveyresult = @COUNT:=@COUNT+1;
select * from waffle_backend.survey_surveyresult;

를 시도해보았고, 그러자 다음과 같은 에러 코드가 발생했습니다.

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect

그래서 시키는대로 SQL Editor를 열어서 Safe Updates를 해제해주었습니다. 이후

Error Code: 1265. Data truncated for column 'id' at row

이러한 에러가 발생해서 해결방법을 찾아보니 my.ini에서 sql-mode를 주석처리 하라는 답변을 받았습니다. (참고 - http://javakorean.com/data-truncated-for-column-%EC%98%A4%EB%A5%98-%EB%B0%9C%EC%83%9D%EC%8B%9C-error-1265/)

마지막으로 my.ini를 찾아 주석처리를 하려고 했지만 ,

이와 같은 에러 메시지가 발생했습니다. 너무 복잡해지는 것 같아서 권한 변경까지는 시도하지 않았습니다.

DB에서 행 수에 맞게 자동으로 id를 업데이트 하고 싶은 경우 다른 방법은 없을까요?

YeonghyeonKO commented 4 years ago

여기서 숫자를 Update한다는 말씀은 기본키인 id를 (현재는 지운 행의 id는 없는 상태지요? ex) 1 4 5 6 8 처럼) 1 2 3 4 5로 만들고 싶다는 뜻인가요??

YeonghyeonKO commented 4 years ago

제가 올렸던 #114 를 참고해보시면 비슷한 맥락의 질문입니다!

SQL에서는 개체무결성이라는 개념이 존재하는데, Primary key에서의 개체무결성이란 하나의 테이블에 중복된 행이 없도록 만드는 제약입니다.

특히 id 칼럼은 Unique한 기본키로서 데이터베이스가 꼬이지 않도록 하는 중요한 역할을 맡고 있기 때문에 2행과 3행을 지우고 다시 정렬하려고 했기 때문에 Error Code가 발생하지 않았나 합니다.

(가령 짧은 순간에 해당 table을 참조하는 다른 table에서 이미 id=2, id=3을 받아 외래키로 사용하고 있는데 또 다른 id=2, id=3이 들어온다면 꼬이는 상황을 생각해보실 수 있습니다.)

eldpswp99 commented 4 years ago

로컬에서 실행해봤는데 _id를 변경하는 것 자체로는 문제가 없어 보입니다.

https://m.blog.naver.com/PostView.nhn?blogId=ksh81850&logNo=220081148042&proxyReferer=https:%2F%2Fwww.google.com%2F 여기와 같이 하신 것 맞나요?

근데 과제같은 경우는 primary key만 쓰는게 아니라 여러개의 테이블과 연결관계가 있어서 아마 문제가 생긴다면 이쪽일거라 생각했는데, mysql에서 외래키 만들어서 시도해보니 이것도 되네요. alter table AUTO_INCREMENT = row갯수+1 은 index다 바꾸고 나서 하셔야 합니다. foreign key 대응도 자동으로 바뀌네요.

testforeign이 test의 _id값을 foreign키로 가지고 있는 테이블이구요 test가 _id를 바꾼 테이블입니다.

yuri-0415 commented 4 years ago

@YeonghyeonKO 아하 이미 올라온 내용이었군요...! 답변 감사드립니다!!

@eldpswp99 workbench에서 실행해서 오류가 났던 듯합니다..! 말씀해주신대로 했더니 수정이 되었습니다! 감사합니다.

Hank-Choi commented 4 years ago

작업을 하다보면 id가 중구난방인 모습이 조금 거슬릴 수 있습니다. 하지만 데이터베이스에서 삭제된 데이터 id를 영구결번처리하는 것은 위에 언급된 무결성 측면과 성능 측면에서 필수적 입니다. DB에 저장된 데이터가 많은 실제 서비스에서는 id를 위와 같은 방식으로 업데이트하는 것은 하면 안되는 위험한 일입니다.

간단히 예를 들어 어떤 사이트에 글이 백만개가 있다고 하고 위와 같은 방식을 사용하면 하나의 글 삭제시 999999개 데이터의 id를 업데이트 시켜야하고 이 게시글들을 foreign key로 갖는 데이터도 있다면 관련된 모든 데이터의 foreign key도 업데이트 해야하는 상황이 생깁니다. 이는 몇억개의 데이터를 업데이트하는 결과가 될 수 있습니다. 이 경우 dbms에 큰 부하가 걸리고 서버가 정상작동을 하지 않을 수도 있습니다.

따라서 개인적인 과제 밖에서 id를 재정렬하는 것은 지양해주시면 좋을 것 같습니다.

davin111 commented 4 years ago

네, id를 예쁘게 하실 필요 전혀 없고, 기본적으로 그래서도 안 됩니다. 참고로 세미나 1의 영상 기준 36분 경에서 id가 영구결번 같은 식으로 건너뛰어진다는 얘기를 언급하기도 했습니다.

wafflestudio / seminar-2020

MySQL table의 행을 삭제한 경우 자동으로 숫자 update하는 방법 #144