文档主页
X DevAPI 用户指南
下载本手册
PDF (美国标准信纸) - 1.4Mb
PDF (A4) - 1.4Mb
X DevAPI 用户指南  /  使用结果集  /  使用 SQL 结果集

9.5 使用 SQL 结果集

当您使用 sql() 方法在会话上执行 SQL 操作时，将返回一个 SqlResult。迭代 SqlResult 与处理 CRUD 操作的结果相同。以下示例假定 users 表存在。

MySQL Shell JavaScript 代码 

var res = mySession.sql('SELECT name, age FROM users').execute();

var row;
while (row = res.fetchOne()) {
  print('Name: ' + row['name'] + '\n');
  print(' Age: ' + row.age + '\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())
{
  Console.WriteLine("Name: " + res.Current["name"]);
  Console.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();

Row row;
while ((row = res.fetchOne()) != null) {
  System.out.println(" Name: " + row.getString("name") + "\n");
  System.out.println(" Age: " + row.getInt("age") + "\n");
}

C++ 代码 

SqlResult res = mysession.sql("SELECT name, age FROM users").execute();

Row row;
while ((row = res.fetchOne())) {
  cout << "Name: " << row[0] << endl;
  cout << " Age: " << row[1] << endl;
}

SqlResult 与 CRUD 操作返回的结果在结果集和数据集的表示方式上有所不同。一个 SqlResult 将由例如 INSERT 生成的结果集和由例如 SELECT 生成的数据集组合在一起。与 CRUD 操作不同，对于 SqlResult，这两种类型之间没有区别。一个 SqlResult 实例导出用于访问数据和检索最后插入的 ID 或受影响的行数的方法。

使用 hasData() 方法了解一个 SqLResult 是否是数据集或结果。当代码需要编写，而不知道一个 SqlResult 的来源时，此方法很有用。当编写通用应用程序函数来打印查询结果或处理存储过程结果时，可能会出现这种情况。如果 hasData() 返回 true，则 SqlResult 来自 SELECT 或类似的命令，该命令可以返回行。

返回值 true 并不表示数据集包含任何行。如果例如 fetchOne() 返回 NULL 或 fetchAll() 返回一个空列表，则数据集可以为空。并且，如果返回多个结果集，则任何结果集都可能为空。以下示例假定过程 my_proc 存在。

MySQL Shell JavaScript 代码 

var res = mySession.sql('CALL my_proc()').execute();

if (res.hasData()){

  var row = res.fetchOne();
  if (row){
    print('List of rows available for fetching.');
    do {
      print(row);
    } while (row = res.fetchOne());
  }
  else{
    print('Empty list of rows.');
  }
}
else {
  print('No row result.');
}

MySQL Shell Python 代码 

res = mySession.sql('CALL my_proc()').execute()

if res.has_data():

    row = res.fetch_one()
    if row:
        print('List of rows available for fetching.')
        while row:
            print(row)
            row = res.fetch_one()
    else:
        print('Empty list of rows.')
else:
    print('No row result.')

Node.js JavaScript 代码 

mySession.sql('CALL my_proc()')
  .execute()
  .then(function (res) {
    if (!res.hasData()) {
      return console.log('No row result.');
    }

    var row = res.fetchOne();

    if (!row) {
      return console.log('Empty list of rows.');
    }

    console.log('List of rows available for fetching.');

    do {
      console.log(row);
    } while (row = res.fetchOne());
})

C# 代码 

var res = Session.SQL("CALL my_proc()").Execute();

if (res.HasData)
{

  var row = res.FetchOne();
  if (row != null)
  {
    Console.WriteLine("List of rows available for fetching.");
    do
    {
      PrintResult(row);
    } while ((row = res.FetchOne()) != null);
  }
  else
  {
    Console.WriteLine("Empty list of rows.");
  }
}
else
{
  Console.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('List of rows available for fetching.')
        while row:
            print(row)
            row = res.fetch_one()
    else:
        print('Empty list of rows.')
else:
    print('No row result.')

Java 代码 

SqlResult res = mySession.sql("CALL my_proc()").execute();

if (res.hasData()){

  Row row = res.fetchOne();
  if (row != null){
    System.out.println("List of rows available for fetching.");
    do {
     for (int c = 0; c < res.getColumnCount(); c++) {
      System.out.println(row.getString(c));
      }
    } while ((row = res.fetchOne()) != null);
  }
  else{
    System.out.println("Empty list of rows.");
  }
}
else {
  System.out.println("No row result.");
}

C++ 代码 

SqlResult res = mysession.sql("CALL my_proc()").execute();

if (res.hasData())
{
  Row row = res.fetchOne();
  if (row)
  {
    cout << "List of rows available for fetching." << endl;
    do {
      cout << "next row: ";
      for (unsigned i=0 ; i < 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;
}

当 hasData() 指示 SqlResult 不是数据集时，调用 fetchOne() 或 fetchAll() 都是错误的。

MySQL Shell JavaScript 代码 

function print_result(res) {
  if (res.hasData()) {
    // SELECT
    var columns = res.getColumns();
    var record = res.fetchOne();

    while (record){
      for (index in columns){
        print (columns[index].getColumnName() + ": " + record[index] + "\n");
      }

      // Get the next record
      record = res.fetchOne();
    }

  } else {
    // INSERT, UPDATE, DELETE, ...
    print('Rows affected: ' + res.getAffectedItemsCount());
  }
}

print_result(mySession.sql('DELETE FROM users WHERE age < 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 in columns:
        print("%s: %s \n" % (column.get_column_name(), record[index]))
        index = index + 1

      # Get the next record
      record = res.fetch_one()
  else:
    #INSERT, UPDATE, DELETE, ...
    print('Rows affected: %s' % res.get_affected_items_count())

print_result(mySession.sql('DELETE FROM users WHERE age < 30').execute())
print_result(mySession.sql('SELECT * FROM users WHERE age = 40').execute())

Node.js JavaScript 代码 

function print_result(res) {
  if (res.hasData()) {
    // SELECT
    var columns = res.getColumns();
    var record = res.fetchOne();

    while (record) {
      for (index in columns) {
        console.log(columns[index].getColumnName() + ": " + record[index]);
      }

      // Get the next record
      record = res.fetchOne();
    }

  } else {
    // INSERT, UPDATE, DELETE, ...
    console.log('Rows affected: ' + res.getAffectedItemsCount());
  }
}

mySession.sql(`DELETE FROM users WHERE age < 30`)
  .execute()
  .then(function (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, ...
    Console.WriteLine("Rows affected: " + res.RecordsAffected);
  }
}

print_result(Session.SQL("DELETE FROM users WHERE age < 30").Execute());
print_result(Session.SQL("SELECT COUNT(*) AS forty 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

      # Get the next record
      record = res.fetch_one()

  else:
    #INSERT, UPDATE, DELETE, ...
    print('Rows affected: %s' % res.get_affected_items_count())


print_result(mySession.sql('DELETE FROM users WHERE age < 30').execute())
print_result(mySession.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("Rows affected: " + 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++ 代码 

void print_result(SqlResult &&_res)
{
  // Note: We need to store the result somewhere to be able to process it.

  SqlResult res(std::move(_res));

  if (res.hasData())
  {
    // SELECT
    const Columns &columns = res.getColumns();
    Row record = res.fetchOne();

    while (record)
    {
      for (unsigned index=0; index < res.getColumnCount(); ++index)
      {
        cout << columns[index].getColumnName() << ": "
             << record[index] << endl;
      }

      // Get the next record
      record = res.fetchOne();
    }

  }
  else
  {
    // INSERT, UPDATE, DELETE, ...
    // Note: getAffectedItemsCount() not yet implemented in 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 代码 

function print_result(res) {
  if (res.hasData()) {
    // SELECT
    var columns = res.getColumns();
    var record = res.fetchOne();

    while (record){
      for (index in columns){
        print (columns[index].getColumnName() + ": " + record[index] + "\n");
      }

      // Get the next record
      record = res.fetchOne();
    }

  } else {
    // INSERT, UPDATE, DELETE, ...
    print('Rows affected: ' + res.getAffectedItemsCount());
  }
}


var res = mySession.sql('CALL my_proc()').execute();

// Prints each returned result
var more = true;
while (more){
  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 in columns:
        print("%s: %s \n" % (column.get_column_name(), record[index]))
        index = index + 1

      # Get the next record
      record = res.fetch_one()
  else:
    #INSERT, UPDATE, DELETE, ...
    print('Rows affected: %s' % res.get_affected_items_count())

res = mySession.sql('CALL my_proc()').execute()

# Prints each returned result
more = True
while more:
  print_result(res)
  more = res.next_result()

Node.js JavaScript 代码 

function print_result(res) {
  if (res.hasData()) {
    // SELECT
    var columns = res.getColumns();
    var record = res.fetchOne();

    while (record) {
      for (index in columns) {
        console.log(columns[index].getColumnName() + ": " + record[index]);
      }

      // Get the next record
      record = res.fetchOne();
    }

  } else {
    // INSERT, UPDATE, DELETE, ...
    console.log('Rows affected: ' + res.getAffectedItemsCount());
  }
}

mySession.sql('CALL my_proc()')
  .execute()
  .then(function (res) {
    // Prints each returned result
    var more = true;

    while (more) {
      print_result(res);

      more = res.nextResult();
    }
  })

C# 代码 

var res = Session.SQL("CALL my_proc()").Execute();

if (res.HasData)
{
  do
  {
    Console.WriteLine("New resultset");
    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

      # Get the next record
      record = res.fetch_one()
  else:
    #INSERT, UPDATE, DELETE, ...
    print('Rows affected: %s' % res.get_affected_items_count())

res = mySession.sql('CALL my_proc()').execute()

# Prints each returned result
more = True
while more:
  print_result(res)

  more = res.next_result()

Java 代码 

SqlResult res = mySession.sql("CALL my_proc()").execute();

C++ 代码 

SqlResult res = mysession.sql("CALL my_proc()").execute();

while (true)
{
  if (res.hasData())
  {
    cout << "List of rows in the resultset." << endl;
    for (Row row; (row = res.fetchOne());)
    {
      cout << "next row: ";
      for (unsigned i = 0; i < 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(
    function (row) {
      console.log(row);
    },
    function (meta) {
      console.log('Begin of result set number ', resultCount++);
    });

查询执行后，结果集的数量并不立即已知。查询结果可以流式传输到客户端或在客户端进行缓冲。在流式传输或部分缓冲模式下，客户端无法确定查询是否发出多个结果集。


上一个   主页   向上   下一个
下载本手册
PDF (美国标准信纸) - 1.4Mb
PDF (A4) - 1.4Mb