Skip to content

MySQL – CREATE FUNCTION ? MODIFIES SQL DATA?

I am about to write a CREATE FUNCTION with MySQL and I am wondering, if CREATE TEMPORARY TABLE counts toward flag MODIFIES SQL DATA.
Function does not modify any permanent table, just temporary table, which it creates for optimization purpose.

Should I use flag MODIFIES SQL DATA or only READS SQL DATA?
What is a really benefit to use MODIFIES SQL DATA or READS SQL DATA flag anyway?

Answer

As of now (MySQL 5.5) these characteristics serve only as an in-code documentation.

From http://dev.mysql.com/doc/refman/5.5/en/create-procedure.html

Several characteristics provide information about the nature of data
use by the routine. In MySQL, these characteristics are advisory only.
The server does not use them to constrain what kinds of statements a
routine will be permitted to execute.

This is in contrast with (NON)DETERMINISTIC clause, which serves as a hint to optimizer whether the results of function can be cached.