SERIAL_EXTRACT 函数

函数说明

SERIAL_EXTRACT() 函数用于提取序列/元组值中的各个元素，需结合函数 MAX()，MIN()，SERIAL()，SERIAL_NULL() 使用。

函数语法

>SERIAL_EXTRACT(serial_col, pos as type)

参数释义

参数	说明
serial_col	必要参数。保存 serial/serial_full 函数值的串行列。如需更换输出类型可结合CAST()函数使用。
pos	必要参数。要提取的字段的位置，0 为第一个。
type	必要参数。导出的元素的原始类型。需要与提取的元素类型保持一致。

示例

drop table if exists vtab64;
create table vtab64(id int primary key auto_increment,`vecf64_3` vecf64(3),`vecf64_5` vecf64(5));
insert into vtab64(vecf64_3,vecf64_5) values("[1,NULL,2]",NULL);
insert into vtab64(vecf64_3,vecf64_5) values(NULL,NULL);
insert into vtab64(vecf64_3,vecf64_5) values("[2,3,4]",NULL);
insert into vtab64(vecf64_3,vecf64_5) values ("[4,5,6]","[1,2,3,4,5]");
insert into vtab64(vecf64_3,vecf64_5) values ("[7,8,9]","[2,3,4,5,6]");

mysql> select * from vtab64;
+------+-----------+-----------------+
| id   | vecf64_3  | vecf64_5        |
+------+-----------+-----------------+
|    1 | NULL      | NULL            |
|    2 | [2, 3, 4] | NULL            |
|    3 | [4, 5, 6] | [1, 2, 3, 4, 5] |
|    4 | [7, 8, 9] | [2, 3, 4, 5, 6] |
+------+-----------+-----------------+
4 rows in set (0.01 sec)

--max(max(serial(id, `vecf64_3`, `vecf64_5`)) 得到一个最大的序列化值，然后正常获取到的 max 应该是 (4,[7, 8, 9],[2, 3, 4, 5, 6]) 这一条记录，但是 1 代表的是第二个位置的值，所以就是 [7, 8, 9]
mysql> select serial_extract(max(serial(id, `vecf64_3`, `vecf64_5`)), 1 as vecf64(3)) as a from vtab64;
+-----------+
| a         |
+-----------+
| [7, 8, 9] |
+-----------+
1 row in set (0.01 sec)

mysql> select serial_extract(min(serial(id, `vecf64_3`, `vecf64_5`)), 2 as vecf64(5)) as a from vtab64;
+-----------------+
| a               |
+-----------------+
| [1, 2, 3, 4, 5] |
+-----------------+
1 row in set (0.00 sec)

mysql> select serial_extract(max(serial_full(cast(id as decimal), `vecf64_3`)), 0 as decimal) as a from vtab64;
+------+
| a    |
+------+
|    4 |
+------+
1 row in set (0.01 sec)

mysql> select serial_extract(min(serial_full(cast(id as decimal), `vecf64_3`)), 1 as vecf64(3)) as a from vtab64;
+------+
| a    |
+------+
| NULL |
+------+
1 row in set (0.00 sec)