Insertion the MySQL way

Today I want to talk about SQL's INSERT statement. For that, I'll be using this sample table.

CREATE TABLE TestTable (
    id int,
    name varchar(100),  
    address varchar(200),
    occupation varchar(100),
    gender varchar(1),
    rating decimal
);

There are a few very basic ways to use an INSERT statement, the most common of which would be:

INSERT INTO TestTable (id, name, address, occupation, gender, rating)
VALUES
(1, "Brews Lee", "11 Whompoa Lane", "Tea Merchant", "M", 0.5)

and the multiple insert version:

INSERT INTO TestTable (id, name, address, occupation, gender, rating)
VALUES
(1, "Brews Lee", "11 Whompoa Lane", "Tea Merchant", "M", 0.5),
(2, "Speedy Lee", "11 Whompoa Lane", "Football Coach", "M", 0.6),
(3, "Tan Jin Koo", "105 West Coast Drive", "Waiter", "M", 0.2),
(4, "Lara Bte Suparman", "65 Serangoon Avenue", "Cosplayer", "F", 2.5),
(5, "Jusdeep Singh", "7 Whitley Road", "Philosopher", "F", 1.5)

This is probably the safest and most generic way to insert a record into a table. You do it this way, it's almost impossible to screw up. Of course, it's a little verbose and in the case of tables with a lot of fields, specifying a value for each field can be a royal pain in the behind because you'll have to match the list of values against the list of parameters.

There's a shorter version of this.

INSERT INTO TestTable
VALUES
(1, "Brews Lee", "11 Whompoa Lane", "Tea Merchant", "M", 0.5)

Now this I don't recommend because while it takes less typing, it's way less robust than the first example.

Firstly, this means you'd need to specify values for all fields in the table. If you don't, like in the example below...

INSERT INTO TestTable
VALUES
(1, "Brews Lee", "11 Whompoa Lane", 0.5)

...this error occurs.

Column name or number of supplied values does not match table definition.

Secondly, if you altered the table by, for example, adding a field to it, the first example would still be valid (for the most part, depending on what mode you're operating in.) but the second example would cause things to break immediately.

Ditto if you change the order in which the fields appear in the table. Try this - delete the table and recreate it, but swop the positions of the last two fields.

CREATE TABLE TestTable (
    id int,
    name varchar(100),
    address varchar(200),
    occupation varchar(100),
    rating decimal,
    gender varchar(1)
);

Then try this SQL statement again. You'll get an error the system is now expecting a decimal and character value, but getting "M" and "0.5" respectively.

INSERT INTO TestTable (id, name, address, occupation, gender, rating)
VALUES
(1, "Brews Lee", "11 Whompoa Lane", "Tea Merchant", "M", 0.5)

The MySQL-specific version

Now, sometime years back, I discovered another way, quite by accident. I copied and pasted an UPDATE statement like the one below...

UPDATE TestTable SET
name = "Sincere Lee",
occupation = "Politician"
WHERE id = 2

...and modified it into an INSERT statement. This shouldn't have worked, but it did. Beautifully.

INSERT INTO TestTable SET
name = "Sincere Lee",
address = "11 Whompoa Lane",
occupation = "Politician",
gender = "M",
rating = 1.2

Upon further examination, this syntax is particular to MySQL only, and not standard SQL. And I have mixed feelings about this one. It's elegant, more robust than the other examples, and close enough to the UPDATE statement to make it relatable. It, of course, won't do multiple inserts like the second example. But for singular inserts, this is gold. Since you've specified what value goes into which column, there's no confusion even if you get the sequence wrong or miss out a value (provided, of course, the table is designed to allow empty or null values).

The main problem here is that it's non-standard syntax, which gives rise to two problems.

Using this too often will turn it into a habit, which may work against you if you have to work on other databases such as SQL Server or Oracle. It's generally encouraged to use standard SQL as much as possible.

If you should ever need to convert your existing back-end to some solution other than MySQL, this will be a sticking point. But, you know, if you chose MySQL to be your back-end, you probably had a really good reason to. So to commit to your decision, using MySQL-specific features would serve to reinforce that decision. Conversely, if you were just trying stuff out, the more standard the better.

Most Sincere Lee,
T___T