Query Select and Insert if not exists

Question

Are there any good ways to make this createIndexBlock() method shorter?

It looks wastefully long. I have many tables. I don't like to make these kinds of methods.

import (
 "database/sql"
 _ "github.com/go-sql-driver/mysql"
)
var db, _ = sql.Open("mysql", "xxx:xxx@(database:3306)/xx")
func createIndexBlock(blockHash string) int64 {
 rows, err := db.Query("SELECT id FROM index_blocks WHERE `hash`=? LIMIT 1", blockHash)
 if err != nil {
 panic(err.Error())
 }
 for rows.Next() {
 var id int64
 err := rows.Scan(&id)
 if err != nil {
 panic(err.Error())
 }
 rows.Close()
 return id
 }
 result, err := db.Exec("INSERT INTO index_blocks (`hash`) values (?)", blockHash)
 if err != nil {
 panic(err.Error())
 }
 id, err := result.LastInsertId()
 if err != nil {
 panic(err.Error())
 }
 return id
}

• 200_success: As per the How to Ask guidelines, please tell us more about what this code accomplishes, and why you are doing it. What does the definition of the index_blocks table look like, and what is it for?

Answer 1

It seems to me that you are trying to retrieve the ID of a specific blockHash in the database, and if the entry does not exist, you want to create a new entry. This is where INSERT ... ON DUPLICATE KEY UPDATE comes in handy.

INSERT INTO `index_blocks` (`hash`) VALUES (?) 
 ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
SELECT LAST_INSERT_ID();

Here's what you can do:

func createIndexBlock(blockHash string) int64 {
 result, err := db.Exec(`INSERT INTO index_blocks (hash) VALUES (?) 
 ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id)`, blockHash)
 if err != nil {
 panic(err.Error())
 }
 id, err := result.LastInsertId()
 if err != nil {
 panic(err.Error())
 }
 return id
}

Alternatively, you can look into INSERT IGNORE, followed by a SELECT. That will work too.

• Macilias: I wonder what to do if you 1. need to perform an backup of the value ON DUPLICATE KEY as well, 2. would prefer to stay away from mysql only syntax 3. not use store procedures but remain atomic.