Chapter 20 Using MySQL as a Document Store

Table of Contents

20.1 Interfaces to a MySQL Document Store
20.2 Document Store Concepts
20.3 JavaScript Quick-Start Guide: MySQL Shell for Document Store
20.3.1 MySQL Shell
20.3.2 Download and Import world_x Database
20.3.3 Documents and Collections
20.3.4 Relational Tables
20.3.5 Documents in Tables
20.4 Python Quick-Start Guide: MySQL Shell for Document Store
20.4.1 MySQL Shell
20.4.2 Download and Import world_x Database
20.4.3 Documents and Collections
20.4.4 Relational Tables
20.4.5 Documents in Tables
20.5 X Plugin
20.5.1 Checking X Plugin Installation
20.5.2 Disabling X Plugin
20.5.3 Using Secure Connections with X Plugin
20.5.4 Using X Plugin with the Caching SHA-2 Authentication Plugin
20.5.5 X Plugin Options and Variables
20.5.6 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.

Traditionally, relational databases such as MySQL have usually required a 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. For example, when you create documents describing products, you do not need to know and define all possible attributes of any products before storing and operating with the documents. 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.

To use MySQL as a document store, you use the following server features:

20.1 Interfaces to a MySQL Document Store

To work with MySQL as a document store, you use dedicated components and a choice of clients that support communicating with the MySQL server to develop document based applications.

  • The following MySQL products support X Protocol and enable you to use X DevAPI in your chosen language to develop applications that communicate with a MySQL Server functioning as a document store:

    • MySQL Shell (which provides implementations of X DevAPI in JavaScript and Python)

    • Connector/C++

    • Connector/J

    • Connector/Node.js

    • Connector/NET

    • Connector/Python

  • MySQL Shell is an interactive interface to MySQL supporting JavaScript, Python, or SQL modes. You can use MySQL Shell to prototype applications, execute queries and update data. Installing MySQL Shell has instructions to download and install MySQL Shell.

  • The quick-start guides (tutorials) in this chapter help you to get started using MySQL Shell with MySQL as a document store.

    The quick-start guide for JavaScript is here: Section 20.3, “JavaScript Quick-Start Guide: MySQL Shell for Document Store”.

    The quick-start guide for Python is here: Section 20.4, “Python Quick-Start Guide: MySQL Shell for Document Store”.

  • The MySQL Shell User Guide at MySQL Shell 8.0 (part of MySQL 8.0) provides detailed information about configuring and using MySQL Shell.

20.2 Document Store Concepts

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

JSON Document

A JSON document is a data structure composed of key-value pairs and is the fundamental structure for using MySQL as document store. For example, the world_x schema (installed later in this chapter) contains this document:

{
    "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"
    }
}

This document shows that the values of keys can be simple data types, such as integers or strings, but can also contain other documents, arrays, and lists of documents. For example, the geography key's value consists of multiple key-value pairs. A JSON document is represented internally using the MySQL binary JSON object, through the JSON MySQL datatype.

The most important differences between a document and the tables known from traditional relational databases are that the structure of a document does not have to be defined in advance, and a collection can contain multiple documents with different structures. Relational tables on the other hand require that their structure be defined, and all rows in the table must contain the same columns.

Collection

A collection is a container that is used to store JSON documents in a MySQL database. Applications usually run operations against a collection of documents, for example to find a specific document.

CRUD Operations

The four basic operations that can be issued against a collection are Create, Read, Update and Delete (CRUD). In terms of MySQL this means:

  • Create a new document (insertion or addition)

  • Read one or more documents (queries)

  • Update one or more documents

  • Delete one or more documents

20.3 JavaScript Quick-Start Guide: MySQL Shell for Document Store

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

  • Introduction to MySQL functionality, MySQL Shell, and the world_x example schema.

  • Operations to manage collections and documents.

  • Operations to manage relational tables.

  • Operations that apply to documents within tables.

To follow this quick-start guide you need a MySQL server with X Plugin installed, the default in 8.0, and MySQL Shell to use as the client. MySQL Shell 8.0 (part of MySQL 8.0) provides more in-depth information about MySQL Shell. The Document Store is accessed using X DevAPI, and MySQL Shell provides this API in both JavaScript and Python.

