Using MySQL Shell to Operate Relational Tables: Examples with MySQL, MySQL X, and SHELL Components

The article introduces MySQL Shell as a versatile tool that can manipulate both document and relational data, focusing on three components: the classic MySQL component, the MySQL X component (based on the X Dev protocol), and the SHELL component that can switch between the two.

MySQL component : Shows how to connect using mysql.get_session or a dictionary, then run SQL statements with run_sql to query, insert, update, delete, start transactions, and close the connection. Sample code snippets illustrate each operation on the city table of the SAMPLE DATABASE WORLD.

MySQL X component : Demonstrates connecting via the X protocol, executing plain SQL, using the SQLRESULT class for fetching rows, the SqlExecute class for prepared‑statement style queries with bound variables, and the Table class (including its sub‑classes TableSelect, TableInsert, TableUpdate, TableDelete) for object‑oriented DML operations. Examples cover selecting cities with population < 800, inserting single and multiple rows, updating, and deleting records.

SHELL component : Explains that the SHELL component can freely switch between MySQL and MySQL X sessions and provides a default db object representing the current schema. It shows how to obtain a Table object via db.get_table("city") and perform the same queries as with MySQL X.

The article concludes that using the SHELL component is the most flexible way to operate relational tables in MySQL Shell and provides several recommended reading links for further exploration.