本教程详细介绍了在knex.js中，如何从mysql的`datetime`类型列中，仅根据日期部分进行数据筛选。针对直接使用`date()`函数报错的问题，文章提供了两种基于`whereraw`方法的解决方案，并强调了参数绑定的重要性，确保查询的安全性与准确性。
在数据库操作中，我们经常需要根据日期来筛选数据，即使原始数据存储在包含时间信息的datetime列中。在MySQL中，这通常通过内置的DATE()函数轻松实现，例如：SELECT * FROM table WHERE DATE(created_at) = '2022-08-15'。然而，当尝试在Knex.js这样的SQL查询构建器中实现类似逻辑时，开发者可能会遇到一些挑战。直接在where子句中使用date(column_name)可能会导致ReferenceError: date is not defined，因为Knex的where方法通常不直接解析为SQL函数调用。同时，如果仅使用.where(created_at, '=', '2022-08-15')，由于created_at是datetime类型，它将无法与纯日期字符串精确匹配。
核心解决方案：使用 whereRaw
Knex.js提供了whereRaw方法，允许开发者直接插入原始SQL片段到WHERE子句中，这为处理复杂或非标准SQL逻辑提供了极大的灵活性。通过whereRaw，我们可以将MySQL的DATE()函数直接应用到查询中。
方法一：使用参数绑定（推荐）
为了防止SQL注入攻击，强烈建议在whereRaw中使用参数绑定。这种方法将查询字符串与实际值分开，由数据库驱动程序负责安全地处理和转义参数。
示例代码：
function getData(req, res) {
const targetDate = '2022-08-15'; // 假设日期来自请求参数或变量
app.db('table')
.select('*') // 或者指定列如 'table.column1', 'table.column2'
.whereRaw('DATE(created_at) = ?', [targetDate])
.then(data => res.json(data))
.catch(err => res.status(500).json({ error: '查询失败', details: err.message }));
}说明：
- 'DATE(created_at) = ?' 是原始的SQL片段，其中?是占位符。
- [targetDate] 是一个数组，包含了要绑定到占位符的值。Knex会自动处理值的转义，确保查询的安全性。
- 这种方法是处理动态日期查询的最佳实践，因为它有效地结合了灵活性和安全性。
方法二：直接插入值（谨慎使用）
在某些特定场景下，如果能绝对信任输入值（例如，该值是硬编码的常量，而非用户输入），也可以选择直接将值插入到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(err => res.status(500).json({ error: '查询失败', details: err.message }));
}说明：
- 这里使用了模板字符串（反引号 `）来方便地将变量targetDate嵌入到SQL字符串中。
- 日期字符串需要用引号（单引号或双引号）括起来，以符合SQL语法。
- 警告： 仅当您能百分之百确定targetDate变量的值是安全且经过严格验证时才使用此方法。对于任何用户输入，务必使用参数绑定。
注意事项与最佳实践
- 安全性优先： 始终优先选择使用参数绑定（方法一），以防止SQL注入攻击。这是编写健壮和安全应用程序的基础。
-
索引使用： 在WHERE子句中对列使用函数（如DATE(created_at)）可能会导致数据库无法有效利用该列上的索引。如果created_at列上有索引，并且查询性能成为问题，可以考虑以下替代方案：
-
范围查询： 将日期筛选转换为时间戳范围查询，例如：
.where('created_at', '>=', '2022-08-15 00:00:00') .andWhere('created_at', '<', '2022-08-16 00:00:00')这种方法允许数据库使用created_at列上的索引。
- 额外日期列： 在表中添加一个独立的created_date列（DATE类型），并在数据插入时同步更新。这样可以直接对created_date列进行索引和查询。
-
范围查询： 将日期筛选转换为时间戳范围查询，例如：
- 时区考量： 如果您的应用程序和数据库处理不同时区的数据，或者created_at存储的是UTC时间，而您需要按本地日期筛选，则需要进行额外的时区转换。MySQL提供了CONVERT_TZ()等函数，或者在应用程序层面进行日期转换。
总结
在Knex.js中根据MySQL datetime列的日期部分进行数据筛选，最佳实践是利用whereRaw方法结合参数绑定。这不仅提供了实现复杂SQL逻辑的灵活性，更重要的是保障了应用程序的安全性。在选择查询方法时，开发者还应考虑性能和时区管理等因素，以构建高效且可靠的数据库交互层。
