Tablestore provides a MySQL-compatible SQL interface for wide tables and time series data. You can run DDL, DQL, and DML statements and use search indexes for full-text search, vector search, array queries, nested queries, and JSON queries.
How it works
Tablestore runs a SQL engine on top of its NoSQL storage. Create a mapping table with CREATE TABLE for an existing data table, then access data with SQL. The SQL engine automatically selects the optimal index to accelerate queries.
Unlike traditional relational databases, CREATE TABLE in Tablestore does not create a physical table. Instead, it creates a SQL access mapping for an existing data table or search index. DROP MAPPING TABLE removes only the mapping — the underlying data is not affected.
SQL engine concepts map to Tablestore concepts as follows.
Database concept | Tablestore concept | Description |
Database | Instance | A repository that organizes, stores, and manages data. An instance can contain one or more tables. |
Table | Mapping table | A SQL access mapping for an existing data table. Created with CREATE TABLE. Consists of rows and columns. |
Index | Secondary index, search index | A storage structure created to accelerate queries. The SQL engine automatically selects the optimal index. |
SQL query is available in the following regions: China (Hangzhou), China (Shanghai), China (Beijing), China (Zhangjiakou), China (Ulanqab), China (Shenzhen), China (Chengdu), China (Hong Kong), Japan (Tokyo), Singapore, Malaysia (Kuala Lumpur), Indonesia (Jakarta), Germany (Frankfurt), UK (London), US (Silicon Valley), US (Virginia), SAU (Riyadh).
Access methods
Run SQL queries through the following methods.
Method | Use case | Reference |
Console | Small-scale data queries and validation without code. | |
CLI | Development, debugging, and automation scripts. | |
JDBC | Java applications that access Tablestore through the standard JDBC interface. | |
Hibernate | Java ORM integration for existing Hibernate projects. | |
MyBatis | Java persistence framework integration for existing MyBatis projects. | |
Go driver | Go applications that access Tablestore through the standard database/sql interface. | |
SDK | Programmatic SQL execution within applications. | |
Supported SQL statements
The SQL engine supports the following statements.
Category | Statement | Description |
CREATE TABLE | Creates a SQL mapping for a data table or search index. | |
ALTER TABLE | Adds or removes attribute columns. | |
DROP MAPPING TABLE | Removes the mapping. The underlying data is not affected. | |
SHOW TABLES | Lists all mapping tables in the current instance. | |
SHOW INDEX | Displays index information. | |
DESCRIBE | Displays the table schema. | |
DROP TABLE | Deletes a data table. Not supported. | |
SELECT | Queries data with filtering, aggregate functions (MIN, MAX, SUM, AVG, COUNT), GROUP BY, ORDER BY, and LIMIT. | |
SELECT JOIN | Joins data across tables. Supports INNER, LEFT, RIGHT, and CROSS JOIN. | |
INSERT / UPDATE / DELETE | Writes, updates, and deletes data. | |
Full-text search / Array / Nested / Vector / JSON | Uses search indexes for full-text search, array queries, nested queries, vector search, and JSON queries. | |
Other | CREATE INDEX | Creates an index. Not supported. |
Other features:
The character set is UTF-8, with binary collation.
Arithmetic, comparison, logical, and bitwise operators are supported (SQL operators).
SQL data types map to Tablestore data types (Data type mapping).
Index selection optimization and computation pushdown are supported (Query optimization).
For a complete list of reserved words and keywords, see SQL reserved words and keywords.
SQL operators
Tablestore SQL supports four categories of operators.
Arithmetic operators
Use arithmetic operators in SELECT or WHERE clauses for numeric calculations.
Operator | Name | Description |
A+B | Addition | Returns the result of A+B. |
A-B | Subtraction | Returns the result of A-B. |
A*B | Multiplication | Returns the result of A*B. |
A/B or A DIV B | Division | Returns the result of A/B. |
A%B or A MOD B | Modulo | Returns the remainder of A/B. |
Comparison operators
Comparison operators evaluate whether a condition is met and return 1 for TRUE or 0 for FALSE. Use them in WHERE clauses.
Operator | Name | Description |
A:=B | Assignment | Assigns the value of B to A. |
A=B | Equal to | Returns 1 if A equals B, otherwise 0. |
A!=B or A<>B | Not equal to | Returns 1 if A does not equal B, otherwise 0. |
A>B | Greater than | Returns 1 if A is greater than B, otherwise 0. |
A<B | Less than | Returns 1 if A is less than B, otherwise 0. |
A>=B | Greater than or equal to | Returns 1 if A is greater than or equal to B, otherwise 0. |
A<=B | Less than or equal to | Returns 1 if A is less than or equal to B, otherwise 0. |
IN (A,B...) | In set | Returns 1 if the column value matches any value in the set, otherwise 0. |
BETWEEN A AND B | In range | Returns 1 if the value is >= A and <= B, otherwise 0. |
NOT BETWEEN A AND B | Not in range | Returns 1 if the value is > B or < A, otherwise 0. |
A LIKE B | Pattern match | Matches strings. |
A NOT LIKE B | Not pattern match | Returns non-matching strings. |
Logical operators
Logical operators evaluate whether expressions are true or false and return 1 for TRUE or 0 for FALSE. Use them in WHERE clauses to build compound conditions.
Operator | Name | Description |
A AND B or A&&B | Logical AND | Returns 1 if both A and B are TRUE, otherwise 0. |
A OR B | Logical OR | Returns 1 if at least one of A or B is TRUE, otherwise 0. |
A XOR B | Logical XOR | Returns 1 if A and B differ, otherwise 0. |
NOT A or !A | Logical NOT | Returns 1 if A is FALSE, otherwise 0. |
Bitwise operators
Bitwise operators perform calculations on binary numbers. Operands are converted to binary for the operation, and the result is converted back to decimal.
Operator | Name | Description |
A&B | Bitwise AND | Returns the bitwise AND of A and B. |
A|B | Bitwise OR | Returns the bitwise OR of A and B. |
A^B | Bitwise XOR | Returns the bitwise XOR of A and B. |
~A | Bitwise NOT | Returns the bitwise complement of A. |
Data type mapping
SQL columns correspond to table columns of the same name. The data types must match.
For primary key columns that use VARBINARY or VARCHAR, set the maximum length to 1024.
BIGINT in SQL and Integer in Tablestore are both 64-bit integer types.
Only BIGINT, VARBINARY, and VARCHAR are supported as primary key column data types.
SQL data type | Data table type | Search index type |
BIGINT | Integer | Integer |
VARBINARY (primary key) / MEDIUMBLOB (attribute column) | Binary | Binary |
VARCHAR (primary key) / MEDIUMTEXT (attribute column) | String | Keyword |
Text | ||
DOUBLE | Double | Double |
BOOL | Boolean | Boolean |
Limits
SQL query applies to Wide Column model and TimeSeries model.
Transactions are not supported.
SQL keywords are reserved words and are case-insensitive. To use a keyword as a table or column name, escape it with
``. For the full list, see SQL reserved words and keywords.The SQL engine is case-insensitive for column names.
SELECT Aa FROM tis equivalent toSELECT aa FROM t. Because the underlying Tablestore table is case-sensitive, the SQL engine converts all column names to lowercase for matching. The original table must not contain columns that differ only in case.
For other limits, see Limits on SQL queries.
Billing
SQL queries do not incur additional fees. Table scans, index queries, and data read/write operations during query execution are billed according to standard pricing. For details, see Billable items of SQL query.