프로젝트
개인정보-컴플라이언스-웹애플리케이션 (수정사항 2-3)
알럽유
2025. 1. 29. 18:15
728x90
반응형
현재 assessment_result 테이블에 동일한 system_id에 대한 평가 결과가 여러 개 존재할 가능성이 있기 때문에 중복 문제가 발생할 수 있어서 수정하도록 하겠습니다.
🚨 문제점 분석
ON DUPLICATE KEY UPDATE가 중복을 해결하지 못할 가능성 있음
system_id가 UNIQUE 제약 조건이 없으면, 같은 system_id에 대해 여러 개의 평가 결과가 삽입될 수 있습니다.
ON DUPLICATE KEY UPDATE는 기존 행이 있는 경우에만 업데이트되므로, 이미 중복된 데이터가 있는 경우 중복이 해결되지 않습니다.
자가진단이 완료될 때마다 INSERT INTO로 새로운 평가 결과가 생성됩니다.
기존에 있는 평가 결과를 업데이트하는 것이 아니라, 새로운 데이터를 삽입하는 방식이라면, 중복된 데이터가 계속 추가될 수 있습니다.
1. 해결 방법: assessment_result의 system_id를 UNIQUE로 설정
ALTER TABLE assessment_result ADD CONSTRAINT unique_system UNIQUE (system_id);같은 시스템에 대한 평가 결과가 하나만 존재하도록 강제할 수 있습니다.
2. 해결 방법 2: INSERT 대신 REPLACE INTO 또는 UPDATE 사용
기존 데이터를 먼저 조회하고, 있으면 UPDATE, 없으면 INSERT하는 방식으로 처리하도록 하겠습니다.
// 자가진단 완료 처리
const completeSelfTest = async (req, res) => {
const { systemId, userId } = req.body;
if (!systemId || !userId) {
return res.status(400).json({
message: "유효하지 않은 요청입니다. systemId와 userId를 확인하세요.",
});
}
console.log("completeSelfTest called with:", { systemId, userId });
try {
// ✅ 1️⃣ `assessment_id`를 `self_assessment`에서 조회
const [selfAssessmentResult] = await pool.query(
"SELECT id FROM self_assessment WHERE system_id = ? AND user_id = ?",
[systemId, userId]
);
if (selfAssessmentResult.length === 0) {
return res.status(404).json({
message: "자가진단 입력 데이터가 없습니다.",
});
}
const assessmentId = selfAssessmentResult[0].id;
console.log("✅ Retrieved assessment_id:", assessmentId);
// ✅ 2️⃣ 점수 및 등급 계산
const { score, grade } = await calculateAssessmentScore(systemId);
console.log("Calculated score and grade:", { score, grade });
// ✅ 3️⃣ 기존 평가 결과가 있는지 확인
const [existingResult] = await pool.query(
"SELECT id FROM assessment_result WHERE system_id = ?",
[systemId]
);
if (existingResult.length > 0) {
// ✅ 기존 데이터가 있다면 `UPDATE`
const updateQuery = `
UPDATE assessment_result
SET score = ?, grade = ?, feedback_status = '전문가 자문이 반영되기전입니다'
WHERE system_id = ?;
`;
const updateValues = [score, grade, systemId];
console.log("Executing update query:", updateQuery, "with values:", updateValues);
await pool.query(updateQuery, updateValues);
} else {
// ✅ 기존 데이터가 없다면 `INSERT`
const insertQuery = `
INSERT INTO assessment_result (system_id, user_id, assessment_id, score, feedback_status, grade)
VALUES (?, ?, ?, ?, '전문가 자문이 반영되기전입니다', ?);
`;
const insertValues = [systemId, userId, assessmentId, score, grade];
console.log("Executing insert query:", insertQuery, "with values:", insertValues);
await pool.query(insertQuery, insertValues);
}
res.status(200).json({
message: "자가진단 결과가 성공적으로 저장되었습니다.",
score,
grade,
});
} catch (error) {
console.error("자가진단 완료 실패:", error.message);
res.status(500).json({
message: "서버 내부 오류 발생",
error: error.message,
});
}
};
🚀 개선된 점
중복 문제 해결 ✅
SELECT id FROM assessment_result WHERE system_id = ? 로 기존 데이터 여부를 먼저 확인
있으면 UPDATE, 없으면 INSERT 수행
ON DUPLICATE KEY UPDATE 대신 UPDATE 활용 ✅
명확하게 기존 데이터 업데이트 vs 새로운 데이터 삽입 분리
더 효율적인 쿼리 실행 ✅
불필요한 INSERT 실행을 방지하고, 필요한 경우에만 UPDATE 진행
불필요한 DELETE 없이 데이터 유지 가능
3. 기존 중복 데이터 삭제
기존에 assessment_result에 중복된 system_id 데이터가 있다면 가장 최신 데이터만 유지하고, 나머지는 삭제하도록 하겠습니다.
DELETE FROM assessment_result
WHERE id NOT IN (
SELECT id FROM (
SELECT id FROM assessment_result
WHERE system_id IN (
SELECT system_id FROM assessment_result
GROUP BY system_id
HAVING COUNT(*) > 1
)
ORDER BY completed_at DESC
) AS latest_results
);
GROUP BY system_id를 사용하여 중복된 system_id를 가진 행들을 찾습니다.
ORDER BY completed_at DESC를 사용하여 가장 최근 데이터만 남기고 나머지를 삭제합니다.
서브쿼리 안에서 SELECT id FROM (...)을 써서 DELETE에서 사용할 수 있도록 처리합니다.
INSERT 하기 전에 중복 확인 후 UPDATE 또는 INSERT
앞으로도 중복이 생기지 않도록 코드를 수정해야 해.
// 자가진단 완료 처리
const completeSelfTest = async (req, res) => {
const { systemId, userId } = req.body;
if (!systemId || !userId) {
return res.status(400).json({
message: "유효하지 않은 요청입니다. systemId와 userId를 확인하세요.",
});
}
console.log("completeSelfTest called with:", { systemId, userId });
try {
// ✅ 1️⃣ `assessment_id`를 `self_assessment`에서 조회
const [selfAssessmentResult] = await pool.query(
"SELECT id FROM self_assessment WHERE system_id = ? AND user_id = ?",
[systemId, userId]
);
if (selfAssessmentResult.length === 0) {
return res.status(404).json({
message: "자가진단 입력 데이터가 없습니다.",
});
}
const assessmentId = selfAssessmentResult[0].id;
console.log("✅ Retrieved assessment_id:", assessmentId);
// ✅ 2️⃣ 점수 및 등급 계산
const { score, grade } = await calculateAssessmentScore(systemId);
console.log("Calculated score and grade:", { score, grade });
// ✅ 3️⃣ 기존 평가 결과가 있는지 확인
const [existingResult] = await pool.query(
"SELECT id FROM assessment_result WHERE system_id = ?",
[systemId]
);
if (existingResult.length > 0) {
// ✅ 기존 데이터가 있다면 `UPDATE`
const updateQuery = `
UPDATE assessment_result
SET score = ?, grade = ?, feedback_status = '전문가 자문이 반영되기전입니다'
WHERE system_id = ?;
`;
const updateValues = [score, grade, systemId];
console.log("Executing update query:", updateQuery, "with values:", updateValues);
await pool.query(updateQuery, updateValues);
} else {
// ✅ 기존 데이터가 없다면 `INSERT`
const insertQuery = `
INSERT INTO assessment_result (system_id, user_id, assessment_id, score, feedback_status, grade)
VALUES (?, ?, ?, ?, '전문가 자문이 반영되기전입니다', ?);
`;
const insertValues = [systemId, userId, assessmentId, score, grade];
console.log("Executing insert query:", insertQuery, "with values:", insertValues);
await pool.query(insertQuery, insertValues);
}
res.status(200).json({
message: "자가진단 결과가 성공적으로 저장되었습니다.",
score,
grade,
});
} catch (error) {
console.error("자가진단 완료 실패:", error.message);
res.status(500).json({
message: "서버 내부 오류 발생",
error: error.message,
});
}
};기존 코드의 문제
- INSERT INTO assessment_result를 실행할 때, 이미 동일한 system_id가 있으면 중복 저장됩니다.
- ON DUPLICATE KEY UPDATE를 사용해도, 이미 중복된 데이터가 있다면 해결되지 않습니다.
- 점수가 갱신될 때마다 새로운 행이 추가될 가능성이 큽니다.
개선된 코드의 장점
✅ 중복 데이터 발생을 방지
- SELECT 쿼리로 기존 데이터 확인합니다.
- 기존 데이터가 있으면 UPDATE
- 기존 데이터가 없으면 INSERT
✅ 기존 데이터 유지
- 기존에 있던 assessment_id를 유지하면서 점수와 등급만 업데이트합니다.
✅ 불필요한 DELETE 실행 방지
- 기존 데이터가 있어도 무조건 삭제하지 않고, 필요한 경우만 갱신합니다.
✅ 데이터 정합성 유지
- system_id에 대한 최신 데이터를 항상 하나만 유지합니다.