mysql - 是否可以使用 MySQL 在日期范围内搜索年份

Question

给定的是包含一段时间的数据，跨越数年。就像这样:

| ID | Name               | Alive                 |
|----|--------------------|-----------------------|
| 1  | Washington, George | 1732-02-22/1799-12-14 |
| 2  | Adams, John        | 1735-10-30/1826-07-04 |
| 3  | Jefferson, Thomas  | 1743-04-13/1826-07-04 |
  …

是否可以通过搜索中间日期(所有字段，仅一年)(如搜索词 1788)产生结果的方式将此数据存储在 MySQL 中？

我正在寻找的是这样的:

CREATE TABLE t (
    id INT NOT NULL,
    name VARCHAR(30),
    alive DATERANGE
);

SELECT * FROM t WHERE * LIKE '%1788%'

我看到的唯一解决方案是添加另一个包含年份列表的列，(1732,1733,...) 但我想还有更好的解决方案。我需要一个或两个字段中的日期吗？我需要什么列类型才能工作？我可以在该列中指定日期范围内(例如 1155/1227)还是必须在插入之前重写它们(例如 1155-01-01/1227-12-31)?

边界匹配也应返回。搜索字符串 1799 仍应返回 George Washington，即使他从 1 月 1 日到 12 月 31 日(含)期间不在世。我想这很简单，因为它已经是一个字符串匹配。

Answer 1

如果您可以编辑您的数据，那么我建议将其更改为 Born & Died 字段，如果不能，那么我们可以对 Born 和 SUBSTRING_INDEX 已死的函数。

SELECT ID, Name, Alive, 
LEFT([ColName],INSTR([Alive],"/")-1) AS Born, 
SUBSTRING_INDEX(Alive,'/',-1) AS Died
FROM t

这将拆分出生日期和死亡日期:

| ID | Name               | Alive                 | Born       | Died       |
|----|--------------------|-----------------------|------------|------------|
| 1  | Washington, George | 1732-02-22/1799-12-14 | 1732-02-22 | 1799-12-14 |
| 2  | Adams, John        | 1735-10-30/1826-07-04 | 1735-10-30 | 1826-07-04 |
| 3  | Jefferson, Thomas  | 1743-04-13/1826-07-04 | 1743-04-13 | 1826-07-04 |

然后你可以使用:

WHERE Alive LIKE '%1788%'

搜索日期。

或单独作为 Born:

WHERE LEFT([ColName],INSTR([Alive],"/")-1) LIKE '%1788%'

死亡:

WHERE SUBSTRING_INDEX(Alive,'/',-1) LIKE '%1788%'

或者，如果您只想在 Born 和 Died 字段中使用年份，请使用额外的 LEFT 函数:

SELECT ID, Name, Alive, 
LEFT(LEFT([ColName],INSTR([Alive],"/")-1),4) AS Born, 
LEFT(SUBSTRING_INDEX(Alive,'/',-1),4) AS Died
FROM t

这会给你:

| ID | Name               | Alive                 | Born | Died |
|----|--------------------|-----------------------|------|------|
| 1  | Washington, George | 1732-02-22/1799-12-14 | 1732 | 1799 |
| 2  | Adams, John        | 1735-10-30/1826-07-04 | 1735 | 1826 |
| 3  | Jefferson, Thomas  | 1743-04-13/1826-07-04 | 1743 | 1826 |

编辑:

您可以反过来使用 BETWEEN 函数。

SELECT ID, Name, Alive, 
LEFT(LEFT([ColName],INSTR([Alive],"/")-1),4) AS Born, 
LEFT(SUBSTRING_INDEX(Alive,'/',-1),4) AS Died
FROM t
WHERE 1788 BETWEEN LEFT(LEFT([ColName],INSTR([Alive],"/")-1),4) AND LEFT(SUBSTRING_INDEX(Alive,'/',-1),4)