Proposal to deprecate INSERT and REPLACE alternative syntax

In the MySQL team we are currently considering a proposal to deprecate a number of alternative syntax uses with the INSERT and REPLACE commands. To provide examples:

CREATE TABLE `city` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;

INSERT INTO city SET
 Name='NewCity', CountryCode='CAN',District='MyDistrict',Population=1234;

INSERT INTO city (Name,CountryCode,District,Population) VALUE
 ('NewCity2', 'CAN', 'MyDistrict', 1234);

INSERT city (Name,CountryCode,District,Population) VALUES
 ('NewCity3', 'CAN', 'MyDistrict', 1234);
 
REPLACE INTO city (Name,CountryCode,District,Population) VALUE
 ('NewCity4', 'CAN', 'MyDistrict', 1234);

REPLACE city (Name,CountryCode,District,Population) VALUES
 ('NewCity5', 'CAN', 'MyDistrict', 1234);

To summarize these queries:

  • INSERT using the SET syntax.
  • INSERT and REPLACE using the keyword VALUE instead of VALUES
  • INSERT and REPLACE without the keyword INTO

Our rationale for this proposal is as follows:

  • Having a number of very similar ways of completing the same task makes it very difficult for training, documentation and support. To explain this in more detail: in our manual we have always tried to document every option that the server will accept, but with no functional difference between the options, this makes the content more verbose and clumsy to read.

    MySQL usage becomes cleaner by stating which usage is explicitly preferred, even if the old syntax remains supported for the legacy use-case.

  • The syntax is non-standard and complicates our parser. While it may take some time before we are able to remove these options, by starting the deprecation cycle now we can provide application authors with as much notice as possible.

Our proposed plan is to deprecate the syntax starting with MySQL 5.7. We will assess feedback from our users before targeting a version for syntax removal.

Will you be affected by this change? Please leave a comment, or Get in touch! We’d love to hear from you.

Published by

morgo

