changing TEMPORARY TABLE default collation when I use CREATE TEMPORARY TABLE to create a temporary table, the collation of this table is utf8_unicode_ci by default. why by default it is utf8_unicode_ci? How can I change the default?
The CREATE TABLE syntax allows for specifying the character set and collation

The MySQL Documentation for CREATE TABLE shows this syntax:


CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

table_options:
    table_option [[,] table_option] ...

table_option:
    ENGINE [=] engine_name
  | AUTO_INCREMENT [=] value
  | AVG_ROW_LENGTH [=] value
  | [DEFAULT] CHARACTER SET [=] charset_name &lt;&lt;------ (YOU NEED THIS OPTION)
  | CHECKSUM [=] {0 | 1}
  | [DEFAULT] COLLATE [=] collation_name &lt;&lt;------ (YOU NEED THIS OPTION)
  | COMMENT [=] 'string'
  | CONNECTION [=] 'connect_string'
  | DATA DIRECTORY [=] 'absolute path to directory'
  | DELAY_KEY_WRITE [=] {0 | 1}
  | INDEX DIRECTORY [=] 'absolute path to directory'
  | INSERT_METHOD [=] { NO | FIRST | LAST }
  | KEY_BLOCK_SIZE [=] value
  | MAX_ROWS [=] value
  | MIN_ROWS [=] value
  | PACK_KEYS [=] {0 | 1 | DEFAULT}
  | PASSWORD [=] 'string'
  | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
  | STATS_AUTO_RECALC [=] {DEFAULT|0|1}
  | STATS_PERSISTENT [=] {DEFAULT|0|1}
  | TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]
  | UNION [=] (tbl_name[,tbl_name]...)



Therefore, your syntax should be something like this:

CREATE TEMPORARY TABLE tblname
(
) ENGINE=MyISAM CHARACTER SET '...' COLLATE '...';


Give it a Try !!!

If you want to manipulate the default options for collation, you have the following:


collation_connection
collation_database
collation_server


These variables can be changed at the Global or Session Level.

If you want to set the collation permamnently, you could put it in /etc/my.cnf

[mysqld]
collation_connection = 'collation_connection'


and restart mysql

If you only want this for an individual BB Connection, you would run this in the session

SET collation_connection = 'collation_connection ';


See my recent post Why are there different levels of MySQL collation/charsets? to see installed character sets and collations.