MySQL Shell API 9.0.0
Unified development interface for MySQL Products
|
Handler for record selection on a Table. More...
Methods | |
TableSelect | select () |
Defines the columns to be retrieved from the table. More... | |
TableSelect | where (str expression) |
Sets the search condition to filter the records to be retrieved from the Table. More... | |
TableSelect | group_by (list columns) |
Sets a grouping criteria for the retrieved rows. More... | |
TableSelect | having (str condition) |
Sets a condition for records to be considered in aggregate function operations. More... | |
TableSelect | order_by (list sortCriteria) |
Sets the order in which the records will be retrieved. More... | |
TableSelect | limit (int numberOfRows) |
Sets the maximum number of rows to be returned on the select operation. More... | |
TableSelect | offset (int numberOfRows) |
Sets number of rows to skip on the resultset when a limit has been defined. More... | |
TableSelect | lock_shared (str lockContention) |
Instructs the server to acquire shared row locks in documents matched by this find operation. More... | |
TableSelect | lock_exclusive (str lockContention) |
Instructs the server to acquire an exclusive lock on rows matched by this find operation. More... | |
TableSelect | bind (str name, Value value) |
Binds a value to a specific placeholder used on this operation. More... | |
RowResult | execute () |
Executes the select operation with all the configured options. More... | |
Handler for record selection on a Table.
This object provides the necessary functions to allow selecting record data from a table.
This object should only be created by calling the select function on the table object from which the record data will be retrieved.
TableSelect select | ( | ) |
Defines the columns to be retrieved from the table.
Calling select() will cause all the columns in the table to be retrieved.
To retrieve only certain columns use either select(list columns) and provide a list object containing the column definitions, or select(str column[, str column, ...]) and pass each column definition as a separate parameter.
After this function invocation, the following functions can be invoked:
TableSelect where | ( | str | expression | ) |
Sets the search condition to filter the records to be retrieved from the Table.
expression | A condition to filter the records to be retrieved. |
If used, only those rows satisfying the expression will be retrieved.
The expression supports Parameter Binding.
This function can be invoked only once after:
TableSelect group_by | ( | list | columns | ) |
Sets a grouping criteria for the retrieved rows.
This function can be invoked only once after:
TableSelect having | ( | str | condition | ) |
Sets a condition for records to be considered in aggregate function operations.
condition | A condition to be used with aggregate functions. |
If used the TableSelect operation will only consider the records matching the established criteria.
The condition supports Parameter Binding.
This function can be invoked only once after:
TableSelect order_by | ( | list | sortCriteria | ) |
Sets the order in which the records will be retrieved.
If used, the TableSelect operation will return the records sorted with the defined criteria.
Every defined sort criterion follows the format:
name [ ASC | DESC ]
ASC is used by default if the sort order is not specified.
This function can be invoked only once after:
TableSelect limit | ( | int | numberOfRows | ) |
Sets the maximum number of rows to be returned on the select operation.
numberOfRows | The maximum number of rows to be retrieved. |
If used, the operation will return at most numberOfRows rows.
This function can be invoked only once after:
After this function invocation, the following functions can be invoked:
TableSelect offset | ( | int | numberOfRows | ) |
Sets number of rows to skip on the resultset when a limit has been defined.
numberOfRows | The number of rows to skip before start including them on the RowResult. |
If used, the first numberOfRows records will not be included on the result.
This function can be invoked only once after:
TableSelect lock_shared | ( | str | lockContention | ) |
Instructs the server to acquire shared row locks in documents matched by this find operation.
lockContention | optional parameter to indicate how to handle rows that are already locked. |
When this function is called, the selected rows will be locked for write operations, they may be retrieved on a different session, but no updates will be allowed.
The acquired locks will be released when the current transaction is committed or rolled back.
The lockContention parameter defines the behavior of the operation if another session contains an exclusive lock to matching rows.
The lockContention can be specified using the following constants:
The lockContention can also be specified using the following string literals (no case sensitive):
If no lockContention or the default is specified, the operation will block if another session already holds an exclusive lock on matching rows until the lock is released.
If lockContention is set to NOWAIT and another session already holds an exclusive lock on matching rows, the operation will not block and an error will be generated.
If lockContention is set to SKIP_LOCKED and another session already holds an exclusive lock on matching rows, the operation will not block and will return only those rows not having an exclusive lock.
This operation only makes sense within a transaction.
This function can be invoked at any time before bind() or execute() are called.
After this function invocation, the following functions can be invoked:
If lock_exclusive() is called, it will override the lock type to be used on the selected documents.
TableSelect lock_exclusive | ( | str | lockContention | ) |
Instructs the server to acquire an exclusive lock on rows matched by this find operation.
lockContention | optional parameter to indicate how to handle rows that are already locked. |
When this function is called, the selected rows will be locked for read operations, they will not be retrievable by other session.
The acquired locks will be released when the current transaction is committed or rolled back.
The lockContention parameter defines the behavior of the operation if another session contains a lock to matching rows.
The lockContention can be specified using the following constants:
The lockContention can also be specified using the following string literals (no case sensitive):
If no lockContention or the default is specified, the operation will block if another session already holds a lock on matching rows until the lock is released.
If lockContention is set to NOWAIT and another session already holds a lock on matching rows, the operation will not block and an error will be generated.
If lockContention is set to SKIP_LOCKED and another session already holds a lock on matching rows, the operation will not block and will return only those rows not having an exclusive lock.
This operation only makes sense within a transaction.
This function can be invoked at any time before bind() or execute() are called.
After this function invocation, the following functions can be invoked:
If lock_shared() is called, it will override the lock type to be used on the selected documents.
TableSelect bind | ( | str | name, |
Value | value | ||
) |
Binds a value to a specific placeholder used on this operation.
name | The name of the placeholder to which the value will be bound. |
value | The value to be bound on the placeholder. |
Binds the given value to the placeholder with the specified name.
An error will be raised if the placeholder indicated by name does not exist.
This function must be called once for each used placeholder or an error will be raised when the execute() method is called.
This function can be invoked multiple times right before calling execute().
After this function invocation, the following functions can be invoked:
RowResult execute | ( | ) |
Executes the select operation with all the configured options.
This function can be invoked after any other function on this class.