How the Go MySQL Driver Returns and Handles Multiple Result Sets from Multi-Statement Executions

The go-sql-driver/mysql driver handles multiple result sets by implementing the driver.RowsNextResultSet interface, using HasNextResultSet() to check the MySQL statusMoreResultsExists flag and NextResultSet() to skip remaining rows and load metadata for the next result set.

The github.com/go-sql-driver/mysql driver enables Go applications to process multi-statement queries that return several independent result sets. When you execute queries like "SELECT 1; SELECT 2", the driver maps MySQL's wire protocol behavior onto the standard database/sql interface, allowing sequential access to each result set through specialized methods implemented in rows.go.

The Driver's Multi-Result Set Interface

The driver exposes multi-result set capabilities through two methods that implement the optional driver.RowsNextResultSet interface. These methods bridge MySQL's wire protocol status flags with Go's database abstraction.

Detecting Additional Result Sets

After each packet that ends a result set, the MySQL server may set the MORE_RESULTS_EXISTS status bit. The driver stores this connection status in mysqlConn.status and exposes detection through HasNextResultSet().

In rows.go, the implementation checks if the connection status flag indicates more results exist:

// rows.go – HasNextResultSet
func (rows *mysqlRows) HasNextResultSet() (b bool) {
    if rows.mc == nil {
        return false
    }
    return rows.mc.status&statusMoreResultsExists != 0
}

This method returns true when the server indicates additional result sets follow the current one.

Navigating Between Result Sets

When the client invokes NextResultSet(), the driver performs several protocol-level operations to advance to the next result set. This involves skipping any unread rows from the current set, checking the status flag, and reading the next result set's metadata.

The binaryRows.NextResultSet() method in rows.go (lines 183-191) implements this logic:

// rows.go – binaryRows.NextResultSet
func (rows *binaryRows) NextResultSet() error {
    resLen, err := rows.nextNotEmptyResultSet()
    if err != nil {
        return err
    }
    rows.rs.columns, err = rows.mc.readColumns(resLen, nil)
    return err
}

The implementation follows the same sequence for text-based rows (textRows.NextResultSet() in lines 205-213). The method:

1. Validates the connection health via rows.mc.error
2. Skips unread rows using rows.mc.skipRows
3. Checks statusMoreResultsExists—if cleared, returns io.EOF
4. Reads the next result set header via resultUnchanged().readResultSetHeaderPacket
5. Retrieves column metadata through mc.readColumns

Automatic Handling in Query Execution

The driver automatically manages result set transitions during query execution, particularly when encountering empty result sets that require immediate advancement to the next set.

Direct Query Execution

In connection.go, the Conn.query method reads the first result set header immediately after execution. When resLen == 0 (indicating no rows), the driver preemptively calls NextResultSet() to position the cursor at the next available result set:

if resLen == 0 {
    rows.rs.done = true
    switch err := rows.NextResultSet(); err {
    case nil, io.EOF:
        return rows, nil
    default:
        return nil, err
    }
}

This logic ensures that callers never receive an empty first result set when subsequent sets contain data.

Prepared Statement Execution

Prepared statements follow the identical pattern in statement.go. The Stmt.query method checks for zero-length results and immediately advances to the next set:

if resLen == 0 {
    rows.rs.done = true
    switch err := rows.NextResultSet(); err {
    case nil, io.EOF:
        return rows, nil
    default:
        return nil, err
    }
}

Both implementations ensure consistent behavior whether executing raw SQL or parameterized queries.

Application Usage Pattern

To access multiple result sets in application code, you must enable multi-statement support in the DSN using multiStatements=true, then use the NextResultSet() method on *sql.Rows:

package main

import (
    "database/sql"
    "log"

    _ "github.com/go-sql-driver/mysql"
)

func main() {
    dsn := "user:password@tcp(127.0.0.1:3306)/test?multiStatements=true"
    db, err := sql.Open("mysql", dsn)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Multi-statement query that yields two result sets
    rows, err := db.Query(`
        SELECT id, name FROM users WHERE id < 3;
        SELECT COUNT(*) FROM users;
    `)
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    // First result set
    for rows.Next() {
        var id int
        var name string
        if err := rows.Scan(&id, &name); err != nil {
            log.Fatal(err)
        }
        log.Printf("user: %d %s", id, name)
    }
    if err = rows.Err(); err != nil {
        log.Fatal(err)
    }

    // Advance to the second result set
    if rows.NextResultSet() {
        if rows.Next() {
            var cnt int
            if err := rows.Scan(&cnt); err != nil {
                log.Fatal(err)
            }
            log.Printf("total users: %d", cnt)
        }
    }
}

The rows.NextResultSet() call forwards to the driver's implementation, which manages the underlying protocol navigation described in the previous sections.

Summary

• Protocol Integration: The driver checks mysqlConn.status for the statusMoreResultsExists bit to detect additional result sets from multi-statement executions.
• Interface Implementation: HasNextResultSet() and NextResultSet() in rows.go provide the standard driver.RowsNextResultSet interface required by database/sql.
• Empty Set Handling: Both connection.go and statement.go automatically advance past empty result sets (where resLen == 0) to ensure the application receives the first non-empty set.
• Metadata Loading: NextResultSet() skips remaining rows via skipRows, then calls readColumns to populate the next result set's column definitions.
• DSN Requirement: Multi-statement support requires multiStatements=true in the connection string to enable server-side parsing of multiple statements.

Frequently Asked Questions

How do I enable multi-statement support in go-sql-driver/mysql?

You must add multiStatements=true to the DSN connection string. Without this parameter, the MySQL server rejects queries containing multiple statements for security reasons. The driver default is false to prevent SQL injection vulnerabilities from unintended multi-statement execution.

What happens if a result set in the sequence contains no rows?

The driver automatically advances to the next result set when it encounters an empty result set during initial query execution. In both connection.go and statement.go, when resLen == 0, the driver immediately calls NextResultSet() before returning the Rows object to ensure the application receives the first available data set.

How does the driver signal that no more result sets exist?

When NextResultSet() detects that the statusMoreResultsExists flag is cleared (no additional results pending), it returns io.EOF. This causes the exported sql.Rows.NextResultSet() method to return false, indicating to the application that all result sets have been consumed.

Can I mix SELECT statements with INSERT/UPDATE in multi-statement queries?

Yes, the driver handles any combination of statements that return result sets. However, statements that don't return rows (like INSERT) produce empty result sets, which the driver handles by automatically advancing to the next set or returning io.EOF if no results remain. Always check rows.Err() after iterating each result set to catch errors from non-SELECT statements.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}