Chapter 3 Using MySQL as a Document Store

This chapter introduces an alternative way of working with MySQL as a document store, sometimes referred to as using NoSQL. If your intention is to use MySQL in a traditional (SQL) way, this chapter is probably not relevant to you.

Using MySQL as a document store is currently a preproduction feature to which this notice applies: Section 3.1, “Preproduction Status — Legal Notice”.

Relational databases such as MySQL usually required a document schema to be defined before documents can be stored. The features described in this section enable you to use MySQL as a document store, which is a schema-less, and therefore schema-flexible, storage system for documents. When using MySQL as a document store, to create documents describing products you do not need to know and define all possible attributes of any products before storing them and operating with them. This differs from working with a relational database and storing products in a table, when all columns of the table must be known and defined before adding any products to the database. The features described in this chapter enable you to choose how you configure MySQL, using only the document store model, or combining the flexibility of the document store model with the power of the relational model.

These sections cover the usage of MySQL as a document store:

3.1 Preproduction Status — Legal Notice

This documentation is in preproduction status and is intended for demonstration and preliminary use only. It may not be specific to the hardware on which you are using the software. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to this documentation and will not be responsible for any loss, costs, or damages incurred due to the use of this documentation.

3.2 Key Concepts

This section explains the concepts introduced as part of using MySQL as a document store.

Document

A Document is a set of key and value pairs, as represented by a JSON object. A Document is represented internally using the MySQL binary JSON object, through the JSON MySQL datatype. The values of fields can contain other documents, arrays, and lists of documents.

{
    "GNP": .6,
    "IndepYear": 1967,
    "Name": "Sealand",
    "_id": "SEA",
    "demographics": {
        "LifeExpectancy": 79,
        "Population": 27
    },
    "geography": {
        "Continent": "Europe",
        "Region": "British Islands",
        "SurfaceArea": 193
    },
    "government": {
        "GovernmentForm": "Monarchy",
        "HeadOfState": "Michael Bates"
    }
}

Collection

A Collection is a container that may be used to store Documents in a MySQL database.

CRUD Operations

Create, Read, Update and Delete (CRUD) operations are the four basic operations that can be performed on a database Collection or Table. In terms of MySQL this means:

  • Create a new entry (insertion or addition)

  • Read entries (queries)

  • Update entries

  • Delete entries

X Plugin

The MySQL Server plugin which enables communication using X Protocol. Supports clients that implement X DevAPI and enables you to use MySQL as a document store.

X Protocol

A protocol to communicate with a MySQL Server running X Plugin. X Protocol supports both CRUD and SQL operations, authentication via SASL, allows streaming (pipelining) of commands and is extensible on the protocol and the message layer.

3.3 Setting Up MySQL as a Document Store

This section describes how to install the X Plugin to enable MySQL Server to use the X Protocol and use MySQL Shell to act as a client to the server.

The prerequisite for using MySQL as a document store is the X Plugin, which implements the X Protocol. Without the X Plugin running, X Protocol clients cannot connect to the server. The X Plugin is shipped with MySQL (5.7.12 or higher) — installing it does not involve a separate download.

Setting up MySQL as a document store follows the steps outlined here:

  1. Install or upgrade to MySQL 5.7.12 or higher.

    When the installation or upgrade is done, start the server. For server startup instructions, see Section 2.10.2, “Starting the Server”.

    Note

    MySQL Installer enables you to perform this and the next step (Install the X Plugin) at the same time for new installations on Microsoft Windows. In the Plugin and Extensions screen, check mark the Enable X Protocol/MySQL as a Document Store check box. After the installation, verify that the X Plugin has been installed.

  2. Install the X Plugin. A non-root account can be used to install the plugin as long as the account has INSERT privilege for the mysql.plugin table.

    Always save your existing configuration settings before reconfiguring the server.

    To install the built-in X Plugin, do one of the following:

    • Using MySQL Installer for Windows:

      1. Launch MySQL Installer for Windows. MySQL Installer dashboard opens.

      2. Click the Reconfigure quick action for MySQL Server. Use Next and Back to configure the following items:

        • In Accounts and Roles, confirm the current root account password.

        • In Plugin and Extensions, check mark the Enable X Protocol/MySQL as a Document Store check box. MySQL Installer provides a default port number and opens the firewall port for network access.

        • In Apply Server Configuration, click Execute.

        • Click Finish to close MySQL Installer.

      3. Install MySQL Shell.

    • Using MySQL Shell:

      1. Install MySQL Shell.

      2. Open a terminal window (command prompt on Windows) and navigate to the MySQL binaries location (for example, /usr/bin/ on Linux).

      3. Run the following command:

        mysqlsh -u user -h localhost --classic --dba enableXProtocol
        
    • Using the MySQL Client program:

      1. Open a terminal window (command prompt on Windows) and navigate to the MySQL binaries location (for example, /usr/bin/ on Linux).

      2. Invoke the mysql command-line client:

        mysql -u user -p
        
      3. Issue the following statement:

        mysql> INSTALL PLUGIN mysqlx SONAME 'mysqlx.so';
        

        Replace mysqlx.so with mysqlx.dll for Windows.

      4. Install MySQL Shell.

  3. Verify that the X Plugin has been installed.

    When the X Plugin is installed properly, it shows up in the list when you query for active plugins on the server with one of the following commands:

    • MySQL Shell command:

      mysqlsh -u user --sqlc -e "show plugins"
      
    • MySQL Client program command:

      mysql -u user -p -e "show plugins"
      

    If you encounter problems with the X Plugin installation, or if you want to learn about alternative ways of installing, configuring, or uninstalling server plugins, see Section 6.5.2, “Installing and Uninstalling Plugins”.

mysqlxsys@localhost User Account

Installing the X Plugin creates a mysqlxsys@localhost user account. If, for some reason, creating the user account fails, the X Plugin installation fails, too. Here is an explanation on what the mysqlxsys@localhost user account is for and what to do when its creation fails.

The X Plugin installation process uses the MySQL root user to create an internal account for the mysqlxsys@localhost user. The mysqlxsys@localhost account is used by the X Plugin for authentication of external users against the MySQL account system and for killing sessions when requested by a privileged user. The mysqlxsys@localhost account is created as locked, so it cannot be used to log in by external users. If for some reason the MySQL root account is not available, before you start the X Plugin installation you must manually create the mysqlxsys@localhost user by issuing the following statements in the mysql command-line client:

CREATE USER IF NOT EXISTS mysqlxsys@localhost IDENTIFIED WITH
mysql_native_password AS 'password' ACCOUNT LOCK;
GRANT SELECT ON mysql.user TO mysqlxsys@localhost;
GRANT SUPER ON *.* TO mysqlxsys@localhost;

Uninstalling the X Plugin

If you ever want to uninstall (deactivate) the X Plugin, issue the following statement in the mysql command-line client:

UNINSTALL PLUGIN mysqlx;

Do not use MySQL Shell to issue the previous statement. It works from MySQL Shell, but you get an error (code 1130). Also, uninstalling the plugin removes the mysqlxsys user.

3.3.1 Installing MySQL Shell

This section describes how to download, install, and start MySQL Shell, which is an interactive JavaScript, Python, or SQL interface supporting development and administration for the MySQL Server. MySQL Shell is a component of MySQL 5.7.12 or higher that you can install separately.

Requirements

MySQL Shell is available on Microsoft Windows, Linux, and OS X for 64-bit platforms. MySQL Shell requires that the built-in X Plugin be active for MySQL 5.7.12 or higher. You can install the server plugin before or after you install MySQL Shell. For instructions, see Installing the X Plugin.

3.3.1.1 Installing MySQL Shell on Microsoft Windows

To install MySQL Shell on Microsoft Windows using the MSI Installer, do the following:

  1. Download the Windows (x86, 64-bit), MSI Installer package from http://dev.mysql.com/downloads/shell/.

  2. When prompted, click Run.

  3. Follow the steps in the Setup Wizard.

    Figure 3.1 Installation of MySQL Shell on Windows

    Installation of MySQL Shell on Windows

If you have installed MySQL without enabling the X Plugin, then later on decide you want to install the X Plugin, or if you are installing MySQL without using MySQL Installer, see Installing the X Plugin.

3.3.1.2 Installing MySQL Shell on Linux

Note

Installation packages for MySQL Shell are available only for a limited number of Linux distributions, and only for 64-bit systems.

For supported Linux distributions, the easiest way to install MySQL Shell on Linux is to use the MySQL APT repository or MySQL Yum repository. For systems not using the MySQL repositories, MySQL Shell can also be downloaded and installed directly.

Installing MySQL Shell with the MySQL APT Repository
Note

Installation of MySQL Shell using the MySQL APT repository is only supported on Ubuntu 14.04 LTS (“Trusty Tahr”) and Ubuntu 15.10 (“Wily Werewolf”).

For Linux distributions supported by the MySQL APT repository, follow one of the paths below:

  • If you do not yet have the MySQL APT repository as a software repository on your system, do the following:

    • Follow the steps given in Adding the MySQL APT Repository, paying special attention to the following:

      • During the installation of the configuration package, when asked in the dialogue box to configure the repository, make sure you choose MySQL 5.7 (which is the default option) as the release series you want, and enable the MySQL Preview Packages component.

      • Make sure you do not skip the step for updating package information for the MySQL APT repository:

        sudo apt-get update
    • Install MySQL Shell with this command:

      sudo apt-get install mysql-shell
      
  • If you already have the MySQL APT repository as a software repository on your system, do the following:

    • Update package information for the MySQL APT repository:

      sudo apt-get update
      
    • Update the MySQL APT repository configuration package with the following command:

      sudo apt-get install mysql-apt-config
      

      When asked in the dialogue box to configure the repository, make sure you choose MySQL 5.7 (which is the default option) as the release series you want, and enable the MySQL Preview Packages component.

    • Install MySQL Shell with this command:

      sudo apt-get install mysql-shell
      
Installing MySQL Shell with the MySQL Yum Repository
Note

Installation of MySQL Shell using the MySQL Yum repository is only supported on EL6 and EL7-based systems, as well as Fedora 24.

For Linux distributions supported by the MySQL Yum repository, follow these steps to install MySQL Shell:

  • Do one of the following:

    • If you already have the MySQL Yum repository as a software repository on your system and the repository was configured with the new release package mysql57-community-release, skip to the next step (Enable the MySQL Tools Preview subrepository...).

    • If you already have the MySQL Yum repository as a software repository on your system but have configured the repository with the old release package mysql-community-release, it is easiest to install MySQL Shell by first reconfiguring the MySQL Yum repository with the new mysql57-community-release package. To do so, you need to remove your old release package first, with the following command :

      sudo yum remove mysql-community-release
      

      For dnf-enabled systems, do this instead:

      sudo dnf erase mysql-community-release
      

      Then, follow the steps given in Adding the MySQL Yum Repository to install the new release package, mysql57-community-release.

    • If you do not yet have the MySQL Yum repository as a software repository on your system, follow the steps given in Adding the MySQL Yum Repository.

  • Enable the MySQL Tools Preview subrepository. You can do that by editing manually the /etc/yum.repos.d/mysql-community.repo file. This is an example of the subrepository's default entry in the file (the baseurl entry in your file might look different, depending on your Linux distribution):

    [mysql-tools-preview]
    name=MySQL Tools Preview
    baseurl=http://repo.mysql.com/yum/mysql-tools-preview/el/6/$basearch/
    enabled=0
    gpgcheck=1
    gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
    

    Change the entry enabled=0 to enabled=1 to enable the subrepository.

  • Install MySQL Shell with this command:

    sudo yum install mysql-shell
    

    For dnf-enabled systems, do this instead:

    sudo dnf install mysql-shell
    
Installing MySQL Shell from Direct Downloads from the MySQL Developer Zone

RPM, Debian, and source packages for installing MySQL Shell are also available for download at Download MySQL Shell.

3.3.1.3 Installing MySQL Shell on OS X

To install MySQL Shell on OS X, do the following:

  1. Download the package from http://dev.mysql.com/downloads/shell/.

  2. Double-click the downloaded DMG to mount it. Finder opens.

  3. Double-click the .pkg file shown in the Finder window.

  4. Follow the steps in the installation wizard.

    Figure 3.2 Installation of MySQL Shell on OS X

    Installation of MySQL Shell on OS X

  5. When the installer finishes, eject the DMG. (It can be deleted.)

3.3.2 Starting MySQL Shell

You need an account name and password to establish a session using MySQL Shell. Replace user with your account name.

On the same system where the server instance is running, open a terminal window (command prompt on Windows) and start MySQL Shell with the following command:

mysqlsh --uri user@localhost

You are prompted to input your password and then this establishes an X Session.

For instructions to get you started using MySQL Shell and MySQL as a document store, see the following quick-start guides:

3.4 Quick-Start Guide: MySQL Shell for JavaScript

This quick-start guide provides instructions to begin prototyping database applications interactively with MySQL Shell. The guide includes the following topics:

  • Introduction to MySQL functionality, MySQL Shell, and the world_x database sample.

  • Operations to manage collections and documents.

  • Operations to manage relational tables.

  • Operations that apply to documents within tables.

Available Quick-Start Guides

Related Information

3.4.1 Introduction

The MySQL Shell for JavaScript quick start provides a short but comprehensive introduction to the database functionality introduced with MySQL 5.7.12. This functionality includes the new X DevAPI, which offers a modern, integrative way to work with relational and document data, without requiring SQL knowledge from application developers.

In MySQL, tables are the native data storage container type and collections are stored internally using tables.

JSON Documents and Collections

A JSON document is a data structure composed of field/value pairs stored within a collection. The values of fields often contain other documents, arrays, and lists of documents.

{
    "GNP": .6,
    "IndepYear": 1967,
    "Name": "Sealand",
    "_id": "SEA",
    "demographics": {
        "LifeExpectancy": 79,
        "Population": 27
    },
    "geography": {
        "Continent": "Europe",
        "Region": "British Islands",
        "SurfaceArea": 193
    },
    "government": {
        "GovernmentForm": "Monarchy",
        "HeadOfState": "Michael Bates"
    }
}

Relational Tables

A table in MySQL enables you to store data organized in rows and columns. The structure of a table is defined by one or more columns with user-defined names and data types. Every row stored in the table has the same structure.

+------+------------------+-------------+-----------------+------------------------+
| ID   | Name             | CountryCode | District        | Info                   |
+------+------------------+-------------+-----------------+------------------------+
|    1 | Kabul            | AFG         | Kabol           |{"Population": 1780000} |
|    2 | Qandahar         | AFG         | Qandahar        |{"Population": 237500}  |
|    3 | Herat            | AFG         | Herat           |{"Population": 186800}  |
|    4 | Mazar-e-Sharif   | AFG         | Balkh           |{"Population": 127800}  |
|    5 | Amsterdam        | NLD         | Noord-Holland   |{"Population": 731200}  |
|    6 | Rotterdam        | NLD         | Zuid-Holland    |{"Population": 593321}  |
+------+------------------+-------------+-----------------+------------------------+

Related Information

3.4.2 Import Database Sample

The world_x database sample contains one JSON collection and a set of three relational tables:

  • Collection

    • countryinfo: Information about countries in the world.

  • Tables

    • country: Minimal information about countries of the world.

    • city: Information about some of the cities in those countries.

    • countrylanguage: Languages spoken in each country.

Requirements

You must install MySQL Shell and MySQL 5.7.12 or higher with the X Protocol enabled. For instructions, see Section 3.3, “Setting Up MySQL as a Document Store”.

Start the server before you load the world_x database for this guide.

Download world_x Database

To prepare the world_x database sample, follow these steps:

  1. Download world_x-db.zip.

  2. Extract the installation archive to a temporary location such as /tmp/. Unpacking the archive results in a single file named world_x.sql.

  3. Create the schema with the following command:

    mysqlsh -u root --sql < /tmp/world_x-db/world_x.sql
    Enter password: ****
    Records: 4079  Duplicates: 0  Warnings: 0
    
    Records: 239  Duplicates: 0  Warnings: 0
    
    Records: 239  Duplicates: 0  Warnings: 0
    
    Records: 984  Duplicates: 0  Warnings: 0
    

    Enter your password when prompted. A non-root account can be used as long as the account has privileges to create new databases.

    Replace /tmp/ with the path to the world_x.sql file on your system.

Related Information

3.4.3 MySQL Shell

MySQL Shell is a unified scripting interface to MySQL Server. It supports scripting in JavaScript and Python. JavaScript is the default processing mode. In most cases, you need an account to connect to the local MySQL server instance.

Start MySQL Shell

After you have installed and started MySQL server, connect MySQL Shell to the server instance. By default, MySQL Shell connects using the X Protocol.

On the same system where the server instance is running, open a terminal window and start MySQL Shell with the following command:

mysqlsh -u name world_x
Creating an X Session to name@localhost:33060/world_x
Enter password: ****

You may need to specify the path as appropriate.

In addition:

  • name represents the user name of your MySQL account.

  • MySQL Shell prompts you for your password.

  • The default schema for this session is the world_x database. For instructions on setting up the world_x database sample, see Section 3.4.2, “Import Database Sample”.

The mysql-js> prompt indicates that the active language for this session is JavaScript.

mysql-js>

When you run mysqlsh without the host argument, MySQL Shell attempts to connect to the server instance running on the localhost interface on port 33060. To specify a different host or port number, as well as other options, see the option descriptions at Section 5.5.7, “mysqlsh — The MySQL Shell”.

MySQL Shell supports input-line editing as follows:

  • left-arrow and right-arrow keys move horizontally within the current input line.

  • up-arrow and down-arrow keys move up and down through the set of previously entered lines.

  • Backspace deletes the character before the cursor and typing new characters enters them at the cursor position.

  • Enter enters the current input line.

Get Help for MySQL Shell

Type mysqlsh --help at the prompt of your command interpreter for a list of command-line options.

mysqlsh --help

Type \help at the MySQL Shell prompt for a list of available commands and their descriptions.

