Using The INSERT INTO ... SET Syntax In MySQL

Rather than the traditional INSERT INTO ... VALUES syntax, an INSERT INTO ... SET syntax appears to be a MySQL-specific extension to the SQL syntax; but, it's pretty darned cool.

Here's what it would look like to insert a new "friend" record:

	-- Create a new friend record.
	INSERT INTO
	friend
	SET
	name = 'Kim',
	isBFF = true,
	updatedAt = UTC_TIMESTAMP()
	;
	-- Return the newly-generated primary-key.
	SELECT
	( @@Identity ) AS id
	;

The most obvious benefit of this syntax, in my opinion, is that is puts the column names and insert values in very close proximity. This completely eliminates the possibility of providing column names and values in an incorrect order. It also cuts down on the cognitive overhead of understanding the query as you don't need to jump back-and-forth between two different portions of the statement.

So far, I've started using this syntax in some migration scripts. But, I've yet to fully embrace it for the core areas of the application. Since it's not a "standard" part of the SQL syntax, I always feel a little hesitant about embracing it. But, that's just an emotional response; if I'm going to use a particular database storage engine, I might as well benefit from that choice rather than feel limited by it.