Adding data, INSERT operator

The operator INSERT is used to add new records to the table.

General query structure with the INSERT operator

MySQL 8.1
INSERT INTO table_name [(table_field, ...)]
VALUES (value_of_table_field, ...)
| SELECT table_field, ... FROM table_name ...

Values can be inserted by enumeration using the word VALUES listing them in parentheses separated by commas or using the operator SELECT.

Field enumeration and value correspondence

When using the INSERT operator, you can explicitly specify which fields of the table the data will be inserted into. This is done by listing the field names in parentheses after the table name:

MySQL 8.1
INSERT INTO table_name (field1, field2, field3)
VALUES (value1, value2, value3);

Important rules:

• The order of values in VALUES must strictly correspond to the order of fields in the enumeration
• The number of values must match the number of specified fields
• If a field is not specified in the enumeration, it will receive the default value (if one is set) or NULL (if the field allows NULL)

For example, if the Goods table has fields good_id, good_name, and type, then the following queries are equivalent:

MySQL 8.1
-- Explicit field specification
INSERT INTO Goods (good_id, good_name, type)
VALUES (20, 'Table', 2);

-- Changed field order - values change accordingly
INSERT INTO Goods (good_name, type, good_id)
VALUES ('Table', 2, 20);

If you don't specify the list of fields, then values must be listed for all table fields in the order they were defined when creating the table:

MySQL 8.1
INSERT INTO Goods
VALUES (20, 'Table', 2);

It is recommended to always explicitly specify the list of fields. This makes the code more readable, protects against errors when changing the table structure, and allows inserting values only into the required fields.

Differences between INSERT syntaxes

The INSERT operator supports two main syntaxes for specifying data:

INSERT INTO ... VALUES

Used to insert predefined values. Can insert one or more rows at once:

MySQL 8.1
-- Single row
INSERT INTO Goods (good_id, good_name, type)
VALUES (20, 'Table', 2);

-- Multiple rows
INSERT INTO Goods (good_id, good_name, type)
VALUES
    (20, 'Table', 2),
    (21, 'Chair', 2),
    (22, 'Lamp', 8);

When to use: for inserting specific, static data that is known in advance.

INSERT INTO ... SELECT

Used to insert data obtained from a query. Allows copying data from one table to another or inserting results of complex calculations:

MySQL 8.1
INSERT INTO Goods (good_id, good_name, type)
SELECT 20, 'Table', 2;

-- Or copying from another table
INSERT INTO Goods (good_id, good_name, type)
SELECT good_id + 100, good_name, type
FROM Goods
WHERE type = 2;

When to use: for copying data between tables, inserting calculation results, or when data depends on existing records in the database.

Thus, You can add new entries in the following ways:

• Using the syntax INSERT INTO ... SELECT

  MySQL 8.1
  INSERT INTO Goods (good_id, good_name, type)
  SELECT 20, 'Table', 2;
  

• Using the syntax INSERT INTO ... VALUES (...)

  MySQL 8.1
  INSERT INTO Goods (good_id, good_name, type)
  VALUES (20, 'Table', 2);
  

Each of these queries will give the same result:

Primary key when adding a new record

It should be remembered that the primary key of the table is a unique value and adding an existing value will result in an error.

When adding a new record with unique indices, the choice of such a unique values can be a daunting task. The solution may be an additional request, aimed at identifying the maximum value of the primary key to generate a new unique value.

MySQL 8.1
INSERT INTO Goods SELECT MAX(good_id) + 1, 'Table', 2 FROM Goods;

Here, we use the MAX function to find the maximum value in the primary key column. However, this method is not the most reliable or universal way to determine the primary key value, as it only works best with numeric data types. For all other data types, implementing this approach would be more complex. Additionally, using this method may result in retrieving a value that was previously present in the table but has since been deleted, leading to potential data inconsistencies in the system 💥. Therefore, it is recommended that an alternative method be used in real-world projects.

Automatic primary key generation

MySQL 8.1
CREATE TABLE Goods (
	good_id INT NOT NULL AUTO_INCREMENT,
	good_name VARCHAR(255),
	type INT
);

MySQL 8.1
INSERT INTO Goods VALUES (NULL, 'Table', 2);

MySQL 8.1
CREATE TABLE Goods (
	good_id SERIAL,
	good_name VARCHAR(255),
	type INT
);

MySQL 8.1
INSERT INTO Goods (good_name, type) VALUES ('Table', 2);