where后面加"1=1"还是不加
- where关键词什么也不加
select * from table_name;
- where关键词后面加 "1=1"
select * from table_name where 1 = 1;
不用where 1=1 在多条件查询的困扰
select * from table_name where <if test="null != id"> id = #{req.id} </if> <if test=' null != req.name and req.name !="" ' > and name = #{req.rname} </if>
如果上述的两个if判断语句均为true时,那么最终的动态SQL语句为:
select * from table_name where id = ** and name = *** ;
select * from table_name where;
select * from table_name where and name=***
使用where 1=1 的好处
如果我们在where条件后加上1=1:
select * from table_name where 1=1 <if test="null != id"> and id = #{req.id} </if> <if test=' null != req.name and req.name !="" ' > and name = #{req.name} </if>
select * from table_name where 1=1 and id = ** and name = *** ;
select * from table_name where 1=1;
select * from table_name where 1=1 and name=***
使用where 1=1 的坏处
select * from table_name where 1=1;
delete from table_name where id = 1
delete from table_name where id = 1 or 1=1
在一个and链接的操作里,select会不会影响效率
EXPLAIN EXTENDED SELECT id FROM room_info WHERE 1=1 and id <10; SHOW WARNINGS;
select `test`.`abc`.`id` AS `id` from `test`.`abc` where (`test`.`abc`.`id` < 10)
其他作用
create table table_abc as select * from abc_table where 1<>1
create table table_abc as select * from abc_table where 1=1
建议
为了防止sql注入等问题,建议尽量少使用where 1=1
在mybatis也提供了<where>来解决多条件查询拼接的问题,例如:
select * from table_name <where> <if test = " id != null "> and id = #{id} </if> <if test = " name != null and name != '' "> and name = #{name} </if> </where>
where 元素只会在至少有一个子元素的条件返回 SQL 子句的情况下才去插入WHERE子句。而且,若语句的开头为AND或OR,where 元素也会将它们去除。