MySQL :: MySQL Shell 9.1 :: 5.3 Code Autocompletion

MySQL Shell 9.1 / MySQL Shell Code Execution / Code Autocompletion

5.3 Code Autocompletion

MySQL Shell supports autocompletion of text preceding the cursor by pressing the Tab key. The Section 3.1, “MySQL Shell Commands” can be autocompleted in any of the language modes. For example typing \con and pressing the Tab key autocompletes to \connect. Autocompletion is available for SQL, JavaScript, and Python language keywords depending on the current Section 5.1, “Active Language”.

Autocompletion supports the following text objects:

In SQL mode, autocompletion is aware of schema names, table names, column names of the current active schema.
In JavaScript and Python modes autocompletion is aware of object members, for example:
- global object names such as session, db, dba, shell, mysql, mysqlx, and so on.
- members of global objects such as session.connect().
- global user defined variables
- chained object property references such as shell.options.verbose.
- chained X DevAPI method calls such as col.find().where().execute().fetchOne().

By default autocompletion is enabled, to change this behavior see Configuring Autocompletion.

Once you activate autocompletion, if the text preceding the cursor has exactly one possible match, the text is automatically completed. If autocompletion finds multiple possible matches, it beeps or flashes the terminal. If the Tab key is pressed again, a list of the possible completions is displayed. If no match is found then no autocompletion happens.

Autocompleting SQL

In SQL mode, context-aware autocompletion completes any word with relevant completions. The following can be autocompleted:

Schemas
Tables
Views
Columns
Stored procedures
Functions
Triggers
Events
Engines
User-defined functions
Runtime functions
Log file groups
User variables
System variables
Tablespaces
Users
Character sets
Collations
Plugins

If you connect to a MySQL instance but do not select a schema, autocompletion is available for global objects, charsets, engines, schemas and so on. For example on a default MySQL installation, USE suggests the names of all schemas detected unless one or more relevant characters from the schema name are provided:

         SQL > use 
         information_schema  mysql  performance_schema  sys

If a schema is selected, additional schema information is loaded and available for autocompletion (tables, events, and so on). If you switch from one schema to another, the objects loaded from the previous schema are still available for autocompletion. However, any new object added during the session will not be available for autocompletion until the \rehash command is run.

To fetch a list of suggestions or complete a partial word from the selected schema, enter the initial fragment and press the Tab button twice. For example:

At the SQL prompt, enter the following fragment: SE.
Press the Tab key twice.

The following suggestions are displayed below your input:
```
            SET SELECT
```
At the SQL prompt, enter the following fragment: SEL.
Press the Tab key twice.

The fragment autocompletes to SELECT.

If there are many possible results, you are prompted to display the results or not. For example:

Display all 118 possibilities? (y or n)

SQL Autocompletion API

The autocompletion API is exposed to developers through the following functions:

JavaScript: shell.autoCompleteSql(statement, options)
Python: shell.auto_complete_sql(statement, options)

statement: "string": A partial SQL statement for autocompletion.

These return feasible candidates for the autocompletion.

Options:

serverVersion: "string": Required. Server grammar version. This takes the format major.minor.patch. serverVersion:"8.0.31", for example.
sqlMode: "string": Required. The SQL Mode to use. A comma-separated string, sqlMode: "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION", for example. For more information, see Server SQL Modes.
statementOffset: number: Optional. The zero-based offset position of the caret in the statement. Default value is the length of the statement.
uppercaseKeywords: [true|false]: Default true. Whether the returned keywords are in upper case.
filtered: [true|false]: Default true. Whether explicit candidate names returned in the result should be filtered using the prefix which is being auto-completed.

This function returns a dictionary describing candidates for statement autocompletion using the following syntax:

        {
         "context": {
           "prefix": string,
           "qualifier": list of strings,
           "references": list of dictionaries,
           "labels": list of strings,
         },
         "keywords": list of strings,
         "functions": list of strings,
         "candidates": list of strings,
        }

context: the context of the autocomplete operation.
prefix: the fragment being autocompleted.
qualifier: present if a qualified name is available.

For example:
- SELECT s: the prefix is 's', no qualifier is present.
- SELECT schema1.t: the prefix is 't', the qualifier is ['schema1'].
- SELECT schema1.table1.c: the prefix is 'c', the qualifier is ['schema1','table1'].
- SELECT schema1.table1.column1 FR: the prefix is 'FR', no qualifier is present.
references: references detected in the statement.
- schema: name of the schema.
- table: name of the table referenced in the statement.
- alias: alias of the table.
labels: labels in labeled blocks.
keywords: candidate keyword suggestions.
functions: candidate MySQL library (runtime) functions whose names are also keywords.
candidates: lists one or more of the supported candidates. Schemas, tables, views, and so on.

For example:

          JS > shell.autoCompleteSql("select * from  ",{serverVersion: "8.0.30", sqlMode: "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"})
            {
                "candidates": [
                    "schemas",
                    "tables",
                    "views"
                ],
                "context": {
                    "prefix": ""
                },
                "functions": [
                    "JSON_TABLE()"
                ],
                "keywords": [
                    "DUAL",
                    "LATERAL"
                ]
            }

Autocompleting JavaScript and Python

In both JavaScript and Python modes, the string to be completed is determined from right to left, beginning at the current cursor position when Tab is pressed. Contents inside method calls are ignored, but must be syntactically correct. This means that strings, comments and nested method calls must all be properly closed and balanced. This allows chained methods to be handled properly. For example, when you are issuing:

print(db.user.select().where("user in ('foo', 'bar')").e

Pressing the Tab key would cause autocompletion to try to complete the text db.user.select().where().e but this invalid code yields undefined behavior. Any whitespace, including newlines, between tokens separated by a . is ignored.

Configuring Autocompletion

By default the autocompletion engine is enabled. This section explains how to disable autocompletion and how to use the \rehash MySQL Shell command. Autocompletion uses a cache of database name objects that MySQL Shell is aware of. When autocompletion is enabled, this name cache is automatically updated. For example whenever you load a schema, the autocompletion engine updates the name cache based on the text objects found in the schema, so that you can autocomplete table names and so on.

To disable this behavior you can:

Start MySQL Shell with the --no-name-cache command option.
Modify the autocomplete.nameCache and devapi.dbObjectHandles keys of the shell.options to disable the autocompletion while MySQL Shell is running.

When the autocompletion name cache is disabled, you can manually update the text objects autocompletion is aware of by issuing \rehash. This forces a reload of the name cache based on the current active schema.

To disable autocompletion while MySQL Shell is running use the following shell.options keys:

autocomplete.nameCache: boolean toggles autocompletion name caching for use by SQL.
devapi.dbObjectHandles: boolean toggles autocompletion name caching for use by the X DevAPI db object, for example db.mytable, db.mycollection.

Both keys are set to true by default, and set to false if the --no-name-cache command option is used. To change the autocompletion name caching for SQL while MySQL Shell is running, issue:

shell.options['autocomplete.nameCache']=true

Use the \rehash command to update the name cache manually.

To change the autocompletion name caching for JavaScript and Python while MySQL Shell is running, issue:

shell.options['devapi.dbObjectHandles']=true

Again you can use the \rehash command to update the name cache manually.

PREV HOME UP NEXT