我在我的数据库中有以下表之一，共享了“显示创建表”的输出：
CREATE TABLE `clues_rma_efficacy_triggers` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`data` int(11) NOT NULL DEFAULT '0',
`data_type` char(1) NOT NULL,
`parent_key_id` tinyint(3) NOT NULL DEFAULT '0',
`child_key_id` tinyint(3) NOT NULL DEFAULT '0',
`parent_flag` tinyint(3) NOT NULL DEFAULT '0',
`child_flag` tinyint(3) NOT NULL DEFAULT '0',
`datetime` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `data_child_key` (`data`,`parent_key_id`,`child_key_id`),
KEY `idx_pki_cf_cki` (`parent_key_id`,`child_flag`,`child_key_id`),
KEY `idx_pki_pf` (`parent_key_id`,`parent_flag`),
KEY `idx_cki_cf` (`child_key_id`,`child_flag`)
) ENGINE=InnoDB AUTO_INCREMENT=32436413 DEFAULT CHARSET=latin1
如您所见，datetime列是int字段，我在其中存储unix_time值 . 但我正在为SELECT查询之一获得意外行为 . 生产环境 步骤如下：
1个样本插入：
INSERT INTO `clues_rma_efficacy_triggers` (`data`,`data_type`,`parent_key_id`,`datetime`) VALUES (1234568197,'R',89,1522933144);
mysql> select * from clues_rma_efficacy_triggers where data = 1234568197;
+----------+------------+-----------+---------------+--------------+-------------+------------+------------+--------------+
| id | data | data_type | parent_key_id | child_key_id | parent_flag | child_flag | datetime | deleted_flag |
+----------+------------+-----------+---------------+--------------+-------------+------------+------------+--------------+
| 34905336 | 1234568197 | R | 89 | 0 | 0 | 0 | 1522933144 | 0 |
+----------+------------+-----------+---------------+--------------+-------------+------------+------------+--------------+
1 row in set (0.01 sec)
下面的查询不应该获取结果，但是它是获取结果（它忽略了完整的datetime子句并且在parent_key_id和parent_flag上工作）
SELECT * , from_unixtime( datetime ) FROM clues_rma_efficacy_triggers WHERE datetime BETWEEN (NOW( ) - ( 86400 *30 )) AND NOW( ) AND parent_key_id =89 AND parent_flag =0 LIMIT 200;
mysql> SELECT * , from_unixtime( datetime ) FROM clues_rma_efficacy_triggers WHERE datetime BETWEEN (NOW( ) - ( 86400 *30 )) AND NOW( ) AND parent_key_id =89 AND parent_flag =0 LIMIT 200 ;
+----------+------------+-----------+---------------+--------------+-------------+------------+------------+--------------+---------------------------+
| id | data | data_type | parent_key_id | child_key_id | parent_flag | child_flag | datetime | deleted_flag | from_unixtime( datetime ) |
+----------+------------+-----------+---------------+--------------+-------------+------------+------------+--------------+---------------------------+
| 34905336 | 1234568197 | R | 89 | 0 | 0 | 0 | 1522933144 | 0 | 2018-04-05 18:29:04 |
+----------+------------+-----------+---------------+--------------+-------------+------------+------------+--------------+---------------------------+
1 row in set, 2 warnings (0.01 sec)
收到警告：
mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------------------+
| Warning | 1292 | Incorrect datetime value: '1522933144' for column 'datetime' at row 1 |
| Warning | 1292 | Incorrect datetime value: '20180407571651' |
+---------+------+-----------------------------------------------------------------------+
2 rows in set (0.01 sec)
这是我转换部分之间的第一个子句时的输出：
mysql> SELECT (NOW( ) - ( 86400 *30 ));
+--------------------------+
| (NOW( ) - ( 86400 *30 )) |
+--------------------------+
| 20180407571651 |
+--------------------------+
1 row in set (0.00 sec)
Between的第二个条款当然是当前日期 . 所以解析后的查询应该变成：
SELECT * , from_unixtime( datetime ) FROM clues_rma_efficacy_triggers WHERE DATETIME BETWEEN 20180407571651 AND '2018-04-10 16:07:00' AND parent_key_id =89 AND parent_flag =0 LIMIT 200;
其中，当然不是取结果 .
mysql> SELECT * , from_unixtime( datetime ) FROM clues_rma_efficacy_triggers WHERE DATETIME BETWEEN 20180407571651 AND '2018-04-10 16:07:00' AND parent_key_id =89 AND parent_flag =0 LIMIT 200;
Empty set, 1 warning (0.00 sec)
收到警告：
mysql> SHOW WARNINGS; +---------+------+---------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '2018-04-10 16:07:00' |
+---------+------+---------------------------------------------------------+
1 row in set (0.01 sec)
我很困惑第一个查询是如何工作的 . Where 子句仅包含 AND 条件 . 它应该立即失败，因为在解析的查询中datetime子句不正确 . Verison输出：
mysql> SELECT VERSION();
+-----------------+
| VERSION() |
+-----------------+
| 10.1.12-MariaDB |
+-----------------+
1 row in set (0.00 sec)
