Chapter 20 Using MySQL as a Document Store

Table of Contents

20.1 Key Concepts
20.2 Setting Up MySQL as a Document Store
20.2.1 Installing MySQL Shell
20.2.2 Starting MySQL Shell
20.3 Quick-Start Guide: MySQL Shell for JavaScript
20.3.1 Introduction
20.3.2 Import Database Sample
20.3.3 MySQL Shell
20.3.4 Documents and Collections
20.3.5 Relational Tables
20.3.6 Documents in Tables
20.4 Quick-Start Guide: MySQL Shell for Python
20.4.1 Introduction
20.4.2 Import Database Sample
20.4.3 MySQL Shell
20.4.4 Documents and Collections
20.4.5 Relational Tables
20.4.6 Documents in Tables
20.5 Quick-Start Guide: MySQL for Visual Studio
20.6 X Plugin
20.6.1 Using Secure Connections with X Plugin
20.6.2 Using X Plugin with the Caching SHA-2 Authentication Plugin
20.6.3 X Plugin Options and Variables
20.6.4 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.

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:

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

20.2 Setting Up MySQL as a Document Store

This section describes the X Plugin, the X Protocol, and how to use MySQL Shell as a MySQL Server client.

The X Plugin is enabled by default and is part of MySQL Server. This plugin implements the X Protocol that is a prerequisite for using MySQL as a document store, and also used by X Protocol clients that connect to MySQL Server.

Note

The X Plugin is enabled by default as of MySQL 8.0 and is statically linked.

Because X Plugin is enabled by default, installing or upgrading to MySQL 8 makes the plugin available. The following shows how to use MySQL Shell and a standard MySQL Client to check if the plugin is installed:

Check the X Plugin Installation

Verify that the X Plugin is installed.

An installed X Plugin shows up in the plugins list on the MySQL server, for example:

MySQL Shell command:

shell> mysqlsh -u user --sqlc -P 3306 -e "SHOW plugins"

MySQL Client program command:

shell> mysql -u user -p -e "SHOW plugins"

A mysqlxplugin; result is highlighted here:

+----------------------------+----------+--------------------+---------+---------+
| Name                       | Status   | Type               | Library | License |
+----------------------------+----------+--------------------+---------+---------+

...


| mysqlx                     | ACTIVE   | DAEMON             | NULL    | GPL     |

...

+----------------------------+----------+--------------------+---------+---------+

Disabling the X Plugin

The X Plugin can be disabled at startup by either setting mysqlx=0 in your MySQL configuration file, or by passing in either --mysqlx=0 or --skip-mysqlx when starting the MySQL server.

20.2.1 Installing MySQL Shell

Important

For the Community and Commercial versions of MySQL Shell: Before installing MySQL Shell, make sure you have the Visual C++ Redistributable for Visual Studio 2015 (available at the Microsoft Download Center) installed on your Windows system.

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 that you can install separately.

Requirements

MySQL Shell is available on Microsoft Windows, Linux, and macOS for 64-bit platforms. MySQL Shell requires the built-in X Plugin that MySQL Server installs and enables by default.

20.2.1.1 Installing MySQL Shell on Microsoft Windows

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

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

  2. When prompted, click Run.

  3. Follow the steps in the Setup Wizard.

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

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 8.0 (which is the default option) as the release series you want.

    • Install MySQL Shell with this command:

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

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 mysql80-community-release.

    • 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 mysql80-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, mysql80-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.

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

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

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

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

20.3 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

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

20.3.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 20.2, “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: ****
    

    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

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

20.3.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 20.3.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 form Description
    db db 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 form Description
    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

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

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

mysql-js> \use world_x
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.

mysql-js> db.createCollection("flags")

The method returns a collection object.

<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

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

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

20.3.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("true").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

20.3.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("true").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("true").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("true") method without specifying any 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

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

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

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

mysql-js> \use world_x

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 form Description
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

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

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

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

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

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

20.4 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

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

20.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 20.2, “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: ****
    

    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

20.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 --py
Creating a 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 20.4.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

20.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 20.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 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 form Description
    db db 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 form Description
    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

20.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-py> db

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

mysql-py> \use world_x
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.

mysql-py> db.create_collection("flags")

The method returns a collection object.

<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

20.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, 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

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

20.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-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(True).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

20.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-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(True).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(True).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(True) 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

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

20.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-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 form Description
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

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

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

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

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

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

20.5 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 start 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.

20.6 X Plugin

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

