Purpose
This statement is used to query data from one or more tables.
This section describes the general syntax of the SELECT statement. For other SELECT statements, see:
Privilege requirements
To execute the SELECT statement, the current user must have the SELECT privilege. For more information about OceanBase AI Database privileges, see Permission classification.
Syntax
select_stmt:
SELECT [hint_options]
[{DISTINCT | UNIQUE} | ALL]
[SQL_CALC_FOUND_ROWS]
[STRAIGHT_JOIN]
select_expr_list
[FROM from_list]
[WHERE where_condition]
[GROUP BY group_by_condition_list [WITH ROLLUP]
| GROUP BY [group_by_condition_list] group_by_summary_option (expression_list)]
[HAVING having_condition]
[window_clause]
[ORDER BY order_by_condition_list]
[LIMIT limit_clause]
[lock_option];
select_expr_list:
select_expr [, select_expr ...]
select_expr:
*
| table_name.{* | column_name}
| table_alias_name.{* | column_name}
| expr [[AS] column_alias_name]
from_list:
DUAL
| table_references
| { location_url | table_function }
location_url:
'file_path'
(
{FORMAT = (
TYPE = 'CSV',
LINE_DELIMITER = '<string>' | <expr>,
FIELD_DELIMITER = '<string>' | <expr>,
ESCAPE = '<character>' | <expr>,
FIELD_OPTIONALLY_ENCLOSED_BY = '<character>' | <expr>,
ENCODING = 'charset',
NULL_IF = ('<string>' | <expr>, '<string>' | <expr> ...),
SKIP_HEADER = <int>,
SKIP_BLANK_LINES = { TRUE | FALSE },
PARSE_HEADER = { TRUE | FALSE },
TRIM_SPACE = { TRUE | FALSE },
EMPTY_FIELD_AS_NULL = { TRUE | FALSE },
BYTEORDERMARK = {check | nocheck}
)
| FORMAT = ( TYPE = 'PARQUET' | 'ORC' )
}
[pattern_expr]
)
table_function:
{
FILES (
LOCATION = {'file_path' | @location_name['/path']},
{
FORMAT = (
TYPE = 'CSV',
LINE_DELIMITER = '<string>' | <expr>,
FIELD_DELIMITER = '<string>' | <expr>,
ESCAPE = '<character>' | <expr>,
FIELD_OPTIONALLY_ENCLOSED_BY = '<character>' | <expr>,
ENCODING = 'charset',
NULL_IF = ('<string>' | <expr>, '<string>' | <expr> ...),
SKIP_HEADER = <int>,
SKIP_BLANK_LINES = { TRUE | FALSE },
PARSE_HEADER = { TRUE | FALSE },
TRIM_SPACE = { TRUE | FALSE },
EMPTY_FIELD_AS_NULL = { TRUE | FALSE },
BYTEORDERMARK = {check | nocheck}
)
| FORMAT = ( TYPE = 'PARQUET' | 'ORC' )
},
[pattern_expr]
)
| SOURCE (
TYPE = 'ODPS',
ACCESSID = '<string>',
ACCESSKEY = '<string>',
ENDPOINT = '<string>',
TUNNEL_ENDPOINT = '<string>',
PROJECT_NAME = '<string>',
SCHEMA_NAME = '<string>',
TABLE_NAME = '<string>',
QUOTA_NAME = '<string>',
COMPRESSION_CODE = '<string>'
)
}
where_condition:
expression
group_by_condition_list:
group_by_condition [, group_by_condition ...]
group_by_condition:
expression [ASC | DESC]
group_by_summary_option:
GROUPING SETS
| ROLLUP
| CUBE
expression_list:
expression [, expression ...]
having_condition:
expression
order_by_condition_list:
order_by_condition [, order_by_condition ...]
order_by_condition:
expression [ASC | DESC]
limit_clause:
[offset,] row_count
| row_count OFFSET offset
lock_option:
FOR UPDATE [opt_for_update_wait]
| LOCK IN SHARE MODE
opt_for_update_wait:
WAIT {decimal | intnum}
| NOWAIT
| NO_WAIT
| SKIP LOCKED
pattern_expr:
PATTERN [=] 'regex_pattern'
| REGEXP_PATTERN [=] 'regex_pattern'
| GLOB_PATTERN [=] 'glob_pattern'
Parameters
Parameter |
Description |
|---|---|
| select_stmt | The part of the SQL statement that specifies which data to retrieve from the database. |
| hint_options | Optional. Specifies the hint Options. For more information about hints, see Optimizer hint. |
| {DISTINCT \ | UNIQUE} \ | ALL | Optional. Specifies whether duplicate rows are included in the returned result set.
|
| SQL_CACHE \ | SQL_NO_CACHE | Optional. Controls whether the query results are cached.
|
| SQL_CALC_FOUND_ROWS | Optional. Used to record the total number of rows returned by a query with a LIMIT clause. The total number of rows can then be retrieved by the FOUND_ROWS() function in subsequent executions. |
| STRAIGHT_JOIN | Optional. Forces the optimizer to join tables in the order specified in the FROM clause. This tuning command does not affect the execution result.
NoticeOceanBase AI Database is not fully compatible with the |
| select_expr_list | Specify the list of columns or expressions to display in the query results. For more information about columns and expressions, see select_expr. |
| column_name | Column name. |
| FROM from_list | Optional. Specifies the data source for the query, which can be a table, view, or subquery. For more information about data sources, see from_list. |
| { table_function \ | location_url } | Optional. The current URL external table supports two syntax forms. For details, see the following:
|
| WHERE where_condition | Optional. Specifies the filter conditions for the query. For more information, see where_condition. |
| GROUP BY group_by_condition_list | Optional. Specifies the columns for grouping the results. Aggregation functions are often used with this clause. For more information, see group_by_condition. |
| WITH ROLLUP | Optional. Summarize each group to represent a higher-level aggregation (also known as super-aggregation), generating additional rows. |
| GROUP BY [group_by_condition_list] group_by_summary_option (expression_list) | Optional. Specifies the group by clause for advanced grouping and summarization operations.
|
| HAVING having_condition | Optional. Specifies the condition for filtering the grouped results. The HAVING clause is similar to the WHERE clause, but the HAVING clause can use aggregate functions (such as SUM, AVG, etc.). For more information, see having_condition. |
| window_clause | Optional. Specifies the window definition for the analytic function (also called a window function in some databases). For more information about the syntax of window_clause, see WINDOW clause. |
| ORDER BY order_by_condition_list | Optional. Specifies the columns to use for sorting the result set. For more information, see order_by_condition. |
| LIMIT limit_clause | Optional. Specifies the number of rows to return. For more information, see limit_clause. |
| lock_option | Optional. Specifies the lock options for the query result. For more information, see lock_option. |
select_expr
select_expr: Indicates the columns or expressions included in the query result. Multiple expressions or column names are separated by commas (,). Valid values:
*: Selects all columns.Example:
Create tables
tbl1andtbl2.CREATE TABLE tbl1(col1 INT PRIMARY KEY, col2 VARCHAR(20), col3 INT) PARTITION BY HASH(col1) PARTITIONS 5;CREATE TABLE tbl2(col1 INT PRIMARY KEY, col2 VARCHAR(20), col3 INT);Insert test data into table
tbl1.INSERT INTO tbl1 VALUES(1, 'A1', 1),(2, 'A2', 2),(3, 'A3', 3);INSERT INTO tbl2 VALUES(1, 'A1', 1),(2, 'A2', 22),(3, 'A3', 33);View the data in table
tbl1.SELECT * FROM tbl1;The return result is as follows:
+------+------+------+ | col1 | col2 | col3 | +------+------+------+ | 1 | A1 | 1 | | 2 | A2 | 2 | | 3 | A3 | 3 | +------+------+------+ 3 rows in set
table_name.{* | column_name}: Selects all columns or specified columns from the specified table.table_name.*: Selects all columns from the specified table.table_name.column_name: specifies a column in the specified table to select.Example:
Read the
col1data from tabletbl1.SELECT tbl1.col1 FROM tbl1;The return result is as follows:
+------+ | col1 | +------+ | 1 | | 2 | | 3 | +------+ 3 rows in set
table_alias_name.{* | column_name}: Similar to the previous format, but uses the table alias when selecting columns.expr [[AS] column_alias_name]: Selects an expression and allows you to give the selected expression an alias as the column name.expr: a column name, function, or calculation expression.expr AS column_alias_name/expr column_alias_name: selects the expression and names itcolumn_alias_name.
Example:
Read the data in column
col1from tabletbl1and calculate a new columncol1_add, whose value iscol1 + 1. Additionally, calculatecol3 * 2to obtain a new columncol3_mul.SELECT col1, col1+1 AS col1_add, col3*2 col3_mul FROM tbl1;The return result is as follows:
+------+----------+----------+ | col1 | col1_add | col3_mul | +------+----------+----------+ | 1 | 2 | 2 | | 2 | 3 | 4 | | 3 | 4 | 6 | +------+----------+----------+ 3 rows in set
from_list
DUAL: Indicates a virtual table name. It is typically used to perform calculations or use functions when no actual table is available.Example:
SELECT 1+1, SYSDATE() FROM DUAL;The return result is as follows:
+------+---------------------+ | 1+1 | SYSDATE() | +------+---------------------+ | 2 | 2024-08-28 15:20:59 | +------+---------------------+ 1 row in settable_references: A list of table references, which can include multiple table references separated by commas (,). This specifies the list of data source tables for the query, which can be a combination of one or more tables. For detailed syntax information abouttable_references, see JOIN clause.
where_condition
expression: A conditional expression that filters the rows to be updated.
Example:
Select all row data from the tbl1 table that satisfy the conditions col1 > 1 and col2 = 'A3'.
SELECT * FROM tbl1
WHERE col1 > 1
AND col2 = 'A3';
The return result is as follows:
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
| 3 | A3 | 3 |
+------+------+------+
1 row in set
table_function
The LOCATION clause specifies the path where the external table files are stored. Typically, the data files for an external table are placed in a single directory, which can contain subdirectories. When creating a table, the external table automatically collects all files in this directory. Valid values:
file_path: the path of the external table file. The details are as follows:The local
LOCATIONformat isLOCATION = '[file://] local_file_path', wherelocal_file_pathcan be a relative or absolute path. If a relative path is provided, the current directory must be the installation directory of OceanBase AI Database.secure_file_privspecifies the file paths that OBServer nodes are authorized to access.local_file_pathmust be a subpath of thesecure_file_privpath.The format of the remote
LOCATIONis as follows:LOCATION = 's3://$ACCESS_ID:$ACCESS_KEY@$HOST:s3_region/remote_file_path', where$ACCESS_ID,$ACCESS_KEY, and$HOSTare the access information required to access AWS S3 and object storage compatible with the S3 protocol.s3_regionis the region selected when using S3. This sensitive access information is stored in an encrypted format in the database's system tables.LOCATION = hdfs://localhost:port/PATH, wherelocalhostis the HDFS address,portis the HDFS port number, andPATHis the directory path in HDFS.With Kerberos authentication:
LOCATION = 'hdfs://localhost:port/user?principal=xxx&keytab=xxx&krb5conf=xxx&configs=xxx'. Where:principal: the user for login authentication.keytab: the path of the keytab file for user authentication.krb5conf: the path to the Kerberos environment description file for the specified user.configs: Specifies additional HDFS configuration parameters. This parameter is empty by default. However, in a Kerberos environment, it usually has values that need to be configured. For example:dfs.data.transfer.protection=authentication,privacy, which specifies the data transfer protection level asauthenticationandprivacy.
@location_name['/path']: Indicates to reference a Location object to create an external table.['/path']is optional and specifies a subdirectory. For more information about creating a Location object, see CREATE LOCATION.
The FORMAT clause is used to specify the format-related attributes for reading files. It supports three file formats: CSV, PARQUET, and ORC.
- When TYPE = 'CSV', the following fields are included:
LINE_DELIMITER: the line delimiter for the CSV file. The default value isLINE_DELIMITER='\n'.FIELD_DELIMITER: Optional. Specifies the column delimiter for the CSV file. Default value:FIELD_DELIMITER='\t'.ESCAPE: specifies the escape character for the CSV file. It can be only one byte. The default isESCAPE ='\'.FIELD_OPTIONALLY_ENCLOSED_BY: Optional. Specifies the character that encloses field values in a CSV file. The default is an empty string. When this option is used, enclosing characters are added only for certain types of fields (such as CHAR, VARCHAR, TEXT, and JSON).ENCODING: Specifies the character set encoding format of the file. If not specified, the default value isUTF8MB4.NULL_IF: The string that will be treated asNULL. The default value is empty.SKIP_HEADER: skips the file header and specifies the number of lines to skip.SKIP_BLANK_LINES: Specifies whether to skip blank lines. The default value isFALSE, which means blank lines are not skipped.TRIM_SPACE: Specifies whether to delete leading and trailing spaces from fields in the file. The default value isFALSE, which means no spaces will be deleted.EMPTY_FIELD_AS_NULL: Specifies whether to treat empty strings asNULL. The default value isFALSE, which means empty strings are not treated asNULL.PARSE_HEADER: If this parameter is specified, the first row of the CSV file is directly retrieved and used as column names for each column.Notice
PARSE_HEADERandSKIP_HEADERcannot be used together because they conflict in meaning.BYTEORDERMARK: Optional. Specifies whether to detect and skip the BOM character. If this attribute is not specified, BOM character detection is not performed. Valid values:check: detects and skips BOM characters. This is recommended for files in UTF-8 with BOM format.nocheck: specifies not to perform BOM character detection (applicable to UTF-8 files without BOM characters).
Note
BYTEORDERMARKis supported starting from V4.6.2.1.
- When TYPE = 'PARQUET/ORC', no additional fields are required.
- When TYPE = 'CSV', the following fields are included:
pattern_expr: Specifies the file matching pattern to filter files in theLOCATIONdirectory. For each file path in theLOCATIONdirectory, if it matches this pattern string, the external table accesses the file; otherwise, it skips the file. If this parameter is not specified, all files in theLOCATIONdirectory are accessible by default.PATTERN [=] 'regex_pattern': Specifies the file name or regular expression. ThePATTERNandREGEXP_PATTERNparameters are equivalent. Controls which files are loaded. Example:PATTERN = 'extdata.csv': specifies the file name to load, which isextdata.csv.- For multiple CSV files, such as
extdata_2024.csvandextdata_2025.csv, you can writePATTERN = 'extdata_\d{4}\.csv'.
REGEXP_PATTERN [=] 'regex_pattern': Specifies to use a regular expression to match file names. This allows for flexible matching of various file names. Example:REGEXP_PATTERN = '.*[.]csv$': Uses a regular expression to filter all files underLOCATIONthat end with.csv.
GLOB_PATTERN [=] 'glob_pattern': Specifies to use wildcards to match file names. This performs a simple match for common file types. Example:GLOB_PATTERN = '*.csv': Matches all files that end with.csv.
For the ODPS format, since data is not retrieved through files and there is no meaningful URL path, only the source form of table_function is supported.
- TYPE = 'ODPS' contains the following fields:
ACCESSID: The ID of the ODPS user.ACCESSKEY: the password of the ODPS user.ENDPOINT: the connection address of the ODPS service.TUNNEL_ENDPOINT: the endpoint of the tunnel data transmission service.PROJECT_NAME: the project containing the table to be queried.SCHEMA_NAME: Optional. Specifies the schema of the table to query.TABLE_NAME: the name of the table to be queried.QUOTA_NAME: Optional. Specifies whether to use the specified quota.COMPRESSION_CODE: Optional. Specifies the compression format for the data source. Supported formats are ZLIB, ZSTD, LZ4, and ODPS_LZ4. If not specified, compression is disabled.
Example:
Reads data in ODPS format.
SELECT * FROM
source (
type = 'ODPS',
accessid = '$ODPS_ACCESSID',
accesskey = '$ODPS_ACCESSKEY',
endpoint= '$ODPS_ENDPOINT',
project_name = 'example_project',
schema_name = '',
table_name = 'example_table',
quota_name = '',
compression_code = ''
);
location_url
- The FORMAT clause is used to specify the format attributes for reading data. It supports three file formats: CSV, PARQUET, and ORC.
- CSV format: Configure parse_header=true/false to specify whether to parse the first row of the file as column headers (default is false). Additionally, you need to use TYPE=CSV to declare the file format (required during export).
- PARQUET/ORC format: The file structure is automatically detected, eliminating the need for an additional header row.
Example:
SELECT * FROM
FILES( location = '/data/',
format (TYPE = 'csv', field_delimiter = ',', parse_header = true),
pattern = 'datafiles$';
group_by_condition
expression [ASC | DESC]: the conditional expression for grouping. Adding ascending order (ASC) or descending order (DESC) specifies whether the order within each group in the resulting dataset after grouping is ascending or descending.
Example:
Query columns col1 and col2 from table tbl1, group by col2, sum col3 for each group, and output the results.
SELECT col1, col2, SUM(col3)
FROM tbl1
GROUP BY col2 DESC;
The return result is as follows:
+------+------+-----------+
| col1 | col2 | SUM(col3) |
+------+------+-----------+
| 3 | A3 | 3 |
| 2 | A2 | 2 |
| 1 | A1 | 1 |
+------+------+-----------+
3 rows in set
group_by_summary_option
GROUPING SETS: Indicates a custom grouping method, allowing you to specify multiple group combinations. It has the following characteristics:- Users can flexibly define the desired group combinations.
- The generated result set includes only the group combinations specified by the user.
Note
GROUP BY GROUPING SETS ((col1), (col2), (col3))is equivalent toGROUP BY GROUPING SETS (col1, col2, col3).Example:
SELECT col1, col2, col3, COUNT(*) FROM tbl1 GROUP BY GROUPING SETS ((col1), (col2), (col3));The return result is as follows:
+------+------+------+----------+ | col1 | col2 | col3 | COUNT(*) | +------+------+------+----------+ | 1 | NULL | NULL | 1 | | 2 | NULL | NULL | 1 | | 3 | NULL | NULL | 1 | | NULL | A1 | NULL | 1 | | NULL | A2 | NULL | 1 | | NULL | A3 | NULL | 1 | | NULL | NULL | 1 | 1 | | NULL | NULL | 2 | 1 | | NULL | NULL | 3 | 1 | +------+------+------+----------+ 9 rows in setROLLUP: Indicates a hierarchical aggregation method. It reduces the number of grouping columns layer by layer in the order of the grouping columns to generate multi-level summary results. It has the following characteristics:The grouping columns are reduced in order until only the total row remains, based on the column order in
expression_list.The generated result set includes:
- The detailed data of each group.
- Sub-aggregated data for each group.
- Total rows.
ROLLUPcan be considered a special case ofGROUPING SETS. For example:GROUP BY ROLLUP(col1, col2, col3) is equivalent to GROUP BY GROUPING SETS ((col1, col2, col3), (col1, col2), (col1), ())ROLLUPcan also be used in combination withGROUPING SETS. For example:GROUP BY GROUPING SETS((col2), (col3), ROLLUP(col1, col2, col3)) Equivalent to GROUP BY GROUPING SETS((col2), (col3), (col1, col2, col3), (col1, col2), (col1), ())Example:
SELECT col1, col2, col3, COUNT(*) FROM tbl1 GROUP BY ROLLUP (col1, col2, col3);The return result is as follows:
+------+------+------+----------+ | col1 | col2 | col3 | COUNT(*) | +------+------+------+----------+ | 1 | A1 | 1 | 1 | | 1 | A1 | NULL | 1 | | 1 | NULL | NULL | 1 | | 2 | A2 | 2 | 1 | | 2 | A2 | NULL | 1 | | 2 | NULL | NULL | 1 | | 3 | A3 | 3 | 1 | | 3 | A3 | NULL | 1 | | 3 | NULL | NULL | 1 | | NULL | NULL | NULL | 3 | +------+------+------+----------+ 10 rows in setCUBE: Indicates a multi-dimensional aggregation method used to generate all possible grouping combinations. It has the following characteristics:- The detailed data of each group.
- Summary data for all possible group combinations.
- Total rows.
CUBEcan be regarded as a special case ofGROUPING SETS. For example:GROUP BY CUBE(col1, col2, col3) Equivalent to GROUP BY GROUPING SETS ((col1, col2, col3), (col1, col2), (col1, col3), (col2, col3), (col1), (col2), (col3), ())The
CUBEclause can also be used together with theGROUPING SETSclause. For example:GROUP BY col1, CUBE(col2, col3), GROUPING SETS((col4), (col5)) is equivalent to GROUP BY GROUPING SETS( (col1, col2, col3, col4), (col1, col2, col3, col5), (col1, col2, col4), (col1, col2, col5), (col1, col3, col4), (col1, col3, col5), (col1, col4), (col1, col5))Example:
SELECT col1, col2, col3, COUNT(*) FROM tbl1 GROUP BY CUBE (col1, col2, col3);The return result is as follows:
+------+------+------+----------+ | col1 | col2 | col3 | COUNT(*) | +------+------+------+----------+ | NULL | NULL | NULL | 3 | | NULL | NULL | 1 | 1 | | NULL | NULL | 2 | 1 | | NULL | NULL | 3 | 1 | | NULL | A1 | NULL | 1 | | NULL | A2 | NULL | 1 | | NULL | A3 | NULL | 1 | | NULL | A1 | 1 | 1 | | NULL | A2 | 2 | 1 | | NULL | A3 | 3 | 1 | | 1 | NULL | NULL | 1 | | 2 | NULL | NULL | 1 | | 3 | NULL | NULL | 1 | | 1 | NULL | 1 | 1 | | 2 | NULL | 2 | 1 | | 3 | NULL | 3 | 1 | | 1 | A1 | NULL | 1 | | 2 | A2 | NULL | 1 | | 3 | A3 | NULL | 1 | | 1 | A1 | 1 | 1 | | 2 | A2 | 2 | 1 | | 3 | A3 | 3 | 1 | +------+------+------+----------+ 22 rows in set
having_condition
expression: A conditional expression that filters the results after grouping.
Example:
Query columns col1 and col2 from table tbl1, group by col2, sum col3, find rows where the sum of col3 is less than 3, and output those rows.
SELECT col1, col2, SUM(col3)
FROM tbl1
GROUP BY col2
HAVING SUM(col3) < 3;
The return result is as follows:
+------+------+-----------+
| col1 | col2 | SUM(col3) |
+------+------+-----------+
| 1 | A1 | 1 |
| 2 | A2 | 2 |
+------+------+-----------+
2 rows in set
order_by_condition
expression [ASC | DESC]: the conditional expression used to sort the result set.
ASC | DESC: Optional. Specifies the sorting order. ASC indicates ascending order (default), and DESC indicates descending order.
Example:
Query the data in table tbl1 and output the query results in descending order (DESC) by col3.
SELECT * FROM tbl1
ORDER BY col3 DESC;
The return result is as follows:
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
| 3 | A3 | 3 |
| 2 | A2 | 2 |
| 1 | A1 | 1 |
+------+------+------+
3 rows in set
limit_clause
[offset,] row_count: The value of this parameter must be an integer constant. The following table describes the parameters.offset: Optional. The number of rows to skip from the start of the result set (the offset). The default offset for the initial record row is 0.row_count: The number of rows to return. If only therow_countparameter is used, the firstrow_countrows of the result set are returned.
row_count OFFSET offset: Skips the firstoffsetrows of the result set and returns the nextrow_countrows.
Example:
Query the data in table tbl1, and use LIMIT to force the query to start from the second row, returning two rows of results from table tbl1.
SELECT * FROM tbl1
LIMIT 1, 2;
The return result is as follows:
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
| 2 | A2 | 2 |
| 3 | A3 | 3 |
+------+------+------+
2 rows in set
lock_option
FOR UPDATE [opt_for_update_wait]: Indicates that an exclusive lock is acquired on all rows of the query results to prevent concurrent modifications by other transactions, or to prevent concurrent reads at certain transaction isolation levels. For more information about exclusive locks, see Lock query results with SELECT FOR UPDATE.opt_for_update_wait: Optional. Specifies the behavior for acquiring locks. Valid values:WAIT {decimal | intnum}: the amount of time to wait for other transactions to release resources. If the resources cannot be acquired within this period, an error is returned. The unit is seconds.NOWAIT/NO_WAIT: Indicates that the transaction will not wait for other transactions to release resources and will directly return if the resources cannot be obtained.SKIP LOCKED: Indicates that the query will not wait for row locks and will be executed immediately, removing the locked rows from the result set.
Example:
You can use
SELECT ... FOR UPDATEto acquire a row-level lock on a table. If theLIMIT 1clause is used, the operator will be pushed down to the table scan step during query optimization, locking only the rows returned by theLIMIT.SELECT * FROM tbl1 LIMIT 1 FOR UPDATE;If you use the
ORDER BYclause to sort the query results, the results are sorted first, and thenLIMIT 1is executed. In this case, locks are acquired for all selected rows.SELECT * FROM tbl1 ORDER BY col1 LIMIT 1 FOR UPDATE;
LOCK IN SHARE MODE: Acquires a shared lock while querying data to prevent other transactions from writing to the data, but allows other transactions to read the data. For more information about shared locks, see Lock in share mode.
Examples
Sample simple table query
Create the
test_tbl1table.CREATE TABLE test_tbl1(col1 INT PRIMARY KEY, col2 VARCHAR(20), col3 INT);Insert test data into the
test_tbl1table.INSERT INTO test_tbl1 VALUES (1, 'A1', 10),(2, 'A2', 15),(3, 'A1', 8);Query the data in column
col2of thetest_tbl1table and deduplicate it.SELECT DISTINCT col2 FROM test_tbl1;The return result is as follows:
+------+ | col2 | +------+ | A1 | | A2 | +------+ 2 rows in set
Sample data query using dblink
Read the
number_ttable from the remotetestdatabaseSELECT * FROM number_t@ob_dblink;The return result is as follows:
+--------+-------------+---------+---------------------+--------------------------------+ | c_int | c_bigint | c_float | c_double | c_decimal | +--------+-------------+---------+---------------------+--------------------------------+ | 214748 | 92233720368 | 3.40282 | 1.7976931348623157 | 123456789123456789.1234567890 | +--------+-------------+---------+---------------------+--------------------------------+ 1 row in setRead the
number_ttable from the remotetestdatabase and perform a JOIN operation with the localnumber_ttable.SELECT a.c_decimal, b.c_double FROM number_t a, number_t@ob_dblink b WHERE a.c_int = b.c_int;The return result is as follows:
+--------------------------------+---------------------+ | c_decimal | c_double | +---------------------+--------------------------------+ | 123456789123456789.1234567890 | 1.7976931348623157 | +---------------------+--------------------------------+ 1 row in setRead the
datetime_ttable from the remotemysqldatabase.SELECT * FROM mysql.datetime_t@ob_dblink;The return result is as follows:
+-------------+------------+---------+----------------------+----------------------+ | c_date | c_time | c_year | c_datetime | c_timestamp | +-------------+------------+---------+-----------------------+---------------------+ | 2023-04-13 | 12:12:12 | 2078 | 2100-11-01 12:12:13 | 2100-12-01 21:14:15 | +-------------+------------+---------+----------------------+----------------------+ 1 row in set