Does mysql have path separators functionality?

Question

For instance, in Nodejs you have path.sep, in Java you have File.separator. Do you have something similar in mysql? If not, which would be the best way to deal with path separators in a way that works in any operating system?

I need it so I can pass correct paths to the LOAD_FILE() function without worrying about current operating system.

Quintonquintuple

Answer 1

I found that I could pass the value of path.sep or File.separator in a parameterized query:

"insert into MY_TABLE (file_contents) values (LOAD_FILE(?))", my_folder+File.separator+filename

Not what I was really looking for, but at least works for me.

Quintonquintuple

Answer 2

MySQL query to get path separator:

SELECT IF(INSTR(LOWER(@@global.version_compile_os), 'win'), '\\', '/') AS `path_separator`;

---

MySQL function to get path separator:

DELIMITER $$
CREATE FUNCTION `path_separator`()
RETURNS VARCHAR(1)
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE `path_separator` VARCHAR(1);
    SELECT IF(INSTR(LOWER(@@global.version_compile_os), 'win'), '\\', '/') INTO `path_separator`;
    RETURN `path_separator`;
END $$
DELIMITER ;

SELECT `path_separator`();

---

MySQL query to build a file path:

Note: if MySQL server is running on Windows, backslashes (Windows path separator) may need to be escaped depending on your use case.

SELECT CONCAT(
    @@global.secure_file_priv,
    CONCAT_WS(
        `path_separator`(),
        'foo',
        'bar',
        'baz.csv'
    )
) AS `output_file_path`;

Output:

'C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\foo\bar\baz.csv'

---

Example: export a CSV file.

Notes:

• Path must be in 'Uploads' directory if MySQL server is running with the --secure-file-priv option or else you'll get, "Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement".
• Cannot create subdirectories in 'Uploads' directory or else you'll get, "Error Code: 1. Can't create/write to file 'C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\foo\bar.csv' (OS errno 2 - No such file or directory)".

DELIMITER $$
CREATE PROCEDURE `csv_export`()
BEGIN
    DECLARE csv_file_path TEXT;

    SELECT
        REPLACE(
             CONCAT(
                @@global.secure_file_priv,
                CONCAT_WS(
                    `path_separator`(),
                    'foo.csv'
                )
            ),
            '\\',
            '\\\\'
        )
    INTO csv_file_path;

    SET @sql = '
            SELECT id
            INTO
                OUTFILE ''csv_file_path''
                FIELDS TERMINATED BY '',''
                ENCLOSED BY ''"''
                LINES TERMINATED BY ''\n''
            FROM your_table
            LIMIT 1;
        ';

    SET @sql = REPLACE(@sql, "csv_file_path", csv_file_path);

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END $$
DELIMITER ;

CALL `csv_export`();

If you need to escape double quotes in text columns so the CSV file can be read properly by Excel, see my answer here.

Flatfish