Related Information

20.3.1 MySQL Shell

This quick-start guide assumes a certain level of familiarity with MySQL Shell. The following section is a high level overview, see the xshe documentation for more information. 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 MySQL server instance.

Start MySQL Shell

After you have installed and started MySQL server, connect MySQL Shell to the server instance. You need to know the address of the MySQL server instance you plan to connect to. To be able to use the instance as a Document Store, the server instance must have X Plugin installed and you should connect to the server using X Protocol. For example to connect to the instance ds1.example.com on the default X Protocol port of 33060 use the network string user@ds1.example.com:33060.

Tip

If you connect to the instance using MySQL classic protocol, for example by using the default port of 3306 rather than the mysqlx_port, you cannot use the Document Store functionality shown in this tutorial. For example the db global object is not populated. To use the Document Store, always connect using X Protocol.

If MySQL Shell is not already running, open a terminal window and issue:

mysqlsh user@ds1.example.com:33060/world_x

Alternatively, if MySQL Shell is already running use the \connect command by issuing:

\connect user@ds1.example.com:33060/world_x

You need to specify the address of the MySQL server instance which you want to connect MySQL Shell to. For example in the previous example:

  • user represents the user name of your MySQL account.

  • ds1.example.com is the hostname of the server instance running MySQL. Replace this with the hostname of the MySQL server instance you are using as a Document Store.

  • The default schema for this session is world_x. For instructions on setting up the world_x schema, see Section 20.3.2, “Download and Import world_x Database”.

For more information, see Section 4.2.5, “Connecting to the Server Using URI-Like Strings or Key-Value Pairs”.

Once MySQL Shell opens, the mysql-js> prompt indicates that the active language for this session is JavaScript.

mysql-js>

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 sends the current input line to the server.

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, issue:

mysql-js> \help \connect

Quit MySQL Shell

To quit MySQL Shell, issue the following command:

mysql-js> \quit

Related Information

20.3.2 Download and Import world_x Database

As part of this quick-start guide, an example schema is provided which is referred to as the world_x schema. Many of the examples demonstrate Document Store functionality using this schema. Start your MySQL server so that you can load the world_x schema, then 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. Import the world_x.sql file to your server. You can either:

    • Start MySQL Shell in SQL mode and import the file by issuing:

      mysqlsh -u root --sql --file /tmp/world_x-db/world_x.sql
      Enter password: ****
      
    • Set MySQL Shell to SQL mode while it is running and source the schema file by issuing:

      \sql
      Switching to SQL mode... Commands end with ;
      \source /tmp/world_x-db/world_x.sql
      

    Replace /tmp/ with the path to the world_x.sql file on your system. Enter your password if prompted. A non-root account can be used as long as the account has privileges to create new schemas.

The world_x Schema

The world_x example schema contains the following JSON collection and 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.

Related Information

20.3.3 Documents and Collections

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

The examples in this section use the countryinfo collection in the world_x schema. For instructions on setting up the world_x schema, see Section 20.3.2, “Download and Import world_x Database”.

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 a 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. When you want to run operations against the schema, for example to retrieve a collection, you use methods available for the db variable.
    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.3.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, issue:

mysql-js> db

If the schema value is not Schema:world_x, then set the db variable by issuing:

mysql-js> \use world_x
Create a Collection

To create a new collection in an existing schema, use the db object's createCollection() method. The following example creates a collection called flags in the world_x schema.

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

The method returns a collection object.

<Collection:flags>
List Collections

To display all collections in the world_x schema, use the db object's getCollections() method. Collections returned by the server you are currently connected to appear between brackets.

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

To drop an existing collection from a schema, use the db object's dropCollection() method. For example, to drop the flags collection from the world_x schema, issue:

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

The dropCollection() method is also used in MySQL Shell to drop a relational table from a schema.

Related Information

20.3.3.2 Working with Collections

