changing TEMPORARY TABLE default collation

Question

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?

Answer 1

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 <<------ (YOU NEED THIS OPTION)
  | CHECKSUM [=] {0 | 1}
  | [DEFAULT] COLLATE [=] collation_name <<------ (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.

Answer 2

normally the temp table takes the default from server/database level. if you want to change it, then change at the sever/DB level and this will impact all your other objects as well.

if you want it only once for your current temp table, then declare it strongly at your temp table creation process.