mysql 引号变成字段问题

Question

突然出现一个引号变成字段问题

SQLSTATE[42S22]: Column not found: 1054 Unknown column '%Y-%m-%d' in 'field list' (SQL: select count(id) count,DATE_FORMAT(created_at,"%Y-%m-%d") time from `user` where `created_at` >= 2017-06-24 and `created_at` <= 2018-07-04 group by `time`)

把其中的

"%Y-%m-%d"

变成单引号

'%Y-%m-%d'

mysql 版本5.7.18 不知道这个是什么原因引起的?要如何解决呢?

Answer 1

没有发现你说的问题，日期是字符串引号括起来

SELECT
    count(id) total ,
    DATE_FORMAT(created_at , "%Y-%m-%d") time
FROM
    `user`
WHERE
    `created_at` >= "2017-06-24" # 注意这里的2017-06-24,引号括起来
AND `created_at` <= "2018-07-04"
GROUP BY
    `time`

注意这里的2017-06-24,引号括起来

注意这里的2018-07-04,引号括起来

另外就是，你用 DATE_FORMAT其实很消耗性能，还不如把你要查询的日期先转换成int类型再取查数据。

Answer 2

sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ANSI"

这个后面多了一个ANSI就会出现我说的那个问题,现在去掉就不会了

Answer 3

ANSI Equivalent to REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, and (as of MySQL 5.7.5) ONLY_FULL_GROUP_BY.

ANSI_QUOTES

Treat " as an identifier quote character (like the ` quote character) and not as a string quote character. You can still use ` to quote identifiers with this mode enabled. With ANSI_QUOTES enabled, you cannot use double quotation marks to quote literal strings because they are interpreted as identifiers.

created_at >= 2017-06-24，这么用没问题？等同数学公式created_at >= 1987