Как в MySQL запросе добавить колонку в таблицу с проверкой наличия этой колонки?

Question

[Сергей Кореневский]

Уважаемые Гуру и специалисты дайте совет пожалуйста.
Имеется узкие рамки создания пакета.
-Все запросы нужно упаковывать в файлы с расширением SQL
-В файле SQL хранится множество запросов
-В одном из запросов нужно добавить колонку в таблицу, но надо проверить отсутсвие этой колонки.

ALTER TABLE jshopping_orders ADD bonus varchar(24) NOT NULL DEFAULT '';

Comments

[Akina]

Вот нафига это НАДО, а? кто сказал, что надо проверять? и зачем?

Не проверяй ничего. Если есть такое поле - этот запрос выполнится с ошибкой... да и фиг бы с им. На остальные запросы пакета это никак не повлияет.

[Сергей Кореневский]

Akina, Вам бы лиж бы просто доказать что кто то не прав в интернете.
Наверно Вы посчитали что Вы в этом разбираетесь лучше меня. Вы наверно Венец MySQL знаний.

Я делаю расширение для CMS Joomla. В пакет расширения кладу SQL файл с множествами запросов.
При установке по очереди эти запросы выполняются в JOOMLA инсталяторе.
Если один из запросов вернет ошибку, то все запросы остальные прервутся на выполнение. И вся установка прервется.
Напомню что Joomla 2ое место в мире среди CMS занимает.

[Akina]

Я делаю расширение для CMS Joomla. В пакет расширения кладу SQL файл с множествами запросов.
При установке по очереди эти запросы выполняются в JOOMLA инсталяторе.
Если один из запросов вернет ошибку, то все запросы остальные прервутся на выполнение. И вся установка прервется.

А сразу сказать, что используется не выполнение скрипта через SOURCE, а парсинг и отдельное выполнение запросов сторонними средствами? и что у этого стороннего средства имеет место подобный идиотизм? не, не слышали...

Тогда именно в случае MySQL (не MariaDB) выход один - включение в файл запросов на создание и на выполнение процедуры, которая проделает указанные операции. И то - при условии, что инсталлятор способен отличить создание процедуры от пакета независимых запросов.

Вы наверно Венец MySQL знаний.

Да... только кто ж это ценит?

[Сергей Кореневский]

Akina,
В общем код, который работает в версии 5,7 не работает при установке JOomla
Так как установщик его скорей всего режит по разделителю ";"
А в моем коду есть другие разделители DELIMITER $$, что Вызывает ошибку.
Пришлось эту часть установки перенести в PHP

[Akina]

Сергей Кореневский, посмотрите, возможно ли в установщике дёрнуть за структуру таблицы, и выполнять .SQL-скрипт только если её нет...

Answer 1

[Slava Rozhnev]

MariaDB позвщляет синтакс:

ALTER TABLE jshopping_orders ADD COLUMN IF NOT EXISTS bonus varchar(24) NOT NULL DEFAULT '';

View MariaDB fiddle

Comments

[Сергей Кореневский]

Увы это не работает в MySQL версии 5.7 , а для меня это оказалось очень Важным.
О! Вы настоящий Гуру, я блин искал песочницы MySQL, всякая хрень с уроками попадается. А у Вас хорошая ссылка на песочницу.
Но и в 8 версии MySQL это НЕ работает.

[Сергей Кореневский]

@rozhnev
Не работает этот код, подскажите в чем ошибка?

DELIMITER $$
DROP PROCEDURE IF EXISTS addColumnToTable $$
CREATE PROCEDURE addColumnToTable(IN tbl_name VARCHAR(20), IN col_name VARCHAR(20))
BEGIN
IF 
NOT EXISTS( (SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE()
        AND TABLE_NAME = tbl_name AND COLUMN_NAME = col_name ) ) 
THEN
    ALTER TABLE tbl_name ADD col_name varchar(24) NOT NULL DEFAULT '';
END IF;
END $$
DELIMITER ;
--   --------------------- Procedure End
CALL addColumnToTable('tbl','bonus');
CALL addColumnToTable('tbl','bonus2');
CALL addColumnToTable('tbl','bonus3');

Пишет что таблицы tbl_name такой не существует

[Akina]

в чем ошибка?

Имя объекта БД в запросе - это литерал, а не параметр. Не рассчитывайте на подстановку в текст запроса содержимого переменной.

В данном случае следует использовать динамический SQL (prepared statement).

PS. Согласно документации, имена объектов соответствуют VARCHAR(64) - именно этот тип следует использовать для параметров процедуры.

[Slava Rozhnev]

DELIMITER $$
CREATE PROCEDURE addColumnToTable(IN tbl_name VARCHAR(64), IN col_name VARCHAR(64))
BEGIN

  IF ((
	SELECT COUNT(*) FROM information_schema.COLUMNS 
	WHERE TABLE_NAME = tbl_name AND COLUMN_NAME = col_name
  )=0) THEN
	  SET @query = CONCAT('ALTER TABLE ', tbl_name, ' ADD ', col_name, ' varchar(24) NOT NULL DEFAULT "";');
	  
	  PREPARE stmt FROM @query;
	  EXECUTE stmt; 
	  DEALLOCATE PREPARE stmt;
	  
  END IF;
