主页 / dba / 问题 / 305189
Accepted
Dolphin
Asked: 2021-12-21 01:19:20 +0800 CST

如何在mysql中使用非标准日期时间格式计算差异年份

  • 772

我的数据库（MySQL 5.7）像这样存储用户生日格式1990年1月1日，现在我想从这种格式日期时间计算用户年龄。我的 sql 看起来像这样，但为所有数据返回 null：

select period_diff(date_format(now(), '%Y'), date_format(birthday, '%Y年')) as months 
from spark_user;

表 DDL 如下所示：

CREATE TABLE `spark_user` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `birthday` varchar(64) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `index_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8mb4;

我也试过这样：

select period_diff(date_format(now(), '%Y'), date_format(STR_TO_DATE(birthday, '%Y'), '%Y')) as months 
from spark_user;

这有效：

    select period_diff( date_format(STR_TO_DATE(birthday, '%Y年%m月%d日'), '%Y'),date_format(now(), '%Y')) as months,
birthday as birthday 
from spark_user;

但结果很奇怪：

57  1990年1月1日
57  1990年1月1日
57  1990年1月1日
57  1990年10月10日
47  1980年1月1日
-20 2001年1月1日
63  1996年10月10日
mysql

3 个回答

  1. 像这样存储用户生日格式...

    关于关系数据库的许多不成文规则之一 -

    日期没有格式...

    ...或者，至少，你或我不需要担心。

    如果您认为您的日期具有“格式”，那么您几乎肯定会查看字符数据，而不是正确的日期，并且正如您所发现的，尝试使用恰好包含日期值的字符数据是困难的。

    始终为正确的工作使用正确的数据类型。

    当然，STR_TO_DATE 将字符值转换为日期值，但这是根据数据库自己的规则进行的。
    究竟是 07 年 1 月 4 日是什么时候？一月？四月？2001 年还是 2007 年？
    （你会找到适合你的，一切都会好起来的——直到你不得不将你的数据库移植到其他一些 DBMS，规则不同，所有的地狱都崩溃了）。
    此外，以这种方式使用函数会阻止数据库使用您在该字段上拥有的任何索引 - 表扫描，我们来了。

    • 1
  2. Best Answer
    YEAR(CURDATE()) - LEFT(birthday, 4)

    注意：最多会关闭 1 年，具体取决于当前日期和出生年份。

    • 1

  3. 我使用这个 sql 命令，有效：

    select YEAR(now()) - YEAR(STR_TO_DATE(birthday, '%Y年%m月%d日'))
    • 0

相关问题