타입형 EAV 모델

DROP TABLE IF EXISTS post_eav_bad;
CREATE TABLE post_eav_bad (
  post_id BIGINT UNSIGNED NOT NULL,
  attr_name VARCHAR(80) NOT NULL,
  value_text VARCHAR(500) NOT NULL,
  PRIMARY KEY (post_id, attr_name)
) ENGINE=InnoDB;

INSERT INTO post_eav_bad VALUES
  (1, 'editorial_score', '100'), (2, 'editorial_score', '20'),
  (3, 'reviewed_at', '2026-99-41'), (4, 'is_verified', 'maybe'),
  (5, 'editroial_score', '70');

SELECT post_id, value_text FROM post_eav_bad
WHERE attr_name = 'editorial_score' ORDER BY value_text;
SELECT * FROM post_eav_bad WHERE attr_name = 'reviewed_at';

post_id | value_text
1       | 100
2       | 20

post_id | attr_name   | value_text
3       | reviewed_at | 2026-99-41

DROP PROCEDURE IF EXISTS set_post_attribute;
DROP TABLE IF EXISTS post_attribute_value;
DROP TABLE IF EXISTS post_attribute_definition;

CREATE TABLE post_attribute_definition (
  attribute_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  attr_code VARCHAR(80) NOT NULL,
  value_type VARCHAR(16) NOT NULL,
  allowed_post_type VARCHAR(16) NULL,
  unit_code VARCHAR(20) NULL,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  description VARCHAR(240) NOT NULL,
  UNIQUE KEY uq_post_attr_code (attr_code),
  CHECK (value_type IN ('TEXT', 'NUMBER', 'BOOLEAN', 'DATE')),
  CHECK (allowed_post_type IS NULL
         OR allowed_post_type IN ('TEXT', 'IMAGE', 'LINK', 'POLL')),
  CHECK (is_active IN (0, 1))
) ENGINE=InnoDB;

CREATE TABLE post_attribute_value (
  post_id BIGINT UNSIGNED NOT NULL,
  attribute_id BIGINT UNSIGNED NOT NULL,
  value_text VARCHAR(500) NULL,
  value_number DECIMAL(18,4) NULL,
  value_boolean BOOLEAN NULL,
  value_date DATE NULL,
  recorded_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (post_id, attribute_id),
  FOREIGN KEY (post_id) REFERENCES posts_joined (post_id) ON DELETE CASCADE,
  FOREIGN KEY (attribute_id)
    REFERENCES post_attribute_definition (attribute_id) ON DELETE RESTRICT,
  CONSTRAINT chk_post_attr_one_value CHECK (
    (value_text IS NOT NULL) + (value_number IS NOT NULL)
    + (value_boolean IS NOT NULL) + (value_date IS NOT NULL) = 1
  ),
  CONSTRAINT chk_post_attr_boolean
    CHECK (value_boolean IS NULL OR value_boolean IN (0, 1)),
  INDEX ix_post_attr_number (attribute_id, value_number, post_id),
  INDEX ix_post_attr_text (attribute_id, value_text(120), post_id),
  INDEX ix_post_attr_date (attribute_id, value_date, post_id)
) ENGINE=InnoDB;

DELIMITER //
CREATE PROCEDURE set_post_attribute(
  IN p_post BIGINT UNSIGNED,
  IN p_code VARCHAR(80),
  IN p_text VARCHAR(500),
  IN p_number DECIMAL(18,4),
  IN p_boolean BOOLEAN,
  IN p_date DATE
)
BEGIN
  DECLARE v_attribute BIGINT UNSIGNED;
  DECLARE v_value_type VARCHAR(16);
  DECLARE v_allowed VARCHAR(16);
  DECLARE v_post_type VARCHAR(16);
  DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;

  START TRANSACTION;
  SELECT post_type INTO v_post_type
  FROM posts_joined
  WHERE post_id = p_post AND is_complete = TRUE
  FOR UPDATE;
  SELECT attribute_id, value_type, allowed_post_type
    INTO v_attribute, v_value_type, v_allowed
  FROM post_attribute_definition
  WHERE attr_code = p_code AND is_active = TRUE;

  IF v_post_type IS NULL OR v_attribute IS NULL THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'post or attribute unavailable';
  END IF;
  IF v_allowed IS NOT NULL AND v_allowed <> v_post_type THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'attribute not allowed for post type';
  END IF;
  IF (v_value_type = 'TEXT') <> (p_text IS NOT NULL)
     OR (v_value_type = 'NUMBER') <> (p_number IS NOT NULL)
     OR (v_value_type = 'BOOLEAN') <> (p_boolean IS NOT NULL)
     OR (v_value_type = 'DATE') <> (p_date IS NOT NULL)
     OR (p_text IS NOT NULL) + (p_number IS NOT NULL)
        + (p_boolean IS NOT NULL) + (p_date IS NOT NULL) <> 1 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'typed attribute value mismatch';
  END IF;

  UPDATE post_attribute_value
  SET value_text = p_text, value_number = p_number,
      value_boolean = p_boolean, value_date = p_date,
      recorded_at = CURRENT_TIMESTAMP(6)
  WHERE post_id = p_post AND attribute_id = v_attribute;
  IF ROW_COUNT() = 0 THEN
    INSERT INTO post_attribute_value
      (post_id, attribute_id, value_text, value_number, value_boolean, value_date)
    VALUES (p_post, v_attribute, p_text, p_number, p_boolean, p_date);
  END IF;
  COMMIT;
