0

What are the comparative benefits of these two statements,

SET @stmt = (SELECT IF(
  (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE   TABLE_SCHEMA = DATABASE() AND
            TABLE_NAME = 'foo' AND
            COLUMN_NAME = 'bar'
  ) > 0,
  'DO 0',
  'ALTER TABLE `foo` ADD COLUMN `bar` varchar(32) NOT NULL;'
));
PREPARE sth FROM @stmt;
EXECUTE sth;
DEALLOCATE PREPARE sth;

vs..

DROP PROCEDURE IF EXISTS `proc`;
DELIMITER $$
CREATE PROCEDURE proc()
BEGIN
    DECLARE _count INT;
    SET _count = (  SELECT COUNT(*)
                    FROM INFORMATION_SCHEMA.COLUMNS
                    WHERE   TABLE_SCHEMA = DATABASE() AND
                            TABLE_NAME = 'foo' AND
                            COLUMN_NAME = 'bar');
    IF _count = 0 THEN
        ALTER TABLE `foo` ADD COLUMN `bar` varchar(32) NOT NULL;
    END IF;
END $$
DELIMITER ;
CALL proc();
DROP PROCEDURE `proc`;

I'm just wondering if either approach is prefered over the other?

0

NOT EXISTS is more readable and does not need to go through to get a full count.

BEGIN
    IF (NOT EXISTS(SELECT 1
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = 'foo'
              AND COLUMN_NAME = 'bar' ))
    THEN
        ALTER TABLE `foo` ADD COLUMN `bar` varchar(32) NOT NULL;
    END IF;
END $$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.