transactions - can we run code in mysql without creating a procedure -

transactions - can we run code in mysql without creating a procedure -

i want create sql file dba check whether index exists on table. if doesn't exist-create it. found many examples utilize stored procedure, want run once.

something this:

-- creates index if not exist in mysql. start transaction; set indexisthere = 0; set given_table = 'idr_chgs'; set given_index = 'fk_idr_patient_pt_id_idx1'; select count(1) indexisthere information_schema.statistics table_name = given_table , index_name = given_index; if indexisthere = 0 set @sqlstmt = concat('create index ',given_index,' on ', given_database,'.',given_table,' (',given_columns,')'); prepare st @sqlstmt; execute st; deallocate prepare st; select concat('created index ', given_table,'.', given_index, ' on columns ', given_columns) 'createindex status'; else select concat('index ', given_index,' exists on table ', given_database,'.',given_table) 'createindex status'; end if; commit;

is doable?

mysql doesn't back upwards if/then/else constructs outside of stored routines or triggers.

if utilize prepare , execute, form string has create index statement if there no index, , no-op statement (e.g. comment) if not.

select coalesce(concat('select \'index ', s.index_name, ' exists already\''), 'create index `idx_x` on test.foo (`x`)') @sql (select null) d left outer bring together information_schema.statistics s on (s.table_schema, s.table_name, s.index_name) = ('test', 'foo', 'idx_x'); prepare s @sql; execute s;

if index exists, @sql be:

mysql> select @sql; +-------------------------------------+ | @sql | +-------------------------------------+ | select 'index idx_x exists already' | +-------------------------------------+

if index not exist, @sql be:

mysql> select @sql; +----------------------------------------+ | @sql | +----------------------------------------+ | create index `idx_x` on test.foo (`x`) | +----------------------------------------+

by way, create , alter statements implicitly commit before , after statement, there's no purpose in using start transaction , commit in way you're doing. see http://dev.mysql.com/doc/refman/5.6/en/implicit-commit.html

mysql transactions