Geek Answers Handbook

MySql Constant Values

I do some math operations inside my database and I would like to declare const values ​​that will be known in all my procedures (e.g. PI)

Is there anything similar in MySql?

+4
source share
3 answers

If a satisfied solution is found below. It is based on compiler optimization, which replaces tables with constants if the result of an expression can return only one row, see the MySQL Guide . This, for example, is the case when a unique, non-null column value is specified.

This is especially important if you have several constants and do not need to create a function for each of them. As a bonus, you can conveniently edit constant values ​​(if they serve for some configuration purpose, not necessarily if you want to change pi) using some interface, instead of overriding functions. It may also be easier to migrate the database, since dump tables are simpler 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
+1
source

here som functions in mathematik

and u can define constants such as

  SET @myVar = 3;

EDIT HERE is an example of this

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

here are som examples

+2
source

An old question, but since I'm studying this problem right now, here are my two cents:

As far as I can tell, there is no such thing as a user-defined constant. However, you can create a function and return it to the value you need:

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

This will obviously return 0 every time.

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

We will warn that if you put a request like: update myTable set myVariable = kMyConstant(); There are two possible performance results:

  • By placing DETERMINISTIC in the definition, since the parameters do not change for each row, it will be evaluated only once.
  • MySQL optimizer is not so smart.

Performance problems, at least you could scatter kMyConstant () throughout your code and know that it will always be the same value, and if you want to change the return value, you just change the function.

PS. If this is overestimated too often, you can always start your routines set @kMyConstant = kMyConstant(); and then use @kMyConstant , but the coding overhead is increasing, so I assume that your mileage will depend on how and how often you use the constant.

+2
source

More articles:


All Articles