20.6.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 Encrypted 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 20.6.3.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.1, “Configuring MySQL to Use Encrypted 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.6, “GRANT Syntax”. Alternatively all X Protocol and MySQL Protocol connections can be forced to use encryption by setting require_secure_transport.

20.6.2 Using X Plugin with the Caching SHA-2 Authentication Plugin

X Plugin supports MySQL accounts created with the Section 6.5.1.3, “Caching SHA-2 Pluggable Authentication” plugin. You can use X Plugin to authenticate against such accounts using non-SSL connections with SHA256_MEMORY authentication and SSL connections with PLAIN authentication.

Although the caching SHA-2 authentication plugin holds an authentication cache, to use such accounts with X Plugin the X Plugin authentication cache plugin named mysqlx_cache_cleaner is used. Like X Plugin, it is enabled by default.

Before you can use non-SSL connections to authenticate an account which uses the caching_sha2_password plugin, the account must have authenticated at least once over an SSL connection to store the password in the X Plugin authentication cache. This means that the first use of an account must be done using an SSL connection with the X Plugin authentication cache enabled. Once this initial authentication over SSL has succeeded non-SSL connections can be used.

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

20.6.3.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 20.1 X Plugin Option and Variable Reference

Name Cmd-Line Option File System Var Status Var Var Scope Dynamic
Mysqlx_aborted_clients Yes Global No
Mysqlx_address Yes Global No
mysqlx-bind-address Yes Yes Yes Global No
mysqlx_bind_address Yes Yes Yes Global No
Mysqlx_bytes_received Yes Both No
Mysqlx_bytes_sent Yes Both No
mysqlx-connect-timeout Yes Yes Yes Global Yes
mysqlx_connect_timeout Yes Yes Yes Global Yes
Mysqlx_connection_accept_errors Yes Both No
Mysqlx_connection_errors Yes Both No
Mysqlx_connections_accepted Yes Global No
Mysqlx_connections_closed Yes Global No
Mysqlx_connections_rejected Yes Global No
Mysqlx_crud_create_view Yes Both No
Mysqlx_crud_delete Yes Both No
Mysqlx_crud_drop_view Yes Both No
Mysqlx_crud_find Yes Both No
Mysqlx_crud_insert Yes Both No
Mysqlx_crud_modify_view Yes Both No
Mysqlx_crud_update Yes Both No
mysqlx_document_id_unique_prefix Yes Yes Yes Global Yes
Mysqlx_errors_sent Yes Both No
Mysqlx_errors_unknown_message_type Yes Both No
Mysqlx_expect_close Yes Both No
Mysqlx_expect_open Yes Both No
mysqlx-idle-worker-thread-timeout Yes Yes Yes Global Yes
mysqlx_idle_worker_thread_timeout Yes Yes Yes Global Yes
Mysqlx_init_error Yes Both No
mysqlx-interactive-timeout Yes Yes Yes Global Yes
mysqlx_interactive_timeout Yes Yes Yes Global Yes
mysqlx-max-allowed-packet Yes Yes Yes Global Yes
mysqlx_max_allowed_packet Yes Yes Yes Global Yes
mysqlx-max-connections Yes Yes Yes Global Yes
mysqlx_max_connections Yes Yes Yes Global Yes
mysqlx-min-worker-threads Yes Yes Yes Global Yes
mysqlx_min_worker_threads Yes Yes Yes Global Yes
Mysqlx_notice_other_sent Yes Both No
Mysqlx_notice_warning_sent Yes Both No
Mysqlx_port Yes Global No
mysqlx-port Yes Yes Yes Global No
mysqlx_port Yes Yes Yes Global No
mysqlx-port-open-timeout Yes Yes Yes Global No
mysqlx_port_open_timeout Yes Yes Yes Global No
mysqlx-read-timeout Yes Yes Yes Session Yes
mysqlx_read_timeout Yes Yes Yes Session Yes
Mysqlx_rows_sent Yes Both No
Mysqlx_sessions Yes Global No
Mysqlx_sessions_accepted Yes Global No
Mysqlx_sessions_closed Yes Global No
Mysqlx_sessions_fatal_error Yes Global No
Mysqlx_sessions_killed Yes Global No
Mysqlx_sessions_rejected Yes Global No
Mysqlx_socket Yes Global No
mysqlx-socket Yes Yes Yes Global No
mysqlx_socket Yes Yes Yes Global No
mysqlx-ssl Yes Yes Yes Global No
Mysqlx_ssl_accept_renegotiates Yes Global No
Mysqlx_ssl_accepts Yes Global No
Mysqlx_ssl_active Yes Both No
mysqlx-ssl-ca Yes Yes Yes Global No
mysqlx-ssl-capath Yes Yes Yes Global No
mysqlx-ssl-cert Yes Yes Yes Global No
Mysqlx_ssl_cipher Yes Both No
mysqlx-ssl-cipher Yes Yes
Mysqlx_ssl_cipher_list Yes Both No
mysqlx-ssl-crl Yes Yes Yes Global No
mysqlx-ssl-crlpath Yes Yes Yes Global No
Mysqlx_ssl_ctx_verify_depth Yes Both No
Mysqlx_ssl_ctx_verify_mode Yes Both No
Mysqlx_ssl_finished_accepts Yes Global No
mysqlx-ssl-key Yes Yes Yes Global No
Mysqlx_ssl_server_not_after Yes Global No
Mysqlx_ssl_server_not_before Yes Global No
Mysqlx_ssl_verify_depth Yes Global No
Mysqlx_ssl_verify_mode Yes Global No
Mysqlx_ssl_version Yes Both No
Mysqlx_stmt_create_collection Yes Both No
Mysqlx_stmt_create_collection_index Yes Both No
Mysqlx_stmt_disable_notices Yes Both No
Mysqlx_stmt_drop_collection Yes Both No
Mysqlx_stmt_drop_collection_index Yes Both No
Mysqlx_stmt_enable_notices Yes Both No
Mysqlx_stmt_ensure_collection Yes Both No
Mysqlx_stmt_execute_mysqlx Yes Both No
Mysqlx_stmt_execute_sql Yes Both No
Mysqlx_stmt_execute_xplugin Yes Both No
Mysqlx_stmt_kill_client Yes Both No
Mysqlx_stmt_list_clients Yes Both No
Mysqlx_stmt_list_notices Yes Both No
Mysqlx_stmt_list_objects Yes Both No
Mysqlx_stmt_ping Yes Both No
mysqlx-wait-timeout Yes Yes Yes Session Yes
mysqlx_wait_timeout Yes Yes Yes Session Yes
Mysqlx_worker_threads Yes Global No
Mysqlx_worker_threads_active Yes Global No
mysqlx-write-timeout Yes Yes Yes Session Yes
mysqlx_write_timeout Yes Yes Yes Session Yes

