Export data to MySQL Out file
In some cases, users want to capture the operations in an output file or some local storage. The storage can be a CSV file or a notepad where the contents of the SQL can be placed.
outfileThis file is generated
using the MySQL command. This command allows the user to export and capture SQL output into a specific file.
select into outfileCommand allows the user to insert rows in specific columns and using options allows reading of tables and the type of format required in the output file. It helps in representing the table in the file in a user defined format.
grammar:
select * from stu into outfile "outfile.txt";
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option, so it cannot execute this statement
When user tries to execute the command, the above error populates. The reason why the error populates is that MySQL server is installed with default configuration in .inithe ./configure/etc/init.d/src/bin/bash file --secure-file-priv.
This option does not allow import and export of libraries for security purposes. This variable exists under Files that restrict users from sharing data to external files sqld.
You can view --secure-file-privthe current path set for the variable with the following command:
SHOW VARIABLES LIKE "secure_file_priv";
Changes a configuration variable value using a path with the variable name. Traverses to the destination shown in the variable.
my.iniFind the .Search variable
at that location secure_file_privand replace the value with an empty value.
Below is a screenshot of how the image variable value appears by default.
Navigate to the path where the variable exists. User will be able to find my.inithe file.
Change the configuration for the same variable and save the file again. Restart the server to see if the error goes away.
The above command changes as follows:
SELECT stu_id, stu_name, stu_age, stu_add INTO OUTFILE 'outfile.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM tablename;
This command allows the user to specify escape sequences that exist in a table. The columns in the table are separated by commas, ,pipes |, or tabs.
It can be configured at insertion time using the above commands and specific commands.
The detailed description of the above commands is as follows.
An image of the MySQL command prompt is provided below.
It shows the commands executed in the local MySQL command prompt.
Now, the above output is outfilethe records present in the file created using the command.
For reprinting, please send an email to 1244347461@qq.com for approval. After obtaining the author's consent, kindly include the source as a link.
Related Articles
If ELSE in MySQL
Publish Date：2025/04/11 Views：85 Category：MySQL
-
In this tutorial, we aim to explore how to use IF ELSE the statement in MySQL. One of the key roles of a data analyst is to gather insights from the data and produce meaningful results. It can be done with the help of several data filtering
DATETIME vs. TIMESTAMP in MySQL
Publish Date：2025/04/11 Views：117 Category：MySQL
-
DATETIME and TIMESTAMP are two different data types that can be used to store values that must contain both a date and a time portion. In this article, we will understand how it is stored in the database and the memory required for ea
Execute multiple joins in one query in MYSQL
Publish Date：2025/04/11 Views：94 Category：MySQL
-
Have you ever wondered how to include multiple joins in one query in MySQL? You have come to the right place. Remember that joins allow us to access information from other tables. This information is included separately to avoid redundancy.
Joining 3 tables in MySQL
Publish Date：2025/04/11 Views：187 Category：MySQL
-
In this tutorial, we will learn how to join three tables in MySQL. Businesses and organizations may have to visualize three tables simultaneously based on certain matching columns common to all three tables. This operation is allowed in MyS
Use of UPDATE JOIN in MySQL
Publish Date：2025/04/11 Views：85 Category：MySQL
-
This tutorial will explain how to use the statement in MySQL database UPDATE JOIN . We generally use joins to iterate over the rows in a particular table which may or may not have similar rows in other tables. We can UPDATE use JOIN the cla
How to use the Row_Number() function in MySQL
Publish Date：2025/04/11 Views：142 Category：MySQL
-
In this tutorial, we will explain how to use the VALUES function in MySQL ROW_NUMBER() . This is a sorting method that assigns consecutive numbers within a partition starting from 1. It is important to note that no two rows within a partiti
Multiple primary keys in MySQL
Publish Date：2025/04/11 Views：66 Category：MySQL
-
In this tutorial, our goal is to explore the concept of multiple primary keys for a table in MySQL. Many times, businesses and organizations have to assign certain columns as primary keys. This primary key has multiple uses and reasons to b
Displaying foreign keys in MySQL
Publish Date：2025/04/11 Views：55 Category：MySQL
-
In this tutorial, we aim to explore how to display foreign keys for tables and columns in MySQL. The type of key that references a primary key, also known as the primary key of another table, is called a foreign key. Understanding the forei
Select first N rows in MySQL
Publish Date：2025/04/11 Views：85 Category：MySQL
-
Sometimes, you have to select first N rows of MySQL database according to your project requirements. n The value of varies according to the requirement; it can be TOP 1 row or TOP 30 rows. We will learn how to select top N rows using the cl
