Learning MySQL

The INSERT Statement

The INSERT statement is used to add new data to tables. In this section, we explain its basic syntax and show you simple examples that add new rows to the music database. In Chapter 6, we’ll discuss how to load data from existing tables or from external data sources.

INSERT Basics

Inserting data typically occurs in two situations: when you bulk-load in a large batch as you create your database, and when you add data on an ad hoc basis as you use the database. In MySQL, there are different optimizations built into the server for each situation and, importantly, different SQL syntaxes available to make it easy for you to work with the server in both cases. We explain a basic INSERT syntax in this section, and show you examples of how to use it for bulk and single record insertion.

Let’s start with the basic task of inserting one new row into the artist table. To do this, you need to understand the table’s structure. As we explained in Chapter 4 in The Music Database,” you can discover this with the SHOW COLUMNS statement:

mysql> SHOW COLUMNS FROM artist;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| artist_id   | smallint(5) | NO   | PRI | 0       |       |
| artist_name | char(128)   | NO   |     |         |       |
+-------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

This tells you that the two columns occur in the order artist_id and then