MySQL存储过程中如何执行外部SQL文件？替代SOURCE命令方案

Got it, let's tackle your question step by step.

First, a quick clarification: MySQL doesn't have a server-side equivalent to the client's SOURCE command—SOURCE is a command that the mysql console app handles directly, not something you can run inside a stored procedure via standard SQL. But there are two solid workarounds to execute your external trigger script from within your procedure.

Workaround 1: Use LOAD_FILE() to read the script and run it via dynamic SQL #

This is the most straightforward method that doesn't require extra plugins, though it has a few prerequisites:

1. Your SQL file must be in a directory that MySQL is allowed to access. Check the secure_file_priv variable with SHOW VARIABLES LIKE 'secure_file_priv';—this defines the only directory MySQL can read/write files from. If it's set to NULL, you'll need to update your my.cnf/my.ini config to set a valid path and restart MySQL.
2. The file must have read permissions for the user that the MySQL server runs as.

Here's how to integrate this into your stored procedure:

DELIMITER //
CREATE PROCEDURE YourExistingProcedure()
BEGIN
    -- Assuming @strSqlFilePath holds the full path to your trigger script
    SET @trigger_script = LOAD_FILE(@strSqlFilePath);

    -- Verify the file was read successfully
    IF @trigger_script IS NOT NULL THEN
        PREPARE stmt FROM @trigger_script;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        SELECT 'Trigger script executed successfully' AS status;
    ELSE
        SELECT 'Failed to read the SQL file. Check path, permissions, or secure_file_priv setting.' AS error_message;
    END IF;
END //
DELIMITER ;

This works because reading the entire script into a variable avoids the issues you hit with writing trigger code directly in PREPARE (like escaping nested quotes or syntax parsing quirks).

Workaround 2: Execute a system command with a UDF plugin (use with caution) #

If you can install a user-defined function (UDF) like lib_mysqludf_sys, you can call the mysql client via a system command to run your script. This is more powerful but way riskier—only use this in controlled environments.

Here's an example:

DELIMITER //
CREATE PROCEDURE YourExistingProcedure()
BEGIN
    -- Build the command to run the script (adjust username/password as needed)
    SET @run_cmd = CONCAT('mysql -u your_db_user -pyour_db_password ', DATABASE(), ' < ', @strSqlFilePath);
    -- sys_exec() returns the exit code (0 = success, non-0 = failure)
    SELECT sys_exec(@run_cmd) AS command_exit_status;
END //
DELIMITER ;

⚠️ Critical Note: This lets MySQL run arbitrary system commands, which is a huge security hole if misconfigured. Only use this if you fully trust the files being executed and have locked down database permissions tightly.

For your specific scenario #

As long as @strSqlFilePath is a valid, accessible path on the MySQL server (use an absolute path to avoid confusion), the first workaround should work perfectly for running your trigger script. The LOAD_FILE() method bypasses the PREPARE syntax issues you ran into earlier because you're executing the exact content of the file directly.

内容的提问来源于stack exchange，提问作者user9846973