MySQL INSERT statement

Last update on August 19 2022 21:51:24 (UTC/GMT +8 hours)

INSERT statement

MySQL INSERT statement is used to insert record(s) or row(s) into a table. The insertion of records or rows in the table can be done in two ways, insert a single row at a time, and insert multiple rows at a time.

Version: MySQL 5.6

Syntax:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name,...)] 
    [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

or:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name,...)]
    SET col_name={expr | DEFAULT}, ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

or:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name,...)] 
    [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

Arguments:

Name	Descriptions
INSERT	Inserts new rows into an existing table.
LOW_PRIORITY	Using the LOW_PRIORITY keyword, execution of the INSERT is delayed until no other clients are reading from the table. This includes other clients that began reading while existing clients are reading. Therefore it is possible, for a client that issues an INSERT LOW_PRIORITY statement to wait for a very long time (or even forever) in a read-heavy environment.
DELAYED	Using DELAYED keyword, the server puts the row or rows to be inserted into a buffer, and the INSERT DELAYED statement (issued by the client) can then continue immediately. The server holds the rows if the table is in use. The server begins inserting rows, when the table is free, checking periodically to see whether there are any new read requests for the table. If there are any new read requests, the delayed row queue is suspended until the table becomes free again.
HIGH_PRIORITY	Using HIGH_PRIORITY, it overrides the effect of the --low-priority-updates option if the server was started with that option. It also causes concurrent inserts not to be used. LOW_PRIORITY and HIGH_PRIORITY affect only storage engines that use only table-level locking such as MyISAM, MEMORY, and MERGE.
IGNORE	Using the IGNORE keyword, errors that occur while executing the INSERT statement are ignored.
INTO	Inserts new rows into an existing table.
tbl_name	Name of the table where rows will be inserted.
PARTITION	In MySQL 5.6.2 and later, you can control which partitions and subpartitions accept new rows when inserting into a partitioned table. The PARTITION option takes a comma-separated list of the names of one or more partitions or subpartitions (or both) of the table. If any of the rows which are ready to insert, by a given INSERT statement do not match one of the partitions listed, the INSERT statement fails with the error Found a row not matching the given partition set.
partition_name	Name of the partitioned table(s).
col_name	A comma-separated list of column names. You can specify a comma-separated list of column names following the table name and a value for each named column must be provided by the VALUES list or the SELECT statement. If you do not specify the column names for INSERT ... VALUES or INSERT ... SELECT, values for every column in the table must be provided by the VALUES list (same as order of the columns in the table) or the SELECT statement. The SET clause indicates the column names explicitly.
VALUES \| VALUE	If strict SQL mode is off, any column not explicitly given a value is set to its default (explicit or implicit) value. For example, if you specify a column list that does not name all the columns in the table, unnamed columns are set to their default values. The keyword DEFAULT is used to set a column explicitly to its default value. If both the column list and the VALUES list are empty, INSERT creates a row with each column set to its default value. You can specify an expression expr to provide a column value. This might involve type conversion if the type of the expression does not match the type of the column, and conversion of a given value can result in different inserted values depending on the data type. For example, inserting the string '1998.0e-2' into an INT, FLOAT, DECIMAL(10,6), or YEAR column results in the values 1998, 19.9821, 19.982100, and 1998 being inserted, respectively.

INSERT ... SELECT

To insert many rows quickly into a table from one or many tables you can use INSERT ... SELECT statement. Here is the syntax :

Syntax:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name 
    [PARTITION (partition_name,...)]
    [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

The following conditions hold for a INSERT ... SELECT statements :

Using the IGNORE keyword, ignore rows that would cause duplicate-key violations.
The keyword DELAYED is ignored with INSERT ... SELECT.
The target table of the INSERT statement may appear in the FROM clause of the SELECT part of the query.
AUTO_INCREMENT columns work as usual.
To avoid ambiguous column reference problems when the SELECT and the INSERT refer to the same table.
In MySQL 5.6.2 and later, you can control which partitions and subpartitions accept new rows when inserting into a partitioned table.

Example:

INSERT INTO tb2 (fld_id)
  SELECT tbl.fld_order_id
  FROM tbl WHERE tbl.fld_order_id > 200;

INSERT DELAYED

The DELAYED option for the INSERT statement is a MySQL extension to standard SQL that can be used for certain kinds of tables (such as MyISAM). When a client uses INSERT DELAYED, it gets an okay from the server at once, and the row is queued to be inserted when the table is not in use by any other thread.

Syntax:

INSERT DELAYED ...

INSERT ... ON DUPLICATE KEY UPDATE

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, MySQL performs a UPDATE of the old row. For example, if column x is declared as UNIQUE and contains the value 1, the following two statements have similar effect:

INSERT INTO table (x,y,z) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE z=z+1;
UPDATE table SET z=z+1 WHERE x=1;

Note: The effects are not identical for an InnoDB table where a is an auto-increment column. With an auto-increment column, an INSERT statement increases the auto-increment value but UPDATE does not.

Examples:

MySQL INSERT INTO statement is used to insert record(s) or row(s) into a table.

Syntax:

INSERT INTO table_name ([column_name],[...column_name],...)
VALUES( [column_value],[..column_value]);

Example with PHP code to insert data into a MySQL table

HTML code (say form.html):

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN"
"http://www.w3.org/TR/html4/strict.dtd">
<html lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>form to insert data</title>
</head>
<body>
<form method="post" action="insert.php">
<input type="text" name="name" />
<input type="text" name="email" />
<input type="text" name="address" />
<input type="submit" value="Submit">
</form>
</body>
</html>

PHP code (say insert.php):

<?php
$host="localhost"; //yout host name
$username="root";  //yout user name
$password="";      // your password
$db_name="test";  // your database name
$con=MySQL_connect("$host", "$username", "$password")or die("cannot connect"); //MySQL connection
MySQL_select_db("$db_name")or die("can not select DB"); //select your database
$name = $_POST['name'];
$email = $_POST['email'];
$address = $_POST['address'];
$query = "INSERT INTO test (name,email,address) VALUES ('$name', '$email', '$address')";
MySQL_query($query) or die('Query "' . $query . '" failed: ' . MySQL_error());
// name, email and address are fields of your fields; test your table. $name, $email and $address are values collected from the form
?>

MySQL: INSERT one row in a table

The following statement inserts a single row into a table using MySQL INSERT INTO statement.

Code:

INSERT INTO newcate 
VALUES ("CA006","Sports");

The above statement will insert one row in the table 'newcate'. We have not mentioned any column name here. That is why all of the columns will be affected.

mysql> select * from newcate;
+---------+--------------+
| cate_id | cate_descrip |
+---------+--------------+
| CA001   | Science      | 
| CA002   | Technology   | 
| CA003   | Computers    | 
| CA004   | Nature       | 
| CA005   | Medical      | 
| CA006   | Sports       | 
+---------+--------------+
6 rows in set (0.00 sec)

MySQL: INSERT values for specific columns

The following statement inserts values for specific columns using MySQL INSERT INTO statement.

Sample table: newpurchase

Code:

INSERT INTO newpurchase  (invoice_no,ord_no,book_name)
VALUES  ("INV001","ORD006",”An advance book of Computer”);

The above statement will insert one(1) row in the table 'newpurchase' for the columns 'invoice_no', 'ord_no', and 'book_name'.

MySQL: INSERT NULL values

The following statement inserts NULL values into one or more columns using MySQL INSERT INTO statement.

Sample table: newpurchase

Code:

INSERT INTO newpurchase (invoice_no,ord_no,book_name) 
VALUES ("INV002","ORD007",NULL);

The above statement will insert one(1) row in the table 'newpurchase'. Columns 'invoice_no', 'ord_no', and 'book_name' got populated with values where as column 'book_name' got populated with the NULL value.

Inserting multiple rows in a single SQL query

In MySQL, you can insert multiple rows in a single SQL query. Here is the syntax:

INSERT INTO Table ( Column1, Column2 ) VALUES
( Value1, Value2 ), ( Value1, Value2 );

MySQL INSERT rows with SELECT statement

The following statement inserts values into a table using MySQL INSERT INTO statement when the column names and values are collected from another identical table using MySQL SELECT statement. This way you can insert values of one table into another when tables are identical.

Sample table : purchase

Code:

INSERT INTO testpurchase 
SELECT * 
FROM purchase;

MySQL INSERT rows with SELECT statement and WHERE

The following statement inserts values into a table using MySQL INSERT INTO statement when the column names and values are collected from another identical table using MySQL SELECT and WHERE. This way you can insert values based upon some conditions of one table into another when tables are identical.

Sample table: purchase

Code:

INSERT INTO testpurchase 
SELECT * 
FROM purchase 
WHERE YEAR(invoice_dt)='2008';

The above statement performs the following operations -

insert rows into 'testpurchase' table from the identical table 'purchase',
the year of 'invoice_date' of 'purchase' table must be '2008' .

Previous: MySQL Partitioning
Next: INSERT records with GROUP BY and ORDER BY