mysql的union和or
实践出真知！
使用union连接
select `id` from `表名` where 0=0 and active=1 and `fullname` like '王%' union
select `id` from `表名` where 0=0 and active=1 and `username` like '王%' union
select `id` from `表名` where 0=0 and active=1 and `mobile` like '王%' union
select `id` from `表名` where 0=0 and active=1 and `citizen_id_number` like '王%'
select `id` from `表名` where 0=0 and active=1 and `username` like '王%' union
select `id` from `表名` where 0=0 and active=1 and `mobile` like '王%' union
select `id` from `表名` where 0=0 and active=1 and `citizen_id_number` like '王%'
使用or
select `id` from `表名` where 0=0 and active=1
and `fullname` like '王%'
or `username` like '王%'
or `mobile` like '王%'
or `citizen_id_number` like '王%'
and `fullname` like '王%'
or `username` like '王%'
or `mobile` like '王%'
or `citizen_id_number` like '王%'
上面两种查询都能够用上索引，且数据库表中有500万条数据的情况下：
如果查询结果数据少的情况下，两种查询时间上差不多，0.006和0.005的区别，
但是当查询结果数据量多时，union查询时间16秒，or查询时间0.05秒。
因为union需要扫描4遍表，再把结果结合在一起。
但是不知道下面这个度娘的说法是为什么？留待以后验证。！
2019/07/08 17:17 验证
