Syntax

    1. mysql> set enable_vectorized_engine=true;
    3. mysql> select k1,k2,k3,array_except(k2,k3) from array_type_table;
    4. +------+-----------------+--------------+--------------------------+
    5. | k1   | k2              | k3           | array_except(`k2`, `k3`) |
    6. +------+-----------------+--------------+--------------------------+
    7. |    1 | [1, 2, 3]       | [2, 4, 5]    | [1, 3]                   |
    8. |    2 | [2, 3]          | [1, 5]       | [2, 3]                   |
    9. +------+-----------------+--------------+--------------------------+
    11. mysql> select k1,k2,k3,array_except(k2,k3) from array_type_table_nullable;
    13. | k1   | k2              | k3           | array_except(`k2`, `k3`) |
    14. +------+-----------------+--------------+--------------------------+
    15. |    1 | [1, NULL, 3]    | [1, 3, 5]    | [NULL]                   |
    16. |    2 | [NULL, NULL, 2] | [2, NULL, 4] | []                       |
    17. |    3 | NULL            | [1, 2, 3]    | NULL                     |
    18. +------+-----------------+--------------+--------------------------+
    20. mysql> select k1,k2,k3,array_except(k2,k3) from array_type_table_varchar;
    21. +------+----------------------------+----------------------------------+--------------------------+
    22. +------+----------------------------+----------------------------------+--------------------------+
    23. |    1 | ['hello', 'world', 'c++']  | ['I', 'am', 'c++']               | ['hello', 'world']       |
    25. |    3 | ['hasnull', NULL, 'value'] | ['nohasnull', 'nonull', 'value'] | ['hasnull', NULL]        |
    26. |    3 | ['hasnull', NULL, 'value'] | ['hasnull', NULL, 'value']       | []                       |
    27. +------+----------------------------+----------------------------------+--------------------------+
    29. mysql> select k1,k2,k3,array_except(k2,k3) from array_type_table_decimal;
    30. +------+------------------+-------------------+--------------------------+
    31. | k1   | k2               | k3                | array_except(`k2`, `k3`) |
    32. +------+------------------+-------------------+--------------------------+
    33. |    1 | [1.1, 2.1, 3.44] | [2.1, 3.4, 5.4]   | [1.1, 3.44]              |
    34. |    2 | [NULL, 2, 5]     | [NULL, NULL, 5.4] | [2, 5]                   |
    35. +------+------------------+-------------------+--------------------------+