MySQL 请问这两条语句谁更快一点？

Question

select * from qs_exam_info where TRANS_DATE LIKE '2018-08-18%'select * from qs_exam_info where date(TRANS_DATE) = '20180818'

数据库里面TRANS_DATE是按2018-10-16 00:14:17这样的格式存储的

Answer 1

如果 TRANS_DATE 为 varchar/char 类型，且该字段有索引，那么大部分情况下第一句比第二句更快如果 TRANS_DATE 为 timestamp/datetime 类型，两句都用不到索引，全表扫描，所以效率差不多

Answer 2

mysql> select count(*) from table where date like '2018-08-18%';+----------+| count(*) |+----------+|     2128 |+----------+1 row in set (0.98 sec)mysql> select count(*) from table where date(date) = '2018-08-18';+----------+| count(*) |+----------+|     2128 |+----------+1 row in set (1.21 sec)mysql> select count(*) from table where date(date) = '2018-08-18';+----------+| count(*) |+----------+|     2128 |+----------+1 row in set (1.21 sec)mysql> select count(*) from table where date like '2018-08-18%';+----------+| count(*) |+----------+|     2128 |+----------+1 row in set (0.98 sec)mysql> select count(*) from table;+----------+| count(*) |+----------+|  2066946 |+----------+1 row in set (0.66 sec)mysql>