mysql:如果列不存在,则更改表

nwlqm0z1  于 2021-07-26  发布在  Java
关注(0)|答案(10)|浏览(418)

我有这个密码:

ALTER TABLE `settings`
ADD COLUMN `multi_user` TINYINT(1) NOT NULL DEFAULT 1

我只想在这个列不存在的情况下修改这个表。
我尝试了很多不同的方法,但都不管用:

ALTER TABLE `settings`
ADD COLUMN IF NOT EXISTS `multi_user` TINYINT(1) NOT NULL DEFAULT 1

使用程序:

DELIMITER $$
CREATE PROCEDURE Alter_Table()
BEGIN
    DECLARE _count INT;
    SET _count = (  SELECT COUNT(*) 
                    FROM INFORMATION_SCHEMA.COLUMNS
                    WHERE   TABLE_NAME = 'settings' AND 
                            COLUMN_NAME = 'multi_user');
    IF _count = 0 THEN
        ALTER TABLE `settings` ADD COLUMN `multi_user` TINYINT(1) NOT NULL DEFAULT 1
    END IF;
END $$
DELIMITER ;

我最后出错了,如果,然后在最后,然后在1
我怎样才能使这个尽可能简单?

b4lqfgs4

b4lqfgs41#

在存储过程中使用以下命令:

IF NOT EXISTS( SELECT NULL
            FROM INFORMATION_SCHEMA.COLUMNS
           WHERE table_name = 'tablename'
             AND table_schema = 'db_name'
             AND column_name = 'columnname')  THEN

  ALTER TABLE `TableName` ADD `ColumnName` int(1) NOT NULL default '0';

END IF;
smtd7mpg

smtd7mpg2#

使用 PREPARE / EXECUTE 以及查询模式。主机不需要具有创建或运行过程的权限:

SET @dbname = DATABASE();
SET @tablename = "tableName";
SET @columnname = "colName";
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (table_name = @tablename)
      AND (table_schema = @dbname)
      AND (column_name = @columnname)
  ) > 0,
  "SELECT 1",
  CONCAT("ALTER TABLE ", @tablename, " ADD ", @columnname, " INT(11);")
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;
yjghlzjz

yjghlzjz3#

这里有一个不涉及查询的解决方案 INFORMATION_SCHEMA ,如果列确实存在,它将忽略错误。

DROP PROCEDURE IF EXISTS `?`;
DELIMITER //
CREATE PROCEDURE `?`()
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
  ALTER TABLE `table_name` ADD COLUMN `column_name` INTEGER;
END //
DELIMITER ;
CALL `?`();
DROP PROCEDURE `?`;

p、 请随便给它起个别的名字,而不是 ?

o0lyfsai

o0lyfsai4#

添加字段(如果不存在):

CALL addFieldIfNotExists ('settings', 'multi_user', 'TINYINT(1) NOT NULL DEFAULT 1');

addfieldifnotexists代码:

DELIMITER $$

DROP PROCEDURE IF EXISTS addFieldIfNotExists 
$$

DROP FUNCTION IF EXISTS isFieldExisting 
$$

CREATE FUNCTION isFieldExisting (table_name_IN VARCHAR(100), field_name_IN VARCHAR(100)) 
RETURNS INT
RETURN (
    SELECT COUNT(COLUMN_NAME) 
    FROM INFORMATION_SCHEMA.columns 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = table_name_IN 
    AND COLUMN_NAME = field_name_IN
)
$$

CREATE PROCEDURE addFieldIfNotExists (
    IN table_name_IN VARCHAR(100)
    , IN field_name_IN VARCHAR(100)
    , IN field_definition_IN VARCHAR(100)
)
BEGIN

    SET @isFieldThere = isFieldExisting(table_name_IN, field_name_IN);
    IF (@isFieldThere = 0) THEN

        SET @ddl = CONCAT('ALTER TABLE ', table_name_IN);
        SET @ddl = CONCAT(@ddl, ' ', 'ADD COLUMN') ;
        SET @ddl = CONCAT(@ddl, ' ', field_name_IN);
        SET @ddl = CONCAT(@ddl, ' ', field_definition_IN);

        PREPARE stmt FROM @ddl;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

    END IF;

END;
$$

这不是原件,但它是复制和粘贴
来源:javajon.blogspot.com/2012/10/

hgncfbus

hgncfbus5#

希望这对你有帮助

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'tbl_name'
AND table_schema = 'db_name'
AND column_name = 'column_name'

delimiter '//'

CREATE PROCEDURE addcol() BEGIN
IF NOT EXISTS(
SELECT * FROM information_schema.COLUMNS
WHERE COLUMN_NAME='new_column' AND TABLE_NAME='tablename' AND TABLE_SCHEMA='the_schema'
)
THEN
    ALTER TABLE `the_schema`.`the_table`
    ADD COLUMN `new_column` TINYINT(1) NOT NULL DEFAULT 1;;

END IF;
END;
//

delimiter ';'

CALL addcol();

DROP PROCEDURE addcol;
o0lyfsai

o0lyfsai6#

我使用了这种方法(不使用存储过程): SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tbl_name' AND COLUMN_NAME = 'column_name' 如果未返回任何行,则该列不存在,然后更改表: ALTER TABLE tbl_name ADD COLUMN column_name TINYINT(1) NOT NULL DEFAULT 1 希望这有帮助。

vsmadaxz

vsmadaxz7#

$sql="SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Your_Table_Name' AND COLUMN_NAME = 'Your_New_Column_Name'";
$RESULT = mysqli_query($conn,$sql);

abobe查询返回0如果表中不存在该列,则需要如下运行alterquery

if($RESULT){
    $sqll="ALTER TABLE Your_table_Name ADD COLUMN Your_New_Column_Name varchar(20) NOT NULL DEFAULT 0";
}
kuhbmx9i

kuhbmx9i8#

SET @dbname = DATABASE();
SET @tablename = "table";
SET @columnname = "fieldname";
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (table_name = @tablename)
      AND (table_schema = @dbname)
      AND (column_name = @columnname)
  ) > 0,
  "SELECT 1",
  CONCAT("ALTER TABLE ", @tablename, " ADD ", @columnname, " DECIMAL(18,4) NULL;")
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;
siotufzp

siotufzp9#

有时在数据库中可能会创建多个模式。
所以要成为我们需要针对的特定模式,这将有助于做到这一点。

SELECT count(*) into @colCnt FROM information_schema.columns WHERE table_name = 'mytable' AND column_name = 'mycolumn' and table_schema = DATABASE();
IF @colCnt = 0 THEN
    ALTER TABLE `mytable` ADD COLUMN `mycolumn` VARCHAR(20) DEFAULT NULL;
END IF;
uplii1fm

uplii1fm10#

下面这个对我有用:

SELECT count(*)
    INTO @exist
    FROM information_schema.columns
    WHERE table_schema = 'mydatabase'
    and COLUMN_NAME = 'mycolumn'
    AND table_name = 'mytable' LIMIT 1;

    set @query = IF(@exist <= 0, 'ALTER TABLE mydatabase.`mytable`  ADD COLUMN `mycolumn` MEDIUMTEXT NULL',
    'select \'Column Exists\' status');

    prepare stmt from @query;

    EXECUTE stmt;

相关问题