无法在表中插入列 [英] Cannot insert a column in table

问题描述

我有一个SQL查询

I have a sql query

delimiter '$$'
DROP PROCEDURE IF Exists addcol $$

CREATE PROCEDURE addcol() BEGIN
IF NOT EXISTS ( (
	SELECT * FROM information_schema.COLUMNS
	WHERE COLUMN_NAME='newcol' AND TABLE_NAME='pp_tbl1' AND TABLE_SCHEMA='mydb'
	) )	THEN 
	ALTER TABLE `mydb`.`pp_tbl1` ADD COLUMN `newcol` bit  NOT NULL default 0;
END IF;
END$$
delimiter ';'
CALL addcol();
DROP PROCEDURE addcol;

之前我添加了一个列''newcol''，但是使用Navicat gui将其删除然后运行此查询

但它给了我0行影响
我执行此查询时

previously i added a column ''newcol'' but deleted it using Navicat gui and then ran this query
but it is giving me 0 rows effected
when i execute this query

SELECT * FROM information_schema.COLUMNS
	WHERE COLUMN_NAME='newcol' AND TABLE_NAME='pp_tbl1' AND TABLE_SCHEMA='mydb'

它给我的结果但我找不到列中的我的桌子



i我正在使用mysql 5.1



还检查以下查询，如果可能的话，该查询无法正常工作

it is giving me result but i cannot find the column in my table

i am using mysql 5.1

also check the below query which is not working correct if possible

delimiter ';;'
create procedure foo ()
begin
    declare continue handler for 1060 begin end;
    alter table `pp_tbl1` add `newcol` bit  NOT NULL default 0;
end;;
call foo();;

推荐答案

'
DROP 程序 IF 存在 addcol

' DROP PROCEDURE IF Exists addcol

CREATE PROCEDURE addcol（） BEGIN
IF NOT EXISTS （（
SELECT * FROM information_schema .COLUMNS
WHERE COLUMN_NAME = ' newcol' AND TABLE_NAME = ' pp_tbl1' AND TABLE_SCHEMA = ' mydb'
））那么
ALTER 表`mydb``pp_tbl1` ADD COLUMN `newcol` bit NOT NULL 默认 0 ;
END IF ;
END

CREATE PROCEDURE addcol() BEGIN IF NOT EXISTS ( ( SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='newcol' AND TABLE_NAME='pp_tbl1' AND TABLE_SCHEMA='mydb' ) ) THEN ALTER TABLE `mydb`.`pp_tbl1` ADD COLUMN `newcol` bit NOT NULL default 0; END IF; END

delimiter ' ;'
CALL addcol（）;
DROP PROCEDURE addcol;

delimiter ';' CALL addcol(); DROP PROCEDURE addcol;

之前我添加了一个列''newcol''，但是使用Navicat gui将其删除然后运行此查询

但它给了我0行影响
我执行此查询时

previously i added a column ''newcol'' but deleted it using Navicat gui and then ran this query
but it is giving me 0 rows effected
when i execute this query

SELECT * FROM information_schema.COLUMNS
	WHERE COLUMN_NAME='newcol' AND TABLE_NAME='pp_tbl1' AND TABLE_SCHEMA='mydb'

它给我的结果但我找不到列中的我的桌子



i我正在使用mysql 5.1



还检查以下查询，如果可能的话，该查询无法正常工作

it is giving me result but i cannot find the column in my table

i am using mysql 5.1

also check the below query which is not working correct if possible

delimiter ';;'
create procedure foo ()
begin
    declare continue handler for 1060 begin end;
    alter table `pp_tbl1` add `newcol` bit  NOT NULL default 0;
end;;
call foo();;

这篇关于无法在表中插入列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！