Inserting Complete Rows
Here is an example of this:
INSERT INTO Customers VALUES(NULL, 'Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', NULL, NULL);
INSERT statements usually generate no output. The preceding example inserts a new customer into the
customers table. The data to be stored in each table column is specified in the
VALUES clause, and a value must be provided for every column. If a column has no value (for example, the
cust_email columns), the
NULL value should be used (assuming the table allows no value to be specified for that column). The columns must be populated in the order in which they appear in the table definition. The first column,
cust_id, is also
NULL. This is because that column is automatically incremented by MySQL each time a row is inserted. You'd not want to specify a value (that is MySQL's job), and nor could you omit the column (as already stated, every column must be listed), and so a
NULL value is specified (it is ignored by MySQL, which inserts the next available
cust_id value in its place).
Although this syntax is indeed simple, it is not at all safe and should generally be avoided at all costs. The previous SQL statement is highly dependent on the order in which the columns are defined in the table. It also depends on information about that order being readily available. Even if it is available, there is no guarantee that the columns will be in the exact same order the next time the table is reconstructed. Therefore, writing SQL statements that depend on specific column ordering is very unsafe. If you do so, something will inevitably break at some point.
The safer (and unfortunately more cumbersome) way to write the
INSERT statement is as follows:
INSERT INTO customers(cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) VALUES('Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', NULL, NULL);
This example does the exact same thing as the previous
INSERT statement, but this time the column names are explicitly stated in parentheses after the table name. When the row is inserted MySQL will match each item in the columns list with the appropriate value in the
VALUES list. The first entry in
VALUES corresponds to the first specified column name. The second value corresponds to the second column name, and so on.
Because column names are provided, the
VALUES must match the specified column names in the order in which they are specified, and not necessarily in the order that the columns appear in the actual table. The advantage of this is that, even if the table layout changes, the
INSERT statement will still work correctly. You'll also notice that the
cust_id was not needed, the
cust_id column was not listed in the column list and so no value was needed.
INSERT statement populates all the row columns (just as before), but it does so in a different order. Because the column names are specified, the insertion will work correctly:
INSERT INTO customers(cust_name, cust_contact, cust_email, cust_address, cust_city, cust_state, cust_zip, cust_country) VALUES('Pep E. LaPew', NULL, NULL, '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA');
Always Use a Columns List As a rule, never use
INSERT without explicitly specifying the column list. This will greatly increase the probability that your SQL will continue to function in the event that table changes occur.
VALUES Carefully Regardless of the
INSERT syntax being used, the correct number of
VALUES must be specified. If no column names are provided, a value must be present for every table column. If columns names are provided, a value must be present for each listed column. If none is present, an error message will be generated, and the row will not be inserted.
Using this syntax, you can also omit columns. This means you only provide values for some columns, but not for others. (You've actually already seen an example of this;
cust_id was omitted when column names were explicitly listed).
Omitting Columns You may omit columns from an
INSERT operation if the table definition so allows. One of the following conditions must exist:
The column is defined as allowing
NULLvalues (no value at all).
A default value is specified in the table definition. This means the default value will be used if no value is specified.
If you omit a value from a table that does not allow
NULL values and does not have a default, MySQL generates an error message, and the row is not inserted.
Improving Overall Performance
Databases are frequently accessed by multiple clients, and it is MySQL's job to manage which requests are processed and in which order.
INSERT operations can be time consuming (especially if there are many indexes to be updated), and this can hurt the performance of
SELECT statements that are waiting to be processed.
If data retrieval is of utmost importance (as is usually is), you can instruct MySQL to lower the priority of your
INSERT statement by adding the keyword
LOW_PRIORITY in between
INTO, like this:
INSERT LOW_PRIORITY INTO
Incidentally, this also applies to the