To work with the collections in a schema, use the db global object to access the current schema. In this example we are using the world_x schema imported previously, and the countryinfo collection. Therefore, the format of the operations you issue is db.collection_name.operation, where collection_name is the name of the collection which the operation is executed against. In the following examples, the operations are executed against the countryinfo collection.

Add a Document

Use the add() method to insert one document or a list of documents into an existing collection. Insert the following document into the countryinfo collection. As this is multi-line content, press Enter twice to insert the document.

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

The method returns the status of the operation.

Each document requires an _id identifier field, and the value must be unique among all documents in the same collection. In MySQL 8.0.11 and higher, document IDs are generated by the server, not the client, so MySQL Shell does not automatically set an _id value. A MySQL server at 8.0.11 or higher sets an _id value if the document does not contain the _id field. A MySQL server at an earlier 8.0 release or at 5.7 does not set an _id value in this situation, so you must specify it explicitly. If you do not, MySQL Shell returns error 5115 Document is missing a required field.

Related Information

20.3.3.3 Find Documents

You can use the find() method to query for and return documents from a collection in a schema. 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 Chapter 12, Functions and Operators. You must enclose all expressions in quotes. For the sake of brevity, some of the examples do not display output.

A simple search condition could consist of the Name field and a value we know is in a document. The following example returns a single document:

mysql-js> db.countryinfo.find("Name = 'Australia'")
[
    {
        "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"
        }
    }
]

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. Therefore 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 use the bind(placeholder, value) 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.

You can use placeholders and the bind() method to create saved searches which you can then call with different values. For example to create a saved search for a country:

mysql-js> var myFind = db.countryinfo.find("Name = :country")
mysql-js> myFind.bind('country', 'France')
[
    {
        "GNP": 1424285,
        "IndepYear": 843,
        "Name": "France",
        "_id": "FRA",
        "demographics": {
            "LifeExpectancy": 78.80000305175781,
            "Population": 59225700
        },
        "geography": {
            "Continent": "Europe",
            "Region": "Western Europe",
            "SurfaceArea": 551500
        },
        "government": {
            "GovernmentForm": "Republic",
            "HeadOfState": "Jacques Chirac"
        }
    }
]
1 document in set (0.0028 sec)

mysql-js> myFind.bind('country', 'Germany')
[
    {
        "GNP": 2133367, 
        "IndepYear": 1955, 
        "Name": "Germany", 
        "_id": "DEU", 
        "demographics": {
            "LifeExpectancy": 77.4000015258789, 
            "Population": 82164700
        }, 
        "geography": {
            "Continent": "Europe", 
            "Region": "Western Europe", 
            "SurfaceArea": 357022
        }, 
        "government": {
            "GovernmentForm": "Federal Republic", 
            "HeadOfState": "Johannes Rau"
        }
    }
]
1 document in set (0.0026 sec)
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.3.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.3.5 Remove Documents

You can use the remove() method to delete some or all documents from a collection in a schema. 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 are 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.3.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.4 Relational Tables

You can also use X DevAPI to work with 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 schema.

Confirm the Schema

To show the schema that is assigned to the db global variable, issue db.

mysql-js> db
<Schema:world_x>

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

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

Show All Tables

To display all relational tables in the world_x schema, use the getTables() method on the db 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.4.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 schema, 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.4.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.4.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.4.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.

Drop a Table

The dropCollection() method is also used in MySQL Shell to drop a relational table from a database. For example, to drop the citytest table from the world_x database, issue:

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

20.3.5 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 Python Quick-Start Guide: MySQL Shell for Document Store

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

  • Introduction to MySQL functionality, MySQL Shell, and the world_x example schema.

  • Operations to manage collections and documents.

  • Operations to manage relational tables.

  • Operations that apply to documents within tables.

To follow this quick-start guide you need a MySQL server with X Plugin installed, the default in 8.0, and MySQL Shell to use as the client. MySQL Shell 8.0 (part of MySQL 8.0) provides more in-depth information about MySQL Shell. The Document Store is accessed using X DevAPI, and MySQL Shell provides this API in both JavaScript and Python.

Related Information

20.4.1 MySQL Shell

