MySQL Function Characteristic

1
votes

I want to create a function that basically returns a random string. I don't know what characteristics to assign in this situation. I'm also in an environment that uses binary logging.

Here's a simplified version of my function:

CREATE FUNCTION `MYRAND`() RETURNS char(10) NOT DETERMINISTIC
  RETURN CONCAT('rand_', FLOOR(RAND() * 10000));

I get this error when creating the function in my environment.

This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)

Possible characteristics:

  • NOT DETERMINISTIC - used because this function returns random values
  • READS/MODIFIES SQL DATA - Function does not read data from tables
  • NO SQL - I am calling other SQL functions (RAND) so I'm not sure if I should be specifying this or not...

Any advice on how to properly define this function when binary logging is enabled would be appreciated.

2
mysqlfunction
You don't use any data in your DB in this finction. Thus NO SQL would be correct. RAND() is not an "SQL function". - Paul Spiegel
Ah, thanks for the clarification! If you want to turn this into an answer I'll gladly accept it. - mark
Would I still use NOT DETERMINISTIC, or does that only pertain to data access/modification? - mark
Since you know that your function is NOT DETERMINISTIC, I would keep that declaration - just because it's correct. But I can't say if that makes any difference. I just guess that the declaration is used to decide, if the result can be cached and reused. - Paul Spiegel

2 Answers

3
votes

MySQL wants you to declare the function as DETERMINISTIC, NO SQL, or READS SQL DATA.

Is it DETERMINISTIC? No - Since it is random.

Does id read SQL DATA? No - Since you have no SELECT statement.

Does it modify SQL DATA? No - Since you have no INSERT, UPDATE or DELETE statement.

Since your function does not touch any data in the DB it's NO SQL.

So you should declare it as NOT DETERMINISTIC and NO SQL

CREATE FUNCTION `MYRAND`() RETURNS char(10) NOT DETERMINISTIC NO SQL
  RETURN CONCAT('rand_', FLOOR(RAND() * 10000));
0
votes

Use somthing like this code:

CREATE FUNCTION get_string(in_strlen int) RETURNS VARCHAR(500) DETERMINISTIC
    BEGIN 
    set @var:='';
    while(in_strlen>0) do
    set @var:=concat(@var,IFNULL(ELT(1+FLOOR(RAND() * 1000),1,2,3,4,5,6,7,8,9));
    set in_strlen:=in_strlen-1;
    end while;  
    RETURN @var;
    END