END//
DELIMITER ;

INSERT INTO post_attribute_definition
  (attr_code, value_type, allowed_post_type, unit_code, description)
VALUES
  ('editorial_score', 'NUMBER', NULL, 'POINT', '편집 검수 기준의 0~100 점수'),
  ('reviewed_on', 'DATE', NULL, NULL, '편집자가 마지막으로 검수한 날짜'),
  ('is_featured_image', 'BOOLEAN', 'IMAGE', NULL, '대표 이미지 사용 여부');

SET @joined_text = (
  SELECT post_id FROM posts_joined
  WHERE post_type = 'TEXT' AND is_complete = TRUE
  ORDER BY post_id LIMIT 1
);
SET @joined_image = (
  SELECT post_id FROM posts_joined
  WHERE post_type = 'IMAGE' AND is_complete = TRUE
  ORDER BY post_id LIMIT 1
);
CALL set_post_attribute(@joined_text, 'editorial_score', NULL, 72.5, NULL, NULL);
CALL set_post_attribute(@joined_text, 'reviewed_on', NULL, NULL, NULL, '2026-07-16');
CALL set_post_attribute(@joined_text, 'editroial_score', NULL, 70, NULL, NULL);

SELECT d.attr_code, v.value_number, v.value_date
FROM post_attribute_value AS v
JOIN post_attribute_definition AS d ON d.attribute_id = v.attribute_id
WHERE v.post_id = @joined_text
ORDER BY d.attribute_id;

ERROR 1644 (45000): post or attribute unavailable

attr_code       | value_number | value_date
editorial_score | 72.5000      | NULL
reviewed_on     | NULL         | 2026-07-16

SELECT post_id, attribute_id FROM post_attribute_value
WHERE (value_text IS NOT NULL) + (value_number IS NOT NULL)
      + (value_boolean IS NOT NULL) + (value_date IS NOT NULL) <> 1;

SELECT v.post_id, d.attr_code, d.value_type
FROM post_attribute_value AS v
JOIN post_attribute_definition AS d ON d.attribute_id = v.attribute_id
WHERE (d.value_type = 'TEXT') <> (v.value_text IS NOT NULL)
   OR (d.value_type = 'NUMBER') <> (v.value_number IS NOT NULL)
   OR (d.value_type = 'BOOLEAN') <> (v.value_boolean IS NOT NULL)
   OR (d.value_type = 'DATE') <> (v.value_date IS NOT NULL);

SELECT v.post_id, d.attr_code
FROM post_attribute_value AS v
JOIN post_attribute_definition AS d ON d.attribute_id = v.attribute_id
JOIN posts_joined AS r ON r.post_id = v.post_id
WHERE d.allowed_post_type IS NOT NULL AND d.allowed_post_type <> r.post_type;

SELECT v.post_id, v.value_number
FROM post_attribute_value AS v
JOIN post_attribute_definition AS d ON d.attribute_id = v.attribute_id
WHERE d.attr_code = 'editorial_score'
  AND (v.value_number < 0 OR v.value_number > 100);

SELECT r.post_id, r.title, v.value_number AS editorial_score
FROM post_attribute_value AS v
JOIN post_attribute_definition AS d ON d.attribute_id = v.attribute_id
JOIN posts_joined AS r ON r.post_id = v.post_id
WHERE d.attr_code = 'editorial_score' AND v.value_number >= 70
ORDER BY v.value_number DESC;

post_id | title            | editorial_score
1       | joined 상속 읽기 | 72.5000