MySQL INTO Keyword

The INTO keyword in MySQL is used to specify the destination for data being inserted or selected. It commonly appears in INSERT INTO statements for adding data to tables and in SELECT ... INTO statements for storing data into variables.

Usage

The INTO keyword is employed when you want to direct data into a table or variable. In an INSERT statement, it helps specify the target table, while in a SELECT ... INTO statement, it defines the destination for the selected data, typically into variables.

Syntax

INSERT INTO

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
提供：

Here, INTO table_name indicates the table where new data will be added.

SELECT INTO Variables

SELECT column1, column2, ...
INTO @variable1, @variable2, ...
FROM existing_table
[WHERE condition];
提供：

This syntax stores the results of the query into user-defined variables.

Creating a New Table from a SELECT Query

CREATE TABLE new_table AS
SELECT column1, column2, ...
FROM existing_table
[WHERE condition];
提供：

This syntax creates a new table new_table containing data selected from existing_table.

Examples

1. Basic Insert

INSERT INTO products (product_name, price)
VALUES ('Laptop', 999.99);
提供：

This example inserts a new row into the products table with specified values for product_name and price.

2. Storing Query Result into Variables

SELECT COUNT(*) INTO @total_customers
FROM customers
WHERE country = 'USA';
提供：

In this example, the result of the count query is stored in a user-defined variable @total_customers.

3. Creating a New Table from a SELECT Query

CREATE TABLE recent_orders AS
SELECT customer_id, order_date
FROM orders
WHERE order_date > '2023-01-01';
提供：

This syntax creates a new table recent_orders containing data selected from orders where the order date is after January 1, 2023.

Tips and Best Practices

• Ensure table existence. When using INSERT INTO, make sure the target table already exists.
• Use CREATE TABLE ... AS SELECT ... for new tables. To create a new table from a select query, prefer using CREATE TABLE ... AS SELECT ... instead of SELECT INTO.
• Verify data types. Match the data types of values with the column definitions in INSERT INTO to avoid errors.
• Leverage variables for complex queries. Use SELECT ... INTO with variables to store results of complex queries for later use.
• Handle errors and exceptions. Implement error handling to manage exceptions during data insertion or selection operations.

Note: MySQL differs from other SQL dialects such as SQL Server, where SELECT INTO is used to create new tables. In MySQL, use CREATE TABLE ... AS SELECT ... for this purpose.