20.6.3.2 X Plugin System Variables and Options

The following command options configure X Plugin:

  • --mysqlx-bind-address[=value]

    Property Value
    Command-Line Format --mysqlx-bind-address=#
    System Variable mysqlx_bind_address
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type string
    Default Value *

    Specifies the network address which X Plugin uses for connections. The equivalent of bind_address for X Plugin.

    By default X Plugin listens for connections on a single IPv4 TCP/IP network socket. Use this option to configure 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]

    Property Value
    Command-Line Format --mysqlx-connect-timeout=#
    System Variable mysqlx_connect_timeout
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 30
    Minimum Value 1
    Maximum Value 1000000000

    Number of seconds X Plugin waits for the first packet to be received from newly connected clients. The X Plugin equivalent of connect_timeout.

  • --mysqlx-idle-worker-thread-timeout[=value]

    Property Value
    Command-Line Format --mysqlx-idle-worker-thread-timeout=#
    System Variable mysqlx_idle_worker_thread_timeout
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 60
    Minimum Value 0
    Maximum Value 3600

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

  • --mysqlx-interactive-timeout=value]

    Property Value
    Command-Line Format --mysqlx-interactive-timeout=#
    Introduced 8.0.4
    System Variable mysqlx_interactive_timeout
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 28800
    Minimum Value 1
    Maximum Value 2147483

    Default value of the mysqlx_wait_timeout session variable for interactive clients.

  • --mysqlx-max-allowed-packet[=value]

    Property Value
    Command-Line Format --mysqlx-max-allowed-packet=#
    System Variable mysqlx_max_allowed_packet
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 1048576
    Minimum Value 512
    Maximum Value 1073741824

    Maxmum size of a network packet that X Plugin can process.

  • --mysqlx-max-connections[=value]

    Property Value
    Command-Line Format --mysqlx-max-connections=#
    System Variable mysqlx_max_connections
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 100
    Minimum Value 1
    Maximum Value 65535

    Maximum number of concurrent client connections the X Plugin can accept. 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]

    Property Value
    Command-Line Format --mysqlx-min-worker-threads=value
    System Variable mysqlx_min_worker_threads
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 2
    Minimum Value 1
    Maximum Value 100

    The minimum number of worker threads the X Plugin uses for handling client requests.

  • --mysqlx-port[=value]

    Property Value
    Command-Line Format --mysqlx-port=#
    System Variable mysqlx_port
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type integer
    Default Value 33060
    Minimum Value 1
    Maximum Value 65535

    Specifies the port where the X Plugin listens for connections. The equivalent of port for X Plugin.

  • --mysqlx-port-open-timeout[=value]

    Property Value
    Command-Line Format --mysqlx-port-open-timeout=#
    System Variable mysqlx_port_open_timeout
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type integer
    Default Value 0
    Minimum Value 1
    Maximum Value 120

    The amount of time in seconds which X Plugin waits for a TCP/IP port to become free.

  • --mysqlx-read-timeout[=value]

    Property Value
    Command-Line Format --mysqlx-read-timeout=#
    Introduced 8.0.4
    System Variable mysqlx_port_read_timeout
    Scope Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 28800
    Minimum Value 30
    Maximum Value 2147483

    Number of seconds that X Plugin waits for blocking read operations to complete. After this time, if the read operation is not successful, the connection is aborted.

  • --mysqlx-socket[=value]

    Property Value
    Command-Line Format --mysqlx-socket=file_name
    System Variable mysqlx_socket
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type string
    Default Value /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]

    Property Value
    Command-Line Format --mysqlx-ssl=#
    System Variable mysqlx_ssl
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type integer

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

  • --mysqlx-ssl-ca[=value]

    Property Value
    Command-Line Format --mysqlx-ssl-ca=file-name
    System Variable mysqlx_ssl_ca
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type file name

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

  • --mysqlx-ssl-capath[=value]

    Property Value
    Command-Line Format --mysqlx-ssl-capath=dir_name
    System Variable mysqlx_ssl_capath
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type directory name

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

  • --mysqlx-ssl-cert[=value]

    Property Value
    Command-Line Format --mysqlx-ssl-cert=name
    System Variable mysqlx_ssl_cert
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type file name

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

  • --mysqlx-ssl-cipher[=value]

    Property Value
    Command-Line Format --mysqlx-ssl-cipher=name
    Type string

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

  • --mysqlx-ssl-crl[=value]

    Property Value
    Command-Line Format --mysqlx-ssl-crl=file_name
    System Variable mysqlx_ssl_crl
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type file name

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

  • --mysqlx-ssl-crlpath[=value]

    Property Value
    Command-Line Format --mysqlx-ssl-crlpath=directory_name
    System Variable mysqlx_ssl_crlpath
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type directory name

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

  • --mysqlx-ssl-key[=value]

    Property Value
    Command-Line Format --mysqlx-ssl-key=file-name
    System Variable mysqlx_ssl_key
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type file name

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

  • --mysqlx-wait-timeout[=value]

    Property Value
    Command-Line Format --mysqlx-wait-timeout=value
    Introduced 8.0.4
    System Variable mysqlx_wait_timeout
    Scope Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 28800
    Minimum Value 1
    Maximum Value 2147483

    Number of seconds that X Plugin waits for activity on a connection. After this time, if the read-operation is not successful, the connection is aborted. If the client is non-interactive, the initial value of the session variable is copied from the global mysqlx_wait_timeout variable. For interactive clients, the initial value is copied from the session mysqlx_interactive_timeout.

  • --mysqlx-write-timeout[=value]

    Property Value
    Command-Line Format --mysqlx-write-timeout=value
    Introduced 8.0.4
    System Variable mysqlx_write_timeout
    Scope Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 60
    Minimum Value 1
    Maximum Value 2147483

    Number of seconds that X Plugin waits for blocking write operations to complete. After this time, if the write operation is not successful, the connection is aborted.

