[TOC] #### **array\_length** 返回数组中元素个数,结果类型是INT,如果参数是NULL,结果也是NULL > array\_length(any\_array) 示例: ```sql mysql> select array_length([1,2,3]); +-----------------------+ | array_length([1,2,3]) | +-----------------------+ | 3 | +-----------------------+ 1 row in set (0.00 sec) mysql> select array_length([[1,2], [3,4]]); +-----------------------------+ | array_length([[1,2],[3,4]]) | +-----------------------------+ | 2 | +-----------------------------+ 1 row in set (0.01 sec) ``` <br> #### **array\_append** 在数组末尾添加一个新的元素 > array\_append(any\_array, any\_element) 示例: ~~~ mysql> select array_append([1, 2], 3); +------------------------+ | array_append([1,2], 3) | +------------------------+ | [1,2,3] | +------------------------+ 1 row in set (0.00 sec) ~~~ 可以向数组中添加NULL ~~~ mysql> select array_append([1, 2], NULL); +---------------------------+ | array_append([1,2], NULL) | +---------------------------+ | [1,2,NULL] | +---------------------------+ 1 row in set (0.01 sec) ~~~ <br> #### **array\_contains** 检查数组中是否包含某个元素,是的话返回1,否则返回0 > array\_contains(any\_array, any\_element) 示例: ~~~ mysql> select array_contains(["apple","orange","pear"], "orange"); +-----------------------------------------------------+ | array_contains(['apple','orange','pear'], 'orange') | +-----------------------------------------------------+ | 1 | +-----------------------------------------------------+ 1 row in set (0.01 sec) ~~~ 也可以检查数组中是否包含NULL ~~~ mysql> select array_contains([1, NULL], NULL); +--------------------------------+ | array_contains([1,NULL], NULL) | +--------------------------------+ | 1 | +--------------------------------+ 1 row in set (0.00 sec) ~~~ 可以检查多维数组中是否包含某个子数组,此时要求子数组元素完全匹配,包括元素排列顺序 ~~~ mysql> select array_contains([[1,2,3], [4,5,6]], [4,5,6]); +--------------------------------------------+ | array_contains([[1,2,3],[4,5,6]], [4,5,6]) | +--------------------------------------------+ | 1 | +--------------------------------------------+ 1 row in set (0.00 sec) mysql> select array_contains([[1,2,3], [4,5,6]], [4,6,5]); +--------------------------------------------+ | array_contains([[1,2,3],[4,5,6]], [4,6,5]) | +--------------------------------------------+ | 0 | +--------------------------------------------+ 1 row in set (0.00 sec) ~~~ #### **array\_sum** > array_sum(array(type)) **对一个ARRAY中的所有数据做和,返回这个结果。** **示例** mysql> select array\_sum(\[11, 11, 12\]); +-----------------------+ | array\_sum(\[11,11,12\]) | +-----------------------+ | 34 | +-----------------------+ mysql> select array\_sum(\[11.33, 11.11, 12.324\]); +---------------------------------+ | array\_sum(\[11.33,11.11,12.324\]) | +---------------------------------+ | 34.764 | +---------------------------------+ #### **array\_avg** > array_avg(array(type)) **求取一个ARRAY中的所有数据的平均数,返回这个结果。** **示例** mysql> select array\_avg(\[11, 11, 12\]); +-----------------------+ | array\_avg(\[11,11,12\]) | +-----------------------+ | 11.333333333333334 | +-----------------------+ mysql> select array\_avg(\[11.33, 11.11, 12.324\]); +---------------------------------+ | array\_avg(\[11.33,11.11,12.324\]) | +---------------------------------+ | 11.588 | +---------------------------------+ #### **array\_min** > array_min(array(type)) **求取一个ARRAY中的所有数据中的最小值,返回这个结果。** **示例** mysql> select array\_min(\[113, 11, 12\]); +------------------------+ | array\_min(\[113,11,12\]) | +------------------------+ | 11 | +------------------------+ mysql> select array\_min(\[11.33, 11.11, 12.324\]); +---------------------------------+ | array\_min(\[11.33,11.11,12.324\]) | +---------------------------------+ | 11.11 | +---------------------------------+ mysql> select array\_min(\[cast('2020-02-25 11:35:17' as datetime), cast('2019-08-25 17:07:10' as datetime), cast('2025-08-25 17:07:10' as datetime)\]); +--------------------------------------------------------------------------------------------------------------------------------------+ | array\_min(\[CAST('2020-02-25 11:35:17' AS DATETIME),CAST('2019-08-25 17:07:10' AS DATETIME),CAST('2025-08-25 17:07:10' AS DATETIME)\]) | +--------------------------------------------------------------------------------------------------------------------------------------+ | 2019-08-25 17:07:10 | +--------------------------------------------------------------------------------------------------------------------------------------+ #### **array\_max** **求取一个ARRAY中的所有数据中的最大值,返回这个结果。** > array_max(array(type)) **示例** mysql> select array\_max(\[113, 11, 12\]); +------------------------+ | array\_max(\[113,11,12\]) | +------------------------+ | 113 | +------------------------+ mysql> select array\_max(\[11.33, 11.11, 12.324\]); +---------------------------------+ | array\_max(\[11.33,11.11,12.324\]) | +---------------------------------+ | 12.324 | +---------------------------------+ mysql> select array\_max(\[cast('2020-02-25 11:35:17' as datetime), cast('2019-08-25 17:07:10' as datetime), cast('2025-08-25 17:07:10' as datetime)\]); +--------------------------------------------------------------------------------------------------------------------------------------+ | array\_max(\[CAST('2020-02-25 11:35:17' AS DATETIME),CAST('2019-08-25 17:07:10' AS DATETIME),CAST('2025-08-25 17:07:10' AS DATETIME)\]) | +--------------------------------------------------------------------------------------------------------------------------------------+ | 2025-08-25 17:07:10 | +--------------------------------------------------------------------------------------------------------------------------------------+