cbess/mysql-sp.md

## mysql-sp.md

      
    Raw
  

              mysql-sp.md
            
          
    A stored procedure that saves (insert/update) a URL, then returns the id for the record.
Works in Go v1.11.6+ and MySQL 5.7+.
DELIMITER ;;

CREATE DEFINER=`root`@`%` PROCEDURE SaveUrl(
    IN p_url varchar(8200),
    IN p_title text
)
BEGIN
    DECLARE v_record_id bigint(20) DEFAULT NULL;

    -- look for url in table
    SELECT id INTO v_record_id
    FROM pages
    WHERE url = p_url;
    
    IF v_record_id IS NULL THEN
        INSERT INTO pages (url, title)
        VALUES (p_url, p_title);

        -- now get the record id inserted
        SELECT LAST_INSERT_ID() INTO v_record_id;
    ELSE
        UPDATE pages
        SET 
            title = p_title
        WHERE 
            id = v_record_id;
    END IF;
    
    -- return one result set
    SELECT v_record_id AS id;
END;;

DELIMITER ;
The key is the connection string. Make sure multiStatements=true and autocommit=true are present.
myDb, err := sql.Open("mysql", "user:password@/somedb?multiStatements=true&autocommit=true")
A function body that calls the above stored procedure, then returns the ID from the stored procedure.
// save url data
var id sql.NullInt64
row := myDb.QueryRow(
  "CALL SaveUrl(?, ?)",
  data.URL,
  data.Title,
)

if err := row.Scan(&id); err != nil {
  return -1, fmt.Errorf("unable to save URL: %s", err)
}

if !id.Valid || id.Int64 == 0 {
  return -1, errors.New("invalid ID value")
}

return id.Int64, nil
Soli Deo Gloria