本手册下载

9.5使用SQL结果集

类在会话上执行SQL操作时sql ()方法返回一个SqlResult。迭代SqlResult与处理CRUD操作的结果相同。下面的示例假设users表存在。

MySQL Shell JavaScript代码

var res = mySession。sql('SELECT name, age FROM users').execute();var行;而(行= res.fetchOne()){打印(“名字:”+行(“名字”)+ ' \ n ');print('年龄:' +行。年龄+ ' \ n ');}

MySQL Shell Python代码

res = mySession。sql('SELECT name, age FROM users').execute() row = res.fetch_one() while row: print(' name: %s\n' % row[0]) print(' age: %s\n' % row.age) row = res.fetch_one()

node . js JavaScript代码

mySession。sql('SELECT name, age FROM users') .execute() .then(res => {while (row = res. fetchone ()) {console.log(' name: ' + row[0] + '\n');console.log(' Age: ' + row[1] + '\n');}});

或者,你也可以使用回调函数:

mySession。sql('SELECT name, age FROM users') .execute(function (row) {console.log(' name: ' + row[0] + '\n');console.log(' Age: ' + row[1] + '\n');});

c#代码

var res = Session。SQL("SELECT name, age FROM users").Execute();while (res.Next()){控制台。WriteLine("Name: " + res.Current[" Name "]);控制台。WriteLine("Age: " + res.Current[" Age "]);}

Python代码

# Connector/Python res = mySession。sql('SELECT name, age FROM users').execute() row = res.fetch_one() while row: print(' name: %s\n' % row[0]) print(' age: %s\n' % row.age) row = res.fetch_one()

Java代码

SqlResult res = mySession。sql("SELECT name, age FROM users").execute();行一行;while ((row = res.fetchOne()) != null) {System.out. whileprintln(“名字:”+ row.getString(“名字”)+“\ n”);system . out。println(" Age: " + row.getInt(" Age ") + "\n");}

c++代码

SqlResult res = mysession。sql("SELECT name, age FROM users").execute();行一行;在((行= res.fetchOne ())) {cout < <”的名字:“[0]< < < <行endl;cout << " Age: " << row[1] << endl;}

SqlResult与CRUD操作返回的结果在表示结果集和数据集的方式上有所不同。SqlResult组合由以下方式产生的结果集:插入,以及一个数据集,例如,选择在一个。与CRUD操作不同,这两种类型之间没有区别。SqlResult导出用于数据访问和检索最后插入的id或受影响行数的方法。

使用hasData ()方法来了解SqlResult是数据集还是结果。当编写的代码不知道SqlResult的起源时,该方法非常有用。在编写打印查询结果的通用应用程序函数或处理存储过程结果时,可能会出现这种情况。如果hasData ()返回true,那么SqlResult起源于选择或类似的命令,可以返回行。

返回值true并不表示数据集是否包含任何行。数据集可以为空,例如如果为空fetchOne ()返回NULL或fetchAll ()返回一个空列表。下面的示例假设过程my_proc的存在。

MySQL Shell JavaScript代码

var res = mySession。sql('调用my_proc () ') . execute ();if (res.hasData()){var行= res.fetchOne();if (row){print('可获取的行列表');做{打印(行);} while (row = res.fetchOne());} else{print('空行列表');}} else {print('无行结果');}

MySQL Shell Python代码

res = mySession。sql('CALL my_proc()').execute() if res.has_data(): row = res.fetch_one() if row: print('可获取的行列表')while row: print(row) row = res.fetch_one() else: print('空行列表')else: print('无行结果')

node . js JavaScript代码

mySession。sql('CALL my_proc()') .execute() .then(function (res) {if (!res. hasdata()){返回console.log('无行结果');} var row = res.fetchOne();if (!row){返回console.log(“空行列表”);} console.log('可获取的行列表');做{console.log(行);} while (row = res.fetchOne());})

c#代码

var res = Session。SQL(“叫my_proc()”). execute ();if (res.HasData) {var行= res.FetchOne();if (row != null){控制台。WriteLine(“可取的行列表”);做{PrintResult(行);} while ((row = res.FetchOne()) != null);} else{控制台。WriteLine(“空的行列表。”);}}其他{控制台。WriteLine("No row result."); }

Python代码

# Connector/Python res = mySession。sql('CALL my_proc()').execute() if res.has_data(): row = res.fetch_one() if row: print('可获取的行列表')while row: print(row) row = res.fetch_one() else: print('空行列表')else: print('无行结果')

Java代码

