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(), dba.configureLocalInstance(), and so on.

  • 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

When MySQL Shell is in SQL mode, autocompletion tries to complete any word with all possible completions that match. In SQL mode the following can be autocompleted:

• SQL keywords - List of known SQL keywords. Matching is case-insensitive.

• SQL snippets - Certain common snippets, such as SHOW CREATE TABLE, ALTER TABLE, CREATE TABLE, and so on.

• Table names - If there is an active schema and database name caching is not disabled, all the tables of the active schema are used as possible completions.

As a special exception, if a backtick is found, only table names are considered for completion. In SQL mode, autocompletion is not context aware, meaning there is no filtering of completions based on the SQL grammar. In other words, autocompleting SEL returns SELECT, but it could also include a table called selfies.

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.