Basic SQL - Learning MySQL - page 165

+------------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
Finally, insert the tracks:
mysql> INSERT INTO track VALUES (1, "Diamonds", 7, 1, 4.10),
-> (2, "Boppin Out / Eternal Morning", 7, 1, 3.22),
-> (3, "Splat Goes the Cat", 7, 1, 1.39),
-> (4, "From Rusholme With Love", 7, 1, 3.59);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
Here, we've used a different INSERT style to add all four tracks in a single SQL query.
This style is recommended when you want to load more than one row. It has a similar
format to the single-insertion style, except that the values for several rows are collected
together in a comma-separated list. Giving MySQL all the data you want to insert in
one statement helps it optimize the insertion process, allowing queries that use this
syntax to be typically many times faster than repeated insertions of single rows. There
are other ways to speed up insertion, and we discuss several in Chapter 6.
The single-row INSERT style is unforgiving: if it finds a duplicate, it'll stop as soon as it
finds a duplicate key. For example, suppose we try to insert the same tracks again:
mysql> INSERT INTO track VALUES (1, "Diamonds", 7, 1, 4.10),
-> (2, "Boppin Out / Eternal Morning", 7, 1, 3.22),
-> (3, "Splat Goes the Cat", 7, 1, 1.39),
-> (4, "From Rusholme With Love", 7, 1, 3.59);
ERROR 1062 (23000): Duplicate entry '7-1-1' for key 1
The INSERT operation stops on the first duplicate key. You can add an IGNORE clause to
prevent the error if you want:
mysql> INSERT IGNORE INTO track VALUES (1, "Diamonds", 7, 1, 4.10),
-> (2, "Boppin Out / Eternal Morning", 7, 1, 3.22),
-> (3, "Splat Goes the Cat", 7, 1, 1.39),
-> (4, "From Rusholme With Love", 7, 1, 3.59);
Query OK, 0 rows affected (0.01 sec)
Records: 4 Duplicates: 4 Warnings: 0
However, in most cases, you want to know about possible problems (after all, primary
keys are supposed to be unique), and so this IGNORE syntax is rarely used.
You'll notice that MySQL reports the results of bulk insertion differently from single
insertion. From our initial bulk insertion, it reports:
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
The first line tells you how many rows were inserted, while the first entry in the final
line tells you how many rows (or records) were actually processed. If you use INSERT
IGNORE and try to insert a duplicate record—for which the primary key matches that
of an existing row—then MySQL will quietly skip inserting it and report it as a duplicate
in the second entry on the final line: