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.
En het kan nóg mooier:
CREATE DEFINER=`root`@`localhost` PROCEDURE `does_table_exist` ( in db_name_in tinytext, in tb_name_in tinytext ) BEGIN set @table_exists= ( select count(*) from information_schema.tables where table_schema=db_name_in and table_name=tb_name_in ); if @table_exists then select "Tabel bestaat"; else select "Tabel bestaat niet"; end if; END
Of zonder tussen-var (maakt de code waarschijnlijk onnodig ingewikkeld):
CREATE DEFINER=`root`@`localhost` PROCEDURE `does_table_exist`( in db_name_in tinytext, in tb_name_in tinytext ) BEGIN if ( select count(*) from information_schema.tables where table_schema=db_name_in 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