---
title: "数组判断函数 - OceanBase 数据库 V4.6.0 | OceanBase 文档中心"
description: 数组判断函数 数组判断函数对输入的数组数据进行一些判断，并返回布尔值。OceanBase 数据库当前所支持的数组判断函数包括 array_contains() 、 array_contains_all() 和 array_overlaps() ，支持的运算符包括 ANY() 。 array_contains arra…
---
切换语言

- 中文站 - 简体中文
- International - English
- 日本站 - 日本語

文档反馈![](https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*P8CuR4UJ_FkAAAAAAAAAAAAADiGDAQ/original) OceanBase 数据库分布式版 - V 4.6.0

# 数组判断函数

更新时间：2026-07-15 15:16:39

[编辑](https://github.com/oceanbase/oceanbase-doc/edit/V4.6.0/zh-CN/700.reference/500.sql-reference/100.sql-syntax/200.common-tenant-of-mysql-mode/400.functions-of-mysql-mode/950.array-functions-of-mysql-mode/300.array-decision-functions-of-mysql-mode.md)  

数组判断函数对输入的数组数据进行一些判断，并返回布尔值。OceanBase 数据库当前所支持的数组判断函数包括 `array_contains()`、`array_contains_all()` 和 `array_overlaps()`，支持的运算符包括 `ANY()`。

## array_contains

`array_contains()` 函数判断数组中是否含有某个元素。语法如下：

```sql
array_contains(arr1, element)

```

输入参数说明如下：

- `arr1` 传入的类型必须是一个数组类型。
 - `element` 用于判断的元素可以是任意类型。

返回结果 `1` 表示包含，`0` 表示不包含。

示例如下：

```sql
SELECT array_contains([1,2,3], 2);
+----------------------------+
| array_contains([1,2,3], 2) |
+----------------------------+
|                          1 |
+----------------------------+
1 row in set

```

```sql
SELECT array_contains([1,2,3], 6);
+----------------------------+
| array_contains([1,2,3], 6) |
+----------------------------+
|                          0 |
+----------------------------+
1 row in set

```

```sql
SELECT array_contains(["hello", "hi"], "hel");
+----------------------------------------+
| array_contains(["hello", "hi"], "hel") |
+----------------------------------------+
|                                      0 |
+----------------------------------------+
1 row in set

```

```sql
SELECT array_contains(["hello", "hi"], "hi");
+---------------------------------------+
| array_contains(["hello", "hi"], "hi") |
+---------------------------------------+
|                                     1 |
+---------------------------------------+
1 row in set

```

```sql
SELECT array_contains([[1,2],[3,4]], [3.0,4.0]);
+------------------------------------------+
| array_contains([[1,2],[3,4]], [3.0,4.0]) |
+------------------------------------------+
|                                        1 |
+------------------------------------------+
1 row in set

```

## array_contains_all

`array_contains_all()` 函数判断输入的数组是否包含另一个数组中的所有元素。语法如下：

```sql
array_contains_all(arr1, arr2)

```

输入参数说明如下：

- `arr1` 传入的类型必须是一个数组类型。
 - `arr2` 传入的类型必须是一个数组类型。

输入参数 `arr1` 和 `arr2` 必须有相同的（或者可转换的）元素类型，否则返回失败。

示例如下：

```sql
SELECT array_contains_all([1,2,3], [2]);
+----------------------------------+
| array_contains_all([1,2,3], [2]) |
+----------------------------------+
|                                1 |
+----------------------------------+
1 row in set

```

```sql
SELECT array_contains_all([2], [1,2,3]);
+----------------------------------+
| array_contains_all([2], [1,2,3]) |
+----------------------------------+
|                                0 |
+----------------------------------+
1 row in set

```

```sql
SELECT array_contains_all([1,2,3], null);
+-----------------------------------+
| array_contains_all([1,2,3], null) |
+-----------------------------------+
|                              NULL |
+-----------------------------------+
1 row in set

```

```sql
SELECT array_contains_all([1,2,3], [2.0]);
+------------------------------------+
| array_contains_all([1,2,3], [2.0]) |
+------------------------------------+
|                                  1 |
+------------------------------------+
1 row in set

```

```sql
SELECT array_contains_all(["hello", "hi"], ["hi"]);
+---------------------------------------------+
| array_contains_all(["hello", "hi"], ["hi"]) |
+---------------------------------------------+
|                                           1 |
+---------------------------------------------+
1 row in set

```

```sql
SELECT array_contains_all([[1,2],[3,4]], [[3.0,4.0]]);
+------------------------------------------------+
| array_contains_all([[1,2],[3,4]], [[3.0,4.0]]) |
+------------------------------------------------+
|                                              1 |
+------------------------------------------------+
1 row in set

```

```sql
SELECT array_contains_all([["hello", "world"], ["hi", "what"], ["are you?"]], [["are you?"]]);
+----------------------------------------------------------------------------------------+
| array_contains_all([["hello", "world"], ["hi", "what"], ["are you?"]], [["are you?"]]) |
+----------------------------------------------------------------------------------------+
|                                                                                      1 |
+----------------------------------------------------------------------------------------+
1 row in set

```

## array_overlaps

`array_overlaps()` 函数判断两个数组是否存在交集。语法如下：

```sql
array_overlaps(arr1, arr2)

```

输入参数说明如下：

返回结果 `1` 表示存在交集，`0` 表示不存在交集。

示例如下：

```sql
SELECT array_overlaps([1,2,3], [2]);
+------------------------------+
| array_overlaps([1,2,3], [2]) |
+------------------------------+
|                            1 |
+------------------------------+
1 row in set

```

```sql
SELECT array_overlaps([1,2,3], null);
+-------------------------------+
| array_overlaps([1,2,3], null) |
+-------------------------------+
|                          NULL |
+-------------------------------+
1 row in set

```

```sql
SELECT array_overlaps([1,2,3], [2.0]);
+--------------------------------+
| array_overlaps([1,2,3], [2.0]) |
+--------------------------------+
|                              1 |
+--------------------------------+
1 row in set

```

```sql
SELECT array_overlaps([1.1,2.2,3.3], [2.2]);
+--------------------------------------+
| array_overlaps([1.1,2.2,3.3], [2.2]) |
+--------------------------------------+
|                                    1 |
+--------------------------------------+
1 row in set

```

```sql
SELECT array_overlaps(["hello", "hi"], ["hi"]);
+-----------------------------------------+
| array_overlaps(["hello", "hi"], ["hi"]) |
+-----------------------------------------+
|                                       1 |
+-----------------------------------------+
1 row in set

```

```sql
SELECT array_overlaps([[1,2],[3,4]], [[3,4]]);
+----------------------------------------+
| array_overlaps([[1,2],[3,4]], [[3,4]]) |
+----------------------------------------+
|                                      1 |
+----------------------------------------+
1 row in set

```

```sql
SELECT array_overlaps([[1,2],[3,4]], [[3.0,4.0]]);
+--------------------------------------------+
| array_overlaps([[1,2],[3,4]], [[3.0,4.0]]) |
+--------------------------------------------+
|                                          1 |
+--------------------------------------------+
1 row in set

```

```sql
SELECT array_overlaps([["hello", "world"], ["hi", "what"], ["are you?"]], [["are you?"]]);
+------------------------------------------------------------------------------------+
| array_overlaps([["hello", "world"], ["hi", "what"], ["are you?"]], [["are you?"]]) |
+------------------------------------------------------------------------------------+
|                                                                                  1 |
+------------------------------------------------------------------------------------+
1 row in set

```

## ANY

`ANY()` 运算符和 `array_contains()` 函数功能、输入参数、返回值均相同，语法有差异：

```sql
element = ANY(arr1)

```

示例如下：

```sql
SELECT 2 = ANY([1,2,3]);
SELECT 6 = ANY([1,2,3]);
SELECT "hel" = ANY(["hello", "hi"]);
SELECT "hi" = ANY(["hello", "hi"]);
SELECT [3.0,4.0] = ANY([[1,2],[3,4]]);

```

 上一篇 下一篇 ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线