CODEWITHSUNDEEP
mysql
klondike
klondike

Reputation: 413

How to execute source command on a file in a specific directory using mysql

I'm trying to use the source command in mysql to execute some files with sql code in a specific directory. But I'm not sure how to do this using either workbench or mysql cli. For example, if I'm in the mysql cli, I'm not sure how to tell it the location of the file I want it to execute

Upvotes: 1

Views: 1297

Answers (2)

ROHIT KHURANA
ROHIT KHURANA

Reputation: 983

on MySQL prompt, just type

source PATHOFFILE;

PATHOFFILE means the absolute path of the file.

Just make sure, either you have disabled secure_file_priv parameter or added that directory path (where you are keeping the files) against this variable which requires a restart of MySQL server. You can find more details of this variable in the below link.

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_secure_file_priv

Upvotes: 1

bigtheo
bigtheo

Reputation: 660

The LOAD DATA statement reads rows from a text file into a table at a very high speed. The file can be read from the server host or the client host, depending on whether the LOCAL modifier is given. LOCAL also affects data interpretation and error handling.

LOAD DATA is the complement of SELECT ... INTO OUTFILE. (See Section 13.2.10.1, “SELECT ... INTO Statement”.) To write data from a table to a file, use SELECT ... INTO OUTFILE. To read the file back into a table, use LOAD DATA. The syntax of the FIELDS and LINES clauses is the same for both statements.

The mysqlimport utility provides another way to load data files; it operates by sending a LOAD DATA statement to the server. See Section 4.5.5, “mysqlimport — A Data Import Program”.

syntax :

LOAD DATA
[LOW_PRIORITY | CONCURRENT] [LOCAL]
INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[CHARACTER SET charset_name]
[{FIELDS | COLUMNS}
    [TERMINATED BY 'string']
    [[OPTIONALLY] ENCLOSED BY 'char']
    [ESCAPED BY 'char']
]
[LINES
    [STARTING BY 'string']
    [TERMINATED BY 'string']
]
[IGNORE number {LINES | ROWS}]
[(col_name_or_user_var
    [, col_name_or_user_var] ...)]
[SET col_name={expr | DEFAULT}
    [, col_name={expr | DEFAULT}] ...]

for more information : https://dev.mysql.com/doc/refman/8.0/en/load-data.html

Upvotes: 1

Related Questions