Knex.js 中从 MySQL datetime 列按日期筛选数据的实用指南

本教程旨在解决 knex.js 在 mysql 中按日期筛选 `datetime` 类型列数据时遇到的挑战。针对 knex 直接调用 `date()` 函数的限制，文章详细阐述了如何利用 `whereraw` 方法实现这一功能，并提供了两种实现方式：安全的参数绑定和便捷的直接值插入。同时，强调了在使用 `whereraw` 时应关注的安全性考量，以确保构建高效且健壮的数据库查询。

在开发基于 Node.js 和 MySQL 的应用时，我们经常需要从数据库中检索数据。当数据库中的时间信息存储在 datetime 类型的列中，而我们仅希望根据日期部分进行筛选时，会遇到一些挑战。例如，在 MySQL 中，可以直接使用 DATE() 函数来提取 datetime 列的日期部分进行比较：

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

然而，在使用 Knex.js 这样的 SQL 查询构建器时，直接将 DATE() 函数作为 where 条件的一部分可能会导致 ReferenceError: date is not defined 错误，因为 Knex 的 where 方法默认期望列名或 Knex 内部函数，而非直接的 SQL 函数调用。如果简单地使用 where(created_at, '=', '2022-08-15')，由于 created_at 是 datetime 类型，而我们提供的参数是日期字符串，将无法精确匹配，因为 datetime 类型通常包含时间信息（例如 '2022-08-15 00:00:00'）。

使用 whereRaw 解决日期筛选问题

Knex.js 提供了 whereRaw 方法，允许开发者直接插入原生的 SQL 片段作为查询条件。这正是解决上述问题的关键。whereRaw 方法接受一个 SQL 字符串作为第一个参数，以及一个可选的参数数组作为第二个参数，用于绑定到 SQL 字符串中的占位符。

方法一：使用参数绑定（推荐）

参数绑定是构建 SQL 查询时最安全和推荐的做法，它可以有效防止 SQL 注入攻击。通过将动态值作为单独的参数传递，数据库驱动会负责正确地转义这些值。

以下是如何使用 whereRaw 和参数绑定来按日期筛选 datetime 列的示例：

function getData(req, res) {
    const targetDate = '2022-08-15'; // 假设日期从请求中获取或已定义
    app.db('table')
        .select('*') // 选择所有列，或指定所需列如 'table.id', 'table.name'
        .whereRaw('DATE(created_at) = ?', [targetDate])
        .then(data => res.json(data))
        .catch(error => res.status(500).json({ message: '查询失败', error }));
}

在上述代码中：

序列猴子开放平台

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

0

查看详情

• 'DATE(created_at) = ?' 是原生 SQL 片段，? 是一个占位符。
• [targetDate] 是一个数组，其中包含要绑定到占位符的值。Knex 会自动处理 targetDate 的转义。

方法二：直接插入值（谨慎使用）

如果开发者对输入值的来源完全信任，并且确定值不需要任何转义（例如，硬编码的日期字符串或经过严格验证的输入），也可以将值直接插入到 SQL 字符串中。然而，这种方法存在潜在的 SQL 注入风险，因此不建议用于处理用户提供的任何输入。

function getData(req, res) {
    const targetDate = '2022-08-15'; // 假设日期是一个已知且安全的值
    app.db('table')
        .select('*')
        .whereRaw(`DATE(created_at) = "${targetDate}"`) // 使用模板字符串插入值
        .then(data => res.json(data))
        .catch(error => res.status(500).json({ message: '查询失败', error }));
}

请注意，这里使用了模板字符串（反引号 `）来方便地将 targetDate 变量嵌入到 SQL 字符串中，并用双引号 " 将日期值包裹起来，以符合 SQL 字符串字面量的要求。

注意事项

1. 安全性：始终优先使用参数绑定（方法一）。直接插入值（方法二）应仅限于完全信任的数据源，并且开发者必须确保这些数据不会包含恶意 SQL 代码。否则，这可能导致严重的 SQL 注入漏洞。
2. 数据库兼容性：DATE() 函数在 MySQL 中是标准用法，但在其他数据库系统（如 PostgreSQL、SQL Server）中，提取日期部分的函数名称可能不同（例如 PostgreSQL 的 DATE() 也能工作，或 created_at::date 类型转换）。在使用 whereRaw 时，请确保您的原生 SQL 片段与目标数据库兼容。
3. 性能：在 created_at 列上使用 DATE() 函数可能会阻止数据库利用该列上的索引。如果性能是关键考量，并且 created_at 列上有索引，可以考虑其他查询策略，例如范围查询：

   // 假设 targetDate = '2022-08-15'
   const startDate = `${targetDate} 00:00:00`;
   const endDate = `${targetDate} 23:59:59`;
   app.db('table')
       .select('*')
       .where('created_at', '>=', startDate)
       .andWhere('created_at', '<=', endDate)
       .then(data => res.json(data));

   登录后复制

   这种范围查询通常能够更好地利用 created_at 列上的索引。

总结

通过 Knex.js 的 whereRaw 方法，我们可以灵活地将原生 SQL 函数和逻辑融入到查询构建器中，从而解决了在 MySQL 中按日期筛选 datetime 列的特定需求。在实践中，为了确保应用程序的安全性和健壮性，强烈建议采用参数绑定的方式来构建 whereRaw 查询。同时，也应根据具体场景和性能要求，考虑是否采用范围查询作为替代方案。

以上就是Knex.js 中从 MySQL datetime 列按日期筛选数据的实用指南的详细内容，更多请关注php中文网其它相关文章！