mysql-js> \help

Type \help followed by a command name for detailed help about an individual MySQL Shell command. For example, to view help on the \connect command, type:

mysql-js> \help \connect

Quit MySQL Shell

To quit MySQL Shell, type the following command:

mysql-js> \quit

Related Information

3.4.4 Documents and Collections

In MySQL, collections contain JSON documents that you can add, find, update, and remove. Collections are containers within a schema that you create, list, and drop.

The examples in this section use the countryinfo collection in the world_x database. For instructions on setting up the world_x database sample, see Section 3.4.2, “Import Database Sample”.

Documents

In MySQL, documents are represented as JSON objects. Internally, they are stored in an efficient binary format that enables fast lookups and updates.

  • Simple document format for JavaScript:

    {field1: "value", field2 : 10, "field 3": null}
    

An array of documents consists of a set of documents separated by commas and enclosed within [ and ] characters.

  • Simple array of documents for JavaScript:

    [{Name: "Aruba", _id: "ABW"}, {Name: "Angola", _id: "AGO"}]
    

MySQL supports the following JavaScript value types in JSON documents:

  • numbers (integer and floating point)

  • strings

  • boolean (false and true)

  • null

  • arrays of more JSON values

  • nested (or embedded) objects of more JSON values

Collections

Collections are containers for documents that share a purpose and possibly share one or more indexes. Each collection has a unique name and exists within a single schema.

The term schema is equivalent to a database, which means a group of database objects (as opposed to relational schema used to enforce structure and constraints over data). A schema does not enforce conformity on the documents in a collection.

In this quick-start guide:

  • Basic objects include:

    Object formDescription
    dbdb is a global variable assigned to the current active schema that you specified on the command line. You can type db in MySQL Shell to print a description of the object, which in this case will be the name of the schema it represents.
    db.getCollections()db.getCollections() holds a list of collections in the schema. Use the list to get references to collection objects, iterate over them, and so on.
  • Basic operations scoped by collections include:

    Operation formDescription
    db.name.add()The add() method inserts one document or a list of documents into the named collection.
    db.name.find()The find() method returns some or all documents in the named collection.
    db.name.modify()The modify() method updates documents in the named collection.
    db.name.remove()The remove() method deletes one document or a list of documents from the named collection.

Related Information

3.4.4.1 Create, List, and Drop Collections

In MySQL Shell, you can create new collections, get a list of the existing collections in a schema, and remove an existing collection from a schema. Collection names are case-sensitive and each collection name must be unique.

Confirm the Schema

To show the value that is assigned to the schema variable, type db.

mysql-js> db
<Schema:world_x>

If the schema value is not Schema:world_x, then set the db variable as follows:

mysql-js> \use world_x
Schema `world_x` accessible through db.
Create a Collection

To create a new collection in an existing schema, use the createCollection() method.

The following example creates in the world_x database a collection called flags. The method returns a collection object.

mysql-js> db.createCollection("flags")
<Collection:flags>
List Collections

To display all collections in the world_x database, use the getCollections() method on the schema object. Collections returned by the server appear between brackets.

mysql-js> db.getCollections()
[
    <Collection:countryinfo>,
    <Collection:flags>
]
Drop a Collection

To drop an existing collection from a database, use the dropCollection() method on the session object. For example, to drop the flags collection from the world_x database, type:

mysql-js> session.dropCollection("world_x", "flags")
Query OK (0.04 sec)
Related Information

3.4.4.2 Add Documents

You can use the add() method to insert one document or a list documents into an existing collection using MySQL Shell. All examples in this section use the countryinfo collection.

Confirm the Schema

To show the value that is assigned to the schema variable, type db.

mysql-js> db
<Schema:world_x>

If the schema value is not Schema:world_x, then set the db variable as follows:

mysql-js> \use world_x
Schema `world_x` accessible through db.
Add a Document

Insert the following document into the countryinfo collection. Press Enter twice to insert the document.

mysql-js> db.countryinfo.add(
 {
    GNP: .6,
    IndepYear: 1967,
    Name: "Sealand",
    _id: "SEA",
    demographics: {
        LifeExpectancy: 79,
        Population: 27
    },
    geography: {
        Continent: "Europe",
        Region: "British Islands",
        SurfaceArea: 193
    },
    government: {
        GovernmentForm: "Monarchy",
        HeadOfState: "Michael Bates"
    }
  }
)
Query OK, 1 item affected (0.02 sec)

The method returns the status of the operation.

Each document requires an identifier field called _id. The value of the _id field must be unique among all documents in the same collection. If the document passed to the add() method does not contain the _id field, MySQL Shell automatically inserts a field into the document and sets the value to a generated universal unique identifier (UUID).

Related Information

3.4.4.3 Find Documents

You can use the find() method to query for and return documents from a collection in a database. MySQL Shell provides additional methods to use with the find() method to filter and sort the returned documents.

MySQL provides the following operators to specify search conditions: OR (||), AND (&&), XOR, IS, NOT, BETWEEN, IN, LIKE, !=, <>, >, >=, <, <=, &, |, <<, >>, +, -, *, /, ~, and %.

Find All Documents in a Collection

To return all documents in a collection, use the find() method without specifying search conditions. For example, the following operation returns all documents in the countryinfo collection.

mysql-js> db.countryinfo.find()
[
     {
          "GNP": 828,
          "IndepYear": null,
          "Name": "Aruba",
          "_id": "ABW",
          "demographics": {
              "LifeExpectancy": 78.4000015258789,
              "Population": 103000
          },
          "geography": {
              "Continent": "North America",
              "Region": "Caribbean",
              "SurfaceArea": 193
          },
          "government": {
              "GovernmentForm": "Nonmetropolitan Territory of The Netherlands",
              "HeadOfState": "Beatrix"
          }
          ...
      }
 ]
240 documents in set (0.00 sec)

The method produces results that contain operational information in addition to all documents in the collection.

An empty set (no matching documents) returns the following information:

Empty set (0.00 sec) 
Filter Searches

You can include search conditions with the find() method. The syntax for expressions that form a search condition is the same as that of traditional MySQL. You must enclose all expressions in quotes.

All examples in this section use the countryinfo collection in the world_x database. For the sake of brevity, some of the examples do not display output.

A simple search condition consists of the _id field and unique identifier of a document. The following example returns a single document matching the identifier string:

mysql-js> db.countryinfo.find("_id = 'AUS'")
[
    {
        "GNP": 351182,
        "IndepYear": 1901,
        "Name": "Australia",
        "_id": "AUS",
        "demographics": {
            "LifeExpectancy": 79.80000305175781,
            "Population": 18886000
        },
        "geography": {
            "Continent": "Oceania",
            "Region": "Australia and New Zealand",
            "SurfaceArea": 7741220
        },
        "government": {
            "GovernmentForm": "Constitutional Monarchy, Federation",
            "HeadOfState": "Elisabeth II"
        }
    }
]
1 document in set (0.01 sec)

The following example searches for all countries that have a GNP higher than $500 billion. The countryinfo collection measures GNP in units of million.

mysql-js> db.countryinfo.find("GNP > 500000")
...[output removed]
10 documents in set (0.00 sec)

The Population field in the following query is embedded within the demographics object. To access the embedded field, use a period between demographics and Population to identify the relationship. Document and field names are case sensitive.

mysql-js> db.countryinfo.find("GNP > 500000 and demographics.Population < 100000000")
...[output removed]
6 documents in set (0.00 sec)

Arithmetic operators in the following expression are used to query for countries with a GNP per capita higher than $30000. Search conditions can include arithmetic operators and most MySQL functions.

Note

Seven documents in the countryinfo collection have a population value of zero. Warning messages appear at the end of the output.

mysql-js> db.countryinfo.find("GNP*1000000/demographics.Population > 30000")
...[output removed]
9 documents in set, 7 warnings (0.00 sec)
Warning (Code 1365): Division by 0
Warning (Code 1365): Division by 0
Warning (Code 1365): Division by 0
Warning (Code 1365): Division by 0
Warning (Code 1365): Division by 0
Warning (Code 1365): Division by 0
Warning (Code 1365): Division by 0

You can separate a value from the search condition by using the bind() method. For example, instead of specifying a hard-coded country name as the condition, substitute a named placeholder consisting of a colon followed by a name that begins with a letter, such as country. Then include the placeholder and value in the bind() method as follows:

mysql-js> db.countryinfo.find("Name = :country").bind("country", "Italy")
[
    {
        "GNP": 1161755,
        "IndepYear": 1861,
        "Name": "Italy",
        "_id": "ITA",
        "demographics": {
            "LifeExpectancy": 79,
            "Population": 57680000
        },
        "geography": {
            "Continent": "Europe",
            "Region": "Southern Europe",
            "SurfaceArea": 301316
        },
        "government": {
            "GovernmentForm": "Republic",
            "HeadOfState": "Carlo Azeglio Ciampi"
        }
    }
]
1 document in set (0.01 sec)
Tip

Within a program, binding enables you to specify placeholders in your expressions, which are filled in with values before execution and can benefit from automatic escaping, as appropriate.

Always use binding to sanitize input. Avoid introducing values in queries using string concatenation, which can produce invalid input and, in some cases, can cause security issues.

Project Results

You can return specific fields of a document, instead of returning all the fields. The following example returns the GNP and Name fields of all documents in the countryinfo collection matching the search conditions.

Use the fields() method to pass the list of fields to return.

mysql-js> db.countryinfo.find("GNP > 5000000").fields(["GNP", "Name"])
[
    {
        "GNP": 8510700,
        "Name": "United States"
    }
]
1 document in set (0.00 sec)
 

In addition, you can alter the returned documents—adding, renaming, nesting and even computing new field values—with an expression that describes the document to return.

First, use the following statement to import the mysqlx module:

mysql-js> var mysqlx=require('mysqlx');

Next, alter the names of the fields with the following expression to return only two documents.

mysql-js> db.countryinfo.find().
fields(mysqlx.expr('{"Name": upper(Name), "GNPPerCapita": GNP*1000000/demographics.Population}')).
limit(2)
[
    {
        "GNPPerCapita": 8038.834951456311,
        "Name": "ARUBA"
    },
    {
        "GNPPerCapita": 263.0281690140845,
        "Name": "AFGHANISTAN"
    }
]
2 documents in set (0.00 sec)
Limit, Sort, and Skip Results

You can apply the limit(), sort(), and skip() methods to manage the number and order of documents returned by the find() method.

To specify the number of documents included in a result set, append the limit() method with a value to the find() method. The following query returns the first five documents in the countryinfo collection.

mysql-js> db.countryinfo.find().limit(5)
... [output removed]
5 documents in set (0.00 sec)

To specify an order for the results, append the sort() method to the find() method. Pass to the sort() method a list of one or more fields to sort by and, optionally, the descending (desc) or ascending (asc) attribute as appropriate. Ascending order is the default order type.

For example, the following query sorts all documents by the IndepYear field and then returns the first eight documents in descending order.

mysql-js> db.countryinfo.find().sort(["IndepYear desc"]).limit(8)
... [output removed]
8 documents in set (0.00 sec)

By default, the limit() method starts from the first document in the collection. You can use the skip() method to change the starting document. For example, to ignore the first document and return the next eight documents matching the condition, pass to the skip() method a value of 1.

mysql-js> db.countryinfo.find().sort(["IndepYear desc"]).limit(8).skip(1)
... [output removed]
8 documents in set (0.00 sec)
Related Information

3.4.4.4 Modify Documents

You can use the modify() method to update one or more documents in a collection. The X DevAPI provides additional methods for use with the modify() method to:

  • Set and unset fields within documents.

  • Append, insert, and delete arrays.

  • Bind, limit, and sort the documents to be modified.

Set and Unset Fields

The modify() method works by filtering a collection to include only the documents to be modified and then applying the operations that you specify to those documents.

In the following example, the modify() method uses the search condition to identify the document to change and then the set() method replaces two values within the nested demographics object.

mysql-js> db.countryinfo.modify("_id = 'SEA'").
set("demographics", {LifeExpectancy: 78, Population: 28})
Query OK, 1 item affected (0.04 sec)

After you modify a document, use the find() method to verify the change.

To remove content from a document, use the modify() and unset() methods. For example, the following query removes the GNP from a document that matches the search condition.

mysql-js> db.countryinfo.modify("Name = 'Sealand'").unset("GNP")
Query OK, 1 item affected (0.01 sec)   

Use the find() method to verify the change.

mysql-js> db.countryinfo.find("Name = 'Sealand'")
[
    {
        "IndepYear": 1967,
        "Name": "Sealand",
        "_id": "SEA",
        "demographics": {
            "LifeExpectancy": 78,
            "Population": 28
        },
        "geography": {
            "Continent": "Europe",
            "Region": "British Islands",
            "SurfaceArea": 193
        },
        "government": {
            "GovernmentForm": "Monarchy",
            "HeadOfState": "Michael Bates"
        }
    }
]
1 document in set (0.00 sec)
Append, Insert, and Delete Arrays

To append an element to an array field, or insert, or delete elements in an array, use the arrayAppend(), arrayInsert(), or arrayDelete() methods. The following examples modify the countryinfo collection to enable tracking of international airports.

The first example uses the modify() and set() methods to create a new Airports field in all documents.

Caution

Use care when you modify documents without specifying a search condition. This action will modify all documents in the collection.

mysql-js> db.countryinfo.modify().set("Airports", [])
Query OK, 240 items affected (0.07 sec)

With the Airports field added, the next example uses the arrayAppend() method to add a new airport to one of the documents. $.Airports in the following example represents the Airports field of the current document.

mysql-js> db.countryinfo.modify("Name = 'France'").arrayAppend("$.Airports", "ORY")
Query OK, 1 item affected (0.02 sec)

Use db.countryinfo.find("Name = 'France'") to see the change.

To insert an element at a different position in the array, use the arrayInsert() method to specify which index to insert in the path expression. In this case, the index is 0, or the first element in the array.

mysql-js> db.countryinfo.modify("Name = 'France'").arrayInsert("$.Airports[0]", "CDG")
Query OK, 1 item affected (0.04 sec)

To delete an element from the array, you must pass to the arrayDelete() method the index of the element to be deleted.

mysql-js> db.countryinfo.modify("Name = 'France'").arrayDelete("$.Airports[1]")
Query OK, 1 item affected (0.03 sec)
Related Information

3.4.4.5 Remove Documents

You can use the remove() method to delete some or all documents from a collection in a database. The X DevAPI provides additional methods for use with the remove() method to filter and sort the documents to be removed.

Remove Documents Using Conditions

The example that follows passes a search condition to the remove() method. All documents matching the condition will be removed from the countryinfo collection. In this example, one document matches the condition.

mysql-js> db.countryinfo.remove("_id = 'SEA'")
Query OK, 1 item affected (0.02 sec)
Remove the First Document

To remove the first document in the countryinfo collection, use the limit() method with a value of 1.

mysql-js> db.countryinfo.remove().limit(1)
Query OK, 1 item affected (0.03 sec)
Remove the Last Document in an Order

The following example removes the last document in the countryinfo collection by country name.

mysql-js> db.countryinfo.remove().sort(["Name desc"]).limit(1)
Query OK, 1 item affected (0.02 sec)
Remove All Documents in a Collection

You can remove all documents in a collection. To do so, use the remove() method without specifying a search condition.

Caution

Use care when you remove documents without specifying a search condition. This action will delete all documents in the collection.

Related Information

3.4.4.6 Create and Drop Indexes

Indexes are used to find documents with specific field values quickly. Without an index, MySQL must begin with the first document and then read through the entire collection to find the relevant fields. The larger the collection, the more this costs. If a collection is large and queries on a specific field are common, then consider creating an index on a specific field inside a document.

For example, the following query will perform better with an index:

mysql-js> db.countryinfo.find("demographics.Population < 100")
...[output removed]
8 documents in set (0.00 sec)

The createIndex() method creates an index that you can define as nonunique or unique. Use the field() method to chain the fields that should be indexed. The execute() method is required to create or drop an index.

In MySQL, the _id field is equivalent to a primary key by default.

Add a Nonunique Index

To create a nonunique index, pass to the createIndex() method an index name. Duplicate index names are prohibited.

In the following example, the first parameter of the field() method specifies the Population field inside the demographics object and the next parameter indicates that the field should be indexed as an Integer numeric value. The last parameter indicates whether the field should require the NOT NULL constraint. If the value is false, the field can contain NULL values.

mysql-js> db.countryinfo.createIndex("pop").
field("demographics.Population", "INTEGER", false).execute()
Query OK (0.04 sec)
Add a Unique Index

To create a unique index, pass to the createIndex() method an index name and the mysqlx.IndexType.UNIQUE type. The following statement imports the mysqlx module, which is required to specify an index type:

mysql-js> var mysqlx=require('mysqlx');

Country "Name" is another common field in the countryinfo collection to index. In the following example, "Text(40)" represents the number of characters to index and true indicates that the field cannot contain any NULL values.

mysql-js> db.countryinfo.createIndex("name", mysqlx.IndexType.UNIQUE).
field("Name", "TEXT(40)", true).execute()
Query OK (0.04 sec)
Drop an Index

To drop an index, pass to the dropIndex() method the name of the index to drop. For example, you can drop the pop index as follows:

mysql-js> db.countryinfo.dropIndex("pop").execute()
Query OK (0.58 sec)
Related Information

3.4.5 Relational Tables

You can use MySQL Shell to manipulate not just JSON documents, but also relational tables.

In MySQL, each relational table is associated with a particular storage engine. The examples in this section use InnoDB tables in the world_x database.

Confirm the Schema

To show the value that is assigned to the schema variable, type db.

mysql-js> db
<Schema:world_x>

If the schema value is not Schema:world_x, then set the db variable as follows:

mysql-js> \use world_x
Schema `world_x` accessible through db.

Show All Tables

To display all relational tables in the world_x database, use the getTables() method on the schema object.

