NULL值的问题

1、NOT IN子查询在有NULL值的情况下返回永远为空结果，查询容易出错

 
2、新业务、测试库可以开启explicit_defaults_for_timestamp=1
    老业务迁移到新数据库，如果不想背锅，建议默认参数explicit_defaults_for_timestamp=0
    NULL值在timestamp类型下容易出问题，特别是没有启用参数explicit_defaults_for_timestamp
 1 mysql> show create table t_timestamp;
 2 +-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
 3 | Table       | Create Table                                                                                                                                              |
 4 +-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
 5 | t_timestamp | CREATE TABLE `t_timestamp` (
 6   `createtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
 7 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
 8 +-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
 9 1 row in set (0.00 sec)
10 
11 mysql> select * from t_timestamp;
12 Empty set (0.00 sec)
13 
14 mysql> insert into t_timestamp values(null);
15 Query OK, 1 row affected (0.00 sec)
16 
17 mysql> select * from t_timestamp;
18 +---------------------+
19 | createtime          |
20 +---------------------+
21 | 2017-07-12 11:57:15 |
22 +---------------------+
23 1 row in set (0.00 sec)
24 
25 mysql> show variables like "sql_mode";
26 +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
27 | Variable_name | Value                                                                                                                                     |
28 +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
29 | 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 |
30 +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
31 1 row in set (0.00 sec)
32 
33 mysql> show variables like "explicit_defaults_for_timestamp";
34 +---------------------------------+-------+
35 | Variable_name                   | Value |
36 +---------------------------------+-------+
37 | explicit_defaults_for_timestamp | OFF   |
38 +---------------------------------+-------+
39 1 row in set (0.01 sec)
40 
41 mysql> set session explicit_defaults_for_timestamp=1;
42 Query OK, 0 rows affected (0.00 sec)
43 
44 mysql> insert into t_timestamp values(null);
45 ERROR 1048 (23000): Column 'createtime' cannot be null

 

3、NULL值到非NULL的更新无法做到原地更新，更容易发生索引分裂，从而影响性能

 
 
 
 
 
 
 
 

posted on 2017-07-20 15:34  星期六男爵  阅读(149)  评论(0)    收藏  举报

导航