Store equations/formula in mysql db table

Question

I want to know what is there a way for storing equations or formula in a DB column. Below is the table where I am storing the machines details(master table). But the last column is what I am worrying.

Columns and values for the table would be

id,name,col1,col2,col3,col4,formula
1,mc1,546,459,1.24,3.9,(col1 * col2 * 0.0016)/(col3 * col4)

The formula changes for each entry in the table. Currently I am hard-coding the formula's in my program like

if(macid == 1) {
 // apply formula1
} else if(macid == 2) {
 // apply formula2
} .....

Now if any new machine gets added in the table I need to make changes int the code. So I want to store the formula in the table itself, so that I can provide UI interface to change the formula.

How can I achieve this. Plese help.

• peterm: How badly do formulas vary? Can you add a few more real examples?
• Nakul Sargur: second machine may have, another formula like, (col1 * 2 * 0.0016)/(col 3 * col4). The problem is when i want to add a new machine to the table and user wants to enter the formula and save it for that from the UI. Now i need to add one more else if in my code.
• peterm: See my answer. Dynamic SQL is the only solution I can possibly think of when you potentially have different formulas for every row

Answer 1

You can try to leverage dynamic SQL.

If you need to get a calculated value for an id

DELIMITER $$
CREATE PROCEDURE get_value(IN _id INT)
BEGIN
 SET @sql = NULL;
 SELECT CONCAT('SELECT ', formula, ' value FROM table1 WHERE id = ', 1)
 INTO @sql
 FROM table1
 WHERE id = 1;
 PREPARE stmt FROM @sql;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
 SET @sql = NULL;
END$$
DELIMITER ;

Note: You can of course use OUT parameter instead of returning the resultset if you want to.

Sample usage:

CALL get_value(1);

Sample output:

| VALUE |
|-----------------|
| 82.916129032258 |

Here is how a procedure might look like to get all values calculated by formulas

DELIMITER $$
CREATE PROCEDURE get_values()
BEGIN
 SET @sql = NULL;
 SELECT GROUP_CONCAT(CONCAT(
 'SELECT id, ', formula, ' value FROM table1 WHERE id = ', id)
 ORDER BY id SEPARATOR ' UNION ALL ')
 INTO @sql
 FROM table1;
 PREPARE stmt FROM @sql;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
 SET @sql = NULL;
END$$
DELIMITER ;

Sample usage:

CALL get_value(1);

Sample output:

| ID | VALUE |
|----|-----------------|
| 1 | 82.916129032258 |
| 2 | 0.0000109375 |

Here is SQLFiddle demo for both procedures

• peterm: Did it help? Do you need more help with your question?