Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
Try show variables like "%character%" ; show variables like "%collation%"; and pay particular attention to the collation_database –  KCD Aug 20 '13 at 19:43

2 Answers 2

up vote 1 down vote accepted

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:

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.

share|improve this answer
    
How can I change the default collation at the server/DB level? –  Arash Mousavi Aug 1 '13 at 20:26
    
Updated my answer ... –  RolandoMySQLDBA Aug 1 '13 at 20:36

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.

share|improve this answer
    
How can I change at the server/DB level? by which query or config? –  Arash Mousavi Aug 1 '13 at 11:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.