MySql const values

Question

I am doing some math operations inside my database and I would like to declare const values that will be known in all my procedures.(Like PI for example)

Is there anything like this in MySql?

Answer 1

here som functions in mathematik

and u can define constants like that

 SET @myVar = 3;

EDIT HERE an example of this

   set @var1 := 0;
   set @var2 := @var1 := 5;
   select @var1, @var2;
    +--------+--------+
    | @var1 | @var2 |
    +--------+--------+
    | 5      | 5      | 
    +--------+--------+

here som examples

Answer 2

Old question, but as I'm now researching this issue myself here are my two cents:

As far as I can tell there is no such thing as a user definable constant. However you could create a function and have it return the value you want:

CREATE FUNCTION `kMyConstant` ()
    NO SQL
    DETERMINISTIC
RETURNS TINYINT UNSIGNED
BEGIN
RETURN 0;
END

This would obviously return 0 every time.

I'm pretty sure that it would be incredibly inefficient compared to just putting 0 in your code, but it would still run in a very small fraction of a second (each time).

Be warned that if put in a query like:update myTable set myVariable = kMyConstant(); There are two possible outcomes performance-wise:

• By putting DETERMINISTIC in the definition, as the parameters don't change per row it will only be evaluated once.
• The MySQL optimizer isn't that smart.

Performance issues aside at least you could then scatter kMyConstant() throughout your code and know that it's always going to be the same value, and if you wish to change the value returned you just change the function.

PS. If it does prove to re-evaluate it too often then you could always start your procedures set @kMyConstant = kMyConstant(); and then use @kMyConstant but the coding overheads are growing, so I guess your mileage will depend on how and how frequently you'll be using the constant.

Answer 3

If found the below solution satisfying. It is based on a compiler optimization that replaces tables with constants if the result of the expression can return only a single row, see the MySQL Manual. This is for instance the case when specifying the value of a unique, non-null column.

This makes especially sense if you have a few constants and don't wan't to create a function for each of them. As a bonus, you can conveniently edit the constant values (if they serve some configuration purpose, not necessarily if you want to change pi) by using some frontend – instead of redefining functions. It might also be easier to transfer the database, because dumping tables is easier than dumping functions.

CREATE TABLE `constant` (
  `id` varchar(45) NOT NULL,
  `double_value` DOUBLE DEFAULT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO constant VALUE ('pi', 3.1415);

SELECT constant.double_value / 4 FROM constant WHERE constant.id = 'pi';
-- is translated into SELECT 3.1415 / 4 

SELECT table1.field1 / constant.double_value 
  FROM table1, constant 
  WHERE constant.id = 'pi';
-- is translated into SELECT table1.field1 / 3.1415 FROM table1