Lindorm:Wildcard columns

Background information

Traditional SQL statements can barely meet the business requirements in scenarios where large amounts of data needs to be processed. For example, a database may contain hundreds or thousands of non-fixed columns in Internet of Vehicles (IoV) scenarios. To write a new column to the database, you must execute the ALTER TABLE statement that is inefficient.

Lindorm supports dynamic columns that allow you to dynamically write and query data. However, dynamic columns support only VARBINARY data at present. To help you write more types of data to dynamic columns, Lindorm provides wildcard columns.

The name of a wildcard column supports asterisks (*) and question marks (?) as wildcards. Data in columns whose names match the wildcards are converted to the specified type. The two wildcards indicate different characters.

• A question mark (?) matches a single character.

• An asterisk (*) matches a character sequence that can be empty.

For example, if you add a wildcard column named c* int to the schema of a table, you can write any column whose name starts with the character c, such as c10, c11, or c20. The type of data in these columns is converted to INT.

Prerequisites

The version of LindormTable is 2.5.3 or later. For more information about how to view or upgrade the version of LindormTable, see Release notes of LindormTable and Upgrade the minor engine version of a Lindorm instance.

Limits

• Wildcard columns cannot be specified as primary keys.

• To use the SELECT * statement to query data in a table that contains wildcard columns, you must add the LIMIT clause to the statement. Example: SELECT * FROM t_dynamic_columns LIMIT 10;

• Only search indexes can be created for wildcard columns. Secondary indexes cannot be created for wildcard columns. For example, the following statement is not supported: CREATE INDEX idx on tb(c2*);

• Column names that contain wildcards cannot be used as conditions to query data. Only actual column names can be used as conditions in the SELECT statement and WHERE clause. For example, the following statements are not supported: SELECT c1*, c2 WHERE c2 > 10; and SELECT c1*, c2 WHERE c1* > 10; The following statement is supported: SELECT pk, c1, c2, c21, c31 WHERE c21 > 10 AND c31 < 'c300';

DDL

Create a table with wildcard columns

You can execute the following statement to create a table named tb, specify the pk column as the primary key, and specify the following two wildcard columns in the WITH clause: c2* and c3*. The data type of the c2* column is BIGINT and the data type of the c3* columns is VARCHAR.

CREATE TABLE tb(pk integer, c1 varchar, `c2*` bigint, `c3*` varchar, primary key(pk)) WITH(wildcard_column='c2*,c3*');

Based on the preceding statement, if the name of a column starts with c2 (such as c2, c21, or c22) and the data type of the column is BIGINT, the column can be written to the tb table. If the name of a column starts with c2 and the data type of the column is not BIGINT, the column can be written to the tb table and the data type of the column is converted to BIGINT. Similarly, if the name of a column starts with c3 (such as c32, c33, or c35) and the data type of the column is VARCHAR, the column can be written to the tb table. If the name of a column starts with c3 and the data type of the column is not VARCHAR, the column can be written to the tb table and the data type of the column is converted to VARCHAR.

Create a search index on wildcard columns

Search indexes can be created for wildcard columns. You can execute the following statement to create a search index on the c2* and c3* wildcard columns in the tb table. All columns whose name match the wildcards are indexed by the search index.

 CREATE SEARCH INDEX IF NOT EXISTS sidx ON tb(`c2*`, `c3*`);

DML

Write data into columns

When you write data to columns that match the wildcard columns, the columns that you specify in the statement must meet the following requirements:

• The column names must meet the matching rules specified by the wildcard columns.

• The data types of the columns must meet the data types specified by the wildcard columns.

UPSERT INTO tb(pk, c1, c2, c21, c22, c31) values (1, 'a1', 2, 21, 22, 'c3');