[TDR Generic Table] [MySQL Protocol Compatibility Interface] Get Records

1. Interface Description

Get existing records from a specified table.

2. Preparations

Refer to Preparation document, create mysql_table table, and use MySQL Client to connect to TcaplusDB successfully.

3. Syntax Rules of WHERE Clause of Get Statement

When the global index is not configured, the WHERE clause of get statement consists of two parts: (1) required part: index or primarykey; (2) optional part: filter conditions.

The fields in indexor primarykey can only be queried equivalently, and the fields that make up index or primarykey can only be connected by the operator AND;

Filter conditions support NOT, =, >, <, !=, >= and <=. Multiple filter conditions can be connected with AND or OR, and support to filter key fields, value fields, version fields, and TTL fields.

When using primarykey to get data from mysql_table, the WHERE clause form is as follows:

WHERE k1=1 AND k2=2 AND k3='one';

When using primarykey + filter condition to get data from mysql_table, the WHERE clause form is as follows: (if the filter condition contains OR operator, the filter condition must be bracketed):

WHERE k1=1 AND k2=2 AND k3='one' AND <extra-condition>;

When using index to get data from mysql_table, the WHERE clause form is as follows:

WHERE k1=1 AND k2=2;

When using index + filter condition to get data from mysql_table, the WHERE clause form is as follows: (if the filter condition contains OR operator, the filter condition must be bracketed):

WHERE k1=1 AND k2=2 AND <extra-condition>;

Note: When using indexto get data, multiple records may be queried at one time.

4. SQL Examples

When using primarykey to get a record from mysql_table, the SQL statement has the following two forms:

SELECT * FROM mysql_table WHERE k1=1 AND k2=2 AND k3='one';
SELECT * FROM mysql_table WHERE k1=1 AND k2=2 AND k3='one' AND (v1>=4 OR v2<=4);

The content in the brackets of the 2nd SQL statement above is the filter condition.

When using index to get multiple records from mysql_table, the SQL statement has the following two forms:

SELECT * FROM mysql_table WHERE k1=1 AND k2=2;
SELECT * FROM mysql_table WHERE k1=1 AND k2=2 AND (k3='one' OR v2<=4);

The content in the brackets of the 2nd SQL statement above is the filter condition.

When using primarykey to get multiple records from mysql_table in batches, the SQL statement is as follows:

SELECT * FROM mysql_table WHERE k1=1 AND k2=2 AND k3='one' OR k1=1 AND k2=2 AND k3='two' AND v2<=4;

v2<=4 in the above SQL statement is the filter condition.

When using index to get multiple records from mysql_table in batches, the SQL statement is as follows:

SELECT * FROM mysql_table WHERE k1=1 AND k2=2 OR k1=1 AND k2=3 AND (k3='one' OR v2<=4);

The content in the brackets of the SQL statement above is the filter condition.

5. FAQ

For details, see Meaning and Handling of Error Codes.

6. Other Reference Documents

[TDR Generic Table] [C++ SDK] Interface Description for Getting a Record

[TDR Generic Table] [Java SDK] Interface Description for Getting a Record

[TDR Generic Table] [Go SDK] Interface Description for Getting a Record

[TDR Generic Table][C++ SDK] Interface Description for Querying Local Index

[TDR Generic Table] [Java SDK] Interface Description for Querying Local Index.

[TDR Generic Table] [Go SDK] Interface Description for Querying Local Index.

[TDR Generic Table][C++ SDK] Interface Description for Batch-Getting Multiple Records

[TDR Generic Table] [Java SDK] Interface Description for Batch-Getting Multiple Records

[TDR Generic Table] [Go SDK] Interface Description for Batch-Getting Multiple Records

[Generic Table] Get Records