Using run_sql and sql Functions in MySQL Shell: Differences and Practical Examples

MySQL Shell is a modern replacement for the traditional MySQL command‑line client that supports SQL, JavaScript, and Python, and includes many components to simplify DBA tasks.

The article focuses on two MySQLX component functions— run_sql and sql —highlighting their distinct behaviors and usage patterns.

run_sql executes any MySQL‑compatible SQL statement directly and returns a SqlResult object. The article shows how to connect to the X protocol port (33060), create a table, insert data, and run queries, including examples of binding variables with an array.

root@ytt-pc-cheap:/home/ytt# mysqlsh mysqlx:/root@localhost:33060/ytt --py
MySQL Shell 8.0.30
...
c1=db.get_session()
c1.run_sql("create table t1(id int auto_increment primary key, r1 int)")
c1.run_sql("insert into t1(r1) values (10),(20),(30)")
c1.run_sql("table t1")

It also demonstrates assigning the result to a variable ( r1 ) to access methods such as has_data() , get_execution_time() , and fetch_one() .

sql differs by returning a SqlExecute object, representing the execution stage before a SqlResult is produced. Assigning the result to a variable ( r2 ) allows repeated execution of the same query, and calling execute() converts it to a SqlResult ( r3 ) that can be fetched once.

r2=c1.sql("table t1")
r2          # executes and shows result
r3=r2.execute()
r3.fetch_all()

The sql function also supports binding variables, enabling parameterized queries with arrays.

r2=c1.sql("select * from t1 where r1 in (?,?,?)")
r2.bind([10,20,30])
r2.bind([40,50,30])

Conclusion : Choose run_sql when you need a one‑time result set (SqlResult) and straightforward execution, and use sql when you require repeated execution or want to work with the SqlExecute stage before fetching results.