Chapter 19 Using MySQL as a Document Store

Table of Contents

19.1 Preproduction Status — Legal Notice
19.2 Key Concepts
19.3 Setting Up MySQL as a Document Store
19.3.1 Installing MySQL Shell
19.3.2 Starting MySQL Shell
19.4 Quick-Start Guide: MySQL Shell for JavaScript
19.4.1 Introduction
19.4.2 Import Database Sample
19.4.3 MySQL Shell
19.4.4 Documents and Collections
19.4.5 Relational Tables
19.4.6 Documents in Tables
19.5 Quick-Start Guide: MySQL Shell for Python
19.5.1 Introduction
19.5.2 Import Database Sample
19.5.3 MySQL Shell
19.5.4 Documents and Collections
19.5.5 Relational Tables
19.5.6 Documents in Tables
19.6 Quick-Start Guide: MySQL for Visual Studio
19.7 X Plugin
19.7.1 Using Secure Connections with X Plugin
19.7.2 X Plugin Options and Variables
19.7.3 Monitoring X Plugin

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 19.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:

19.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.

19.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.

19.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 5.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.

19.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.

19.3.1.1 Installing MySQL Shell on Microsoft Windows

Important

The Community version of MySQL Shell requires the Visual C++ Redistributable for Visual Studio 2013 (available at the Microsoft Download Center) to work; make sure that is installed on your Windows system before installing MySQL Shell.

Note

MySQL Shell is currently not supplied with an MSI Installer. See Installing MySQL Shell Binaries for the manual install procedure.

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 19.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.

Installing MySQL Shell Binaries

To install MySQL Shell binaries:

  1. Unzip the content of the Zip file to the MySQL products directory, for example C:\Program Files\MySQL\.

  2. To be able to start MySQL Shell from a command prompt add the bin directory C:\Program Files\MySQL\mysql-shell-1.0.8-rc-windows-x86-64bit\bin to the PATH system variable.

19.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.

19.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 19.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.)

19.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:

19.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

19.4.1 Introduction

The MySQL Shell for JavaScript quick start provides a short but comprehensive introduction to the database functionality including 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

19.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 with the X Protocol enabled. For instructions, see Section 19.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

19.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 name@localhost/world_x
Creating a Session to 'root@localhost/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 19.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 4.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

19.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 19.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

19.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

19.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

19.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. For example, 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

19.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

19.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

19.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. 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

19.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

19.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

19.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

19.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

19.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

19.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

19.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

19.5.1 Introduction

The MySQL Shell for Python quick start provides a short but comprehensive introduction to the database functionality including 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

19.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 with the X Protocol enabled. For instructions, see Section 19.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

19.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 name@localhost/world_x --py
Creating an Session to name@localhost/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 19.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 4.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

19.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 19.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

19.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

19.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

19.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. For example, 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

19.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

19.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

19.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. 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

19.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

19.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

19.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

19.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

19.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

19.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

19.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.

19.7 X Plugin

This section explains how to configure and monitor the X Plugin. X Plugin implements a network protocol for interacting with MySQL called X Protocol and a Document Store layer. The Document Store enables you to build schema-less applications through SQL or programming interfaces that can be used either as an alternative to SQL, or in combination with SQL, without compromising the ACID compliant storage capabilities of MySQL. When using MySQL as a Document Store, JSON documents can be stored in collections, which can be manipulated using Create, Read, Update, Delete (CRUD) operations. You use the CRUD interface provided by X Plugin through MySQL Shell (see Chapter 18, MySQL Shell User Guide) or through an X DevAPI enabled MySQL connector (see MySQL Connectors). For more information on using X DevAPI see X DevAPI User Guide.

Running X Plugin enables your applications to use the X Protocol to communicate with MySQL. X Protocol is built on the Google Protobuf serialization framework and offers features not available in the classic MySQL protocol, such as pipelining, out-of-band push notifications, expectation blocks and more. For more information on X Protocol see X Protocol. .

19.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 6.4, “Using Secure Connections”.

X Plugin has its 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 19.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 6.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 13.7.1.4, “GRANT Syntax”. Alternatively all X Protocol and MySQL Protocol connections can be forced to use encryption by setting require_secure_transport.

19.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.

19.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 19.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_create_view    YesBothNo
Mysqlx_crud_delete   YesBothNo
Mysqlx_crud_drop_view   YesBothNo
Mysqlx_crud_find   YesBothNo
Mysqlx_crud_insert   YesBothNo
Mysqlx_crud_modify_view    YesBothNo
Mysqlx_crud_update   YesBothNo
Mysqlx_errors_sent   YesBothNo
Mysqlx_errors_unknown_message_type   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_socketYesYesYes 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_ensure_collection   YesBothNo
Mysqlx_stmt_execute_mysqlx   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

19.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 wait 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 wait before retrying 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=file_name
    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
    Command-Line Format--mysqlx-socket=file_name
    System VariableNamemysqlx_socket
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypestring
    Default/tmp/mysqlx.sock

    The socket where X Plugin listens for connections.

19.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.

19.7.3.1 Status Variables for X Plugin

The status variables have the following meanings.