mysql-js> db.getTables()
{
    "city": <Table:city>,
    "country": <Table:country>,
    "countrylanguage": <Table:countrylanguage>
}

Basic Table Operations

Basic operations scoped by tables include:

Operation formDescription
db.name.insert()The insert() method inserts one or more records into the named table.
db.name.select()The select() method returns some or all records in the named table.
db.name.update()The update() method updates records in the named table.
db.name.delete()The delete() method deletes one or more records from the named table.

Related Information

3.4.5.1 Insert Records into Tables

You can use the insert() method with the values() method to insert records into an existing relational table. The insert() method accepts individual columns or all columns in the table. Use one or more values() methods to specify the values to be inserted.

Insert a Complete Record

To insert a complete record, pass to the insert() method all columns in the table. Then pass to the values() method one value for each column in the table. For example, to add a new record to the city table in the world_x database, insert the following record and press Enter twice.

mysql-js> db.city.insert("ID", "Name", "CountryCode", "District", "Info").
values(null, "Olympia", "USA", "Washington", '{"Population": 5000}')
Query OK, 1 item affected (0.01 sec)

The city table has five columns: ID, Name, CountryCode, District, and Info. Each value must match the data type of the column it represents.

Insert a Partial Record

The following example inserts values into the ID, Name, and CountryCode columns of the city table.

mysql-js> db.city.insert("ID", "Name", "CountryCode").
values(null, "Little Falls", "USA").values(null, "Happy Valley", "USA")
Query OK, 2 item affected (0.03 sec)

When you specify columns using the insert() method, the number of values must match the number of columns. In the previous example, you must supply three values to match the three columns specified.

Related Information

3.4.5.2 Select Tables

You can use the select() method to query for and return records from a table in a database. The X DevAPI provides additional methods to use with the select() method to filter and sort the returned records.

MySQL provides the following operators to specify search conditions: OR (||), AND (&&), XOR, IS, NOT, BETWEEN, IN, LIKE, !=, <>, >, >=, <, <=, &, |, <<, >>, +, -, *, /, ~, and %.

Select All Records

To issue a query that returns all records from an existing table, use the select() method without specifying search conditions. The following example selects all records from the city table in the world_x database.

Note

Limit the use of the empty select() method to interactive statements. Always use explicit column-name selections in your application code.

mysql-js> db.city.select()
+------+------------+-------------+------------+-------------------------+
| ID   | Name       | CountryCode | District   | Info                    |
+------+------------+-------------+------------+-------------------------+
|    1 | Kabul      | AFG         | Kabol      |{"Population": 1780000}  |
|    2 | Qandahar   | AFG         | Qandahar   |{"Population": 237500}   |
|    3 | Herat      | AFG         | Herat      |{"Population": 186800}   |
...    ...          ...           ...          ...
| 4079 | Rafah      | PSE         | Rafah      |{"Population": 92020}    |
+------+------- ----+-------------+------------+-------------------------+
4082 rows in set (0.01 sec)

An empty set (no matching records) returns the following information:

Empty set (0.00 sec)
Filter Searches

To issue a query that returns a set of table columns, use the select() method and specify the columns to return between square brackets. This query returns the Name and CountryCode columns from the city table.

mysql-js> db.city.select(["Name", "CountryCode"])
+-------------------+-------------+
| Name              | CountryCode |
+-------------------+-------------+
| Kabul             | AFG         |
| Qandahar          | AFG         |
| Herat             | AFG         |
| Mazar-e-Sharif    | AFG         |
| Amsterdam         | NLD         |
...                 ...
| Rafah             | PSE         |
| Olympia           | USA         |
| Little Falls      | USA         |
| Happy Valley      | USA         |
+-------------------+-------------+
4082 rows in set (0.00 sec)

To issue a query that returns rows matching specific search conditions, use the where() method to include those conditions. For example, the following example returns the names and country codes of the cities that start with the letter Z.

mysql-js> db.city.select(["Name", "CountryCode"]).where("Name like 'Z%'")
+-------------------+-------------+
| Name              | CountryCode |
+-------------------+-------------+
| Zaanstad          | NLD         |
| Zoetermeer        | NLD         |
| Zwolle            | NLD         |
| Zenica            | BIH         |
| Zagazig           | EGY         |
| Zaragoza          | ESP         |
| Zamboanga         | PHL         |
| Zahedan           | IRN         |
| Zanjan            | IRN         |
| Zabol             | IRN         |
| Zama              | JPN         |
| Zhezqazghan       | KAZ         |
| Zhengzhou         | CHN         |
...                 ...
| Zeleznogorsk      | RUS         |
+-------------------+-------------+
59 rows in set (0.00 sec)

You can separate a value from the search condition by using the bind() method. For example, instead of using "Name = 'Z%' " as the condition, substitute a named placeholder consisting of a colon followed by a name that begins with a letter, such as name. Then include the placeholder and value in the bind() method as follows:

mysql-js> db.city.select(["Name", "CountryCode"]).
              where("Name like :name").bind("name", "Z%")
Tip

Within a program, binding enables you to specify placeholders in your expressions, which are filled in with values before execution and can benefit from automatic escaping, as appropriate.

Always use binding to sanitize input. Avoid introducing values in queries using string concatenation, which can produce invalid input and, in some cases, can cause security issues.

Project Results

To issue a query using the AND operator, add the operator between search conditions in the where() method.

mysql-js> db.city.select(["Name", "CountryCode"]).
               where("Name like 'Z%' and CountryCode = 'CHN'")
+----------------+-------------+
| Name           | CountryCode |
+----------------+-------------+
| Zhengzhou      | CHN         |
| Zibo           | CHN         |
| Zhangjiakou    | CHN         |
| Zhuzhou        | CHN         |
| Zhangjiang     | CHN         |
| Zigong         | CHN         |
| Zaozhuang      | CHN         |
...              ...
| Zhangjiagang   | CHN         |
+----------------+-------------+
22 rows in set (0.01 sec)

To specify multiple conditional operators, you can enclose the search conditions in parenthesis to change the operator precedence. The following example demonstrates the placement of AND and OR operators.

mysql-js> db.city.select(["Name", "CountryCode"]).
where("Name like 'Z%' and (CountryCode = 'CHN' or CountryCode = 'RUS')")
+-------------------+-------------+
| Name              | CountryCode |
+-------------------+-------------+
| Zhengzhou         | CHN         |
| Zibo              | CHN         |
| Zhangjiakou       | CHN         |
| Zhuzhou           | CHN         |
...                 ...
| Zeleznogorsk      | RUS         |
+-------------------+-------------+
29 rows in set (0.01 sec)
Limit, Order, and Offset Results

You can apply the limit(), orderBy(), and offSet() methods to manage the number and order of records returned by the select() method.

To specify the number of records included in a result set, append the limit() method with a value to the select() method. For example, the following query returns the first five records in the country table.

mysql-js> db.country.select(["Code", "Name"]).limit(5)
+------+-------------+
| Code | Name        |
+------+-------------+
| ABW  | Aruba       |
| AFG  | Afghanistan |
| AGO  | Angola      |
| AIA  | Anguilla    |
| ALB  | Albania     |
+------+-------------+
5 rows in set (0.00 sec)

To specify an order for the results, append the orderBy() method to the select() method. Pass to the orderBy() method a list of one or more columns to sort by and, optionally, the descending (desc) or ascending (asc) attribute as appropriate. Ascending order is the default order type.

For example, the following query sorts all records by the Name column and then returns the first three records in descending order .

mysql-js> db.country.select(["Code", "Name"]).orderBy(["Name desc"]).limit(3)
+------+------------+
| Code | Name       |
+------+------------+
| ZWE  | Zimbabwe   |
| ZMB  | Zambia     |
| YUG  | Yugoslavia |
+------+------------+
3 rows in set (0.00 sec)

By default, the limit() method starts from the first record in the table. You can use the offset() method to change the starting record. For example, to ignore the first record and return the next three records matching the condition, pass to the offset() method a value of 1.

mysql-js> db.country.select(["Code", "Name"]).orderBy(["Name desc"]).limit(3).offset(1)
+------+------------+
| Code | Name       |
+------+------------+
| ZMB  | Zambia     |
| YUG  | Yugoslavia |
| YEM  | Yemen      |
+------+------------+
3 rows in set (0.00 sec)
Related Information

3.4.5.3 Update Tables

You can use the update() method to modify one or more records in a table. The update() method works by filtering a query to include only the records to be updated and then applying the operations you specify to those records.

To replace a city name in the city table, pass to the set() method the new city name. Then, pass to the where() method the city name to locate and replace. The following example replaces the city Peking with Beijing.

mysql-js> db.city.update().set("Name", "Beijing").where("Name = 'Peking'")
Query OK, 1 item affected (0.04 sec)

Use the select() method to verify the change.

mysql-js> db.city.select(["ID", "Name", "CountryCode", "District", "Info"]).where("Name = 'Beijing'")
+------+-----------+-------------+----------+-----------------------------+
| ID   | Name      | CountryCode | District | Info                        |
+------+-----------+-------------+----------+-----------------------------+
| 1891 | Beijing   | CHN         | Peking   | {"Population": 7472000}     |
+------+-----------+-------------+----------+-----------------------------+
1 row in set (0.00 sec)
Related Information

3.4.5.4 Delete Tables

You can use the delete() method to remove some or all records from a table in a database. The X DevAPI provides additional methods to use with the delete() method to filter and order the records to be deleted.

Delete Records Using Conditions

The example that follows passes search conditions to the delete() method. All records matching the condition will be deleted from the city table. In this example, one record matches the condition.

mysql-js> db.city.delete().where("Name = 'Olympia'")
Query OK, 1 item affected (0.01 sec)
Delete the First Record

To delete the first record in the city table, use the limit() method with a value of 1.

mysql-js> db.city.delete().limit(1)
Query OK, 1 item affected (0.02 sec)
Delete All Records in a Table

You can delete all records in a table. To do so, use the delete() method without specifying a search condition.

Caution

Use care when you delete records without specifying a search condition. This action will delete all records from the table.

Related Information

3.4.6 Documents in Tables

In MySQL, a table may contain traditional relational data, JSON values, or both. You can combine traditional data with JSON documents by storing the documents in columns having a native JSON data type.

Examples in this section use the city table in the world_x database.

city Table Description

The city table has five columns (or fields).

+---------------+------------+-------+-------+---------+------------------+
| Field         | Type       | Null  | Key   | Default | Extra            |
+---------------+------------+-------+-------+---------+------------------+
| ID            | int(11)    | NO    | PRI   | null    | auto_increment   |
| Name          | char(35)   | NO    |       |         |                  |
| CountryCode   | char(3)    | NO    |       |         |                  |
| District      | char(20)   | NO    |       |         |                  |
| Info          | json       | YES   |       | null    |                  |
+---------------+------------+-------+-------+---------+------------------+

Insert a Record

To insert a document into the column of a table, pass to the values() method a well-formed JSON document in the correct order. In the following example, a document is passed as the final value to be inserted into the Info column.

mysql-js> db.city.insert().
values(null, "San Francisco", "USA", "California", '{"Population":830000}')
Query OK, 1 item affected (0.01 sec)

Select a Record

You can issue a query with a search condition that evaluates document values in the expression.

mysql-js> db.city.select(["ID", "Name", "CountryCode", "District", "Info"]).
  where("CountryCode = :country and Info->'$.Population' > 1000000").
  bind('country', 'USA')
+------+----------------+-------------+----------------+-----------------------------+
| ID   | Name           | CountryCode | District       | Info                        |
+------+----------------+-------------+----------------+-----------------------------+
| 3793 | New York       | USA         | New York       | {"Population": 8008278}     |
| 3794 | Los Angeles    | USA         | California     | {"Population": 3694820}     |
| 3795 | Chicago        | USA         | Illinois       | {"Population": 2896016}     |
| 3796 | Houston        | USA         | Texas          | {"Population": 1953631}     |
| 3797 | Philadelphia   | USA         | Pennsylvania   | {"Population": 1517550}     |
| 3798 | Phoenix        | USA         | Arizona        | {"Population": 1321045}     |
| 3799 | San Diego      | USA         | California     | {"Population": 1223400}     |
| 3800 | Dallas         | USA         | Texas          | {"Population": 1188580}     |
| 3801 | San Antonio    | USA         | Texas          | {"Population": 1144646}     |
+------+----------------+-------------+----------------+-----------------------------+
9 rows in set (0.01 sec)

Related Information

3.5 Quick-Start Guide: MySQL Shell for Python

This quick-start guide provides instructions to begin prototyping database applications interactively with MySQL Shell. The guide includes the following topics:

  • Introduction to MySQL functionality, MySQL Shell, and the world_x database sample.

  • Operations to manage collections and documents.

  • Operations to manage relational tables.

  • Operations that apply to documents within tables.

Available Quick-Start Guides

Related Information

3.5.1 Introduction

The MySQL Shell for Python quick start provides a short but comprehensive introduction to the database functionality introduced with MySQL 5.7.12. This functionality includes the new X DevAPI, which offers a modern, integrative way to work with relational and document data, without requiring SQL knowledge from application developers.

In MySQL, tables are the native data storage container type and collections are stored internally using tables.

JSON Documents and Collections

A JSON document is a data structure composed of field/value pairs stored within a collection. The values of fields often contain other documents, arrays, and lists of documents.

{
    "GNP": .6,
    "IndepYear": 1967,
    "Name": "Sealand",
    "_id": "SEA",
    "demographics": {
        "LifeExpectancy": 79,
        "Population": 27
    },
    "geography": {
        "Continent": "Europe",
        "Region": "British Islands",
        "SurfaceArea": 193
    },
    "government": {
        "GovernmentForm": "Monarchy",
        "HeadOfState": "Michael Bates"
    }
}

Relational Tables

A table in MySQL enables you to store data organized in rows and columns. The structure of a table is defined by one or more columns with user-defined names and data types. Every row stored in the table has the same structure.

+------+------------------+-------------+-----------------+------------------------+
| ID   | Name             | CountryCode | District        | Info                   |
+------+------------------+-------------+-----------------+------------------------+
|    1 | Kabul            | AFG         | Kabol           |{"Population": 1780000} |
|    2 | Qandahar         | AFG         | Qandahar        |{"Population": 237500}  |
|    3 | Herat            | AFG         | Herat           |{"Population": 186800}  |
|    4 | Mazar-e-Sharif   | AFG         | Balkh           |{"Population": 127800}  |
|    5 | Amsterdam        | NLD         | Noord-Holland   |{"Population": 731200}  |
|    6 | Rotterdam        | NLD         | Zuid-Holland    |{"Population": 593321}  |
+------+------------------+-------------+-----------------+------------------------+

Related Information

3.5.2 Import Database Sample

The world_x database sample contains one JSON collection and a set of three relational tables:

  • Collection

    • countryinfo: Information about countries in the world.

  • Tables

    • country: Minimal information about countries of the world.

    • city: Information about some of the cities in those countries.

    • countrylanguage: Languages spoken in each country.

Requirements

You must install MySQL Shell and MySQL 5.7.12 or higher with the X Protocol enabled. For instructions, see Section 3.3, “Setting Up MySQL as a Document Store”.

Start the server before you load the world_x database for this guide.

Download world_x Database

To prepare the world_x database sample, follow these steps:

  1. Download world_x-db.zip.

  2. Extract the installation archive to a temporary location such as /tmp/. Unpacking the archive results in a single file named world_x.sql.

  3. Create the schema with the following command:

    mysqlsh -u root --sql < /tmp/world_x-db/world_x.sql
    Enter password: ****
    Records: 4079  Duplicates: 0  Warnings: 0
    
    Records: 239  Duplicates: 0  Warnings: 0
    
    Records: 239  Duplicates: 0  Warnings: 0
    
    Records: 984  Duplicates: 0  Warnings: 0
    

    Enter your password when prompted. A non-root account can be used as long as the account has privileges to create new databases.

    Replace /tmp/ with the path to the world_x.sql file on your system.

Related Information

3.5.3 MySQL Shell

MySQL Shell is a unified scripting interface to MySQL Server. It supports scripting in JavaScript and Python. JavaScript is the default processing mode. In most cases, you need an account to connect to the local MySQL server instance.

Start MySQL Shell

After you have installed and started MySQL server, connect MySQL Shell to the server instance. By default, MySQL Shell connects using the X Protocol.

On the same system where the server instance is running, open a terminal window and start MySQL Shell with the following command:

mysqlsh -u name --py world_x
Creating an X Session to name@localhost:33060/world_x
Enter password: ****

You may need to specify the path as appropriate.

In addition:

  • name represents the user name of your MySQL account.

  • MySQL Shell prompts you for your password.

  • The --py option starts MySQL Shell in Python mode. If you omit --py, MySQL Shell starts in JavaScript mode.

  • The default schema for this session is the world_x database. For instructions on setting up the world_x database sample, see Section 3.5.2, “Import Database Sample”.

The mysql-py> prompt indicates that the active language for this session is Python.

mysql-py>

When you run mysqlsh without the host argument, MySQL Shell attempts to connect to the server instance running on the localhost interface on port 33060. To specify a different host or port number, as well as other options, see the option descriptions at Section 5.5.7, “mysqlsh — The MySQL Shell”.

MySQL Shell supports input-line editing as follows:

  • left-arrow and right-arrow keys move horizontally within the current input line.

  • up-arrow and down-arrow keys move up and down through the set of previously entered lines.

  • Backspace deletes the character before the cursor and typing new characters enters them at the cursor position.

  • Enter enters the current input line.

Get Help for MySQL Shell

Type mysqlsh --help at the prompt of your command interpreter for a list of command-line options.

mysqlsh --help

Type \help at the MySQL Shell prompt for a list of available commands and their descriptions.

mysql-py> \help

Type \help followed by a command name for detailed help about an individual MySQL Shell command. For example, to view help on the \connect command, type:

mysql-py> \help \connect

Quit MySQL Shell

To quit MySQL Shell, type the following command:

mysql-py> \quit

Related Information

