One of the common MySQL operation is to insert records into a table.
This tutorial explains how to use MySQL insert command with several practical and useful examples.
The following example will connect to devdb database with username devuser and password mysecretpwd
This tutorial explains how to use MySQL insert command with several practical and useful examples.
The following example will connect to devdb database with username devuser and password mysecretpwd
# mysql -u devuser -pmysecretpwd devdb mysql>
For this tutorial, we’ll insert values into employee table. This is the structure of the employee table.
mysql> desc employee; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | dept | varchar(10) | YES | | NULL | | | salary | int(10) | YES | | NULL | | +--------+-------------+------+-----+---------+----------------+
If you are new to MySQL, use this How to create MySQL database and table to get started.
1. Basic Insert Command Example
The following command will insert three new records into employee table. In this example, after the “values”, specify the values for all the columns in the table.
INSERT INTO employee VALUES(100,'Thomas','Sales',5000);
Use the MySQL select command to verify that the records got inserted successfully.
SELECT * FROM employee;
2. Insert Values only for Selected Columns
If you want to insert values only to selected columns, you should specify the column names in the insert command.
The following will insert two records only for columns id and name.
INSERT INTO employee(id,name) VALUES(200,'Jason');
For the “dept” and “salary” column, we didn’t specify any values for this particular records. So, we’ll see NULL as the value in our select command output. Please note that this is not the string “NULL”, the select command just displays the string “NULL” to indicate that the column value is really null.
mysql> SELECT * FROM employee; +-----+--------+-------+--------+ | id | name | dept | salary | +-----+--------+-------+--------+ | 100 | Thomas | Sales | 5000 | | 200 | Jason | NULL | NULL | +-----+--------+-------+--------+ 2 rows in set (0.00 sec)
3. Insert Set Example
Instead of using “values” keyword in your select command, you can also use “set” keyword in your select command as shown below.
The following insert command is exactly same as the previous example. But, instead of value, this is using set.
mysql> INSERT INTO employee SET id=300, name='Mayla'; mysql> select * from employee; +-----+--------+-------+--------+ | id | name | dept | salary | +-----+--------+-------+--------+ | 100 | Thomas | Sales | 5000 | | 200 | Jason | NULL | NULL | | 300 | Mayla | NULL | NULL | +-----+--------+-------+--------+
4. Insert Records Based on Rows from Another Table
In this example, we’ll use INSERT … SELECT method, which will select the rows from another table, and insert it into our table.
The following example will take all the records from contractor table and insert it into employee table.
INSERT INTO employee SELECT * FROM contractor;
You can also use various “where” condition in the select command to only pick selected records from contractor table and insert into employee table as shown below.
INSERT INTO employee SELECT * FROM contractor WHERE salary >= 7000;
Note: If you are used to Oracle database, you’ll use “insert into employee AS select * from contractor”. Please note that mysql doesn’t use the “AS” keywords in this context.
5. Insert Selected Columns Values From Another Table
You can also pick only selected column values of rows from another table and insert it into your table.
The following example will take the values of “id” and “name” for ALL the rows in the contractor table and insert it into employee table.
INSERT INTO employee(id,name) SELECT id,name FROM contractor;
Just like the previous example, you can also use where condition and limit the records.
INSERT INTO employee(id,name) SELECT id,name FROM contractor WHERE salary >= 7000;
Please note that if the record already exist for the primary key (which is id) in the employee table, you’ll get the following error message. The following error indicates that the employee id “100” already exists in the employee table.
ERROR 1062 (23000): Duplicate entry '100' for key 'PRIMARY'
6. Insert Records to a Specific Partition
If you have created the table using partition by range, then you can specify the partition in your insert command as shown below.
The following example will insert records into the employee table in partition p1
INSERT INTO employee PARTITION (p1) VALUES(100,'Thomas','Sales',5000);
Please note that the row already exists in that particular partition. For example, in this example p1, you’ll get the following error message:
ERROR 1729 (HY000): Found a row not matching the given partition set
Note: This will work only on MySQL 5.6 and above.
7. Insert Records to Multiple Partition in a Table
You can also insert records into multiple partitions using a single insert statement.
The following insert statement will insert the record with id “100” to partition p1, and record with id “200” to partition p2.
INSERT INTO employee PARTITION (p1, p2) VALUES(100,'Thomas','Sales',5000), (200,'Jason','Technology',5500);
Please note that in the above example, if for some reason, MySQL is unable to insert one of the records into a partition, the entire insert statement will fail, and both the records will not be inserted.
Again, this will work only on MySQL 5.6 and above.
8. Ignore Error Message During Insert
For some reason, if you want to ignore the error message thrown by MySQL during insert statement, you can use insert ignore.
For example, the following command will throw an error message, as the record already exists in the table.
mysql> INSERT INTO employee VALUES(100,'Thomas','Sales',5000); ERROR 1062 (23000): Duplicate entry '100' for key 'PRIMARY'
To ignore the above error message, you can use “insert ignore” (instead of just insert) as shown below. Please note that this will still not insert the record into the table, as there is a primary key on id column. But, this will simply ignore the error message.
mysql> INSERT IGNORE INTO employee VALUES(100,'Thomas','Sales',5000); Query OK, 0 rows affected (0.00 sec)
9. Default Values in Insert
If the MySQL is running in strict mode, and when we don’t specify default values, it will throw error message.
However if strict mode is not enabled (which is default), and when you do an insert command and don’t specify a value fora column, it will use the default value for that particular column data types.
For example, in the bonus table, both the column values are set to “not null”.
mysql> DESC bonus; +--------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+---------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | amount | int(11) | NO | | NULL | | +--------+---------+------+-----+---------+-------+
Let us insert a record into this table for id column.
INSERT INTO bonus(id) VALUES(100);
When you do a select command, you’ll notice that the amount column is automatically set to implicit default value of 0.
SELECT * FROM bonus; +-----+--------+ | id | amount | +-----+--------+ | 100 | 0 | +-----+--------+
If you don’t specify for both id and amount, both will be set to 0 automatically as shown below. i.e When values are not specified, MySQL will use the DEFAULT values.
INSERT INTO bonus VALUES(); mysql> select * from bonus; +-----+--------+ | id | amount | +-----+--------+ | 0 | 0 | +-----+--------+
Note: You can also use the keyword “DEFAULT” in the values as shown below, which will achieve the above output as above.
INSERT INTO bonus VALUES(DEFAULT,DEFAULT);
For string column, the default value is empty string. Also, please note that when numeric column has AUTO_INCREMENT set, then the default value will be the next value of the appropriate sequence.
10. Expression in Insert Values
In the following example, for the bonus value, we’ve specified “5000+id” as value. So, this will add the employee id value to the bonus value and insert the final value into the bonus column as shown below.
You can use “+”, “-“, “*”, or any other valid MySQL expression operator in the values. In the following example, it is using “50*2” for id column. So, the id that will be inserted is “100”
You can also refer to the values of other columns. In the following example, it uses “5000+id” for bonus column. So, this will take the value of id column (which is 100), and add it to 5000. So, final bonus value is “5100” as shown below.
mysql> INSERT INTO employee VALUES(50*2,'Thomas','Sales',5000+id); mysql> select * from employee; +-----+--------+-------+--------+ | id | name | dept | salary | +-----+--------+-------+--------+ | 100 | Thomas | Sales | 5100 | +-----+--------+-------+--------+
11. Make Insert a Low Priority (or High Priority) Activity
For storage engine that supports table locking (for example, MyISAM), you can specify the priority of your insert.
For example, this insert statement will delay the insert (make it low priority) until there are no reads on the table.
INSERT LOW_PRIORITY INTO employee VALUES(100,'Thomas','Sales',5000);
You can also specify high priority as shown below, which will behave opposite to the low priority inserts.
INSERT HIGH_PRIORITY INTO employee VALUES(100,'Thomas','Sales',5000);
Please keep in mind that if your database is read intensive and if you specify a low_priority insert statement, your insert might not go through for a very long time.
Also, please note that this is little different than “INSERT … DELAYED”, which is deprecated starting from MySQL 5.6.6. So, don’t use “insert … delayed” anymore.
12. When Duplicate is Found, Update a Column Value
During insert, if there is a duplicate key, it will fail as shown below, as the id “100” already exist in the table.
mysql> INSERT INTO employee VALUES(100,'Thomas','Sales',5000); ERROR 1062 (23000): Duplicate entry '100' for key 'PRIMARY' mysql> select * from employee; +-----+--------+-------+--------+ | id | name | dept | salary | +-----+--------+-------+--------+ | 100 | Thomas | Sales | 5000 | +-----+--------+-------+--------+
However, you can do some update to that particular record (when a duplicate is found) using the “ON DUPLICATE KEY UPDATE” as shown below.
As shown in the following example, when the insert failed (because of duplicate key), we are updated the salary column by adding 500 to its value.
mysql> INSERT INTO employee VALUES(100,'Thomas','Sales',5000) on DUPLICATE KEY UPDATE salary=salary+500; mysql> select * from employee; +-----+--------+-------+--------+ | id | name | dept | salary | +-----+--------+-------+--------+ | 100 | Thomas | Sales | 5500 | +-----+--------+-------+--------+
Please note that in the above example, while inserting, even though it updated only one record, the output will say “2 rows affected”.
Tags:
prog