This quick-start guide assumes a certain level of familiarity with MySQL Shell. The following section is a high level overview, see the MySQL Shell documentation for more information. MySQL Shell is a unified scripting interface to MySQL Server. It supports scripting in JavaScript and Python. JavaScript is the default processing mode.

Start MySQL Shell

After you have installed and started MySQL server, connect MySQL Shell to the server instance. You need to know the address of the MySQL server instance you plan to connect to. To be able to use the instance as a Document Store, the server instance must have X Plugin installed and you should connect to the server using X Protocol. For example to connect to the instance ds1.example.com on the default mysqlx_port of 33060 use the network string user@ds1.example.com:33060.

Tip

If you connect to the instance using MySQL classic protocol, for example by using the default port of 3306 instead of the mysqlx_port, you cannot use the Document Store functionality shown in this tutorial. For example the db global object is not populated. To use the Document Store, always connect using X Protocol.

If MySQL Shell is not already running, open a terminal window and issue:

mysqlsh user@ds1.example.com:33060/world_x

Alternatively, if MySQL Shell is already running use the \connect command by issuing:

\connect user@ds1.example.com:33060/world_x

You need to specify the address of the MySQL server instance which you want to connect MySQL Shell to. For example in the previous example:

  • user represents the user name of your MySQL account.

  • ds1.example.com is the hostname of the server instance running MySQL. Replace this with the hostname of the MySQL server instance you are using as a Document Store.

  • The default schema for this session is world_x. For instructions on setting up the world_x schema, see Section 20.4.2, “Download and Import world_x Database”.

For more information, see Section 4.2.5, “Connecting to the Server Using URI-Like Strings or Key-Value Pairs”.

Once MySQL Shell opens, the mysql-js> prompt indicates that the active language for this session is JavaScript. To switch MySQL Shell to Python mode, use the \py command.

mysql-js> \py
Switching to Python mode...
mysql-py>

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 sends the current input line to the server.

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, issue:

mysql-py> \help \connect

Quit MySQL Shell

To quit MySQL Shell, issue the following command:

mysql-py> \quit

Related Information

20.4.2 Download and Import world_x Database

As part of this quick-start guide, an example schema is provided which is referred to as the world_x schema. Many of the examples demonstrate Document Store functionality using this schema. Start your MySQL server so that you can load the world_x schema, then 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. Import the world_x.sql file to your server. You can either:

    • Start MySQL Shell in SQL mode and import the file by issuing:

      mysqlsh -u root --sql --file /tmp/world_x-db/world_x.sql
      Enter password: ****
      
    • Set MySQL Shell to SQL mode while it is running and source the schema file by issuing:

      \sql
      Switching to SQL mode... Commands end with ;
      \source /tmp/world_x-db/world_x.sql
      

    Replace /tmp/ with the path to the world_x.sql file on your system. Enter your password if prompted. A non-root account can be used as long as the account has privileges to create new schemas.

The world_x Schema

The world_x example schema contains the following JSON collection and 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.

Related Information

20.4.3 Documents and Collections

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

The examples in this section use the countryinfo collection in the world_x schema. For instructions on setting up the world_x schema, see Section 20.4.2, “Download and Import world_x Database”.

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 a 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. When you want to run operations against the schema, for example to retrieve a collection, you use methods available for the db variable.
    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.3.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, issue:

mysql-py> db

If the schema value is not Schema:world_x, then set the db variable by issuing:

mysql-py> \use world_x
Create a Collection

To create a new collection in an existing schema, use the db object's createCollection() method. The following example creates a collection called flags in the world_x schema.

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

The method returns a collection object.

<Collection:flags>
List Collections

To display all collections in the world_x schema, use the db object's get_collections() method. Collections returned by the server you are currently connected to appear between brackets.

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

To drop an existing collection from a schema, use the db object's drop_collection() method. For example, to drop the flags collection from the current schema, issue:

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

The drop_collection() method is also used in MySQL Shell to drop a relational table from a schema.

Related Information

20.4.3.2 Working with Collections

