Setting default collation for all table in MySQL store procedure -

my stored procedure has more 5 create tables statement. there way pick default collation set in global variable these create tables statement without going through individual create table statement.

-- sample block --    drop table if exists abc;     set @createtable = concat("create table "abc"(    record_id varchar(255),    member varchar(255),    name varchar(255)    )character set 'utf8'     collate 'utf8_unicode_ci'");     prepare create_table_statement @createtable;    execute create_table_statement;    deallocate prepare create_table_statement;     -- sample block b--    drop table if exists def;     set @createtable1 = concat("create table "def"(    address varchar(255),    member_gender varchar(255),    member_age varchar(255)      )character set 'utf8'     collate 'utf8_unicode_ci'");     prepare create_table_statement1 @createtable1;    execute create_table_statement1;    deallocate prepare create_table_statement1;    .    .    

there 2 options:

1: use local variable can change affect creation tables

-- sample block --    set @character_set = 'utf8'; set @default_collation = 'utf8_unicode_ci';  drop table if exists abc;     -- use variables build sql:  set @createtable = concat("create table "abc"(    record_id varchar(255),    member varchar(255),    name varchar(255)    )character set '",@character_set,"'     collate '",@default_collation,"'");      -- same next  

2: set database default character set , collation, when set default values: new tables created without specific character set or collation use default.

 create database `your_database`  default character set utf8  default collate utf8_general_ci; 

wiki