Evaluate an expression in MySQL
I have a table with column a, b, expr, which expr is an expression from another column. for example the table contains
a b expr
------------
2 5 a+b
3 4 a*b+3
I like to just run the query and get the current results:
a | b | expr
------------
2 | 5 | 7
3 | 4 | 15
I'm looking for a doc for a function, procedure, etc., but I can't seem to do it. please help me!
+3
source to share
2 answers
Here is the solution I made for fun . Think about it using a real programming language if it's not one time.
drop table if exists Table1;
CREATE TABLE Table1
(`a` int, `b` int, `expr` varchar(6))
;
INSERT INTO Table1
(`a`, `b`, `expr`)
VALUES
(2, 5, 'a+b'),
(3, 4, 'a*b +3')
;
drop table if exists Table2;
CREATE TABLE Table2
(`a` int, `b` int, `expr` int)
;
drop procedure if exists curdemo;
DELIMITER $$
CREATE PROCEDURE curdemo()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE seqel VARCHAR(255);
DECLARE cur1 CURSOR FOR SELECT CONCAT('INSERT INTO Table2 (a, b, expr) SELECT a, b, ' , expr, ' FROM (SELECT ', a, ' as a, ', b, ' as b) sq;') FROM Table1;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO seqel;
IF NOT done THEN
SET @sql:=seqel;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
END $$
DELIMITER ;
CALL curdemo();
SELECT * FROM Table2;
+2
source to share