MySQL STR_TO_DATE转字符串结果为Null

最近在进行企业实训，目前在完成一个p2p的项目。今天遇到一个问题，记录在此。我的目的是为了提取出数据库中date类型字段的记录的年份字符串形式，即若记录为2020-04-02，则要提取出2020的字符串形式。

若要直接看解决方法，则推荐方法4，直接看即可，不用修改sql_mode

问题描述：

问题是在使用sql语句中的STR_TO_DATE命令时出现的。iting数据库中有一个名为apply的table，其中有一个字段applyDate，其属性为date。数据库中该字段的记录为2020-04-02的格式，但现在我只需要提取它的年份，即2020的部分。企业老师在上课的时候向我们介绍了STR_TO_DATE命令，即用其可以将日期转变为字符串格式，如命令STR_TO_DATE(applyDate,'%Y')，该命令理论上可以将2020-04-02转变成2020-00-00的格式，但实际却显示了Null，如下图：

但是，老师在上课时同样的命令却可以显示出我所期望的结果值，即2020-00-00，之后再使用left(STR_TO_DATE(applyDate,'%Y'),4)即可得到字符串2020

问题排查：

经过一番排查(将近1小时)，最终发现是sql_mode的问题，我先查询一下当前的sql_mode(命令为select @@sql_mode)，显示为：

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

果然，问题的确出在这里，其中的NO_ZERO_IN_DATE和NO_ZERO_DATE正是问题所在

其中，NO_ZERO_DATE模式会影响服务器是否允许'0000-00-00'作为有效日期

而，NO_ZERO_IN_DATE模式会影响服务器是否允许年份部分为非零但月份或日期部分为0的日期

问题解决：

方法1(未尝试):将mysql版本降低，我的当前版本为mysql-5.7.28-winx64，理论上降低版本即可解决该问题，但没有亲自尝试。

方法2(未成功):在my.ini文件中更改sql_mode，即设置

sql_mode=STRICT_TRANS_TABLES

此方法可以永久的更改sql_mode，以达到一劳永逸，但是此方法我却并没有成功。我的mysql根目录下并没有my.ini文件，故我按其他CSDN文章中的自己创建了一个，但再次启动mysql时却再也启不动了。。。只能强制kill该服务。有说需要将my.ini的编码改成ANSI，我使用了notepad++进行了编码更改尝试，然并卵。。。

方法3(成功):下面介绍一下我使用的解决方法。即直接在sql语句中修改sql_mode，但此法在mysql重启之后便失效，即sql_mode回复原样，需要再次修改

首先，我们需要使用root账号登录mysql，否则并无sql_mode的修改权限。

其次，我们应该修改全局的，即global，像如下的查询和修改命令只是局部修改，并没什么用

-- 查询局部命令
select @@sql_mode;

-- 修改局部命令
set @@sql_mode='STRICT_TRANS_TABLES';

我们应该使用的是如下的global命令

-- 设置global域
set @@global.sql_mode='STRICT_TRANS_TABLES';
-- 或者下面的命令也可以
set global sql_mode='STRICT_TRANS_TABLES';

-- 查询命令
select @@global.sql_mode;

此时，我们再使用STR_TO_DATE(applyDate,'%Y')得到的便是：2020-00-00的形式，成功

select
	STR_TO_DATE(applyDate,'%Y')
from
	apply

但要想得到字符串2020，则还需使用left命令

select
	left(STR_TO_DATE(applyDate,'%Y'),4)
from
	apply

此时，得到的便是字符串2020，目标达成

方法4(推荐):可以在不改动sql_mode的情况下解决该问题，即不需要将NO_ZERO_IN_DATE和NO_ZERO_DATE从中移除。

此时我们需要将STR_TO_DATE的fmt(即第二个参数)完善到月和日，然后再提取字符串的前四个字符2020

SELECT
	left(STR_TO_DATE(applyDate,'%Y-%m-%d'),4)
from
	apply

总结：

问题的关键在于sql_mode的配置，我们需要将NO_ZERO_IN_DATE和NO_ZERO_DATE从中移除。如果使用cmd修改的话，则最好以管理员身份运行。

但若不移除且不降mysql版本，我们仍要使用STR_TO_DATE命令的话，则月和日不能为0，即使用

STR_TO_DATE(applyDate,'%Y-%m-%d')

否则将得到Null值

此文章为本人一个字一个字码出来的，若要转载请注明出处，谢谢。