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>