3.5.4 Documents and Collections

In MySQL, collections contain JSON documents that you can add, find, update, and remove. Collections are containers within a schema that you create, list, and drop.

The examples in this section use the countryinfo collection in the world_x database. For instructions on setting up the world_x database sample, see Section 3.5.2, “Import Database Sample”.

Documents

In MySQL, documents are represented as JSON objects. Internally, they are stored in an efficient binary format that enables fast lookups and updates.

  • Simple document format for Python:

    {"field1": "value", "field2" : 10, "field 3": null}
    

An array of documents consists of a set of documents separated by commas and enclosed within [ and ] characters.

  • Simple array of documents for Python:

    [{"Name": "Aruba", "_id": "ABW"}, {"Name": "Angola", "_id": "AGO"}]
    

MySQL supports the following Python value types in JSON documents:

  • numbers (integer and floating point)

  • strings

  • boolean (False and True)

  • None

  • arrays of more JSON values

  • nested (or embedded) objects of more JSON values

Collections

Collections are containers for documents that share a purpose and possibly share one or more indexes. Each collection has a unique name and exists within a single schema.

The term schema is equivalent to a database, which means a group of database objects (as opposed to relational schema used to enforce structure and constraints over data). A schema does not enforce conformity on the documents in a collection.

In this quick-start guide:

  • Basic objects include:

    Object formDescription
    dbdb is a global variable assigned to the current active schema that you specified on the command line. You can type db in MySQL Shell to print a description of the object, which in this case will be the name of the schema it represents.
    db.get_collections()db.get_collections() holds a list of collections in the schema. Use the list to get references to collection objects, iterate over them, and so on.
  • Basic operations scoped by collections include:

    Operation formDescription
    db.name.add()The add() method inserts one document or a list of documents into the named collection.
    db.name.find()The find() method returns some or all documents in the named collection.
    db.name.modify()The modify() method updates documents in the named collection.
    db.name.remove()The remove() method deletes one document or a list of documents from the named collection.

Related Information

3.5.4.1 Create, List, and Drop Collections

In MySQL Shell, you can create new collections, get a list of the existing collections in a schema, and remove an existing collection from a schema. Collection names are case-sensitive and each collection name must be unique.

Confirm the Schema

To show the value that is assigned to the schema variable, type db.

mysql-py> db
<Schema:world_x>

If the schema value is not Schema:world_x, then set the db variable as follows:

mysql-py> \use world_x
Schema `world_x` accessible through db.
Create a Collection

To create a new collection in an existing schema, use the create_collection() method.

The following example creates in the world_x database a collection called flags. The method returns a collection object.

mysql-py> db.create_collection("flags")
<Collection:flags>
List Collections

To display all collections in the world_x database, use the get_collections() method on the schema object. Collections returned by the server appear between brackets.

mysql-py> db.get_collections()
[
    <Collection:countryinfo>,
    <Collection:flags>
]
Drop a Collection

To drop an existing collection from a database, use the drop_collection() method on the session object. For example, to drop the flags collection from the world_x database, type:

mysql-py> session.drop_collection("world_x", "flags")
Query OK (0.04 sec)
Related Information

3.5.4.2 Add Documents

You can use the add() method to insert one document or a list documents into an existing collection using MySQL Shell. All examples in this section use the countryinfo collection.

Confirm the Schema

To show the value that is assigned to the schema variable, typedb.

mysql-py> db
<Schema:world_x>

If the schema value is not Schema:world_x, then set the db variable as follows:

mysql-py> \use world_x
Schema `world_x` accessible through db.
Add a Document

Insert the following document into the countryinfo collection. Press Enter twice to insert the document.

mysql-py> db.countryinfo.add(
 {
    "GNP": .6,
    "IndepYear": 1967,
    "Name": "Sealand",
    "_id": "SEA",
    "demographics": {
        "LifeExpectancy": 79,
        "Population": 27
    },
    "geography": {
        "Continent": "Europe",
        "Region": "British Islands",
        "SurfaceArea": 193
    },
    "government": {
        "GovernmentForm": "Monarchy",
        "HeadOfState": "Michael Bates"
    }
  }
)
Query OK, 1 item affected (0.02 sec)

The method returns the status of the operation.

Each document requires an identifier field called _id. The value of the _id field must be unique among all documents in the same collection. If the document passed to the add() method does not contain the _id field, MySQL Shell automatically inserts a field into the document and sets the value to a generated universal unique identifier (UUID).

Related Information

3.5.4.3 Find Documents

You can use the find() method to query for and return documents from a collection in a database. MySQL Shell provides additional methods to use with the find() method to filter and sort the returned documents.

MySQL provides the following operators to specify search conditions: OR (||), AND (&&), XOR, IS, NOT, BETWEEN, IN, LIKE, !=, <>, >, >=, <, <=, &, |, <<, >>, +, -, *, /, ~, and %.

Find All Documents in a Collection

To return all documents in a collection, use the find() method without specifying search conditions. For example, the following operation returns all documents in the countryinfo collection.

mysql-py> db.countryinfo.find()
[
     {
          "GNP": 828,
          "IndepYear": null,
          "Name": "Aruba",
          "_id": "ABW",
          "demographics": {
              "LifeExpectancy": 78.4000015258789,
              "Population": 103000
          },
          "geography": {
              "Continent": "North America",
              "Region": "Caribbean",
              "SurfaceArea": 193
          },
          "government": {
              "GovernmentForm": "Nonmetropolitan Territory of The Netherlands",
              "HeadOfState": "Beatrix"
          }
          ...
      }
 ]
240 documents in set (0.00 sec)

The method produces results that contain operational information in addition to all documents in the collection.

An empty set (no matching documents) returns the following information:

Empty set (0.00 sec) 
Filter Searches

You can include search conditions with the find() method. The syntax for expressions that form a search condition is the same as that of traditional MySQL. You must enclose all expressions in quotes.

All examples in this section use the countryinfo collection in the world_x database. For the sake of brevity, some of the examples do not display output.

A simple search condition consists of the _id field and unique identifier of a document. The following example returns a single document matching the identifier string:

mysql-py> db.countryinfo.find("_id = 'AUS'")
[
    {
        "GNP": 351182,
        "IndepYear": 1901,
        "Name": "Australia",
        "_id": "AUS",
        "demographics": {
            "LifeExpectancy": 79.80000305175781,
            "Population": 18886000
        },
        "geography": {
            "Continent": "Oceania",
            "Region": "Australia and New Zealand",
            "SurfaceArea": 7741220
        },
        "government": {
            "GovernmentForm": "Constitutional Monarchy, Federation",
            "HeadOfState": "Elisabeth II"
        }
    }
]
1 document in set (0.01 sec)

The following example searches for all countries that have a GNP higher than $500 billion. The countryinfo collection measures GNP in units of million.

mysql-py> db.countryinfo.find("GNP > 500000")
...[output removed]
10 documents in set (0.00 sec)

The Population field in the following query is embedded within the demographics object. To access the embedded field, use a period between demographics and Population to identify the relationship. Document and field names are case sensitive.

mysql-py> db.countryinfo.find("GNP > 500000 and demographics.Population < 100000000")
...[output removed]
6 documents in set (0.00 sec)

Arithmetic operators in the following expression are used to query for countries with a GNP per capita higher than $30000. Search conditions can include arithmetic operators and most MySQL functions.

Note

Seven documents in the countryinfo collection have a population value of zero. Warning messages appear at the end of the output.

mysql-py> db.countryinfo.find("GNP*1000000/demographics.Population > 30000")
...[output removed]
9 documents in set, 7 warnings (0.00 sec)
Warning (Code 1365): Division by 0
Warning (Code 1365): Division by 0
Warning (Code 1365): Division by 0
Warning (Code 1365): Division by 0
Warning (Code 1365): Division by 0
Warning (Code 1365): Division by 0
Warning (Code 1365): Division by 0

You can separate a value from the search condition by using the bind() method. For example, instead of specifying a hard-coded country name as the condition, substitute a named placeholder consisting of a colon followed by a name that begins with a letter, such as country. Then include the placeholder and value in the bind() method as follows:

mysql-py> db.countryinfo.find("Name = :country").bind("country", "Italy")
[
    {
        "GNP": 1161755,
        "IndepYear": 1861,
        "Name": "Italy",
        "_id": "ITA",
        "demographics": {
            "LifeExpectancy": 79,
            "Population": 57680000
        },
        "geography": {
            "Continent": "Europe",
            "Region": "Southern Europe",
            "SurfaceArea": 301316
        },
        "government": {
            "GovernmentForm": "Republic",
            "HeadOfState": "Carlo Azeglio Ciampi"
        }
    }
]
1 document in set (0.01 sec)
Tip

Within a program, binding enables you to specify placeholders in your expressions, which are filled in with values before execution and can benefit from automatic escaping, as appropriate.

Always use binding to sanitize input. Avoid introducing values in queries using string concatenation, which can produce invalid input and, in some cases, can cause security issues.

Project Results

You can return specific fields of a document, instead of returning all the fields. The following example returns the GNP and Name fields of all documents in the countryinfo collection matching the search conditions.

Use the fields() method to pass the list of fields to return.

mysql-py> db.countryinfo.find("GNP > 5000000").fields(["GNP", "Name"])
[
    {
        "GNP": 8510700,
        "Name": "United States"
    }
]
1 document in set (0.00 sec)
 

In addition, you can alter the returned documents—adding, renaming, nesting and even computing new field values—with an expression that describes the document to return.

First, use the following statement to import the mysqlx module:

mysql-py> import mysqlx

Next, alter the names of the fields with the following expression to return only two documents.

mysql-py> db.countryinfo.find().\
fields(mysqlx.expr('{"Name": upper(Name), "GNPPerCapita": GNP*1000000/demographics.Population}')).\
limit(2)
[
    {
        "GNPPerCapita": 8038.834951456311,
        "Name": "ARUBA"
    },
    {
        "GNPPerCapita": 263.0281690140845,
        "Name": "AFGHANISTAN"
    }
]
2 documents in set (0.00 sec)
Limit, Sort, and Skip Results

You can apply the limit(), sort(), and skip() methods to manage the number and order of documents returned by the find() method.

To specify the number of documents included in a result set, append the limit() method with a value to the find() method. The following query returns the first five documents in the countryinfo collection.

mysql-py> db.countryinfo.find().limit(5)
... [output removed]
5 documents in set (0.00 sec)

To specify an order for the results, append the sort() method to the find() method. Pass to the sort() method a list of one or more fields to sort by and, optionally, the descending (desc) or ascending (asc) attribute as appropriate. Ascending order is the default order type.

For example, the following query sorts all documents by the IndepYear field and then returns the first eight documents in descending order.

mysql-py> db.countryinfo.find().sort(["IndepYear desc"]).limit(8)
... [output removed]
8 documents in set (0.00 sec)

By default, the limit() method starts from the first document in the collection. You can use the skip() method to change the starting document. For example, to ignore the first document and return the next eight documents matching the condition, pass to the skip() method a value of 1.

mysql-py> db.countryinfo.find().sort(["IndepYear desc"]).limit(8).skip(1)
... [output removed]
8 documents in set (0.00 sec)
Related Information

3.5.4.4 Modify Documents

You can use the modify() method to update one or more documents in a collection. The X DevAPI provides additional methods for use with the modify() method to:

  • Set and unset fields within documents.

  • Append, insert, and delete arrays.

  • Bind, limit, and sort the documents to be modified.

Set and Unset Fields

The modify() method works by filtering a collection to include only the documents to be modified and then applying the operations that you specify to those documents.

In the following example, the modify() method uses the search condition to identify the document to change and then the set() method replaces two values within the nested demographics object.

mysql-py> db.countryinfo.modify("_id = 'SEA'").\
set("demographics", {"LifeExpectancy": 78, "Population": 28})
Query OK, 1 item affected (0.04 sec)

After you modify a document, use the find() method to verify the change.

To remove content from a document, use the modify() and unset() methods. For example, the following query removes the GNP from a document that matches the search condition.

mysql-py> db.countryinfo.modify("Name = 'Sealand'").unset("GNP")
Query OK, 1 item affected (0.01 sec)   

Use the find() method to verify the change.

mysql-py> db.countryinfo.find("Name = 'Sealand'")
[
    {
        "IndepYear": 1967,
        "Name": "Sealand",
        "_id": "SEA",
        "demographics": {
            "LifeExpectancy": 78,
            "Population": 28
        },
        "geography": {
            "Continent": "Europe",
            "Region": "British Islands",
            "SurfaceArea": 193
        },
        "government": {
            "GovernmentForm": "Monarchy",
            "HeadOfState": "Michael Bates"
        }
    }
]
1 document in set (0.00 sec)
Append, Insert, and Delete Arrays

To append an element to an array field, or insert, or delete elements in an array, use the array_append(), array_insert(), or array_delete() methods. The following examples modify the countryinfo collection to enable tracking of international airports.

The first example uses the modify() and set() methods to create a new Airports field in all documents.

Caution

Use care when you modify documents without specifying a search condition. This action will modify all documents in the collection.

mysql-py> db.countryinfo.modify().set("Airports", [])
Query OK, 240 items affected (0.07 sec)

With the Airports field added, the next example uses the array_append() method to add a new airport to one of the documents. $.Airports in the following example represents the Airports field of the current document.

mysql-py> db.countryinfo.modify("Name = 'France'").array_append("$.Airports", "ORY")
Query OK, 1 item affected (0.02 sec)

Use db.countryinfo.find("Name = 'France'") to see the change.

To insert an element at a different position in the array, use the array_insert() method to specify which index to insert in the path expression. In this case, the index is 0, or the first element in the array.

mysql-py> db.countryinfo.modify("Name = 'France'").array_insert("$.Airports[0]", "CDG")
Query OK, 1 item affected (0.04 sec)

To delete an element from the array, you must pass to the array_delete() method the index of the element to be deleted.

mysql-py> db.countryinfo.modify("Name = 'France'").array_delete("$.Airports[1]")
Query OK, 1 item affected (0.03 sec)
Related Information

3.5.4.5 Remove Documents

You can use the remove() method to delete some or all documents from a collection in a database. The X DevAPI provides additional methods for use with the remove() method to filter and sort the documents to be removed.

Remove Documents Using Conditions

The example that follows passes a search condition to the remove() method. All documents matching the condition will be removed from the countryinfo collection. In this example, one document matches the condition.

mysql-py> db.countryinfo.remove("_id = 'SEA'")
Query OK, 1 item affected (0.02 sec)
Remove the First Document

To remove the first document in the countryinfo collection, use the limit() method with a value of 1.

mysql-py> db.countryinfo.remove().limit(1)
Query OK, 1 item affected (0.03 sec)
Remove the Last Document in an Order

The following example removes the last document in the countryinfo collection by country name.

mysql-py> db.countryinfo.remove().sort(["Name desc"]).limit(1)
Query OK, 1 item affected (0.02 sec)
Remove All Documents in a Collection

You can remove all documents in a collection. To do so, use the remove() method without specifying a search condition.

Caution

Use care when you remove documents without specifying a search condition. This action will delete all documents from the collection.

Related Information

3.5.4.6 Create and Drop Indexes

Indexes are used to find documents with specific field values quickly. Without an index, MySQL must begin with the first document and then read through the entire collection to find the relevant fields. The larger the collection, the more this costs. If a collection is large and queries on a specific field are common, then consider creating an index on a specific field inside a document.

For example, the following query will perform better with an index:

mysql-js> db.countryinfo.find("demographics.Population < 100")
...[output removed]
8 documents in set (0.00 sec)

The create_index() method creates an index that you can define as nonunique or unique. Use the field() method to chain the fields that should be indexed. The execute() method is required to create or drop an index.

In MySQL, the _id field is equivalent to a primary key by default.

Add a Nonunique Index

To create a nonunique index, pass to the create_index() method an index name. Duplicate index names are prohibited.

In the following example, the first parameter of the field() method specifies the Population field inside the demographics object and the next parameter indicates that the field should be indexed as an Integer numeric value. The last parameter indicates whether the field should require the NOT NULL constraint. If the value is False, the field can contain NULL values.

mysql-js> db.countryinfo.create_index("pop").\
field("demographics.Population", "INTEGER", False).execute()
Query OK (0.04 sec)
Add a Unique Index

To create a unique index, pass to the create_index() method an index name and the mysqlx.IndexType.UNIQUE type. The following statement imports the mysqlx module, which is required to specify an index type:

mysql-js> import mysqlx

Country "Name" is another common field in the countryinfo collection to index. In the following example, "Text(40)" represents the number of characters to index and True indicates that the field cannot contain any NULL values.

mysql-js> db.countryinfo.create_index("name", mysqlx.IndexType.UNIQUE).\
field("Name", "TEXT(40)", True).execute()
Query OK (0.04 sec)
Drop an Index

To drop an index, pass to the drop_index() method the name of the index to drop. For example, you can drop the pop index as follows:

mysql-js> db.countryinfo.drop_index("pop").execute()
Query OK (0.58 sec)
Related Information

3.5.5 Relational Tables

You can use MySQL Shell to manipulate not just JSON documents, but also relational tables.

In MySQL, each relational table is associated with a particular storage engine. The examples in this section use InnoDB tables in the world_x database.

Confirm the Schema

To show the value that is assigned to the schema variable, type db.

mysql-py> db
<Schema:world_x>

If the schema value is not the Schema:world_x database, then set the db variable as follows:

mysql-py> \use world_x
Schema `world_x` accessible through db.

Show All Tables

To display all relational tables in the world_x database, use the get_tables() method on the schema object.

mysql-py> db.get_tables()
{
    "city": <Table:city>,
    "country": <Table:country>,
    "countrylanguage": <Table:countrylanguage>
}

Basic Table Operations

Basic operations scoped by tables include:

Operation formDescription
db.name.insert()The insert() method inserts one or more records into the named table.
db.name.select()The select() method returns some or all records in the named table.
db.name.update()The update() method updates records in the named table.
db.name.delete()The delete() method deletes one or more records from the named table.

