如何在MySQL视图中设置默认值且保留索引功能？

问题出在IFNULL(customerdate, ...)对索引字段做了函数包装——MySQL没法直接利用customerdate的索引定位经过函数计算后的值，导致索引失效。以下是几种不用修改主表就能保留索引能力的方案：

方案1：视图保留原字段，新增处理后的别名字段 #

修改视图定义，同时输出原始customerdate字段和处理默认值后的字段：

create view myview as
select 
    id,
    customername,
    customerdate,
    IFNULL(customerdate, CAST('1900-01-01' AS DATE)) as customerdate_with_default
from mytable;

遗留系统直接使用customerdate_with_default获取带默认值的结果；当需要做过滤查询时，直接针对原始的customerdate字段写条件（比如WHERE customerdate > '2020-01-01'），MySQL就能正常利用mykey索引。

方案2：重写查询条件，绕过函数包装 #

如果遗留系统必须用原字段名（即视图里只能输出customerdate），那在查询视图时，把针对该字段的过滤条件拆成两种情况：
比如原本的查询是：

select * from myview where customerdate > '2000-01-01';

改成：

select * from myview 
where (customerdate > '2000-01-01') OR (customerdate IS NULL AND '1900-01-01' > '2000-01-01');

这样MySQL会先利用customerdate的索引处理非空值的过滤，空值部分直接判断默认值是否符合条件，既满足业务逻辑，又能用到索引。

方案3：用同步表模拟物化视图 #

如果上面的方案都无法满足需求，可以创建一个和mytable结构一致的新表，定时同步mytable的数据（比如用INSERT ... ON DUPLICATE KEY UPDATE），同步时直接把NULL的customerdate替换成1900-01-01，然后给新表的customerdate字段加索引，让遗留系统直接访问这个同步表。这种方式需要维护数据同步的定时任务，但能完全解决索引问题。

内容的提问来源于stack exchange，提问作者membersound