mysql的in关键字应用——传入集合参数快速查询

最新推荐文章于 2024-05-16 17:07:00 发布

Duktig丶

最新推荐文章于 2024-05-16 17:07:00 发布

阅读量8.4k

点赞数 7

分类专栏： mysql 文章标签： mysql in

本文链接：https://blog.csdn.net/qq_42937522/article/details/115790566

版权

mysql 专栏收录该内容

10 篇文章 1 订阅

订阅专栏

mysql IN关键字的基础用法

mysql的in关键字应该是经常使用的一个查询条件了，其作用是查询某个范围内的数据。

SELECT * FROM WHERE field IN (value1,value2,value3,…)

当 IN 前面加上 NOT 运算符时，表示与 IN 相反的意思，即不在这些列表项内选择：

SELECT * FROM WHERE field NOT IN (value1,value2,value3,…)

更多情况下，IN 列表项的值是不明确的，而可能是通过一个子查询得到的：

SELECT * FROM article WHERE uid IN (SELECT uid FROM user WHERE status=0)

然后将查询结果作为 IN 的列表项以实现最终的查询结果，注意在子查询中返回的结果必须是一个字段列表项。

问题

正常使用mysql的in关键字，我相信没有什么问题。但是在实际的场景当中，我们的需求，往往不是这样的，很多情况下，往往是传入一个集合的参数，那么我们如何能够可以快速使用in关键字进行查询呢？

例如：现在有一个用户id集合，我们想要查询在这个id集合中的用户信息，该如何快速实现？

解决方案

我们默认使用现在比较流行的持久化框架Mybatis

方案一：使用mybatis的foreach

使用注解的实现方式：

@Select("<script> " +
        "	SELECT * FROM user WHERE id IN " +
        "		<foreach collection='userId' item='item' index='index' open='(' separator=',' close=')'> " +
        "			#{item}" +
        "		</foreach>" +
        "</script>"
)
List<User> queryUsersBatch(Set<Long> userId);

方案二：将集合数据转为字符串处理

一般情况下如果是用户id集合，直接输出的结果应该是：[1, 2, 3] 。我们将“ [ ”和“ ] ”去电，岂不是就可以转为1, 2, 3 ，就可以将其直接放进IN的括号里了。

集合去掉中括号的方法

可以直接使用Hutool的工具包中StrUtil的方法，去掉前后缀。

/**
 * 去除两边的指定字符串
 *
 * @param str    被处理的字符串
 * @param prefix 前缀
 * @param suffix 后缀
 * @return 处理后的字符串
 * @since 3.1.2
 */
public static String strip(CharSequence str, CharSequence prefix, CharSequence suffix) {
	if (isEmpty(str)) {
		return str(str);
	}

	int from = 0;
	int to = str.length();

	String str2 = str.toString();
	if (startWith(str2, prefix)) {
		from = prefix.length();
	}
	if (endWith(str2, suffix)) {
		to -= suffix.length();
	}

	return str2.substring(Math.min(from, to), Math.max(from, to));
}

使用

更改mybatis中mapper的方法，将集合参数换为String字符串参数：

@Select("SELECT * FROM user WHERE id IN #{userId}")
List<User> queryUsersBatch(String userId);

使用：

List<Long> userIdList = new ArrayList<>();
userIdList.add(25L);
userIdList.add(50L);
userIdList.add(2L);
String userIdsStr = StringUtils.strip(userIdList.toString(), "[", "]");

注意事项

如果在使用mybatis的时候，传入参数使用#的话，会在IN的括号里默认加上''，会导致查询数据为空，结果如下：

IN ( '25, 50, 2' )

出现这样的原因是，IN后边的括号里边的数据默认是一个整个的字符串而且这个方法处理，字符串中有空格。解决方法是使用$来替换#，默认不添加''即可。

修改mapper方法为：

@Select("SELECT * FROM user WHERE id IN ${userId}")
List<User> queryUsersBatch(String userId);

修改后IN部分的sql：

IN ( 25, 50, 2 )

这样即可完成查询。

两种方案对比

两种方案都可以快速在java中传入集合参数，然后进行处理，使用mysql的IN关键字快速查询。实现方式虽然不同，但是最后的sql都是一样的，根据个人喜好自行选择即可。

但是方案二处理速度应该会比方案一快吧，毕竟不需要遍历即可实现。

补充

关于 IN 关键字补充

IN 列表项不仅支持数字，也支持字符甚至时间日期类型等，并且可以将这些不同类型的数据项混合排列而无须跟 column 的类型保持一致：

SELECT * FROM user WHERE id IN(1,2,'3','c')

一个 IN 只能对一个字段进行范围比对，如果要指定更多字段，可以使用 AND 或 OR 逻辑运算符：

SELECT * FROM user WHERE id IN(1,2) OR username IN('admin','manong')

关于 IN 运算符的效率问题

如果 IN 的列表项是确定的，那么可以用多个 OR 来代替：

SELECT * FROM user WHERE id IN (2,3,5)

等效为：

SELECT * FROM user WHERE (id=2 OR id=3 OR id=5)

一般认为：

如果是对索引字段进行操作，使用 OR 效率高于 IN，但对于列表项不确定的时候（如需要子查询得到结果），就必须使用 IN 运算符。另外，对于子查询表数据小于主查询的时候，也是适用 IN 运算符的。
in或or在字段没有添加索引的情况下,所连接的字段越多(1 or 2 or 3 or 4 or…)，or比in的查询效率低很多。