To work with the collections in a schema, use the db global object to access the current schema. In this example we are using the world_x schema imported previously, and the countryinfo collection. Therefore, the format of the operations you issue is db.collection_name.operation, where collection_name is the name of the collection which the operation is executed against. In the following examples, the operations are executed against the countryinfo collection.

Add a Document

Use the add() method to insert one document or a list of documents into an existing collection. Insert the following document into the countryinfo collection. As this is multi-line content, press Enter twice to insert the document.

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

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. In MySQL 8.0.11 and higher, document IDs are generated by the server, not the client, so MySQL Shell does not automatically set an _id value. A MySQL server at 8.0.11 or higher sets an _id value if the document does not contain the _id field. A MySQL server at an earlier 8.0 release or at 5.7 does not set an _id value in this situation, so you must specify it explicitly. If you do not, MySQL Shell returns error 5115 Document is missing a required field.

Related Information

20.4.3.3 Find Documents

You can use the find() method to query for and return documents from a collection in a schema. 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 Chapter 12, Functions and Operators. You must enclose all expressions in quotes. For the sake of brevity, some of the examples do not display output.

A simple search condition could consist of the Name field and a value we know is in a document. The following example returns a single document:

mysql-py> db.countryinfo.find("Name = 'Australia'")
[
    {
        "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"
        }
    }
]

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. Therefore 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 use the bind(placeholder, value) 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.

You can use placeholders and the bind() method to create saved searches which you can then call with different values. For example to create a saved search for a country:

mysql-py> myFind = db.countryinfo.find("Name = :country")
mysql-py> myFind.bind('country', 'France')
[
    {
        "GNP": 1424285,
        "IndepYear": 843,
        "Name": "France",
        "_id": "FRA",
        "demographics": {
            "LifeExpectancy": 78.80000305175781,
            "Population": 59225700
        },
        "geography": {
            "Continent": "Europe",
            "Region": "Western Europe",
            "SurfaceArea": 551500
        },
        "government": {
            "GovernmentForm": "Republic",
            "HeadOfState": "Jacques Chirac"
        }
    }
]
1 document in set (0.0028 sec)

mysql-py> myFind.bind('country', 'Germany')
[
    {
        "GNP": 2133367, 
        "IndepYear": 1955, 
        "Name": "Germany", 
        "_id": "DEU", 
        "demographics": {
            "LifeExpectancy": 77.4000015258789, 
            "Population": 82164700
        }, 
        "geography": {
            "Continent": "Europe", 
            "Region": "Western Europe", 
            "SurfaceArea": 357022
        }, 
        "government": {
            "GovernmentForm": "Federal Republic", 
            "HeadOfState": "Johannes Rau"
        }
    }
]
1 document in set (0.0026 sec)
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.3.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.3.5 Remove Documents

You can use the remove() method to delete some or all documents from a collection in a schema. 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 are 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.3.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.4 Relational Tables

You can also use X DevAPI to work with 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 schema.

Confirm the Schema

To show the schema that is assigned to the db global variable, issue db.

mysql-py> db
<Schema:world_x>

If the returned value is not Schema:world_x, 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 schema, use the get_tables() method on the db 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.4.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.4.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.4.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.4.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.

Drop a Table

The drop_collection() method is also used in MySQL Shell to drop a relational table from a database. For example, to drop the citytest table from the world_x database, issue:

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

20.4.5 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 schema.

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 X Plugin

This section explains how to use, configure and monitor X Plugin.

20.5.1 Checking X Plugin Installation

X Plugin is enabled by default in MySQL 8, therefore installing or upgrading to MySQL 8 makes the plugin available. You can verify X Plugin is installed on an instance of MySQL server by using the SHOW plugins statement to view the plugins list.

To use MySQL Shell to verify X Plugin is installed, issue:

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

To use MySQL Client to verify X Plugin is installed, issue:

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

An example result if X Plugin is installed is highlighted here:

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

...


| mysqlx                     | ACTIVE   | DAEMON             | NULL    | GPL     |

...

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

20.5.2 Disabling 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.

Alternatively, use the -DWITH_MYSQLX=OFF CMake option to compile MySQL Server without X Plugin.

