mysql - Key commands for creating temporary tables -

i creating temporary tables in stored procedures. wanted know commands include in procedure avoid conflicts?

i use below syntax

 create temporary table if not exists temp_shipmentcount 

and use

drop command @ end.

should add drop command @ beginning of procedure also?

temporary tables connection scoped. exist until connection closed.

as per documentation on temporary tables

you can use temporary keyword when creating table. temporary table visible current connection, , dropped automatically when connection closed. means 2 different connections can use same temporary table name without conflicting each other or existing non-temporary table of same name...

if connection not closed shared among users, have drop , re-create. dropping database object using shared connection result issues.

it better option use temporary tables runtime generated names within stored procedure. safe on using shared connection objects too.

exampe:

set @temp_table_name := concat( 'temp_table_', ( current_timestamp + 0 ) );  set @sql := concat( 'create temporary table ', @temp_table_name ); set @select_stmt := concat( 'select this', blah, blah ); set @sql := concat( @sql, ' ' , @select_stmt );  prepare stmt @sql; execute stmt; drop prepare stmt;  -- perform opearations on temp table -- -- in between here -- -- , drop before leaving  set @drop_temp := concat( 'drop temporary table ', @temp_table_name ); prepare stmt @drop_temp; execute stmt; drop prepare stmt;