The SELECT INTO statement in MySQL is used to export or copy data from one table or query into a file or a new variable. However, unlike some other database systems (like SQL Server), MySQL does not support directly copying data into a new table using SELECT INTO. Instead, MySQL offers two main functionalities with SELECT INTO:

1. SELECT INTO OUTFILE: Exports query results to a file.
2. SELECT INTO variable: Assigns query results to user-defined variables.

1. SELECT INTO OUTFILE

This version is used to export query results into a text file. The file is created on the server host, and you can specify the file format.

Syntax

SELECT columns
INTO OUTFILE 'file_path'
FIELDS TERMINATED BY 'delimiter'
ENCLOSED BY 'character'
LINES TERMINATED BY 'line_terminator'
FROM table_name
WHERE conditions;

Example 1: Exporting Data to a CSV File

Goal: Export the list of customers to a file called customers.csv.

SELECT CustomerID, CompanyName, ContactName
INTO OUTFILE '/var/lib/mysql-files/customers.csv'
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\n'
FROM Customers;

Explanation:

• FIELDS TERMINATED BY ',': Columns are separated by commas.
• ENCLOSED BY '"': Each field is wrapped in double quotes.
• LINES TERMINATED BY '\n': Each row ends with a newline character.
• File is saved in /var/lib/mysql-files/ directory on the MySQL server.

Note: Ensure the MySQL server has permissions to write to the specified directory.

2. SELECT INTO variable

This version is used to store query results into user-defined variables for later use in the same session.

Syntax

SELECT column_name INTO @variable
FROM table_name
WHERE condition
LIMIT 1;

Example 2: Storing a Value in a Variable

Goal: Store the name of the customer with the earliest order into a variable.

SELECT ContactName INTO @FirstCustomer
FROM Customers
ORDER BY CustomerID
LIMIT 1;

SELECT @FirstCustomer AS First_Customer_Name;

Explanation:

• The query assigns the ContactName of the first customer to the variable @FirstCustomer.
• The second query retrieves and displays the variable value.

Differences Between SELECT INTO OUTFILE and INSERT INTO ... SELECT

1. SELECT INTO OUTFILE:
   • Exports data to a file.
   • Used for data backup or external processing.
2. INSERT INTO ... SELECT:
   • Copies data from one table into another table.
   • Example: INSERT INTO CustomersBackup (CustomerID, CompanyName, ContactName) SELECT CustomerID, CompanyName, ContactName FROM Customers;

Use Cases for SELECT INTO

• Data Export: Use SELECT INTO OUTFILE for creating backups or exporting data for external use.
• Temporary Data Storage: Use SELECT INTO variable for storing intermediate results in procedures or scripts.

Security Note

• SELECT INTO OUTFILE requires file permissions and is restricted to the server file system. This ensures security to avoid unauthorized data dumps.