将mysql表字段改为可为空_表中已有数据，将表中某个字段为空的改为非空

1.需求：

表t中大概有5亿条数据，现需要将表中的is_validate字段改为非空。考虑到表中有大量数据，而且表还是实时插入，如果直接修改为not null,将会使用很长时间。

发现加not null 可以使旧的数据不生效，只对新数据生效。即enable novalidate属性.

2.实施过程:

1)查看表结构

SQL> desc t

名称                                      是否为空? 类型

----------------------------------------- -------- ---------------

BATCH_ID                                  NOT NULL NUMBER(11)

OUTPUT_ID                                          NUMBER(14)

OUTFALL_TYPE                                       NUMBER(4)

FLUX_VALUE                                         NUMBER(20,3)

MEASURE_TIME                                       DATE

IS_MEASURE                                         CHAR(1)

FLUX_SUM                                           NUMBER(20,3)

INSERT_TIME                                        DATE

IS_VALIDATE                                        CHAR(1)

2)修改字段is_validate为非空，只对新数据生效

SQL> alter table t modify is_validate not null enable novalidate;

表已更改。

已用时间:  00: 00: 00.85

3)更新表中为空的值

SQL> update t set is_validate=0 where is_validate is null;

已更新7766852行。

已用时间:  00: 30: 18.67

SQL> commit;

4)查看约束状态

SQL> select table_name,constraint_name,status,deferrable,deferred,validated from user_constraints where table_name='T';

TABLE_NAME                     CONSTRAINT_NAME                STATUS   DEFERRABLE     DEFERRED  VALIDATED

------------------------------ ------------------------------ -------- -------------- --------- -------------

T                  SYS_C005606                    ENABLED  NOT DEFERRABLE IMMEDIATE NOT VALIDATED

已用时间:  00: 00: 00.00

4)启用约束

SQL> alter table t modify constraint sys_c005606 enable validate;

表已更改。

已用时间:  00: 02: 39.905)使trace跟踪查看两者调用情况

alter session set events '10046 trace name context forever, level 12';

alter table t modify status not null enable novalidate;

alter session set events '10046 trace name context off';

alter session set events '10046 trace name context forever, level 12';

alter table t modify status not null；

alter session set events '10046 trace name context off';