Controleer of een entiteit bestaat (MySQL)

Zo controleer je of een tabel of kolom bestaat!

Database

Kijk maar:

select
   *
from
   information_schema.schemata
where
   schema_name = "wp_tmp";

Voorbeeld van gebruik voor flow control in een sproc:

CREATE DEFINER=`strompf`@`localhost` PROCEDURE `sanitize`
(
   in source_db	tinytext,
   in dest_db tinytext
)
BEGIN
#
#
################################################################################
# Check for existence of dest-db
################################################################################
#
# You can use a variable here in WHERE statement
#
if
(
   select 
      count(*)
   from
      information_schema.schemata
   where
      schema_name = dest_db
)
then
   select "Bestaat";
else
   select "Bestaat niet";
end if;

Tabel

Mischien de elegantste manier om te verifiëren of een bepaalde tabel bestaat:

select
   count(*)
from
   information_schema.tables 
where
   table_schema = "database name"
   and
   table_name = "table name";

of als het om de huidige db gaat:

select 
   count(*) 
from 
   information_schema.tables
where
   table_schema=database()
   and
   table_name="root"

Voor flow control in een sproc:

CREATE DEFINER=`root`@`localhost` PROCEDURE `do_something_to_table_root`
(
   in tb_name_in   tinytext
)
BEGIN

if
(
   select count(*) from information_schema.tables
   where
      table_schema=database()
      and    
      table_name=tb_name_in
)
then
   select "bestaat";
else
   select "bestaat niet";
end if;

END

Kolom

Zie deze schitterende code, afkomstig van m'n sproc add_column_unless_exists():

CREATE DEFINER=`root`@`localhost` PROCEDURE `add_column_unless_exists`(
    IN tableName tinytext,
    IN fieldName tinytext,
    in fieldDef varchar(100)
)
begin

# Add a column to the given table in the current database
#######################################################################################################
#
# Important to include the name of the current db in the query below. If this column happens to be 
# available in another database, this check wouldn't work
#
# Example
#####################
#
# call add_column_unless_exists("item_name_tmp","cdiscount_basket_short_wording","varchar(30)");
#
if not exists
(
	select * from information_schema.columns
	where column_name=fieldname
	and table_name=tablename
	and table_schema=database()
)
then
	set @ddl=concat
        (
	   'alter table ',database(), '.', tablename,
	   ' add column ',fieldname," ",fielddef
	);

	# select @ddl;
	
	prepare stmt from @ddl;
	execute stmt;
	deallocate prepare stmt;

end if;
end

Bronnen

• http://stackoverflow.com/questions/1525784/mysql-check-if-a-table-exists-without-throwing-an-exception