<select id="" parameterType = "">
	SELECT * FROM users 
	WHERE 1=1
		<if test="userName != null ">
			AND user_name = #{userName}
		</if>
		<if test="userAge != null ">
			AND user_age = #{userAge }
		</if>
		<if test="userSex!= null ">
			AND	user_sex = #{userSex}
		</if>
</select>

<select id="" parameterType = "">
	SELECT * FROM users 
	WHERE 1=1
		<if test="userName != null ">
			AND user_name = #{userName}
		</if>
		<if test="userAge != null ">
			AND user_age = #{userAge }
		</if>
		<if test="userSex!= null ">
			AND	user_sex = #{userSex}
		</if>
</select>

<select id="" parameterType = "">
	SELECT * FROM users 
	<where>
		<if test="userName != null ">
			AND user_name = #{userName}
		</if>
		<if test="userAge != null ">
			AND user_age = #{userAge }
		</if>
		<if test="userSex!= null ">
			AND	user_sex = #{userSex}
		</if>
	</where>
</select>

SELECT VERSION();

5.7.44

# 數(shù)據(jù)構(gòu)造 SQL
CREATE TABLE IF NOT EXISTS t_user
(
  id INT not null auto_increment primary key comment '自增ID',
  name varchar(20) comment '姓名',
  age tinyint  comment '年齡'
)ENGINE = INNODB;


INSERT INTO t_user ( NAME, age ) VALUES ( '張三', 18 ),( '李四', 19 ),( '王五', 20 ),( '司總', 21 );

# 注：需要選中一起執(zhí)行可以查看到優(yōu)化后的 SQL
explain select * from t_user where 1=1 AND name = '張三';
show warnings;

# 優(yōu)化后的 SQL
/* select#1 */ SELECT
`mydatabase`.`t_user`.`id` AS `id`,
`mydatabase`.`t_user`.`name` AS `name`,
`mydatabase`.`t_user`.`age` AS `age` 
FROM
	`mydatabase`.`t_user` 
WHERE
	(
	`mydatabase`.`t_user`.`name` = '張三')

# 性能對(duì)比
select * from t_user where 1=1 AND name = '張三'
> OK
> 查詢時(shí)間: 0.046s

select * from t_user where 1=1
> OK
> 查詢時(shí)間: 0.046s