mysql trigger 文件_MySQL触发器将数据写入文件

There is a way to write data to disk from a trigger into a file? The file name need to be created dynamically.

Using PREPARE will not work on trigger.

e.g.

SET @Query1 =

CONCAT("SELECT ",'data'," INTO DUMPFILE 'c:/temp/", UNIX_TIMESTAMP(),"-",RAND(), ".txt'");

PREPARE statementCreate FROM @Query1;

EXECUTE statementCreate;

DEALLOCATE PREPARE statementCreate;

Note: data is a variable

Or there is other solution to append to the same file from the trigger? Will work that too.

解决方案

The solution is: MySQL UDF:http://www.mysqludf.org.

I install sys_exec function and after that from trigger I have something like

SET @ret_val = sys_exec(CONCAT('echo.>d:/temp/',NEW.id, '-', UNIX_TIMESTAMP() , '.txt'));

This will create a empty file for each insert. Tested on windows and linux(CentOS).