MySQL Tutorial: Selecting Single and Multiple Rows
Welcome to tutorial no. 3 in our MySQL tutorial series. In the previous tutorial, we discussed creating a table and inserting rows into the table. In this tutorial, we will learn how to select a single row as well as multiple rows from a table.
MySQL Series Index
Connecting to MySQL and creating a Database
Creating a Table and Inserting Rows
Selecting Single Row and Multiple rows
Prepared statements - WIP
Updating rows - WIP
Deleting rows - WIP
Selecting a single row
The product table we created in the previous tutorial has three rows. Running select * from product; on the table returns the following rows.
In this section of the tutorial, we will write code to select a single row from this table. We will write a function that will return the product price when the product name is given as the input. The query to do this is provided below.
select product_price from product where product_name = "iphone";
The above query will return one row and will return the product_price column from that row. The above query will return 950
Now that we have the query ready, let’s go ahead and code it.
The first step is to create a prepared statement. Prepared statements are used to parametrize a SQL query so that the same query can be run with different arguments efficiently. It also prevents sql injection.
The template for the prepared statement is provided below.
select product_price from product where product_name = ?
We have replaced iphone with ? to create the prepared statement template.
The next step is to create the prepared statement. This is done using the PrepareContext method.
1func selectPrice(db *sql.DB, productName string) (int, error) {
2 query := `select product_price from product where product_name = ?`
3 ctx, cancelfunc := context.WithTimeout(context.Background(), 5*time.Second)
4 defer cancelfunc()
5 stmt, err := db.PrepareContext(ctx, query)
6 if err != nil {
7 log.Printf("Error %s when preparing SQL statement", err)
8 return 0, err
9 }
10 defer stmt.Close()
11 ...
12}
We pass a context with a 5 second timeout to the PrepareContext method in line no. 5. This method returns a prepared statement. We defer closing the statement in line no. 10.
The next step is to execute this prepared statement. This is done using the QueryRowContext method.
1var price int
2row := stmt.QueryRowContext(ctx, productName)
3if err := row.Scan(&price); err != nil {
4 return 0, err
5}
The QueryRowContext method is called on the prepared statement stmt. This method takes a context and a variadic list of arguments as parameter. In our case, we have only one argument in the query which is the product name and we pass this as the argument to this method.
The QueryRowContext returns a single row. We then need to call Scan on the row to copy the columns returned by the query to the pointer passed to the Scan method. Here we pass an integer pointer in line no. 3 of the above code snippet to which the price will be stored. If the query returns more than one row, the Scan method will return only the first row and there will be no error. If there are no rows returned, Scan will return an error. We will discuss error handling in the next section.
The complete function is provided below.
1func selectPrice(db *sql.DB, productName string) (int, error) {
2 log.Printf("Getting product price")
3 query := `select product_price from product where product_name = ?`
4 ctx, cancelfunc := context.WithTimeout(context.Background(), 5*time.Second)
5 defer cancelfunc()
6 stmt, err := db.PrepareContext(ctx, query)
7 if err != nil {
8 log.Printf("Error %s when preparing SQL statement", err)
9 return 0, err
10 }
11 defer stmt.Close()
12 var price int
13 row := stmt.QueryRowContext(ctx, productName)
14 if err := row.Scan(&price); err != nil {
15 return 0, err
16 }
17 return price, nil
18}
Error handling
The Scan method used in line no. 14 of the above function will return error ErrNoRows when no rows are returned by the query. Let’s call our selectPrice function from main where will handle errors.
1func main() {
2...
3 productName := "iphone"
4 price, err := selectPrice(db, productName)
5 switch {
6 case err == sql.ErrNoRows:
7 log.Printf("Product %s not found in DB", productName)
8 case err != nil:
9 log.Printf("Encountered err %s when fetching price from DB", err)
10 default:
11 log.Printf("Price of %s is %d", productName, price)
12 }
13}
We pass product name as iphone to selectPrice and then switch case on the error. If the error is of type ErrNoRows we print a message saying that the product is not found. In case of any other error, we print the error message. And in the default case we print the result.
This program will print
Price of iphone is 950
Selecting multiple rows
In this section, we will learn how to select multiple rows from a table. We will write a function that will take minimum price and maximum price as parameters and return the name and price of the products that match the query.
The query that does that is provided below.
select product_name, product_price from product where product_price >= 900 && product_price <= 1000;
The above query will return the name and price of all products whose prices fall between 900 and 1000.
Most of the steps for selecting multiple rows are similar to selecting a single row which we discussed in the previous section. I have provided the complete function to select multiple rows below.
1func selectProductsByPrice(db *sql.DB, minPrice int, maxPrice int) ([]product, error) {
2 log.Printf("Getting products by price")
3 query := `select product_name, product_price from product where product_price >= ? && product_price <= ?;`
4 ctx, cancelfunc := context.WithTimeout(context.Background(), 5*time.Second)
5 defer cancelfunc()
6 stmt, err := db.PrepareContext(ctx, query)
7 if err != nil {
8 log.Printf("Error %s when preparing SQL statement", err)
9 return []product{}, err
10 }
11 defer stmt.Close()
12 rows, err := stmt.QueryContext(ctx, minPrice, maxPrice)
13 if err != nil {
14 return []product{}, err
15 }
16 defer rows.Close()
17 var products = []product{}
18 for rows.Next() {
19 var prd product
20 if err := rows.Scan(&prd.name, &prd.price); err != nil {
21 return []product{}, err
22 }
23 products = append(products, prd)
24 }
25 if err := rows.Err(); err != nil {
26 return []product{}, err
27 }
28 return products, nil
29}
Similar to the previous section, we first created a prepared statement in line no. 6 of the above function. After that we call QueryContext method on the prepared statement stmt in line no. 12. This method will return the list of rows selected by the query. We defer closing the rows in line no. 16.
After getting the row list, we call Next method on rows in line no. 18. This prepares the result for reading using the Scan method. If there is any error when calling Next(), it will return false and the for loop will terminate. In case of error, Err() method on rows will return a non nil value and we need to call that to find out if there was any error when Next() was called. This is done in line no. 25.
If there is no error when fetching the rows, rows is closed automatically by the Next() method call in line no. 18. If there is any error, it is necessary to call Close explicitly. Since the call to Close() is idempotent, it’s ok to call it twice i.e once during the successfully Next() call and again during defer in line no. 16. Hence we have deferred the rows close in line no. 16.
In line no. 19 we create prd of type product to store the result.
In case you don’t remember, product struct has the following fields.
1type product struct {
2 name string
3 price int
4}
In line no. 20, the result columns are copied to the name and price fields of the prd struct and in line no. 23, prd is appended to the products slice.
products is returned in line no. 28.
The next step is to call this function from main.
1func main() {
2...
3 minPrice := 900
4 maxPrice := 1000
5 products, err := selectProductsByPrice(db, minPrice, maxPrice)
6 if err != nil {
7 log.Printf("Error %s when selecting product by price", err)
8 return
9 }
10 for _, product := range products {
11 log.Printf("Name: %s Price: %d", product.name, product.price)
12 }
We pass 900 and 1000 as the minimum and maximum price to the selectProductsByPrice function in line no. 5.
If there is any error, we return after printing the error.
If there is no error, the name and price of the products matching the query are printed in line no. 11.
This program will print,
2021/10/16 21:10:52 Getting products by price
2021/10/16 21:10:52 Name: iphone Price: 950
2021/10/16 21:10:52 Name: Galaxy Price: 990
This brings us to the end of this tutorial.
The entire code is available at https://github.com/golangbot/mysqltutorial/blob/master/select/main.go
I hope you liked this tutorial. Please leave your feedback and comments. Please consider sharing this tutorial on twitter and LinkedIn. Have a good day.
Previous tutorial - Creating a Table and Inserting Rows
