What does a MySQL "NO SQL" function mean exactly?

Question

What does the MySQL "NO SQL" function qualifer mean exactly? Suppose I have this function definition:

DELIMITER $$
CREATE FUNCTION Test() RETURNS text
NO SQL
BEGIN
  DECLARE x TEXT DEFAULT '';
  SET x = CONCAT('X = ', UUID());
  RETURN x;
END $$
DELIMITER ;

Is it correct that this should be declared as NO SQL? If not how should it be declared?

If the above should not be declared as "NO SQL", what is an example of such a function?

Answer 1

It means that the function or procedure contains no SQL in it.

From the online docs:

• NO SQL indicates that the routine contains no SQL statements.

It's probably in there as an optimisation assistance method, allowing the function to be flagged as not requiring processing that would be necessary for SQL. The other options in that group all indicate varying levels of data processing:

• CONTAINS SQL indicates that the routine does not contain statements that read or write data. This is the default if none of these characteristics is given explicitly. Examples of such statements are SET @x = 1 or DO RELEASE_LOCK('abc'), which execute but neither read nor write data.

• NO SQL indicates that the routine contains no SQL statements.

• READS SQL DATA indicates that the routine contains statements that read data (for example, SELECT), but not statements that write data.

• MODIFIES SQL DATA indicates that the routine contains statements that may write data (for example, INSERT or DELETE).

These are actually clauses defined by the SQL standard but it's interesting to note another quote from that linked page:

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.

In other words, they don't actually do anything in MySQL other than make the language compatible with the standards.

In terms of an example of a function that uses no SQL, you may have one that takes a string and returns a hash value for storage into a database later on.