mysql5.7和8.0及mariadb时间条件查询结果差别

ä¸ãç¯å¢

äºè¿å¶çæ¬mariadb10-2.10,mariadb10-2.17 mysql-5.7.22 mysql5.7.32 mysql-8.0.28

ç¯å¢sql_modeé½æç§å¦ä¸:

sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

åå¤æµè¯æ°æ®ï¼

CREATE TABLE `t_user` (
 `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ä¸»é®',
 `user_name` varchar(48) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'åå·¥å§å/æºæåç§°',
 `nick_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'æµç§°',
 `phone` varchar(15) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'åå·¥ææºå·/æºæææºå·',
 `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'åå·¥/æºæé®ç®± ',
 `employee_id` bigint(11) NOT NULL DEFAULT '0' COMMENT 'äººååå·¥id',
 `user_type` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0:ä¸ªäººè´¦å·-é»è®¤,1:æºæè´¦å·',
 `avatar` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'å¤´å ',
 `profile_bio` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'ä¸ªæ§ç¾å',
 `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '1:å¯ç¨-é»è®¤,0:ç¦ç¨',
 `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'åå»ºæ¶é´',
 `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'æ´æ°æ¶é´',
 PRIMARY KEY (`id`),
 KEY `idx_user_emp_id` (`employee_id`) USING BTREE,
 KEY `idx_user_email` (`email`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='ç¨æ·è¡¨';

insertæµè¯æ°æ®:
insert into t_user(`user_name`,`nick_name`,`phone`,`email`,`create_time`)value('å°è±','èå¤©','13800000000','3056782@qq.com','2022-04-01 10:21:35');
insert into t_user(`user_name`,`nick_name`,`phone`,`email`,`create_time`)value('èå','èå°','13800000000','3056782@qq.com','2022-04-02 10:21:35');
insert into t_user(`user_name`,`nick_name`,`phone`,`email`,`create_time`)value('èæ','é¿å','13800000001','3056782@qq.com','2022-04-03 10:21:35');

insert into t_user(`user_name`,`nick_name`,`phone`,`email`,`create_time`)value('æ¨æ','å¨æ','13800000002','3056782@qq.com','2022-04-03 10:21:35');
insert into t_user(`user_name`,`nick_name`,`phone`,`email`,`create_time`)value('çæ','å°æ','13800000005','3056782@qq.com','2022-04-04 11:21:35');
insert into t_user(`user_name`,`nick_name`,`phone`,`email`,`create_time`)value('æå','å°æ','13800000225','3056782@qq.com','2022-04-05 11:21:35');
insert into t_user(`user_name`,`nick_name`,`phone`,`email`,`create_time`)value('èµµå','å°æ','1380033226','3056782@qq.com','2022-04-06 11:21:35');

äºãå·ä½æµè¯è¿ç¨

2.1ãmysqlçæ¬ä¸º5.7.22å5.7.32

root@tidb04 16:50:  [test001]> select version();
+------------+
| version()  |
+------------+
| 5.7.22-log |
+------------+
1 row in set (0.00 sec)


æ¥ä¸åºç»æ:
root@tidb04 16:50:  [test001]> select * from t_user where create_time > '2022-04-0400:00:00';
Empty set, 1 warning (0.00 sec)

root@tidb04 16:49:  [test001]> select * from t_user where create_time > concat(current_date,'00:00:00') ;
Empty set, 1 warning (0.00 sec)

root@tidb04 16:49:  [test001]> show warnings;
+---------+------+----------------------------------------------------------------------------------+
| Level   | Code | Message                                                                          |
+---------+------+----------------------------------------------------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2022-04-0400:00:00' for column 'create_time' at row 1 |
+---------+------+----------------------------------------------------------------------------------+
1 row in set (0.00 sec)

å»æç©ºæ ¼,æ¾ç¤ºæ¥è¯¢æ£ç¡®çç»æ:
root@tidb04 16:49:  [test001]> select * from t_user where create_time > concat(current_date,' 00:00:00') ;
+------+-----------+-----------+-------------+----------------+-------------+-----------+--------+-------------+--------+---------------------+---------------------+
| id   | user_name | nick_name | phone       | email          | employee_id | user_type | avatar | profile_bio | status | create_time         | update_time         |
+------+-----------+-----------+-------------+----------------+-------------+-----------+--------+-------------+--------+---------------------+---------------------+
| 2016 | çæ      | å°æ      | 13800000005 | 3056782@qq.com |           0 |         0 |        |             |      1 | 2022-04-04 11:21:35 | 2022-04-04 16:19:54 |
+------+-----------+-----------+-------------+----------------+-------------+-----------+--------+-------------+--------+---------------------+---------------------+
1 row in set (0.00 sec)
root@tidb04 16:56:  [test001]> select * from t_user where create_time > '2022-04-04 00:00:00';
+------+-----------+-----------+-------------+----------------+-------------+-----------+--------+-------------+--------+---------------------+---------------------+
| id   | user_name | nick_name | phone       | email          | employee_id | user_type | avatar | profile_bio | status | create_time         | update_time         |
+------+-----------+-----------+-------------+----------------+-------------+-----------+--------+-------------+--------+---------------------+---------------------+
| 2016 | çæ      | å°æ      | 13800000005 | 3056782@qq.com |           0 |         0 |        |             |      1 | 2022-04-04 11:21:35 | 2022-04-04 16:19:54 |
+------+-----------+-----------+-------------+----------------+-------------+-----------+--------+-------------+--------+---------------------+---------------------+
1 row in set (0.00 sec)

2.2ãmysql8.0.28 æµè¯

root@tidb05 21:28:  [test001]> select * from t_user where create_time > '2022-04-04 00:00:00';
+----+-----------+-----------+-------------+----------------+-------------+-----------+--------+-------------+--------+---------------------+---------------------+
| id | user_name | nick_name | phone       | email          | employee_id | user_type | avatar | profile_bio | status | create_time         | update_time         |
+----+-----------+-----------+-------------+----------------+-------------+-----------+--------+-------------+--------+---------------------+---------------------+
|  5 | çæ      | å°æ      | 13800000005 | 3056782@qq.com |           0 |         0 |        |             |      1 | 2022-04-04 11:21:35 | 2022-04-04 16:33:51 |
|  6 | æå      | å°æ      | 13800000225 | 3056782@qq.com |           0 |         0 |        |             |      1 | 2022-04-05 11:21:35 | 2022-04-04 21:25:34 |
|  7 | èµµå      | å°æ      | 1380033226  | 3056782@qq.com |           0 |         0 |        |             |      1 | 2022-04-06 11:21:35 | 2022-04-04 21:25:42 |
+----+-----------+-----------+-------------+----------------+-------------+-----------+--------+-------------+--------+---------------------+---------------------+
3 rows in set (0.00 sec)


root@tidb05 21:25:  [test001]> select * from t_user where create_time > concat(current_date,' 00:00:00') ;
+----+-----------+-----------+-------------+----------------+-------------+-----------+--------+-------------+--------+---------------------+---------------------+
| id | user_name | nick_name | phone       | email          | employee_id | user_type | avatar | profile_bio | status | create_time         | update_time         |
+----+-----------+-----------+-------------+----------------+-------------+-----------+--------+-------------+--------+---------------------+---------------------+
|  5 | çæ      | å°æ      | 13800000005 | 3056782@qq.com |           0 |         0 |        |             |      1 | 2022-04-04 11:21:35 | 2022-04-04 16:33:51 |
|  6 | æå      | å°æ      | 13800000225 | 3056782@qq.com |           0 |         0 |        |             |      1 | 2022-04-05 11:21:35 | 2022-04-04 21:25:34 |
|  7 | èµµå      | å°æ      | 1380033226  | 3056782@qq.com |           0 |         0 |        |             |      1 | 2022-04-06 11:21:35 | 2022-04-04 21:25:42 |
+----+-----------+-----------+-------------+----------------+-------------+-----------+--------+-------------+--------+---------------------+---------------------+
3 rows in set (0.00 sec)

å»æç©ºæ ¼çè¯å¨é¨æ¾ç¤ºåºæ¥,å¾å°çç»ææ¯éè¯¯çï¼
root@tidb05 21:25:  [test001]> select * from t_user where create_time > concat(current_date,'00:00:00') ;
+----+-----------+-----------+-------------+----------------+-------------+-----------+--------+-------------+--------+---------------------+---------------------+
| id | user_name | nick_name | phone       | email          | employee_id | user_type | avatar | profile_bio | status | create_time         | update_time         |
+----+-----------+-----------+-------------+----------------+-------------+-----------+--------+-------------+--------+---------------------+---------------------+
|  1 | å°è±      | èå¤©      | 13800000000 | 3056782@qq.com |           0 |         0 |        |             |      1 | 2022-04-01 10:21:35 | 2022-04-04 16:32:31 |
|  2 | èå      | èå°      | 13800000000 | 3056782@qq.com |           0 |         0 |        |             |      1 | 2022-04-02 10:21:35 | 2022-04-04 16:32:42 |
|  3 | èæ      | é¿å      | 13800000001 | 3056782@qq.com |           0 |         0 |        |             |      1 | 2022-04-03 10:21:35 | 2022-04-04 16:32:54 |
|  4 | æ¨æ      | å¨æ      | 13800000002 | 3056782@qq.com |           0 |         0 |        |             |      1 | 2022-04-03 10:21:35 | 2022-04-04 16:33:30 |
|  5 | çæ      | å°æ      | 13800000005 | 3056782@qq.com |           0 |         0 |        |             |      1 | 2022-04-04 11:21:35 | 2022-04-04 16:33:51 |
|  6 | æå      | å°æ      | 13800000225 | 3056782@qq.com |           0 |         0 |        |             |      1 | 2022-04-05 11:21:35 | 2022-04-04 21:25:34 |
|  7 | èµµå      | å°æ      | 1380033226  | 3056782@qq.com |           0 |         0 |        |             |      1 | 2022-04-06 11:21:35 | 2022-04-04 21:25:42 |
+----+-----------+-----------+-------------+----------------+-------------+-----------+--------+-------------+--------+---------------------+---------------------+
7 rows in set, 7 warnings (0.00 sec)


root@tidb05 21:29:  [test001]> show warnings;
+---------+------+----------------------------------------------------------------------------------+
| Level   | Code | Message                                                                          |
+---------+------+----------------------------------------------------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2022-04-0400:00:00' for column 'create_time' at row 1 |
| Warning | 1292 | Incorrect datetime value: '2022-04-0400:00:00' for column 'create_time' at row 2 |
| Warning | 1292 | Incorrect datetime value: '2022-04-0400:00:00' for column 'create_time' at row 3 |
| Warning | 1292 | Incorrect datetime value: '2022-04-0400:00:00' for column 'create_time' at row 4 |
| Warning | 1292 | Incorrect datetime value: '2022-04-0400:00:00' for column 'create_time' at row 5 |
| Warning | 1292 | Incorrect datetime value: '2022-04-0400:00:00' for column 'create_time' at row 6 |
| Warning | 1292 | Incorrect datetime value: '2022-04-0400:00:00' for column 'create_time' at row 7 |
+---------+------+----------------------------------------------------------------------------------+
7 rows in set (0.00 sec)

2.3ã10.2.10-MariaDB-å10.2.17-MariaDBæµè¯

(Mon Apr  4 20:46:51 2022)[root@MySQL][test]>select version();
+---------------------+
| version()           |
+---------------------+
| 10.2.10-MariaDB-log |
+---------------------+
1 row in set (0.00 sec)

(Mon Apr  4 20:51:17 2022)[root@MySQL][test]>select * from t_user where create_time > '2022-04-04 00:00:00';
+----+-----------+-----------+-------------+----------------+-------------+-----------+--------+-------------+--------+---------------------+---------------------+
| id | user_name | nick_name | phone       | email          | employee_id | user_type | avatar | profile_bio | status | create_time         | update_time         |
+----+-----------+-----------+-------------+----------------+-------------+-----------+--------+-------------+--------+---------------------+---------------------+
|  4 | çæ      | å°æ      | 13800000005 | 3056782@qq.com |           0 |         0 |        |             |      1 | 2022-04-04 11:21:35 | 2022-04-04 20:46:35 |
+----+-----------+-----------+-------------+----------------+-------------+-----------+--------+-------------+--------+---------------------+---------------------+
1 row in set (0.00 sec)

(Mon Apr  4 20:51:31 2022)[root@MySQL][test]>select * from t_user where create_time > concat(current_date,' 00:00:00') ;
+----+-----------+-----------+-------------+----------------+-------------+-----------+--------+-------------+--------+---------------------+---------------------+
| id | user_name | nick_name | phone       | email          | employee_id | user_type | avatar | profile_bio | status | create_time         | update_time         |
+----+-----------+-----------+-------------+----------------+-------------+-----------+--------+-------------+--------+---------------------+---------------------+
|  4 | çæ      | å°æ      | 13800000005 | 3056782@qq.com |           0 |         0 |        |             |      1 | 2022-04-04 11:21:35 | 2022-04-04 20:46:35 |
+----+-----------+-----------+-------------+----------------+-------------+-----------+--------+-------------+--------+---------------------+---------------------+
1 row in set (0.00 sec)

**mariadb ç¼ºå°ç©ºæ ¼ æ´ä¸ªè¡¨æ°æ®é½åºæ¥äºï¼å¾å°çä¸æ¯è¦æ¥è¯¢çæ°æ®ï¼è¿ä¸ªæåºè¯¥ç®mariadbçæ¤çæ¬çä¸ä¸ªbug**
(Mon Apr  4 20:57:33 2022)[root@MySQL][test]>select * from t_user where create_time > concat(current_date,'00:00:00') ;
+----+-----------+-----------+-------------+----------------+-------------+-----------+--------+-------------+--------+---------------------+---------------------+
| id | user_name | nick_name | phone       | email          | employee_id | user_type | avatar | profile_bio | status | create_time         | update_time         |
+----+-----------+-----------+-------------+----------------+-------------+-----------+--------+-------------+--------+---------------------+---------------------+
|  1 | èå      | èå°      | 13800000000 | 3056782@qq.com |           0 |         0 |        |             |      1 | 2022-04-02 10:21:35 | 2022-04-04 20:46:34 |
|  2 | èæ      | é¿å      | 13800000001 | 3056782@qq.com |           0 |         0 |        |             |      1 | 2022-04-03 10:21:35 | 2022-04-04 20:46:34 |
|  3 | æ¨æ      | å¨æ      | 13800000002 | 3056782@qq.com |           0 |         0 |        |             |      1 | 2022-04-03 10:21:35 | 2022-04-04 20:46:34 |
|  4 | çæ      | å°æ      | 13800000005 | 3056782@qq.com |           0 |         0 |        |             |      1 | 2022-04-04 11:21:35 | 2022-04-04 20:46:35 |
|  5 | æå      | å°æ      | 13800002225 | 3056782@qq.com |           0 |         0 |        |             |      1 | 2022-04-05 11:21:35 | 2022-04-04 20:57:13 |
|  6 | èµµå      | hha       | 13800002225 | 3056782@qq.com |           0 |         0 |        |             |      1 | 2022-04-06 11:21:35 | 2022-04-04 20:57:31 |
+----+-----------+-----------+-------------+----------------+-------------+-----------+--------+-------------+--------+---------------------+---------------------+
6 rows in set, 1 warning (0.00 sec)

(Mon Apr  4 20:57:37 2022)[root@MySQL][test]>select current_date();
+----------------+
| current_date() |
+----------------+
| 2022-04-04     |
+----------------+
1 row in set (0.00 sec)

(Mon Apr  4 20:53:59 2022)[root@MySQL][test]>show warnings;
+---------+------+------------------------------------------------+
| Level   | Code | Message                                        |
+---------+------+------------------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2022-04-0400:00:00' |
+---------+------+------------------------------------------------+
1 row in set (0.00 sec)

mysql5.7和8.0及mariadb时间条件查询结果差别

ä¸ãç¯å¢

äºãå·ä½æµè¯è¿ç¨

2.1ãmysqlçæ¬ä¸º5.7.22å5.7.32

2.2ãmysql8.0.28 æµè¯

2.3ã10.2.10-MariaDB-å10.2.17-MariaDBæµè¯

继续阅读

2022秋招面试总结（cpp+java+测开）百度测开一面字节后端一面虾皮后端一面虾皮后端二面

数据库之DDL操作数据库DDL操作数据库DDL操作数据表

数据库之DQL操作数据库

mysql优化（sql优化）

数据迁移方法数据迁移原则数据迁移之双写方案数据迁移之级联同步方案

redis集群数据一致性_RedisRaft为Redis集群带来强大的数据一致性

宝塔面板mysql恢复2018.1.8更新

Centos7 MySQL 5.7 安装MySQL 5.7 安装

查找入职员工时间排名倒数第三的员工所有信息

Hibernate使用Hibernate的“3个准备，7个步骤”Hibernate API简介操作实体对象对象识别

云计算面试题——mysql/存储引擎/备份

SQL语言基础：常用的数据查询语句

Ubuntu16.04安装Apache+MySQL+PHP1. 安装Apache2. 安装MySQL3. 安装PHP4. 安装phpMyAdmin

MySQL的4种隔离级别？出现问题

neo4j之cypher使用文档

mysql使用source命令导入.sql文件

mysql5.7和8.0及mariadb时间条件查询结果差别

ä¸ãç¯å¢

äºãå ·ä½æµè¯è¿ç¨

2.1ãmysqlçæ¬ä¸º5.7.22å5.7.32

2.2ãmysql8.0.28 æµè¯

2.3ã10.2.10-MariaDB-å10.2.17-MariaDBæµè¯

继续阅读

ä¸ãç¯å¢

äºãå·ä½æµè¯è¿ç¨

2.1ãmysqlçæ¬ä¸º5.7.22å5.7.32

2.2ãmysql8.0.28 æµè¯

2.3ã10.2.10-MariaDB-å10.2.17-MariaDBæµè¯