mysql - 如何使用sql从数据库中的日期列中导出年龄？

Question

我正在尝试使用 mysqli 显示特定年龄范围内(例如 10 到 20 岁之间)的所有用户.什么查询最适合推导年龄以及如何推导？

我目前有一个查询没有显示准​​确结果的问题。我想要的是显示这个年龄和那个年龄之间的用户，但下面的查询没有给出任何错误，而是显示甚至低于 10 岁或高于 20 岁的年龄。我有 2 个表 user 和 user_likes。

        user
        username(pk)   gender   date_of_birth      country
          John           M        2010-12-31        India
          Bing           M        2000-12-31        Italy
          Rose           F        2000-12-01         UK

         user_likes 
         username(fk)    gender     age_from      to_age
         John             F             18            30
         Bing             F             20            30
         Rose             M             18            25

$username下面是存储在该变量中的 session 。

        $sql = "SELECT gender, age_from, to_age FROM user_likes WHERE username = '$username'";
        $result = $conn->query($sql);
        if ($result->num_rows > 0)
        {
            while ($row = $result->fetch_assoc())
            {
                $gender1 = $row['gender'];
                $age_from = $row['age_from'];
                $to_age = $row['to_age'];
            }
        }

        $sql1 = "SELECT username, date_of_birth, TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) AS age FROM user WHERE NOT username = '$username' AND gender = '$gender1'  AND (date_of_birth BETWEEN '$age_from' AND '$to_age')";
        $result = $conn->query($sql1);
        if ($result->num_rows > 0)
        { 

            while ($row = $result->fetch_assoc())
            {                   
                echo "<div>";
                    echo "<span><a href = 'accounts.php?usernames=".$username."'><img src = '".$row['photo']."' alt = 'profile photo'></span>";
                    echo "<div>".$row['username']."</a><br><span>From".$row['country']."</span></div>";
                echo "</div>";
            }               
        }

上面代码中的问题是，如果我选择从 18 到 80 的年龄，即使 18 岁以下或 80 岁以上的年龄仍然显示，所以我相信这与

      TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) AS age

或

      date_of_birth BETWEEN '$age_from' AND '$to_age'

我期望的是 -- 假设我是 Rose，因为我的兴趣性别是男性并且选择的年龄范围是 18 到 25 岁，我只想要 'Bing'要显示的用户。

Answer 1

date_of_birth BETWEEN '$age_from' AND '$to_age'

让我们用一些值替换字段。

'2010-12-31' BETWEEN '18' AND '20'

这是胡说八道，你可能更愿意使用

...
AND TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) BETWEEN '$age_from' AND '$to_age'

例如:

模式(MySQL v5.7)

CREATE TABLE users (
  `username` VARCHAR(4),
  `gender` VARCHAR(1),
  `date_of_birth` VARCHAR(10),
  `country` VARCHAR(5)
);

INSERT INTO users
  (`username`, `gender`, `date_of_birth`, `country`)
VALUES
  ('John', 'M', '2010-12-31', 'India'),
  ('Bing', 'M', '2000-12-31', 'Italy'),
  ('Rose', 'F', '2000-12-01', 'UK');

---

查询#1

SELECT username,
       date_of_birth,
       TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) AS "age"
FROM users
WHERE (TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) BETWEEN '18' AND '20');

输出

| username | date_of_birth | age |
| -------- | ------------- | --- |
| Bing     | 2000-12-31    | 18  |
| Rose     | 2000-12-01    | 18  |

---

查询#2

SELECT username,
       date_of_birth,
       TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) AS "age"
FROM users
WHERE (TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) BETWEEN '01' AND '10');

输出

| username | date_of_birth | age |
| -------- | ------------- | --- |
| John     | 2010-12-31    | 8   |

---

View on DB Fiddle