我可以将MysQL时间戳记与datetime列进行比较吗？是坏吗

问题描述

所以，我有一个表ABC是时间戳，BCD是datetime。

So, I have a table where columns "ABC" is a timestamp and "BCD" is a datetime.

如果我这样做：

SELECT * FROM myTable WHERE ABC > BCD

是不是坏？它会影响性能吗？

is it bad? and will it affect performance?

他们如何在性能方面进行比较？

How do they compare in terms of performance?

推荐答案

是的，您可以将 datetime 与时间戳进行比较。这不错，但要注意以下几点：

Yes, you can compare a datetime with a timestamp. It's not bad, but be aware of the following:

这意味着日期如'1968-01-01'，而合法为 DATETIME 或 DATE 的值，作为 TIMESTAMP 值无效，转换为0。

This means that a date such as '1968-01-01', while legal as a DATETIME or DATE value, is not valid as a TIMESTAMP value and is converted to 0.

从。

请注意以下测试用例一切工作正常：

Note how the following test cases all works fine:

CREATE TABLE t1 (d1 datetime, d2 timestamp);

INSERT INTO t1 VALUES ('1968-01-01 00:00:00', '1980-01-01 00:00:00');
INSERT INTO t1 VALUES ('2040-01-01 00:00:00', '1980-01-01 00:00:00');

SELECT * FROM t1 WHERE d2 < d1;
+---------------------+---------------------+
| d1                  | d2                  |
+---------------------+---------------------+
| 2040-01-01 00:00:00 | 1980-01-01 00:00:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)

SELECT * FROM t1 WHERE d2 > d1;
+---------------------+---------------------+
| d1                  | d2                  |
+---------------------+---------------------+
| 1968-01-01 00:00:00 | 1980-01-01 00:00:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)

SELECT * FROM t1 WHERE d2 < '2040-01-01 00:00:00';
+---------------------+---------------------+
| d1                  | d2                  |
+---------------------+---------------------+
| 1968-01-01 00:00:00 | 1980-01-01 00:00:00 |
| 2040-01-01 00:00:00 | 1980-01-01 00:00:00 |
+---------------------+---------------------+
2 rows in set (0.00 sec)

SELECT * FROM t1 WHERE d2 > '2040-01-01 00:00:00';
Empty set (0.00 sec)

这篇关于我可以将MysQL时间戳记与datetime列进行比较吗？是坏吗的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！