SqlResult res = mySession。sql(“叫my_proc()”). execute ();if (res.hasData()){Row Row = res.fetchOne();if (row != null){System.out。println("可获取的行列表。");Do {for (int c = 0;c < res.getColumnCount ();c++) {System.out.println (row.getString (c));}} while ((row = res.fetchOne()) != null);} {system . out。println("Empty list of rows."); } } else { System.out.println("No row result."); }

c++代码

SqlResult res = mysession。sql(“叫my_proc()”). execute ();if (res.hasData()) {Row Row = res.fetchOne();if (row) {cout << "可获取的行列表。"< < endl;Do {cout << "next row: ";For (unsigned i=0;我< row.colCount ();++i) cout << row[i] << ", ";cout < < endl; } while ((row = res.fetchOne())); } else { cout << "Empty list of rows." << endl; } } else { cout << "No row result." << endl; }

调用任何一个都是错误的fetchOne ()fetchAll ()hasResult ()表示SqlResult不是一个数据集。

MySQL Shell JavaScript代码

函数print_result(res) {if (res. hasdata ()) {// SELECT var columns = res. getcolumns ();var记录= res.fetchOne();while (record){for (column中的索引){print (columns[index].getColumnName() + ": " + record[index] + "\n");} //获取下一个记录record = res.fetchOne();}} else {// INSERT, UPDATE, DELETE,…print('受影响的行:' + res.getAffectedItemsCount());}} print_result (mySession。sql('删除用户WHERE年龄< 30').execute());print_result (mySession。sql('SELECT * FROM users WHERE age = 40').execute());

MySQL Shell Python代码

def print_result(res): if res.has_data(): # SELECT columns = res.get_columns() record = res.fetch_one() while record: index = 0 for column: print("%s: %s \n" % (column.get_column_name(), record[index])) index = index + 1 #获取下一个记录record = res.fetch_one() else: #INSERT, UPDATE, DELETE,…打印('受影响的行:%s' % res.get_affected_items_count())sql('DELETE FROM users WHERE age < 30').execute())sql('SELECT * FROM users WHERE age = 40').execute())

node . js JavaScript代码

函数print_result(res) {if (res. hasdata ()) {// SELECT var columns = res. getcolumns ();var记录= res.fetchOne();while (record) {for(列中的索引){console.log(columns[index].getColumnName() + ": " + record[index]);} //获取下一个记录record = res.fetchOne();}} else {// INSERT, UPDATE, DELETE,…console.log('受影响的行:' + res.getAffectedItemsCount());}} mySession。sql('删除用户WHERE年龄< 30 ').execute() .then(函数(res) {print_result(res);});mySession。sql(`SELECT * FROM users WHERE age = 40`) .execute() .then(function (res) { print_result(res); });

c#代码

private void print_result(SqlResult res) {if (res. hasdata) {// SELECT} else {// INSERT, UPDATE, DELETE,…控制台。WriteLine("Rows affected: " + res.RecordsAffected);}} print_result(会话。SQL("DELETE FROM users WHERE age < 30").Execute());print_result(会话。SQL("SELECT COUNT(*) AS 40 FROM users WHERE age = 40").Execute());

Python代码

# Connector/Python def print_result(res): if res.has_data(): # SELECT columns = res.get_columns() record = res.fetch_one() while record: index = 0 for column in columns: print("%s: %s \n" % (column.get_column_name(), record[index])) index = index + 1 #获取下一个记录record = res.fetch_one() else: #INSERT, UPDATE, DELETE,…打印('受影响的行:%s' % res.get_affected_items_count())sql('DELETE FROM users WHERE age < 30').execute())sql('SELECT * FROM users WHERE age = 40').execute())

Java代码

private void print_result(SqlResult res) {if (res. hasdata ()) {// SELECT Row Row;while ((row = res.fetchOne()) != null){for (int c = 0;c < res.getColumnCount ();c++) {System.out.println (row.getString (c));}}} else {// INSERT, UPDATE, DELETE,…system . out。println("受影响的行:" + res.getAffectedItemsCount());}} print_result (mySession。sql("DELETE FROM users WHERE age < 30").execute());print_result (mySession。sql("SELECT COUNT(*) AS forty FROM users WHERE age = 40").execute());

c++代码

{//注意:我们需要将结果存储在某个地方以便能够处理它。SqlResult res (std::移动(_res));if (res.hasData()) {// SELECT const Columns &columns = res.getColumns();行记录= res.fetchOne();While (record) {for (unsigned index=0;指数< res.getColumnCount ();++index) {cout << columns[index].getColumnName() << ": " << record[index] << endl;} //获取下一个记录record = res.fetchOne();}} else {// INSERT, UPDATE, DELETE,…//注意:getAffectedItemsCount()尚未在Connector/ c++中实现。 cout << "No rows in the result" << endl; } } print_result(mysession.sql("DELETE FROM users WHERE age < 30").execute()); print_result(mysession.sql("SELECT * FROM users WHERE age = 40").execute());

调用存储过程可能导致必须在一次执行中处理多个结果集。作为查询执行的结果,返回一个SqlResult对象,它封装了第一个结果集。在处理结果集之后,可以调用nextResult ()向前移动到下一个结果,如果有的话。一旦进入到下一个结果集,它将替换之前加载的结果,然后该结果将变得不可用。

MySQL Shell JavaScript代码

函数print_result(res) {if (res. hasdata ()) {// SELECT var columns = res. getcolumns ();var记录= res.fetchOne();while (record){for (column中的索引){print (columns[index].getColumnName() + ": " + record[index] + "\n");} //获取下一个记录record = res.fetchOne();}} else {// INSERT, UPDATE, DELETE,…print('受影响的行:' + res.getAffectedItemsCount());}} var res = mySession。sql('调用my_proc () ') . execute ();//打印每个返回结果var more = true;而(){print_result (res); more = res.nextResult(); }

MySQL Shell Python代码

def print_result(res): if res.has_data(): # SELECT columns = res.get_columns() record = res.fetch_one() while record: index = 0 for column: print("%s: %s \n" % (column.get_column_name(), record[index])) index = index + 1 #获取下一个记录record = res.fetch_one() else: #INSERT, UPDATE, DELETE,…print('受影响的行:%s' % res.get_affected_items_count()) res = mySession. print('受影响的行:%s' % res.get_affected_items_count())sql('CALL my_proc()').execute() #打印每个返回结果more = True while more: print_result(res) more = res.next_result()

node . js JavaScript代码

函数print_result(res) {if (res. hasdata ()) {// SELECT var columns = res. getcolumns ();var记录= res.fetchOne();while (record) {for(列中的索引){console.log(columns[index].getColumnName() + ": " + record[index]);} //获取下一个记录record = res.fetchOne();}} else {// INSERT, UPDATE, DELETE,…console.log('受影响的行:' + res.getAffectedItemsCount());}} mySession。sql('CALL my_proc()') .execute() .then(function (res){//打印每个返回结果var more = true;While (more) {print_result(res);更= res.nextResult (); } })

c#代码

var res = Session。SQL(“叫my_proc()”). execute ();if (res.HasData) {do{控制台。WriteLine(“新结果集”);while (res.Next()) {Console.WriteLine(res.Current);}} while (res.NextResult());}

Python代码

# Connector/Python def print_result(res): if res.has_data(): # SELECT columns = res.get_columns() record = res.fetch_one() while record: index = 0 for column in columns: print("%s: %s \n" % (column.get_column_name(), record[index])) index = index + 1 #获取下一个记录record = res.fetch_one() else: #INSERT, UPDATE, DELETE,…print('受影响的行:%s' % res.get_affected_items_count()) res = mySession. print('受影响的行:%s' % res.get_affected_items_count())sql('CALL my_proc()').execute() #打印每个返回结果more = True while more: print_result(res) more = res.next_result()

Java代码

SqlResult res = mySession。sql(“叫my_proc()”). execute ();

c++代码

SqlResult res = mysession。sql(“叫my_proc()”). execute ();while (true) {if (res.hasData()) {cout << "结果集中的行列表。"< < endl;(一行一行;(row = res.fetchOne());) {cout << "下一行:";For (unsigned I = 0;我< row.colCount ();++i) cout << row[i] << ", ";cout < < endl; } } else { cout << "No rows in the resultset." << endl; } if (!res.nextResult()) break; cout << "Next resultset." << endl; }

在使用Node.js时,可以使用回调立即返回单个行,回调必须提供给execute ()方法。要标识单个结果集,可以提供第二个回调,它用于标记结果集开始的元数据。

node . js JavaScript代码

Var resultcount = 0;var res = session .sql('CALL my_proc()') .execute(函数(行){console.log(行);},函数(meta) {console.log('结果集编号的开始',resultCount++);});

结果集的数量在查询执行后无法立即得知。查询结果可以流到客户端,也可以在客户端进行缓冲。在流或部分缓冲模式下,客户端无法判断查询是否产生多个结果集。