Related Information

3.5.5.1 Insert Records into Tables

You can use the insert() method with the values() method to insert records into an existing relational table. The insert() method accepts individual columns or all columns in the table. Use one or more values() methods to specify the values to be inserted.

Insert a Complete Record

To insert a complete record, pass to the insert() method all columns in the table. Then pass to the values() method one value for each column. For example, to add a new record to the city table in the world_x database, insert the following record and press Enter twice.

mysql-py> db.city.insert("ID", "Name", "CountryCode", "District", "Info").\
values(None, "Olympia", "USA", "Washington", '{"Population": 5000}')
Query OK, 1 item affected (0.01 sec)

The city table has five columns: ID, Name, CountryCode, District, and Info. Each value must match the data type of the column it represents.

Insert a Partial Record

The following example inserts values into the ID, Name, and CountryCode columns of the city table.

mysql-py> db.city.insert("ID", "Name", "CountryCode").\
values(None, "Little Falls", "USA").values(None, "Happy Valley", "USA")
Query OK, 2 item affected (0.03 sec)

When you specify columns using the insert() method, the number of values must match the number of columns. In the previous example, you must supply three values to match the three columns specified.

Related Information

3.5.5.2 Select Tables

You can use the select() method to query for and return records from a table in a database. The X DevAPI provides additional methods to use with the select() method to filter and sort the returned records.

MySQL provides the following operators to specify search conditions: OR (||), AND (&&), XOR, IS, NOT, BETWEEN, IN, LIKE, !=, <>, >, >=, <, <=, &, |, <<, >>, +, -, *, /, ~, and %.

Select All Records

To issue a query that returns all records from an existing table, use the select() method without specifying search conditions. The following example selects all records from the city table in the world_x database.

Note

Limit the use of the empty select() method to interactive statements. Always use explicit column-name selections in your application code.

mysql-py> db.city.select()
+------+------------+-------------+------------+-------------------------+
| ID   | Name       | CountryCode | District   | Info                    |
+------+------------+-------------+------------+-------------------------+
|    1 | Kabul      | AFG         | Kabol      |{"Population": 1780000}  |
|    2 | Qandahar   | AFG         | Qandahar   |{"Population": 237500}   |
|    3 | Herat      | AFG         | Herat      |{"Population": 186800}   |
...    ...          ...           ...          ...
| 4079 | Rafah      | PSE         | Rafah      |{"Population": 92020}    |
+------+------- ----+-------------+------------+-------------------------+
4082 rows in set (0.01 sec)

An empty set (no matching records) returns the following information:

Empty set (0.00 sec)
Filter Searches

To issue a query that returns a set of table columns, use the select() method and specify the columns to return between square brackets. This query returns the Name and CountryCode columns from the city table.

mysql-py> db.city.select(["Name", "CountryCode"])
+-------------------+-------------+
| Name              | CountryCode |
+-------------------+-------------+
| Kabul             | AFG         |
| Qandahar          | AFG         |
| Herat             | AFG         |
| Mazar-e-Sharif    | AFG         |
| Amsterdam         | NLD         |
...                 ...
| Rafah             | PSE         |
| Olympia           | USA         |
| Little Falls      | USA         |
| Happy Valley      | USA         |
+-------------------+-------------+
4082 rows in set (0.00 sec)

To issue a query that returns rows matching specific search conditions, use the where() method to include those conditions. For example, the following example returns the names and country codes of the cities that start with the letter Z.

mysql-py> db.city.select(["Name", "CountryCode"]).where("Name like 'Z%'")
+-------------------+-------------+
| Name              | CountryCode |
+-------------------+-------------+
| Zaanstad          | NLD         |
| Zoetermeer        | NLD         |
| Zwolle            | NLD         |
| Zenica            | BIH         |
| Zagazig           | EGY         |
| Zaragoza          | ESP         |
| Zamboanga         | PHL         |
| Zahedan           | IRN         |
| Zanjan            | IRN         |
| Zabol             | IRN         |
| Zama              | JPN         |
| Zhezqazghan       | KAZ         |
| Zhengzhou         | CHN         |
...                 ...
| Zeleznogorsk      | RUS         |
+-------------------+-------------+
59 rows in set (0.00 sec)

You can separate a value from the search condition by using the bind() method. For example, instead of using "Name = 'Z%' " as the condition, substitute a named placeholder consisting of a colon followed by a name that begins with a letter, such as name. Then include the placeholder and value in the bind() method as follows:

mysql-py> db.city.select(["Name", "CountryCode"]).\
              where("Name like :name").bind("name", "Z%")
Tip

Within a program, binding enables you to specify placeholders in your expressions, which are filled in with values before execution and can benefit from automatic escaping, as appropriate.

Always use binding to sanitize input. Avoid introducing values in queries using string concatenation, which can produce invalid input and, in some cases, can cause security issues.

Project Results

To issue a query using the AND operator, add the operator between search conditions in the where() method.

mysql-py> db.city.select(["Name", "CountryCode"]).\
               where("Name like 'Z%' and CountryCode = 'CHN'")
+----------------+-------------+
| Name           | CountryCode |
+----------------+-------------+
| Zhengzhou      | CHN         |
| Zibo           | CHN         |
| Zhangjiakou    | CHN         |
| Zhuzhou        | CHN         |
| Zhangjiang     | CHN         |
| Zigong         | CHN         |
| Zaozhuang      | CHN         |
...              ...
| Zhangjiagang   | CHN         |
+----------------+-------------+
22 rows in set (0.01 sec)

To specify multiple conditional operators, you can enclose the search conditions in parenthesis to change the operator precedence. The following example demonstrates the placement of AND and OR operators.

mysql-py> db.city.select(["Name", "CountryCode"]).\
where("Name like 'Z%' and (CountryCode = 'CHN' or CountryCode = 'RUS')")
+-------------------+-------------+
| Name              | CountryCode |
+-------------------+-------------+
| Zhengzhou         | CHN         |
| Zibo              | CHN         |
| Zhangjiakou       | CHN         |
| Zhuzhou           | CHN         |
...                 ...
| Zeleznogorsk      | RUS         |
+-------------------+-------------+
29 rows in set (0.01 sec)
Limit, Order, and Offset Results

You can apply the limit(), order_by(), and offset() methods to manage the number and order of records returned by the select() method.

To specify the number of records included in a result set, append the limit() method with a value to the select() method. For example, the following query returns the first five records in the country table.

mysql-py> db.country.select(["Code", "Name"]).limit(5)
+------+-------------+
| Code | Name        |
+------+-------------+
| ABW  | Aruba       |
| AFG  | Afghanistan |
| AGO  | Angola      |
| AIA  | Anguilla    |
| ALB  | Albania     |
+------+-------------+
5 rows in set (0.00 sec)

To specify an order for the results, append the order_by() method to the select() method. Pass to the order_by() method a list of one or more columns to sort by and, optionally, the descending (desc) or ascending (asc) attribute as appropriate. Ascending order is the default order type.

For example, the following query sorts all records by the Name column and then returns the first three records in descending order .

mysql-py> db.country.select(["Code", "Name"]).order_by(["Name desc"]).limit(3)
+------+------------+
| Code | Name       |
+------+------------+
| ZWE  | Zimbabwe   |
| ZMB  | Zambia     |
| YUG  | Yugoslavia |
+------+------------+
3 rows in set (0.00 sec)

By default, the limit() method starts from the first record in the table. You can use the offset() method to change the starting record. For example, to ignore the first record and return the next three records matching the condition, pass to the offset() method a value of 1.

mysql-py> db.country.select(["Code", "Name"]).order_by(["Name desc"]).limit(3).offset(1)
+------+------------+
| Code | Name       |
+------+------------+
| ZMB  | Zambia     |
| YUG  | Yugoslavia |
| YEM  | Yemen      |
+------+------------+
3 rows in set (0.00 sec)
Related Information

3.5.5.3 Update Tables

You can use the update() method to modify one or more records in a table. The update() method works by filtering a query to include only the records to be updated and then applying the operations you specify to those records.

To replace a city name in the city table, pass to the set() method the new city name. Then, pass to the where() method the city name to locate and replace. The following example replaces the city Peking with Beijing.

mysql-py> db.city.update().set("Name", "Beijing").where("Name = 'Peking'")
Query OK, 1 item affected (0.04 sec)

Use the select() method to verify the change.

mysql-py> db.city.select(["ID", "Name", "CountryCode", "District", "Info"]).where("Name = 'Beijing'")
+------+-----------+-------------+----------+-----------------------------+
| ID   | Name      | CountryCode | District | Info                        |
+------+-----------+-------------+----------+-----------------------------+
| 1891 | Beijing   | CHN         | Peking   | {"Population": 7472000}     |
+------+-----------+-------------+----------+-----------------------------+
1 row in set (0.00 sec)
Related Information

3.5.5.4 Delete Tables

You can use the delete() method to remove some or all records from a table in a database. The X DevAPI provides additional methods to use with the delete() method to filter and order the records to be deleted.

Delete Records Using Conditions

The example that follows passes search conditions to the delete() method. All records matching the condition will be deleted from the city table. In this example, one record matches the condition.

mysql-py> db.city.delete().where("Name = 'Olympia'")
Query OK, 1 item affected (0.01 sec)
Delete the First Record

To delete the first record in the city table, use the limit() method with a value of 1.

mysql-py> db.city.delete().limit(1)
Query OK, 1 item affected (0.02 sec)
Delete All Records in a Table

You can delete all records in a table. To do so, use the delete() method without specifying a search condition.

Caution

Use care when you delete records without specifying a search condition. This action will delete all records from the table.

Related Information

3.5.6 Documents in Tables

In MySQL, a table may contain traditional relational data, JSON values, or both. You can combine traditional data with JSON documents by storing the documents in columns having a native JSON data type.

Examples in this section use the city table in the world_x database.

city Table Description

The city table has five columns (or fields).

+---------------+------------+-------+-------+---------+------------------+
| Field         | Type       | Null  | Key   | Default | Extra            |
+---------------+------------+-------+-------+---------+------------------+
| ID            | int(11)    | NO    | PRI   | null    | auto_increment   |
| Name          | char(35)   | NO    |       |         |                  |
| CountryCode   | char(3)    | NO    |       |         |                  |
| District      | char(20)   | NO    |       |         |                  |
| Info          | json       | YES   |       | null    |                  |
+---------------+------------+-------+-------+---------+------------------+

Insert a Record

To insert a document into the column of a table, pass to the values() method a well-formed JSON document in the correct order. In the following example, a document is passed as the final value to be inserted into the Info column.

 mysql-py> db.city.insert().\
values(None, "San Francisco", "USA", "California", '{"Population":830000}')
Query OK, 1 item affected (0.01 sec)

Select a Record

You can issue a query with a search condition that evaluates document values in the expression.

mysql-py> db.city.select(["ID", "Name", "CountryCode", "District", "Info"]).\
  where("CountryCode = :country and Info->'$.Population' > 1000000").\
  bind('country', 'USA')
+------+----------------+-------------+----------------+-----------------------------+
| ID   | Name           | CountryCode | District       | Info                        |
+------+----------------+-------------+----------------+-----------------------------+
| 3793 | New York       | USA         | New York       | {"Population": 8008278}     |
| 3794 | Los Angeles    | USA         | California     | {"Population": 3694820}     |
| 3795 | Chicago        | USA         | Illinois       | {"Population": 2896016}     |
| 3796 | Houston        | USA         | Texas          | {"Population": 1953631}     |
| 3797 | Philadelphia   | USA         | Pennsylvania   | {"Population": 1517550}     |
| 3798 | Phoenix        | USA         | Arizona        | {"Population": 1321045}     |
| 3799 | San Diego      | USA         | California     | {"Population": 1223400}     |
| 3800 | Dallas         | USA         | Texas          | {"Population": 1188580}     |
| 3801 | San Antonio    | USA         | Texas          | {"Population": 1144646}     |
+------+----------------+-------------+----------------+-----------------------------+
9 rows in set (0.01 sec)

Related Information

3.6 Quick-Start Guide: MySQL for Visual Studio

This section explains how to use MySQL Shell to script a server using MySQL for Visual Studio.

Introduction

MySQL for Visual Studio provides access to MySQL objects and data without forcing developers to leave Visual Studio. Designed and developed as a Visual Studio package, MySQL for Visual Studio integrates directly into Server Explorer providing a seamless experience for setting up new connections and working with database objects.

The following MySQL for Visual Studio features are available as of version 2.0.2:

  • JavaScript and Python code editors, where scripts in those languages can be executed to query data from a MySQL database.

  • Better integration with the Server Explorer to open MySQL, JavaScript, and Python code editors directly from a connected MySQL instance.

  • A newer user interface for displaying query results, where different views are presented from result sets returned by a MySQL Server like:

    • Multiple tabs for each result set returned by an executed query.

    • Results view, where the information can be seen in grid, tree, or text representation for JSON results.

    • Field types view, where information about the columns of a result set is shown, such as names, data types, character sets, and more.

    • Query statistics view, displaying information about the executed query such as execution times, processed rows, index and temporary tables usage, and more.

    • Execution plan view, displaying an explanation of the query execution done internally by the MySQL Server.

Getting Started

The requirements are MySQL for Visual Studio 2.0.2 or higher, and Visual Studio 2010 or higher. X DevAPI support requires MySQL Server 5.7.12 or higher with the X plugin enabled.

Opening a Code Editor

Before opening a code editor that can execute queries against a MySQL server, a connection needs to be established:

  1. Open the Server Explorer pane through the View menu, or with Control + W, K.

  2. Right-click on the Data Connections node, select Add Connection....

  3. In the Add Connection dialog, make sure the MySQL Data Provider is being used and fill in all the information.

    Note

    To enter the port number, click Advanced... and set the Port among the list of connection properties.

  4. Click Test Connection to ensure you have a valid connection, then click OK.

  5. Right-click your newly created connection, select New MySQL Script and then the language for the code editor you want to open.

For existing MySQL connections, to create a new editor you need only to do the last step.

Using the Code Editor

The MySQL script editors have a toolbar at the top where information about the session is displayed, along with the actions that can be executed.

Note

Note the first two buttons in the toolbar represent a way to connect or disconnect from a MySQL server. If the editor was opened from the Server Explorer, the connection will be already established for the new editor window.

The third button is the Run button, the script contained in the editor window is executed by clicking it and results from the script execution are displayed below the script window.

Note

Some commands in the MySQL Shell can be executed without appending execute() while in interactive mode. In MySQL for Visual Studio, these commands do require execute(). In other words, append ".execute()" to execute commands.

3.7 X Plugin

X Plugin extends MySQL Server to be able to function as a document store. Running X Plugin enables MySQL Server to communicate with clients using the X Protocol, which is designed to expose the ACID compliant storage abilities of MySQL as a document store. Documents are stored in JSON format and enable schema-less storage. Using the X DevAPI you can use a NoSQL-like syntax to execute Create, Read, Update, Delete (CRUD) operations against these documents.

This section explains how to configure and monitor the X Plugin in MySQL Server.

3.7.1 Using Secure Connections with X Plugin

This section explains how to configure X Plugin to use secure connections. For more background information, see Section 7.4, “Using Secure Connections”.

X Plugin has is own SSL settings which can differ from those used with MySQL Server. This means that X Plugin can be configured with a different SSL key, certificate, and certificate authorities file than MySQL Server. Similarly, X Plugin has its own SSL status variables calculated independently from the MySQL Server SSL related variables. By default the X Plugin SSL configuration is taken from the mysqlx_ssl_* variables, described at Section 3.7.2.2, “X Plugin System Variables and Options”. If no configuration is provided using the mysqlx_ssl_* variables, X Plugin falls back to using the MySQL Server SSL system variables. This means you can choose to either have separate SSL configurations for MySQL Protocol and X Protocol connections by configuring each separately, or share the SSL configuration between MySQL Protocol and X Protocol connections by only configuring the ssl-* variables.

On a server with X Plugin installed, to configure MySQL Protocol and X Protocol connections with separate SSL configurations use both the ssl-* and mysqlx-ssl-* variables in my.cnf:

[mysqld]
ssl-ca=ca1.pem
ssl-cert=server-cert1.pem
ssl-key=server-key1.pem

mysqlx-ssl-ca=ca2.pem
mysqlx-ssl-cert=server-cert2.pem
mysqlx-ssl-key=server-key2.pem

The available mysqlx_ssl_* variables mirror the SSL variables in MySQL Server, so the files and techniques described for configuring MySQL Server to use SSL at Section 7.4.4, “Configuring MySQL to Use Secure Connections” are relevant to configuring X Plugin to use secure connections. An exception to this rule is the mysqlx_ssl variable, which defaults to 1 whenever any of the other X Protocol SSL related variables is configured. In other words, if you configure any of the mysqlx_ssl_* variables there is no need to configure mysqlx_ssl.

You can configure the TLS versions used by X Protocol SSL connections using the tls_version system variable. The TLS version used by MySQL Protocol and X Protocol connections is therefore the same TLS version.

Encryption per connection is optional, but a specific user can be forced to use encryption for X Protocol and MySQL Protocol connections. You configure such a user by issuing a GRANT statement with the REQUIRE option. For more details see Section 14.7.1.4, “GRANT Syntax”. Alternatively all X Protocol and MySQL Protocol connections can be forced to use encryption by setting require_secure_transport.

3.7.2 X Plugin Options and Variables

This section describes the command options and system variables which configure X Plugin. If values specified at startup time are incorrect, X Plugin could fail to initialize properly and the server does not load it. In this case, the server could also produce error messages for other X Plugin settings because it cannot recognize them.

3.7.2.1 X Plugin Option and Variable Reference

This table provides an overview of the command options, and system and status variables provided by X Plugin.

Table 3.1 X Plugin Option and Variable Reference

