A little while back, I was doing a code review for one my colleagues - Bryan Stanley - when I saw him using an INSERT syntax that I had never seen before. Rather than the traditional INSERT INTO ... VALUES syntax, he was using an INSERT INTO ... SET syntax. This 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.
Want to use code from this post? Check out the license.