按参考或按值扫描功能

Question

I have the following code:

statement := `SELECT id from source where mgmt = $1 `
var exists string
errUnique := dr.db.QueryRow(statement, mgmt).Scan(exists)
if errUnique != nil && errUnique != sql.ErrNoRows {
    return errUnique
}

The above code works for me. However, shouldn't my .Scan(&exists) have a by reference? This does not work. On the other hand, when I change exists to a bool as seen here var exists bool the .Scan(&exists) suddenly works. Why isn't the string exists and .Scan(&exists) not working?

Answer 1

You should have exists be the same type as, or a compatible type to, the type of the value you are retrieving from the database.

Since you're selecting the id column, I will assume that it is an integer, and therefore you should declare exists as follows var exists int. If, however, you want to find out whether a row is present in the table or not you would generally use a query of the form:

SELECT EXISTS(SELECT 1 FROM source WHERE mgmt= $1)

(at least in postgres, I'm not sure what db you're using)

EXISTS:

The argument of EXISTS is an arbitrary SELECT statement, or subquery. The subquery is evaluated to determine whether it returns any rows. If it returns at least one row, the result of EXISTS is “true”; if the subquery returns no rows, the result of EXISTS is “false”.

Then your Go code would something like this:

query := `SELECT EXISTS(SELECT 1 FROM source WHERE mgmt = $1)`

var exists bool
if err := dr.db.QueryRow(query, mgmt).Scan(&exists); err != nil {
    return err
}

if exists {
    // already taken
} else {
    // unique
}

Also note that since EXISTS always returns a boolean value you don't have to check the error against sql.ErrNoRows, if you do get an error it would not be that one.