This tutorial demonstrates how insert statement works in MySQL with syntax and examples explained in details.

MySQL insert statement allows you to insert new rows into an existing table, based on explicitly specified values in the statement, or values in another table or tables.

The simplest INSERT statement takes the following syntax format, where explicitly specified values are inserted.

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

To learn how to insert multiple new rows into an existing table, based on values in another table or tables, read Create MySQL table by using another table.

When creating the INSERT statement, consider the following guidelines.

1. Following the TABLE keyword, you can provide a comma-separated list of column names. In this case, a value for each named column must be provided in the VALUES list. See Practice #1.

   When a column has AUTO_INCREMENT attribute defined, you can omit the column name and value in the insert statement and the row inserted will automatically use the next available ID for the column. See this tutorial for details about how to define and obtain the auto generated sequence number of the last inserted row.

2. If you do provide a value for the AUTO_INCREMENT column, you need to make sure the value you provide does not already exist in the table, see Practice #2a.

3. If you do include the ProductID column but don't want to provide a value for it, use the keyword NULL. In this case, the next available sequence number will be generated automatically, see Practice #2b.

4. A value must be provided for every NOT NULL column defined in the table.

5. You don't have to always provide a list of column names after the TABLE keyword. In this case, values for every column in the table must be provided in the VALUES list. The order of the values is important because they must be in the same order as the column names, To find out the order of the columns in the table, use DESCRIBE keyword. See Practice #3.

6. If you insert a NULL value, the column definition must allow NULLs. Otherwise the query will fail.

7. You can omit a column from the INSERT statement if the column allows NULL values or the column has default value defined. See Practice #4.

8. It's much clearer in the INSERT statement to use the keyword DEFAULT(col_name) to set a column to its explicit default value, see Practice #5.

9. If you are not running in strict SQL mode, any column that is not explicitly provided a value is set to its explicit default value or implicit default value. It's wise to use good practice in this case to avoid implicit default values inserted so make sure you always provide a specific value for columns that don't have a default value defined. Otherwise, an implicit default value might be added that may not be what you want.

10. You can specify an expression to provide a column value. Note that you may have to do type conversion if the expression involves another column of a different type, see Practice #6.

11. If both the table names and the VALUES list are empty, INSERT could create a row with each column set to its explicit or implicit default value. This is only possible if all the columns in the table can be filled with either null or default values.

    INSERT INTO tbl_name () VALUES();