I joined MySQL AB in 2006, left, and am now back at Oracle working on the MySQL team. I’ve also worked at Percona and InPowered.

  • In SQL-99, the INTO keyword is required. I had never noticed that MySQL allows this to be optional.

    Likewise VALUES is the standard part of INSERT syntax in the context you show, not VALUE. I had never noticed MySQL allows VALUE in this context. I guess to avoid the appearance of odd English grammar if one inserts a single column. I have never seen anyone use VALUE (the singular).

    I do think the INSERT..SET syntax is useful, and I wish this were standard SQL. It helps readability, and it makes it a lot easier to avoid the error of mismatched number of columns. Although it only supports single-row INSERTs, which I have no problem with.

    • This is good feedback. Thank you!

    • Roy Lyseng

      Hi Bill,

      VALUES is more versatile than using SET. In standard SQL, it is also a clause that can be used wherever SELECT is used. For example, this is possible:

      SELECT * FROM t JOIN (VALUES 1,2,3,4,5,6) AS v(x)) ON t.z=v.x;

      (The VALUES clause in this example represents a table with 6 rows, each with one integer column value).

      Thus, INSERT … SELECT and INSERT … VALUES is actually the same statement. It is just the source that is different. SET will always have limited use compared to this.

      Btw, the third standard way to specify a data source is TABLE:

      SELECT * FROM (TABLE t) AS dt;

      • In spite of it being standard SQL, MySQL does not support that usage of the VALUES clause. That would be a nice feature request. It might be one already. Have you searched bugs.mysql.com?

    • Tony Marston

      > I do think the INSERT..SET syntax is useful, and I wish this were standard SQL.
      I agree 100%. I would NOT like this syntax to be dropped

  • Shane Bester

    I found the INSERT … SET syntax makes it a lot easier to read what columns get what values when you’re inserting data into a table with many many columns.

  • Jörg Brühe

    I think INSERT … SET is the syntax to use if you need to generate the statement, it is far easier to generate one list of “name = value” items than two parallel “name” and “value” lists in sync. Also, it matches the UPDATE syntax pretty close. Because of this, IMO it should not be dropped.

    Regards,
    Jörg

  • Kevin White

    I think the INSERT … SET syntax should be dropped. It’s not a standard syntax for SQL and if you really want your value and key names to be so close together and easier to read then use a SELECT and aliases.

    I also think the INTO should be required and I like the VALUES keyword instead of VALUE.

    If you’re trying to set one single standard way to insert, this is what I prefer and what I most often see people in my organization using.

    • When you INSERT INTO…SELECT, the column names in the SELECT are irrelevant. They match up to the columns of your destination table by position, not by name.

      • Kevin White

        Yes, sorry I wasn’t clear, but if someone is seeking the organization they can select col1 as an alias.

        tableA
        col1|col2|col3

        tableB
        cola|colb|colc

        insert into tableB (cola, colb)
        select col1 as cola, col2 as colb from tableA;

        Hope this clarifies what I meant.

  • Justin Swanhart

    The INSERT … SET syntax is used in mysqlbinlog -v
    It would very very hard to parse RBR without that.

  • j_blotus

    please don’t deprecate INSERT SET! We use it all the time, as it makes setting up prepared statements a bit more visually appealing. name = value much more readable to me

  • Ike Walker

    I think INSERT…SET is useful and would like to see MySQL continue to support it. I have no problem with the other 4 variants being deprecated.

  • Federico Razzoli

    The SET syntax is easier, when you need to insert/replace one rows. Both for humans and programs.
    Anyway, my congratulations: Oracle is doing a great job in showing that it has no idea about what its users think or need.

  • Federico Razzoli

    The SET syntax is easier, when you need to insert/replace one row. Both for humans (when there are many columns) and programs.
    Anyway, my congratulations: Oracle is doing a great job in showing that it has no idea about what its users think or need.

    • > Anyway, my congratulations: Oracle is doing a great job in showing that it has no idea about what its users think or need.

      Rather than making decisions in the dark, we are externalizing this at a very early stage. As I mentioned, this is a proposal we are considering.

      I will leave your comment here, but please consider being friendlier next time. This is not an opportunity to make digs at Oracle.

    • All companies have a challenge to know exactly how their users are using their product. The fact that Oracle is paying an employee specifically to reach out to the user community with questions like this belies your sarcastic comment.

    • How do you do multi-row inserts using the SET syntax?

      • I am not sure if that was a rhetorical question. You can not of course 🙂

        • It was rhetorical, of course 🙂 to make a point that the SQL standard VALUES syntax is more generally useful (and thus, perhaps “easier” from that viewpoint) than the proprietary SET syntax, even if not entirely to Federico’s liking…

  • François Piat

    Oh nooooooooooo. Why change something so cool. This syntax is really very simple and convenient. We can make the same affectations for insert AND update. Our code is much more simple and maintainable, and we never forget something when we change the table structure. It will be really annoying for us if this syntax is removed because we should modify a really great number of code lines.

  • Rob Wultsch

    I also really like the insert… Set syntax. I find it much easier to read and default to it for adhoc queries.

  • Joe Koenig

    Please NOT drop the INSERT…SET syntax! It matches UPDATE and is WAY easier to use. We use it all the time.

  • Michael Widenius

    The INSERT .. SET clause was inspired by MySQL users to make the INSERT easier to read. There is also other databases, like Adabas, that also supports this syntax. (Not sure of which other databases supports it).

    I added the support of VALUE as an alias for VALUES in 2002 because there was some other database/application that used or required this. Unfortunately I don’t remember the exact reason any more.

  • Giuseppe Maxia

    I know many tools that use INSERT … SET to generate SQL statements. Removing this capability will probably break many tools that now work well, and it will make many developers and users upset. I think the additional toll on the parser can be tolerated for this feature.
    Please keep it!

  • Mike Warner

    Please do not deprecate INSERT…SET. Sure it may be a bit more work for the parser, but how much more, really? Given how much safer (less chance of value count mismatch or col/val mismatch) and easier it is to write queries with it, I’ll be less than pleased if you drop it. Many, many, many pieces of software/sites will have to be rewritten if you do.