NameCmd-LineOption FileSystem VarStatus VarVar ScopeDynamic
Mysqlx_address   YesGlobalNo
mysqlx_bind_addressYesYesYes GlobalNo
Mysqlx_bytes_received   YesBothNo
Mysqlx_bytes_sent   YesBothNo
mysqlx_connect_timeoutYesYesYes GlobalYes
Mysqlx_connection_accept_errors   YesBothNo
Mysqlx_connection_errors   YesBothNo
Mysqlx_connections_accepted   YesGlobalNo
Mysqlx_connections_closed   YesGlobalNo
Mysqlx_connections_rejected   YesGlobalNo
Mysqlx_crud_delete   YesBothNo
Mysqlx_crud_find   YesBothNo
Mysqlx_crud_insert   YesBothNo
Mysqlx_crud_update   YesBothNo
Mysqlx_errors_sent   YesBothNo
Mysqlx_expect_close   YesBothNo
Mysqlx_expect_open   YesBothNo
mysqlx_idle_worker_thread_timeoutYesYes    
Mysqlx_init_error   YesBothNo
mysqlx_max_allowed_packetYesYes    
mysqlx_max_connectionsYesYesYes GlobalYes
mysqlx_min_worker_threadsYesYes    
Mysqlx_notice_other_sent   YesBothNo
Mysqlx_notice_warning_sent   YesBothNo
Mysqlx_port   YesGlobalNo
mysqlx_portYesYesYes GlobalNo
mysqlx_port_open_timeoutYesYesYes GlobalNo
Mysqlx_rows_sent   YesBothNo
Mysqlx_sessions   YesGlobalNo
Mysqlx_sessions_accepted   YesGlobalNo
Mysqlx_sessions_closed   YesGlobalNo
Mysqlx_sessions_fatal_error   YesGlobalNo
Mysqlx_sessions_killed   YesGlobalNo
Mysqlx_sessions_rejected   YesGlobalNo
Mysqlx_socket   YesGlobalNo
mysqlx-socketYesYesYes GlobalNo
mysqlx_socket  Yes GlobalNo
mysqlx_sslYesYesYes GlobalNo
Mysqlx_ssl_accept_renegotiates   YesGlobalNo
Mysqlx_ssl_accepts   YesGlobalNo
Mysqlx_ssl_active   YesBothNo
mysqlx_ssl_caYesYesYes GlobalNo
mysqlx_ssl_capathYesYesYes GlobalNo
mysqlx_ssl_certYesYesYes GlobalNo
Mysqlx_ssl_cipher   YesBothNo
mysqlx_ssl_cipherYesYes    
Mysqlx_ssl_cipher_list   YesBothNo
mysqlx_ssl_crlYesYesYes GlobalNo
mysqlx_ssl_crlpathYesYesYes GlobalNo
Mysqlx_ssl_ctx_verify_depth   YesBothNo
Mysqlx_ssl_ctx_verify_mode   YesBothNo
Mysqlx_ssl_finished_accepts   YesGlobalNo
mysqlx_ssl_keyYesYesYes GlobalNo
Mysqlx_ssl_server_not_after   YesGlobalNo
Mysqlx_ssl_server_not_before   YesGlobalNo
Mysqlx_ssl_verify_depth   YesGlobalNo
Mysqlx_ssl_verify_mode   YesGlobalNo
Mysqlx_ssl_version   YesBothNo
Mysqlx_stmt_create_collection   YesBothNo
Mysqlx_stmt_create_collection_index   YesBothNo
Mysqlx_stmt_disable_notices   YesBothNo
Mysqlx_stmt_drop_collection   YesBothNo
Mysqlx_stmt_drop_collection_index   YesBothNo
Mysqlx_stmt_enable_notices   YesBothNo
Mysqlx_stmt_execute_sql   YesBothNo
Mysqlx_stmt_execute_xplugin   YesBothNo
Mysqlx_stmt_kill_client   YesBothNo
Mysqlx_stmt_list_clients   YesBothNo
Mysqlx_stmt_list_notices   YesBothNo
Mysqlx_stmt_list_objects   YesBothNo
Mysqlx_stmt_ping   YesBothNo
Mysqlx_worker_threads   YesGlobalNo
Mysqlx_worker_threads_active   YesGlobalNo

3.7.2.2 X Plugin System Variables and Options

The following command options configure X Plugin:

  • --mysqlx_port[=value]

    Command-Line Format--mysqlx_port=#
    System VariableNamemysqlx_port
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeinteger
    Default33060
    Min Value1
    Max Value65535

    Specifies the port where the X Plugin listens for connections. This option is only read at start up, therefore to change it on a running server requires a restart.

  • --mysqlx_port_open_timeout[=value]

    Command-Line Format--mysqlx_port_open_timeout=#
    System VariableNamemysqlx_port_open_timeout
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeinteger
    Default0
    Min Value1
    Max Value120

    Specifies how long X Plugin should retry to use a port when the port is already allocated. By default no retries are attempted. Set the value to the number of seconds X Plugin should retry before stopping to use the port.

  • --mysqlx_bind_address[=value]

    Command-Line Format--mysqlx-bind-address=#
    System VariableNamemysqlx_bind_address
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypestring
    Default*

    By default X Plugin listens for connections on a single IPv4 TCP/IP network socket. Use this option to configure changes to where X Plugin listens for connections, such as binding to an IPv6 address, all IPv4 and IPv6 addresses or a default IPv6 address with a fallback IPv4 address.

    X Plugin treats different types of addresses as follows:

    • If the address is *, X Plugin accepts TCP/IP connections on all server host IPv6 and IPv4 interfaces if the server host supports IPv6, or accepts TCP/IP connections on all IPv4 addresses otherwise. Use this address to permit both IPv4 and IPv6 connections on all server interfaces. This value is the default.

    • If the address is 0.0.0.0, X Plugin accepts TCP/IP connections on all server host IPv4 interfaces. This value is the default.

    • If the address is ::, X Plugin accepts TCP/IP connections on all server host IPv4 and IPv6 interfaces.

    • If the address is *, X Plugin accepts TCP/IP connections on all server host IPv6 and IPv4 interfaces if the server host supports IPv6, or accepts TCP/IP connections on all IPv4 addresses otherwise. Use this address to permit both IPv4 and IPv6 connections for X Plugin.

    • If the address is an IPv4-mapped address, X Plugin accepts TCP/IP connections for that address, in either IPv4 or IPv6 format. For example, if X Plugin is bound to ::ffff:127.0.0.1, a client such as MySQL Shell can connect using --host=127.0.0.1 or --host=::ffff:127.0.0.1.

    • If the address is a regular IPv4 or IPv6 address (such as 127.0.0.1 or ::1), X Plugin accepts TCP/IP connections only for that IPv4 or IPv6 address.

  • --mysqlx_connect_timeout[=value]

    Command-Line Format--mysqlx_connect_timeout=#
    System VariableNamemysqlx_connect_timeout
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default30
    Min Value1
    Max Value18446744073709551615

    Number of seconds to wait for the first packet to be received from newly connected clients. This option is dynamically changeable.

  • mysqlx_idle_worker_thread_timeout[=value]

    Command-Line Format--mysqlx_idle_worker_thread_timeout=#
    Permitted ValuesTypeinteger
    Default60
    Min Value0
    Max Value18446744073709551615

    Time in seconds after which an idle worker thread is terminated.

  • mysqlx_max_allowed_packet[=value]

    Command-Line Format--mysqlx_max_allowed_packet=#
    Permitted ValuesTypeinteger
    Default1048576
    Min Value0
    Max Value18446744073709551615

    Maxmum size of a network packet that X Plugin can process. This option is dynamically changeable.

  • mysqlx_max_connections[=value]

    Command-Line Format--mysqlx_max_connections=#
    System VariableNamemysqlx_max_connections
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default100
    Min Value1
    Max Value18446744073709551615

    Maximum number of concurrent client connections the X Plugin can accept. This option is dynamically changeable. When modifying this variable, if the new value is smaller than the current number of connections, the new limit is only taken into account for new connections.

  • mysqlx_min_worker_threads[=value]

    Command-Line Format--mysqlx_min_worker_threads=#
    Permitted ValuesTypeinteger
    Default2
    Min Value1
    Max Value18446744073709551615

    The minimum number of worker threads the X Plugin uses for handling client requests. The maximum number of worker threads is limited implicitly by --mysqlx_max_connections. This option is dynamically changeable.

  • --mysqlx-socket[=value]

    Introduced5.7.15
    Command-Line Format--mysqlx-socket=#
    System VariableNamemysqlx_socket
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypestring
    Default/tmp/mysqlx.sock

    The path to a Unix socket file which X Plugin uses for connections. Only used by MySQL Server when running on Unix operating systems. Clients can then use this socket to connect to MySQL Server using the X Plugin.

    The value of mysqlx_socket depends on the value of socket. By default socket is /tmp/mysql.sock and therefore mysqlx_socket is /tmp/mysqlx.sock. If socket is set to /sockets/mysql.sock and mysqlx_socket has not been manually configured, then mysqlx_socket is set to /sockets/mysqlx.sock. In other words the path and filename of socket is used and an x is appended to the filename, this enables you to conveniently store the sockets used by MySQL server at a single path. To define separate paths or unique filenames for the MySQL and X Plugin sockets, configure both the socket and the mysqlx_socket system variables. For example in a configuration file:

    socket=/home/sockets/mysqld/mysql.sock
    mysqlx_socket=/home/sockets/xplugin/xplugin.sock
    

    Alternatively, X Plugin can be configured at compile time to use a specific socket with the MYSQLX_UNIX_ADDR option. If the X Plugin MYSQLX_UNIX_ADDR compile option is not set, the value is based on the MYSQL_UNIX_ADDR and adds an x to the file name, for example resulting in /tmp/mysqlx.sock.

  • --mysqlx_ssl[=value]

    Command-Line Format--mysqlx_ssl=#
    System VariableNamemysqlx_ssl
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeinteger

    The equivalent of ssl for X Plugin, see that variable for more information.

  • --mysqlx_ssl_ca[=value]

    Command-Line Format--mysqlx_ssl_ca=file_name
    System VariableNamemysqlx_ssl_ca
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypefile name

    The equivalent of ssl_ca for X Plugin, see that variable for more information.

  • --mysqlx_ssl_capath[=value]

    Command-Line Format--mysqlx_ssl_capath=dir_name
    System VariableNamemysqlx_ssl_capath
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypedirectory name

    The equivalent of ssl_capath for X Plugin, see that variable for more information.

  • --mysqlx_ssl_cert[=value]

    Command-Line Format--mysqlx_ssl_cert
    System VariableNamemysqlx_ssl_key
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypefile name

    The equivalent of ssl_cert for X Plugin, see that variable for more information.

  • --mysqlx_ssl_cipher[=value]

    Command-Line Format--mysqlx_ssl_cipher=name
    Permitted ValuesTypestring

    The equivalent of ssl_cipher for X Plugin, see that variable for more information.

  • --mysqlx_ssl_crl[=value]

    Command-Line Format--mysqlx_ssl_crl=file_name
    System VariableNamemysqlx_ssl_crl
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypefile name

    The equivalent of ssl_crl for X Plugin, see that variable for more information.

  • --mysqlx_ssl_crlpath[=value]

    Command-Line Format--mysqlx_ssl_crlpath=directory_name
    System VariableNamemysqlx_ssl_crlpath
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypedirectory name

    The equivalent of ssl_crlpath for X Plugin, see that variable for more information.

  • --mysqlx_ssl_key[=value]

    Command-Line Format--mysqlx_ssl_key=file_name
    System VariableNamemysqlx_ssl_key
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypefile name

    The equivalent of ssl_key for X Plugin, see that variable for more information.

The following system variables control X Plugin.

  • mysqlx_socket

    Introduced5.7.15
    System VariableNamemysqlx_socket
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypestring
    Default/tmp/mysqlx.sock

    The socket where X Plugin listens for connections.

3.7.3 Monitoring X Plugin

This section describes how to monitor X Plugin. There are two available methods of monitoring, using Performance Schema tables or status variables.

3.7.3.1 Status Variables for X Plugin

The status variables have the following meanings.

3.8 MySQL Shell User Guide

This section provides user documentation for MySQL Shell, which enables you to prototype code using the X DevAPI to communicate with a MySQL Server running the X Plugin. The X Plugin is a new MySQL Server feature available with MySQL Server 5.7.12 and higher.

MySQL Shell is an advanced command-line client and code editor for the MySQL Server. In addition to SQL, MySQL Shell also offers scripting capabilities for JavaScript and Python. When MySQL Shell is connected to the MySQL Server through the X Protocol, the X DevAPI can be used to work with both relational and document data.

3.8.1 MySQL Shell Features

The following features are available in MySQL Shell.

Interactive Code Execution

MySQL Shell provides an interactive code execution mode, where you type code at the MySQL Shell prompt and each entered statement is processed, with the result of the processing printed onscreen.

Supported Languages

MySQL Shell processes code in the following languages: JavaScript, Python and SQL. Any entered code is processed as one of these languages, based on the language that is currently active. There are also specific commands that are treated as shell commands which enable you to configure MySQL Shell or retrieve information. For more information see Section 3.8.4.1, “MySQL Shell Commands”.

Batch Code Execution

In addition to the interactive execution of code, MySQL Shell can also take code from different sources and process it. This method of processing code in a non-interactive way is called Batch Execution.

As batch execution mode is intended for script processing of a single language, it is limited to having minimal non-formatted output and disabling the execution of commands. To avoid these limitations, use the --interactive command-line option, which tells MySQL Shell to execute the input as if it were an interactive session. In this mode the input is processed line by line just as if each line were typed in an interactive session. For more information see Section 3.8.3.5, “Batch Mode Made Interactive”.

Output Formats

MySQL Shell provides output in different formats depending on how it is used: Tabbed, Table and JSON. For more information see Section 3.8.3.3, “Output Formats”.

Multiple-line Support

Multiple-line code can be written using a command, enabling MySQL Shell to cache multiple lines and then execute them as a single statement. For more information see Section 3.8.3.5.1, “Multiple-line Support”.

Stored Sessions

MySQL Shell maintains a list of connection data for different servers. You can connect to a server using these stored sessions. The list of stored sessions can be edited. See Section 3.8.5, “Stored Sessions” for more information.

Application Log

MySQL Shell can be configured to log information about the execution process. For more information see Section 3.8.6, “MySQL Shell Application Log”.

X DevAPI

Code written in MySQL Shell uses the X DevAPI, provided for Python and JavaScript. For general information, see X DevAPI User Guide.

X Protocol Support

MySQL Shell is designed to provide an integrated command-line client for all MySQL products which support X Protocol. The development features of MySQL Shell are designed for sessions using the X Protocol. MySQL Shell can also connect to MySQL Servers that do not support the X Protocol using the legacy MySQL Protocol. A minimal set of features from the X DevAPI are available for sessions created using the legacy MySQL protocol.

Global Session

Interaction with a MySQL Server is done through a Session object. For Python and JavaScript, a Session can be created through the getSession and getNodeSession functions of the mysqlx module. If a session is created in JavaScript mode using any of these methods, it is available only in JavaScript mode. The same happens if the session is created in Python mode. None of these sessions can be used in SQL mode.

For SQL Mode, the concept of Global Session is supported by the MySQL Shell. A Global Session is created when the connection information is passed to MySQL Shell using command-line options, or by using the \connect command.

The Global Session is used to execute statements in SQL mode and the same session is available in both Python or JavaScript modes. When a Global Session is created, a variable called session is set in the scripting languages, so you can execute code in the different languages by switching the active mode.

For more information, see Section 3.8.2.1, “MySQL Shell Sessions”.

3.8.2 Getting Started with MySQL Shell

This section describes how to get started with MySQL Shell. This section assumes you have a MySQL Server running X Plugin and that you have installed MySQL Shell, see Section 3.3, “Setting Up MySQL as a Document Store”.

3.8.2.1 MySQL Shell Sessions

This section explains the different types of sessions in MySQL Shell and how to create and configure them.

3.8.2.1.1 MySQL Shell Sessions Explained

MySQL Shell is a unified interface to operate MySQL Server through scripting languages such as JavaScript or Python. To maintain compatibility with previous versions, SQL can also be executed in certain modes. A connection to a MySQL server is required. In MySQL Shell these connections are handled by a Session object.

The following different types of Session objects are available:

  • XSession: Use this session type for new application development. It offers the best integration with MySQL Server, and therefore, it is used by default. SQL execution is not supported and therefore it is not compatible with MySQL Shell's SQL Mode.

  • Node Session: Use this session type for SQL execution on a MySQL Server with the X Protocol enabled. SQL execution is available with this session type, therefore it can be used in MySQL Shell's SQL Mode.

    This session type should only be used when connecting directly to an X Protocol enabled MySQL Server.

  • Classic Session Use this session type to interact with MySQL Servers that do not have the X Protocol enabled. SQL execution is available with this session type, therefore it can be used in MySQL Shell's SQL Mode.

    The development API available for this type of session is very limited. For example, there are no CRUD operations, no collection handling, and binding is not supported.

3.8.2.1.2 Choosing a MySQL Shell Session Type

MySQL Shell creates an XSession object by default. To choose which type of session should be created, use one of these options:

  • --node creates a Node Session.

  • --classic creates a Classic Session.

  • --x creates an XSession.

For more information, see Section 3.8.2.2.1, “Connecting using a URI String” and Section 3.8.2.2.2, “Connecting using Individual Parameters”.

3.8.2.2 MySQL Shell Connections

MySQL Shell can be configured to connect to a MySQL Server running X Plugin using command-line options at start up or from MySQL Shell itself. The address of the MySQL Server can be specified using individual parameters, such as user, hostname and port, or using a Uniform Resource Identifier (URI) in the format user@host:port/schema, such as mike@myserver:33060/testDB. The following sections describe these methods.

Regardless of the method you choose to connect it is important to understand how passwords are handled by MySQL Shell:

Important

By default connections are assumed to require a password. The password is requested at the login prompt. To specify a password-less account use the --password option, or use a : after the user in a URI.

