[SOLVED] How can i define variable for default values in MariaDB structure?

Issue

I need to change a lot default values of the DB structure for new entries in MULTIPLE tables to have this new default value in DB I tried to use something like this:

SET @money_rename_def := 20*10000;
SET @money_gender_def := 40*10000;

ALTER TABLE `money`     CHANGE `money_rename` `money_rename` int(10) UNSIGNED NOT NULL DEFAULT '@money_rename_def', 
                        CHANGE `money_gender` `money_gender` int(10) UNSIGNED NOT NULL DEFAULT '@money_gender_def';

But the SET prefix does not fork for alter table command. Is there any way how to do this to use pre-defined value so I can only change it once in SET or simillar definition? I tried to search documentation but maybe just missed it?

Solution

You can use dynamic query like this:

DROP PROCEDURE IF EXISTS `tableDefaultSetter`$$
CREATE PROCEDURE `tableDefaultSetter`()
BEGIN

    SET @default1 = 20;

    SET @query = concat('ALTER TABLE `ttestt` CHANGE `val` `val` int NOT NULL DEFAULT ', @default1);
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END$$

Sample Data:

DROP TABLE IF EXISTS ttestt;
CREATE TABLE ttestt
(
    id  INT,
    val INT(10) DEFAULT 10
);
INSERT INTO ttestt (id)
VALUES (1);

CALL tableDefaultSetter();

INSERT INTO ttestt (id)
VALUES (1);
SELECT *
FROM ttestt;

Result:

1,10
1,20

So the first item had 10 as default value and second item has been changed to 20. You see that it works.

---

For multiple values, you cannot put multiple queries inside one statement: Doc

The text must represent a single statement, not multiple statements.

So you can create another procedure for convenience:

DELIMITER $$

DROP PROCEDURE IF EXISTS `exec_query`$$
CREATE PROCEDURE `exec_query`(queryStr TEXT)
BEGIN

    SET @query = queryStr;
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END$$


DROP PROCEDURE IF EXISTS `tableDefaultSetter`$$
CREATE PROCEDURE `tableDefaultSetter`()
BEGIN

    SET @default1 = 20;
    SET @default2 = 30;
    SET @default3 = 40;
    SET @default4 = 50;

    CALL exec_query(concat('ALTER TABLE `ttestt` CHANGE `val` `val` int NOT NULL DEFAULT ', @default1));
    CALL exec_query(concat('ALTER TABLE `ttestt2` CHANGE `val` `val` int NOT NULL DEFAULT ', @default2));
    CALL exec_query(concat('ALTER TABLE `ttestt3` CHANGE `val` `val` int NOT NULL DEFAULT ', @default3));
    CALL exec_query(concat('ALTER TABLE `ttestt4` CHANGE `val` `val` int NOT NULL DEFAULT ', @default4));

END$$

DELIMITER ;

Use it like this:

CALL tableDefaultSetter();
DROP PROCEDURE `tableDefaultSetter`;
DROP PROCEDURE `exec_query`;

Answered By - Soheil Rahsaz