20.5.3 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.3, “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.5.5.2, “X Plugin Options and System Variables”. 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.3.1, “Configuring MySQL to Use Encrypted Connections” are relevant to configuring X Plugin to use secure connections.

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.5.4 Using X Plugin with the Caching SHA-2 Authentication Plugin

X Plugin supports MySQL accounts created with the Section 6.4.1.2, “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.5.5 X Plugin Options and Variables

This section describes the command options and system variables that 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.5.5.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 Yes Yes
Mysqlx_aborted_clients Yes Global No
Mysqlx_address 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_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_enable_hello_notice 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_init_error Yes Both No
mysqlx_interactive_timeout Yes Yes Yes Global Yes
mysqlx_max_allowed_packet Yes Yes Yes Global Yes
mysqlx_max_connections Yes Yes Yes Global Yes
mysqlx_min_worker_threads Yes Yes Yes Global Yes
Mysqlx_notice_global_sent Yes Both No
Mysqlx_notice_other_sent Yes Both No
Mysqlx_notice_warning_sent Yes Both No
Mysqlx_notified_by_group_replication Yes Both No
Mysqlx_port Yes Global No
mysqlx_port Yes Yes Yes Global No
mysqlx_port_open_timeout Yes Yes Yes Global No
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_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 Yes Global No
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_worker_threads Yes Global No
Mysqlx_worker_threads_active Yes Global No
mysqlx_write_timeout Yes Yes Yes Session Yes

20.5.5.2 X Plugin Options and System Variables

To control activation of X Plugin, use this option:

  • --mysqlx[=value]

    Property Value
    Command-Line Format --mysqlx[=value]
    Introduced 8.0.11
    Type Enumeration
    Default Value ON
    Valid Values

    ON

    OFF

    FORCE

    FORCE_PLUS_PERMANENT

    This option controls how the server loads X Plugin at startup. In MySQL 8.0, X Plugin is enabled by default, but this option may be used to control its activation state.

    The option value should be one of those available for plugin-loading options, as described in Section 5.6.1, “Installing and Uninstalling Plugins”.

