Insert statements are used for inserting data (adding new rows) into MySQL tables. They have following syntax.
- Column count and value count should be same.
- Columns need not be in their defined order (as in table definition).
- If a column has a default value (auto-increment, NULL etc), that column can be omitted.
- It’s a good practice to enclose table name and column names with backtick (
Consider a data table with following table definition (For most examples, you will need to truncate the table first).
Following is a valid insert statement for this table. You can run this statement in MySQL command-line. in a GUI tool or with mysqli_query in a PHP script.
- Specifying values for `id`, `job_title`, `salary` and `notes` was not required since they had default values.
- `first_name` and `last_name` columns can not be null and they don’t have default values. Therefore all the insert statements of `employee` table should contain values for these two columns.
If you are going to insert values for all the columns in order, you can omit mentioning column names and have following syntax.
This syntax is not recommended because you have to mention values for all the columns (even though some may have default values) and column order needs to be kept.
In following syntax, columns and values are paired.
Below is an example for this syntax.
Inserting Multiple Rows
You can insert multiple rows by providing a parenthesized list of values separated by commas like below.
When you have multiple rows to
insert, this method is recommended over executing separate insert statements since it is efficient and reduces the load on the server.
Provided that there is already a row in `employee` table with `id` 1, following insert statement will throw an error (for Robin Jackman’s row) since `id` is a unique value column.
In cases where there can be duplicate keys like above, you have three options.
INSERT IGNORE INTO
Ignore the inserting of rows with duplicate keys and insert rest of the rows with INSERT IGNORE INTO statement.
In above example, if there is already a row with `id` 1, first row will be ignored but second row will be added.
Replace the current rows of duplicate keys and insert new rows with REPLACE INTO statement.
In above example, if there is already a row with `id` 1 (say Vivian Dickens’s row), it will be deleted and the new row will be added. Now the row with `id` 1 will contain Robin Jackman’s details.
ON DUPLICATE KEY UPDATE
Update the current row with ON DUPLICATE KEY UPDATE statement.
In above example, if there is already a row with `id` 1, the `notes` field of it will be appended with ‘Updated record’ and no other field will be affected. That is INSERT statement will be ignored and UPDATE statement will be executed.
CONCAT() is a MySQL function. You may use CONCAT_WS() instead of CONCAT() that allows to have a separator.
Inserting from Another Table
In above example, schema (table definition) of `employee` and `new_employee` don’t have to be same. Number of columns specified in SELECT statement should be equal to that of INSERT statement and data types should match.