Databases Reference
In-Depth Information
Query OK, 0 rows affected (0.00 sec)
Records: 1 Duplicates: 1 Warnings: 0
MySQL doesn't complain, but it does report that it encountered a duplicate. Prior to
MySQL 4.0.1, the
IGNORE
mode was the default behavior, but for later versions, you
have to add the keyword if you want duplicates to be ignored.
Finally, note that for versions of MySQL older than 4.0.14, you couldn't insert into a
table that's listed in the
SELECT
statement, since the
SELECT
would find the newly inserted
rows and try to insert them again. On newer systems, you still need to avoid duplicate
primary keys:
mysql>
INSERT INTO artist SELECT artist_id,artist_name FROM artist;
ERROR 1062 (23000): Duplicate entry '1' for key 1
but you can modify values in the
SELECT
statement to get a different primary key value
and insert it back into the same table:
mysql>
INSERT INTO artist SELECT 10*artist_id,artist_name FROM artist;
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
Here, we're copying the rows but multiplying their
artist_id
s by 10 before we insert
them. This is the result:
mysql>
SELECT * FROM artist;
+-----------+---------------------------+
| artist_id | artist_name |
+-----------+---------------------------+
| 1 | New Order |
| 2 | Nick Cave & The Bad Seeds |
| 3 | Miles Davis |
| 4 | The Rolling Stones |
| 5 | The Stone Roses |
| 6 | Kylie Minogue |
| 60 | Kylie Minogue |
| 50 | The Stone Roses |
| 40 | The Rolling Stones |
| 30 | Miles Davis |
| 20 | Nick Cave & The Bad Seeds |
| 10 | New Order |
+-----------+---------------------------+
12 rows in set (0.01 sec)
Loading Data from Comma-Delimited Files
Databases are sometimes an afterthought. In fact, a staggeringly large amount of time
spent by IT professionals is devoted to reformatting data from one application to suit
another. It's very common, for example, to store data using a spreadsheet program such
as Microsoft Excel or OpenOffice Calc, only to realize later—when you're swamped
with data—that a relational database would have been a better choice. Most spread-
sheet programs allow you to export data as rows of
comma-separated values
(CSV),
