摘要
错误使用的例子:
todayZeroTime, _ := time.Parse("2006-01-02", time.Now().Format("2006-01-02"))
db = db.Where("(start_at = ? || end_at = ?)", todayZeroTime, todayZeroTime.AddDate(0, 0, -1))
例子修改如下:
db = db.Where("(start_at = cast(? as DATE) || end_at = cast(? as DATE))", time.Now(), time.Now().AddDate(0, 0, -1))
问题起因
TiDB某条记录有date字段字段,值例如为2020.09.14,sql直接使用:
SELECT * FROM `rule_tag_ele_num` WHERE `rule_tag_ele_num`.`deleted_at` IS NULL AND ((tag_id = 1 AND date BETWEEN '2020-09-14 00:00:00' AND '2020-09-16 00:00:00'))
查询的话,2020.09.14这条记录就不会返回,但是mysql是可以的,但是TiDB声称完全兼容mysql,所以是不是因为我between用的话不规范的问题,对TiDB和mysql都不规范,但是这种不规范被mysql漫长的版本迭代中给容错掉了,遂查mysql官方文档的标准推荐用法
mysql官方文档
https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#operator_between
If
expr
is greater than or equal tomin
andexpr
is less than or equal tomax
,BETWEEN
returns1
, otherwise it returns0
. This is equivalent to the expression(
if all the arguments are of the same type. Otherwise type conversion takes place according to the rules described in Section 12.3, “Type Conversion in Expression Evaluation”, but applied to all the three arguments.min
<=expr
ANDexpr
<=max
)mysql> SELECT 2 BETWEEN 1 AND 3, 2 BETWEEN 3 and 1; -> 1, 0 mysql> SELECT 1 BETWEEN 2 AND 3; -> 0 mysql> SELECT 'b' BETWEEN 'a' AND 'c'; -> 1 mysql> SELECT 2 BETWEEN 2 AND '3'; -> 1 mysql> SELECT 2 BETWEEN 2 AND 'x-3'; -> 0
For best results when using
BETWEEN
with date or time values, useCAST()
to explicitly convert the values to the desired data type. Examples: If you compare aDATETIME
to twoDATE
values, convert theDATE
values toDATETIME
values. If you use a string constant such as'2001-1-1'
in a comparison to aDATE
, cast the string to aDATE
.
果然,其中的:
For best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type.
给出了明确的建议。
修改后的sql
("SELECT * FROM `extension_tag_num` WHERE `extension_tag_num`.`deleted_at` IS NULL AND ((basic_id = ? AND date BETWEEN cast(? as DATE) AND cast(? as DATE)))", tagId, dateStart, dateEnd)
此时unit test在mysql和TiDB的执行都pass了