If you do not specify parameters for a connection the following defaults are used:

  • user defaults to the current system user name

  • host defaults to localhost

  • port defaults to the X Plugin port 33060 when using an X Session, and port 3306 when using a Classic session

MySQL Shell connections using X Protocol always use TCP, using Linux sockets is not supported. MySQL Shell connections using MySQL Protocol can use Linux sockets by specifying the --socket command-line option.

3.8.2.2.1 Connecting using a URI String

You configure the MySQL Server which MySQL Shell connects to by passing the connection data in string format using the --uri command-line option.

Use the following format:

[dbuser[:[dbpassword]]@]host[:port][/schema]

Descriptions of these options:

  • dbuser: specifies the MySQL user account to be used for the authentication process.

  • dbpassword: specifies the dbuser password to be used for the authentication process.

    Warning

    Storing the password in the URI is not recommended.

  • host: specifies the host to which the session object connects. If not specified, localhost is used by default.

  • port: specifies which port the target MySQL server is listening on for the connection. If not specified, 33060 is used by default for the X Protocol enabled sessions, and 3306 is the default for traditional MySQL protocol sessions.

  • schema: Indicates the database to be set as default when the session is established.

If no password is specified using the URI, which is recommended, then the password is prompted for. The following examples show how to use these command-line options:

  • Connect with a Node Session at port 33065.

    shell> mysqlsh --uri user@localhost:33065 --node
    
  • Connect with a Classic Session.

    shell> mysqlsh --uri user@localhost --classic
    

Although using a password-less account is not recommended, you can specify a user without a password using a : after the user name, for example:

shell> mysqlsh --uri user:@localhost

In addition to specifying a URI manually, you can use a stored session. See Section 3.8.5, “Stored Sessions” for more information.

3.8.2.2.2 Connecting using Individual Parameters

In addition to specifying connection parameters using a URI, it is also possible to define the connection data using separate parameters for each value.

Use the following parameters:

The first 5 parameters match the tokens used in the URI format described at Section 3.8.2.2.1, “Connecting using a URI String”.

The --password parameter indicates the user should connect without a password.

For consistency, the following aliases are supported for some parameters:

Attempt to establish an XSession with a specified user at port 33065.

shell> mysqlsh -u user -h localhost -P 33065

Attempt to establish a Classic Session with a specified user.

shell> mysqlsh -u user -h localhost --classic

Attempt to establish a Node Session with a specified user.

shell> mysqlsh --node -u user -h localhost
3.8.2.2.3 Using SSL for Secure Connections

Using SSL is possible when connecting to an SSL enabled MySQL server.

To configure an SSL connection, use the following command-line options:

  • --ssl : This enables or disables connections through SSL. If set to 0, the other SSL command-line options are ignored.

  • --ssl-ca=filename: The path to a file in PEM format that contains a list of trusted SSL certificate authorities.

  • --ssl-cert=filename: The name of the SSL certificate file in PEM format to use for establishing a secure connection.

  • --ssl-key=filename: The name of the SSL key file in PEM format to use for establishing a secure connection.

The --ssl option is assumed to be 1 (enabled) if the other SSL options are set.

3.8.2.2.4 Connecting using both URI and Individual Parameters

When the --uri option is specified in combination with some of the individual parameters, the address specified by the --uri option is used as the base connection data and the values provided using individual parameters override the corresponding value from the URI. If the --user option is used, it would replace any user specified as part of a URI.

For example to establish an XSession and override user from the URI:

shell> mysqlsh --uri user@localhost:33065 --user otheruser
3.8.2.2.5 Creating a Session Using Shell Commands

If you open MySQL Shell without specifying connection parameters, MySQL Shell opens without an established global session. It is possible to establish a global session once MySQL Shell has been started using the following Shell Commands:

  • \connect URI: Creates an XSession.

  • \connect -n URI: Creates a Node Session.

  • \connect -c URI: Creates a Classic Session.

Configure the connection using the URI parameter, which follows the same syntax as for the --uri command-line option. For additional information, see Section 3.8.2.2.1, “Connecting using a URI String”.

For example:

mysql-js> \connect root@localhost
Creating XSession to root@localhost...
Enter password: ****
No default schema selected.
mysql-js>
3.8.2.2.5.1 Creating a Secure Session Using SSL

To establish an SSL connection, the URI parameter passed to the connect shell commands must include the SSL information as URL parameters. For example:

mysql-js> \connect root@localhost?ssl_ca=/path/to/ca/file&\
ssl_cert=/path/to/cert/file&ssl_key=/path/to/key/file
Creating XSession to root@localhost...
Enter password: ****
No default schema selected.
mysql-js>
3.8.2.2.6 Connections in JavaScript and Python

When a connection is made using the command-line options or by using any of the shell commands, a global session object is created. This session is global because once created, it can be used in any of the MySQL Shell execution modes.

Any global session object is available in JavaScript or Python modes because a variable called session holds a reference to it.

In SQL mode, both Node and Classic sessions can be used because they both expose SQL execution. In SQL mode an XSession cannot be used.

In addition to the global session object, sessions can be established and assigned to a different variable by using the functions available in the mysql and mysqlx JavaScript and Python modules.

For example, the following functions are provided by these modules:

  • mysql.getSession(connectionData[, password])

  • mysqlx.getNodeSession(connectionData[, password])

  • mysql.getClassicSession(connectionData[, password])

The first of these functions is used to create an XSession which features the most comprehensive development API and supports X Protocol.

The second creates a Node Session which connects to a X Protocol enabled MySQL Server and allows SQL Execution.

The latter returns a Classic Session object which uses the traditional MySQL protocol and has a very limited development API.

connectionData can be either a URI as specified above or a dictionary containing the connection parameters. See Section 3.8.2.2.1, “Connecting using a URI String”.

The following example shows how to create a Node Session using the X Protocol:

mysql-js> var mysqlx=require('mysqlx').mysqlx;

mysql-js> var session=mysqlx.getNodeSession('root@localhost');

mysql-js> print(session)
<NodeSession:root@localhost>
mysql-js>

The following example shows how to create a Node Session using the X Protocol so that you can execute SQL:

mysql-js> var mysqlx=require('mysqlx').mysqlx;

mysql-js> var session=mysqlx.getNodeSession({host: 'localhost', dbUser: 'root'});

mysql-js> print(session)
<NodeSession:root@localhost>
mysql-js>

The following example shows how to create a Classic Session:

mysql-js> var mysql=require('mysql').mysql;

mysql-js> var session = mysql.getClassicSession('root@localhost:3307');
mysql-js> print(session)
<ClassicSession:root@localhost:3307>
mysql-js>
3.8.2.2.6.1 Using SSL for Secure Connections

To establish an SSL connection, set the SSL information in the connectionData dictionary. For example:

mysql-js> var mysqlx=require('mysqlx').mysqlx;

mysql-js> var session=mysqlx.getNodeSession({host: 'localhost',
                                             dbUser: 'root',
                                             dbPassword: 'mypasswd',
                                             ssl_ca: "path_to_ca_file",
                                             ssl_cert: "path_to_cert_file",
                                             ssl_key: "path_to_key_file"});

mysql-js> print(session)
<NodeSession:root@localhost>
mysql-js>

3.8.2.3 MySQL Shell Global Variables

MySQL Shell reserves certain variables as global variables, which are assigned to commonly used objects in scripting. This section describes the available global variables and provides examples of working with them. The global variables are:

  • session represents the global session if one has been established.

  • db represents a schema if one has been defined, for example by a URI.

MySQL Shell provides interactive error resolution for common situations related to using the global variables. For example:

  • Attempting to use an undefined session global variable.

  • Attempting to retrieve an nonexistent schema using session.

  • Attempting to use an undefined db global variable.

Undefined Global Session

The global session variable is set when a global session is established. When a global session is established, issuing a session statement in MySQL Shell displays the session type and its URI as follows:

mysql-js> session
<XSession:root@localhost:33060>
mysql-js>

If no global session has been established, MySQL Shell displays the following:

mysql-js> session
<Undefined>
mysql-js>

If you attempt to use the session variable when no global session is established, interactive error resolution starts and you are prompted to provide the required information to establish a global session. If the session is successfully established, it is assigned to the session variable. The prompts are:

  • An initial prompt explains that no global session is established and asks if one should be established.

  • If the you choose to set a global session, the session type is requested.

  • Either the URI or the alias of a stored session is requested.

  • If required, a password is requested.

For example:

mysql-js> session.uri
The global session is not set, do you want to establish a session? [y/N]: y
Please specify the session type:

1) X
2) Node
3) Classic

Type: 2
Please specify the MySQL server URI (or $slias): root@localhost
Enter password:*******
root@localhost:33060
mysql-js>
Undefined db Variable

The global db variable is set when a global session is established and a default schema is configured. For example, using a URI such as root@localhost/sakila to establish a global session connected to the MySQL Server at localhost, on port 33060, as the user root, assigns the schema sakila to the global variable db. Once a schema is defined, issuing db at the MySQL Shell prompt prints the schema name as follows:

mysql-js> db
<Schema:world_x>
mysql-js>

If there is no global session established, the following is displayed:

mysql-js> db
<Undefined>
mysql-js>

If you attempt to use the db variable when no global session has been established, the following error is displayed:

mysql-js> db.getCollections()
LogicError: The db variable is not set, establish a global session first.
at (shell):1:2
in db.getCollections()
^

If a global session has been established but you attempt to use an undefined db, interactive error resolution begins and you are prompted to define an active schema by providing the schema name. If this succeeds the db variable is set to the defined schema. For example:

mysql-js> db.getCollections()
The db variable is not set, do you want to set the active schema? [y/N]:y
Please specify the schema:world_x
[
<Collection:countryinfo>
]
mysql-js> db
<Schema:world_x>
mysql-js>
Retrieving an Nonexistent Schema

If you attempt to use session to retrieve an nonexistent schema, interactive error resolution provides the option to create the schema.

mysql-js> var mySchema = session.getSchema('my_test')
The schema my_test does not exist, do you want to create it? [y/N]: y

mysql-js> mySchema
<Schema:my_test>
mysql-js>

In all cases, if you do not provide the information required to resolve each situation, a proper result of executing the requested statement on an undefined variable is displayed.

3.8.3 MySQL Shell Code Execution

This section explains how code execution works in MySQL Shell.

3.8.3.1 Interactive Code Execution

The default mode of MySQL Shell provides interactive execution of database operations that you type at the command prompt. These operations can be written in JavaScript, Python or SQL depending on the type of session being used. When executed, the results of the operation are displayed on-screen.

As with any other language interpreter, MySQL Shell is very strict regarding syntax. For example, the following JavaScript snippet reads and prints the documents in a collection:

var mysqlx = require('mysqlx').mysqlx;
var mySession = mysqlx.getSession('user:pwd@localhost');
var result = mySession.world_x.countryinfo.find().execute();
var record = result.fetchOne();
while(record){
  print(record);
  record = result.fetchOne();
}

As seen above, the call to find() is followed by the execute() function. CRUD database commands are only actually executed on the MySQL Server when execute() is called. However, when working with MySQL Shell interactively, execute() is implicitly called whenever you press Return on a statement. Then the results of the operation are fetched and displayed on-screen. The rules for when you need to call execute() or not are as follows:

  • When using MySQL Shell in this way, calling execute() becomes optional on:

    • Collection.add()

    • Collection.find()

    • Collection.remove()

    • Collection.modify()

    • Table.insert()

    • Table.select()

    • Table.delete()

    • Table.update()

    • NodeSession.sql()

  • Automatic execution is disabled if the object is assigned to a variable. In such a case calling execute() is mandatory to perform the operation.

  • When a line is processed and the function returns any of the available Result objects, the information contained in the Result object is automatically displayed on screen. The functions that return a Result object include:

    • The SQL execution and CRUD operations (listed above)

    • Transaction handling and drop functions of the session objects in both mysql and mysqlx modules: -

      • startTransaction()

      • commit()

      • rollback()

      • dropSchema()

      • dropTable()

      • dropCollection()

      • dropView()

      • ClassicSession.runSql()

Based on the above rules, the statements needed in the MySQL Shell in interactive mode to establish a session, query, and print the documents in a collection are:

mysql-js> var mysqlx = require('mysqlx').mysqlx;
mysql-js> var mySession = mysqlx.getSession('user:pwd@localhost');

No call to execute() is needed and the Result object is automatically printed.

mysql-js> mySession.world_x.countryinfo.find();

3.8.3.2 Batch Code Execution

As well as interactive code execution, MySQL Shell provides batch code execution from:

  • A file loaded for processing.

  • A file containing code that is redirected to the standard input for execution.

  • Code from a different source that is redirected to the standard input for execution.

For example:

Loading SQL code from a file for batch processing.

shell> mysqlsh --file code.js

Redirecting a file to standard input for execution.

shell> mysqlsh < code.js

Redirecting code to standard input for execution.

shell> echo "show databases;" | mysqlsh --sql --uri root@192.168.1.141:33060
Executable Scripts

Starting with version 1.0.4, on Linux you can create executable scripts that run with MySQL Shell by including a #! line as the first line of the script. This line should provide the full path to MySQL Shell and include the --file option. For example:

#!/usr/local/mysql-shell/bin/mysqlsh --file
print("Hello World\n");

The script file must be marked as executable in the filesystem. Running the script invokes MySQL Shell and it executes the contents of the script.

3.8.3.3 Output Formats

The output of the commands processed on the server can be formatted in different ways. This section details the different available output formats.

3.8.3.3.1 Table Format

The table format is used by default when MySQL Shell is in interactive mode. The output is presented as a formatted table for a better view and to aid analysis.

mysql-sql> select * from sakila.actor limit 3;
+----------+-------------+----------------+----------------------+
| actor_id | first_name  | last_name      | last_update          |
+----------+-------------+----------------+----------------------+
|        1 | PENELOPE    | GUINESS        | 2006-02-15 4:34:33   |
|        2 | NICK        | WAHLBERG       | 2006-02-15 4:34:33   |
|        3 | ED          | CHASE          | 2006-02-15 4:34:33   |
+----------+-------------+----------------+----------------------+
3 rows in set (0.00 sec)

mysql-sql>

To get this output format when running in batch mode, use the --table command-line option.

3.8.3.3.2 Tab Separated Format

This format is used by default when running MySQL Shell in batch mode, to have better output for automated analysis.

>echo "select * from sakila.actor limit 3;" | mysqlsh --classic --uri root@192.168.1.141:33460
actor_id        first_name      last_name       last_update
1       PENELOPE        GUINESS 2006-02-15 4:34:33
2       NICK    WAHLBERG        2006-02-15 4:34:33
3       ED      CHASE   2006-02-15 4:34:33
3.8.3.3.3 JSON Format Output

MySQL Shell supports the JSON format for output and it is available both in interactive and batch mode. This output format can be enabled using the --json command-line option:

JSON Format in Batch Mode

shell>echo "select * from sakila.actor limit 3;" | mysqlsh --json --sqlc --uri root@192.168.1.141:3306
{"duration":"0.00 sec","info":"","row_count":3,"rows":[[1,"PENELOPE","GUINESS",{"year":2006,"month":1,"day":15,"hour":4,"minute":34,"second":33.0}],[2,"NICK","WAHLBERG",{"year":2006,"month":1,"day":15,"hour":4,"minute":34,"second":33.0}],[3,"ED","CHASE",{"year":2006,"month":1,"day":15,"hour":4,"minute":34,"second":33.0}]],"warning_count":0}

shell>echo "select * from sakila.actor limit 3;" | mysqlsh --json=raw --sqlc --uri root@192.168.1.141:3306
{"duration":"0.00 sec","info":"","row_count":3,"rows":[[1,"PENELOPE","GUINESS",{"year":2006,"month":1,"day":15,"hour":4,"minute":34,"second":33.0}],[2,"NICK","WAHLBERG",{"year":2006,"month":1,"day":15,"hour":4,"minute":34,"second":33.0}],[3,"ED","CHASE",{"year":2006,"month":1,"day":15,"hour":4,"minute":34,"second":33.0}]],"warning_count":0}

shell>echo "select * from sakila.actor limit 3;" | mysqlsh --json=pretty --sqlc --uri root@192.168.1.141:3306
{
    "duration": "0.00 sec",
    "info": "",
    "row_count": 3,
    "rows": [
        [
            1,
            "PENELOPE",
            "GUINESS",
            {
                "year": 2006,
                "month": 1,
                "day": 15,
                "hour": 4,
                "minute": 34,
                "second": 33.0
            }
        ],
        [
            2,
            "NICK",
            "WAHLBERG",
            {
                "year": 2006,
                "month": 1,
                "day": 15,
                "hour": 4,
                "minute": 34,
                "second": 33.0
            }
        ],
        [
            3,
            "ED",
            "CHASE",
            {
                "year": 2006,
                "month": 1,
                "day": 15,
                "hour": 4,
                "minute": 34,
                "second": 33.0
            }
        ]
    ],
    "warning_count": 0
}
shell>

JSON Format in Interactive Mode (started with --json=raw)

mysql-sql> select * from sakila.actor limit 3;
{"duration":"0.00 sec","info":"","row_count":3,"rows":[[1,"PENELOPE","GUINESS",{"year":2006,"month":1,"day":15,"hour":4,"minute":34,"second":33.0}],[2,"NICK","WAHLBERG",{"year":2006,"month":1,"day":15,"hour":4,"minute":34,"second":33.0}],[3,"ED","CHASE",{"year":2006,"month":1,"day":15,"hour":4,"minute":34,"second":33.0}]],"warning_count":0}

mysql-sql>

JSON Format in Interactive Mode (started with --json=pretty)