END $$
DELIMITER ;

MySQL fiddle

Answer 2

[Борис Сёмов]

Можно проверить в INFORMATION_SCHEMA наличие столбца:

IF NOT EXISTS( SELECT NULL
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'jshopping_orders'
       AND table_schema = (SELECT DATABASE())
       AND column_name = 'bonus')  
THEN
  ALTER TABLE `jshopping_orders` ADD bonus varchar(24) NOT NULL DEFAULT '';
END IF;

!Написано без проверки могут быть опечатки, работоспособность "как есть" не гарантируется. =)

Comments

[Сергей Кореневский]

Исправьте Ваш комментарий, напишите в нем в какой версии MySQL работает. и я отмечу ее решением.
Но она в MySQL 5.7 НЕ РАБОТАЕТ К СОЖАЛЕНИЮ. В общем то я вначале не знал требование версии базы. Проверял каждый запрос.

[Сергей Кореневский]

Борис Сёмов
Не работает этот код, подскажите в чем ошибка?

DELIMITER $$
DROP PROCEDURE IF EXISTS addColumnToTable $$
CREATE PROCEDURE addColumnToTable(IN tbl_name VARCHAR(20), IN col_name VARCHAR(20))
BEGIN
IF 
NOT EXISTS( (SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE()
        AND TABLE_NAME = tbl_name AND COLUMN_NAME = col_name ) ) 
THEN
    ALTER TABLE tbl_name ADD col_name varchar(24) NOT NULL DEFAULT '';
END IF;
END $$
DELIMITER ;
--   --------------------- Procedure End
CALL addColumnToTable('tbl','bonus');
CALL addColumnToTable('tbl','bonus2');
CALL addColumnToTable('tbl','bonus3');

Пишет что таблицы tbl_name такой не существует

Answer 3

[Сергей Кореневский]

Нашел ответ!
Поддерживает версию MySQL 5.7 !
Дождешься тут Гуру и специалистов.
При помощи //duckduckgo.com нашёл ответ.
Яндекс вообще не чего не ищет. Да и браузер свой сделать по нормальному как Chrome ни как не могут уже второй год.
Это Все один запрос такой длинный, но он работает:

DELIMITER $$
DROP PROCEDURE IF EXISTS addColumnToTable $$
CREATE PROCEDURE addColumnToTable()
BEGIN
IF 
NOT EXISTS( (SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE()
        AND COLUMN_NAME='column_name' AND TABLE_NAME='table_name') ) 
THEN
    ALTER TABLE table_name ADD column_name varchar(24) NOT NULL DEFAULT '';
END IF;
END $$
CALL addColumnToTable() $$
DELIMITER ;

SET @s = (SELECT IF(
    (SELECT COUNT(*)
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE table_name = 'tbl'
        AND table_schema = DATABASE()
        AND column_name = 'bonus'
    ) > 0,
    "SELECT 1",
    "ALTER TABLE tbl ADD bonus VARCHAR(100)"
));

PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Comments

[Akina]

Это - из пушки по воробьям.

[Vitsliputsli]

Если вам нужно подобное, значит вы утратили контроль над структурой БД. Здесь нужно внедрить нормальный механизм миграций, вместо написания монструозных костылей.

[Сергей Кореневский]

Akina, Vitsliputsli зато поддерживает MySQL 5.7
Выше описанные альтернативные решения выдают ошибку в этой базе.
К сожалению у моего хостинга TimeWeb и на хостинге(другой) моего клиента именно эта версия базы.
А что предложите Вы?

[Сергей Кореневский]

DELIMITER $$
DROP PROCEDURE IF EXISTS addColumnToTable $$
CREATE PROCEDURE addColumnToTable(IN tbl_name VARCHAR(20), IN col_name VARCHAR(20))
BEGIN
DECLARE s VARCHAR(100);

SET s = (SELECT IF(
    (SELECT COUNT(*)
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE table_name = tbl_name
        AND table_schema = DATABASE()
        AND column_name = col_name
    ) > 0,
    "SELECT 1",
    CONCAT("ALTER TABLE ",tbl_name," ADD ",col_name," VARCHAR(100)")
));
PREPARE stmt FROM s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END $$
DELIMITER ;

 
CALL addColumnToTable('tbl','bonus');
CALL addColumnToTable('tbl','bonus2');
CALL addColumnToTable('tbl','bonus3');

[Vitsliputsli]

Сергей Кореневский, уже предложил выше. Изменения структуры бд не должны проходить наугад. Любой вменяемый механизм миграций гарантирует наличие столбца, если миграция применена и его отсутствие, если не применена. Т.е. при нормальной работе, когда сохраняется контроль над базой, подобные костыли не нужны.