Controleer of een entiteit bestaat (MySQL)

Zo controleer je of een tabel of kolom bestaat!

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";

Voorbeeld 1:

select count(*) from information_schema.tables
where table_schema='koolborstels'
and table_name='term_data_bk01';

Resultaat:

+----------+--------------+----------------+
| count(*) | table_schema | table_name     |
+----------+--------------+----------------+
|        1 | koolborstels | term_data_bk01 |
+----------+--------------+----------------+

Voorbeeld 2:

select count(*) from information_schema.tables
where table_schema='koolborstels'
and table_name='term_data_bk0fdsfdsfdsfds1';

Resultaat:

+----------+--------------+----------------+
| count(*) | table_schema | table_name     |
+----------+--------------+----------------+
|        0 | koolborstels | term_data_bk0fdsfdsfdsfds1 |
+----------+--------------+----------------+

Dit genereert géén foutmelding.

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