MySQL VALUES Keyword

The `VALUES` keyword in MySQL is used to specify the data to be inserted into a table during an `INSERT` operation. It defines the literal values to be inserted for each column in the table.

Usage

The `VALUES` keyword is used in conjunction with `INSERT INTO` to add new rows of data into a table. It is followed by a list of values that match the columns specified in the `INSERT INTO` clause.

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

In this syntax, `VALUES (value1, value2, ...)` specifies the data to be inserted into the respective columns of the table. `VALUES` can only be used with `INSERT` operations and not with `UPDATE` or other operations.

Examples

1. Basic Insert

sql
INSERT INTO students (name, age)
VALUES ('John Doe', 21);

This example inserts a new row into the `students` table with `name` set to 'John Doe' and `age` set to 21.

2. Inserting Multiple Rows

sql
INSERT INTO products (product_name, price)
VALUES ('Laptop', 899.99), ('Smartphone', 599.99);

Here, two new rows are added to the `products` table, each with specified values for `product_name` and `price`.

3. Insert with SELECT

sql
INSERT INTO archive_table (column1, column2)
SELECT column1, column2
FROM main_table
WHERE condition;

Data is inserted into `archive_table` based on a `SELECT` statement, allowing for bulk data transfer under specific conditions. Unlike `INSERT ... VALUES`, this operation does not use the `VALUES` keyword.

Tips and Best Practices

• Ensure value order matches columns. The values must align with the order of columns specified in the `INSERT INTO` statement.
• Use single quotes for strings. Enclose string literals in single quotes to avoid syntax errors.
• Validate data types. Ensure that the values match the data types expected by the table’s columns.
• Consider using `INSERT IGNORE` or `REPLACE`. These can handle duplicates or conflicts based on primary keys or unique indexes.
• Secure your inputs. Use parameterized queries or prepared statements to prevent SQL injection attacks.
• Handle errors and exceptions. Be aware of possible errors like duplicate key or foreign key constraint violations.