A multi-value column contains multiple undetermined values. A multi-value column field can contain a variety of data types supported by AnalyticDB for MySQL. You can define one or more multi-value columns in a table. You can use multi-value columns for grouping, joining, and filtering.

Create a table

In this example, a table named test1 is created, and the tags field is defined as a multi-value column through MULTIVALUE. Fields in the columns are separated by commas (,). The multi-value column contains the value_type field of the VARCHAR type.

CREATE TABLE test1 (
    user_id BIGINT,
    city VARCHAR,
    tags MULTIVALUE delimiter_tokenizer ',' value_type 'varchar'
)
DISTRIBUTED BY HASH (user_id)

Write data into a table

Execute the following statements to write three data records into the test1 table:

insert into test1 values(1, 'HZ', 'A,B,C');
insert into test1 values(2, 'BJ', 'B,D');
insert into test1 values(3, 'SH', 'A,C,D,F');
select * from test1 order by user_id;
+---------+------+---------+
| user_id | city | tags    |
+---------+------+---------+
|       1 | HZ   | A,B,C   |
|       2 | BJ   | B,D     |
|       3 | SH   | A,C,D,F |

Query data

  • Query by filtering

    Execute the following statement to query the total number of users that contain either the A or B tag in the test1 table:

    select count(*) from test1 where ref(tags,0) in ('A', 'B');
    +----------+
    | COUNT(*) |
    +----------+
    |        3 |

    Execute the following statement to query the number of users that contain both the A and B tags in the test1 table:

    select * from test1 where ref(tags,0) in ('A') and ref(tags,0) in ('B');
    +---------+------+-------+
    | user_id | city | tags  |
    +---------+------+-------+
    |       1 | HZ   | A,B,C |
  • Query by grouping

    Execute the following statement to query the number of users that do not locate in the Hangzhou region and contain the A tag and the number of users that do not locate in the Hangzhou region and contain the B tag in the test1 table:

    select ref(tags,0), count(*) from test1
    where ref(tags,0) in ('A', 'B') and city ! = 'HZ' group by ref(tags,0);
    +--------------+----------+
    | ref(tags, 0) | COUNT(*) |
    +--------------+----------+
    | A            |        1 |
    | B            |        1 |

    Execute the following statement to query the number of users that contain the A or B tag in each corresponding city in the test1 table:

    select ref(tags,0), city, count(*) from test1
    where ref(tags,0) in ('A', 'B') group by ref(tags,0), city;
    +--------------+----------+
    | ref(tags, 0) | COUNT(*) |
    +--------------+----------+
    | B            | BJ       |
    | B            | HZ       |
    | A            | SH       |
    | A            | HZ       |
  • Query by joining
    select count(*) from test1 join test
    on test1.user_id = test.id where ref(tags,0) in ('A', 'B') ;
    +----------+
    | COUNT(*) |
    +----------+
    |        0 |

Precautions

  • Multi-value column conditions can only be used for queries in a single table. Multi-value column conditions cannot be used for queries in joined tables.
  • If the group specified by the Group By field contains a multi-value column, the WHERE condition must also contain the multi-value column. Otherwise, the engine layer of AnalyticDB for MySQL will exhaustively list all values of the multi-value column, which will affect query performance.
    Note By default, 1,024 values of a multi-value column are listed.