mysql中的sql_mode导致的datetime类型字段不能为0000

问题描述:

在执行建表语句的时候,出现invalid default datetime value '0000-00-00 00:00:00',从字面意思看,就是不合法的默认值’0000-00-00 00:00:00’,但是为什么呢?,datetime类型应该是允许这样的值出现。

排查:
这个时候我们需要执行

1
select @@sql_mode;

你会发现值是这样的:

1
2

@@sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

各项数值含义:

ONLY_FULL_GROUP_BY

对于GROUP BY聚合操作,如果在SELECT中的列,没有在GROUP BY中出现,那么将认为这个SQL是不合法的,因为列不在GROUP BY从句中

STRICT_TRANS_TABLES

在该模式下,如果一个值不能插入到一个事务表中,则中断当前的操作,对非事务表不做任何限制

NO_ZERO_IN_DATE

在严格模式,不接受月或日部分为0的日期。如果使用IGNORE选项,我们为类似的日期插入’0000-00-00’。在非严格模式,可以接受该日期,但会生成警告。

NO_ZERO_DATE

在严格模式,不要将 ‘0000-00-00’做为合法日期。你仍然可以用IGNORE选项插入零日期。在非严格模式,可以接受该日期,但会生成警告

ERROR_FOR_DIVISION_BY_ZERO

在严格模式,在INSERT或UPDATE过程中,如果被零除(或MOD(X,0)),则产生错误(否则为警告)。如果未给出该模式,被零除时MySQL返回NULL。如果用到INSERT IGNORE或UPDATE IGNORE中,MySQL生成被零除警告,但操作结果为NULL。

NO_AUTO_CREATE_USER

防止GRANT自动创建新用户,除非还指定了密码。

NO_ENGINE_SUBSTITUTION

如果需要的存储引擎被禁用或未编译,那么抛出错误。不设置此值时,用默认的存储引擎替代,并抛出一个异常。

解决方案:

经过排查,问题已经很明显了,所以我们怎么做呢?

答案就是去掉NO_ZERO_IN_DATE&&NO_ZERO_DATE

1
2
3
4
#1.先执行这句更改SQL模式
set @@sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
#2.在执行要执行的建表语句
create table .......

以上。

关于Mysql:unknow column in where clause

写在前边:

已经很久不更新了啊,整个2月份几乎没有遇到什么新鲜事。直到昨天我又犯了一次傻,貌似只有我犯傻的时候才有材料可以跟大家分享…..

问题表现:

mysql 报错: unknow column ‘sys’ in where clause

事实上这是个挺常见的错误,我猜你大概会说,这个问题不是已经很明了了么? 你查询了一个不存在的字段! 可是事情的真相真的是这样么?并不是,报这个错误的原因还有一种可能就是你的where条件中字符串的拼接出了问题。

现场还原:

  • 还是先说一下我犯的傻吧

我首先开始排查的问题的时候输出了我的查询SQL,形似

1
2

$sql = 'select * from user where id in ('.implode(',',$idArr).')';

$idArr一个元素很多ID数组,里边的内容都是整型的数字,所以我也没想很多,然后抛出了这个mysql错误,我开始检查我的sql语句 。我用记事本打开了我刚刚打印的sql,开始搜索sys这个关键词,但是我没注意到我鼠标的位置,记事本默认向下搜索,提示没搜索到。我后来就随机把这个SQL中的id字符串删掉了一段,删的只剩几个,执行,诶!好了!我开始疑惑,难道整型的id查询在数据长的时候会出问题么?加上在CSDN看到某博客上说是字符串拼接的问题,所以我决定给他做一下处理,顺带加上了这么一段注释。

1
2
3
4
5
//做一下处理换成字符串否则在$IdString过长的时候会引发unknow column 'sys' in where clause 错误

foreach ($idArr as &$v) {
$v = '\''.$v.'\'';
}

加上之后,问题果不其然的解决了,好的,没有问题,完美!然后开开心心的上个线。

引发问题的真实原因

到了晚上快下班的时候,我在开发另一个需求的时候,突然在列表里看到了一个名为sys的用户!真的是瞬间像是被一道闪电击中了!卧槽! (这绝对值两个卧槽,才能表达我当时的内心!),是的就是你想的那样!那个用户id数组里有一个用户的idsys,而这东西是个字符串,但是却没被引号包裹。所以才引发了mysqlunknow column 'sys' in where clause错误。这才是错误的真相!

好了,不说了,趁着还没有同事发现,我要把这个包含了我愚蠢猜测的注释给删掉。

溜了溜了。

两个比较好用的mysql函数

我们经常会面临要从数据库里**判断时间,取出特定日期的查询。但是数据库里储存的都是unix时间戳,处理起来并不是特别友好。幸而MYSQL提供了几个处理时间戳的函数,可以帮助我们在查询的时候,就将时间戳格式化。用法举例如下:

1.FROM_UNIXTIME()函数

FROM_UNIXTIME(unix_timestamp,format)

  • 参数unix_timestamp 时间戳 可以用数据库里的存储时间数据的字段

  • 参数format  要转化的格式 比如“”%Y-%m-%d“” 这样格式化之后的时间就是 2017-11-30

可以有的形式:

  • %M 月名字(January~December)
  • %W 星期名字(Sunday~Saturday)
  • %D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。)
  • %Y 年, 数字, 4 位
  • %y 年, 数字, 2 位
  • %a 缩写的星期名字(Sun~Sat)
  • %d 月份中的天数, 数字(00~31)
  • %e 月份中的天数, 数字(0~31)
  • %m 月, 数字(01~12)
  • %c 月, 数字(1~12)
  • %b 缩写的月份名字(Jan~Dec)
  • %j 一年中的天数(001~366)
  • %H 小时(00~23)
  • %k 小时(0~23)
  • %h 小时(01~12)
  • %I 小时(01~12)
  • %l 小时(1~12)
  • %i 分钟, 数字(00~59)
  • %r 时间,12 小时(hh:mm:ss [AP]M)
  • %T 时间,24 小时(hh:mm:ss)
  • %S 秒(00~59)
  • %s 秒(00~59)
  • %p AM或PM
  • %w 一个星期中的天数(0=Sunday ~6=Saturday )
  • %U 星期(0~52), 这里星期天是星期的第一天
  • %u 星期(0~52), 这里星期一是星期的第一天
  • %% 一个文字%

使用举例:

1
2
3
4
5
6
7
8
9
SELECT
username,
FROM_UNIXTIME(create_time, "%Y-%m-%d") AS dat
FROM
`wp_user`

GROUP BY 

dat

这样就能查出每天有哪些用户注册了。按天分组,你可以将数据导出后进行其他操作。

2.UNIX_TIMESTAMP()

UNIX_TIMESTAMP(date)

  • 其中date可以是一个DATE字符串,一个DATETIME字符串,一个TIMESTAMP或者一个当地时间的YYMMDD或YYYMMDD格式的数字

  • 用这个函数可以帮助我们在时间戳中筛选出某些天的数据。

比如说:

1
2
3
4
5
6
7
8
9
10
11
SELECT
username,
FROM_UNIXTIME(create_time, "%Y-%m-%d") AS dat
FROM
`wp_user`
WHERE
create_time >=UNIX_TIMESTAMP(''2017-11-29')
AND
create_time <UNIX_TIMESTAMP(''2017-11-30')
GROUP BY 
dat
  • 这个查询可以让我们查出29号那一天的用户注册记录。

善用这两个MYSQL函数可以帮助我们提高处理数据的效率。

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×