MySQL 5.7 Release Notes
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
Resultobjects, 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();
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 \Gdisplays 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.
[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...]