mysql-sql> select * from sakila.actor limit 3;
{
    "duration": "0.00 sec",
    "info": "",
    "row_count": 3,
    "rows": [
        [
            1,
            "PENELOPE",
            "GUINESS",
            {
                "year": 2006,
                "month": 1,
                "day": 15,
                "hour": 4,
                "minute": 34,
                "second": 33.0
            }
        ],
        [
            2,
            "NICK",
            "WAHLBERG",
            {
                "year": 2006,
                "month": 1,
                "day": 15,
                "hour": 4,
                "minute": 34,
                "second": 33.0
            }
        ],
        [
            3,
            "ED",
            "CHASE",
            {
                "year": 2006,
                "month": 1,
                "day": 15,
                "hour": 4,
                "minute": 34,
                "second": 33.0
            }
        ]
    ],
    "warning_count": 0
}

mysql-sql>
3.8.3.3.4 Result Metadata

When an operation is executed, in addition to any results returned, some additional information is available. This includes information such as the number of affected rows, warnings, duration, and so on, when any of these conditions is true:

  • JSON format is being used for the output

  • MySQL Shell is running in interactive mode.

3.8.3.4 Active Language

MySQL Shell can execute SQL, JavaScript or Python code, but only one language can be active at a time. The active mode determines how the executed statements are processed:

  • If using SQL mode, statements are processed as SQL which means they are sent to the MySQL server for execution.

  • If using JavaScript mode, statements are processed as JavaScript code.

  • If using Python mode, statements are processed as Python code.

When running MySQL Shell in interactive mode, activate a specific language by entering the commands: \sql, \js, \py.

When running MySQL Shell in batch mode, activate a specific language by passing any of these command-line options: --js, --py or --sql. The default mode if none is specified is JavaScript.

Use MySQL Shell to execute the content of the file code.sql as SQL.

shell> mysqlsh --sql < code.sql

Use MySQL Shell to execute the content of the file code.js as JavaScript code.

shell> mysqlsh < code.js

Use MySQL Shell to execute the content of the file code.py as Python code.

shell> mysqlsh --py < code.py

3.8.3.5 Batch Mode Made Interactive

This section describes code execution in batch mode.

  • In batch mode, all the command logic described above is not available, only valid code for the active language can be executed.

  • When processing SQL code, it is executed statement by statement using the following logic: read/process/print result.

  • When processing non-SQL code, it is loaded entirely from the input source and executed as a unit.

Use the --interactive (or -i) command-line option to configure MySQL Shell to process the input source as if it were being issued in interactive mode; this enables all the features provided by the Interactive mode to be used in batch processing.

Note

In this case, whatever the source is, it is read line by line and processed using the interactive pipeline.

3.8.3.5.1 Multiple-line Support

It is possible to specify statements over multiple lines. When in Python or JavaScript mode, multiple-line mode is automatically enabled when a block of statements starts like in function definitions, if/then statements, for loops, and so on. In SQL mode multiple line mode starts when the command \ is issued.

Once multiple-line mode is started, the subsequently entered statements are cached.

For example:

mysql-sql> \
... create procedure get_actors()
... begin
...   select first_name from sakila.actor;
... end
...
mysql-sql>

3.8.4 Configuring MySQL Shell

This section explains how to configure MySQL Shell using commands executable from the interactive code editor and command-line options. For a description of MySQL Shell command-line options, see Section 5.5.7, “mysqlsh — The MySQL Shell”.

3.8.4.1 MySQL Shell Commands

MySQL Shell provides commands which enable you to modify the execution environment of the code editor, for example to configure the active programming language or a MySQL Server connection. The following table lists the commands that are available regardless of the currently selected language. As commands need to be available independent of the execution mode, they start with an escape sequence, the \ character.

CommandAlias/ShortcutDescription

\help

\h or \?

Prints help about MySQL Shell commands.

\quit

\q or \exit

Exit MySQL Shell.

\

 

In SQL mode, begin multiple-line mode. Code is cached and executed when an empty line is entered.

\status

 

Show the current MySQL Shell status.

\js

 

Switch execution mode to JavaScript.

\py

 

Switch execution mode to Python.

\sql

 

Switch execution mode to SQL.

\connect

\cx

Connect to a MySQL Server with a URI using an XSession (X Protocol).

\connect_node

\cn

(Removed in version 1.0.4, use \connect -n) Connect to a MySQL Server with a URI using a Node session.

\connect_classic

\cc

(Removed in version 1.0.4, use \connect -c) Connect to a MySQL Server with a URI using a Classic session (MySQL Protocol).

\use

 

Specify the schema to use.

\source

\.

Execute a script file using the active language.

\warnings

\W

Show any warnings generated by a statement.

\nowarnings

\w

Do not show any warnings generated by a statement.

\lsconn

\lsc

Print the connection data for the stored sessions.

\saveconn

\savec

Save connection data of a session, optionaly use -f to force overwriting an existing connection.

\addconn

\addc

(Removed in version 1.0.4, see \saveconn) Store the connection data of a session.

\rmconn

 

Removes a stored session.

\chconn

 

(Removed in version 1.0.4, see \saveconn) Updates a stored session.

Help Command

The \help command can be used with or without parameters. When used without parameters a general help is printed including information about:

  • Available commands.

  • Available commands for the active mode.

When used with a parameter, the parameter must be a valid command. If that is the case, help for that specific command is printed including:

  • Description

  • Supported aliases if any

  • Additional help if any

For example:

\help connect

If the parameter is not a valid command, the general help is printed.

Connect Command

The \connect command is used to connect to a MySQL Server using an URI. This command creates an X Protocol connection by default.

For example:

\connect root@localhost:3306

If a password is required you are prompted for it.

Use the -n option to create a Node session, using the X Protocol to connect to a single server. For example:

\connect -n root@localhost:3306

Use the -c option to create a Classic session, enabling you to use the MySQL Protocol to issue SQL commands directly on a server. For example:

\connect -c root@localhost:3306
Source Command

The \source command is used to execute code from a script at a given path. For example:

\source /tmp/mydata.sql

It can be used to execute either SQL, JavaScript or Python code. The code in the file is executed using the active language, so to process SQL code the MySQL Shell must be in SQL mode.

Warning

As the code is executed using the active language, executing a script in a different language than the currently selected execution mode language could lead to unexpected results.

Use Command

The \use command enables you to choose which schema is active, for example:

\use schema_name

The \use command requires a global development session to be active. If the global development session is an XSession then the \use command only sets db to the object representing the schema_name but does not set a current schema on the database. If the global development session is one of NodeSession or ClassicSession the \use command sets the current schema to the specified schema_name and updates the db variable to the object that represents the selected schema.

3.8.5 Stored Sessions

This section describes how MySQL Shell stores a persistent list of connection data for sessions, identified by a session name.

There are two approaches to manage and use stored connection data:

  • Through MySQL Shell commands.

  • Through a built-in object available for JavaScript and Python.

With these options you can add, update, remove, list and use stored connection data.

The connections are stored as a text file in the MySQL Shell folder, usually found in the user directory.

The file name is stored_sessions.json

In Windows a typical location is:

%APPDATA%\MySQL\mysqlsh\stored_sessions.json

In Unix a typical location is:

~/.mysqlsh/stored_sessions.json

3.8.5.1 MySQL Shell Stored Session Commands

The following commands are used to work with stored sessions within MySQL Shell.

Adding a Connection

To add connection data for a session use:

\saveconn name [URI]
  • name : Mandatory parameter, the name that is assigned to the stored connection data. Note that this must be a valid identifier:

    • Starts with an alphabetic character.

    • Only contains alphanumeric characters and underscore.

  • URI : Optional parameter containing connection data in URI format. If not specified and a global session is active, the URI for the global session is used instead.

  • -f : Optional parameter to force overwriting an existing connection. If an attempt is made to store a connection, but the provided name already exists, an error occurs. Use this parameter to force storing the connection, which overrides what was previously stored under the given session name.

Updating a Stored Connection

To update the connection data for a specific session name use:

\saveconn -f name URI
  • name : Mandatory parameter, indicates the connection to be updated.

  • URI : Mandatory parameter, contains the connection data that replaces the connection for the indicated session name.

Removing a Stored Connection

To remove a connection use:

\rmconn name
  • name : Mandatory parameter that indicates the connection data to be removed.

Listing the Stored Connections

To list the stored connections use:

\lsconn
Using a Stored Connection

Stored connections can be used to create a global session by issuing a \connect name command. The stored session is loaded and used to create the global session.

When using a stored connection through the connect shell commands, you are prompted for the password if it is missing from the stored connection data.

3.8.5.2 Scripting Stored Sessions

A built in object is available to manage and use the stored connections through JavaScript or Python: shell.storedSessions

3.8.5.2.1 Adding a Connection

To add a new connection use:

shell.storedSessions.add(name, connectionData[, override])
  • name : Mandatory parameter, it is the session name that identifies the connection data. This must be a valid identifier.

  • connectionData : Mandatory parameter, it contains the connection information and can be either a string in URI format or a connection data map containing any of the following values:

    • host

    • port

    • dbUser

    • dbPassword

    • schema

    • ssl_ca

    • ssl_cert

    • ssl_key

      To store the connection data at least the host and dbUser parameter should be specified, the rest are optional.

  • override : Optional boolean parameter, used to indicate the desired behavior in case there is a stored connection data associated to the same name. Default value is false.

    • If override is false and connection data with the same name already exists, an error is generated.

    • In the same scenario, if the new connection data overrides the previous data and an error is not generated.

3.8.5.2.2 Updating a Stored Connection

To update the connection data for a specific session name use:

shell.storedSessions.update(name, connectionData)
  • name : Mandatory parameter, it is the name that will identify the connection data. Note that this must be a valid identifier.

  • connectionData : Mandatory parameter, it contains the connection data and can be either a string in URI format or a connection data map.

3.8.5.2.3 Removing a Stored Connection

To remove a connection use:

shell.storedSessions.remove(name)
  • name : Mandatory parameter, identifies the connection data to be removed.

3.8.5.2.4 Listing the Stored Connections

To list the stored connections use:

shell.storedSessions
3.8.5.2.5 Using a Stored Connection

When the shell.storedSessions object is initialized, it loads the stored session data and creates a property to access them as shell.storedSessions.name

var session = mysqlx.getSession(shell.storedSessions.name)

When used this way, if the connection data has no password it is not requested. It is the developers responsibility to provide the password either using the stored session data or as an additional parameter as follows:

var session = mysqlx.getSession(shell.storedSessions.name, password)

3.8.6 MySQL Shell Application Log

This section explains the logging provided by MySQL Shell, where to find logs and how to configure the level of logging.

MySQL Shell can be configured to generate an application log file with information about issues of varying severity. You can use this information to verify the state of MySQL Shell while it is running. The log format is plain text and entries contain a timestamp and description of the problem. For example:

2016-04-05 22:23:01: Error: Default Domain: (shell):1:8: MySQLError: You have an error
in your SQL syntax; check the manual that corresponds to your MySQL server version for
the right syntax to use near '' at line 1 (1064) in session.sql("select * from t
limit").execute().all();

The amount of information to add to the log can be configured using --log-level. See Configuring Logging.

MySQL Shell Log File Location

The location of the log file is the user configuration path and the file is named mysqlsh.log.

Log File on Windows

On Windows, the default path to the log file is %APPDATA%\MySQL\mysqlsh\mysqlsh.log

To find the location of %APPDATA% on your system, echo it from the comand-line. For example:

C:>echo %APPDATA%
C:\Users\exampleuser\AppData\Roaming

On Windows, the path is determined by the result of gathering the %APPDATA% folder specific to that user, and then appending MySQL\mysqlsh. Using the above example results in C:\Users\exampleuser\AppData\Roaming\MySQL\mysqlsh\mysqlsh.log.

Log File on Unix-based Systems

For a machine running Unix, the default path is ~/.mysqlsh/mysqlsh.log where ~ represents your home directory. The environment variable HOME also represents the home directory. Appending .mysqlsh to the this home directory determines the default path to the logs. For example:

shell>echo $HOME
/home/exampleuser

Therefore the location of the MySQL Shell file on this system is /home/exampleuser/.mysqlsh/mysqlsh.log.

These paths can be overridden on all platforms by defining the environment variable MYSQL_USER_CONFIG_PATH. The value of this variable replaces %APPDATA% in Windows or $HOME in Unix.

Configuring Logging

By default, logging is disabled in MySQL Shell. To enable logging use the --log-level command option when starting MySQL Shell. The value assigned to --log-level controls the level of detail in the log. The level of logging can be defined using either numeric levels from 1 to 8, or equivalent named levels as shown in the following table.

Log Level NumberLog Level NameMeaning

1

none

No logging, the default

2

internal

Internal Error

3

error

Errors are logged

4

warning

Warnings are logged

5

info

Information is logged

6

debug

Debug information is logged

7

debug2

Debug with more information is logged

8

debug3

Debug with full information is logged

The numeric and named levels are equivalent. For example there is no difference in logging when starting MySQL Shell in either of these ways:

shell>mysqlsh --log-level=4
shell>mysqlsh --log-level=warning

3.8.7 Customizing MySQL Shell

MySQL Shell offers the ability to customize the behavior and code execution environment through startup scripts, which are executed when the application is first run. Using such scripts enables you to:

  • Add additional search paths for Python or JavaScript modules.

  • Override the default prompt used by the Python and JavaScript modes.

  • Define global functions or variables.

  • Any other possible initialization through JavaScript or Python.

3.8.7.1 Working With Start-Up Scripts

When MySQL Shell enters either into JavaScript or Python mode, it searches for startup scripts to be executed. The startup scripts are JavaScript or Python specific scripts containing the instructions to be executed when the corresponding mode is initialized.

Startup scripts must be named as follows:

  • For JavaScript mode: mysqlshrc.js

  • For Python mode: mysqlshrc.py

MySQL Shell searches the following paths for these files (in order of execution).

On Windows:

  1. %PROGRAMDATA%MySQLmysqlshmysqlshrc.[js|py]

  2. %MYSQLSH_HOME%sharedmysqlshmysqlshrc.[js|py]

  3. <mysqlsh binary path>mysqlshrc.[js|py]

  4. %APPDATA%MySQLmysqlshmysqlshrc.[js|py]

On Linux and OSX:

  1. /etc/mysql/mysqlsh/mysqlshrc.[js|py]

  2. $MYSQLSH_HOME/shared/mysqlsh/mysqlshrc.[js|py]

  3. <mysqlsh binary path>/mysqlshrc.[js|py]

  4. $HOME/.mysqlsh/mysqlshrc.[js|py]

The environment variable MYSQLSH_HOME defines the root folder of a standard setup of MySQL Shell. If MYSQLSH_HOME is not defined it is automatically calculated based on the location of the MySQL Shell binary, therefore on many standard setups it is not required to define MYSQLSH_HOME.

If MYSQLSH_HOME is not defined and the MySQL Shell binary is not in a standard install folder structure, then the path defined in option 3 in the above lists is used. If using a standard install or if MYSQLSH_HOME points to a standard install folder structure, then the path defined in option 3 is not used.

Warning

The lists above also define the order of searching the paths, so if something is defined in two different scripts, the script executed later takes precedence.

3.8.7.2 Adding Module Search Paths

There are two ways to add additional module search paths:

  • Through environment variables

  • Through startup scripts

3.8.7.2.1 Environment Variables

Python uses the PYTHONPATH environment variable to allow extending the search paths for python modules. The value of this variable is a list of paths separated by:

  • A colon character in Linux and OSX

  • A semicolon character in Windows

To achieve this in JavaScript, MySQL Shell supports defining additional JavaScript module paths using the MYSQLSH_JS_MODULE_PATH environment variable. The value of this variable is a list of semicolon separated paths.

3.8.7.2.2 Startup Scripts

The addition of module search paths can be achieved for both languages through the corresponding startup script.

For Python modify the mysqlshrc.py file and append the required paths into the sys.path array.

# Import the sys module
import sys

# Append the additional module paths
sys.path.append('~/custom/python')
sys.path.append('~/other/custom/modules')

For JavaScript the same task is achieved by adding code into the mysqlshrc.js file to append the required paths into the predefined shell.js_module_paths array.

// Append the additional module paths
shell.js.module_paths[shell.js.module_paths.length] = '~/custom/js';
shell.js.module_paths[shell.js.module_paths.length] = '~/other/custom/modules';

3.8.7.3 Overriding the Default Prompt

MySQL Shell uses a default prompt for both Python ( mysql-py> ) and JavaScript ( mysql-js> ).

You can customize the language specific prompt using the shell.custom_prompt() function. This function must return a string that is used as the prompt. To have a custom prompt when MySQL Shell starts, define this function in a startup script. The following example shows how this functionality can be used.

In Python shell.custom_prompt() could be defined as:

# Import the sys module
from time import gmtime, strftime

def my_prompt():
  ret_val = strftime("%H:%M:%S", gmtime())

  if session and session.isOpen():
    data = shell.parseUri(session.getUri())

    ret_val = "%s-%s-%s-py> " % (ret_val, data.dbUser, data.host)
  else:
    ret_val = "%s-disconnected-py> " % ret_val

  return ret_val


shell.custom_prompt = my_prompt

In JavaScript shell.custom_prompt() could be defined as:

shell.custom_prompt = function(){
  var now = new Date();

  var ret_val = now.getHours().toString()+ ":" + now.getMinutes().toString() + ":" + now.getSeconds().toString();

  if (session && session.isOpen()){
    var data = shell.parseUri(session.getUri());

    ret_val += "-" + data.dbUser + "-" + data.host + "-js> ";
  }
  else
    ret_val += "-disconnected-js> ";

  return ret_val;
}

The following example demonstrates using the custom prompt functions defined above in startup script. The prompts show the current system time, and if a session is open the current user and host:

Welcome to MySQL Shell 1.0.4 Development Preview

Copyright (c) 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type '\help', '\h' or '\?' for help.

Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.
14:34:32-disconnected-js> \py
Switching to Python mode...

19:34:39-disconnected-py> \connect root:@localhost
Creating an X Session to root@localhost:33060
No default schema selected.

19:34:50-root-localhost-py> \js
Switching to JavaScript mode...
14:34:57-root-localhost-js>