Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 37.2Mb
PDF (A4) - 37.2Mb
PDF (RPM) - 36.6Mb
HTML Download (TGZ) - 10.1Mb
HTML Download (Zip) - 10.2Mb
HTML Download (RPM) - 8.8Mb
Man Pages (TGZ) - 206.0Kb
Man Pages (Zip) - 313.0Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

3.8.3.1 Interactive Code Execution

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

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

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

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

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

    • Collection.add()

    • Collection.find()

    • Collection.remove()

    • Collection.modify()

    • Table.insert()

    • Table.select()

    • Table.delete()

    • Table.update()

    • NodeSession.sql()

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

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

    • The SQL execution and CRUD operations (listed above)

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

      • startTransaction()

      • commit()

      • rollback()

      • dropSchema()

      • dropTable()

      • dropCollection()

      • dropView()

      • ClassicSession.runSql()

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

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

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

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

When executing SQL using MySQL Shell you can display results in a column-per-row format with the \G command, in a similar way to mysql. If a statement is terminated with \G instead of the active delimiter (which defaults to ;), it is executed by the server and the results are displayed in vertical format, regardless of the current default output format. For example issuing a statement such as

SELECT * FROM mysql.user \G

displays the results vertically.

Multiple SQL statements on the same line which are separated by \G are executed separately as if they appeared one per line., for example

select 1\Gselect 2\Gselect 3\G

In other words \G functions as a normal delimiter.


User Comments
  Posted by Wagner Bianchi on April 18, 2016
After creating the world_x database, execute the below to give you fast results when testing your environment:

[wb@mysqldocstoresrv01 ~]$ mysqlsh -u root -p world_x
Creating an X Session to root@localhost:33060/world_x
Enter password:
Default schema `world_x` accessible through db.

Welcome to MySQL Shell 1.0.3 Development Preview

Copyright (c) 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type '\help', '\h' or '\?' for help.

Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.
mysql-js>mysql-js> var mySession = mysqlx.getSession('root:P@ssw0rd@localhost');

mysql-js> mySession.world_x.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"
}
},
[...snip...]
Sign Up Login You must be logged in to post a comment.