If X Plugin is enabled, it exposes several system variables that permit control over its operation:

  • mysqlx_bind_address

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

    The network address on which X Plugin listens for TCP/IP connections. This variable is not dynamic and can be configured only at startup. This is the X Plugin equivalent of the bind_address system variable; see that variable description for more information.

    If mysqlx_bind_address is specified, its value must be a single non-wildcard IP address or host name, or one of the wildcard address formats that permit listening on multiple network interfaces (*, 0.0.0.0, or ::).

    An IP address can be specified as an IPv4 or IPv6 address. If the value is a host name, X Plugin resolves the name to an IP address and binds to that address. If a host name resolves to multiple IP addresses, X Plugin uses the first IPv4 address if there are any, or the first IPv6 address otherwise.

    X Plugin treats different types of addresses as follows:

    • If the address is *, X Plugin accepts TCP/IP connections on all server host IPv4 interfaces, and, if the server host supports IPv6, on all IPv6 interfaces. Use this address to permit both IPv4 and IPv6 connections for X Plugin. 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.

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

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

    If binding to the address fails, X Plugin produces an error and the server does not load it.

  • mysqlx_connect_timeout

    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

    The number of seconds X Plugin waits for the first packet to be received from newly connected clients. This is the X Plugin equivalent of connect_timeout; see that variable for more information.

  • mysqlx_document_id_unique_prefix

    Property Value
    Command-Line Format --mysqlx-document-id-unique-prefix=#
    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 to a unique value per instance, you can ensure document IDs are unique across instances. See Understanding Document IDs.

  • mysqlx_enable_hello_notice

    Property Value
    Command-Line Format --mysqlx-enable-hello-notice[={OFF|ON}]
    System Variable mysqlx_enable_hello_notice
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Boolean
    Default Value ON

    Controls messages sent to classic MySQL protocol clients that try to connect over X Protocol. When enabled, clients which do not support X Protocol that attempt to connect to the server X Protocol port receive an error explaining they are using the wrong protocol.

  • mysqlx_idle_worker_thread_timeout

    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

    The number of seconds after which idle worker threads are terminated.

  • mysqlx_interactive_timeout

    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

    The default value of the mysqlx_wait_timeout session variable for interactive clients. (The number of seconds to wait for interactive clients to timeout.)

  • mysqlx_max_allowed_packet

    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 67108864
    Minimum Value 512
    Maximum Value 1073741824

    The maximum size of network packets that can be received by X Plugin. This is the X Plugin equivalent of max_allowed_packet; see that variable for more information.

  • mysqlx_max_connections

    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

    The maximum number of concurrent client connections X Plugin can accept. This is the X Plugin equivalent of max_connections; see that variable for more information.

    For modifications to this variable, if the new value is smaller than the current number of connections, the new limit is taken into account only for new connections.

  • mysqlx_min_worker_threads

    Property Value
    Command-Line Format --mysqlx-min-worker-threads=#
    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 used by X Plugin for handling client requests.

  • mysqlx_port

    Property Value
    Command-Line Format --mysqlx-port=port_num
    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 on which X Plugin listens for TCP/IP connections. This is the X Plugin equivalent of port; see that variable for more information.

  • mysqlx_port_open_timeout

    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 0
    Maximum Value 120

    The number of seconds X Plugin waits for a TCP/IP port to become free.

  • mysqlx_read_timeout

    Property Value
    Command-Line Format --mysqlx-read-timeout=#
    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

    The 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 path to a Unix socket file which X Plugin uses for connections. This setting is only used by MySQL Server when running on Unix operating systems. Clients can use this socket to connect to MySQL Server using X Plugin.

    The default mysqlx_socket path and file name is based on the default path and file name for the main socket file for MySQL Server, with the addition of an x appended to the file name. The default path and file name for the main socket file is /tmp/mysql.sock, therefore the default path and file name for the X Plugin socket file is /tmp/mysqlx.sock.

    If you specify an alternative path and file name for the main socket file at server startup using the socket system variable, this does not affect the default for the X Plugin socket file. In this situation, if you want to store both sockets at a single path, you must set the mysqlx_socket system variable as well. For example in a configuration file:

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

    If you change the default path and file name for the main socket file at compile time using the MYSQL_UNIX_ADDR compile option, this does affect the default for the X Plugin socket file, which is formed by appending an x to the MYSQL_UNIX_ADDR file name. If you want to set a different default for the X Plugin socket file at compile time, use the MYSQLX_UNIX_ADDR compile option.

    The MYSQLX_UNIX_PORT environment variable can also be used to set a default for the X Plugin socket file at server startup (see Section 4.9, “MySQL Environment Variables”). If you set this environment variable, it overrides the compiled MYSQLX_UNIX_ADDR value, but is overridden by the mysqlx_socket value.

  • mysqlx_ssl_ca

    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

    This is the X Plugin equivalent of ssl_ca; see that variable for more information.

  • mysqlx_ssl_capath

    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

    This is the X Plugin equivalent of ssl_capath; see that variable for more information.

  • mysqlx_ssl_cert

    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

    This is the X Plugin equivalent of ssl_cert; see that variable for more information.

  • mysqlx_ssl_cipher

    Property Value
    Command-Line Format --mysqlx-ssl-cipher=name
    System Variable mysqlx_ssl_cipher
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type String

    The SSL cipher to use for X Protocol connections. This is the X Plugin equivalent of ssl_cipher; see that variable for more information.

  • mysqlx_ssl_crl

    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

    This is the X Plugin equivalent of ssl_crl; see that variable for more information.

  • mysqlx_ssl_crlpath

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

    This is the X Plugin equivalent of ssl_crlpath; see that variable for more information.

  • mysqlx_ssl_key

    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

    This is the X Plugin equivalent of ssl_key; see that variable for more information.

  • mysqlx_wait_timeout

    Property Value
    Command-Line Format --mysqlx-wait-timeout=#
    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

    The 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 noninteractive, 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=#
    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

    The 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.5.6 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.5.6.1 Status Variables for X Plugin

The status variables have the following meanings.