How to Insert in MySQL

Basic

The simplest way to insert a row in MySQL is to use the INSERT INTO command and specify values for all columns. If you have 10 columns, you have to specify 10 values and they have to be in order how the table was defined:

-- Assuming the users table has only three columns: first_name, last_name, and email, and in that order
INSERT INTO users VALUES ('John', 'Doe', 'john@doe.com');

Specifying a Column List

It's optional, but specifying a column list before the VALUES keyword is highly recommended:

INSERT INTO users (first_name, last_name, email, birth_date, city, state)
VALUES ('John', 'Doe', 'john@doe.com','2000-01-01','Los Angeles','CA');

Having a column list has the following advantages:

You don't have to remember the column order as defined in the table.
You don't have to specify a value for all columns, just the required ones.
In case there are many columns, it is easier to match a value to the column it's intended for when you see it in the statement, rather than having to look at the table definition.
INSERT statements without a column lists are invalidated once a column is added or removed from the table. You need to modify your query to reflect the new or deleted column in order for them to work again.

Using the SET Keyword

Although it is not frequently used, MySQL also allows the SET keyword in the INSERT statement. This is useful when there are many columns to be inserted because it's easier to read:

INSERT INTO users SET
  first_name='Vincent',
  last_name='Aviles',
  birth_date='1973-08-14',
  profession='Artist',
  educational_attainment='College Degree',
  city='Daly City',
  current_points=543,
  date_registred='2018-11-30';

The limitation of using the SET keyword is it can only be used to insert one row at a time.

Inserting Multiple Rows

You can insert multiple rows in one INSERT statement by having multiple sets of values enclosed in parentheses:

INSERT INTO users (first_name, last_name)
VALUES
  ('John','Lennon'),
  ('Paul','McCartney'),
  ('George','Harrison'),
  ('Ringo','Starr');

You can also use INSERT with a SELECT command to copy data from an existing table. Note the VALUES keyword is omitted:

INSERT INTO beta_users (first_name, last_name)
SELECT first_name, last_name
FROM users
where beta = 1;

Inserting JSON Values

Starting with version 5.7.8, MySQL supports JSON data types. If you want to insert into a JSON column, just wrap the valid JSON in a single quoted string:

INSERT INTO test_json
VALUES ('{"beta": true, "status": "for review", "test_count": 1}');

We also have a tutorial on querying JSON columns.

Handling Conflicts/Duplicates

If inserting a row would violate a unique constraint, there are different ways to handle it, depending on your requirements.

Using INSERT IGNORE will quietly discard the row to be inserted:

INSERT IGNORE INTO products (product_id, product_name, stocks)
VALUES (1, 'VPN Product 1', 50);

See it in action:

mysql> create table products
    -> (product_id int not null primary key, product_name varchar(100), stocks int);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT IGNORE INTO products (product_id, product_name, stocks)
    -> VALUES (1, 'VPN Product 1', 50);
Query OK, 1 row affected (0.01 sec)

mysql> select * from products;
+------------+---------------+--------+
| product_id | product_name  | stocks |
+------------+---------------+--------+
|          1 | VPN Product 1 |     50 |
+------------+---------------+--------+
1 row in set (0.00 sec)

mysql> INSERT IGNORE INTO products (product_id, product_name, stocks)
    -> VALUES (1, 'VPN Product 1', 40);
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select * from products;
+------------+---------------+--------+
| product_id | product_name  | stocks |
+------------+---------------+--------+
|          1 | VPN Product 1 |     50 |
+------------+---------------+--------+
1 row in set (0.00 sec)

Note that the stocks value did not change.

If you want to do an "upsert" (update the row it exists, otherwise insert), you can use INSERT... ON DUPLICATE KEY UPDATE:

INSERT INTO products (product_id, product_name, stocks)
VALUES (1, 'VPN Product 1', 45)
ON DUPLICATE KEY UPDATE stocks = VALUES(stocks);

In action:

mysql> INSERT INTO products (product_id, product_name, stocks)
    -> VALUES (1, 'VPN Product 1', 45)
    -> ON DUPLICATE KEY UPDATE stocks=VALUES(stocks);
Query OK, 2 rows affected (0.00 sec)

mysql> select * from products;
+------------+---------------+--------+
| product_id | product_name  | stocks |
+------------+---------------+--------+
|          1 | VPN Product 1 |     45 |
+------------+---------------+--------+
1 row in set (0.00 sec)

MySQL also has the REPLACE keyword. When it detects a duplicate, it will delete the conflicting row and insert the new one.

REPLACE INTO products (product_id, product_name, stocks)
VALUES (1, 'VPN Product 1', 45)
ON DUPLICATE KEY UPDATE stocks = VALUES(stocks);

Be careful when using REPLACE vs INSERT ON DUPLICATE KEY because REPLACE deletes the entire row, so it affects foreign keys and can produce unexpected results if the table uses AUTO_INCREMENT to assign a value.

How to Add an Index

How to Update

From MySQL query to chart to Slack in seconds

Get to answers faster, together, with PopSQL and MySQL