When X Plugin is running the following system variables are available.

  • mysqlx_bind_address

    Property Value
    Command-Line Format --mysqlx-bind-address=value
    System Variable mysqlx_bind_address
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type string
    Default Value *

    The network address which X Plugin uses for connections. The equivalent of bind_address for X Plugin, see that variable for more information. This variable is not dynamic and can only be configured at start up.

  • mysqlx_connect_timeout

    Property Value
    Command-Line Format --mysqlx-connect-timeout=value
    System Variable mysqlx_connect_timeout
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 30
    Minimum Value 1
    Maximum Value 1000000000

    Number of seconds X Plugin waits for the first packet to be received from newly connected clients. The equivalent of connect_timeout for X Plugin.

  • mysqlx_document_id_unique_prefix

    Property Value
    Command-Line Format --mysqlx-document-id-unique-prefix=value
    System Variable mysqlx_document_id_unique_prefix
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 0
    Minimum Value 0
    Maximum Value 65535

    Sets the first 4 bytes of document IDs generated by the server when documents are added to a collection. By setting this variable you can ensure that your documents can be moved between replicasets.

  • mysqlx_idle_worker_thread_timeout

    Property Value
    Command-Line Format --mysqlx-idle-worker-thread-timeout=value
    System Variable mysqlx_idle_worker_thread_timeout
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 60
    Minimum Value 0
    Maximum Value 3600

    Number of seconds after which an idle worker thread is terminated.

  • mysqlx_interactive_timeout

    Property Value
    Command-Line Format --mysqlx-interactive-timeout=value
    Introduced 8.0.4
    System Variable mysqlx_interactive_timeout
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 28800
    Minimum Value 1
    Maximum Value 2147483

    Default value of the mysqlx_wait_timeout session variable for interactive clients.

  • mysqlx_max_allowed_packet

    Property Value
    Command-Line Format --mysqlx-max-allowed-packet=value
    System Variable mysqlx_max_allowed_packet
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 1048576
    Minimum Value 512
    Maximum Value 1073741824

    Maximum size of a network packet that can be received by X Plugin. The X Plugin equivalent of max_allowed_packet.

  • mysqlx_min_worker_threads

    Property Value
    Command-Line Format --mysqlx-min-worker-threads=value
    System Variable mysqlx_min_worker_threads
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 2
    Minimum Value 1
    Maximum Value 100

    Minimum number of worker threads the X Plugin uses for handling client requests.

  • mysqlx_max_connections

    Property Value
    Command-Line Format --mysqlx-max-connections=value
    System Variable mysqlx_max_connections
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 100
    Minimum Value 1
    Maximum Value 65535

    Maximum number of concurrent client connections X Plugin can accept. The X Plugin equivalent of max_connections.

  • mysqlx_port

    Property Value
    Command-Line Format --mysqlx-port=value
    System Variable mysqlx_port
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type integer
    Default Value 33060
    Minimum Value 1
    Maximum Value 65535

    The network port which X Plugin uses for connections. The X Plugin equivalent of port.

  • mysqlx_port_open_timeout

    Property Value
    Command-Line Format --mysqlx-port-open-timeout=value
    System Variable mysqlx_port_open_timeout
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type integer
    Default Value 0
    Minimum Value 0
    Maximum Value 100

    The amount of time in seconds which X Plugin waits for a TCP/IP port to become free.

  • mysqlx_read_timeout

    Property Value
    Command-Line Format --mysqlx-read-timeout=value
    Introduced 8.0.4
    System Variable mysqlx_read_timeout
    Scope Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 28800
    Minimum Value 30
    Maximum Value 2147483

    Number of seconds that X Plugin waits for blocking read operations to complete. After this time, if the read operation is not successful, the connection is aborted.

  • mysqlx_socket

    Property Value
    Command-Line Format --mysqlx-socket=file_name
    System Variable mysqlx_socket
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type string
    Default Value /tmp/mysqlx.sock

    The socket where X Plugin listens for connections.

  • mysqlx_wait_timeout

    Property Value
    Command-Line Format --mysqlx-wait-timeout=value
    Introduced 8.0.4
    System Variable mysqlx_wait_timeout
    Scope Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 28800
    Minimum Value 1
    Maximum Value 2147483

    Number of seconds that X Plugin waits for activity on a connection. After this time, if the read-operation is not successful, the connection is aborted. If the client is non-interactive, the initial value of the session variable is copied from the global mysqlx_wait_timeout variable. For interactive clients, the initial value is copied from the session mysqlx_interactive_timeout.

  • mysqlx_write_timeout

    Property Value
    Command-Line Format --mysqlx-write-timeout=value
    Introduced 8.0.4
    System Variable mysqlx_write_timeout
    Scope Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type integer
    Default Value 60
    Minimum Value 1
    Maximum Value 2147483

    Number of seconds that X Plugin waits for blocking write operations to complete. After this time, if the write operation is not successful, the connection is aborted.

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

20.6.4.1 Status Variables for X Plugin

The status variables have the following meanings.