mysql - 当某些记录有月/日/年而其他记录只有年时，如何存储日期？

我很难决定如何在我的专辑数据库中存储发行日期。有些专辑有月，日和年，但其他专辑只有年。如果要显示两种格式，如何存储这些发行日期？我注意到allmusic.com做到了。我的同事告诉我，他认为它们将月，日和年存储为数据库中的单独字段，但是我认为这将使按时间顺序排列专辑变得困难。关于应该如何做的任何建议？

我正在使用MySQL，但我想将其作为有关关系数据库设计的一般问题。

最佳答案

我将使用DATE类型存储日期，并使用准确性列。

SQL Fiddle

MySQL模式：

CREATE TABLE Album(
  Title               VARCHAR(40),
  ReleaseDate         DATE,
  ReleaseDateAccuracy ENUM('D', 'M', 'Y')
);

INSERT INTO Album
  (Title, ReleaseDate, ReleaseDateAccuracy)
VALUES
  ('Foo', '2015-01-01', 'Y'),
  ('Bar', '2015-04-01', 'M'),
  ('Baz', '2015-04-06', 'D');

查询：

SELECT Title,
  CASE ReleaseDateAccuracy
    WHEN 'Y' THEN DATE_FORMAT(ReleaseDate, '%Y')
    WHEN 'M' THEN DATE_FORMAT(ReleaseDate, '%Y-%m')
    WHEN 'D' THEN DATE_FORMAT(ReleaseDate, '%Y-%m-%d')
  END AS FormattedDate
  FROM Album

结果：

| Title | FormattedDate |
|-------|---------------|
|   Foo |          2015 |
|   Bar |       2015-04 |
|   Baz |    2015-04-06 |