12. To insert special characters, like the apostrophe ( ' ), use Escape Sequence, see Practice #7.

13. To avoid duplicated rows from being inserted into a table, use INSERT IGNORE or ON DUPLICATE KEY UPDATE. For more detailed, refer to this tutorial.

14. Insert into a table with SET keyword to assign values. This is different to the general INSERT statement where a VALUES list is used. See Practice #8.

    Here is the syntax format.

    INSERT INTO table_name
    SET column1=value1,column2=value2,...;
    

15. It's illegal if the number of values does not match the number of column names in the query.

    The following query is illegal as the number of values is more than the number of columns.

    INSERT INTO tbl_name (a,b,c) VALUES(1,2,3,4,5,6,7,8,9);

Practice #1: Basic insert statement

The following insert statement adds a value to products table. Both column names and values are explicitly provided.

Note that the ProductID column is not listed in the columns list and no corresponding value is provided because ProductID is an AUTO_INCREMENT primary key. For this type of column, the next available sequence number will be generated automatically when the record is inserted.

INSERT INTO products(ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, 
UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued)
VALUES('Cheetose Cheese',23,4,'500 g',16,37,0,10,'n')

To see this just inserted product, run the following query. LAST_INSERT_ID() function returns the last auto generated integer number which was assigned to ProductID column.

SELECT * FROM products
WHERE PRODUCTID=LAST_INSERT_ID()

Query result set - 1 row returned:

Practice #2a: Insert where AUTO_INCREMENT is hardcoded as primary key.

This insert statement is very similar to the one above. The only difference is that ProductID is listed and a value is provide.

Before insert, the maximum ProductID in the table is 78, so we assign 79 to the record as it leaves no gap and is considered a good practice to follow.

INSERT INTO products(ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, 
UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued)
VALUES(79,'Golden Cheese',23,4,'500 g',16,37,0,10,'n')

Below is the records in products table. ProductID 78 was auto generated in Practice #1, but 79 was hardcoded and inserted by our query above.

Practice #2b: use keyword NULL for the AUTO_INCREMENT column.

The difference here to the query in Practice #2a above is the use of keyword NULL. This way, ProductID will be assigned to the next available sequence number.

INSERT INTO products(ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, 
UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued)
VALUES(NULL,'Mondseer Cheese',23,4,'500 g',16,37,0,10,'n')

Products table looks like below after the insert.

Practice #3: Insert where column names are omitted.

This insert query omits the column names. Values for every column must be provided in the VALUES list. However, the best practice is to include the column names because the query wouldn't break if table structure is changed laster, e.g. number of columns or column order is changed.

You need to make sure the values are in the same order as the columns.

INSERT INTO products
VALUES(null,'Golden Chocolate',2,3,'12 - 100 g bars',12.5,85,0,30,'n')

Below is the records in products table.

To find out the order of the columns in the table, use DESCRIBE keyword. The following query finds out the table structure - the columns and order of them are presented clearly.

DESCRIBE products

Products table looks like below after the insert.

Practice #4: Insert where default value is defined.

This insert statement does not list the Discontinued column and it will use the default value 'n' for the inserted row.

INSERT INTO products(ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, 
UnitsInStock, UnitsOnOrder, ReorderLevel) VALUES('Crispy Squid',15,8,'1k pkg.',6.5,5,90,15)

Products table looks like below after the insert.

For a given table, you can use the SHOW CREATE TABLE statement to see which columns have an explicit DEFAULT clause.

SHOW CREATE TABLE products

Practice #5: Insert a default value by using keyword DEFAULT

In the insert statement below, it explicitly specifies by DEFAULT(col_name) that the default value for Discontinued column is to be used.

INSERT INTO products(ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, 
UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued)
VALUES('Strawberry Jam',8,2,'12 - 12 oz jars',4,55,0,15,DEFAULT(Discontinued))

Practice #6: Insert a column value by using expression.

The following insert query uses UnitsInStock column in the expression to provide a value for ReorderLevel. An expression can refer to any column that was set earlier in a value list. The caveat is that the column that contains an expression must refer to column(s) defined before it. Here UnitsInStock is defined before ReorderLevel.

INSERT INTO products(ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, 
UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued)
VALUES('Grain Mix',21,5,'16 - 2 kg boxes',9,45,0,UnitsInStock*2,'n')

Products table looks like below after the insert.

Practice #7: Insert a single quote or other special characters

The following query inserts a string data type which contains a single quote. Single quote is a special character that must begin with a backslash (also known as Escape Character).

Refer to this MySQL page about special character escape sequences.

Please note that the single quote ' can also be escaped by two single quotes ''.

INSERT INTO products(ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, 
UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued)
VALUES('Sam\'s Peanut Butter',10,2,'15 - 625 g jars',6.8,56,0,15,'n')

Below is the Special Character Escape Sequences listed on MySQL website. Each of these sequences begins with a backslash.

Practice #8: Insert into a table with SET keyword

This insert statement is nearly identical to the example above, but instead of using VALUES list, is assigns each column value by using SET keyword.

INSERT INTO products
SET ProductName='Sam\'s Sesame Spread',
    SupplierID=10, 
    CategoryID=2, 
    QuantityPerUnit='15 - 625 g jars', 
    UnitPrice=6.8, 
    UnitsInStock=56, 
    UnitsOnOrder=0, 
    ReorderLevel=15,
    Discontinued='n'

Products table looks like below after the insert.

Happy coding!

Other tutorials in this category

1. Update Statement in MySQL

2. How to update data in a specific order in MySQL

3. How to update top N rows of data in MySQL

4. Use outer join in update

5. How to do cross-table update in MySQL - Part 1

6. How to do cross-table update in MySQL - Part 2

7. Using Bulk Insert Statement

8. How to create and use AUTO_INCREMENT column and use LAST_INSERT_ID function

9. Combine update and insert into one statement

10. Using MySQL REPLACE (INTO) to mimic DELETE + INSERT

11. Update multiple rows (distinctive by primary key) with different values in one query

12. Update multiple rows (distinctive by primary key) for multiple columns in one query

Back to Tutorial Index Page