MariaDB Crash Course - Ben Forta [57]
Use of the INSERT statement can be disabled per table or per user using MariaDB security, as explained in Chapter 28, “Managing Security.”
* * *
Inserting Complete Rows
The simplest way to insert data into a table is to use the basic INSERT syntax, which requires that you specify the table name and the values to be inserted into the new row. Here is an example of this:
Input
INSERT INTO Customers
VALUES(NULL,
'Pep E. LaPew',
'100 Main Street',
'Los Angeles',
'CA',
'90046',
'USA',
NULL,
NULL);
* * *
Note: No Output
INSERT statements usually generate no output.
* * *
Analysis
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_contact and 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 MariaDB each time a row is inserted. You’d not want to specify a value (that is MariaDB’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 MariaDB, 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 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:
Input
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);
Analysis
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 MariaDB matches 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 NULL for cust_id was not needed; the cust_id column was not listed in the column list and so no value was needed.
The following 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 works correctly:
Input
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');
* * *
Tip: Always Use a Columns List
As a rule, never use INSERT without explicitly specifying the column list. This greatly increases the probability that your SQL will continue to function in the event that table changes occur.
* * *
* * *
Caution: Use VALUES Carefully
Regardless of the INSERT syntax being used, the correct number of VALUES must be specified.