Knex 中从 MySQL DATETIME 列按日期筛选数据的技巧

本教程旨在解决使用 knex 从 mysql 的 datetime 类型列中仅按日期部分筛选数据的常见问题。我们将探讨直接使用 date() 函数失败的原因，并详细介绍如何利用 knex 的 whereraw 方法实现安全有效的日期筛选，同时提供参数绑定和直接插入值的示例及注意事项。

Knex 中从 MySQL DATETIME 列按日期筛选数据的技巧

在数据库操作中，我们经常需要从包含日期和时间信息的 DATETIME 类型列中，仅根据日期部分进行数据筛选。例如，在 MySQL 中，可以直接使用 DATE() 函数来提取日期部分进行比较：

SELECT * FROM your_table WHERE DATE(created_at) = '2022-08-15';

然而，在使用 Knex.js 这样的 SQL 查询构建器时，直接将 MySQL 的 DATE() 函数应用于 where 子句可能会遇到问题。

常见误区：直接在 where 子句中使用 DATE()

许多开发者初次尝试在 Knex 中实现上述逻辑时，可能会尝试以下方式：

function getData(req, res) {
    app.db('table')
        .select('*')
        .where(date(created_at), '=', '2022-08-15') // 错误示例
        .then(data => res.json(data))
        .catch(err => res.status(500).send(err));
}

这段代码在执行时会抛出 ReferenceError: date is not defined 的错误。这是因为 date() 是一个 MySQL 数据库函数，而不是 JavaScript 环境中的一个全局函数或 Knex 查询构建器的方法。Knex 的 where 方法期望接收列名、操作符和值，或者是一个对象，它不会自动将字符串解析为数据库函数调用。

如果仅使用 .where(created_at, '=', '2022-08-15')，虽然不会报错，但由于 created_at 是 DATETIME 类型，而 '2022-08-15' 仅是日期，默认情况下数据库会将其解释为 '2022-08-15 00:00:00'。这会导致只有精确匹配该时间点的数据才会被选中，无法实现按日期范围筛选的目的。

正确方法：使用 whereRaw 进行原始 SQL 条件查询

为了在 Knex 中调用 MySQL 的 DATE() 函数或任何其他数据库特有的函数，我们需要使用 whereRaw 方法。whereRaw 允许您直接插入原始 SQL 字符串作为 WHERE 子句的一部分。

whereRaw 方法的签名通常是 whereRaw(sqlString, [bindings])。它接受一个包含原始 SQL 条件的字符串，以及一个可选的数组，用于绑定参数。

序列猴子开放平台

具有长序列、多模态、单模型、大数据等特点的超大规模语言模型

0

查看详情

1. 使用参数绑定（推荐）

使用参数绑定是推荐的做法，因为它能够有效防止 SQL 注入攻击，并确保查询的安全性。Knex 会自动处理参数的转义。

function getData(req, res) {
    const targetDate = '2022-08-15'; // 假设日期来自请求参数或已验证
    app.db('your_table')
        .select('*')
        .whereRaw('DATE(created_at) = ?', [targetDate])
        .then(data => res.json(data))
        .catch(err => res.status(500).send(err));
}

在这个例子中：

• 'DATE(created_at) = ?' 是原始 SQL 条件字符串。? 是一个占位符。
• [targetDate] 是一个数组，其中包含要绑定到占位符的值。Knex 会将 targetDate 的值安全地插入到 ? 的位置。

2. 直接插入值（谨慎使用）

如果您确定要插入的值是完全可信的（例如，硬编码的日期或经过严格内部验证的日期），并且不需要 Knex 进行转义，也可以直接将值嵌入到 SQL 字符串中。然而，这种方法存在 SQL 注入的风险，因此应极其谨慎使用。

function getData(req, res) {
    const trustedDate = '2022-08-15'; // 假设这是一个完全信任的、安全的日期字符串
    app.db('your_table')
        .select('*')
        .whereRaw(`DATE(created_at) = "${trustedDate}"`) // 注意字符串拼接和引号
        .then(data => res.json(data))
        .catch(err => res.status(500).send(err));
}

注意事项：

• 当直接插入字符串时，请确保日期值被正确地用单引号或双引号括起来，以符合 SQL 语法。
• 强烈建议始终优先使用参数绑定 (? 占位符) 来处理任何来自外部或不可信源的数据，以避免潜在的安全漏洞。

总结

在 Knex 中处理 MySQL DATETIME 列的日期筛选时，核心在于理解 Knex 查询构建器与底层数据库函数之间的交互方式。whereRaw 方法是实现此类高级或特定数据库操作的关键工具。为了确保应用程序的安全性与健壮性，请务必采纳参数绑定的最佳实践，避免直接将未经处理的用户输入拼接到 SQL 字符串中。通过这种方式，您可以高效且安全地从 DATETIME 列中筛选出符合特定日期条件的数据。

以上就是Knex 中从 MySQL DATETIME 列按日期筛选数据的技巧的详细内容，更多请关注php中文网其它相关文章！