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
I'd think it's probably not a best practice to use EXCEPTION
to do logic/handling, such as duplicate keys. Here's an idea: write a stored proc. Also create a sequence that has your alternate values that will be used in cases of duplicate keys. You might want to start this sequence with a fairly high value like 1,000,000 (or much higher, just depends on your actual data). Then do your inserts via this stored proc whenever you want the dupe-sub functionality.
CREATE TABLE components (
id_component INTEGER PRIMARY KEY
, code VARCHAR2(255)
, description VARCHAR2(255)
, model VARCHAR2(255)
, resp VARCHAR2(255)
);
CREATE SEQUENCE components_alt_id_seq
START WITH 1000000;
CREATE OR REPLACE PROCEDURE component_ins
( p_id_component IN components.id_component%TYPE
, p_code IN components.code%TYPE
, p_description IN components.description%TYPE
, p_model IN components.model%TYPE
, p_resp IN components.resp%TYPE )
AS
v_is_duplicate INTEGER;
BEGIN
SELECT count(*) INTO v_is_duplicate FROM components WHERE id_component = p_id_component;
IF v_duplicate = 0 THEN
INSERT INTO components (id_component, code, description, model, resp)
VALUES (p_id_component, p_code, p_description, p_model, p_resp);
ELSE
INSERT INTO components (id_component, code, description, model, resp)
VALUES (components_alt_id_seq.NEXTVAL, p_code, p_description, p_model, p_resp);
END IF;
END;
/
EXEC component_ins (39822, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')
EXEC component_ins (39823, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')
EXEC component_ins (39824, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')
EXEC component_ins (39822, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')
EXEC component_ins (39822, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')
EXEC component_ins (40015, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')
EXEC component_ins (40016, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')
And then checked the result. Notice that the duplicate input values were automatically subbed with a value taken from the sequence.
SELECT id_component FROM components;
ID_COMPONENT
------------
39822
39823
39824
40015
40016
1000005
1000006
7 rows selected.
Best Answer
Use
--complete-insert
Source: http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html#option_mysqldump_complete-insert