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.