This was tested using Windows 10 and Windows Server 2016, both running MySQL 8.0.x. You need to put the following content in your my.cnf (Mac, Linux) or my. These could be inserted into the variable if needed. I omitted field terminations, enclosures, and line terminations because they were the default values. This produces a file: X:/mysql_secure/my_script_data/my_file_prefix_190827_suffix.csv. MYSQLWORKBENCH DATABASE BANCODEDADOS ERRORCODENeste vdeo mostro como resolver os erros de importao de arquivo csv.In this video I show how to solve th. SET := CONCAT("SELECT * FROM my_database.table WHERE conditions INTO OUTFILE "_suffix.csv'") SELECT 'my_script_data/') INTO in Windows returns a path with '\' The secure-file-priv or securefilepriv variable is used to put a. SET = DATE_FORMAT(NOW(), '%Y-%m-%d_%H%i%s') In this sql tutorial well walk you through a MySQL tutorial on a MySQL variable: secure-file-priv mysql. So, reissue the LOAD DATA statement with this right path for fixing the error. To create a timestamped CSV file with your output use the following: - # VARIABLES Open the file and under mysqld section we can see secure-file-priv along with value as shown: secure-file-privC:/ProgramData/MySQL/MySQL Server 5.7/Uploads This is the path we need to use. That means the following path must already exist: X:/mysql_secure/my_script_data/ In your SQL script, store your output directory in a variable, using CONCAT() to append other file paths as needed (they will have to be created manually before executing, unfortunately). However, I cant, because -secure-file-priv is enabled. If you do not, the MySQLxx service will not start. I have MySQL Community Edition v8 running on my laptop, and I would like to load a CSV file into it. NOTE: Make sure to save your file in the same encoding that it was originally written in (usually UTF-8). Do this by modifying the my.ini file ( create a backup first), searching for secure-file-priv and providing an absolute path: Let's assume you want to store the output in a subdirectory to make it easier to manage e.g./my_script_data/ inside the secure folder, but you might run this script on multiple servers, possibly with different secure directories.įirstly, for each server, set the secure_file_priv to the most appropriate directory, perhaps on another drive e.g.
0 Comments
Leave a Reply. |