Controleer of een entiteit bestaat (MySQL)
Versie door Jeroen Strompf (overleg | bijdragen) op 22 okt 2019 om 09:48
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