MySQL's mysql_affected_rows() detects `SELECT INTO` as affected row

Question

Dump of the test database:

-- MariaDB dump 10.19  Distrib 10.9.6-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database: book
-- ------------------------------------------------------
-- Server version   10.9.6-MariaDB
--
-- Table structure for table publisher
CREATE TABLE publisher (
  ID int(10) unsigned NOT NULL AUTO_INCREMENT,
  PublisherName varchar(100) NOT NULL,
  PRIMARY KEY (ID),
  UNIQUE KEY publisher_UN (PublisherName)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
--
-- Dumping data for table publisher
LOCK TABLES publisher WRITE;
INSERT INTO publisher VALUES (1,'TestCase');
UNLOCK TABLES;
--
-- Dumping routines for database 'book'
DELIMITER ;;
CREATE  PROCEDURE ensurePublisher(
    IN v_PublisherName VARCHAR(100)
)
    MODIFIES SQL DATA
BEGIN
    DECLARE pubID INT unsigned;
SELECT ID INTO pubID FROM publisher WHERE PublisherName = v_PublisherName LIMIT 1;

IF ISNULL(pubID) THEN
    INSERT INTO publisher (PublisherName) VALUES (v_PublisherName);
END IF;

END ;;
DELIMITER ;
-- Dump completed on 2023-06-10 15:24:11

Doing a CALL ensurePublisher("TestCase"); query 100 times will return mysql_affected_rows() 1 even though duplicates won't INSERT (unique key on PublisherName).

Is that intended behavior?

Please note that this is a minimal example I came up with to show the issue I have. This one could easily just be INSERT IGNORE INTO.

Delicious Bacon · Answer 1 · 2023-06-11T14:21:21.717

From my investigation, it is because of the SELECT ID INTO pubID FROM publisher WHERE PublisherName = v_PublisherName LIMIT 1; line. That is because, "For SELECT statements, mysql_affected_rows() works like mysql_num_rows().".

However, is that an intended behavior, since I am SELECTing into a Stored Procedure's local variable, which is gone after the procedure ends?

A workaround I've found for my use case is this:

CREATE PROCEDURE `book`.`ensurePublisher`(
    IN `v_PublisherName` VARCHAR(100)
)
    MODIFIES SQL DATA
BEGIN
DECLARE pubID INT unsigned;

SET pubID = (SELECT ID FROM publisher WHERE PublisherName = v_PublisherName LIMIT 1);

IF ISNULL(pubID) THEN
    INSERT IGNORE INTO publisher (PublisherName) VALUES (v_PublisherName);
END IF;


END

SET does not return "affected rows" 1 when an entry already exists.

At first, I used a session variable, but it requires resetting the session variable to NULL at the end of procedure. The reset is needed in that case because of IF ISNULL(@pubID) THEN part would get skipped if previous call during the same session registered ID into @pubID session variable, even if the new procedure's call of SET would reassign it. :shrug:

MySQL's mysql_affected_rows() detects `SELECT INTO` as affected row

-- Table structure for table `publisher`

-- Dumping data for table `publisher`

-- Dumping routines for database 'book'

1 Answers1