Key Conflicts On INSERT Nonetheless Increment AUTO_INCREMENT Worth In MySQL

In terms of database schema design, selecting the correct indexes is a crucial half of the way you architect your ColdFusion functions. Not solely do indexes result in enormously improved efficiency, they will also be used to implement information integrity and drive idempotent workflows. Earlier this yr, I checked out among the strategies that MySQL supplies for gracefully reacting to key-conflicts; however, one factor that I utterly missed in that exploration was the truth that key-conflict errors nonetheless increment the desk’s underlying AUTO_INCREMENT worth.

To see this in motion, let’s create a easy desk with a UNIQUE KEY constraint:

CREATE TABLE `token` (
	`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
	`worth` varchar(50) NOT NULL,
	PRIMARY KEY (`id`),
	UNIQUE KEY `IX_byToken` (`worth`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Right here, our token desk has a single column, worth, which should be distinctive throughout all rows. To see how a key-conflict in MySQL interacts with the AUTO_INCREMENT worth, let’s attempt to insert the identical token worth a number of instances in a row, adopted by a brand new worth. We will then examine the resultant id of the 2 inserted rows.

ASIDE: In Lucee CFML, the createUniqueId() operate returns a small worth that’s distinctive to the present request.

Observe that our INSERT question under is utilizing INSERT INGORE INTO. Which means that once we attempt to insert the identical worth a number of instances, MySQL will merely ignore the insert moderately than throwing a key-conflict error.

<cfscript>

	// Let's clear the tokens desk and reset the AUTO_INCREMENT worth.
	truncateTokens()

	// Create our first token - we all know this can succeed since we simply cleared the desk.
	worth = createUniqueId();
	id1 = createTokenOrIgnore( worth );

	dump( id1 );

	// These will all be no-ops, since we're making an attempt to insert the identical token over and
	// over. As such, ZERO will probably be returned.
	dump( createTokenOrIgnore( worth ) );
	dump( createTokenOrIgnore( worth ) );
	dump( createTokenOrIgnore( worth ) );
	dump( createTokenOrIgnore( worth ) );

	// Now, let's attempt to insert a brand new token, which is able to end in a brand new AUTO_INCREMENT ID.
	id2 = createTokenOrIgnore( worth & "new" );

	dump( id2 );

	// ------------------------------------------------------------------------------- //
	// ------------------------------------------------------------------------------- //

	/**
	* I insert the given token worth and return the related ID. If the token is already
	* within the desk, ZERO is returned.
	*/
	public numeric operate createTokenOrIgnore( required string worth ) {

		```
		<cfquery title="native.outcomes" end result="native.metaResults">
			INSERT IGNORE INTO
				token
			SET
				worth = <cfqueryparam worth="#worth#" sqltype="varchar" />
			;
		</cfquery>
		```

		return( val( metaResults?.generatedKey ) );

	}


	/**
	* I truncate the tokens desk, resetting the AUTO_INCREMENT worth.
	*/
	public void operate truncateTokens() {

		```
		<cfquery title="native.outcomes" end result="native.metaResults">
			TRUNCATE TABLE
				token
			;
		</cfquery>
		```

	}

</cfscript>

Now, once we run this ColdFusion code, we get the next output:

As you’ll be able to see, even when our INSERT INGORE INTO SQL assertion resulted in no new row being inserted, the underlying AUTO_INCREMENT worth on the InnoDB desk was nonetheless elevated. For this reason we’re seeing a spot between the 2 primary-keys even though our ColdFusion demo solely inserted two rows.

This MySQL habits does not hassle me; however, it is good to know that it really works this manner in order that I can higher perceive the information that I see displaying up within the desk. I assume that MySQL is utilizing this strategy for efficiency causes (to extend concurrent operations whereas nonetheless implementing a predictable state).

I ought to lastly be aware that whereas I’m demonstrating this utilizing INSERT IGNORE INTO, the identical habits seems to carry true for any key battle. So, for instance, if I had been to even have an ON DUPLICATE KEY UPDATE assertion, the key-conflict logic would additionally increment the AUTO_INCREMENT worth.

Need to use code from this publish?
Try the license.